前提:知道Mysql创建存储过程的语法及sql语句,不知道先看一下这方面知识
http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html
1、jdbc创建存储过程
- private static Connection conn = null;
- private static PreparedStatement pstmt = null;
- public static void loginProcedure(){
- conn = getConnection(); // 这里getConnection方法没列出来,就是连接数据库,返回java.sql.Connection对象引用
- String procedureSQL = "create procedure USER_EXIST(in loginName varchar(50),out amount int)" +
- "select count(*) into amount from user where username = loginName ; ";
- try {
- pstmt = conn.prepareStatement(procedureSQL);
- pstmt.executeUpdate();
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } finally{
- try {
- pstmt.close();
- pstmt = null;
- conn.close();
- conn = null;
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
-
- }
- }
主要是存储过程的sql语句的书写,其实写法和普通的jdbc书写sql语句一样,只是存储过程的纯sql语句本来就复杂些,特别是存储过程比较复杂的时候,jdbc的sql语句写法就要好好注意了,不过只要数据库的存储过程sql语句清楚的话,jdbc书写也还是简单的;
上面的String procedureSQL是简化的写法,一般化的写法如下:
- String procedureSQL = " create procedure USER_EXIST(in loginName varchar(50),out amount int)" +
- " begin" +
- " declare middleVariable int;" + //声明中间变量,用到的话就声明
- " select count(*) into amount from user where username = loginName;" +
- " end;" ;
- //在begin和end之间可以进行逻辑代码的书写
当然,不能把凑起来的sql语句直接放到数据库中执行来检验是否正确,因为她解析不过来,需要按如下方式在数据库中输入
- mysql> delimiter $$
- mysql> create procedure USER_EXIST(in loginName varchar(50),out amount int)
- -> begin
- -> declare middleVariable int;
- -> select count(*) into amount from user where username = loginName;
- -> end
- -> $$
- Query OK, 0 rows affected (0.00 sec)
-
- mysql> delimiter ;
delimiter $$是将Mysql的语句结束符改成$$,这样在创建存储过程中就能使用分号了(分号默认是Mysql的语句结束符) 最后delimiter ;是还原Mysql语句结束符
2、调用存储过程
- private static Connection conn = null;
- private static PreparedStatement pstmt = null;
- private static CallableStatement cstmt = null;
- String username = "132";
- conn = getConnection();
- try {
- cstmt = conn.prepareCall("{call USER_EXIST(?,?)}");
- cstmt.setString(1, username);
- cstmt.registerOutParameter(2, Types.INTEGER); //注册输出参数
- cstmt.execute();
- System.out.println(cstmt.getInt(2)); //与上注册的对应
-
- } catch (SQLException e) {
- e.printStackTrace();
- } finally{
- try {
- cstmt.close();
- cstmt = null;
- conn.close();
- conn = null;
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
|