|
package com.ekexiu.portal.dao;
import com.ekexiu.portal.po.Image;
import com.ekexiu.portal.po.Professor;
import com.ekexiu.portal.po.Resource;
import com.ekexiu.portal.pojo.EditProfessor;
import org.jfw.apt.annotation.Autowrie;
import org.jfw.apt.annotation.DefaultValue;
import org.jfw.apt.annotation.Nullable;
import org.jfw.apt.orm.annotation.dao.Column;
import org.jfw.apt.orm.annotation.dao.DAO;
import org.jfw.apt.orm.annotation.dao.Dynamic;
import org.jfw.apt.orm.annotation.dao.method.From;
import org.jfw.apt.orm.annotation.dao.method.LimitColumn;
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.QueryVal;
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.GroupSqlColumn;
import org.jfw.apt.orm.annotation.dao.param.In;
import org.jfw.apt.orm.annotation.dao.param.Like;
import org.jfw.apt.orm.annotation.dao.param.Set;
import org.jfw.apt.orm.core.defaultImpl.LongHandler;
import org.jfw.apt.orm.core.defaultImpl.StringHandler;
import org.jfw.util.PageQueryResult;
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;
@DAO
public abstract class ResourceDao {
@Autowrie
private ImageDao imageDao;
@Autowrie
private ProfessorDao professorDao;
public ImageDao getImageDao() {
return imageDao;
}
public void setImageDao(ImageDao imageDao) {
this.imageDao = imageDao;
}
public ProfessorDao getProfessorDao() {
return professorDao;
}
public void setProfessorDao(ProfessorDao professorDao) {
this.professorDao = professorDao;
}
@Insert
public abstract int insert(Connection con,Resource resource) throws SQLException;
@Update
public abstract int update(Connection con,Resource resource) throws SQLException;
@Update
@Dynamic
public abstract int updateRes(Connection con,Resource resource) throws SQLException;
@UpdateWith
@From(Resource.class)
@SetSentence("PAGE_VIEWS = PAGE_VIEWS + 1")
public abstract int incPageViews(Connection con,String resourceId) throws SQLException;
@UpdateWith
@From(Resource.class)
public abstract int updateStatus(Connection con, String resourceId, @Set String status) throws SQLException;
@UpdateWith
@From(Resource.class)
public abstract int updatePublishTime(Connection con,String resourceId,@Set String status,@Set String publishTime)throws SQLException;
@UpdateWith
@From(Resource.class)
public abstract int updateNameAndSupport(Connection con, String resourceId, @Set String resourceName,
@Set String supportedServices) throws SQLException;
@UpdateWith
@From(Resource.class)
public abstract int updateSubject(Connection con, String resourceId, @Set String subject) throws SQLException;
@UpdateWith
@From(Resource.class)
public abstract int updateIndustry(Connection con, String resourceId, @Set String industry) throws SQLException;
@UpdateWith
@From(Resource.class)
public abstract int updateDescp(Connection con, String resourceId, @Set String descp) throws SQLException;
@UpdateWith
@From(Resource.class)
public abstract int updateHopePayMethod(Connection con, String resourceId, @Set String hopePayMethod) throws SQLException;
@UpdateWith
@From(Resource.class)
public abstract int updateCooperationNotes(Connection con,String resourceId,@Set String cooperationNotes) throws SQLException;
@DeleteWith
@From(Resource.class)
public abstract int deletePro(Connection con, String professorId) throws SQLException;
@DeleteWith
@From(Resource.class)
public abstract int delete(Connection con, String resourceId) throws SQLException;
@SelectOne
@Nullable
public abstract Resource queryOne(Connection con, String resourceId) throws SQLException;
@LimitSelect
@OrderBy("ORDER BY RESOURCE_NAME ASC")
@Where("STATUS ='1'")
public abstract List<Resource> publishByProfessor(Connection con,String professorId,@Like String resourceName, int rows)throws SQLException;
@LimitSelect
@OrderBy("ORDER BY RESOURCE_NAME ASC")
@Where("STATUS ='1'")
public abstract List<Resource> publishByOrg(Connection con,String orgId,@Like String resourceName, int rows)throws SQLException;
public PageQueryResult<Resource> queryForSelf(Connection con,String professorId,@Nullable String key,int pageSize,int pageNo) throws SQLException{
int total = 0;
PageQueryResult<Resource> queryResult = new PageQueryResult<Resource>();
int index = 1;
boolean hasProfessorId = null != professorId;
boolean hasKey = null != key;
StringBuilder sql = new StringBuilder();
sql.append("SELECT COUNT(1) FROM RESOURCE WHERE STATUS IN('0','1')");
if(hasProfessorId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasKey){
sql.append(" AND (RESOURCE_NAME LIKE ? OR SUPPORTED_SERVICES LIKE ? OR SUBJECT LIKE ?)");
}
PreparedStatement ps = con.prepareStatement(sql.toString());
try{
if(hasProfessorId){
ps.setString(index++, professorId);
}
if(hasKey){
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.<Resource>emptyList());
return queryResult;
}
boolean firstPage = (1 == pageNo);
if(firstPage){
queryResult.setPageNo(1);
sql = new StringBuilder();
sql.append("(SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',PROFESSOR_ID,PUBLISH_TIME,CREATE_TIME,MODIFY_TIME,PAGE_VIEWS FROM RESOURCE WHERE STATUS = '0'");
if(hasProfessorId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasKey){
sql.append(" AND (RESOURCE_NAME LIKE ? OR SUPPORTED_SERVICES LIKE ? OR SUBJECT LIKE ?)");
}
sql.append(" ORDER BY MODIFY_TIME DESC) UNION ALL (SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',PROFESSOR_ID,PUBLISH_TIME,CREATE_TIME,MODIFY_TIME,PAGE_VIEWS FROM RESOURCE WHERE STATUS = '1'");
if(hasProfessorId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasKey){
sql.append(" AND (RESOURCE_NAME LIKE ? OR SUPPORTED_SERVICES LIKE ? OR SUBJECT 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 RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',PROFESSOR_ID,PUBLISH_TIME,CREATE_TIME,MODIFY_TIME,PAGE_VIEWS FROM RESOURCE WHERE STATUS = '0'");
if(hasProfessorId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasKey){
sql.append(" AND (RESOURCE_NAME LIKE ? OR SUPPORTED_SERVICES LIKE ? OR SUBJECT LIKE ?)");
}
sql.append(" ORDER BY MODIFY_TIME DESC) UNION ALL (SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',PROFESSOR_ID,PUBLISH_TIME,CREATE_TIME,MODIFY_TIME,PAGE_VIEWS FROM RESOURCE WHERE STATUS = '1'");
if(hasProfessorId){
sql.append(" AND PROFESSOR_ID = ?");
}
if(hasKey){
sql.append(" AND (RESOURCE_NAME LIKE ? OR SUPPORTED_SERVICES LIKE ? OR SUBJECT 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(hasKey){
ps.setString(index++, key);
ps.setString(index++, key);
ps.setString(index++, key);
}
if(hasProfessorId){
ps.setString(index++, professorId);
}
if(hasKey){
ps.setString(index++, key);
ps.setString(index++, key);
ps.setString(index++, key);
}
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
queryResult.setData(resources);
int size = 0;
while((size<pageSize) && rs.next()){
++size;
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
resource.setSupportedServices(rs.getString(4));
String orgName = rs.getString(5);
if(rs.wasNull()){
orgName = null;
}
resource.setOrgName(orgName);
String spec = rs.getString(6);
if(rs.wasNull()){
spec = null;
}
resource.setSpec(spec);
String parameter = rs.getString(7);
if(rs.wasNull()){
parameter = null;
}
resource.setParameter(parameter);
String status = rs.getString(8);
if(rs.wasNull()){
status = null;
}
resource.setStatus(status);
String descp = rs.getString(9);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
resource.setProfessorId(rs.getString(10));
String publishTime = rs.getString(11);
if(rs.wasNull()){
publishTime = null;
}
resource.setPublishTime(publishTime);
resource.setCreateTime(rs.getString(12));
resource.setModifyTime(rs.getString(13));
int pageViews = rs.getInt(14);
if(rs.wasNull()){
pageViews = 0;
}
resource.setPageViews(pageViews);
resources.add(resource);
}
return queryResult;
}finally{
try{rs.close();}catch(Exception e3){}
}
}finally{
try{ps.close();}catch(Exception e4){}
}
}
public PageQueryResult<Resource> querySelfForOrg(Connection con,String orgId,@Nullable String key,int pageSize,int pageNo) throws SQLException{
int total = 0;
PageQueryResult<Resource> queryResult = new PageQueryResult<Resource>();
int index = 1;
boolean hasOrgId = null != orgId;
boolean hasKey = null != key;
StringBuilder sql = new StringBuilder();
sql.append("SELECT COUNT(1) FROM RESOURCE WHERE STATUS IN('0','1')");
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasKey){
sql.append(" AND (RESOURCE_NAME LIKE ? OR SUPPORTED_SERVICES LIKE ? OR SUBJECT LIKE ?)");
}
PreparedStatement ps = con.prepareStatement(sql.toString());
try{
if(hasOrgId){
ps.setString(index++, orgId);
}
if(hasKey){
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.<Resource>emptyList());
return queryResult;
}
boolean firstPage = (1 == pageNo);
if(firstPage){
queryResult.setPageNo(1);
sql = new StringBuilder();
sql.append("(SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',ORG_ID,PUBLISH_TIME,CREATE_TIME,MODIFY_TIME,PAGE_VIEWS FROM RESOURCE WHERE STATUS = '0'");
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasKey){
sql.append(" AND (RESOURCE_NAME LIKE ? OR SUPPORTED_SERVICES LIKE ? OR SUBJECT LIKE ?)");
}
sql.append(" ORDER BY MODIFY_TIME DESC) UNION ALL (SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',ORG_ID,PUBLISH_TIME,CREATE_TIME,MODIFY_TIME,PAGE_VIEWS FROM RESOURCE WHERE STATUS = '1'");
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasKey){
sql.append(" AND (RESOURCE_NAME LIKE ? OR SUPPORTED_SERVICES LIKE ? OR SUBJECT 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 RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',ORG_ID,PUBLISH_TIME,CREATE_TIME,MODIFY_TIME,PAGE_VIEWS FROM RESOURCE WHERE STATUS = '0'");
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasKey){
sql.append(" AND (RESOURCE_NAME LIKE ? OR SUPPORTED_SERVICES LIKE ? OR SUBJECT LIKE ?)");
}
sql.append(" ORDER BY MODIFY_TIME DESC) UNION ALL (SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',ORG_ID,PUBLISH_TIME,CREATE_TIME,MODIFY_TIME,PAGE_VIEWS FROM RESOURCE WHERE STATUS = '1'");
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
}
if(hasKey){
sql.append(" AND (RESOURCE_NAME LIKE ? OR SUPPORTED_SERVICES LIKE ? OR SUBJECT 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(hasOrgId){
ps.setString(index++, orgId);
}
if(hasKey){
ps.setString(index++, key);
ps.setString(index++, key);
ps.setString(index++, key);
}
if(hasOrgId){
ps.setString(index++, orgId);
}
if(hasKey){
ps.setString(index++, key);
ps.setString(index++, key);
ps.setString(index++, key);
}
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
queryResult.setData(resources);
int size = 0;
while((size<pageSize) && rs.next()){
++size;
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
resource.setSupportedServices(rs.getString(4));
String orgName = rs.getString(5);
if(rs.wasNull()){
orgName = null;
}
resource.setOrgName(orgName);
String spec = rs.getString(6);
if(rs.wasNull()){
spec = null;
}
resource.setSpec(spec);
String parameter = rs.getString(7);
if(rs.wasNull()){
parameter = null;
}
resource.setParameter(parameter);
String status = rs.getString(8);
if(rs.wasNull()){
status = null;
}
resource.setStatus(status);
String descp = rs.getString(9);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
resource.setOrgId(rs.getString(10));
String publishTime = rs.getString(11);
if(rs.wasNull()){
publishTime = null;
}
resource.setPublishTime(publishTime);
resource.setCreateTime(rs.getString(12));
resource.setModifyTime(rs.getString(13));
int pageViews = rs.getInt(14);
if(rs.wasNull()){
pageViews = 0;
}
resource.setPageViews(pageViews);
resources.add(resource);
}
return queryResult;
}finally{
try{rs.close();}catch(Exception e3){}
}
}finally{
try{ps.close();}catch(Exception e4){}
}
}
public List<Resource> queryByProId(Connection con,String professorId,String resourceId,int rows) throws SQLException{
int index = 1;
boolean hasProId = null != professorId;
boolean hasResId = null != resourceId;
StringBuilder sql = new StringBuilder();
sql.append("SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',PROFESSOR_ID,PUBLISH_TIME,PAGE_VIEWS,ORG_ID,RESOURCE_TYPE FROM RESOURCE");
sql.append(" WHERE STATUS = '1'");
if(hasResId){
sql.append(" AND RESOURCE_ID != ?");
}
if(hasProId){
sql.append(" AND PROFESSOR_ID = ?");
sql.append(" ORDER BY PUBLISH_TIME DESC");
}else{
sql.append(" ORDER BY PAGE_VIEWS DESC");
}
sql.append(" LIMIT ").append(rows);
PreparedStatement ps = con.prepareStatement(sql.toString());
try{
if(hasResId){
ps.setString(index++, resourceId);
}
if(hasProId){
ps.setString(index++, professorId);
}
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
while(rs.next()){
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
resource.setSupportedServices(rs.getString(4));
String orgName = rs.getString(5);
if(rs.wasNull()){
orgName = null;
}
resource.setOrgName(orgName);
String spec = rs.getString(6);
if(rs.wasNull()){
spec = null;
}
resource.setSpec(spec);
String parameter = rs.getString(7);
if(rs.wasNull()){
parameter = null;
}
resource.setParameter(parameter);
String status = rs.getString(8);
if(rs.wasNull()){
status = null;
}
resource.setStatus(status);
String descp = rs.getString(9);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
String proId = rs.getString(10);
if(rs.wasNull()){
proId = null;
}
resource.setProfessorId(proId);
String publishTime = rs.getString(11);
if(rs.wasNull()){
publishTime = null;
}
resource.setPublishTime(publishTime);
int pageViews = rs.getInt(12);
if(rs.wasNull()){
pageViews = 0;
}
resource.setPageViews(pageViews);
String orgid = rs.getString(13);
if(rs.wasNull()){
orgid = null;
}
resource.setOrgId(orgid);
resource.setResourceType(rs.getString(14));
resources.add(resource);
}
return resources;
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
}
public List<Resource> queryByOrgId(Connection con,String orgId,String resourceId,int rows) throws SQLException{
int index = 1;
boolean hasOrgId = null != orgId;
boolean hasResId = null != resourceId;
StringBuilder sql = new StringBuilder();
sql.append("SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',PROFESSOR_ID,PUBLISH_TIME,PAGE_VIEWS,ORG_ID,RESOURCE_TYPE FROM RESOURCE");
sql.append(" WHERE STATUS = '1'");
if(hasResId){
sql.append(" AND RESOURCE_ID != ?");
}
if(hasOrgId){
sql.append(" AND ORG_ID = ?");
sql.append(" ORDER BY PUBLISH_TIME DESC");
}else{
sql.append(" ORDER BY PAGE_VIEWS DESC");
}
sql.append(" LIMIT ").append(rows);
PreparedStatement ps = con.prepareStatement(sql.toString());
try{
if(hasResId){
ps.setString(index++, resourceId);
}
if(hasOrgId){
ps.setString(index++, orgId);
}
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
while(rs.next()){
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
resource.setSupportedServices(rs.getString(4));
String orgName = rs.getString(5);
if(rs.wasNull()){
orgName = null;
}
resource.setOrgName(orgName);
String spec = rs.getString(6);
if(rs.wasNull()){
spec = null;
}
resource.setSpec(spec);
String parameter = rs.getString(7);
if(rs.wasNull()){
parameter = null;
}
resource.setParameter(parameter);
String status = rs.getString(8);
if(rs.wasNull()){
status = null;
}
resource.setStatus(status);
String descp = rs.getString(9);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
String proId = rs.getString(10);
if(rs.wasNull()){
proId = null;
}
resource.setProfessorId(proId);
String publishTime = rs.getString(11);
if(rs.wasNull()){
publishTime = null;
}
resource.setPublishTime(publishTime);
int pageViews = rs.getInt(12);
if(rs.wasNull()){
pageViews = 0;
}
resource.setPageViews(pageViews);
String orgid = rs.getString(13);
if(rs.wasNull()){
orgid = null;
}
resource.setOrgId(orgid);
resource.setResourceType(rs.getString(14));
resources.add(resource);
}
return resources;
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
}
/**
* 按关键字搜索文章列表，不包括本条资源
* @param con
* @param keys 关键词
* @param resourceId 资源ID
* @param rows 返回记录数
* @return
* @throws SQLException
*/
public List<Resource> queryLimit(Connection con,String[] keys,String resourceId,int rows) throws SQLException{
int index = 1;
boolean hasKeys = null != keys;
boolean hasResId = null != resourceId;
StringBuilder sql = new StringBuilder();
sql.append("SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',PROFESSOR_ID,PUBLISH_TIME,PAGE_VIEWS,ORG_ID,RESOURCE_TYPE FROM RESOURCE");
sql.append(" WHERE STATUS = '1'");
if(hasResId){
sql.append(" AND RESOURCE_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(hasResId){
ps.setString(index++, resourceId);
}
if(hasKeys){
for (String key : keys) {
ps.setString(index++, "%"+key+"%");
}
}
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
while(rs.next()){
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
resource.setSupportedServices(rs.getString(4));
String orgName = rs.getString(5);
if(rs.wasNull()){
orgName = null;
}
resource.setOrgName(orgName);
String spec = rs.getString(6);
if(rs.wasNull()){
spec = null;
}
resource.setSpec(spec);
String parameter = rs.getString(7);
if(rs.wasNull()){
parameter = null;
}
resource.setParameter(parameter);
String status = rs.getString(8);
if(rs.wasNull()){
status = null;
}
resource.setStatus(status);
String descp = rs.getString(9);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
resource.setProfessorId(rs.getString(10));
String publishTime = rs.getString(11);
if(rs.wasNull()){
publishTime = null;
}
resource.setPublishTime(publishTime);
int pageViews = rs.getInt(12);
if(rs.wasNull()){
pageViews = 0;
}
resource.setPageViews(pageViews);
String orgid = rs.getString(13);
if(rs.wasNull()){
orgid = null;
}
resource.setOrgId(orgid);
resource.setResourceType(rs.getString(14));
resources.add(resource);
}
return resources;
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
}
/**
* 查询资源基础信息(ID,名称,学术领域,应用行业,可提供服务,发布者ID,资源图片)
* @param con
* @param resourceId 资源ID
* @return 返回资源基础信息
* @throws SQLException
*/
public Resource queryBaseInfo(Connection con,String resourceId) throws SQLException{
String sql = "SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,INDUSTRY,SUPPORTED_SERVICES,PROFESSOR_ID"
+ " FROM RESOURCE WHERE RESOURCE_ID = ?";
PreparedStatement ps = con.prepareStatement(sql);
try{
ps.setString(1,resourceId);
ResultSet rs = ps.executeQuery();
try{
if(rs.next()){
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
String industry = rs.getString(4);
if(rs.wasNull()){
industry = null;
}
resource.setIndustry(industry);
resource.setSupportedServices(rs.getString(5));
resource.setProfessorId(rs.getString(6));
List<Image> images = this.imageDao.queryRes(con, resourceId);
resource.setImages(images);
return resource;
}else{
return null;
}
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
}
public Resource query(Connection con,String resourceId) throws SQLException{
int index = 1;
String sql = "SELECT RESOURCE_ID,RESOURCE_NAME,RESOURCE.SUBJECT,RESOURCE.INDUSTRY,"
+ " SUPPORTED_SERVICES,RESOURCE.DESCP,PROFESSOR_ID,ASCRIPTION,HOPE_PAY_METHOD,"
+ " COOPERATION_NOTES,RESOURCE.CREATE_TIME,RESOURCE.MODIFY_TIME,PROFESSOR.NAME "
+ " FROM RESOURCE LEFT JOIN PROFESSOR ON PROFESSOR_ID = PROFESSOR.ID WHERE RESOURCE_ID = ?";
PreparedStatement ps = con.prepareStatement(sql);
try{
ps.setString(index++,resourceId);
ResultSet rs = ps.executeQuery();
try{
if(rs.next()){
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
String industry = rs.getString(4);
if(rs.wasNull()){
industry = null;
}
resource.setIndustry(industry);
resource.setSupportedServices(rs.getString(5));
String descp = rs.getString(6);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
Professor professor = new Professor();
professor.setId(rs.getString(7));
resource.setAscription(rs.getString(8));
String hopePay = rs.getString(9);
if(rs.wasNull()){
hopePay = null;
}
resource.setHopePayMethod(hopePay);
String cooperationNotes = rs.getString(10);
if(rs.wasNull()){
cooperationNotes = null;
}
resource.setCooperationNotes(cooperationNotes);
resource.setCreateTime(rs.getString(11));
resource.setModifyTime(rs.getString(12));
professor.setName(rs.getString(13));
resource.setProfessor(professor);
return resource;
}else{
return null;
}
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
}
/**
* 查询资源详细信息
* @param con
* @param resourceId
* @return
* @throws SQLException
*/
public Resource queryInfo(Connection con,String resourceId) throws SQLException{
int index = 1;
String sql = "SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,INDUSTRY,SUPPORTED_SERVICES,DESCP,PROFESSOR_ID,"
+ " ASCRIPTION,HOPE_PAY_METHOD,COOPERATION_NOTES,CREATE_TIME,MODIFY_TIME "
+ " FROM RESOURCE WHERE RESOURCE_ID = ?";
PreparedStatement ps = con.prepareStatement(sql);
try{
ps.setString(index++,resourceId);
ResultSet rs = ps.executeQuery();
try{
if(rs.next()){
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
String industry = rs.getString(4);
if(rs.wasNull()){
industry = null;
}
resource.setIndustry(industry);
resource.setSupportedServices(rs.getString(5));
String descp = rs.getString(6);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
resource.setProfessorId(rs.getString(7));
resource.setAscription(rs.getString(8));
String hopePay = rs.getString(9);
if(rs.wasNull()){
hopePay = null;
}
resource.setHopePayMethod(hopePay);
String cooperationNotes = rs.getString(10);
if(rs.wasNull()){
cooperationNotes = null;
}
resource.setCooperationNotes(cooperationNotes);
resource.setCreateTime(rs.getString(11));
resource.setModifyTime(rs.getString(12));
return resource;
}else{
return null;
}
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
}
public PageQueryResult<Resource> queryPageAll(Connection con,int pageSize,int pageNo) throws SQLException{
int total = 0;
PageQueryResult<Resource> queryResult = new PageQueryResult<Resource>();
String sql = null;
sql = " SELECT COUNT(1) FROM RESOURCE ";
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.<Resource>emptyList());
return queryResult;
}
boolean firstPage = (1 == pageNo);
if(firstPage){
queryResult.setPageNo(1);
sql = " SELECT RESOURCE_ID,RESOURCE_NAME,RESOURCE.SUBJECT,RESOURCE.INDUSTRY,"
+ " SUPPORTED_SERVICES,'',PROFESSOR_ID,ASCRIPTION,HOPE_PAY_METHOD,"
+ " COOPERATION_NOTES,RESOURCE.CREATE_TIME,RESOURCE.MODIFY_TIME,PROFESSOR.NAME "
+ " FROM RESOURCE LEFT JOIN PROFESSOR ON PROFESSOR_ID = PROFESSOR.ID ";
sql = sql + " ORDER BY RESOURCE_NAME ";
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 RESOURCE_ID,RESOURCE_NAME,RESOURCE.SUBJECT,RESOURCE.INDUSTRY,"
+ " SUPPORTED_SERVICES,'',PROFESSOR_ID,ASCRIPTION,HOPE_PAY_METHOD,"
+ " COOPERATION_NOTES,RESOURCE.CREATE_TIME,RESOURCE.MODIFY_TIME,PROFESSOR.NAME "
+ " FROM RESOURCE LEFT JOIN PROFESSOR ON PROFESSOR_ID = PROFESSOR.ID ";
sql = sql + " ORDER BY RESOURCE_NAME ";
sql = sql + " LIMIT " + pageSize+ " OFFSET " + offset;
}
ps = con.prepareStatement(sql);
try{
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
queryResult.setData(resources);
int size = 0;
while((size<pageSize) && rs.next()){
++size;
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
String industry = rs.getString(4);
if(rs.wasNull()){
industry = null;
}
resource.setIndustry(industry);
resource.setSupportedServices(rs.getString(5));
String descp = rs.getString(6);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
Professor professor = new Professor();
professor.setId(rs.getString(7));
String ascription = rs.getString(8);
if(rs.wasNull()){
ascription = null;
}
resource.setAscription(ascription);
String hopePay = rs.getString(9);
if(rs.wasNull()){
hopePay = null;
}
resource.setHopePayMethod(hopePay);
String cooperationNotes = rs.getString(10);
if(rs.wasNull()){
cooperationNotes = null;
}
resource.setCooperationNotes(cooperationNotes);
resource.setCreateTime(rs.getString(11));
resource.setModifyTime(rs.getString(12));
professor.setName(rs.getString(13));
resource.setProfessor(professor);
resources.add(resource);
}
return queryResult;
}finally{
try{rs.close();}catch(Exception e3){}
}
}finally{
try{ps.close();}catch(Exception e4){}
}
}
/**
* APP专家搜索页面显示该专家所发布的资源信息
* @param con
* @param professorId 专家ID
* @return 返回该专家所发布的所有资源 按发布时间倒序
* @throws SQLException
*/
public List<Resource> queryList(Connection con,String professorId) throws SQLException{
String sql = "SELECT RESOURCE_ID,RESOURCE_NAME FROM RESOURCE WHERE PROFESSOR_ID = ? ORDER BY CREATE_TIME DESC";
PreparedStatement ps = con.prepareStatement(sql);
try{
ps.setString(1,professorId);
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
while(rs.next()){
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
resources.add(resource);
}
return resources;
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
}
@LimitSelect
@Where("STATUS = '1'")
@OrderBy("ORDER BY PUBLISH_TIME DESC")
public abstract List<Resource> queryByName(Connection con, @Like String resourceName, int rows) throws SQLException;
@SelectList
@OrderBy("ORDER BY PUBLISH_TIME DESC")
@Where("STATUS = '1'")
public abstract List<Resource> queryProPublish(Connection con,String professorId) throws SQLException;
@PageSelect
@OrderBy("ORDER BY PUBLISH_TIME DESC")
@Where("STATUS = '1'")
public abstract PageQueryResult<Resource> pageQueryProPublish(Connection con,String professorId,int pageSize,int pageNo) throws SQLException;
//@LimitSelect
//@OrderBy(cols = {@LimitColumn(value = "publishTime", asc = false), @LimitColumn(value = "shareId",asc = false,handlerClass = LongHandler.class)}, value = "")
//@Where("STATUS='1'")
//public abstract List<Resource> proPublish(Connection con, String professorId, String publishTime, long shareId, int rows) throws SQLException;
//
//@LimitSelect
//@OrderBy(cols = {@LimitColumn(value = "publishTime", asc = false), @LimitColumn(value = "shareId",asc = false, handlerClass = LongHandler.class)}, value = "")
//@Where("STATUS='1'")
//public abstract List<Resource> orgPublish(Connection con, String orgId, String publishTime, long shareId, int rows) throws SQLException;
@LimitSelect
@OrderBy(cols = {@LimitColumn(value = "publishTime", asc = false), @LimitColumn(value = "shareId",asc = false, handlerClass = LongHandler.class)}, value = "")
@Where("STATUS='1'")
public abstract List<Resource> publish(Connection con,String resourceType,@GroupSqlColumn(handlerClass = StringHandler.class,value ={"PROFESSOR_ID = ?","ORG_ID = ?"},isAnd = false) String owner, String publishTime, long shareId, int rows) throws SQLException;
@SelectList
@OrderBy("ORDER BY PUBLISH_TIME DESC")
@Where("STATUS IN ('0','1')")
public abstract List<Resource> queryForDesk(Connection con,String professorId) throws SQLException;
@SelectList
@OrderBy("ORDER BY PUBLISH_TIME DESC")
@Where("STATUS = '1'")
public abstract List<Resource> queryOrgPublish(Connection con,String orgId) throws SQLException;
@PageSelect
@OrderBy("ORDER BY PUBLISH_TIME DESC")
@Where("STATUS = '1'")
public abstract PageQueryResult<Resource> pageQueryOrgPublish(Connection con,String orgId,int pageSize,int pageNo) throws SQLException;
/**
* 按专家ID查询发布的所有资源
* @param con
* @param professorId 专家ID
* @return 返回该专家发布的所有资源 按发布时间倒序
* @throws SQLException
*/
public List<Resource> queryPro(Connection con,String professorId) throws SQLException{
int index = 1;
String sql = "SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,INDUSTRY,SUPPORTED_SERVICES,'',"
+ " PROFESSOR_ID,ASCRIPTION,HOPE_PAY_METHOD,COOPERATION_NOTES,CREATE_TIME,MODIFY_TIME "
+ " FROM RESOURCE WHERE PROFESSOR_ID = ? ORDER BY CREATE_TIME DESC";
PreparedStatement ps = con.prepareStatement(sql);
try{
ps.setString(index++,professorId);
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
while(rs.next()){
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
String industry = rs.getString(4);
if(rs.wasNull()){
industry = null;
}
resource.setIndustry(industry);
resource.setSupportedServices(rs.getString(5));
String descp = rs.getString(6);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
resource.setProfessorId(rs.getString(7));
String ascription = rs.getString(8);
if(rs.wasNull()){
ascription = null;
}
resource.setAscription(ascription);
String hopePay = rs.getString(9);
if(rs.wasNull()){
hopePay = null;
}
resource.setHopePayMethod(hopePay);
String cooperationNotes = rs.getString(10);
if(rs.wasNull()){
cooperationNotes = null;
}
resource.setCooperationNotes(cooperationNotes);
resource.setCreateTime(rs.getString(11));
resource.setModifyTime(rs.getString(12));
resources.add(resource);
}
return resources;
}finally{
try{rs.close();}catch(Exception e1){}
}
}finally{
try{ps.close();}catch(Exception e2){}
}
}
public PageQueryResult<Resource> firstPageQuery(Connection con,String key,int pageSize,int pageNo) throws SQLException{
int total = 0;
PageQueryResult<Resource> queryResult = new PageQueryResult<Resource>();
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 RESOURCE_NAME LIKE ? OR SUBJECT LIKE ? OR SUPPORTED_SERVICES LIKE ? OR ORG_NAME LIKE ? OR SPEC LIKE ?)");
}
StringBuilder whereSql = sql;
sql = new StringBuilder();
sql.append("SELECT COUNT(1) FROM RESOURCE");
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);
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.<Resource>emptyList());
return queryResult;
}
index = 1;
boolean firstPage = (1 == pageNo);
if(firstPage){
queryResult.setPageNo(1);
sql = new StringBuilder();
sql.append("SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',PROFESSOR_ID,PUBLISH_TIME,PAGE_VIEWS,ORG_ID,RESOURCE_TYPE FROM RESOURCE");
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 RESOURCE_ID,RESOURCE_NAME,SUBJECT,SUPPORTED_SERVICES,ORG_NAME,SPEC,PARAMETER,STATUS,'',PROFESSOR_ID,PUBLISH_TIME,PAGE_VIEWS,ORG_ID,RESOURCE_TYPE FROM RESOURCE");
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);
ps.setString(index++,key);
ps.setString(index++,key);
ps.setString(index++,key);
}
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
queryResult.setData(resources);
int size = 0;
while((size<pageSize) && rs.next()){
++size;
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subject = rs.getString(3);
if(rs.wasNull()){
subject = null;
}
resource.setSubject(subject);
resource.setSupportedServices(rs.getString(4));
String orgName = rs.getString(5);
if(rs.wasNull()){
orgName = null;
}
resource.setOrgName(orgName);
String spec = rs.getString(6);
if(rs.wasNull()){
spec = null;
}
resource.setSpec(spec);
String parameter = rs.getString(7);
if(rs.wasNull()){
parameter = null;
}
resource.setParameter(parameter);
String status = rs.getString(8);
if(rs.wasNull()){
status = null;
}
resource.setStatus(status);
String descp = rs.getString(9);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
String proId = rs.getString(10);
if(rs.wasNull()){
proId = null;
}
resource.setProfessorId(proId);
String publishTime = rs.getString(11);
if(rs.wasNull()){
publishTime = null;
}
resource.setPublishTime(publishTime);
int pageViews = rs.getInt(12);
if(rs.wasNull()){
pageViews = 0;
}
resource.setPageViews(pageViews);
String orgid = rs.getString(13);
if(rs.wasNull()){
orgid = null;
}
resource.setOrgId(orgid);
resource.setResourceType(rs.getString(14));
resources.add(resource);
}
return queryResult;
}finally{
try{rs.close();}catch(Exception e3){}
}
}finally{
try{ps.close();}catch(Exception e4){}
}
}
@LimitSelect
@Where("STATUE='1'")
@OrderBy(cols = {@LimitColumn(value = "sortNum", asc = false, handlerClass = LongHandler.class), @LimitColumn(value = "publishTime", asc = false, handlerClass = StringHandler.class), @LimitColumn(value = "resourceId", asc = false, handlerClass = StringHandler.class)}, value = "")
public abstract List<Resource> indexSearch(Connection con, @Nullable @GroupSqlColumn(handlerClass = StringHandler.class, value = {"PROFESSOR_ID IN (SELECT ID FROM PROFESSOR WHERE NAME LIKE ?", "ORG_ID IN (SELECT ID FROM ORGANIZATION WHERE NAME LIKE ? OR FOR_SHORT LIKE ?)", "RESOURCE_NAME LIKE ?", "SUBJECT LIKE ?", "SUPPORTED_SERVICES LIKE ?", "ORG_NAME LIKE ?", "SPEC LIKE ?"}, additional = 1, isAnd = false, force = true) String key, long sortNum, String publishTime, String resourceId, int rows) throws SQLException;
/**
* 资源搜索页面接口，添加资源拥有者基本信息
* @param key 搜索条件
* @param subject 学术领域
* @param industry 应用行业
* @param address 所在城市
* @param pageSize
* @param pageNo
* @return 按条件返回资源列表(包括资源所有者基本信息)
* @throws SQLException
*/
public PageQueryResult<Resource> queryPageRes(Connection con,String key,String subject,String industry,
String province,String address,Integer authType,int pageSize,int pageNo) throws SQLException{
int total = 0;
PageQueryResult<Resource> queryResult = new PageQueryResult<Resource>();
int index = 1;
boolean hasKey = null != key;
boolean hasSubject = null != subject;
boolean hasIndustry = null != industry;
boolean hsaProvince = null != province;
boolean hasAddress = null != address;
boolean hasAuthType = null != authType;
StringBuilder sql = new StringBuilder();
if(hasKey){
sql.append(" WHERE ((RESOURCE_NAME LIKE ?) OR (SUPPORTED_SERVICES LIKE ?) "
+ " OR (RESOURCE.SUBJECT LIKE ?) OR (RESOURCE.INDUSTRY LIKE ?) "
+ " OR (RESOURCE.PROFESSOR_ID IN (SELECT PROFESSOR.ID FROM PROFESSOR "
+ " WHERE PROFESSOR.PROVINCE LIKE ? OR PROFESSOR.ADDRESS LIKE ? OR PROFESSOR.NAME LIKE ?))"
+ " OR (RESOURCE.PROFESSOR_ID IN (SELECT PROFESSOR.ID FROM PROFESSOR WHERE ORG_ID "
+ " IN(SELECT ORGANIZATION.ID FROM ORGANIZATION WHERE ORGANIZATION.NAME LIKE ?) )) ) ");
}
if(hasSubject){
sql.append(sql.length() > 0 ? " AND " : " WHERE ").append("(RESOURCE.SUBJECT LIKE ?)");
}
if(hasIndustry){
sql.append(sql.length() > 0 ? " AND " : " WHERE ").append("(RESOURCE.INDUSTRY LIKE ?)");
}
if(hsaProvince){
sql.append(sql.length() > 0 ? " AND " : " WHERE ").
append("(RESOURCE.PROFESSOR_ID IN (SELECT PROFESSOR.ID FROM PROFESSOR WHERE PROFESSOR.PROVINCE LIKE ?))");
}
if(hasAddress){
sql.append(sql.length() > 0 ? " AND " : " WHERE ").
append("(RESOURCE.PROFESSOR_ID IN (SELECT PROFESSOR.ID FROM PROFESSOR WHERE PROFESSOR.ADDRESS LIKE ?))");
}
if(hasAuthType){
sql.append(sql.length() > 0 ? " AND " : " WHERE ").
append("(RESOURCE.PROFESSOR_ID IN (SELECT PROFESSOR.ID FROM PROFESSOR WHERE PROFESSOR.AUTH_TYPE = ?))");
}
StringBuilder whereSql = sql;
sql = new StringBuilder();
sql.append(" SELECT COUNT(1) FROM RESOURCE ");
if(whereSql.length()>0){
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);
ps.setString(index++,key);
ps.setString(index++,key);
ps.setString(index++,key);
}
if(hasSubject){
ps.setString(index++,subject);
}
if(hasIndustry){
ps.setString(index++,industry);
}
if(hsaProvince){
ps.setString(index++,province);
}
if(hasAddress){
ps.setString(index++,address);
}
if(hasAuthType){
ps.setInt(index++,authType);
}
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.<Resource>emptyList());
return queryResult;
}
index = 1;
boolean firstPage = (1 == pageNo);
if(firstPage){
queryResult.setPageNo(1);
sql = new StringBuilder();
sql.append("SELECT RESOURCE_ID,RESOURCE_NAME,SUBJECT,INDUSTRY,SUPPORTED_SERVICES,DESCP,PROFESSOR_ID,"
+ " ASCRIPTION,HOPE_PAY_METHOD,COOPERATION_NOTES,CREATE_TIME,MODIFY_TIME FROM RESOURCE ");
if(whereSql.length()>0){
sql.append(whereSql);
}
sql.append(" ORDER BY RESOURCE_NAME");
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 RESOURCE_ID,RESOURCE_NAME,SUBJECT,INDUSTRY,SUPPORTED_SERVICES,DESCP,PROFESSOR_ID,"
+ " ASCRIPTION,HOPE_PAY_METHOD,COOPERATION_NOTES,CREATE_TIME,MODIFY_TIME FROM RESOURCE ");
if(whereSql.length()>0){
sql.append(whereSql);
}
sql.append(" ORDER BY RESOURCE_NAME");
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);
ps.setString(index++,key);
ps.setString(index++,key);
ps.setString(index++,key);
}
if(hasSubject){
ps.setString(index++,subject);
}
if(hasIndustry){
ps.setString(index++,industry);
}
if(hsaProvince){
ps.setString(index++,province);
}
if(hasAddress){
ps.setString(index++,address);
}
if(hasAuthType){
ps.setInt(index++,authType);
}
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
queryResult.setData(resources);
int size = 0;
while((size<pageSize) && rs.next()){
++size;
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subj = rs.getString(3);
if(rs.wasNull()){
subj = null;
}
resource.setSubject(subj);
String indust = rs.getString(4);
if(rs.wasNull()){
indust = null;
}
resource.setIndustry(indust);
resource.setSupportedServices(rs.getString(5));
String descp = rs.getString(6);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
resource.setProfessorId(rs.getString(7));
resource.setAscription(rs.getString(8));
resource.setHopePayMethod(rs.getString(9));
String cooperationNotes = rs.getString(10);
if(rs.wasNull()){
cooperationNotes = null;
}
resource.setCooperationNotes(cooperationNotes);
resource.setCreateTime(rs.getString(11));
resource.setModifyTime(rs.getString(12));
resources.add(resource);
}
return queryResult;
}finally{
try{rs.close();}catch(Exception e3){}
}
}finally{
try{ps.close();}catch(Exception e4){}
}
}
/**
* 资源搜索页面接口
* @param key 搜索条件
* @param subject 学术领域
* @param industry 应用行业
* @param address 所在城市
* @param pageSize
* @param pageNo
* @return 按搜索条件返回资源列表
* @throws SQLException
*/
public PageQueryResult<Resource> queryPage(Connection con,String key,String subject,String industry,
String address,Integer authType,int pageSize,int pageNo) throws SQLException{
int total = 0;
PageQueryResult<Resource> queryResult = new PageQueryResult<Resource>();
int index = 1;
boolean hasKey = null != key;
boolean hasSubject = null != subject;
boolean hasIndustry = null != industry;
boolean hasAddress = null != address;
boolean hasAuthType = null != authType;
StringBuilder sql = new StringBuilder();
boolean state = true;
if(hasKey){
state = false;
sql.append(" WHERE ((RESOURCE_NAME LIKE ?) OR (SUPPORTED_SERVICES LIKE ?) OR (R.SUBJECT LIKE ?) OR (R.INDUSTRY LIKE ?) "
+ " OR (R.PROFESSOR_ID IN (SELECT ID FROM PROFESSOR WHERE ADDRESS LIKE ? OR NAME LIKE ?))"
+ " OR (R.PROFESSOR_ID IN (SELECT ID FROM PROFESSOR WHERE ORG_ID IN(SELECT ID FROM ORGANIZATION WHERE NAME LIKE ?) )) ) ");
}
if(hasSubject){
if(state){
state = false;
sql.append(" WHERE (R.SUBJECT LIKE ?) ");
}else{
sql.append(" AND (R.SUBJECT LIKE ?) ");
}
}
if(hasIndustry){
if(state){
state = false;
sql.append(" WHERE (R.INDUSTRY LIKE ?) ");
}else{
sql.append(" AND (R.INDUSTRY LIKE ?) ");
}
}
if(hasAddress){
if(state){
state = false;
sql.append(" WHERE (R.PROFESSOR_ID IN (SELECT PROFESSOR.ID FROM PROFESSOR WHERE PROFESSOR.ADDRESS LIKE ?)) ");
}else{
sql.append(" AND (R.PROFESSOR_ID IN (SELECT PROFESSOR.ID FROM PROFESSOR WHERE PROFESSOR.ADDRESS LIKE ?)) ");
}
}
if(hasAuthType){
if(state){
state = false;
sql.append(" WHERE (R.PROFESSOR_ID IN (SELECT PROFESSOR.ID FROM PROFESSOR WHERE PROFESSOR.AUTH_TYPE = ?)) ");
}else{
sql.append(" AND (R.PROFESSOR_ID IN (SELECT PROFESSOR.ID FROM PROFESSOR WHERE PROFESSOR.AUTH_TYPE = ?)) ");
}
}
StringBuilder whereSql = sql;
sql = new StringBuilder();
sql.append(" SELECT COUNT(1) FROM RESOURCE R ");
if(whereSql.length()>0){
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);
ps.setString(index++,key);
ps.setString(index++,key);
}
if(hasSubject){
ps.setString(index++,subject);
}
if(hasIndustry){
ps.setString(index++,industry);
}
if(hasAddress){
ps.setString(index++,address);
}
if(hasAuthType){
ps.setInt(index++, authType);
}
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.<Resource>emptyList());
return queryResult;
}
index = 1;
boolean firstPage = (1 == pageNo);
if(firstPage){
queryResult.setPageNo(1);
sql = new StringBuilder();
sql.append("SELECT RESOURCE_ID,RESOURCE_NAME,R.SUBJECT,R.INDUSTRY,SUPPORTED_SERVICES,'',PROFESSOR_ID,"
+ " ASCRIPTION,HOPE_PAY_METHOD,COOPERATION_NOTES,R.CREATE_TIME,R.MODIFY_TIME,P.NAME,P.TITLE,"
+ " P.DEPARTMENT,P.ADDRESS,P.OFFICE,AUTHENTICATION,P.AUTH_TYPE,P.AUTH_STATUS,O.NAME "
+ " FROM RESOURCE R LEFT JOIN PROFESSOR P ON PROFESSOR_ID = P.ID "
+ " LEFT JOIN ORGANIZATION O ON P.ORG_ID = O.ID");
if(whereSql.length()>0){
sql.append(whereSql);
}
sql.append(" ORDER BY RESOURCE_NAME");
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 RESOURCE_ID,RESOURCE_NAME,R.SUBJECT,R.INDUSTRY,SUPPORTED_SERVICES,'',PROFESSOR_ID,"
+ " ASCRIPTION,HOPE_PAY_METHOD,COOPERATION_NOTES,R.CREATE_TIME,R.MODIFY_TIME,P.NAME,P.TITLE,"
+ " P.DEPARTMENT,P.ADDRESS,P.OFFICE,AUTHENTICATION,P.AUTH_TYPE,P.AUTH_STATUS,O.NAME "
+ " FROM RESOURCE R LEFT JOIN PROFESSOR P ON PROFESSOR_ID = P.ID "
+ " LEFT JOIN ORGANIZATION O ON P.ORG_ID = O.ID");
if(whereSql.length()>0){
sql.append(whereSql);
}
sql.append(" ORDER BY RESOURCE_NAME");
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);
ps.setString(index++,key);
ps.setString(index++,key);
}
if(hasSubject){
ps.setString(index++,subject);
}
if(hasIndustry){
ps.setString(index++,industry);
}
if(hasAddress){
ps.setString(index++,address);
}
if(hasAuthType){
ps.setInt(index++, authType);
}
ResultSet rs = ps.executeQuery();
try{
List<Resource> resources = new ArrayList<Resource>();
queryResult.setData(resources);
int size = 0;
while((size<pageSize) && rs.next()){
++size;
Resource resource = new Resource();
resource.setResourceId(rs.getString(1));
resource.setResourceName(rs.getString(2));
String subj = rs.getString(3);
if(rs.wasNull()){
subj = null;
}
resource.setSubject(subj);
String indust = rs.getString(4);
if(rs.wasNull()){
indust = null;
}
resource.setIndustry(indust);
resource.setSupportedServices(rs.getString(5));
String descp = rs.getString(6);
if(rs.wasNull()){
descp = null;
}
resource.setDescp(descp);
EditProfessor professor = new EditProfessor();
professor.setId(rs.getString(7));
resource.setAscription(rs.getString(8));
resource.setHopePayMethod(rs.getString(9));
String notes = rs.getString(10);
if(rs.wasNull()){
notes = null;
}
resource.setCooperationNotes(notes);
resource.setCreateTime(rs.getString(11));
resource.setModifyTime(rs.getString(12));
professor.setName(rs.getString(13));
String title = rs.getString(14);
if(rs.wasNull()){
title = null;
}
professor.setTitle(title);
String depart = rs.getString(15);
if(rs.wasNull()){
depart = null;
}
professor.setDepartment(depart);
String addr = rs.getString(16);
if(rs.wasNull()){
addr = null;
}
professor.setAddress(addr);
String office = rs.getString(17);
if(rs.wasNull()){
office = null;
}
professor.setOffice(office);
professor.setAuthentication(rs.getInt(18));
professor.setAuthType(rs.getInt(19));
professor.setAuthStatus(rs.getInt(20));
String orgName = rs.getString(21);
if(rs.wasNull()){
orgName = null;
}
professor.setOrgName(orgName);
resource.setEditProfessor(professor);
resources.add(resource);
}
return queryResult;
}finally{
try{rs.close();}catch(Exception e3){}
}
}finally{
try{ps.close();}catch(Exception e4){}
}
}
@SelectOne
@Nullable
public abstract Resource query(Connection con,long shareId)throws SQLException;
@SelectList
public abstract List<Resource> query(Connection con,@In String[] resourceId)throws SQLException;
public String[] queryResourceIdWithSameKeyWord(Connection con,String id,int rows)throws SQLException{
List<String> ret = new ArrayList<String>();
String sql = "select id,count(1) sort from res_key_word where kw in (select kw from res_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()]);
}
@DefaultValue("0")
@QueryVal
@Where("STATUS = '1'")
@Column(value = "COUNT(1)", handlerClass = LongHandler.class)
@From(Resource.class)
public abstract long countPublish(Connection con, String resourceType, @GroupSqlColumn(handlerClass = StringHandler.class, value = {"PROFESSOR_ID = ?", "ORG_ID = ?"}, isAnd = false) String owner) throws SQLException;
}
|
