public ArticleBean queryArticleSingle(int id) {
String sql = "select * from tb_article where id ='" + id + "'";
ResultSet rs = connection.executeQuery(sql);
try {
while (rs.next()) {
articleBean = new ArticleBean();
articleBean.setId(rs.getInt(1));
articleBean.setTypeId(rs.getInt(2));
articleBean.setTitle(rs.getString(3));
articleBean.setContent(rs.getString(4));
articleBean.setSdTime(rs.getString(5));
articleBean.setCreate(rs.getString(6));
articleBean.setInfo(rs.getString(7));
articleBean.setCount(rs.getInt(8));
/* 查询tb_review数据表统计当前文章的评论数 */
sql =
"select count(id) from tb_review where review_article_articleId=" + articleBean.getId();
ResultSet rsr = connection.executeQuery(sql);
if (rsr != null) {
rsr.next();
articleBean.setReview(rsr.getInt(1));
rsr.close();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return articleBean;
}
public List queryArticle(int typeId, String type) {
List articleList = new ArrayList();
String sql = "";
if (typeId <= 0) { // 不按文章类别的查询，查询前3条记录
sql = "select * from tb_article order by article_sdTime DESC";
} else // 按文件类别查询
if (type == null || type.equals("") || !type.equals("all"))
// 生成查询某类别下的前5篇文章的SQL语句
sql =
"select top 5 * from tb_article where article_typeID ="
+ typeId
+ " order by article_sdTime DESC";
else
// 查询某类别下的所有文章的SQL语句
sql =
"select * from tb_article where article_typeID="
+ typeId
+ "order by article_sdTime DESC";
ResultSet rs = connection.executeQuery(sql);
if (rs != null) {
try {
while (rs.next()) {
articleBean = new ArticleBean();
articleBean.setId(rs.getInt(1));
articleBean.setTypeId(rs.getInt(2));
articleBean.setTitle(rs.getString(3));
articleBean.setContent(rs.getString(4));
articleBean.setSdTime(rs.getString(5));
articleBean.setCreate(rs.getString(6));
articleBean.setInfo(rs.getString(7));
articleBean.setCount(rs.getInt(8));
// 查询tb_article数据表统计当前文章的评论数
sql = "select count(id) from tb_review where review_articleId =" + articleBean.getId();
ResultSet rsr = connection.executeQuery(sql);
if (rsr != null) {
rsr.next();
articleBean.setReview(rsr.getInt(1));
rsr.close();
}
articleList.add(articleBean);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return articleList;
}
public List queryArticleFromTo(int begin, int count) {
List articleList = new ArrayList();
String sql = "";
if (begin == 0 && count == 0) {
sql = "select * from tb_article order by id desc";
} else
sql =
"select * from tb_article order by id desc limit " + (begin - 1) * 10 + "," + count + "";
ResultSet rs = connection.executeQuery(sql);
if (rs != null) {
try {
while (rs.next()) {
articleBean = new ArticleBean();
articleBean.setId(rs.getInt(1));
articleBean.setTypeId(rs.getInt(2));
articleBean.setTitle(rs.getString(3));
articleBean.setContent(rs.getString(4));
articleBean.setSdTime(rs.getString(5));
articleBean.setCreate(rs.getString(6));
articleBean.setInfo(rs.getString(7));
articleBean.setCount(rs.getInt(8));
// 查询tb_article数据表统计当前文章的评论数
sql = "select count(id) from tb_review where review_articleId =" + articleBean.getId();
ResultSet rsr = connection.executeQuery(sql);
if (rsr != null) {
rsr.next();
articleBean.setReview(rsr.getInt(1));
rsr.close();
}
articleList.add(articleBean);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return articleList;
}