你的位置:首页 > 数据库

[数据库]MySQL 存储过程实例 与 ibatis/mybatis/hibernate/jdbc 如何调用存储过程


虽然MySQL的存储过程,一般情况下,是不会使用到的,但是在一些特殊场景中,还是有需求的。最近遇到一个sql server向mysql迁移的项目,有一些sql server的存储过程需要向mysql迁移。所以进行复习了一下。下面是一些存储过程的例子。

1. 例子1

DELIMITER //DROP PROCEDURE IF EXISTS loginandreg //CREATE PROCEDURE loginandreg(  OUT userId   BIGINT,  IN user_Pwd             VARCHAR(32),  IN user_MobileCode          VARCHAR(16),  IN user_RegIP            VARCHAR(16))BEGINDECLARE cnt BIGINT DEFAULT 0;DECLARE cnt2 BIGINT DEFAULT 0;DECLARE outid BIGINT DEFAULT -1;SELECT COUNT(*) INTO cnt FROM Users u WHERE u.user_MobileCode=user_MobileCode;IF cnt > 0 THEN  SELECT COUNT(*) INTO cnt2 FROM Users u WHERE u.user_MobileCode=user_MobileCode AND u.user_Pwd=user_Pwd;    IF cnt2 > 0 THEN    SELECT u.userId INTO outid FROM Users u     WHERE u.user_MobileCode=user_MobileCode AND u.user_Pwd=user_Pwd LIMIT 1;  ELSE      SELECT -1 INTO outid;  END IF;    SELECT outid INTO userId;ELSE   INSERT INTO Users(user_Pwd,user_MobileCode,user_Visibility,user_Level,user_RegTime,        user_RegIP,user_Collecter,user_Collected)  VALUES (user_Pwd,user_MobileCode,6,6,NOW(),user_RegIP,0,0);  SET userId=LAST_INSERT_ID();  SELECT userId;END IF;END //DELIMITER ;

知识点

1)参数分为 in, out 类型,即输入类型和输出类型;

2)select xx into varible from table where ... 句式:

     SELECT COUNT(*) INTO cnt FROM Users u WHERE u.user_MobileCode=user_MobileCode;

3)if cnt > 0 then ... elseif cnt =0 then ... else ... end if;

    if 语句注意带有 then 关键字和 end if 结束关键字。

4)获取 insert 语句的主键:set userId=last_insert_id(); select userId;

   select last_insert_id() into userId; 也是可以的。

5)mysql客户端 如何调用该存储过程:

CALL loginandreg(@userId,'112358','18357xxx7','127.0.0.1');SELECT @userId;

 最后的 select @userId 就是存储过程的 out 类型参数返回的结果。

2. 例子2

DELIMITER //DROP PROCEDURE IF EXISTS mingRenTangJiangLi //CREATE PROCEDURE mingRenTangJiangLi()BEGINDECLARE total_level,role_id,ming_ren_level,ming_ren_type,        fuben_times,tiaozhan_times,duobei_shijian,no_more_data INT DEFAULT 0;DECLARE my_cursor CURSOR FOR SELECT playerRoleId,`level`,type from mingrentang;DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_data = 1;OPEN my_cursor;FETCH my_cursor INTO role_id,ming_ren_level,ming_ren_type;REPEATset total_level = ming_ren_level + 10 * (ming_ren_type-1);set fuben_times = total_level / 2;set tiaozhan_times = total_level /3;set duobei_shijian = 10 * total_level;select total_level,fuben_times,tiaozhan_times,duobei_shijian;update player_role set hufu=hufu+1000,paihangbangNumber=paihangbangNumber+tiaozhan_times,        duobeiShiJian=duobeiShiJian+duobei_shijian,fubenTimes=fubenTimes+fuben_times;FETCH my_cursor INTO role_id,ming_ren_level,ming_ren_type;UNTIL no_more_data = 1END REPEAT;CLOSE my_cursor;END //DELIMITER ;

知识点

1)该例子演示了游标的用法:

DECLARE my_cursor CURSOR FOR SELECT playerRoleId,`level`,type from mingrentang;DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_data = 1;

定义了游标语句,也说明了游标循环结束时设置的标志:SET no_more_data = 1;

OPEN my_cursor;FETCH my_cursor INTO role_id,ming_ren_level,ming_ren_type;

打开游标,从游标中获取值。

REPEAT
......
FETCH my_cursor INTO role_id,ming_ren_level,ming_ren_type;UNTIL no_more_data = 1END REPEAT;
repeat 循环 直到 no_more_data = 1: UNTIL no_more_data = 1,然后结束循环 END REPEAT;
最后关闭游标 close my_cursor;

因为上面在定义游标时,指明了,没有数据时设置了 no_more_data = 1,所以这里使用 UNTIL no_more_data = 1 来退出repeat

2)判断相等是使用 = ,而不是 == ,赋值操作是使用 set var=xxx; :set fuben_times = total_level / 2;

3)循环: repeat ... until ...  end repeat;

3. Java 如何调用存储过程

1)hibernate调用存储过程:

  /*     * 调用无参数的存储过程,传入存储过程名字     */  public int callProcedure(final String procedureName)  {      int count = (Integer)this.getHibernateTemplate().execute(        new HibernateCallback(){        public Object doInHibernate(Session session) throws HibernateException, SQLException {          String procedureSql = "{call "+ procedureName +"()}";          Query query = session.createSQLQuery(procedureSql);          Integer num = query.executeUpdate();          return num;        }      });      return count;  }

2)ibatis 调用mysql 存储过程:

  @Override  public Long loginAndRegByProcedure(String user_Pwd, String user_MobileCode, String user_RegIP){    Long userId = null;    HashMap<String,Object> paramMap = new HashMap<String,Object>();     paramMap.put("userId", userId);     paramMap.put("user_Pwd", user_Pwd);     paramMap.put("user_MobileCode", user_MobileCode);     paramMap.put("user_RegIP", user_RegIP);         this.getSqlMapClientTemplate().queryForObject("Users.loginAndRegByProcedure", paramMap);     return (Long)paramMap.get("userId");   }

对应的

 <parameterMap id="pro_pram_Map" class="java.util.Map">   <parameter property="userId" javaType="java.lang.Long" jdbcType="BIGINT" mode="OUT"/>   <parameter property="user_Pwd" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>   <parameter property="user_MobileCode" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>   <parameter property="user_RegIP" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/> </parameterMap> <procedure id="loginAndRegByProcedure" parameterMap="pro_pram_Map">   {call loginandreg(?, ?, ?, ?)} </procedure>

存储过程的参数的类型,是在

3) JDBC 调用mysql 存储过程:

  public Long loginAndRegByProcedure2(String user_Pwd, String user_MobileCode, String user_RegIP){    Connection conn = DbUtil.getConnection();    CallableStatement cstmt = conn.prepareCall("{call loginandreg(?, ?, ?, ?)}");    cstmt.setString(2, user_Pwd);    cstmt.setString(3, user_MobileCode);    cstmt.setString(4, user_RegIP);    cstmt.registerOutParameter(1, java.sql.Types.BIGINT);    cstmt.execute();    return cstmt.getLong(1);  }

输入参数:cstmt.setString(2, user_Pwd);

输出参数:cstmt.registerOutParameter(1, java.sql.Types.BIGINT);

4)mybatis 调用mysql存储过程:

mapper 接口

public interface UserMapper {  Long loginAndRegByProcedure(Map<String, Object> param);

 

 <select id="loginAndRegByProcedure" parameterType="java.util.Map" statementType="CALLABLE" resultType="long">    {call loginandreg(       #{userId,jdbcType=BIGINT,mode=OUT},       #{user_Pwd,jdbcType=VARCHAR,mode=IN},       #{user_MobileCode,jdbcType=VARCHAR,mode=IN},        #{user_RegIP,jdbcType=VARCHAR,mode=IN}     )    } </select>

注意: statementType="CALLABLE" resultType="long" 和 mode=OUT

service 层调用mapper接口:

    Long userId = null;    HashMap<String,Object> paramMap = new HashMap<String,Object>();     paramMap.put("userId", userId);     paramMap.put("user_Pwd", user_Pwd);     paramMap.put("user_MobileCode", user_MobileCode);     paramMap.put("user_RegIP", user_RegIP);    
userId=userMapper.loginAndRegByProcedure(map);