province_code-----Char(2) --》Character
cert-----Clob 類型 ---》SerializableClob
certstate------Number類型 --》BigDecimal
usercode------Varchar2類型 ---》String
addtime ----- Date 類型 --
public List<CertInfoListView> getInfoList(CertInfoListView view) throws DAOException 
{
List<CertInfoListView> liinfo = new ArrayList<CertInfoListView>();
// String sql="select b.usercode,b.agentcode,i.sn,i.cert,i.certstate,i.endtime, i.ipasskeyno,b.id,b.addtime from certbind b inner join certinfo i on b.ipasskeyno=i.ipasskeyno";
// String sql="select b.usercode,b.agentcode,i.sn,i.cert,i.certstate,i.endtime, i.ipasskeyno,b.id,b.addtime,b.agenttype from certbind b inner join cert_info_bind_view i on b.ipasskeyno=i.ipasskeyno";
String sql="select v.usercode,v.agentcode,v.sn,v.cert,v.certstate,v.infoendtime, v.ipasskeyno,v.bindid,v.bindaddtime,v.agenttype,s.staff_name as username,s.province_code as provincecode,s.eparchy_code as citycode from tf_m_staff s inner join cert_info_ipasskey_view v on s.staff_id=v.usercode";
Session session = HibernateTemplate.getInstance().getSession();
StringBuffer sb = new StringBuffer(sql);
sb.append(" where 1=1 ");
if(!CheckEmpty.isEmpty(view.getIpasskeyno())){
sb.append(" and v.ipasskeyno ='").append(view.getIpasskeyno()).append("'");
}
if(!CheckEmpty.isEmpty(view.getAgentcode())){
sb.append(" and v.agentcode ='").append(view.getAgentcode()).append("'");
}
if(!CheckEmpty.isEmpty(view.getSn())){
sb.append(" and v.sn ='").append(view.getSn()).append("'");
}
if(!CheckEmpty.isEmpty(view.getUsercode())){
sb.append(" and v.usercode ='").append(view.getUsercode()).append("'");
}
if(view.getAgenttype()!=0){
sb.append(" and v.agenttype=").append(view.getAgenttype());
}
if(view.getCertstate()!=0){
sb.append(" and v.certstate!=").append(view.getCertstate());
}
if(view.getProvincecode()!=null){
sb.append(" and s.province_code='").append(view.getProvincecode()).append("'");
}
if(view.getCitycode()!=null){
sb.append(" and s.eparchy_code='").append(view.getCitycode()).append("'");
}
sb.append(" order by ipasskeyno ,bindaddtime desc");
Query q = session.createSQLQuery(sb.toString());
int pageno = view.getPageno();
int size = view.getPagesize();
if(pageno!=0&&size!=0){
q.setFirstResult((pageno-1)*size);
q.setMaxResults(size);
}
List list = q.list();
Iterator it = list.iterator();
while(it.hasNext()){
//v.usercode,v.agentcode,v.sn,v.cert,v.certstate,v.infoendtime, v.ipasskeyno,v.bindid,
Object[] obj = (Object[])it.next();
CertInfoListView c = new CertInfoListView();
for(int i=0;i<obj.length;i++){
if(!CheckEmpty.isEmpty((String)obj[0])){
c.setUsercode((String)obj[0]);
}
if(!CheckEmpty.isEmpty((String)obj[1])){
c.setAgentcode((String)obj[1]);
}
if(!CheckEmpty.isEmpty((String)obj[2])){
c.setSn((String)obj[2]);
}
if(obj[3]!=null){
SerializableClob sc = (SerializableClob)obj[3];
String cc = null;
if(sc!=null){
try {
cc = sc.getSubString(1, (int)sc.length());
} catch (SQLException e) {
}
}
if(cc!=null)c.setCert(cc);
}
if(obj[4]!=null){
BigDecimal b = (BigDecimal)obj[4];
c.setCertstate(b.intValue());
}
if((obj[5])!=null){
c.setEndtime(((Date)obj[5]));
}
if(!CheckEmpty.isEmpty((String)obj[6])){
c.setIpasskeyno((String)obj[6]);
}
c.setCertbindid(Integer.parseInt((String)obj[7]));
// v.bindaddtime,v.agenttype,s.staff_name as username,
c.setAddtime((Date)obj[8]);
if(obj[9]!=null){
BigDecimal b = (BigDecimal)obj[9];
c.setAgenttype(b.intValue());
}
if(!CheckEmpty.isEmpty((String)obj[10])){
c.setUsername((String)obj[10]);
}
// s.province_code as provincecode,s.eparchy_code as citycode
if(obj[11]!=null){
Character ch = (Character)obj[11];
c.setProvincecode(ch.toString());
}
if(obj[12]!=null){
Character ch = (Character)obj[12];
c.setCitycode(ch.toString());
}
}
liinfo.add(c);
}
if(session!=null&&session.isConnected())session.close();
return liinfo;
}posted on 2009-08-31 16:26
David1228 閱讀(510)
評論(0) 編輯 收藏 所屬分類:
Hibernate/ibatis