java调用mysql存储过程例子
获取数据库连接的类
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(); } } } }
