基于servlet和JSP的Java技术实现增删改查

package Service_nyd; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import DBUtil_nyd.DBUtil; import Model_nyd.Book; public class BookService { /* * 查询 */ public static List<Book> getbookies() { List<Book> bookies = new ArrayList<Book>(); Connection con = null; Statement statement = null; ResultSet set = null; try { // 获取连接 connection con = DBUtil.getConnection(); String sql = "SELECT * FROM book "; statement = con.createStatement(); set = statement.executeQuery(sql); while (set.next()) { Book book = new Book(); book.setBookID(set.getInt("bookID")); book.setBookName(set.getString("bookName")); book.setBookWriter(set.getString("bookWriter")); book.setBookPrice(set.getString("bookPrice")); bookies.add(book); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(set, statement, con); } return bookies; } /* * 增加 */ public static void insertBook(Book book) { Connection con = null; PreparedStatement statement = null; ResultSet set = null; try { // 获取连接 connection con = DBUtil.getConnection(); String sql = "INSERT INTO book" + "(bookName,bookWriter,bookPrice) values(?,?,?)"; statement = con.prepareStatement(sql); statement.setString(1, book.getBookName()); statement.setString(2, book.getBookWriter()); statement.setString(3, book.getBookPrice()); int update = statement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(set, statement, con); } } /* * 更新 */ public static Book getBook(int bookID) { Book book = null; Connection con = null; PreparedStatement statement = null; ResultSet set = null; try { // 获取连接 connection con = DBUtil.getConnection(); String sql = "SELECT * FROM book WHERE bookID = ?"; statement = con.prepareStatement(sql); statement.setInt(1, bookID); set = statement.executeQuery(); if (set.next()) { book = new Book(); book.setBookID(set.getInt("bookID")); book.setBookName(set.getString("bookName")); book.setBookWriter(set.getString("bookWriter")); book.setBookPrice(set.getString("bookPrice")); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(set, statement, con); } return book; } /** * 根据id获取单个city * * @param cityId * @return */ public static void updateBook(Book book) { Connection con = null; PreparedStatement statement = null; ResultSet set = null; try { // 获取连接 connection con = DBUtil.getConnection(); String sql = "UPDATE book SET bookName=?,bookWriter=?,bookPrice=? WHERE bookID=?"; statement = con.prepareStatement(sql); statement.setString(1, book.getBookName()); statement.setString(2, book.getBookWriter()); statement.setString(3, book.getBookPrice()); statement.setInt(4, book.getBookID()); int update = statement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(set, statement, con); } } /* * 删除 */ public static void deleteBook(int[] ids) { // 判断ids 是否为空，如果为空就返回 if (ids == null) { System.out.println("1"); return; } // 用PreparedStatement 批量执行 删除操作 String sql = "DELETE FROM book WHERE bookID=?"; Connection con = null; PreparedStatement ps = null; try { con = DBUtil.getConnection(); // 创建语句对象 ps = con.prepareStatement(sql); for (int bookID : ids) { ps.setInt(1, bookID); ps.addBatch(); } // 批量执行SQL语句 int[] executeBatch = ps.executeBatch(); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(null, ps, con); } } }