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.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.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.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.LessThan;
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;
	
	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
    @Where("STATUS='1'")
    public abstract List<Resource> proPublish(Connection con, String professorId, @LessThan String publishTime, int rows) throws SQLException;

    @LimitSelect
    @Where("STATUS='1'")
    public abstract List<Resource> orgPublish(Connection con, String orgId, @LessThan String publishTime, 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()]);		
	}
}