java调用mysql存储过程例子

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;
}

}

import java.sql.*;
/**
* 调用带有输入参数的存储过程，三种情况例子
*/
public class ProcedureTest {

public static void main(String[] args) {
//callIn(111);
//callOut();
callResult();
}

/**
* 1.调用带有输入参数的存储过程
* @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();
}
}

}

/**
* 2.调用带有输出参数的存储过程
*
*/
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();
}
}
}

/**
* 3.调用输出结果集的存储过程
*/
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.*;      /**    * 调用带有输入参数的存储过程，三种情况例子    */ public class ProcedureTest {      public static void main(String[] args) {    //callIn(111);    //callOut();    callResult();   }      /**    * 1.调用带有输入参数的存储过程    * @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();     }    }       }     /**    * 2.调用带有输出参数的存储过程    *    */   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();     }    } }      /**    * 3.调用输出结果集的存储过程    */   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();     }    }   }     }