?? publishdaoimpl.java
字號(hào):
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();
//得到語(yǔ)句對(duì)象
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();
//得到語(yǔ)句對(duì)象
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();
//得到語(yǔ)句對(duì)象
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();
//得到語(yǔ)句對(duì)象
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);
}
}
/**
* 結(jié)貼
* @param po
* @throws Exception
*/
public void updateFinish(PublishPO po) throws SQLException {
String sql = "update publish set finish = ? where id = ?";
try {
con = DBUtil.getDBUtil().getConnection();
//得到語(yǔ)句對(duì)象
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();
//得到語(yǔ)句對(duì)象
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);
}
}
/**
* 查詢某個(gè)用戶發(fā)表的主題帖
* @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;
}
/**
* 查詢某個(gè)版塊的主題帖
* @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;
}
/**
* 查詢某個(gè)版塊的主題帖
* @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;
}
/**
* 查詢具體的某個(gè)帖子
* @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;
}
/**
* 按主題查詢相關(guān)主題
* @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);
}
}
?? 快捷鍵說(shuō)明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -