Java EE WEB工程师培训_7.doc

Java EE WEB工程师培训发表于2009-6-6 21:56|来自 51CTO网页 只看他 调用有输入、输出参数的存储过程#Michael分割线# CallableStatement 简介 CallableStatement 继承PreparedStatement 提供了调用存储过程的能力 调用简单的存储过程 先创建一个存储过程create procedure all_user() select * from UserTbl 调用存储过程call all_user(); F5刷新 ConnectionUtil.javapackage com.michael.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.util.Properties; public class ConnectionUtil 第一种方法 public Connection getConnection() Connection conn = null; try Class.forName加载驱动 Class.forName( com.mysql.jdbc.Driver ); DriverManager获水草玛瑙 得连接 conn = DriverManager.getConnection( jdbc:mysql:localhost:3306jdbc_db , root , mysqladmin return conn; catch (Exception e) e.printStackTrace(); return null; 第二种方法 public Connection getConnection(String driver,String url,String user,String password) Connection conn = null; try Class.forName加载驱动 Class.forName(driver); DriverManager获得连接 conn = DriverManager.getConnection(url,user,password); return conn; catch (Exception e) e.printStackTrace(); return null; 第三种方法 public Connection openConnection() String driver = String url = String user = String password = Properties prop = new Properties(); Connection conn = null; try 加载属性文件 prop.load(this.getClass().getClassLoader().getResourceAsStream( DBCperties ); driver = prop.getProperty( driver ); url = prop.getProperty( url ); user = prop.getProperty( user ); password = prop.getProperty( password ); Class.forName加载驱动 Class.forName(driver); DriverManager获得连接 conn = DriverManager.getConnection(url,user,password); return conn; catch (Exception e) e.printStackTrace(); return null; TestCallableStatement.javapackage com.michael.jdbc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; public class TestCallableStatement public static void call() Connection conn = new ConnectionUtil().openConnection(); try CallableStatement cstmt = conn.prepareCall( call all_user() ); ResultSet rs = cstmt.executeQuery(); while(rs.next() int id = rs.getInt(1); String user = rs.getString(2); String password = rs.getString(3); int age = rs.getInt(4); System.out.println(id+ : +user+ : +password+ : +age); catch (SQLException e) e.printStackTrace(); finally try conn.close(); catch (SQLException e) e.printStackTrace(); Main.javapackage com.michael.main; import com.michael.jdbc.TestCallableStatement; public class Main public static void main(String args) TestCallableStatement.call(); 调用有输入参数的存储过程 创建有输入参数的存储过程create procedure insert_user(in un varchar(20),in pw varchar(20),in a int) insert into UserTbl(user,password,age) values(un,pw,a) 调用有输入参数高山茶 的存储过程call insert_user(testname,123456,25) 数据己增加 TestCallableStatement.javapackage com.michael.jdbc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; public class TestCallableStatement 调用简单的存储过程 public static void call1() Connection conn = new ConnectionUtil().openConnection(); try CallableStatement cstmt = conn.prepareCall( call all_user() ); ResultSet rs = cstmt.executeQuery(); while(rs.next() int id = rs.getInt(1); String user = rs.getString(2); String password = rs.getString(3); int age = rs.getInt(4); System.out.println(id+ : +user+ : +password+ : +age); catch (SQLException e) e.printStackTrace(); finally try conn.close(); catch (SQLException e) e.printStackTrace(); 调用有输入参数的存储过程 public static void call2() Connection conn = new ConnectionUtil().openConnection(); try CallableStatement cstmt = conn.prepareCall( call insert_user(?,?,?) ); cstmt.setString(1, test1 ); cstmt.setString(2, test2 ); cstmt.setInt(3, 3); cstmt.executeUpdate(); catch (SQLException e) e.printStackTrace(); finally try conn.close(); catch (SQLException e) e.printStackTrace(); Main.javapackage com.michael.main; import com.michael.jdbc.TestCallableStatement; public class Main public static void main(String args) TestCallableStatement.call2(); TestCallableStatement.call1(); 调用有输入、输出参数的存储过程 创建有输入输出参数的存储过程create procedure getAgeByName(in name varchar(20),out return_age int) begin declare a int; select age into a from UserTbl where user=name; set return_age = a; end 测试查询michael 输出年龄11 调用有输入输出的存储过程TestCallableStatement.javapackage com.michael.jdbc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; public class TestCallableStatement 调用简单的存储过程 public static void call1() Connection conn = new ConnectionUtil().openConnection(); try CallableStatement cstmt = conn.prepareCall( call all_user() ); ResultSet rs = cstmt.executeQuery(); while(rs.next() int id = rs.getInt(1); String user = rs.getString(2); String password = rs.getString(3); int age = rs.getInt(4); System.out.println(id+ : +user+ : +password+ : +age); catch (SQLException e) e.printStackTrace(); finally try conn.close(); catch (SQLException e) e.printStackTrace(); 调用有输入参数的存储过程 public static void call2() Connection conn = new ConnectionUtil().openConnection(); try CallableStatement cstmt = conn.prepareCall( call insert_user(?,?,?) ); cstmt.setString(1, test1 ); cstmt.setString(2, test2 ); cstmt.setInt(3, 3); cstmt.executeUpdate(); catch (SQLException e) e.printStackTrace(); finally try conn.close(); catch (SQLException e) e.printStackTrace(); 调用有输入输出参数的存储过程 public static void call3() Connection conn = new ConnectionUtil().openConnection(); try CallableStatement cstmt = conn.pre