MYSQL存储过程的简单运用，包括传值、返回值、返回集合

边学边记

创建一个简单的存储过程，没有复杂的处理，就是简单的封装几个sql语句，方便后台程序调用

#创建存储过程

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(`i_uuid` varchar(40),`appid` varchar(20),`channel` varchar(20),`device_id` varchar(255),`androidid` varchar(40),`model` varchar(255),`display` varchar(40),`osversion` varchar(20),`osapiver` varchar(20),`pck` varchar(255),`vcode` varchar(20),`i_user_key` varchar(40),OUT `o_openid` varchar(40),OUT `o_name` varchar(255),OUT `o_photo` text,OUT `o_coin` int)
BEGIN
   

    #有则插入，无则更新
    INSERT `app_device`(`uuid`,`appid`,`channel`,`device_id`,`androidid`,`model`,`display`,`osversion`,`osapiver`,`pck`,`vcode`) VALUES (i_uuid,appid,channel,device_id,androidid,model,display,osversion,osapiver,pck,vcode) ON DUPLICATE KEY UPDATE `regTime`=CURRENT_TIMESTAMP();

    #有则插入，无则更新
  INSERT `bby_user`(`user_key`,`uuid`,`channel`) VALUES (i_user_key,i_uuid,channel) ON DUPLICATE KEY UPDATE `regTime`= CURRENT_TIMESTAMP();#CURRENT_TIMESTAMP()不能使用系统自动填充的写成`CURRENT_TIMESTAMP`()

    # 返回用户`openid`,`name`,`photo`,`coin` 
    SELECT `openid`,`name`,`photo`,`coin` INTO o_openid,o_name,o_photo,o_coin FROM bby_user WHERE `user_key`=i_user_key AND `uuid`=i_uuid; 

    # 查询今日完成的任务
    SELECT `type`,`actionType`,`action` FROM `bby_actions` WHERE `user_key`=i_user_key AND `type`=1 AND(actionType=2 OR actionType=3 OR actionType=4)AND `mms`>UNIX_TIMESTAMP(CURDATE()) ORDER BY `type`,`actionType`,`action`;
    
END;

 

java调用

protected static JSONObject device(DeviceDom dom) {
        Connection conn = Listener.getConnection();
        CallableStatement cs = null;
        ResultSet rs = null;
        JSONObject json = new JSONObject();
        try {
            String uuid = MD5Util.MD5Encode("xxx", "UTF-8");
            String user_key = MD5Util.MD5Encode("xxx", "UTF-8");
            // 调用存储过程
            cs = conn.prepareCall("{call bby_device(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setString(1, uuid); // varchar(40)
            cs.setString(2, dom.appid); // varchar(20)
            cs.setString(3, dom.channel); // varchar(20)
            cs.setString(4, dom.device_id); // varchar(255)
            cs.setString(5, dom.androidid); // varchar(40)
            cs.setString(6, dom.model); // varchar(255)
            cs.setString(7, dom.display); // varchar(40)
            cs.setString(8, dom.osversion); // varchar(20)
            cs.setString(9, dom.osapiver); // varchar(20)
            cs.setString(10, dom.pck); // varchar(255)
            cs.setString(11, dom.vcode); // varchar(20)
            cs.setString(12, user_key); // varchar(40)
            // 返回用户`openid`,`name`,`photo`,`coin`
            cs.registerOutParameter(13, Types.VARCHAR);// 返回openid
            cs.registerOutParameter(14, Types.VARCHAR);// 返回name
            cs.registerOutParameter(15, Types.VARCHAR);// 返回photo
            cs.registerOutParameter(16, Types.INTEGER);// 返回coin
            rs = cs.executeQuery();// 执行查询操作，并获取结果集
            json.put("uuid", uuid);
            json.put("user_key", user_key);
            json.put("openid", cs.getString(13));// 获取返回的指定值
            json.put("name", cs.getString(14));// 获取返回的指定值
            json.put("photo", cs.getString(15));// 获取返回的指定值
            json.put("coin", cs.getInt(16));// 获取返回的指定值
            JSONArray array = new JSONArray();
            while (rs != null && rs.next()) {// 获取的返回集合封存到JSONArray里
                int type = rs.getInt("type");
                int actionType = rs.getInt("actionType");
                int action = rs.getInt("action");
                JSONObject job = new JSONObject();
                job.put("type", type);
                job.put("actionType", actionType);
                job.put("action", action);
                array.add(job);
            }
            json.put("actions", array);
        } catch (Exception e) {
            Listener.Log.error(bubuying.prefix + "初始化APP异常。;androidid:" + dom.androidid, e);
        } finally {
            Listener.freeConnection(conn, rs, null, cs, bubuying.prefix + "关闭初始化APP失败连接失败");
        }
        return json;
    }