利用java实现数据库增删查改(dao层)

查找

public Books inquire(String bookName, String bookAuthor) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnnection();
            String sql = "select * from book where bookName=? and author=?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, bookName);
            pstm.setString(2, bookAuthor);
            rs = pstm.executeQuery();
            if (rs.next()) {
                return new Books(rs.getString("bookName"), rs.getString("author"),
                        rs.getString("bookType"), rs.getInt("bookNumber"),
                        rs.getInt("id"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(rs, pstm, conn);
        }
        return null;
    }

添加

public void addLend(int userId, int bookId) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnnection();
            String sql = "insert lendBooks (userId,bookId) values (?,?)";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, userId);
            pstm.setInt(2, bookId);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(null, pstm, conn);
        }
    }

删除

public void userIdClear(int userId, int bookId) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnnection();
            String sql = "delete from lendBooks where userId=? and bookId=?";
            pstm=conn.prepareStatement(sql);
             pstm.setInt(1,userId);
             pstm.setInt(2,bookId);
             pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(null,pstm,conn);
        }

修改

 public void update(String account, String newPhoneNum) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstm = null;
        try {
            conn = JdbcUtils.getConnnection();
            String sql = "select * from manager where account=?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, account);
            rs = pstm.executeQuery();
            while (rs.next()) {
                String sql1 = "update manager set telephone=? where account=?";
                pstm = conn.prepareStatement(sql1);
                pstm.setString(1, newPhoneNum);
                pstm.setString(2, account);
                pstm.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(rs, pstm, conn);
        }
    }