JDBC之——PreparedStatement实现对数据库的增删改操作

一、PreparedStatement接口引入

PreparedStatement是Statement的子接口，属于预处理操作，与直接使用Statement不同的是，PreparedStatement在操作时，是现在数据表中准备好了一条SQL语句，但是此SQL语句的具体内容暂时不设置，二十之后再进行设置。

（以后开发一部用PreparedStatement，不用Statement）

二、使用PreparedStatement接口实现添加数据操作

//工具
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();
		}
	}
}


//book对象
public class Book {
	private int id;
	private String bookName;
	private float price;
	private String author;
	private int 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(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 Demo01{
	private static DbUtil dbUtil = new DbUtil();
	
	private static int addBook(Book book)throws Exception{
		Connection con = dbUtil.getCon();
		String sql = "insert into t_book values(null,?,?,?,?)";
		
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());//给第一个坑设值
		pstmt.setFloat(2, book.getPrice());
		pstmt.setString(3, book.getAuthor());
		pstmt.setInt(4, book.getBookTypeId());
		int result = pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	public static void main(String[] args) throws Exception {
		Book book = new Book("java测试", 99, "测试", 1);
		int result = addBook(book);
		if(result ==1) {
			System.out.println("添加成功");
		}else {
			System.out.println("添加失败");
		}
	}
}

 

三、使用PreparedStatement接口实现更新数据操作

public class demo02 {
	private static DbUtil dbUtil = new DbUtil();
	
	private static int updateBook(Book book)throws Exception{
		Connection con = dbUtil.getCon();
		String sql = "update t_book set bookName=?,price=?,author=?,bookTypeId=? where id=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setFloat(2, book.getPrice());
		pstmt.setString(3, book.getAuthor());
		pstmt.setInt(4, book.getBookTypeId());
		pstmt.setInt(5, book.getId());
		int result = pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	public static void main(String[] args) throws Exception {
		Book book = new Book(1, "book1", 222, "book作者", 3);
		int result = updateBook(book);
		if(result ==1) {
			System.out.println("更新数据成功");
		}else {
			System.out.println("更新数据失败");
		}
	}
}

 

四、使用PreparedStatement接口实现删除数据操作

public class Demo3 {
	private static DbUtil dbUtil = new DbUtil();
	
	private static int deleteBook(int id) throws Exception{
		Connection con = dbUtil.getCon();
		String sql = "delete from t_book where id = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, id);
		int result = pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	public static void main(String[] args) throws Exception {
		int result = deleteBook(6);
		if(result ==1 ) {
			System.out.println("删除成功");
		}else {
			System.out.println("删除失败");
		}
	}
}