|
package com.ekexiu.portal.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.jfw.apt.annotation.Nullable;
import org.jfw.apt.orm.annotation.dao.DAO;
import org.jfw.apt.orm.annotation.dao.Dynamic;
import org.jfw.apt.orm.annotation.dao.method.Exclude;
import org.jfw.apt.orm.annotation.dao.method.From;
import org.jfw.apt.orm.annotation.dao.method.OrderBy;
import org.jfw.apt.orm.annotation.dao.method.SetSentence;
import org.jfw.apt.orm.annotation.dao.method.Where;
import org.jfw.apt.orm.annotation.dao.method.operator.DeleteWith;
import org.jfw.apt.orm.annotation.dao.method.operator.Insert;
import org.jfw.apt.orm.annotation.dao.method.operator.LimitSelect;
import org.jfw.apt.orm.annotation.dao.method.operator.PageSelect;
import org.jfw.apt.orm.annotation.dao.method.operator.SelectList;
import org.jfw.apt.orm.annotation.dao.method.operator.SelectOne;
import org.jfw.apt.orm.annotation.dao.method.operator.Update;
import org.jfw.apt.orm.annotation.dao.method.operator.UpdateWith;
import org.jfw.apt.orm.annotation.dao.param.In;
import org.jfw.apt.orm.annotation.dao.param.LessThan;
import org.jfw.apt.orm.annotation.dao.param.Like;
import org.jfw.apt.orm.annotation.dao.param.Set;
import org.jfw.util.PageQueryResult;
import com.ekexiu.portal.po.Article;
import com.ekexiu.portal.pojo.FindInfo;
@DAO
public abstract class ArticleDao {
@Insert
public abstract int insert(Connection con, Article article) throws SQLException;
@Update
@Dynamic
public abstract int update(Connection con, Article article) throws SQLException;
@UpdateWith
@From(Article.class)
@SetSentence("PAGE_VIEWS = PAGE_VIEWS + 1")
public abstract int incPageViews(Connection con,String articleId) throws SQLException;
@UpdateWith
@From(Article.class)
public abstract int updateStatus(Connection con,String articleId,@Set String status) throws SQLException;
@UpdateWith
@From(Article.class)
public abstract int updatePublishTime(Connection con,String articleId,@Set String status,@Set String publishTime) throws SQLException;
@UpdateWith
@From(Article.class)
@SetSentence("ARTICLE_AGREE = ARTICLE_AGREE + 1")
public abstract int incAgree(Connection con,String articleId) throws SQLException;
@UpdateWith
@From(Article.class)
@SetSentence("ARTICLE_AGREE = ARTICLE_AGREE - 1")
public abstract int decAgree(Connection con,String articleId) throws SQLException;
@UpdateWith
@From(Article.class)
public abstract int updateSubject(Connection con, @Set String subject, String articleId) throws SQLException;
@UpdateWith
@From(Article.class)
public abstract int updateIndustry(Connection con, @Set String industry, String articleId) throws SQLException;
@DeleteWith
@From(Article.class)
public abstract int delete(Connection con, String articleId) throws SQLException;
@DeleteWith
@From(Article.class)
public abstract int deletePro(Connection con, String professorId) throws SQLException;
@Exclude("articleContent")
@SelectList
@Where("STATUS = '2'")
public abstract List<Article> queryTiming(Connection con, @LessThan String publishTime) throws SQLException;
@Exclude("articleContent")
@SelectList
@OrderBy(" ORDER BY MODIFY_TIME DESC ")
public abstract List<Article> queryPro(Connection con, String professorId) throws SQLException;
@Exclude("articleContent")
@SelectList
@OrderBy(" ORDER BY MODIFY_TIME DESC ")
@Where("STATUS = '1'")
public abstract List<Article> queryProPublish(Connection con, String professorId) throws SQLException;
@Exclude("articleContent")
@PageSelect
@OrderBy(" ORDER BY PUBLISH_TIME DESC,SHARE_ID ASC")
@Where("STATUS = '1'")
public abstract PageQueryResult<Article> pageQueryProPublish(Connection con, String professorId,int pageSize,int pageNo) throws SQLException;
@Exclude("articleContent")
@SelectList
@OrderBy(" ORDER BY MODIFY_TIME DESC ")
@Where("STATUS IN ('0','1','2')")
public abstract List<Article> queryForDesk(Connection con, String professorId) throws SQLException;
@Exclude("articleContent")
@SelectList
@OrderBy(" ORDER BY MODIFY_TIME DESC ")
public abstract List<Article> queryOrg(Connection con, String orgId) throws SQLException;
@Exclude("articleContent")
@SelectList
@OrderBy(" ORDER BY MODIFY_TIME DESC ")
@Where("STATUS = '1'")
public abstract List<Article> queryOrgPublish(Connection con, String orgId) throws SQLException;
@Exclude("articleContent")
@PageSelect
@OrderBy(" ORDER BY PUBLISH_TIME DESC,SHARE_ID ASC")
@Where("STATUS = '1'")
public abstract PageQueryResult<Article> pageQueryOrgPublish(Connection con, String orgId,int pageSize,int pageNo) throws SQLException;
@Exclude("articleContent")
@LimitSelect
@OrderBy(" ORDER BY MODIFY_TIME DESC ")
public abstract List<Article> queryLimit(Connection con,String orgId,@LessThan String modifyTime,int rows)throws SQLException;
@SelectOne
@Nullable
public abstract Article queryOne(Connection con, String articleId) throws SQLException;
public List<Article> queryByPageViews(Connection con,String articleId,int rows)throws SQLException{
int index = 1;
boolean hasArticleId = null != articleId;
StringBuilder sql = new StringBuilder();
sql.append("SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,'',SUBJECT,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME,SHARE_ID FROM ARTICLE");
sql.append(" WHERE STATUS = '1'");
if(hasArticleId){
sql.append(" AND ARTICLE_ID != ?");
}
sql.append(" ORDER BY PAGE_VIEWS DESC");
sql.append(" LIMIT ").append(rows);
PreparedStatement ps = con.prepareStatement(sql.toString());
try{
if(hasArticleId){
ps.setString(index++, articleId);
}
ResultSet rs = ps.executeQuery();
try{
List<Article> articles = new ArrayList<Article>();
while(rs.next()){
Article article = new Article();
article.setArticleId(rs.getString(1));
String proId = rs.getString(2);
if(rs.wasNull()){
proId = null;
}
article.setProfessorId(proId);
article.setArticleTitle(rs.getString(3));
String articleContent = rs.getString(4);
if(rs.wasNull()){
articleContent = null;
}
article.setArticleContent(articleContent);
String subject = rs.getString(5);
if(rs.wasNull()){
subject = null;
}
article.setSubject(subject);
String publishTime = rs.getString(6);
if(rs.wasNull()){
publishTime = null;
}
article.setPublishTime(publishTime);
String articleImg = rs.getString(7);
if(rs.wasNull()){
articleImg = null;
}
article.setArticleImg(articleImg);
String org = rs.getString(8);
if(rs.wasNull()){
org = null;
}
article.setOrgId(org);
String articleType = rs.getString(9);
if(rs.wasNull()){
articleType = null;
}
article.setArticleType(articleType);
article.setArticleAgree(rs.getInt(10));
article.setPageViews(rs.getInt(11));
String status = rs.getString(12);
if(rs.wasNull()){
status = null;
}
article.setStatus(status);
article.setCreateTime(rs.getString(13));
article.setModifyTime(rs.getString(14));
article.setShareId(rs.getLong(15));
articles.add(article);
}
return articles;
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
}
public List<Article> queryByAuthor(Connection con,String professorId,String orgId,String articleId,int rows)throws SQLException{
int index = 1;
boolean hasProId = null != professorId;
boolean hasOrgId = null != orgId;
boolean hasArticleId = null != articleId;
StringBuilder sql = new StringBuilder();
sql.append("SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,'',SUBJECT,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME,SHARE_ID FROM ARTICLE");
sql.append(" WHERE STATUS = '1'");
if(hasProId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasArticleId){
sql.append(" AND ARTICLE_ID != ?");
}
sql.append(" ORDER BY PUBLISH_TIME DESC");
sql.append(" LIMIT ").append(rows);
PreparedStatement ps = con.prepareStatement(sql.toString());
try{
if(hasProId){
ps.setString(index++, professorId);
}
if(hasOrgId){
ps.setString(index++, orgId);
}
if(hasArticleId){
ps.setString(index++, articleId);
}
ResultSet rs = ps.executeQuery();
try{
List<Article> articles = new ArrayList<Article>();
while(rs.next()){
Article article = new Article();
article.setArticleId(rs.getString(1));
String proId = rs.getString(2);
if(rs.wasNull()){
proId = null;
}
article.setProfessorId(proId);
article.setArticleTitle(rs.getString(3));
String articleContent = rs.getString(4);
if(rs.wasNull()){
articleContent = null;
}
article.setArticleContent(articleContent);
String subject = rs.getString(5);
if(rs.wasNull()){
subject = null;
}
article.setSubject(subject);
String publishTime = rs.getString(6);
if(rs.wasNull()){
publishTime = null;
}
article.setPublishTime(publishTime);
String articleImg = rs.getString(7);
if(rs.wasNull()){
articleImg = null;
}
article.setArticleImg(articleImg);
String org = rs.getString(8);
if(rs.wasNull()){
org = null;
}
article.setOrgId(org);
String articleType = rs.getString(9);
if(rs.wasNull()){
articleType = null;
}
article.setArticleType(articleType);
article.setArticleAgree(rs.getInt(10));
article.setPageViews(rs.getInt(11));
String status = rs.getString(12);
if(rs.wasNull()){
status = null;
}
article.setStatus(status);
article.setCreateTime(rs.getString(13));
article.setModifyTime(rs.getString(14));
article.setShareId(rs.getLong(15));
articles.add(article);
}
return articles;
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
}
public String[] queryArticleIdWithSameKeyWord(Connection con,String id,int rows)throws SQLException{
List<String> ret = new ArrayList<String>();
String sql = "select id,count(1) sort from art_key_word where kw in (select kw from art_key_word where id=?) and id <>? group by id order by sort desc limit ?";
PreparedStatement ps = con.prepareStatement(sql);
try{
ps.setString(1, id);
ps.setString(2, id);
ps.setInt(3, rows);
ResultSet rs = ps.executeQuery();
try{
while(rs.next()){
ret.add(rs.getString(1));
}
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
return ret.isEmpty()?null:ret.toArray(new String[ret.size()]);
}
@SelectList
public abstract List<Article> query(Connection con,@In String[] articleId) throws SQLException;
@Exclude("articleContent")
@LimitSelect
@Where("STATUS ='1'")
@OrderBy("ORDER BY PAGE_VIEWS DESC")
public abstract List<Article> limitQueryPublish(Connection con,@In String[] articleId,int rows) throws SQLException;
// public List<Article> queryLimit(Connection con,String[] keys,String articleId,int rows) throws SQLException{
// int index = 1;
// boolean hasKeys = null != keys;
// boolean hasArticleId = null != articleId;
// StringBuilder sql = new StringBuilder();
// sql.append("SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,ARTICLE_CONTENT,SUBJECT,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME,SHARE_ID FROM ARTICLE");
// sql.append(" WHERE STATUS = '1'");
// if(hasArticleId){
// sql.append(" AND ARTICLE_ID != ?");
// }
// if(hasKeys){
// for (int i = 0; i < keys.length; i++) {
// if(i == 0){
// sql.append(" AND (SUBJECT LIKE ?");
// }else{
// sql.append(" OR SUBJECT LIKE ?");
// }
// }
// sql.append(")");
// }
// sql.append(" ORDER BY PUBLISH_TIME DESC");
// sql.append(" LIMIT ").append(rows);
// PreparedStatement ps = con.prepareStatement(sql.toString());
// try{
// if(hasArticleId){
// ps.setString(index++, articleId);
// }
// if(hasKeys){
// for (String key : keys) {
// ps.setString(index++, "%"+key+"%");
// }
// }
// ResultSet rs = ps.executeQuery();
// try{
// List<Article> articles = new ArrayList<Article>();
// while(rs.next()){
// Article article = new Article();
// article.setArticleId(rs.getString(1));
// String proId = rs.getString(2);
// if(rs.wasNull()){
// proId = null;
// }
// article.setProfessorId(proId);
// article.setArticleTitle(rs.getString(3));
// String articleContent = rs.getString(4);
// if(rs.wasNull()){
// articleContent = null;
// }
// article.setArticleContent(articleContent);
// String subject = rs.getString(5);
// if(rs.wasNull()){
// subject = null;
// }
// article.setSubject(subject);
// String publishTime = rs.getString(6);
// if(rs.wasNull()){
// publishTime = null;
// }
// article.setPublishTime(publishTime);
// String articleImg = rs.getString(7);
// if(rs.wasNull()){
// articleImg = null;
// }
// article.setArticleImg(articleImg);
// String org = rs.getString(8);
// if(rs.wasNull()){
// org = null;
// }
// article.setOrgId(org);
// String articleType = rs.getString(9);
// if(rs.wasNull()){
// articleType = null;
// }
// article.setArticleType(articleType);
// article.setArticleAgree(rs.getInt(10));
// article.setPageViews(rs.getInt(11));
// String status = rs.getString(12);
// if(rs.wasNull()){
// status = null;
// }
// article.setStatus(status);
// article.setCreateTime(rs.getString(13));
// article.setModifyTime(rs.getString(14));
// article.setShareId(rs.getLong(15));
// articles.add(article);
// }
// return articles;
// }finally{
// try{rs.close();}catch(Exception e1){}
// }
// }finally{
// try{ps.close();}catch(Exception e2) {}
// }
// }
//
@Exclude("articleContent")
@PageSelect
@OrderBy("ORDER BY PUBLISH_TIME DESC")
@Where("STATUS = '1'")
public abstract PageQueryResult<Article> queryPageForPublish(Connection con,@Like@Nullable String articleTitle,int pageSize,int pageNo) throws SQLException;
public PageQueryResult<Article> firstPageQuery(Connection con,String key,int pageSize,int pageNo) throws SQLException{
int total = 0;
PageQueryResult<Article> queryResult = new PageQueryResult<Article>();
int index = 1;
boolean hasKey = null != key;
StringBuilder sql = new StringBuilder();
sql.append(" WHERE STATUS = '1'");
if(hasKey){
sql.append(" AND (PROFESSOR_ID IN (SELECT ID FROM PROFESSOR WHERE NAME LIKE ?)");
sql.append(" OR ORG_ID IN (SELECT ID FROM ORGANIZATION WHERE NAME LIKE ? OR FOR_SHORT LIKE ?)");
sql.append(" OR ARTICLE_TITLE LIKE ? OR SUBJECT LIKE ?)");
}
StringBuilder whereSql = sql;
sql = new StringBuilder();
sql.append("SELECT COUNT(1) FROM ARTICLE");
sql.append(whereSql);
PreparedStatement ps = con.prepareStatement(sql.toString());
try{
if(hasKey){
ps.setString(index++,key);
ps.setString(index++,key);
ps.setString(index++,key);
ps.setString(index++,key);
ps.setString(index++,key);
}
queryResult.setPageSize(pageSize);
ResultSet rs = ps.executeQuery();
try{
rs.next();
total = rs.getInt(1);
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
queryResult.setTotal(total);
if(0== total){
queryResult.setPageNo(1);
queryResult.setData(Collections.<Article>emptyList());
return queryResult;
}
index = 1;
boolean firstPage = (1 == pageNo);
if(firstPage){
queryResult.setPageNo(1);
sql = new StringBuilder();
sql.append("SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,'',SUBJECT,INDUSTRY,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME,SHARE_ID FROM ARTICLE");
sql.append(whereSql);
sql.append(" ORDER BY SORT_NUM DESC, PUBLISH_TIME DESC");
sql.append(" LIMIT ").append(pageSize);
}else{
int pageNum = total / pageSize;
if(total % pageSize != 0){
++pageNum;
}
if(pageNo > pageNum){
pageNo = pageNum;
}
queryResult.setPageNo(pageNo);
--pageNo;
int offset = (pageNo * pageSize);
sql = new StringBuilder();
sql.append("SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,'',SUBJECT,INDUSTRY,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME,SHARE_ID FROM ARTICLE");
sql.append(whereSql);
sql.append(" ORDER BY SORT_NUM DESC,PUBLISH_TIME DESC");
sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(offset);
}
ps = con.prepareStatement(sql.toString());
try{
if(hasKey){
ps.setString(index++,key);
ps.setString(index++,key);
ps.setString(index++,key);
ps.setString(index++,key);
ps.setString(index++,key);
}
ResultSet rs = ps.executeQuery();
try{
List<Article> articles = new ArrayList<Article>();
queryResult.setData(articles);
int size = 0;
while((size<pageSize) && rs.next()){
++size;
Article article = new Article();
article.setArticleId(rs.getString(1));
String professorId = rs.getString(2);
if(rs.wasNull()){
professorId = null;
}
article.setProfessorId(professorId);
article.setArticleTitle(rs.getString(3));
String articleContent = rs.getString(4);
if(rs.wasNull()){
articleContent = null;
}
article.setArticleContent(articleContent);
String subject = rs.getString(5);
if(rs.wasNull()){
subject = null;
}
article.setSubject(subject);
String industry = rs.getString(6);
if(rs.wasNull()){
industry = null;
}
article.setIndustry(industry);
String publishTime = rs.getString(7);
if(rs.wasNull()){
publishTime = null;
}
article.setPublishTime(publishTime);
String articleImg = rs.getString(8);
if(rs.wasNull()){
articleImg = null;
}
article.setArticleImg(articleImg);
String orgId = rs.getString(9);
if(rs.wasNull()){
orgId = null;
}
article.setOrgId(orgId);
String articleType = rs.getString(10);
if(rs.wasNull()){
articleType = null;
}
article.setArticleType(articleType);
article.setArticleAgree(rs.getInt(11));
article.setPageViews(rs.getInt(12));
String status = rs.getString(13);
if(rs.wasNull()){
status = null;
}
article.setStatus(status);
article.setCreateTime(rs.getString(14));
article.setModifyTime(rs.getString(15));
article.setShareId(rs.getLong(16));
articles.add(article);
}
return queryResult;
}finally{
try{rs.close();}catch(Exception e3){}
}
}finally{
try{ps.close();}catch(Exception e4){}
}
}
public PageQueryResult<Article> queryPageForSelf(Connection con,@Nullable String professorId,@Nullable String orgId,
@Nullable String articleTitle,int pageSize,int pageNo) throws SQLException{
int total = 0;
PageQueryResult<Article> queryResult = new PageQueryResult<Article>();
int index = 1;
boolean hasProfessorId = null != professorId;
boolean hasOrgId = null != orgId;
boolean hasArticleTitle = null != articleTitle;
StringBuilder sql = new StringBuilder();
sql.append("SELECT COUNT(1) FROM ARTICLE WHERE STATUS IN('0','1','2')");
if(hasProfessorId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasArticleTitle){
sql.append(" AND ARTICLE_TITLE LIKE ?");
}
PreparedStatement ps = con.prepareStatement(sql.toString());
try{
if(hasProfessorId){
ps.setString(index++, professorId);
}
if(hasOrgId){
ps.setString(index++, orgId);
}
if(hasArticleTitle){
ps.setString(index++, articleTitle);
}
queryResult.setPageSize(pageSize);
ResultSet rs = ps.executeQuery();
try{
rs.next();
total = rs.getInt(1);
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
queryResult.setTotal(total);
if(0== total){
queryResult.setPageNo(1);
queryResult.setData(Collections.<Article>emptyList());
return queryResult;
}
boolean firstPage = (1 == pageNo);
if(firstPage){
queryResult.setPageNo(1);
sql = new StringBuilder();
sql.append("(SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,'',SUBJECT,INDUSTRY,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME,SHARE_ID FROM ARTICLE WHERE STATUS IN('0','2')");
if(hasProfessorId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasArticleTitle){
sql.append(" AND ARTICLE_TITLE LIKE ?");
}
sql.append(" ORDER BY MODIFY_TIME DESC) UNION ALL (SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,ARTICLE_CONTENT,SUBJECT,INDUSTRY,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME,SHARE_ID FROM ARTICLE WHERE STATUS = '1'");
if(hasProfessorId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasArticleTitle){
sql.append(" AND ARTICLE_TITLE LIKE ?");
}
sql.append(" ORDER BY PUBLISH_TIME DESC)");
sql.append(" LIMIT ").append(pageSize);
}else{
int pageNum = total / pageSize;
if(total % pageSize != 0){
++pageNum;
}
if(pageNo > pageNum){
pageNo = pageNum;
}
queryResult.setPageNo(pageNo);
--pageNo;
int offset = (pageNo * pageSize);
sql = new StringBuilder();
sql.append("(SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,'',SUBJECT,INDUSTRY,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME,SHARE_ID FROM ARTICLE WHERE STATUS IN('0','2')");
if(hasProfessorId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasArticleTitle){
sql.append(" AND ARTICLE_TITLE LIKE ?");
}
sql.append(" ORDER BY MODIFY_TIME DESC) UNION ALL (SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,ARTICLE_CONTENT,SUBJECT,INDUSTRY,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME,SHARE_ID FROM ARTICLE WHERE STATUS = '1'");
if(hasProfessorId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasArticleTitle){
sql.append(" AND ARTICLE_TITLE LIKE ?");
}
sql.append(" ORDER BY PUBLISH_TIME DESC)");
sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(offset);
}
ps = con.prepareStatement(sql.toString());
index = 1;
try{
if(hasProfessorId){
ps.setString(index++, professorId);
}
if(hasOrgId){
ps.setString(index++, orgId);
}
if(hasArticleTitle){
ps.setString(index++, articleTitle);
}
if(hasProfessorId){
ps.setString(index++, professorId);
}
if(hasOrgId){
ps.setString(index++, orgId);
}
if(hasArticleTitle){
ps.setString(index++, articleTitle);
}
ResultSet rs = ps.executeQuery();
try{
List<Article> articles = new ArrayList<Article>();
queryResult.setData(articles);
int size = 0;
while((size<pageSize) && rs.next()){
++size;
Article article = new Article();
article.setArticleId(rs.getString(1));
String proId = rs.getString(2);
if(rs.wasNull()){
proId = null;
}
article.setProfessorId(proId);
article.setArticleTitle(rs.getString(3));
String articleContent = rs.getString(4);
if(rs.wasNull()){
articleContent = null;
}
article.setArticleContent(articleContent);
String subject = rs.getString(5);
if(rs.wasNull()){
subject = null;
}
article.setSubject(subject);
String industry = rs.getString(6);
if(rs.wasNull()){
industry = null;
}
article.setIndustry(industry);
String publishTime = rs.getString(7);
if(rs.wasNull()){
publishTime = null;
}
article.setPublishTime(publishTime);
String articleImg = rs.getString(8);
if(rs.wasNull()){
articleImg = null;
}
article.setArticleImg(articleImg);
String org = rs.getString(9);
if(rs.wasNull()){
org = null;
}
article.setOrgId(org);
String articleType = rs.getString(10);
if(rs.wasNull()){
articleType = null;
}
article.setArticleType(articleType);
article.setArticleAgree(rs.getInt(11));
article.setPageViews(rs.getInt(12));
String status = rs.getString(13);
if(rs.wasNull()){
status = null;
}
article.setStatus(status);
article.setCreateTime(rs.getString(14));
article.setModifyTime(rs.getString(15));
article.setShareId(rs.getLong(16));
articles.add(article);
}
return queryResult;
}finally{
try{rs.close();}catch(Exception e3){}
}
}finally{
try{ps.close();}catch(Exception e4){}
}
}
public PageQueryResult<FindInfo> queryPage(Connection con,int pageSize,int pageNo) throws SQLException{
int total = 0;
PageQueryResult<FindInfo> queryResult = new PageQueryResult<FindInfo>();
String sql = null;
sql = "SELECT SUM(NUM) FROM (SELECT COUNT(1) NUM FROM ARTICLE UNION ALL SELECT COUNT(1) NUM FROM RESOURCE) TBL";
PreparedStatement ps = con.prepareStatement(sql);
try{
queryResult.setPageSize(pageSize);
ResultSet rs = ps.executeQuery();
try{
rs.next();
total = rs.getInt(1);
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
queryResult.setTotal(total);
if(0== total){
queryResult.setPageNo(1);
queryResult.setData(Collections.<FindInfo>emptyList());
return queryResult;
}
boolean firstPage = (1 == pageNo);
if(firstPage){
queryResult.setPageNo(1);
sql = "SELECT ARTICLE_ID,ARTICLE_TITLE,ARTICLE_IMG,ARTICLE_TYPE,CREATE_TIME,ORG_ID,PROFESSOR_ID FROM ARTICLE"
+ " UNION ALL SELECT RESOURCE_ID,RESOURCE_NAME,NULL,'3',CREATE_TIME,NULL,PROFESSOR_ID FROM RESOURCE";
sql = sql + " ORDER BY CREATE_TIME DESC ";
sql = sql + " LIMIT " + pageSize;
}else{
int pageNum = total / pageSize;
if(total % pageSize != 0){
++pageNum;
}
if(pageNo > pageNum){
pageNo = pageNum;
}
queryResult.setPageNo(pageNo);
--pageNo;
int offset = (pageNo * pageSize);
sql = "SELECT ARTICLE_ID,ARTICLE_TITLE,ARTICLE_IMG,ARTICLE_TYPE,CREATE_TIME,ORG_ID,PROFESSOR_ID FROM ARTICLE"
+ " UNION ALL SELECT RESOURCE_ID,RESOURCE_NAME,NULL,'3',CREATE_TIME,NULL,PROFESSOR_ID FROM RESOURCE";
sql = sql + " ORDER BY CREATE_TIME DESC ";
sql = sql + " LIMIT " + pageSize+ " OFFSET " + offset;
}
ps = con.prepareStatement(sql);
try{
ResultSet rs = ps.executeQuery();
try{
List<FindInfo> findInfos = new ArrayList<FindInfo>();
queryResult.setData(findInfos);
int size = 0;
while((size<pageSize) && rs.next()){
++size;
FindInfo findInfo = new FindInfo();
findInfo.setId(rs.getString(1));
findInfo.setName(rs.getString(2));
String image = rs.getString(3);
if(rs.wasNull()){
image = null;
}
findInfo.setImage(image);
findInfo.setType(rs.getString(4));
findInfo.setCreateTime(rs.getString(5));
String orgId = rs.getString(6);
if(rs.wasNull()){
findInfo.setOwner(rs.getString(7));
}else{
findInfo.setOwner(orgId);
}
findInfos.add(findInfo);
}
return queryResult;
}finally{
try{rs.close();}catch(Exception e3){}
}
}finally{
try{ps.close();}catch(Exception e4){}
}
}
public PageQueryResult<FindInfo> queryFindHot(Connection con,int pageSize,int pageNo) throws SQLException{
int total = 0;
PageQueryResult<FindInfo> queryResult = new PageQueryResult<FindInfo>();
String sql = null;
sql = "SELECT SUM(NUM) FROM ((SELECT COUNT(1) NUM FROM ARTICLE WHERE STATUS = '1') UNION ALL (SELECT COUNT(1) NUM FROM RESOURCE WHERE STATUS = '1')) TBL";
PreparedStatement ps = con.prepareStatement(sql);
try{
queryResult.setPageSize(pageSize);
ResultSet rs = ps.executeQuery();
try{
rs.next();
total = rs.getInt(1);
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
queryResult.setTotal(total);
if(0== total){
queryResult.setPageNo(1);
queryResult.setData(Collections.<FindInfo>emptyList());
return queryResult;
}
boolean firstPage = (1 == pageNo);
if(firstPage){
queryResult.setPageNo(1);
sql = "(SELECT ARTICLE_ID,ARTICLE_TITLE,ARTICLE_IMG,ARTICLE_TYPE,PUBLISH_TIME,ORG_ID,PROFESSOR_ID FROM ARTICLE WHERE STATUS = '1')"
+ " UNION ALL (SELECT RESOURCE_ID,RESOURCE_NAME,NULL,CASE WHEN RESOURCE_TYPE = '1' THEN '3' WHEN RESOURCE_TYPE = '2' THEN '4' ELSE NULL END,"
+ " PUBLISH_TIME,ORG_ID,PROFESSOR_ID FROM RESOURCE WHERE STATUS = '1')";
sql = sql + " ORDER BY PUBLISH_TIME DESC ";
sql = sql + " LIMIT " + pageSize;
}else{
int pageNum = total / pageSize;
if(total % pageSize != 0){
++pageNum;
}
if(pageNo > pageNum){
pageNo = pageNum;
}
queryResult.setPageNo(pageNo);
--pageNo;
int offset = (pageNo * pageSize);
sql = "(SELECT ARTICLE_ID,ARTICLE_TITLE,ARTICLE_IMG,ARTICLE_TYPE,PUBLISH_TIME,ORG_ID,PROFESSOR_ID FROM ARTICLE WHERE STATUS = '1')"
+ " UNION ALL (SELECT RESOURCE_ID,RESOURCE_NAME,NULL,CASE WHEN RESOURCE_TYPE = '1' THEN '3' WHEN RESOURCE_TYPE = '2' THEN '4' ELSE NULL END,"
+ " PUBLISH_TIME,ORG_ID,PROFESSOR_ID FROM RESOURCE WHERE STATUS = '1')";
sql = sql + " ORDER BY PUBLISH_TIME DESC ";
sql = sql + " LIMIT " + pageSize+ " OFFSET " + offset;
}
ps = con.prepareStatement(sql);
try{
ResultSet rs = ps.executeQuery();
try{
List<FindInfo> findInfos = new ArrayList<FindInfo>();
queryResult.setData(findInfos);
int size = 0;
while((size<pageSize) && rs.next()){
++size;
FindInfo findInfo = new FindInfo();
findInfo.setId(rs.getString(1));
findInfo.setName(rs.getString(2));
String image = rs.getString(3);
if(rs.wasNull()){
image = null;
}
findInfo.setImage(image);
findInfo.setType(rs.getString(4));
String createTime = rs.getString(5);
if(rs.wasNull()){
createTime = null;
}
findInfo.setCreateTime(createTime);
String orgId = rs.getString(6);
if(rs.wasNull()){
findInfo.setOwner(rs.getString(7));
}else{
findInfo.setOwner(orgId);
}
findInfos.add(findInfo);
}
return queryResult;
}finally{
try{rs.close();}catch(Exception e3){}
}
}finally{
try{ps.close();}catch(Exception e4){}
}
}
@SelectOne
@Nullable
public abstract Article query(Connection con ,long shareId)throws SQLException;
}
|
