使用 JDBC CallableStatements 执行存储过程
CallableStatement 的所有超级接口为 PreparedStatement,Statement,Wrapper. 其中继承自 PreparedStatement 接口. CallableStatement 主要是调用数据库中的存储过程. 在使用 CallableStatement 时可以接收存储过程的返回值. CallableStatement 对象为所有的 DBMS 提供了一种标准的形式去调用数据库中已存在的存储过程. 对数据库中存储过程的调用是 CallableStatement 对象所含的内容. 有两种形式: 1: 形式带结果参数; 2: 形式不带结果参数. 结果参数是一种输出参数 (存储过程中的输出 OUT 参数), 是存储过程的返回值. 两种形式都有带有数量可变的输入, 输出, 输入和输出的参数. 用问号做占位符.
形式带结果参数语法格式:{ ? = call 存储过程名 [(?, ?, ?, ...)]};
形式不带结果参数语法格式:{ call 存储过程名 [(?, ?, ?, ...)]};PS 方括号里面的内容可有可无.
CallableStatement 接口中常用的方法.
1:getInt(int parameterIndex),getInt(String parameterName), 还有 getString,getBigDecimal,getString,getDate,getURL 等等都类似和 PreparedStatement 与 Statement 中的用法类似.
2:registerOutParameter(int parameterIndex, int sqlType): 按顺序位置 parameterIndex 将 OUT 参数注册为 JDBC 类型 sqlType.
3:wasNull(): 查询最后一个读取的 OUT 参数是否为 SQL Null. 等等还有很多方法, 感兴趣的读者可以自行查阅 JDK API 文档.
讲解了那么多, 不如一个例子来的痛快. 下面通过一个例子让读者更清楚的看到 CallableStatement 的用法.
首先在原先的 t_employee 表中添加表示干了多少年的 tyear 字段.
1 alter table t_employee add tyear int;
在数据库中编写存储过程统计指定 id 的 userName 的人, 输出一下他一共赚了多少钱.
JDBC 代码:
- package com.panli.dbutil;
- /**
- * 连接数据库
- */
- import java.sql.CallableStatement;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- public class DbUtil {
- // 数据库驱动名字
- private static String jdbcName = "com.mysql.jdbc.Driver";
- // 数据库协议地址
- private static String dbUrl = "jdbc:mysql://localhost:3306/db_user";
- // 数据库用户名
- private static String dbUser = "root";
- // 数据库密码
- private static String dbPassword = "123456";
- /**
- * 获取连接
- * @return
- * @throws Exception
- */
- public static Connection getCon() throws Exception{
- Class.forName(jdbcName);
- Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
- return conn;
- }
- /**
- * 关闭连接
- * @param stmt
- * @param conn
- * @throws Exception
- */
- public static void close(Statement stmt,Connection conn) throws Exception{
- if(stmt!=null){
- stmt.close();
- if(conn!=null){
- conn.close();
- }
- }
- }
- /**
- * 关闭连接
- * @param cstmt
- * @param conn
- * @throws Exception
- */
- public static void close(CallableStatement cstmt, Connection conn) throws Exception{
- if(cstmt!=null){
- cstmt.close();
- if(conn!=null){
- conn.close();
- }
- }
- }
- /**
- * 关闭连接
- * @param pstmt
- * @param conn
- * @throws SQLException
- */
- public static void close(PreparedStatement pstmt, Connection conn) throws SQLException{
- if(pstmt!=null){
- pstmt.close();
- if(conn!=null){
- conn.close();
- }
- }
- }
- /**
- * 重载关闭方法
- * @param pstmt
- * @param conn
- * @throws Exception
- */
- public void close(ResultSet rs,PreparedStatement pstmt, Connection conn) throws Exception{
- if(rs!=null){
- rs.close();
- if(pstmt!=null){
- pstmt.close();
- if(conn!=null){
- conn.close();
- }
- }
- }
- }
- }
- package com.panli.model;
- import java.io.File;
- /**
- * model 包下的 cemployee 类, 对每个字段进行建模
- * @author Peter
- *
- */
- public class CEmployee {
- private int id;
- private String userName;
- private double salary;
- private String job;
- private int jobTypeId;
- private File context;
- private File pic;
- private double counts;
- /**
- * 默认的构造方法
- */
- public CEmployee() {
- super();
- // TODO Auto-generated constructor stub
- }
- /**
- * 带一个参数的构造方法
- * @param id
- */
- public CEmployee(int id) {
- super();
- this.id = id;
- }
- /**
- * 两个参数的构造方法
- * @param counts
- * @param userNames
- */
- public CEmployee(double counts, String userName) {
- // TODO Auto-generated constructor stub
- this.counts = counts;
- this.userName = userName;
- }
- /**
- * 重写 toString() 方法
- */
- @Override
- public String toString(){
- return userName+"一共赚了"+counts+"钱";
- }
- public int getId() {
- return id;
- }
- public void setId(int id) {
- this.id = id;
- }
- public String getUserName() {
- return userName;
- }
- public void setUserName(String userName) {
- this.userName = userName;
- }
- public double getSalary() {
- return salary;
- }
- public void setSalary(double salary) {
- this.salary = salary;
- }
- public String getJob() {
- return job;
- }
- public void setJob(String job) {
- this.job = job;
- }
- public int getJobTypeId() {
- return jobTypeId;
- }
- public void setJobTypeId(int jobTypeId) {
- this.jobTypeId = jobTypeId;
- }
- public File getContext() {
- return context;
- }
- public void setContext(File context) {
- this.context = context;
- }
- public File getPic() {
- return pic;
- }
- public void setPic(File pic) {
- this.pic = pic;
- }
- public double getCounts() {
- return counts;
- }
- public void setCounts(double counts) {
- this.counts = counts;
- }
- }
- package com.panli.dao;
- import java.sql.CallableStatement;
- import java.sql.Connection;
- import java.sql.Types;
- import java.util.ArrayList;
- import java.util.List;
- import com.panli.dbutil.DbUtil;
- import com.panli.model.CEmployee;
- public class CountsEmployeeDao {
- private static DbUtil dbUtil = new DbUtil();
- /**
- * 调用存储过程得到指定 ID 用户的一共赚了多少钱
- * @param employee
- * @return
- * @throws Exception
- */
- public static List getCountsById(CEmployee cemployee)throws Exception{
- List list = new ArrayList();
- Connection conn = dbUtil.getCon();
- String sql = "{call pro_getCountById(?, ?, ?)}";
- CallableStatement cstmt = conn.prepareCall(sql);
- cstmt.setInt(1, cemployee.getId());
- cstmt.registerOutParameter(2, Types.DOUBLE);
- cstmt.registerOutParameter(3, Types.VARCHAR);
- cstmt.execute();
- double counts = cstmt.getDouble("counts");
- String userNames = cstmt.getString("userNames");
- CEmployee emp = new CEmployee(counts, userNames);
- list.add(emp);
- dbUtil.close(cstmt, conn);
- return list;
- }
- /**
- * 做测试的主方法
- * @param args
- */
- public static void main(String[] args)throws Exception {
- CEmployee cemployee = new CEmployee(1);
- List list = getCountsById(cemployee);
- for(CEmployee cemp: list){
- System.out.println(cemp);
- }
- }
- }
创建的存储过程为:
- delimiter &&
- create procedure pro_getCountById(in tid int, out counts double, out userNames varchar(20))
- begin
- select salary*tyear into counts from t_employee where id = tid;
- select userName into userNames from t_employee where id = tid;
- end
- &&
测试:
- call pro_getCountById(1, @counts, @userNames);
- select @counts, @userNames;
来源: http://www.bubuko.com/infodetail-3099716.html