|
package com.ekexiu.portal.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
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.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 com.ekexiu.portal.basepo.BaseOrganization;
import com.ekexiu.portal.po.Organization;
import com.ekexiu.portal.pojo.EditOrganization;
@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 PageQueryResult<EditOrganization> queryByKwAndIndustryAndAddr(Connection con,String kw,String industry,String addr,int pageSize,int pageNo)throws SQLException{
int _m_1 = 0;
org.jfw.util.PageQueryResult<com.ekexiu.portal.pojo.EditOrganization> _result = new org.jfw.util.PageQueryResult<com.ekexiu.portal.pojo.EditOrganization>();
int _m_2 = 1;
String sql = null;
sql = " WHERE INDUSTRY LIKE ? AND ADDR LIKE ? 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'))";
String _m_3 = sql;
sql = "SELECT COUNT(1) FROM ORGANIZATION";
sql = sql + _m_3;
java.sql.PreparedStatement ps = con.prepareStatement(sql);
try{
ps.setString(_m_2++,industry);
ps.setString(_m_2++,addr);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
_result.setPageSize(pageSize);
java.sql.ResultSet _pageRs = ps.executeQuery();
try{
_pageRs.next();
_m_1 = _pageRs.getInt(1);
}finally{
try{_pageRs.close();}catch(Exception _m_4){}
}
}finally{
try{ps.close();}catch(Exception _m_5){}
}
_result.setTotal(_m_1);
if(0== _m_1){
_result.setPageNo(1);
_result.setData(java.util.Collections.<com.ekexiu.portal.pojo.EditOrganization>emptyList());
return _result;
}
_m_2 = 1;
boolean _m_6 = (1 == pageNo);
if(_m_6){
_result.setPageNo(1);
sql = "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,ORG_ATTR,ID,NAME,ORG_TYPE,CREATE_TIME,MODIFY_TIME,DESCP FROM ORGANIZATION";
sql = sql + _m_3;
sql = sql + " ORDER BY SORT_NUM DESC,MODIFY_TIME DESC";
sql = sql + " LIMIT " + pageSize;
}else{
int _m_7 = _m_1 / pageSize;
if(_m_1 % pageSize != 0){
++_m_7;
}
if(pageNo > _m_7){
pageNo = _m_7;
}
_result.setPageNo(pageNo);
--pageNo;
int _m_8 = (pageNo * pageSize);
sql = "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,ORG_ATTR,ID,NAME,ORG_TYPE,CREATE_TIME,MODIFY_TIME,DESCP FROM ORGANIZATION";
sql = sql + _m_3;
sql = sql + " ORDER BY SORT_NUM DESC,MODIFY_TIME DESC";
sql = sql + " LIMIT " + pageSize+ " OFFSET "+_m_8;
}
ps = con.prepareStatement(sql);
try{
ps.setString(_m_2++,industry);
ps.setString(_m_2++,addr);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
ps.setString(_m_2++,kw);
java.sql.ResultSet rs = ps.executeQuery();
try{
java.util.List<com.ekexiu.portal.pojo.EditOrganization> _m_9 = new java.util.ArrayList<com.ekexiu.portal.pojo.EditOrganization>();
_result.setData(_m_9);
int _m_10 = 0;
while((_m_10<pageSize) && rs.next()){
++_m_10;
com.ekexiu.portal.pojo.EditOrganization _obj = new com.ekexiu.portal.pojo.EditOrganization();
java.lang.String _m_11 = rs.getString(1);
if(rs.wasNull()){
_m_11 = null;
}
_obj.setForShort(_m_11);
java.lang.String _m_12 = rs.getString(2);
if(rs.wasNull()){
_m_12 = null;
}
_obj.setOrgUrl(_m_12);
java.lang.String _m_13 = rs.getString(3);
if(rs.wasNull()){
_m_13 = null;
}
_obj.setFoundTime(_m_13);
java.lang.String _m_14 = rs.getString(4);
if(rs.wasNull()){
_m_14 = null;
}
_obj.setProvince(_m_14);
java.lang.String _m_15 = rs.getString(5);
if(rs.wasNull()){
_m_15 = null;
}
_obj.setCity(_m_15);
java.lang.String _m_16 = rs.getString(6);
if(rs.wasNull()){
_m_16 = null;
}
_obj.setSubject(_m_16);
java.lang.String _m_17 = rs.getString(7);
if(rs.wasNull()){
_m_17 = null;
}
_obj.setIndustry(_m_17);
java.lang.String _m_18 = rs.getString(8);
if(rs.wasNull()){
_m_18 = null;
}
_obj.setOrgSize(_m_18);
java.lang.String _m_19 = rs.getString(9);
if(rs.wasNull()){
_m_19 = null;
}
_obj.setQualification(_m_19);
_obj.setIsJoin(rs.getString(10));
_obj.setAuthStatus(rs.getString(11));
java.lang.String _m_20 = rs.getString(12);
if(rs.wasNull()){
_m_20 = null;
}
_obj.setAddr(_m_20);
java.lang.String _m_21 = rs.getString(13);
if(rs.wasNull()){
_m_21 = null;
}
_obj.setEmail(_m_21);
java.lang.String _m_22 = rs.getString(14);
if(rs.wasNull()){
_m_22 = null;
}
_obj.setContactNum(_m_22);
java.lang.String _m_23 = rs.getString(15);
if(rs.wasNull()){
_m_23 = null;
}
_obj.setFieldOfCustomer(_m_23);
java.lang.String _m_24 = rs.getString(16);
if(rs.wasNull()){
_m_24 = null;
}
_obj.setFieldOfSupplier(_m_24);
_obj.setSortNum(rs.getLong(17));
_obj.setPageViews(rs.getLong(18));
_obj.setShareId(rs.getLong(19));
java.lang.String _m_25 = rs.getString(20);
if(rs.wasNull()){
_m_25 = null;
}
_obj.setOrgAttr(_m_25);
_obj.setId(rs.getString(21));
_obj.setName(rs.getString(22));
java.lang.String _m_26 = rs.getString(23);
if(rs.wasNull()){
_m_26 = null;
}
_obj.setOrgType(_m_26);
_obj.setCreateTime(rs.getString(24));
_obj.setModifyTime(rs.getString(25));
java.lang.String _m_27 = rs.getString(26);
if(rs.wasNull()){
_m_27 = null;
}
_obj.setDescp(_m_27);
_m_9.add(_obj);
}
return _result;
}finally{
try{rs.close();}catch(Exception _m_28){}
}
}finally{
try{ps.close();}catch(Exception _m_29){}
}
}
@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()]);
}
}
|
