JAVA 中SQL字符动态拼接

2022-05-07 03:42:56

select SYR,SFZMHM,CJRZH,XSZBH,HPHM,CLSBDH,FDJH,CLLX,ZDYZT,to_char(CCDJRQ,'YYYY-MM-DD') CCDJRQ from VEH_DISABLED   where CCDJRQ between to_date('2013-10-15','yyyy-MM-dd')and to_date('2015-10-15','yyyy-MM-dd') order by CCDJRQ desc

Service里的查询：

public List getAdvancedListService(Map map){
		StringBuilder sqlCondition = new StringBuilder();
		Set keys = map.keySet();
		if (keys.contains("SYR")) {
			if (map.get("SYR") != null && !"".equals(map.get("SYR").toString().trim()) && !"null".equals(map.get("SYR"))) {
				sqlCondition.append("and SYR like '%" + map.get("SYR") + "%'");
			}
		}
		if (keys.contains("SFZMHM")) {
			if (map.get("SFZMHM") != null && !"".equals(map.get("SFZMHM").toString().trim()) && !"null".equals(map.get("SFZMHM"))) {
				sqlCondition.append("and SFZMHM = '" + map.get("SFZMHM") + "'");
			}
		}
		if (keys.contains("CJRZH")) {
			if (map.get("CJRZH") != null && !"".equals(map.get("CJRZH").toString().trim()) && !"null".equals(map.get("CJRZH"))) {
				sqlCondition.append("and CJRZH like '%" + map.get("CJRZH") + "%' ");
			}
		}
		if (keys.contains("XSZBH")) {
			if (map.get("XSZBH") != null && !"".equals(map.get("XSZBH").toString().trim()) && !"null".equals(map.get("XSZBH").toString().trim())) {
				sqlCondition.append("and XSZBH like '%" + map.get("XSZBH") + "%' ");
			}
		}
		if (keys.contains("CLSBDH")) {
			if (map.get("CLSBDH") != null && !"".equals(map.get("CLSBDH").toString().trim()) && !"null".equals(map.get("CLSBDH").toString().trim())) {
				sqlCondition.append("and CLSBDH = '" + map.get("CLSBDH") + "' ");
			}
		}
		if (keys.contains("FDJH")) {
			if (map.get("FDJH") != null && !"".equals(map.get("FDJH").toString().trim()) && !"null".equals(map.get("FDJH").toString().trim())) {
				sqlCondition.append("and FDJH = '" + map.get("FDJH") + "' ");
			}
		}
		if (keys.contains("CLLX")) {
			if (map.get("CLLX") != null && !"".equals(map.get("CLLX").toString().trim()) && !"null".equals(map.get("CLLX").toString().trim())) {
				sqlCondition.append("and CLLX = '" + map.get("CLLX") + "' ");
			}
		}
		if (keys.contains("HPHM")) {
			if (map.get("HPHM") != null && !"".equals(map.get("HPHM").toString().trim()) && !"null".equals(map.get("HPHM"))) {
				sqlCondition.append("and HPHM = '" + map.get("HPHM") + "' ");
			}
		}
		if (keys.contains("loginBeginTime") && keys.contains("loginEndTime")){
			sqlCondition.append("and CCDJRQ between to_date('").append(map.get("loginBeginTime")).append("','yyyy-MM-dd')and to_date('").append(map.get("loginEndTime")).append("','yyyy-MM-dd')");
		}
		if(sqlCondition.length() > 3){
			return disabledvehDao.getAdvancedList(sqlCondition.substring(3));
		}
		    return disabledvehDao.getAdvancedList(sqlCondition.toString());
	}

Dao类：

	/**
	 * 高级查询
	 * @param
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List getAdvancedList(String condition){
		String sql;
		if("".equals(condition) || "null".equals(condition) || condition == null){
			sql ="select d.SYR,d.SFZMHM,d.CJRZH,d.XSZBH,d.HPHM,d.CLSBDH,d.FDJH,d.CLLX,d.ZDYZT,to_char(d.CCDJRQ,'YYYY-MM-DD') CCDJRQ"+
			" from VEH_DISABLED d order by d.CCDJRQ desc";
		}else{
			sql = "select d.SYR,d.SFZMHM,d.CJRZH,d.XSZBH,d.HPHM,d.CLSBDH,d.FDJH,d.CLLX,d.ZDYZT,to_char(d.CCDJRQ,'YYYY-MM-DD') CCDJRQ"+
			" from VEH_DISABLED d  where "+condition+" order by d.CCDJRQ desc";
		}
		return jdbcTemplate.queryForList(sql);
	}

provider类：

package com.suntek.disabledvehmgr.web.disabledveh;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import com.suntek.ccf.web.service.ServiceInvocationException;
import com.suntek.eap.core.app.AppHandle;
import com.suntek.disabledvehmgr.dao.base.BaseGrid;
import com.suntek.disabledvehmgr.log.DisabledvehMgrLogger;
import com.suntek.disabledvehmgr.service.disabledveh.DisabledvehService;
import com.suntek.disabledvehmgr.util.CommonUtil;
import com.suntek.disabledvehmgr.util.Constants;
import com.suntek.disabledvehmgr.util.SpringContextUtils;

/**
 * 残疾车业务provider类
 * @author sawshaw
 *
 */
public class AdvancedQueryProvider extends BaseGrid{

	@SuppressWarnings("unused")
	private AppHandle appHandle;
	private HttpServletRequest request;
	private DisabledvehService disabledvehService;

	@Override
	public void after(HttpServletRequest arg0)
			throws ServiceInvocationException {
	}
	@Override
	public void before(HttpServletRequest request)
			throws ServiceInvocationException {
		this.request = request;
		this.appHandle = AppHandle.getHandle(Constants.APP_NAME);
		this.disabledvehService = (DisabledvehService)
				SpringContextUtils.getBean("disabledvehService",request);
	}
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public int directCount(Map map) {
		try{
			if(!CommonUtil.isRequestLegal(request)){
				datas = new ArrayList<Map>();
				return datas.size();
			}
			if("firstLoad".equals(CommonUtil.getKeyValue(map, "opera_type"))){
				return 0;
			}
			if (map != null) {
				datas = disabledvehService.getAdvancedListService(map);
			}
			if(datas == null){
				datas = new ArrayList<Map>();
			}
		}catch(Exception e){
		    	DisabledvehMgrLogger.logger.error("[AdvancedQueryProvider]directCount,查询异常,", e);
			datas = new ArrayList<Map>();
		}
		return datas.size();
	}
	@SuppressWarnings("rawtypes")
	@Override
	public List<Map> directFetch(Map map, int pageNo, int pageSize) {
		if("firstLoad".equals(CommonUtil.getKeyValue(map, "opera_type"))){
			return new ArrayList<Map>();
		}
		return CommonUtil.getPageList(pageNo, pageSize, datas);
	}
}

页面关键部分：

<%@ page language="java" import="java.util.*" contentType="text/html; charset=gbk" pageEncoding="gbk" %>
<%@ taglib uri="http://eaptag.suntek.com" prefix="ui" %>

<%
   String userCode=request.getRemoteUser();
   String path = request.getContextPath();
   if(userCode == null){
		out.println("<script>top.location.href='" + path+ "/index.jsp';</script>");
		return ;
	}

%>
<html>
<head>
	<title>高级查询</title>
	<script type="text/javascript" src="/eaptag2/js/ccftag.form.js"></script>
	<script type="text/javascript" src="/eaptag2/js/ccftag.zdialog.js" ></script>
	<script type="text/javascript" src="<%=path%>/js/common.js" ></script>
	<script type="text/javascript" src="<%=path%>/js/station.util.js" ></script>

	<ui:Import tagNames="Window,Label,DateTimePicker,GTGrid,Button,GTGridField,Select"></ui:Import>
<style type="text/css">
.queryLabel{
 height:19px;
 width: 65px;
 text-align:right;
 padding-top: 2px;
}
</style>
<script type="text/javascript">

var ajax = new ccftag_ajax("<%=path%>/ccftag",false);

$(function(){
	$('input:checkbox').click(function(){
		var _this = $(this);
		if(_this.attr("checked") == true){
			$("input[name='status']").each(function(i){
				$(this).removeAttr("checked");
			});
			_this.attr("checked","checked");
		}else{
			_this.removeAttr("checked");
		}
		doQuery();
	});
});
function doQuery(){
		var param = Form.formToBean(queryForm,"java.util.Map",true);
		if(param.loginEndTime!=''&& param.loginBeginTime=='')
			{
			alert("请选择开始登记日期！");
			return ;
			}
		if(param.logiBeginTime!=''&& param.loginEndTime=='')
		{
		alert("请选择结束登记日期！");
		return ;
		}
		if(param.loginEndTime < param.loginBeginTime){
			alert("开始登记日期必须小于结束登记日期！");
			return ;
		}
		param.SYR = $("#queryName").val();
		param.SFZMHM = $("#queryIdNo").val();
		param.CJRZH = $("#queryDisabledNo").val();
		param.XSZBH = $("#queryTravelNo").val();
		param.HPHM = $("#queryVehNo").val();
		param.CLSBDH = $("#queryChassisNo").val();
		param.FDJH = $("#queryEngineNo").val();
		param.CLLX= $("#queryBrand").val();
		param.loginBeginTime= $("#loginBeginTime").val();
		param.loginEndTime= $("#loginEndTime").val();
		param.status = getSelectStatus();
		var map = disabledGrid.resetParameter(20,param);
		ajax.remoteCall("bean://com.suntek.disabledvehmgr.web.disabledveh.AdvancedQueryProvider:getData",[map],function(reply){
			//alert(reply.getResult());
			disabledGrid.cleanContent();
			disabledGrid.setContent(eval("(" + reply.getResult() + ")"));
		});
}
 function getSelectStatus(){
	var status = "";
	var $statusObj = $("input[name='status']:checked");
	$statusObj.each(function(i){
		status += $statusObj.eq(i).val() + ",";
	});
	if(status.length > 0){
		status = status.substring(0, status.length-1);
	}
	return status;
}
</script>
</head>
<body style="margin: 0px; overflow: hidden;" scroll="no">

	 <ui:Window id="winVehicleList" title=""  style="width:100%;height:100%;table-layout:fixed;" >
		<ui:Window.head>
			<form id="queryForm">
				<input type="hidden" id="BOOKING_TYPE" name="BOOKING_TYPE" value="4"/>
				<label class="queryLabel">登记日期：</label>
				<ui:DateTimePicker id="loginBeginTime" dateFmt="yyyy-mm-dd" value="2013-10-15" style="width:100px" readOnly="true"></ui:DateTimePicker>
				至<ui:DateTimePicker id="loginEndTime" dateFmt="yyyy-mm-dd" value="2014-12-15" style="width:100px" readOnly="true"></ui:DateTimePicker>
				<label class="queryLabel">姓名：</label><input type="text" id="queryName" style="width:106px;"/>
				<label class="queryLabel">身份证号：</label><input type="text" id="queryIdNo" style="width: 166px;"/>
				<label class="queryLabel">行驶证号：</label><input type="text" id="queryTravelNo" style="width:136px;"/><br/><br/>
				<label class="queryLabel">残疾证号：</label><input type="text" id="queryDisabledNo" style="width:136px;"/>
				<label class="queryLabel">车牌号：</label><input type="text" id="queryVehNo" style="width:106px;"/>
				<label class="queryLabel">车架号：</label><input type="text" id="queryChassisNo" style="width:106px;"/>
				<label class="queryLabel">发动机号：</label><input type="text" id="queryEngineNo" style="width:106px;"/>
				<label class="queryLabel">厂牌型号：</label><input type="text" id="queryBrand" style="width:106px;"/>
				    
				<ui:Button cls="query" onClick="doQuery()">查询</ui:Button>
			</form><hr/>
		</ui:Window.head>
  		<ui:Window.body id="winBlackListGrid" style="width:100%;height:100%">
				<ui:GTGrid id="disabledGrid"
					rowNumPerPage="20"
					css="width:100%; height:100%;"
					uniqueField="ID"
				  	exportSupport="true"
				  	autoLoad="false"
					dataProvider="bean://com.suntek.disabledvehmgr.web.disabledveh.AdvancedQueryProvider:getData">
					<%-- <ui:GTGridField name="ID" header="序号" isChecked="true"/> --%>
					<ui:GTGridField name="SYR" header="姓名" fieldType="string" align="left" width="100"/>
					<ui:GTGridField name="SFZMHM" header="身份证号" fieldType="string" align="center" width="150"/>
					<ui:GTGridField name="XSZBH" header="行驶证号" fieldType="string" align="center" width="120"/>
					<ui:GTGridField name="CJRZH" header="残疾证号" fieldType="string" align="center" width="120"/>
					<ui:GTGridField name="HPHM" header="车牌号" fieldType="string" align="center" width="120"/>
					<ui:GTGridField name="CLSBDH" header="车架号" fieldType="string" align="center" width="80"/>
					<ui:GTGridField name="FDJH" header="发动机号" fieldType="string" align="center" width="80"/>
					<ui:GTGridField name="CLLX" header="厂牌型号" fieldType="string" align="center" width="80"/>
					<ui:GTGridField name="ZDYZT" header="状态" fieldType="string" align="center" width="80"/>
					<ui:GTGridField name="CCDJRQ" header="登记日期" fieldType="string" align="center" width="120"/>
				<%-- 	<ui:GTGridField name="Files" header="申请资料电子影像文件" fieldType="string" align="center" width="140"/>	 --%>
				</ui:GTGrid>
  		</ui:Window.body>
  	</ui:Window>
  	<!-- <iframe name="downloadFrame" border='0' width='0' height='0'></iframe> -->
</body>
</html>

码农公寓

相关文章