package com.tanlan.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.tanlan.model.Blog;
import com.tanlan.util.PageBean;
public class BlogDAO {
public int addBlog(Blog blog) {
int num = 0;
if (blog == null) {
return num;
}
Connection connection = DBConnectionPool.getConnection();
PreparedStatement stmt = null;
String sql = "insert into TBlog values(?,?,?,?,?,?)";
try {
stmt = connection.prepareStatement(sql);
stmt.setString(1, blog.getTitle());
stmt.setString(2, blog.getContent());
stmt.setInt(3, blog.getTypeId());
stmt.setInt(4, blog.getUserId());
stmt.setString(5, blog.getBlogTime());
stmt.setInt(6, blog.getClickCount());
num = stmt.executeUpdate();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
DBConnectionPool.closeStatement(stmt);
DBConnectionPool.closeConnection(connection);
}
return num;
}
public int deleteBlogsByIds(String[] ids) {
int num = 0;
if (ids == null) {
return num;
}
Connection connection = DBConnectionPool.getConnection();
Statement stmt = null;
String sql = "delete from TBlog where id=";
try {
stmt = connection.createStatement();
for (String id : ids) {
stmt.addBatch(sql + id);
}
num = stmt.executeBatch().length;
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
DBConnectionPool.closeStatement(stmt);
DBConnectionPool.closeConnection(connection);
}
return num;
}
public List<Blog> getBlogsByCondition(Blog blog, int topnum) {
List<Blog> blogs = null;
if (blog == null) {
return blogs;
}
Connection connection = DBConnectionPool.getConnection();
Statement stmt = null;
ResultSet rs = null;
String sql = "select top " + topnum
+ " * from VBlogUserType where 1=1 ";
if (blog.getId() != 0) {
sql += " and id=" + blog.getId();
}
if (blog.getUserId() != 0) {
sql += " and userid=" + blog.getUserId();
}
if (blog.getTypeId() != 0) {
sql += " and typeid=" + blog.getTypeId();
}
sql += " order by blogtime desc";
try {
blogs = new ArrayList<Blog>();
stmt = connection.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
Blog aBlog = new Blog();
aBlog.setId(rs.getInt(1));
aBlog.setTitle(rs.getString(2));
aBlog.setContent(rs.getString(3));
aBlog.setTypeId(rs.getInt(4));
aBlog.setTypeName(rs.getString(5));
aBlog.setUserId(rs.getInt(6));
aBlog.setUserName(rs.getString(7));
aBlog.setNickName(rs.getString(8));
aBlog.setBlogTime(rs.getString(9));
blogs.add(aBlog);
}
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
DBConnectionPool.closeStatement(stmt);
DBConnectionPool.closeConnection(connection);
}
return blogs;
}
public PageBean getPageBlogsByCondition(int page, int perpage, Blog blog) {
PageBean pageBean = new PageBean();
List<Blog> blogs = new ArrayList<Blog>();
if (blog == null) {
return pageBean;
}
int totalCount = getTotalCountByUserId(blog.getUserId()); // �õ��ܼ�¼��
int totalPage = totalCount / perpage; // �õ���ҳ��
if (totalCount % perpage > 0) {
totalPage++;
}
if (page < 1) {
page = 1;
}
if (totalPage != 0 && page > totalPage) {
page = totalPage;
}
StringBuffer condition = new StringBuffer();
if (blog.getUserId() != 0) {
condition.append(" and userid=").append(blog.getUserId());
}
if (blog.getTypeId() != 0) {
condition.append(" and typeid=").append(blog.getTypeId());
}
StringBuffer sql = new StringBuffer("select top ");
sql.append(perpage);
sql.append(" * from VBlogUserType where id not in(select top ");
sql.append(((page - 1) * perpage));
sql.append(" id from VBlogUserType where 1=1 ");
sql.append(condition);
sql.append(" order by id desc)");
sql.append(condition);
sql.append(" order by id desc");
Connection connection = DBConnectionPool.getConnection();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = connection.createStatement();
rs = stmt.executeQuery(sql.toString());
while (rs.next()) {
Blog aBlog = new Blog();
aBlog.setId(rs.getInt(1));
aBlog.setTitle(rs.getString(2));
aBlog.setContent(rs.getString(3));
aBlog.setTypeId(rs.getInt(4));
aBlog.setTypeName(rs.getString(5));
aBlog.setUserId(rs.getInt(6));
aBlog.setUserName(rs.getString(7));
aBlog.setBlogTime(rs.getString(8));
blogs.add(aBlog);
}
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
DBConnectionPool.closeResultSet(rs);
DBConnectionPool.closeStatement(stmt);
DBConnectionPool.closeConnection(connection);
}
pageBean.setPage(page);
pageBean.setTotalPage(totalPage);
pageBean.setData(blogs);
pageBean.setTotalCount(totalCount);
if (totalCount == 0) {
return pageBean;
}
if (perpage < 0) {
perpage = 0;
}
pageBean.setPerpage(perpage);
pageBean.setRowCount(blogs.size());
return pageBean;
}
private int getTotalCountByUserId(int userId) {
int totalCount = 0;
Connection connection = DBConnectionPool.getConnection();
Statement stmt = null;
ResultSet rs = null;
String sql = "";
if (userId == 0) {
sql = "select count(*) from TBlog";
} else {
sql = "select count(*) from TBlog where userid=" + userId;
}
try {
stmt = connection.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()) {
totalCount = rs.getInt(1);
}
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
DBConnectionPool.closeResultSet(rs);
DBConnectionPool.closeStatement(stmt);
DBConnectionPool.closeConnection(connection);
}
return totalCount;
}
public int editBlog(Blog blog) {
int num = 0;
if (blog == null) {
return num;
}
Connection connection = DBConnectionPool.getConnection();
PreparedStatement stmt = null;
String sql = "update TBlog set title=?, content=?, typeid=? where id=?";
try {
stmt = connection.prepareStatement(sql);
stmt.setString(1, blog.getTitle());
stmt.setString(2, blog.getContent());
stmt.setInt(3, blog.getTypeId());
stmt.setInt(4, blog.getId());
num = stmt.executeUpdate();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
DBConnectionPool.closeStatement(stmt);
DBConnectionPool.closeConnection(connection);
}
return num;
}
public int addBlogClickCount(int id, int addClickCount) {
int num = 0;
if (id == 0) {
return num;
}
Connection connection = DBConnectionPool.getConnection();
PreparedStatement stmt = null;
String sql = "update TBlog set clickcount=clickcount+" + addClickCount
+ " where id=?";
try {
stmt = connection.prepareStatement(sql);
stmt.setInt(1, id);
num = stmt.executeUpdate();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
DBConnectionPool.closeStatement(stmt);
DBConnectionPool.closeConnection(connection);
}
return num;
}
/**
* 查询热门日志
*
* @return
*/
public List<Blog> getHotBlog(int topnum) {
List<Blog> blogs = null;
Connection connection = DBConnectionPool.getConnection();
Statement stmt = null;
ResultSet rs = null;
String sql = "select top " + topnum
+ " * from VBlogUserType order by clickcount desc";
try {
blogs = new ArrayList<Blog>();
stmt = connection.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
Blog aBlog = new Blog();
aBlog.setId(rs.getInt(1));
aBlog.setTitle(rs.getString(2));
aBlog.setContent(rs.getString(3));
aBlog.setTypeId(rs.
评论0