|
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.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 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;
@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()]);
}
}
|
