7、使用CallableStatement接口调用存储过程

第一节：CallableStatement 接口的引入

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

第二节：使用 CallableStatement 接口调用存储过程

void registerOutParameter(int parameterIndex, int sqlType)

按顺序位置 parameterIndex 将 OUT 参数注册为 JDBC 类型 sqlType。

实例演示：

创建存储过程，通过图书id获取图书名bookName

DELIMITER &&
CREATE PROCEDURE pro_getBookNameById(IN bookId INT,OUT bN VARCHAR(100) character set utf8)
 BEGIN
	SELECT bookName INTO bn FROM books WHERE id=bookId;
 END 
&&
DELIMITER ;

CALL pro_getBookNameById(10,@bookName);

SELECT @bookName;

实例：在程序中调用存储过程

public class Books {

	private int id;
	private String bookName;
	private String author;
	private float price;
	private File content;
	private File illustration;
	
	public Books() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Books(String bookName, String author, float price, File content) {
		super();
		this.bookName = bookName;
		this.author = author;
		this.price = price;
		this.content = content;
	}
	
	
	public Books(String bookName, String author, float price, File content, File illustration) {
		super();
		this.bookName = bookName;
		this.author = author;
		this.price = price;
		this.content = content;
		this.illustration = illustration;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getBookName() {
		return bookName;
	}
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public float getPrice() {
		return price;
	}
	public void setPrice(float price) {
		this.price = price;
	}
	public File getContent() {
		return content;
	}
	public void setContent(File content) {
		this.content = content;
	}
	public File getIllustration() {
		return illustration;
	}
	public void setIllustration(File illustration) {
		this.illustration = illustration;
	}
	
	
}

public class DbUtil {

	private static String dbUrl="jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=UTF-8";
	private static String dbUserName="root";
	private static String dbPassword="root";
	private static String jdbcName="com.mysql.jdbc.Driver";
	
	/**
	 * 获取数据库连接
	 * @return
	 * @throws Exception
	 */
	public Connection getCon() throws Exception{
		Class.forName(jdbcName);
		Connection con=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);		
		return con;
	}
	
	/**
	 * 关闭连接
	 * @param con
	 * @throws Exception
	 */
	public void close(Statement stmt,Connection con)throws Exception{
		if(stmt!=null){
			stmt.close();
			if(con!=null){
				con.close();
			}
		}
		
	}
}

public class jdbcTest {
	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(26));
	}
}

运行结果：