java调用mysql存储过程实例分析

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

-- 删除储存过程delimiter // -- 定义结束符号drop procedure p_test;

create procedure p_test()begindeclare temp int;set temp = 0; update test set field1 = values(temp);end

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 existssp1 //  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 parametervalue  */ 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; } }