JDBC-使用CallableStatement接口调用存储过程

最新推荐文章于 2022-10-09 11:30:54 发布

原创最新推荐文章于 2022-10-09 11:30:54 发布 · 787 阅读

0 ·

CC 4.0 BY-SA版权

JDBC基础专栏收录该内容

8 篇文章

订阅专栏

本文介绍如何在Java中使用CallableStatement接口调用数据库存储过程，以查询图书信息。通过示例代码展示了如何设置参数、注册输出参数并执行存储过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 CallableStatement接口的引入
在这里插入图片描述
2 使用CallableStatement接口调用存储过程

package com.chb.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;

import com.chb.util.DbUtil;

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(11));
	}
}

package com.chb.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(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;
	}
	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(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,
			File context, File pic) {
		super();
		this.bookName = bookName;
		this.price = price;
		this.author = author;
		this.bookTypeId = bookTypeId;
		this.context = context;
		this.pic = pic;
	}
	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 float getPrice() {
		return price;
	}
	public void setPrice(float price) {
		this.price = price;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public int getBookTypeId() {
		return bookTypeId;
	}
	public void setBookTypeId(int bookTypeId) {
		this.bookTypeId = bookTypeId;
	}
	
	
	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;
	}
	@Override
	public String toString() {
		return "["+this.id+","+this.bookName+","+this.price+","+this.author+","+this.bookTypeId+"]";
	}
}

package com.chb.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;


public class DbUtil {

	// 数据库地址
	private static String dbUrl="jdbc:mysql://localhost:3306/db_book";
	// 用户名
	private static String dbUserName="root";
	// 密码
	private static String dbPassword="123456";
	// 驱动名称
	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();
			}
		}
	}
	
	/**
	 * 关闭连接
	 * @param con
	 * @throws Exception
	 */
	public void close(PreparedStatement pstmt,Connection con)throws Exception{
		if(pstmt!=null){
			pstmt.close();
			if(con!=null){
				con.close();
			}
		}
	}
	
	/**
	 * 关闭连接
	 * @param con
	 * @throws Exception
	 */
	public void close(CallableStatement cstmt,Connection con)throws Exception{
		if(cstmt!=null){
			cstmt.close();
			if(con!=null){
				con.close();
			}
		}
	}
}