程序执行存储过程首先需要在控制台书写存储过程代码,并且执行存储过程,我是用idea的数据库模块书写代码并且在上面执行存储过程,我的存储过程代码为:
create procedure PRO_usedGoods_Purchase(IN inuid varchar(11), IN ingid int) label:BEGIN -- 声明变量 账户余额、商品价格、商品状态 DECLARE decoin int; DECLARE deprice int; DECLARE destate int; -- 获取变量值 SELECT coin into decoin FROM user WHERE uid = inuid; SELECT price into deprice FROM usedgoods WHERE gid = ingid; SELECT gstatus into destate FROM usedgoods WHERE gid = ingid; -- 账户余额小于商品价格 IF decoin < deprice THEN # '您的账户余额不足!' ROLLBACK; LEAVE label; END IF; -- 商品已被出售 IF destate != 0 THEN # '该商品已出售' ROLLBACK; LEAVE label; END IF; IF decoin > deprice THEN -- 账户上扣除商品价格 UPDATE user SET coin = decoin-deprice WHERE uid = inuid; -- 置商品为已售 UPDATE usedGoods SET gstatus = 1 WHERE gid = ingid; -- 添加纪录到goodsOrder商品订单表 # INSERT INTO goodsOrder(gid,otime,uid) VALUES(ingid,DATE(CURDATE()),inuid); -- 成功执行存储过程 END IF; END; 其中SELECT coin into decoin FROM user WHERE uid = inuid;语句为将查询到的金币值赋给一个变量decoin,我先前是写的SELECT coin = decoin FROM user WHERE uid = inuid;最终查询到的结果全为空,mysql不能这样赋值,所以应该用into语句。
第二,在JDBC工具类上面书写调用存储过程的函数,在JDBC工具类中书写存储过程的好处是该工具类已经获取了MySQL连接数据库的驱动、用户名、密码、路径等,所以在该类中书写代码然后在其他类中直接调用该方法,增加了程序的可读性,代码如下:
//执行存储过程代码 //当用户点击购买后若用户金币大于商品价钱则执行购买操作,减去用户账户上面相对应的金币,且相应的 //商品状态置为已售状态,若小于商品价格则执行回滚操作,并提示账户余额不足 public static void executePRO(String uid, int gid) throws ClassNotFoundException, SQLException { Class.forName(driver); Connection connection = DriverManager.getConnection(url, username, password); CallableStatement cstm = connection.prepareCall("{call PRO_usedGoods_Purchase(?,?)}"); cstm.setString(1, uid); cstm.setInt(2, gid); cstm.execute(); cstm.close(); connection.close(); }
最后在DAO层直接调用该类中的方法
public static int insert(GoodsOrder go) throws SQLException, ClassNotFoundException { String sql = "select gstatus from usedGoods where gid = ?"; ResultSet rs = JDBCHelper.query(sql,go.getGid()); int gstatus=0; if(rs.next()){ gstatus = rs.getInt(1); } if (gstatus==0) { String SQL = " insert into " +tableName + " ( gid , uid ,name , address , phone , oimage , price) " +" values ( "+go.getGid()+" , '"+go.getUid()+"' , '"+go.getName()+"' , '"+go.getAddress()+"' , '"+go.getPhone()+"' , '"+go.getOimage()+"' , "+go.getPrice()+" )"; JDBCHelper.executePRO(go.getUid(),go.getGid());//调用存储过程 return JDBCHelper.executeInsert( SQL) ; } return 0; }