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; }