JDBC之数据库基本操作

今天我们来介绍一下如何使用JDBC对数据库进行基本操作。

还记得之前我们在数据库db_book中建立了一个t_book表。我这里把建表的代码再贴一下：

create table `t_book` (
    `id` int (11),
    `bookName` varchar (60),
    `author` varchar (30),
    `price` Decimal (8),
    `bookTypeId` int (11)
); 

根据面向对象的思想，我们在这里建了一个Book类：

public class Book {

    private int id;
    private String bookName;
    private String author;
    private float price;
    private int bookTypeId;

    //为了减少篇幅，以下省略构造方法和get/set方法
}

因为每次操作之前，我们都需要进行数据库的连接，而这些代码又比较繁琐，所以我们把它给封装起来方便使用。

public class DbUtil {
    private static String jdbcName = "com.mysql.jdbc.Driver";
    private static String dbURL = "jdbc:mysql://localhost:3306/db_book";
    private static String dbUserName = "root";
    private static String dbPassword = "123456";

    public Connection getCon() throws Exception {
        Class.forName(jdbcName);
        Connection con = DriverManager.getConnection(dbURL, dbUserName, dbPassword);
        return con;
    }

    public void close(Connection con) throws Exception {
        if (con != null) {
            con.close();
        }
    }

    public void close(Statement stmt, Connection con) throws Exception {
        if (stmt != null) {
            stmt.close();
            if (con != null) {
                con.close();
            }
        }
    }
}

下面我们的目标就是对t_book表进行插入、更新和删除操作。二话不说，直接上代码：

    private static int addBook(Book book) throws Exception {
        Connection con = dbUtil.getCon();
        String sql = "insert into t_book values(null,'" + book.getBookName() + "','" + book.getAuthor() + "',"
                + book.getPrice() + "," + book.getBookTypeId() + ")";
        Statement stmt = con.createStatement();
        int result = stmt.executeUpdate(sql);
        dbUtil.close(stmt, con);
        return result;
    }

    private static int updateBook(Book book) throws Exception {
        Connection con = dbUtil.getCon();
        String sql = "update t_book set bookName='" + book.getBookName() + "',author='" + book.getAuthor() + "',price="
                + book.getPrice() + ",bookTypeId=" + book.getBookTypeId() + " where id = " + book.getId();
        Statement stmt = con.createStatement();
        int result = stmt.executeUpdate(sql);
        dbUtil.close(stmt, con);
        return result;
    }

    private static int deleteBook(int id) throws Exception {
        Connection con = dbUtil.getCon();
        String sql = "delete from t_book where id =" + id;
        Statement stmt = con.createStatement();
        int result = stmt.executeUpdate(sql);
        dbUtil.close(stmt, con);
        return result;
    }

其中Statement接口被用来执行静态SQL语句并返回生成对象的结果。
那么问题又来了，每次书写静态SQL语句的时候总会有一大堆的变量嵌入到字符串中，过程繁琐而且容易出错，为此这里推荐在实战中使用Statement接口的子类PreparedStatement，具体用法见下方代码。

    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.setString(2, book.getAuthor());
        pstmt.setFloat(3, book.getPrice());
        pstmt.setInt(4, book.getBookTypeId());

        int result = pstmt.executeUpdate();
        dbUtil.close(pstmt, con);
        return result;
    }

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

    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;
    }

看看，是不是方便多了？现在我们知道如何对数据库进行操作了，那么我们应该如何从数据库中查到我们需要的数据呢？
欲知后事如何，且听下回分解。