基于JavaWeb的论坛项目

package com.igeekhome.tieba.Dao; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.igeekhome.tieba.model.Ba; import com.igeekhome.tieba.model.BaType; import com.igeekhome.tieba.model.Member; import com.igeekhome.tieba.model.User; import com.igeekhome.util.JDBCUtil; /*** * 用来封装对吧的各种数据库操作，单例模式 * @author 陈杰 * */ public class BaDao { private static BaDao instance; private BaDao() { } public static BaDao getInstance() { if(instance != null) { synchronized (instance) { return instance; } } instance = new BaDao(); return instance; } /*** * * @return 所有的吧列表 */ public List<Ba> getAllBa() { List<Ba> list = new ArrayList<Ba>(); try { String sql = "select * from tb_ba"; PreparedStatement ps = JDBCUtil.getInstance().getConnection().prepareStatement(sql); ResultSet result = ps.executeQuery(); while(result.next()) { Ba ba = new Ba(); ba.setId(result.getInt("b_id")); ba.setTypeId(result.getInt("b_type_id")); BaType type = BarTypeDao.getInstance().getBaTypeByBaId((result.getInt("b_type_id"))); ba.setType(type.getName()); ba.setName(result.getString("b_name")); ba.setDescription(result.getString("b_description")); ba.setMemberNum(result.getInt("b_member_num")); ba.setTieNum(result.getInt("b_tie_num")); ba.setOwnerId(result.getString("b_owner_user_id")); ba.setTouxiang(result.getString("b_touxiang")); list.add(ba); //System.out.println("查找成功:" + ba); } } catch (SQLException e) { System.out.println("查找失败:" + e.getMessage()); return list; } return list; } /*** * * @param baId 吧的Id * @return baId对应的Ba，若没有则返回null */ public Ba getBabyId(int baId) { Ba ba = null; try { String sql = "select * from tb_ba where b_id = ?"; PreparedStatement ps = JDBCUtil.getInstance().getConnection().prepareStatement(sql); ps.setInt(1, baId); ResultSet result = ps.executeQuery(); if(result.next()) { ba = new Ba(); ba.setId(result.getInt("b_id")); ba.setTypeId(result.getInt("b_type_id")); BaType type = BarTypeDao.getInstance().getBaTypeByBaId((result.getInt("b_type_id"))); ba.setType(type.getName()); ba.setName(result.getString("b_name")); ba.setDescription(result.getString("b_description")); ba.setMemberNum(result.getInt("b_member_num")); ba.setTieNum(result.getInt("b_tie_num")); ba.setOwnerId(result.getString("b_owner_user_id")); ba.setTouxiang(result.getString("b_touxiang")); //System.out.println("查找成功:" + ba); } } catch (SQLException e) { System.out.println("查找失败:" + e.getMessage()); return ba; } return ba; } /** * * @param ba 要新建的吧 * @return 成功则返回true,失败则返回false */ public boolean save(Ba ba) { try { String sql = "insert into tb_ba(b_type_id, b_name, b_description, b_owner_user_id, b_touxiang) values(?,?,?,?,?)"; PreparedStatement ps = JDBCUtil.getInstance().getConnection().prepareStatement(sql); ps.setInt(1, ba.getTypeId()); ps.setString(2, ba.getName()); ps.setString(3, ba.getDescription()); ps.setString(4,ba.getOwnerId()); ps.setString(5, ba.getTouxiang()); ps.execute(); return true; } catch (SQLException e) { return false; } } /** * * @param num 要得到的吧的最大数量 * @return 排名前num的吧的集合，如果num<=0，集合为null */ public List<Ba> getTopBar(int num) { if(num <= 0) return null; List<Ba> list = new ArrayList<Ba>(); try { String sql = "select * from tb_ba order by b_tie_num DESC limit 0,?"; PreparedStatement ps = JDBCUtil.getInstance().getConnection().prepareStatement(sql); ps.setInt(1, num); ResultSet result = ps.executeQuery(); while(result.next()) { Ba ba = new Ba(); ba.setId(result.getInt("b_id")); ba.setTypeId(result.getInt("b_type_id")); BaType type = BarTypeDao.getInstance().getBaTypeByBaId((result.getInt("b_type_id"))); ba.setType(type.getName()); ba.setName(result.getString("b_name")); ba.setDescription(result.getString("b_description")); ba.setMemberNum(result.getInt("b_member_num")); ba.setTieNum(result.getInt("b_tie_num")); ba.setOwnerId(result.getString("b_owner_user_id")); ba.setTouxiang(result.getString("b_touxiang")); list.add(ba); //System.out.println("查找成功:" + ba); } } catch (SQLException e) { System.out.println("查找失败:" + e.getMessage()); return list; } return list; } /** * * @param num 要查询的最大数量 * @return 最近有更新的num个吧 */ public List<Ba> getBarWhereHasNewTies(int num) { if(num <= 0) return null; List<Ba> list = new ArrayList<Ba>(); try { String sql = "select * from tb_ba where b_id in(select b_id from tb_tie order by t_time DESC) limit 0,?"; PreparedStatement ps = JDBCUtil.getInstance().getConnection().prepareStatement(sql); ps.setInt(1, num); ResultSet result = ps.executeQuery(); while(result.next()) { Ba ba = new Ba(); ba.setId(result.getInt("b_id")); ba.setTypeId(result.getInt("b_type_id")); BaType type = BarTypeDao.getInstance().getBaTypeByBaId((result.getInt("b_type_id"))); ba.setType(type.getName()); ba.setName(result.getString("b_name")); ba.setDescription(result.getString("b_description")); ba.setMemberNum(result.getInt("b_member_num")); ba.setTieNum(result.getInt("b_tie_num")); ba.setOwnerId(result.getString("b_owner_user_id")); ba.setTouxiang(result.getString("b_touxiang")); list.add(ba); //System.out.println("查找成功:" + ba); } } catch (SQLException e) { System.out.println("查找失败:" + e.getMessage()); return list; } return list; } /** * 通过吧的id删除吧 * @param baId 吧的id * @return 成功与否 */ public boolean deleteBaById(int baId) { try { String sql = "delete from tb_ba where b_id = ?"; PreparedStatement ps = JDBCUtil.getInstance().getConnection().prepareStatement(sql); ps.setInt(1, baId); ps.execute(); return true; } catch (SQLException e) { return false; } } /** * 删除吧 * @param ba * @return */ public boolean delete(Ba ba) { return deleteBaById(ba.getId()); } /** * 更新吧 * @param ba * @return 成功与否 */ public boolean update(Ba ba) { try { String sql = "update tb_ba set b_type_id = ? , b_name = ? , b_description = ?, b_owner_user_id = ?, b_touxiang = ? where b_id = ?"; PreparedStatement ps = JDBCUtil.getInstance().getConnection().prepareStatement(sql); ps.setInt(1, ba.getTypeId()); ps.setString(2, ba.getName()); ps.setString(3, ba.getDescription()); ps.setString(4,ba.getOwnerId()); ps.setString(5, ba.getTouxiang()); ps.setInt(6,ba.getId()); ps.execute(); return true; } catch (SQLException e) { return false; } } /** * 得到用户关注的所有吧 * @param userId * @return */ public List<Ba> getAllBaWhoCaresById(String userId) { List<Ba> list = new ArrayList<Ba>(); try { List<Member> memberList = new ArrayList<Member>(); String sql = "select * from tb_member where m_user_id = ?"; PreparedStatement ps = JDBCUtil.getInstance().getConnection().prepareStatement(sql); ps.setString(1, userId); ResultSet result = ps.executeQuery(); while(result.next()) { Member member = new Member(); member.setM_ba_id(result.getInt("m_ba_id")); member.setM_user_id(result.getString("m_user_id")); member.setM_days(result.getInt("m_days")); memberList.add(member); } for(M