Y2JAVA阶段项目-在线通讯录

package y2javaee.xmal1.operation; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.*; import y2javaee.xmal1.common.DBConnection; import y2javaee.xmal1.entity.Book; public class BookBo { private Connection conn;//用于保存数据库连接对象 private Statement stm;//用于执行SQL语句 private PreparedStatement ps;//用于执行SQL语句（预处理） private ResultSet rs;//用于保存查询的结果集 /** * 根据用户的ID查询用户相关的通讯录信息 * @param userId * @return 通讯录信息列表 */ public List selectAllBook(int userId) { List list = new ArrayList(); String sql = "select * from book where userId=" + userId; try { conn = DBConnection.getConnection(); stm = conn.createStatement(); rs = stm.executeQuery(sql); while (rs.next()) { Book book = new Book(); book.setId(rs.getInt("id")); book.setName(rs.getString("name")); book.setSex(rs.getString("sex")); book.setPhone(rs.getString("phone")); book.setAddress(rs.getString("address")); book.setMobilePhone(rs.getString("mobilePhone")); book.setCompany(rs.getString("company")); book.setComPhone(rs.getString("comPhone")); book.setComAddress(rs.getString("comAddress")); list.add(book); } } catch (Exception e) { e.printStackTrace(); } finally { DBConnection.closeResultSet(rs); DBConnection.closeStatement(stm); DBConnection.closeConnection(); } return list; } /** * 根据用户的ID和联系人与用户关系，查询通讯录信息 * @param userId * @param relation * @return 通讯录信息列表 */ public List selectBookByRelation(int userId,String relation) { List list = new ArrayList(); String sql = "select * from book where userId=? and relation= ? "; try { conn = DBConnection.getConnection(); ps = conn.prepareStatement(sql); ps.setInt(1, userId); ps.setString(2, relation); rs = ps.executeQuery(); while (rs.next()) { Book book = new Book(); book.setId(rs.getInt("id")); book.setName(rs.getString("name")); book.setSex(rs.getString("sex")); book.setPhone(rs.getString("phone")); book.setAddress(rs.getString("address")); book.setMobilePhone(rs.getString("mobilePhone")); book.setCompany(rs.getString("company")); book.setComPhone(rs.getString("comPhone")); book.setComAddress(rs.getString("comAddress")); list.add(book); } } catch (Exception e) { e.printStackTrace(); } finally { DBConnection.closeResultSet(rs); DBConnection.closeStatement(stm); DBConnection.closeConnection(); } return list; } /** * 根据通讯录对象，操作数据库的添加功能 * @param book * @return 影响数据行数 */ public int insertBook(Book book) { String sql = "insert into book values(?,?,?,?,?,?,?,?,?,?) "; int count = 0; try { conn = DBConnection.getConnection(); ps = conn.prepareStatement(sql); ps.setString(1, book.getName()); ps.setString(2, book.getSex()); ps.setString(3, book.getPhone()); ps.setString(4, book.getAddress()); ps.setString(5, book.getMobilePhone()); ps.setString(6, book.getCompany()); ps.setString(7, book.getComPhone()); ps.setString(8, book.getComAddress()); ps.setString(9, book.getRelation()); ps.setInt(10, book.getUserId()); count = ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { DBConnection.closeStatement(ps); DBConnection.closeConnection(); } return count; } /** * 根据通讯录ID删除相关信息 * @param id * @return 影响数据库行数 */ public int deleteBookById(String id) { String sql = "delete from book where id= ? "; int count = 0; try { conn = DBConnection.getConnection(); ps = conn.prepareStatement(sql); ps.setString(1, id); count = ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { DBConnection.closeStatement(ps); DBConnection.closeConnection(); } return count; } }