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;
}