Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之07. JDBC CallableStatement

–CallableStatement 简介
–调用简单的存储过程
–调用有输入参数的存储过程
–调用有输入、输出参数的存储过程

################Michael分割线####################

• CallableStatement 简介
–CallableStatement 继承PreparedStatement
–提供了调用存储过程的能力

• 调用简单的存储过程

先创建一个存储过程

create procedure all_user()    
select * from UserTbl

调用存储过程

call all_user();

F5刷新

ConnectionUtil.java

package 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:3306/jdbc_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("DBConfig.properties"));    
                        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.java

package 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.java

package 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.java

package 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.java

package 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.java

package 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.prepareCall( "{call getAgeByName(?,?)}");    
                        cstmt.setString(1, "redking");    
                         //注册输出参数    
                        cstmt.registerOutParameter(2, Types.INTEGER);    
                        cstmt.execute();    
                         int age = cstmt.getInt(2);    
                        System.out.println(age);    
                } catch (SQLException e) {    
                        e.printStackTrace();    
                } finally{    
                         try {    
                                conn.close();    
                        } catch (SQLException e) {    
                                e.printStackTrace();    
                        }    
                }    
        }    
}

Main.java

package com.michael.main;    

import com.michael.jdbc.TestCallableStatement;    

public class Main {    
         public static void main(String[] args) {    
                 //TestCallableStatement.call2();    
                 //TestCallableStatement.call1();    
                TestCallableStatement.call3();    
        }    
}

输出redking年龄为22

################Michael分割线####################