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;
}
/**
* 查询申成知道提问
*
* @param question
* @return
*/
public static List<CloudKnowAsk> queryCloudKnowAsksByQuestion(String question) throws Exception {
List<CloudKnowAsk> list = new ArrayList<CloudKnowAsk>();
String sql =
"SELECT id,user_id,question,description,type,urgent,state,"
+ "create_date,create_time,create_ip,update_date,update_time,update_ip FROM"
+ " cloud_know_ask WHERE question like '%"
+ question
+ "%' ORDER BY id DESC"; // AND state=" + STATE_NORMAL +" 前面有or这里用and好像无效
Connection c = DB.getConn();
Statement stmt = DB.createStatement(c);
ResultSet rs = DB.executeQuery(c, stmt, sql);
try {
if (rs == null) {
throw new RuntimeException("数据库操作出错，请重试！");
}
while (rs.next()) {
int id = rs.getInt("id");
int userId = rs.getInt("user_id");
String newQuestion = rs.getString("question");
String description = rs.getString("description");
String type = rs.getString("type");
int urgent = rs.getInt("urgent");
int state = rs.getInt("state");
String createDate = rs.getString("create_date");
String createTime = rs.getString("create_time");
String createIp = rs.getString("create_ip");
String updateDate = rs.getString("update_date");
String updateTime = rs.getString("update_time");
String updateIp = rs.getString("update_ip");
/** SQL 语句总 AND state=" + STATE_NORMAL +" 前面有or这里用and好像无效 */
if (state != STATE_NORMAL) {
continue;
}
CloudKnowAsk cloudKnowAsk =
new CloudKnowAsk(
id,
userId,
newQuestion,
description,
type,
urgent,
state,
createDate,
createTime,
createIp,
updateDate,
updateTime,
updateIp);
list.add(cloudKnowAsk);
}
return list;
} finally {
DB.close(rs);
DB.close(stmt);
DB.close(c);
}
}
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;
}
/**
* 根据id查申成知道提问
*
* @param id
* @return
* @throws Exception
*/
public static CloudKnowAsk getCloudKnowAskById(int id) throws Exception {
CloudKnowAsk cloudKnowAsk = null;
String sql =
"SELECT id,user_id,question,description,type,urgent,state,create_date,create_time,"
+ "create_ip,update_date,update_time,update_ip FROM cloud_know_ask WHERE id="
+ id;
Connection c = DB.getConn();
Statement stmt = DB.createStatement(c);
ResultSet rs = DB.executeQuery(c, stmt, sql);
try {
if (rs == null) {
throw new RuntimeException("数据库操作出错，请重试！");
}
while (rs.next()) {
int userId = rs.getInt("user_id");
String question = rs.getString("question");
String description = rs.getString("description");
String type = rs.getString("type");
int urgent = rs.getInt("urgent");
int state = rs.getInt("state");
String createDate = rs.getString("create_date");
String createTime = rs.getString("create_time");
String createIp = rs.getString("create_ip");
String updateDate = rs.getString("update_date");
String updateTime = rs.getString("update_time");
String updateIp = rs.getString("update_ip");
cloudKnowAsk =
new CloudKnowAsk(
id,
userId,
question,
description,
type,
urgent,
state,
createDate,
createTime,
createIp,
updateDate,
updateTime,
updateIp);
}
return cloudKnowAsk;
} finally {
DB.close(rs);
DB.close(stmt);
DB.close(c);
}
}
public int queryArticleSum() {
String sql = "select count(*) from tb_article";
int sum = 0;
ResultSet rs = connection.executeQuery(sql);
if (rs != null) {
try {
rs.next();
sum = rs.getInt(1);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return sum;
}
/**
* 根据用户id查最大的申成知道提问Id
*
* @param userId
* @return
* @throws Exception
*/
public static int getMaxIdByUserId(int userId) throws Exception {
String sql = "SELECT MAX(id) max_id FROM cloud_know_ask WHERE user_id=" + userId;
Connection c = DB.getConn();
Statement stmt = DB.createStatement(c);
ResultSet rs = DB.executeQuery(c, stmt, sql);
try {
if (rs == null) {
throw new RuntimeException("数据库操作出错，请重试！");
}
while (rs.next()) {
return rs.getInt("max_id");
}
return 0;
} finally {
DB.close(rs);
DB.close(stmt);
DB.close(c);
}
}
public HashMap<Integer, String> queryArticleStyle() {
String sql = "select * from tb_articleType";
HashMap<Integer, String> hm = new HashMap<Integer, String>();
int id = 0;
String type = "";
ResultSet rs = connection.executeQuery(sql);
if (rs != null) {
try {
rs.next();
id = rs.getInt(1);
type = rs.getString(2);
hm.put(id, type);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return hm;
}
