posts - 167,  comments - 30,  trackbacks - 0
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 阅读(485) 评论(0)  编辑  收藏 所属分类: Hibernate/ibatis

只有注册用户登录后才能发表评论。


网站导航:
 

<2009年8月>
2627282930311
2345678
9101112131415
16171819202122
23242526272829
303112345

常用链接

留言簿(4)

随笔分类

随笔档案

文章档案

新闻分类

新闻档案

相册

收藏夹

Java

Linux知识相关

Spring相关

云计算/Linux/虚拟化技术/

友情博客

多线程并发编程

开源技术

持久层技术相关

搜索

  •  

积分与排名

  • 积分 - 353900
  • 排名 - 155

最新评论

阅读排行榜

评论排行榜