JDBC之——PreparedStatement实现对数据库的增删改操作-蒲公英云

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

傷城~ 2022-05-19 01:09 272阅读 0赞

一、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("删除失败");
        }
    }
}