使用 PreparedStatement 接口实现增，删，改操作

　一、PreparedStatement接口　

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

连接与加载数据库

    // 数据库地址
    private static String dbUrl = "jdbc:mysql://localhost:3306/db_bank";
    // 用户名
    private static String dbUserName = "root";
    // 密码
    private static String dbPassvord = "3306";
    // 驱动名称
    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, dbPassvord);
        return con;
        
    }

连接关闭：

 1　　　 /**
 2      * 关闭连接
 3      * @param con
 4      * @throws Exception
 5      */
 6     public void close(PreparedStatement pstmt,Connection con) throws Exception {
 7         if (pstmt!=null) {
 8             pstmt.close();
 9             if (con!=null) {
10                 con.close();
11             }
12         }
13     }

1、插入数据

 1         /**
 2      * 添加图书
 3      * @param book
 4      * @return
 5      * @throws Exception
 6      */
 7     private static int addBook2(Book book) throws Exception{
 8         Connection con=dbUtil.getCon();//获取连接
 9         String sql="insert into t_book values(null,?,?,?,?)";
10         PreparedStatement pstmt=con.prepareStatement(sql);
11         pstmt.setString(1, book.getBookName());
12         pstmt.setString(2, book.getAuthor());
13         pstmt.setFloat(3, book.getPrice());
14         pstmt.setInt(4, book.getBookTypeId());
15         int result=pstmt.executeUpdate();
16         dbUtil.close(pstmt, con);
17         return result;
18         }
19     public static void main(String[] args) throws Exception {
20         Book book=new Book("java牛牛3","牛哥1",129,1);
21         int result=addBook2(book);
22         if (result==1) {
23             System.out.println("添加成功！");    
24         }else {
25             System.out.println("添加失败！");
26             }
27 
28     }
29

2、更新数据

/**
     * 更新图书
     * @param book
     * @return
     * @throws Exception
     */
    private static int updateBook(Book book) throws Exception{
        Connection con=dbUtil.getCon();//获取连接
        String sql="update t_book set bookName=?,author=?,price=?,bookTypeId=? where id=?";
        PreparedStatement pstmt=con.prepareStatement(sql);//创建PreparedStatement
        pstmt.setString(1, book.getBookName());
        pstmt.setString(2, book.getAuthor());
        pstmt.setFloat(3, book.getPrice());
        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(3,"kk","牛k",12,1);
        int result=updateBook(book);
        System.out.println(book.getId());
        if (result==1) {
            System.out.println("更新成功！");    
        }else {
            System.out.println("更新失败！");
            }
    }

3、删除数据

/**
     * 删除图书
     * @param book
     * @return
     * @throws Exception
     */
    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);//创建PrepareStatement
        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(4);
        if (result==1) {
            System.out.println("删除成功！");    
        }else {
            System.out.println("删除失败！");
        }
    }