|
package com.ekexiu.portal.dao;
import com.ekexiu.portal.basepo.BaseOrganization;
import com.ekexiu.portal.po.Organization;
import com.ekexiu.portal.pojo.EditOrganization;
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.method.From;
import org.jfw.apt.orm.annotation.dao.method.Or;
import org.jfw.apt.orm.annotation.dao.method.OrderBy;
import org.jfw.apt.orm.annotation.dao.method.Select;
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.LimitQuery;
import org.jfw.apt.orm.annotation.dao.method.operator.PageQuery;
import org.jfw.apt.orm.annotation.dao.method.operator.PageSelect;
import org.jfw.apt.orm.annotation.dao.method.operator.QueryList;
import org.jfw.apt.orm.annotation.dao.method.operator.QueryOne;
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.annotation.dao.param.SqlColumn;
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.List;
@DAO
public abstract class OrgDao {
@Insert
public abstract int insert(Connection con, Organization org) throws SQLException;
@UpdateWith
@From(Organization.class)
public abstract int updateIsJoin(Connection con, String id, @Set String isJoin) throws SQLException;
@UpdateWith
@From(Organization.class)
public abstract int updateAuthStatus(Connection con, String id, @Set String authStatus) throws SQLException;
@Update
public abstract int update(Connection con, Organization org) throws SQLException;
@Nullable
@SelectOne
public abstract Organization query(Connection con, String id) throws SQLException;
@Nullable
@QueryOne
@From(Organization.class)
@Select(Organization.class)
public abstract EditOrganization queryEditOrg(Connection con, String id) throws SQLException;
// public EditOrganization queryEditOrg(Connection con,String id) throws
// SQLException{
// int index = 1;
// String sql = "SELECT
// FOR_SHORT,ORG_URL,FOUND_TIME,PROVINCE,CITY,SUBJECT,INDUSTRY,ORG_SIZE,QUALIFICATION,IS_JOIN,AUTH_STATUS,ORG_ATTR,ID,NAME,ORG_TYPE,CREATE_TIME,MODIFY_TIME,DESCP
// FROM ORGANIZATION WHERE ID = ?";
// PreparedStatement ps = con.prepareStatement(sql);
// try{
// ps.setString(index++,id);
// ResultSet rs = ps.executeQuery();
// try{
// if(rs.next()){
// EditOrganization editOrg = new EditOrganization();
// String forShort = rs.getString(1);
// if(rs.wasNull()){
// forShort = null;
// }
// editOrg.setForShort(forShort);
// String orgUrl = rs.getString(2);
// if(rs.wasNull()){
// orgUrl = null;
// }
// editOrg.setOrgUrl(orgUrl);
// String foundTime = rs.getString(3);
// if(rs.wasNull()){
// foundTime = null;
// }
// editOrg.setFoundTime(foundTime);
// String province = rs.getString(4);
// if(rs.wasNull()){
// province = null;
// }
// editOrg.setProvince(province);
// String city = rs.getString(5);
// if(rs.wasNull()){
// city = null;
// }
// editOrg.setCity(city);
// String subject = rs.getString(6);
// if(rs.wasNull()){
// subject = null;
// }
// editOrg.setSubject(subject);
// String industry = rs.getString(7);
// if(rs.wasNull()){
// industry = null;
// }
// editOrg.setIndustry(industry);
// String orgSize = rs.getString(8);
// if(rs.wasNull()){
// orgSize = null;
// }
// editOrg.setOrgSize(orgSize);
// String qualification = rs.getString(9);
// if(rs.wasNull()){
// qualification = null;
// }
// editOrg.setQualification(qualification);
// editOrg.setIsJoin(rs.getString(10));
// editOrg.setAuthStatus(rs.getString(11));
// String orgAttr = rs.getString(12);
// if(rs.wasNull()){
// orgAttr = null;
// }
// editOrg.setOrgAttr(orgAttr);
// editOrg.setId(rs.getString(13));
// editOrg.setName(rs.getString(14));
// String orgType = rs.getString(15);
// if(rs.wasNull()){
// orgType = null;
// }
// editOrg.setOrgType(orgType);
// editOrg.setCreateTime(rs.getString(16));
// editOrg.setModifyTime(rs.getString(17));
// String descp = rs.getString(18);
// if(rs.wasNull()){
// descp = null;
// }
// editOrg.setDescp(descp);
// return editOrg;
// }else{
// return null;
// }
// }finally{
// try{rs.close();}catch(Exception e1){}
// }
// }finally{
// try{ps.close();}catch(Exception e2){}
// }
// }
//
@SelectList
public abstract List<Organization> query(Connection con) throws SQLException;
@LimitQuery
public abstract List<Organization> limitQuery(Connection con, @Like String name, int rows) throws SQLException;
@LimitQuery
@From(Organization.class)
@OrderBy("ORDER BY NAME")
@Select(Organization.class)
public abstract List<EditOrganization> queryLimit(Connection con, @Like String name, int rows) throws SQLException;
@Nullable
@QueryVal
@Column(handlerClass = StringHandler.class, value = "id")
@From(Organization.class)
public abstract String queryByName(Connection con, String name) throws SQLException;
@Nullable
@QueryVal
@Column(handlerClass = StringHandler.class, value = "auth_status")
@From(Organization.class)
public abstract String queryAuthStatus(Connection con, String id) throws SQLException;
@PageSelect
public abstract PageQueryResult<Organization> query(Connection con, @Nullable @Like String name, @Nullable String orgType, int pageSize, int pageNo)
throws SQLException;
@From(Organization.class)
@LimitQuery
public abstract List<BaseOrganization> query(Connection con, @Nullable @Like String name, int rows) throws SQLException;
@From(Organization.class)
@DeleteWith
public abstract int delete(Connection con, String id) throws SQLException;
@From(Organization.class)
@UpdateWith
public abstract int updateSortNum(Connection con, String id, @Set long sortNum) throws SQLException;
@From(Organization.class)
@UpdateWith
@SetSentence("PAGE_VIEWS = PAGE_VIEWS + 1")
public abstract int incPageViews(Connection con, String id) throws SQLException;
@SelectOne
@Nullable
public abstract Organization query(Connection con, long shareId) throws SQLException;
@PageQuery
@From(Organization.class)
@OrderBy("ORDER BY SORT_NUM DESC,MODIFY_TIME DESC")
@Select(Organization.class)
@Or
public abstract PageQueryResult<EditOrganization> query(Connection con,
@SqlColumn(handlerClass = StringHandler.class, value = { "(NAME LIKE ? AND IS_JOIN ='1')", "(FOR_SHORT LIKE ? AND IS_JOIN ='1')",
"(DESCP LIKE ? AND IS_JOIN ='1')", "(SUBJECT LIKE ? AND IS_JOIN ='1') ", "(industry LIKE ? AND IS_JOIN ='1')",
"(ID IN (SELECT ORG_ID FROM RESOURCE WHERE RESOURCE_TYPE='2' AND RESOURCE_NAME LIKE ?) AND IS_JOIN ='1')",
"(ID IN (SELECT ORG_ID FROM ARTICLE WHERE ARTICLE_TYPE='2' AND ARTICLE_TITLE LIKE ?) AND IS_JOIN ='1')" }) String kw,
int pageSize, int pageNo) throws SQLException;
public List<EditOrganization> indexSearch(Connection con, String kw,long sortNum,String modifyTime,String id,int rows)throws SQLException {
int index = 1;
StringBuilder sql = new StringBuilder();
sql.append("SELECT FOR_SHORT,ORG_URL,FOUND_TIME,PROVINCE,CITY,SUBJECT,INDUSTRY,ORG_SIZE,QUALIFICATION,IS_JOIN,AUTH_STATUS,ADDR,EMAIL,CONTACT_NUM,FIELD_OF_CUSTOMER,FIELD_OF_SUPPLIER,SORT_NUM,PAGE_VIEWS,SHARE_ID,COL_MGR,RES_MGR,ORG_ATTR,ID,NAME,ORG_TYPE,CREATE_TIME,MODIFY_TIME,DESCP FROM ORGANIZATION");
sql.append(" WHERE (SORT_NUM < ? OR (SORT_NUM = ? AND MODIFY_TIME < ?) OR (SORT_NUM = ? AND MODIFY_TIME = ? AND ID < ?)) ");
if (kw != null) {
sql.append(" AND ((ID IN (SELECT ORG_ID FROM ARTICLE WHERE ARTICLE_TYPE='2' AND ARTICLE_TITLE LIKE ?) AND IS_JOIN ='1') OR (ID IN (SELECT ORG_ID FROM RESOURCE WHERE RESOURCE_TYPE='2' AND RESOURCE_NAME LIKE ?) AND IS_JOIN ='1') OR (industry LIKE ? AND IS_JOIN ='1') OR (SUBJECT LIKE ? AND IS_JOIN ='1') OR (DESCP LIKE ? AND IS_JOIN ='1') OR (FOR_SHORT LIKE ? AND IS_JOIN ='1') OR (NAME LIKE ? AND IS_JOIN ='1')) ");
}
sql.append(" ORDER BY SORT_NUM DESC, MODIFY_TIME DESC LIMIT ? ");
PreparedStatement ps = con.prepareStatement(sql.toString());
try {
ps.setLong(index++, sortNum);
ps.setLong(index++, sortNum);
ps.setString(index++, modifyTime);
ps.setLong(index++, sortNum);
ps.setString(index++, modifyTime);
ps.setString(index++, id);
if (kw != null) {
ps.setString(index++, kw);
ps.setString(index++, kw);
ps.setString(index++, kw);
ps.setString(index++, kw);
ps.setString(index++, kw);
ps.setString(index++, kw);
ps.setString(index++, kw);
}
ps.setInt(index++, rows);
ResultSet resultSet = ps.executeQuery();
try {
List<EditOrganization> orgs = new ArrayList<>();
while (resultSet.next()) {
EditOrganization organization = new EditOrganization();
String forshort = resultSet.getString(1);
if(resultSet.wasNull()){
forshort = null;
}
organization.setForShort(forshort);
String orgUrl = resultSet.getString(2);
if(resultSet.wasNull()){
orgUrl = null;
}
organization.setOrgUrl(orgUrl);
String foundTime = resultSet.getString(3);
if(resultSet.wasNull()){
foundTime = null;
}
organization.setFoundTime(foundTime);
String province = resultSet.getString(4);
if(resultSet.wasNull()){
province = null;
}
organization.setProvince(province);
String city = resultSet.getString(5);
if(resultSet.wasNull()){
city = null;
}
organization.setCity(city);
String subject = resultSet.getString(6);
if(resultSet.wasNull()){
subject = null;
}
organization.setSubject(subject);
String industry = resultSet.getString(7);
if(resultSet.wasNull()){
industry = null;
}
organization.setIndustry(industry);
String orgSize = resultSet.getString(8);
if(resultSet.wasNull()){
orgSize = null;
}
organization.setOrgSize(orgSize);
String qualification = resultSet.getString(9);
if(resultSet.wasNull()){
qualification = null;
}
organization.setQualification(qualification);
organization.setIsJoin(resultSet.getString(10));
organization.setAuthStatus(resultSet.getString(11));
String addr = resultSet.getString(12);
if(resultSet.wasNull()){
addr = null;
}
organization.setAddr(addr);
String email = resultSet.getString(13);
if(resultSet.wasNull()){
email = null;
}
organization.setEmail(email);
String contactNum = resultSet.getString(14);
if(resultSet.wasNull()){
contactNum = null;
}
organization.setContactNum(contactNum);
String fieldOfCustomer = resultSet.getString(15);
if(resultSet.wasNull()){
fieldOfCustomer = null;
}
organization.setFieldOfCustomer(fieldOfCustomer);
String fieldOfSupplier = resultSet.getString(16);
if(resultSet.wasNull()){
fieldOfSupplier = null;
}
organization.setFieldOfSupplier(fieldOfSupplier);
organization.setSortNum(resultSet.getLong(17));
organization.setPageViews(resultSet.getLong(18));
organization.setShareId(resultSet.getLong(19));
organization.setColMgr("1".equals(resultSet.getString(20)));
organization.setResMgr("1".equals(resultSet.getString(21)));
String orgAttr = resultSet.getString(22);
if(resultSet.wasNull()){
orgAttr = null;
}
organization.setOrgAttr(orgAttr);
organization.setId(resultSet.getString(23));
organization.setName(resultSet.getString(24));
String orgType = resultSet.getString(25);
if(resultSet.wasNull()){
orgType = null;
}
organization.setOrgType(orgType);
organization.setCreateTime(resultSet.getString(26));
organization.setModifyTime(resultSet.getString(27));
String descp = resultSet.getString(28);
if(resultSet.wasNull()){
descp = null;
}
organization.setDescp(descp);
orgs.add(organization);
}
return orgs;
} finally {
try {
resultSet.close();
} catch (Exception e1) {
}
}
} finally {
try {
ps.close();
} catch (Exception e2) {
}
}
}
@PageQuery
@Select(Organization.class)
@From(Organization.class)
@OrderBy(" ORDER BY SORT_NUM DESC,MODIFY_TIME DESC")
public abstract PageQueryResult<EditOrganization> queryByKwAndIndustryAndAddr(Connection con,
@GroupSqlColumn(isAnd=false, handlerClass = StringHandler.class, value = { "NAME LIKE ? AND IS_JOIN ='1'", "FOR_SHORT LIKE ? AND IS_JOIN ='1'",
"DESCP LIKE ? AND IS_JOIN ='1'", "SUBJECT LIKE ? AND IS_JOIN ='1' ", "industry LIKE ? AND IS_JOIN ='1'",
"ID IN (SELECT ORG_ID FROM RESOURCE WHERE RESOURCE_TYPE='2' AND RESOURCE_NAME LIKE ?) AND IS_JOIN ='1'",
"ID IN (SELECT ORG_ID FROM ARTICLE WHERE ARTICLE_TYPE='2' AND ARTICLE_TITLE LIKE ?) AND IS_JOIN ='1'" })
@Nullable String kw, @Nullable String industry,@Nullable String addr, int pageSize,
int pageNo) throws SQLException;
@PageQuery
@From(Organization.class)
@OrderBy("ORDER BY SORT_NUM DESC,MODIFY_TIME DESC")
@Select(Organization.class)
@Where("IS_JOIN ='1'")
public abstract PageQueryResult<EditOrganization> query(Connection con, int pageSize, int pageNo) throws SQLException;
@PageQuery
@From(Organization.class)
@OrderBy("ORDER BY SORT_NUM DESC,MODIFY_TIME DESC")
@Select(Organization.class)
@Where("IS_JOIN ='1'")
public abstract PageQueryResult<EditOrganization> queryByIndustryAndAddr(Connection con, @Like String industry, @Like String addr, int pageSize, int pageNo)
throws SQLException;
@QueryList
@From(Organization.class)
@Select(Organization.class)
public abstract List<EditOrganization> query(Connection con, @In String[] id) throws SQLException;
@LimitQuery
@Select(Organization.class)
@From(Organization.class)
@OrderBy(" ORDER BY SORT_NUM DESC,PAGE_VIEWS DESC,SHARE_ID ASC")
@Or
public abstract List<EditOrganization> relateQuery(Connection con, @Nullable @SqlColumn(value = { "(IS_JOIN='1' AND NAME LIKE ?)",
"(IS_JOIN='1' AND FOR_SHORT LIKE ?)" }, handlerClass = StringHandler.class) String kw, int rows) throws SQLException;
public String[] queryOrgIdWithSameKeyWord(Connection con, String id, int rows) throws SQLException {
List<String> ret = new ArrayList<String>();
String sql = "select id,count(1) sort from org_key_word where kw in (select kw from org_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()]);
}
}
|
