JDBC之——CallableStatement调用存储过程

小鱼儿 2022-05-19 02:57 277阅读 0赞

### 一、CallableStatement接口的引入 ###

CallableStatement主要是调用数据库中的存储过程，CallableStatement也是Statement接口的子接口。在使用CallableStatement时可以接收存储过程的返回值。

### 二、使用CallableStatement接口调用存储过程 ###

void registerOutParameter(int parameterIndex,int sqlType)

核心代码：

![70][]

数据库存储过程：

![70 1][]

代码：

public class DbUtil {
    	
    	//数据库地址
    	private static String dbUrl="jdbc:mysql://localhost:3306/db_book";
    	//用户名
    	private static String dbUserName="root";
    	//密码
    	private static String dbPassword="root";
    	//驱动名称
    	private static String jdbcName="com.mysql.jdbc.Driver";
    	
    	/**
    	 * 获取数据库连接
    	 * 	1.加载数据库驱动
    	 *  2.获取数据库连接
    	 */
    	public Connection getCon() throws Exception {
    		
    		Class.forName(jdbcName);//加载数据库驱动
    		
    		Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
    		
    		return con;
    	}
    	
    	/**
    	 * 关闭连接
    	 */
    	public void close(Statement stmt,Connection con) throws Exception {
    		if(stmt!=null) {
    			stmt.close();
    		}
    		if(con!=null) {
    			con.close();
    		}
    	}
    }

package com.java1234.jdbc.model;
    
    import java.io.File;
    
    public class Book {
    	private int id;
    	private String bookName;
    	private float price;
    	private String author;
    	private int bookTypeId;
    	private File context;
    	private File pic;
    	
    
    	public Book(String bookName, float price, String author, int bookTypeId, File context, File pic) {
    		super();
    		this.bookName = bookName;
    		this.price = price;
    		this.author = author;
    		this.bookTypeId = bookTypeId;
    		this.context = context;
    		this.pic = pic;
    	}
    
    	public Book(String bookName, float price, String author, int bookTypeId, File context) {
    		super();
    		this.bookName = bookName;
    		this.price = price;
    		this.author = author;
    		this.bookTypeId = bookTypeId;
    		this.context = context;
    	}
    
    	public Book(String bookName, float price, String author, int bookTypeId) {
    		super();
    		this.bookName = bookName;
    		this.price = price;
    		this.author = author;
    		this.bookTypeId = bookTypeId;
    	}
    	
    	public Book(int id, String bookName, float price, String author, int bookTypeId) {
    		super();
    		this.id = id;
    		this.bookName = bookName;
    		this.price = price;
    		this.author = author;
    		this.bookTypeId = bookTypeId;
    	}
    
    }

demo

public class Demo1 {
    	
    	private static DbUtil dbUtil = new DbUtil();
    	/**
    	 * 调用存储过程，通过id查询bookName
    	 * @param id
    	 * @return
    	 * @throws Exception
    	 */
    	private static String getBookNameById(int id) throws Exception{
    		Connection con = dbUtil.getCon();
    		String sql = "{CALL pro_getBookNameById(?,?)}";
    		CallableStatement cstmt = con.prepareCall(sql);
    		cstmt.setInt(1, id);//设置第一个参数
    		cstmt.registerOutParameter(2, Types.VARCHAR);//设置返回类型
    		cstmt.execute();
    		String bookName = cstmt.getString("bn");//获取返回值
    		dbUtil.close(cstmt, con);
    		return bookName;
    	}
    	public static void main(String[] args) throws Exception{
    		System.out.println("图书名称时："+getBookNameById(4));
    	}
    	
    }

[70]: /images/20220519/a48efaf12acf4c68b9d306f6d4b6f2ef.png
[70 1]: /images/20220519/043a0ddefc614b5dac4da252f351ec1b.png