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

云朵里有星星

已于 2023-04-28 11:25:28 修改

阅读量2k

点赞数 2

文章标签： mysql java tomcat sqlserver

于 2022-04-03 12:52:05 首次发布

本文链接：https://blog.csdn.net/qq_63897791/article/details/123935777

版权

本文介绍了如何使用Java进行数据库的基本操作，包括根据书名和作者查找书籍，向借阅表中添加记录，通过用户ID和书ID删除借阅信息，以及更新管理员的电话号码。这些操作都利用了PreparedStatement进行SQL语句的预编译，提高了代码的安全性和效率。

摘要由CSDN通过智能技术生成

查找

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