📄 publishdaoimpl.java

📁 一个bbs论坛系统
💻 JAVA
字号:
package com.lovo.dao;

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 com.lovo.factory.DAOFactory;
import com.lovo.po.BlockPO;
import com.lovo.po.PublishPO;
import com.lovo.po.ReplyPO;
import com.lovo.po.UserPO;
import com.lovo.util.DBUtil;
import com.lovo.util.Page;

public class PublishDAOImpl implements PublishDAO {

	private Connection con = null;

	private PreparedStatement pre = null;

	private ResultSet rs = null;

	/**
	 * 添加主题帖
	 * @param po
	 * @throws SQLException
	 */
	public void insert(PublishPO po) throws SQLException {
		String sql = "insert into publish(title, bounty, content, user_id, block_id, publishdate,id)"
				+ " values(?, ?, ?, ?, ?, date_format(now(),'%Y-%m-%d %H:%i:%s') , ?)";

		try {
			con = DBUtil.getDBUtil().getConnection();
			
			//得到语句对象
			pre = con.prepareStatement(sql);
			pre.setString(1, po.getTitle());
			pre.setInt(2, po.getBounty());
			pre.setString(3, po.getContent());
			pre.setInt(4, po.getPublishMan().getId());
			pre.setInt(5, po.getBlock().getId());
			pre.setInt(6, po.getId());
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

	}

	/**
	 * 删除主题帖
	 * @param id
	 * @throws SQLException
	 */
	public void delete(int id) throws SQLException {
		String sql = "delete from publish where id = ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			//得到语句对象
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}

	/**
	 * 修改主题贴为推荐贴
	 * @param po
	 * @throws Exception
	 */
	public void updateRecmmend(PublishPO po) throws SQLException {
		String sql = "update publish set recommend = ?, recomendate = date_format(now(),'%Y-%m-%d %H:%i:%s') where id = ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			//得到语句对象
			pre = con.prepareStatement(sql);
			pre.setInt(1, po.getRecommend());
			pre.setInt(2, po.getId());

			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}
	
	/**
	 * 修改主题贴为置顶贴
	 * @param po
	 * @throws Exception
	 */
	public void updateStick(PublishPO po) throws SQLException {
		String sql = "update publish set stick = ?, stickdate = date_format(now(),'%Y-%m-%d %H:%i:%s') where id = ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			//得到语句对象
			pre = con.prepareStatement(sql);
			pre.setInt(1, po.getStick());
			pre.setInt(2, po.getId());

			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}
	
	/**
	 * 结贴
	 * @param po
	 * @throws Exception
	 */
	public void updateFinish(PublishPO po) throws SQLException {
		String sql = "update publish set finish = ? where id = ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			//得到语句对象
			pre = con.prepareStatement(sql);
			pre.setInt(1, po.getFinish());
			pre.setInt(2, po.getId());

			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}
	
	/**
	 * 修改主题贴为精华贴
	 * @param po
	 * @throws Exception
	 */
	public void updateDigest(PublishPO po) throws SQLException {
		String sql = "update publish set digest = ?, stickdate = date_format(now(),'%Y-%m-%d %H:%i:%s') where id = ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			//得到语句对象
			pre = con.prepareStatement(sql);
			pre.setInt(1, po.getDigest());
			pre.setInt(2, po.getId());

			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}
	
	/**
	 * 查询某个用户发表的主题帖
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<PublishPO> queryByUserId(int userId) throws SQLException {
		List<PublishPO> list = new ArrayList<PublishPO>();
		String sql = "select * from publish where user_id =? ";
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(sql);
			pre.setInt(1, userId);
			rs = pre.executeQuery();
			while (rs.next()) {
				PublishPO publishPO = new PublishPO();
				this.initPublish(publishPO, rs);
				list.add(publishPO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return list;
	}

	/**
	 * 查询某个版块的主题帖
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public List<PublishPO> queryByBlockId(int id, Page page) throws SQLException {
		List<PublishPO> list = new ArrayList<PublishPO>();
		String sql = "select * from publish where block_id = ? order by publishdate desc limit ?, ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			pre.setInt(2, (page.getCurrPage() - 1) * page.getPageRows());
			pre.setInt(3, page.getPageRows());
			rs = pre.executeQuery();
			while (rs.next()) {
				PublishPO publishPO = new PublishPO();
				this.initPublish(publishPO, rs);
				list.add(publishPO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return list;
	}
	
	public List<PublishPO> queryByContent(String content) throws SQLException {
		List<PublishPO> list = new ArrayList<PublishPO>();
		String sql = "select * from publish where  content like ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			
			pre = con.prepareStatement(sql);
			pre.setString(1, "%" + content + "%");
			rs = pre.executeQuery();
			while (rs.next()) {
				PublishPO publishPO = new PublishPO();
				this.initPublish(publishPO, rs);
				list.add(publishPO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return list;
	}
	
	
	
	/**
	 * 查询某个版块的主题帖
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public int queryPublishMaxRowsInBlock(int id) throws SQLException {
		int maxRows = 0;
		String sql = "select count(id) maxRows from publish where block_id = ? order by publishdate";
		try {
			con = DBUtil.getDBUtil().getConnection();
			
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			rs = pre.executeQuery();
			while (rs.next()) {
				maxRows = rs.getInt("maxRows");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return maxRows;
	}
	
	/**
	 * 最新的主题帖
	 * @return
	 * @throws SQLException
	 */
	public List<PublishPO> queryByPublishDate() throws SQLException {
		List<PublishPO> list = new ArrayList<PublishPO>();
		String sql = "select  * from publish order by publishdate";
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			while (rs.next()) {
				PublishPO publishPO = new PublishPO();
				this.initPublish(publishPO, rs);
				list.add(publishPO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return list;
	}
	
	public PublishPO queryByRecomendate() throws SQLException {
		List<PublishPO> list = new ArrayList<PublishPO>();
		String sql = "select * from publish where recommend = 1 order by recomendate limit 0, 1";
		PublishPO publishPO = null;
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			while (rs.next()) {
				publishPO = new PublishPO();
				this.initPublish(publishPO, rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return publishPO;
	}
	
	public PublishPO queryByDigestdate() throws SQLException {
		List<PublishPO> list = new ArrayList<PublishPO>();
		String sql = "select * from publish where digest = 1 order by digestdate limit 0, 1";
		PublishPO publishPO = null;
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			while (rs.next()) {
				publishPO = new PublishPO();
				this.initPublish(publishPO, rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return publishPO;
	}

	/**
	 * 查询具体的某个帖子
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public PublishPO queryById(int id) throws SQLException {
		String sql = "select * from publish where id = ?";
		PublishPO publishPO = null;
		try {
			con = DBUtil.getDBUtil().getConnection();
			
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			rs = pre.executeQuery();
			if (rs.next()) {
				publishPO = new PublishPO();
				this.initPublish(publishPO, rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return publishPO;
	}

	/**
	 * 按主题查询相关主题
	 * @param title
	 * @return
	 * @throws SQLException
	 */
	public List<PublishPO> queryByTitle(String title) throws SQLException {
		List<PublishPO> list = new ArrayList<PublishPO>();
		String sql = "select * from publish where title like ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			
			pre = con.prepareStatement(sql);
			pre.setString(1, "%" + title + "%");
			rs = pre.executeQuery();
			while (rs.next()) {
				PublishPO publishPO = new PublishPO();
				this.initPublish(publishPO, rs);
				list.add(publishPO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		System.out.println("list.size = " + list.size());
		return list;
	}
	
	public void initPublish(PublishPO publishPO, ResultSet rs) throws SQLException {
		publishPO.setId(rs.getInt("id"));
		publishPO.setBounty(rs.getInt("bounty"));
		publishPO.setRecommend(rs.getInt("recommend"));
		publishPO.setDigest(rs.getInt("digest"));
		publishPO.setStick(rs.getInt("stick"));
		publishPO.setFinish(rs.getInt("finish"));
		publishPO.setTitle(rs.getString("title"));
		publishPO.setContent(rs.getString("content"));
		publishPO.setPublishDate(rs.getDate("publishDate").toString());
		UserPO publishMan = new UserPO();
		publishMan.setId(rs.getInt("user_id"));
		BlockPO block = new BlockPO();
		block.setId(rs.getInt("block_id"));
		publishPO.setBlock(block);
		publishPO.setPublishMan(publishMan);
	}
	
}
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -