java調用mysql存儲過程實例分析

本文實例講述了java調用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()begindeclare 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 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; } }

希望本文所述對大家的java程序設計有所幫助。