E家园-个人网站系统与在线通讯录(一)

package operation; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import common.DBConnection; import entity.Book; public class BookBo { private Connection conn; private PreparedStatement pst; private ResultSet rs; /** * 根据用户ID查询全部资料 * @param userId * @return list */ public List selectAllBook(int userId) { List list = new ArrayList(); String sql = "select * from Book where userId=?"; try { conn = DBConnection.getConn(); pst = conn.prepareStatement(sql); pst.setInt(1, userId); rs = pst.executeQuery(); while (rs.next()) { Book book = new Book(); book.setId(rs.getInt("Id")); book.setName(rs.getString("name")); book.setSex(rs.getString("sex")); book.setAddress(rs.getString("address")); book.setCompany(rs.getString("company")); book.setPhone(rs.getString("phone")); book.setMobilePhone(rs.getString("mobilePhone")); list.add(book); } conn.close(); } catch (ClassNotFoundException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } return list; } public List selectAllBooks(int userId) { List list = new ArrayList(); String sql = "select * from Book where id=?"; try { conn = DBConnection.getConn(); pst = conn.prepareStatement(sql); pst.setInt(1, userId); rs = pst.executeQuery(); while (rs.next()) { Book book = new Book(); book.setId(rs.getInt("Id")); book.setName(rs.getString("name")); book.setSex(rs.getString("sex")); book.setAddress(rs.getString("address")); book.setCompany(rs.getString("company")); book.setPhone(rs.getString("phone")); book.setMobilePhone(rs.getString("mobilePhone")); list.add(book); } conn.close(); } catch (ClassNotFoundException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } return list; } /** * 删除通讯录信息 * @param id * @return boolean */ public boolean deleteBookById(String id) { boolean res = false; String sql = "delete from Book where id=?"; try { conn = DBConnection.getConn(); pst = conn.prepareStatement(sql); pst.setString(1, id); int x = pst.executeUpdate(); if (x > 0) { res = true; } conn.close(); } catch (ClassNotFoundException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } return res; } /** * 批量删除 * 附加 * @param userID * @return */ public boolean deleteBookById(int[] userID) { boolean result = false; String userIDstr = ""; String userId = ""; try { if (userID != null) { for (int i = 0; i < userID.length; i++) { userIDstr += userID[i] + ","; } // 获得所有字段 userId = userIDstr.substring(0, userIDstr.length() - 1); } String sql = "delete from Book where id in(" + userId + ")"; conn = DBConnection.getConn(); pst = conn.prepareStatement(sql); int x = pst.executeUpdate(); if (x > 0) { result = true; } conn.close(); } catch (Exception e) { e.printStackTrace(); } return result; } /** * 添加通讯录，userId为用户ID，数据库中为外键 * @param book * @param userId * @return 执行受影响行数 */ public int insertBook(Book book, int userId) { int res = 0; String sql = "insert into book values(?,?,?,?,?,?,?,?,?,?)"; try { conn = DBConnection.getConn(); pst = conn.prepareStatement(sql); pst.setString(1, book.getName()); pst.setString(2, book.getSex()); pst.setString(3, book.getPhone()); pst.setString(4, book.getAddress()); pst.setString(5, book.getMobilePhone()); pst.setString(6, book.getCompany()); pst.setString(7, book.getComPhone()); pst.setString(8, book.getComAddress()); pst.setInt(9, book.getRelation()); pst.setInt(10, userId); res = pst.executeUpdate(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } } return res; } /** * 根据选择类型和用户ID来分类查询 * @param relation * @param userId * @return */ public List selectBookByRelation(int relation, int userId,int page,int pageSize) { List list = new ArrayList(); //String sql = "select * from book where userId=? and relation=?"; int pageNum=1;//默认页面从1开始 if(page>=1){ pageNum=pageSize*(page-1);//计算需要过滤的信息数据 } String sql="select top(?) * from book where id not in(select top(?) id from book)and userId=? and relation=?"; try { conn = DBConnection.getConn(); pst = conn.prepareStatement(sql); pst.setInt(1, pageSize); pst.setInt(2, pageNum); pst.setInt(3, userId); pst.setInt(4, relation); rs = pst.executeQuery(); while (rs.next()) { Book book = new Book(); book.setId(rs.getInt(1)); book.setName(rs.getString(2)); book.setSex(rs.getString(3)); book.setPhone(rs.getString(4)); book.setAddress(rs.getString(5)); book.setMobilePhone(rs.getString(6)); book.setCompany(rs.getString(7)); book.setComPhone(rs.getString(8)); book.setComAddress(rs.getString(9)); book.setRelation(rs.getInt(10)); book.setUseId(rs.getInt(11)); list.add(book); } conn.close(); } catch (Exception e) { e.printStackTrace(); } return list; } /** * 修改通讯录用户，id为book用户ID，数据库中为外键 * @param book * @param userId * @return 执行受影响行数 */ public int updateBook(Book book, int id) { int res = 0; String sql = "update book set name=?,sex=?,phone=?,address=?,relation=? where id=?"; try { conn = DBConnection.getConn(); pst = conn.prepareStatement(sql); pst.setString(1, book.getName()); pst.setString(2, book.getSex()); pst.setString(3, book.getPhone()); pst.setString(4, book.getAddress()); pst.setInt(5, book.getRelation()); pst.setInt(6, id); res = pst.executeUpdate(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } } return res; } /** * 更新数据 * @param name * @param sex * @param phone * @param mobilePhone * @param id * @return */ public boolean updateBook(String name ,String sex,String phone ,String mobilePhone,int id) { boolean res = false; int x =0; String sql = "update book set name=?,sex=?,phone=?,mobilePhone=? where id=?"; try { conn = DBConnection.getConn(); pst = conn.prepareStatement(sql); pst.setString(1, name); pst.setString(2, sex); pst.setString(3, phone); pst.setString(4, mobilePhone); pst.setInt(5,id); x = pst.executeUpdate(); if(x>0) { res=true; } System.out.print(x); System.out.print(res); }catch(Exception e) { e.printStackTrace(); } finally{ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return res; } /** * 根据页面传入的三个参数分页 * @param page * @param pageSize * @return