JDBC与MySQL存储过程

数据库的测试代码如下 :

1、新建表test
create table test(
field1 int not null
)
TYPE=MyISAM ;
insert into test(field1) values(1);


2、删除已存在的存储过程
-- 删除储存过程
delimiter // -- 定义结束符号
drop procedure p_test;


3、mysql存储过程定义
create procedure p_test()
begin
declare temp int;
set temp = 0;
update test set field1 = values(temp);
end


4、调用方法
CallableStatement cStmt = conn.prepareCall("{call p_test()}");
cStmt.executeUpdate();

 import java.sql.*;
 
/**
iGoder
*/
public class ProcedureTest {
 /*
 表和存储过程定义如下：
 delimiter //
 
DROP TABLE if exists test //
    CREATE TABLE test(
      id int(11) NULL
    ) //

 drop procedure if exists sp1 //
   
    create procedure sp1(in p int)
    comment 'insert into a int value'
    begin
      declare v1 int;
      set v1 = p;
      insert into test(id) values(v1);
    end
    //
   
    drop procedure if exists sp2 //
    create procedure sp2(out p int)
    begin
      select max(id) into p from test;
    end
    //
    
 drop procedure if exists sp6 //
    create procedure sp6()
    begin
       select * from test;
    end//
   
 */
 
 public static void main(String[] args) {
  //callIn(111);
  //callOut();
  callResult();
 }
 
 /**
  * 调用带有输入参数的存储过程
  * @param in     stored procedure input parameter value
  */
 public static void callIn(int in){
  //获取连接
  Connection conn = ConnectDb.getConnection();
  CallableStatement cs = null;
  try {
   //可以直接传入参数
   //cs = conn.prepareCall("{call sp1(1)}");
 
   //也可以用问号代替
   cs = conn.prepareCall("{call sp1(?)}");
   //设置第一个输入参数的值为110
   cs.setInt(1, in);
 
   cs.execute();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if(cs != null){
     cs.close();
    }
    if(conn != null){
     conn.close();
    }
   } catch (Exception ex) {
    ex.printStackTrace();
   }
  }
 
 }
 
 /**
  * 调用带有输出参数的存储过程
  *
  */
 public static void callOut() {
  Connection conn = ConnectDb.getConnection();
  CallableStatement cs = null;
  try {
   cs = conn.prepareCall("{call sp2(?)}");
   //第一个参数的类型为Int
   cs.registerOutParameter(1, Types.INTEGER);
   cs.execute();
 
   //得到第一个值
   int i = cs.getInt(1);
   System.out.println(i);
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if(cs != null){
     cs.close();
    }
    if(conn != null){
     conn.close();
    }
   } catch (Exception ex) {
    ex.printStackTrace();
   }
  }
 }
 
 /**
  * 调用输出结果集的存储过程
  */
 public static void callResult(){
  Connection conn = ConnectDb.getConnection();
  CallableStatement cs = null;
  ResultSet rs =  null;
  try {
   cs = conn.prepareCall("{call sp6()}");
   rs = cs.executeQuery();
 
   //循环输出结果
   while(rs.next()){
    System.out.println(rs.getString(1));
   }
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if(rs != null){
     rs.close();
    }
    if(cs != null){
     cs.close();
    }
    if(conn != null){
     conn.close();
    }
   } catch (Exception ex) {
    ex.printStackTrace();
   }
  }
 }
 
}

 

/**
*获取数据库连接的类
*/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
class ConnectDb {
 
 public static Connection getConnection(){
  Connection conn = null;
  PreparedStatement preparedstatement = null;
  try {
   Class.forName("org.gjt.mm.mysql.Driver").newInstance();
   String dbname = "test";
   String url ="jdbc:mysql://localhost/"+dbname+"?user=root&password=root&useUnicode=true&characterEncoding=8859_1" ;
   conn= DriverManager.getConnection(url);
  } catch (Exception e) {
   e.printStackTrace();
  }
  return conn;
 }
 
}