jsp页面15个输入条件框 多条件模糊查询方法

1.在Action中

  1.1 封装 jsp页面表单属性：

    private String taxpayerId;
    private String taxpayerNumber;
    private String taxpayerName;
    private String dealAddress;
    private String businessOrganName;
    private String dealScope;
    private String taxpayerState;
    private String recroadType;
    private String industry;
    private String chargeTaxOrgan;
    private String chargeTaxOfficer;
    private String streetVillageTowns;
    private Date startTime;
    private Date endTime;

 【注:】这些属性对应jsp页面输入控件名称

 

   1.2 调用Service层

2.在Service中

   2.1 把封装好的属性传入Service层

    int count = taxpayerInfoService.countTaxpayerList(taxpayerId, taxpayerNumber, taxpayerName, dealAddress, businessOrganName , dealScope,
                taxpayerState, recroadType, industry, chargeTaxOrgan, chargeTaxOfficer, streetVillageTowns, sTime, eTime);
       
        List ls = taxpayerInfoService.getTaxpayerList(taxpayerId, taxpayerNumber, taxpayerName, dealAddress, businessOrganName , dealScope,
                taxpayerState, recroadType, industry, chargeTaxOrgan, chargeTaxOfficer, streetVillageTowns, sTime, eTime, offset, pageSize);
        if(count > 0 && ls.isEmpty()){
            ls = taxpayerInfoService.getTaxpayerList(taxpayerId, taxpayerNumber, taxpayerName, dealAddress, businessOrganName , dealScope,
                    taxpayerState, recroadType, industry, chargeTaxOrgan, chargeTaxOfficer, streetVillageTowns, sTime, eTime,offset-pageSize, pageSize);
        }

3.在DAO中

 3.1 在DAO中先进行非空判断

     3.1.1 先得统计符合查询条件的记录总数

    /**
     * 统计符合查询条件的纳税人
     */
    public int countTaxpayerList(String _taxpayerId, String _taxpayerNumber,
            String _taxpayerName, String _dealAddress, String _businessOrganName,
            String _dealScope, String _taxpayerState, String _recroadType,
            String _industry, String _chargeTaxOrgan, String _chargeTaxOfficer,
            String _streetVillageTowns, String _startTime, String _endTime) {
        List params = new ArrayList();
        Date d1 = null;
        Date d2 = null;
        if( _startTime != null && !"".equals(_startTime)){
            d1 =  java.sql.Date.valueOf(_startTime);
        }
        if( _endTime != null && !"".equals(_endTime)){
            d2 =  java.sql.Date.valueOf(_endTime); 
        }
        String taxpayerId = "%"+_taxpayerId+"%";
        String taxpayerNumber = "%"+_taxpayerNumber+"%";
        String taxpayerName ="%"+_taxpayerName+"%";
        String dealAddress = "%"+_dealAddress+"%";
        String businessOrganName ="%"+_businessOrganName+"%";
        String dealScope ="%"+_dealScope+"%";
        String taxpayerState = "%"+_taxpayerState+"%";
        String recroadType ="%"+_recroadType+"%";
        String industry ="%"+_industry+"%";
        String chargeTaxOrgan ="%"+_chargeTaxOrgan+"%";
        String chargeTaxOfficer ="%"+_chargeTaxOfficer+"%";
        String streetVillageTowns ="%"+_streetVillageTowns+"%";
        Date startTime = d1;
        Date endTime = d2;
       
        StringBuilder buffer = new StringBuilder();
        buffer.append(" select count(*) ");
        buffer.append(" from taxpayer_info tax");
        buffer.append(" where 1=1 ");
       
        if(_taxpayerId != null && !_taxpayerId.equals("")){
            buffer.append(" and tax.taxpayer_id like ? ");
            params.add(taxpayerId);
        }
        if(_taxpayerNumber != null && !_taxpayerNumber.equals("")){
            buffer.append(" and tax.taxpayer_number like ? ");
            params.add(taxpayerNumber);
        }
        if(_taxpayerName != null && !_taxpayerName.equals("")){
            buffer.append(" and tax.taxpayer_name like ? ");
            params.add(taxpayerName);
        }
        if(_dealAddress != null && !_dealAddress.equals("")){
            buffer.append(" and tax.taxpayer_deal_address like ? ");
            params.add(dealAddress);
        }
        if(_businessOrganName != null && !_businessOrganName.equals("")){
            buffer.append(" and tax.business_organ_name like ? ");
            params.add(businessOrganName);
        }
        if(_dealScope != null && !_dealScope.equals("")){
            buffer.append(" and tax.taxpayer_deal_scope like ? ");
            params.add(dealScope);
        }
        if(_taxpayerState != null && !_taxpayerState.equals("")){
            buffer.append(" and tax.taxpayer_state like ? ");
            params.add(taxpayerState);
        }
        if(_recroadType != null && !_recroadType.equals("")){
            buffer.append(" and tax.recroad_type like ? ");
            params.add(recroadType);
        }
        if(_industry != null && !_industry.equals("")){
            buffer.append(" and tax.industry like ? ");
            params.add(industry);
        }
        if(_chargeTaxOrgan != null && !_chargeTaxOrgan.equals("")){
            buffer.append(" and tax.charge_tax_organ like ? ");
            params.add(chargeTaxOrgan);
        }
        if(_chargeTaxOfficer != null && !_chargeTaxOfficer.equals("")){
            buffer.append(" and tax.charge_tax_officer like ? ");
            params.add(chargeTaxOfficer);
        }
        if(_streetVillageTowns != null && !_streetVillageTowns.equals("")){
            buffer.append(" and tax.street_village_towns like ? ");
            params.add(streetVillageTowns);
        }
        if(_startTime!=null){
            buffer.append(" and tax.validity_start>=? ");
            params.add(startTime);
        }
        if(_endTime!=null){
            buffer.append(" and tax.validity_end<=? ");
            params.add(endTime);
        }
        final String hql = buffer.toString();
        final Object[] ps = params.toArray();
        Integer count = (Integer)this.getHibernateTemplate().execute(new HibernateCallback(){
            public Object doInHibernate(Session session)throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(hql);
                if( ps != null && ps.length > 0 ){
                    for( int i= 0;i < ps.length; i++ ){
                        query.setParameter(i, ps[i]);
                    }
                }
                return ((Integer)query.list().iterator().next()).intValue();
               
            }
        });
        return count;
    }// end countTaxpayerList方法
    3.1.2 符合多条件查询的集合List
    /**
     * 返回符合查询条件的纳税人信息
     */
    public List<TaxpayerInfo> getTaxpayerList(String _taxpayerId, String _taxpayerNumber,
            String _taxpayerName, String _dealAddress, String _businessOrganName,
            String _dealScope, String _taxpayerState, String _recroadType,
            String _industry, String _chargeTaxOrgan, String _chargeTaxOfficer,
            String _streetVillageTowns, String _startTime, String _endTime, final int firstResult, final int maxResult) {
   
        List params = new ArrayList();
        Date d1 = null;
        Date d2 = null;
        if( _startTime != null && !"".equals(_startTime)){
            d1 =  java.sql.Date.valueOf(_startTime);
        }
        if( _endTime != null && !"".equals(_endTime)){
            d2 =  java.sql.Date.valueOf(_endTime); 
        }
        String taxpayerId = "%"+_taxpayerId+"%";
        String taxpayerNumber = "%"+_taxpayerNumber+"%";
        String taxpayerName ="%"+_taxpayerName+"%";
        String dealAddress = "%"+_dealAddress+"%";
        String businessOrganName ="%"+_businessOrganName+"%";
        String dealScope ="%"+_dealScope+"%";
        String taxpayerState = "%"+_taxpayerState+"%";
        String recroadType ="%"+_recroadType+"%";
        String industry ="%"+_industry+"%";
        String chargeTaxOrgan ="%"+_chargeTaxOrgan+"%";
        String chargeTaxOfficer ="%"+_chargeTaxOfficer+"%";
        String streetVillageTowns ="%"+_streetVillageTowns+"%";
        Date startTime =d1;
        Date endTime =d2;
       
        StringBuilder buffer = new StringBuilder();
        buffer.append("select tax.taxpayer_state, tax.taxpayer_id, tax.taxpayer_name, tax.recroad_type,tax.charge_tax_organ,tax.street_village_towns,tax.charge_tax_officer");
        buffer.append(" from taxpayer_info tax");
        buffer.append(" where 1=1 ");
       
        if(_taxpayerId != null && !_taxpayerId.equals("")){
            buffer.append(" and tax.taxpayer_id like ? ");
            params.add(taxpayerId);
        }
        if(_taxpayerNumber != null && !_taxpayerNumber.equals("")){
            buffer.append(" and tax.taxpayer_number like ? ");
            params.add(taxpayerNumber);
        }
        if(_taxpayerName != null && !_taxpayerName.equals("")){
            buffer.append(" and tax.taxpayer_name like ? ");
            params.add(taxpayerName);
        }
        if(_dealAddress != null && !_dealAddress.equals("")){
            buffer.append(" and tax.taxpayer_deal_address like ? ");
            params.add(dealAddress);
        }
        if(_businessOrganName != null && !_businessOrganName.equals("")){
            buffer.append(" and tax.business_organ_name like ? ");
            params.add(businessOrganName);
        }
        if(_dealScope != null && !_dealScope.equals("")){
            buffer.append(" and tax.taxpayer_deal_scope like ? ");
            params.add(dealScope);
        }
        if(_taxpayerState != null && !_taxpayerState.equals("")){
            buffer.append(" and tax.taxpayer_state like ? ");
            params.add(taxpayerState);
        }
        if(_recroadType != null && !_recroadType.equals("")){
            buffer.append(" and tax.recroad_type like ? ");
            params.add(recroadType);
        }
        if(_industry != null && !_industry.equals("")){
            buffer.append(" and tax.industry like ? ");
            params.add(industry);
        }
        if(_chargeTaxOrgan != null && !_chargeTaxOrgan.equals("")){
            buffer.append(" and tax.charge_tax_organ like ? ");
            params.add(chargeTaxOrgan);
        }
        if(_chargeTaxOfficer != null && !_chargeTaxOfficer.equals("")){
            buffer.append(" and tax.charge_tax_officer like ? ");
            params.add(chargeTaxOfficer);
        }
        if(_streetVillageTowns != null && !_streetVillageTowns.equals("")){
            buffer.append(" and tax.street_village_towns like ? ");
            params.add(streetVillageTowns);
        }
        if(_startTime!=null){
            buffer.append(" and tax.validity_start>=? ");
            params.add(startTime);
        }
        if(_endTime!=null){
            buffer.append(" and tax.validity_end<=? ");
            params.add(endTime);
        }

        buffer.append(" order by tax.tax_info_id desc");
        final String hql = buffer.toString();
        final Object[] ps = params.toArray();
        List ls =  this.getHibernateTemplate().executeFind(new HibernateCallback(){
            public Object doInHibernate(Session session)throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(hql);
                if( ps != null && ps.length > 0 ){
                    for( int i = 0;i < ps.length; i++ ){
                        query.setParameter(i, ps[i]);
                    }
                }
               
                query.setFirstResult(firstResult);
                query.setMaxResults(maxResult);
                return     query.list();
            }
        });
       
        List result = new ArrayList();
        for( int i = 0; i < ls.size(); i++ ){
            Object[] objs = (Object[])ls.get(i);
            TaxpayerInfo taxpayerInfo = new TaxpayerInfo();
            taxpayerInfo.setTaxpayerState((String)objs[0]);
            taxpayerInfo.setTaxpayerId((String)objs[1]);
            taxpayerInfo.setTaxpayerName((String)objs[2]);
            taxpayerInfo.setRecroadType((String)objs[3]);
            taxpayerInfo.setChargeTaxOrgan((String)objs[4]);
            taxpayerInfo.setStreetVillageTowns((String)objs[5]);
            taxpayerInfo.setChargeTaxOfficer((String)objs[6]);
            result.add(taxpayerInfo);
        }
        return result;
    }