queryutil.java

来自「基于SSH (struts+spring+hibernate)框架设计的 C」· Java 代码 · 共 223 行

package com.csu.crm.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.csu.crm.common.DBConnection;
import com.csu.crm.common.dao.original.CrmAreaDAO;
import com.csu.crm.common.dao.original.CrmBankDAO;
import com.csu.crm.common.dao.original.CrmCuLevDAO;
import com.csu.crm.common.dao.original.CrmCuStateDAO;
import com.csu.crm.common.dao.original.CrmCustomerDAO;
import com.csu.crm.common.dao.original.CrmEntCreditDAO;
import com.csu.crm.common.dao.original.CrmEntIndustryDAO;
import com.csu.crm.common.dao.original.CrmEntPropertyDAO;
import com.csu.crm.common.page.PageinationVO;
import com.csu.crm.common.page.PaginationDAO;
import com.csu.crm.common.vo.CrmCustomer;
import com.csu.crm.customer.vo.CrmCustomerVO;
/**
 * 此类统一处理 当查询带有条件时,进行匹配查询
 * 
 * @author 3eCRM小组：曾东
 * @since Oct 2, 2007 10:43:31 PM
 * @version 1.0 创建时间：Oct 2, 2007 10:43:31 PM,初始版本
 */
public class QueryUtil {
	private static final Log log = LogFactory.getLog(QueryUtil.class);
	private CrmAreaDAO crmAreaDAO;
	private CrmEntIndustryDAO crmEntIndustryDAO;
	private CrmEntPropertyDAO crmEntPropertyDAO;
	private CrmBankDAO crmBankDAO;
	private CrmCuLevDAO crmCuLevDAO;
	private CrmCuStateDAO crmCuStateDAO;
	private CrmEntCreditDAO crmEntCreditDAO;
	private CrmCustomerDAO crmCustomerDAO;
	private PaginationDAO paginationDAO;	
	public CrmEntIndustryDAO getCrmEntIndustryDAO() {
		return crmEntIndustryDAO;
	}
	public void setCrmEntIndustryDAO(CrmEntIndustryDAO crmEntIndustryDAO) {
		this.crmEntIndustryDAO = crmEntIndustryDAO;
	}
	public CrmEntPropertyDAO getCrmEntPropertyDAO() {
		return crmEntPropertyDAO;
	}
	public void setCrmEntPropertyDAO(CrmEntPropertyDAO crmEntPropertyDAO) {
		this.crmEntPropertyDAO = crmEntPropertyDAO;
	}
	public CrmBankDAO getCrmBankDAO() {
		return crmBankDAO;
	}
	public void setCrmBankDAO(CrmBankDAO crmBankDAO) {
		this.crmBankDAO = crmBankDAO;
	}
	public CrmCuLevDAO getCrmCuLevDAO() {
		return crmCuLevDAO;
	}
	public void setCrmCuLevDAO(CrmCuLevDAO crmCuLevDAO) {
		this.crmCuLevDAO = crmCuLevDAO;
	}
	public CrmCuStateDAO getCrmCuStateDAO() {
		return crmCuStateDAO;
	}
	public void setCrmCuStateDAO(CrmCuStateDAO crmCuStateDAO) {
		this.crmCuStateDAO = crmCuStateDAO;
	}
	public CrmEntCreditDAO getCrmEntCreditDAO() {
		return crmEntCreditDAO;
	}
	public void setCrmEntCreditDAO(CrmEntCreditDAO crmEntCreditDAO) {
		this.crmEntCreditDAO = crmEntCreditDAO;
	}
	public CrmAreaDAO getCrmAreaDAO() {
		return crmAreaDAO;
	}
	public void setCrmAreaDAO(CrmAreaDAO crmAreaDAO) {
		this.crmAreaDAO = crmAreaDAO;
	}
	public CrmCustomerDAO getCrmCustomerDAO() {
		return crmCustomerDAO;
	}
	public void setCrmCustomerDAO(CrmCustomerDAO crmCustomerDAO) {
		this.crmCustomerDAO = crmCustomerDAO;
	}
	public PaginationDAO getPaginationDAO() {
		return paginationDAO;
	}
	public void setPaginationDAO(PaginationDAO paginationDAO) {
		this.paginationDAO = paginationDAO;
	}	
	/**
	 * 传入查询条件进行分页查询
	 * 当obj和strO都为null时,查询全部
	 * 
	 * @param obj 要匹配的模板对象
	 * @param strO 要匹配的字符串(仍为object)
	 * @param currPage 当前页
	 * @param pageSize 每页页数
	 * @return
	 */
	public PageinationVO query(String target,Object obj,Object strO,PageinationVO pageinationVO) {
		if(Constant.CRM_CUSTOMER.equals(target)) pageinationVO = queryCrmCustomer(obj,strO,pageinationVO);
		return pageinationVO;
	}
	
	/**
	 * 查询Customer表
	 * 
	 * @param obj
	 * @param StrO
	 * @return
	 */
	private PageinationVO queryCrmCustomer(Object obj,Object strO,PageinationVO pageinationVO) {
		//原始查询语句
		String baseSQL = "SELECT * FROM CRM_CUSTOMER ORDER BY CUSTOMER_ID";
		if(obj != null || strO != null) baseSQL = baseSQL + " WHERE ";		
		//如果有模板匹配条件
		if(obj != null) {
			CrmCustomer crmCustomer = (CrmCustomer)obj;
			String tmp = "";
			if(crmCustomer.getCrmArea()!= null) tmp +=" AREA_ID=" +  crmCustomer.getCrmArea().getAreaId() + " AND "; 
			if(crmCustomer.getCrmBank() != null) tmp +=" BANK_ID=" + crmCustomer.getCrmBank().getBankId() + " AND ";
			if(crmCustomer.getCrmCuLev() != null) tmp +=" CU_LEV_ID=" + crmCustomer.getCrmCuLev().getCuLevId() + " AND ";
			if(crmCustomer.getCrmCuState() != null) tmp +=" CU_STATE_ID=" + crmCustomer.getCrmCuState().getCuStateId() + " AND ";
			if(crmCustomer.getCrmEntCredit() != null) tmp +=" CREDIT_ID=" + crmCustomer.getCrmEntCredit().getCreditId() + " AND ";
			if(crmCustomer.getCrmEntIndustry() != null) tmp +=" INDUSTRY_ID=" + crmCustomer.getCrmEntIndustry().getIndustryId() + " AND ";
			if(crmCustomer.getCrmEntProperty() != null) tmp +=" PROPERTY_ID=" + crmCustomer.getCrmEntProperty().getPropertyId() + " AND ";			
			baseSQL = baseSQL + tmp;
			log.warn("[QueryUtil]---[queryCrmCustomer]---[有模板匹配条件]---[baseSQL=]---" + baseSQL);			
		}
		//如果有字符串匹配
		if(strO != null) {
			String tmp = " ENTERPRISE like " + strO ;
			baseSQL = baseSQL + tmp;
			log.warn("[QueryUtil]---[queryCrmCustomer]---[有字符串匹配]---[baseSQL=]---" + baseSQL);	
		}
		//去掉AND后缀
		/** 
		 * 如
		 * SELECT * FROM CRM_CUSTOMER ORDER BY CUSTOMER_ID WHERE  AREA_ID= AND 
		 * 01234567890123456789012345678901234567890123456789012345678901234567 
		 * 这样一个生成的sql语句
		 * 应去除AND后缀
		 * 总长68(包括最后一个空格)
		 * AND中的"A"位于第64(0开始)
		 * 应把后面4位扔掉
		 */
		if(baseSQL.lastIndexOf("AND") == baseSQL.length()-4) {
			baseSQL = baseSQL.substring(0,baseSQL.length()-5);
			log.warn("[QueryUtil]---[queryCrmCustomer]---[有多余的AND后缀]");
		} 
		
		log.warn("[QueryUtil]---[queryCrmCustomer]---[最终匹配的SQL语句]---[baseSQL=]---" + baseSQL);
		
		
		DBConnection dBConnection = new DBConnection();		
		Connection connection=null;
		ResultSet resultSet = null;
		try {
			connection = dBConnection.getConnection(dBConnection.ORACLE_LOCAL_ZD);
			paginationDAO.setNPageNo(pageinationVO.getNPageNo());
			paginationDAO.setNPageRows(pageinationVO.getNPageRows());
			
			PreparedStatement psSum = null;
			PreparedStatement psPage = null;			
			
			psSum = paginationDAO.makeSumPrepareStatement(connection, baseSQL);
			psPage = paginationDAO.makePagePrepareStatement(connection, baseSQL);
			
			int i = 1;
			paginationDAO.getSumNumberBySQL(psSum);
			// 总页数
			pageinationVO.setNPageSum(paginationDAO.getNPageSum());

			// 总记录数
			pageinationVO.setNTotal(paginationDAO.getNTotal());
			i = 1;
			
			resultSet = paginationDAO.executeQuery(psPage, i);
			while(resultSet.next()){
				CrmCustomerVO crmCustomerVO= new CrmCustomerVO();
				
				if(resultSet.getString("AREA_ID") != null) crmCustomerVO.setCrmArea(crmAreaDAO.findById(resultSet.getString("AREA_ID")).getCity());
				if(resultSet.getString("INDUSTRY_ID") != null)  crmCustomerVO.setCrmEntIndustry(crmEntIndustryDAO.findById(resultSet.getString("INDUSTRY_ID")).getIndustry());
				if(resultSet.getString("PROPERTY_ID") != null)  crmCustomerVO.setCrmEntProperty(crmEntPropertyDAO.findById(resultSet.getString("PROPERTY_ID")).getProperty());
				if(resultSet.getString("BANK_ID") != null)  crmCustomerVO.setCrmBank(crmBankDAO.findById(resultSet.getString("BANK_ID")).getBank());
				if(resultSet.getString("CU_LEV_ID") != null)  crmCustomerVO.setCrmCuLev(crmCuLevDAO.findById(resultSet.getString("CU_LEV_ID")).getCuLev());
				if(resultSet.getString("CU_STATE_ID") != null) crmCustomerVO.setCrmCuState(crmCuStateDAO.findById(resultSet.getString("CU_STATE_ID")).getCuState());
				if(resultSet.getString("CREDIT_ID") != null)  crmCustomerVO.setCrmEntCredit(crmEntCreditDAO.findById(resultSet.getString("CREDIT_ID")).getCredit());
	
				if(resultSet.getString("ACCOUNT") != null)  crmCustomerVO.setAccount(resultSet.getString("ACCOUNT"));
				if(resultSet.getString("ADDR") != null)  crmCustomerVO.setAddr(resultSet.getString("ADDR"));				
				if(resultSet.getString("CUSTOMER_ID") != null)  crmCustomerVO.setCustomerId(resultSet.getString("CUSTOMER_ID"));
				if(resultSet.getString("ENTERPRISE") != null)  crmCustomerVO.setEnterprise(resultSet.getString("ENTERPRISE"));
				if(resultSet.getString("SITE") != null)  crmCustomerVO.setSite(resultSet.getString("SITE"));
				if(resultSet.getString("TEL") != null)  crmCustomerVO.setTel(resultSet.getString("TEL"));
				if(resultSet.getString("TAX") != null)  crmCustomerVO.setTax(resultSet.getString("TAX"));
				if(resultSet.getString("EMAIL") != null)  crmCustomerVO.setEmail(resultSet.getString("EMAIL"));
				if(resultSet.getString("FAX") != null)  crmCustomerVO.setFax(resultSet.getString("FAX"));
				if(resultSet.getString("LEGAL_REPR") != null)  crmCustomerVO.setLegalRepr(resultSet.getString("LEGAL_REPR"));
				if(resultSet.getString("DESCRIPTION") != null)  crmCustomerVO.setDescription(resultSet.getString("DESCRIPTION"));
				pageinationVO.getLs().add(crmCustomerVO);
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			dBConnection.closeConnection(connection);
		}
		
		
		
		return pageinationVO;
	}	
	
}