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)

)

BEGIN

DECLARE 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,'123456','18357xxx7','127.0.0.1');

SELECT @userId;

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

6) 上面的例子，还可以使用 if exists ( select from ...) 语句和 FOUND_ROWS() 函数来优化一下：

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)

)

BEGIN

IF EXISTS(SELECT * FROM Users u WHERE u.user_MobileCode=user_MobileCode) THEN

    SELECT u.userId INTO userId FROM Users u WHERE u.user_MobileCode=user_MobileCode AND u.user_Pwd=user_Pwd;

    IF FOUND_ROWS() < 1 THEN

        SELECT -1 INTO userId;

    END IF;

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);

    SELECT LAST_INSERT_ID() INTO userId;

END IF;

END //

DELIMITER ;

2. 例子2

DELIMITER //

DROP PROCEDURE IF EXISTS mingRenTangJiangLi //

CREATE PROCEDURE mingRenTangJiangLi()

BEGIN

DECLARE 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;

REPEAT

set 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 = 1

END 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 = 1

END 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");

    }

对应的xml 文件配置：

  <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>

存储过程的参数的类型，是在xml文件中说明的。

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);

xml 配置文件

  <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);

秒客网

MySQL 存储过程实例与 ibatis/mybatis/hibernate/jdbc 如何调用存储过程

相关文章

MySQL 存储过程实例 与 ibatis/mybatis/hibernate/jdbc 如何调用存储过程

相关文章

MySQL 存储过程实例与 ibatis/mybatis/hibernate/jdbc 如何调用存储过程