dhtmlxGrid分页查询，条件查询实例

使用jquery的ajax get将页面条件请求到后台，取得数据库数据，分页查询，返回前台grid中。

引入所需文件：

[html] view plaincopyprint?
  1. <script>window.dhx_globalImgPath = "dhtmlxCombo/codebase/imgs/";</script>  
  2. <link rel="stylesheet" href="css/dhtmlxgrid.css" type="text/css"></link>  
  3. <link rel="stylesheet" type="text/css"  
  4.     href="js/pagination/jquery.pagination/pagination.css" />  
  5. <script type="text/javascript" src="js/jquery-1.6.1.js"></script>  
  6. <script type="text/javascript"  
  7.     src="js/pagination/jquery.pagination/jquery.pagination.js"></script>  
  8. <script type="text/javascript" src="js/dhtmlxgrid.js"></script>  
  9. <link rel="stylesheet" href="css/dhtmlxcalendar.css" type="text/css"></link>  
  10. <link rel="stylesheet" href="css/dhtmlxcalendar_dhx_blue.css"  
  11.     type="text/css"></link>  
  12. <link rel="stylesheet" type="text/css"  
  13.     href="dhtmlxCombo/codebase/dhtmlxcombo.css" />  
  14. <link rel="stylesheet" type="text/css"  
  15.     href="css/dhtmlxgrid_dhx_custom.css" />  
  16. <link href="css/storemanage.css" type="text/css" rel="stylesheet" />  
  17. <link rel="stylesheet" href="css/dhtmlxgrid.css" type="text/css"></link>  
  18. <script type="text/javascript"  
  19.     src="dhtmlxGrid/codebase/excells/dhtmlxgrid_excell_cntr.js"></script>  
  20. <script type="text/javascript"  
  21.     src="dhtmlxGrid/codebase/ext/dhtmlxgrid_filter.js"></script>  
  22. <script type="text/javascript"  
  23.     src="dhtmlxGrid/codebase/ext/dhtmlxgrid_validation.js"></script>  
  24. <script type="text/javascript" src="js/dhtmlxcalendar.js"></script>  
  25. <script type="text/javascript" src="js/wpCalendar.js"></script>  
		<script>window.dhx_globalImgPath = "dhtmlxCombo/codebase/imgs/";</script>
		<link rel="stylesheet" href="css/dhtmlxgrid.css" type="text/css"></link>
		<link rel="stylesheet" type="text/css"
			href="js/pagination/jquery.pagination/pagination.css" />
		<script type="text/javascript" src="js/jquery-1.6.1.js"></script>
		<script type="text/javascript"
			src="js/pagination/jquery.pagination/jquery.pagination.js"></script>
		<script type="text/javascript" src="js/dhtmlxgrid.js"></script>
		<link rel="stylesheet" href="css/dhtmlxcalendar.css" type="text/css"></link>
		<link rel="stylesheet" href="css/dhtmlxcalendar_dhx_blue.css"
			type="text/css"></link>
		<link rel="stylesheet" type="text/css"
			href="dhtmlxCombo/codebase/dhtmlxcombo.css" />
		<link rel="stylesheet" type="text/css"
			href="css/dhtmlxgrid_dhx_custom.css" />
		<link href="css/storemanage.css" type="text/css" rel="stylesheet" />
		<link rel="stylesheet" href="css/dhtmlxgrid.css" type="text/css"></link>
		<script type="text/javascript"
			src="dhtmlxGrid/codebase/excells/dhtmlxgrid_excell_cntr.js"></script>
		<script type="text/javascript"
			src="dhtmlxGrid/codebase/ext/dhtmlxgrid_filter.js"></script>
		<script type="text/javascript"
			src="dhtmlxGrid/codebase/ext/dhtmlxgrid_validation.js"></script>
		<script type="text/javascript" src="js/dhtmlxcalendar.js"></script>
		<script type="text/javascript" src="js/wpCalendar.js"></script>


js代码

[javascript] view plaincopyprint?
  1.     <script>  
  2.     var mygrid;  
  3.     var combo_unite;  
  4.     var myCalendar;  
  5.     var combo_intype;  
  6.     var intype='';  
  7.     var insheetno='';  
  8.     var date1='';  
  9.     var date2='';  
  10.     var unitname='';  
  11.     var sheetmname='';  
  12.     var checkmname='';  
  13.     $(document).ready(function(){  
  14.         adjustDisp();  
  15.         mygrid = new dhtmlXGridObject('grid_storein');  
  16.         mygrid.enableAutoWidth(true);  
  17.         mygrid.setImagePath("dhtmlxGrid/codebase/imgs/");  
  18.         mygrid.setSkin("dhx_custom");  
  19.         mygrid.setHeader("入库单号,往来单位,入库类型,制单人,制单日期,金额,审核人,审核时间,记账人,记账日期,备注");  
  20.         mygrid.setInitWidths("*,*,*,*,*,*,*,*,*,*,*");  
  21.         mygrid.setColTypes("ro,ro,ro,ro,ro,ro,ro,ro,ro,ro,ro");  
  22.         mygrid.setColAlign("left,left,left,left,left,left,left,left,left,left,left");  
  23.         mygrid.init();  
  24.         combo_intype = new dhtmlXCombo("combo_zone1", "alfa1", 105);  
  25.         combo_intype.enableFilteringMode(true);  
  26.         //combo_intype.attachEvent("comboFilter",comboFilter(13, json, 'combo_intype', 'py', true, true));   
  27.             combo_unite = new dhtmlXCombo("combo_zone2", "alfa2", 155);  
  28.         combo_unite.enableFilteringMode(true);  
  29.         //combo_unite.attachEvent("comboFilter",comboFilter(13, json, 'combo_intype', 'py', true, true));   
  30.         //  y.addOption([[1, 'aaaa', "color:red"], [2, 'bbbb'], [3, 'cccc'], [4, 'dddd'], [5, 'eeee']]);   
  31.         combo1();  
  32.         combo2();  
  33.         //alert('1');   
  34.         myCalendar = new dhtmlXCalendarObject(["date1", "date2"]);  
  35.         loadCount_no();                                                                                                                                                                                                                                                                         
  36.     });  
  37.       
  38.     function loadCount_no(){  
  39.         intype=combo_intype.getComboText();// 取得页面上条件，入库类型  作为查询条件传到后台   
  40.         insheetno=$('#insheetno').val();// 取得页面上条件，入库单号   作为查询条件传到后台   
  41.         date1=$('#date1').val();// 取得页面上条件，入库日期1   作为查询条件传到后台   
  42.         date2=$('#date2').val();// 取得页面上条件，入库日期2   作为查询条件传到后台   
  43.         unitname=combo_unite.getComboText();//    
  44.         // alert($('#sheetmname').val());   
  45.         sheetmname=$('#sheetmname').val();// 取得页面上条件，制单人   
  46.         checkmname=$('#checkmname').val();// 取得页面上条件，审核人   
  47.         if(intype==''||intype==null){  
  48.             intype='';  
  49.         }  
  50.         if(insheetno==''||insheetno==null){  
  51.             insheetno='';  
  52.         }  
  53.         if(date1==''||insheetno==null){  
  54.             date1='';  
  55.         }  
  56.         if(date2==''||insheetno==null){  
  57.             date2='';  
  58.         }  
  59.         if(unitname==''||unitname==null){  
  60.             unitname='';  
  61.         }  
  62.         if(sheetmname==''||sheetmname==null){  
  63.             sheetmname='';  
  64.         }  
  65.         if(checkmname==''||checkmname==null){  
  66.             checkmname='';  
  67.         }  
  68.         //intype = encodeURI(encodeURI(intype));   
  69.         //insheetno = encodeURI(encodeURI(insheetno));         
  70.         //unitname = encodeURI(encodeURI(unitname));   
  71.         //date1 = encodeURI(encodeURI(date1));   
  72.         //date2 = encodeURI(encodeURI(date2));   
  73.         //sheetmname = encodeURI(encodeURI(sheetmname));           
  74.         //checkmname = encodeURI(encodeURI(checkmname));   
  75.           
  76.         intype=encodeURIComponent(intype);  
  77.         insheetno=encodeURIComponent(insheetno);  
  78.         unitname=encodeURIComponent(unitname);  
  79.         date1=encodeURIComponent(date1);  
  80.         date2=encodeURIComponent(date2);  
  81.         sheetmname=encodeURIComponent(sheetmname);  
  82.         checkmname=encodeURIComponent(checkmname);  
  83.         $.ajax({  
  84.             async:false,  
  85.             cache:false,  
  86.             type:"GET",  
  87.             url:"sick3/getCounttnb.htm?intype="+intype+"&insheetno="+insheetno+"&unitname="+unitname+"&date1="+date1+"&date2="+date2+"&sheetmname="+sheetmname+"&checkmname="+checkmname,  
  88.             error:function(){  
  89.                 alert("服务器内部错误！");  
  90.             },  
  91.             success:function(data){   
  92.                 if(data=='fail'){  
  93.                     alert("获取数据失败！");  
  94.                 }else{  
  95.                     //alert(data);   
  96.                      createPagination_no(data);  
  97.                 }  
  98.             }  
  99.         });  
  100.     }  
  101.   
  102.     var pageSize = 10;  
  103.     var pageIndex = 0;  
  104.     function createPagination_no(pageCount){//创建分页标签，pageCount为返回的记录数   
  105.         if(pageCount==0){  
  106.             pageCount=0;  
  107.         }  
  108.         //分页，pageCount是总条目数，这是必选参数，其它参数都是可选    
  109.         $("#pagination").pagination(pageCount, {   
  110.             callback: pageCallback_no,   
  111.             prev_text: '上一页', //上一页按钮里text    
  112.             next_text: '下一页', //下一页按钮里text    
  113.             items_per_page: pageSize, //显示条数    
  114.             num_display_entries: 6, //连续分页主体部分分页条目数    
  115.             current_page: pageIndex, //当前页索引    
  116.             num_edge_entries: 2 //两侧首尾分页条目数    
  117.         });  
  118.     }  
  119.     function pageCallback_no(index, jq){//翻页回调   
  120.         pageIndex = index; //当前页索引   
  121.         loadgrid_no(index);   
  122.         return false;  
  123.     }  
  124.   
  125.     //条件查询获取当前页未处理报表   
  126.     function loadgrid_no(index){  
  127.         intype=combo_intype.getComboText();  
  128.         insheetno=$('#insheetno').val();  
  129.         date1=$('#date1').val();  
  130.         date2=$('#date2').val();  
  131.         unitname=combo_unite.getComboText();  
  132.         sheetmname=$('#sheetmname').val();  
  133.         checkmname=$('#checkmname').val();  
  134.         if(intype==''||intype==null){  
  135.             intype='';  
  136.         }  
  137.         if(insheetno==''||insheetno==null){  
  138.             insheetno='';  
  139.         }  
  140.         if(date1==''||insheetno==null){  
  141.             date1='';  
  142.         }  
  143.         if(date2==''||insheetno==null){  
  144.             date2='';  
  145.         }  
  146.         if(unitname==''||unitname==null){  
  147.             unitname='';  
  148.         }  
  149.         if(sheetmname==''||sheetmname==null){  
  150.             sheetmname='';  
  151.         }  
  152.         if(checkmname==''||checkmname==null){  
  153.             checkmname='';  
  154.         }  
  155.         //intype = encodeURI(encodeURI(intype));   
  156.         //insheetno = encodeURI(encodeURI(insheetno));         
  157.         //unitname = encodeURI(encodeURI(unitname));   
  158.         //date1 = encodeURI(encodeURI(date1));   
  159.         //date2 = encodeURI(encodeURI(date2));   
  160.         //sheetmname = encodeURI(encodeURI(sheetmname));           
  161.         //checkmname = encodeURI(encodeURI(checkmname));   
  162.           
  163.         intype=encodeURIComponent(intype);  
  164.         insheetno=encodeURIComponent(insheetno);  
  165.         unitname=encodeURIComponent(unitname);  
  166.         date1=encodeURIComponent(date1);  
  167.         date2=encodeURIComponent(date2);  
  168.         sheetmname=encodeURIComponent(sheetmname);  
  169.         checkmname=encodeURIComponent(checkmname);  
  170.           
  171.         if(index!=0){  
  172.             index = pageIndex;  
  173.         }  
  174.           
  175.         //mygrid.clearAndLoad("sick3/loadtnb.htm");   
  176.         //alert('3');   
  177.         //alert(intype);   
  178.         mygrid.clearAndLoad("sick3/loadtnb.htm?index="+index+"&size="+pageSize+"&intype="+intype+"&insheetno="+insheetno+"&unitname="+unitname+"&date1="+date1+"&date2="+date2+"&sheetmname="+sheetmname+"&checkmname="+checkmname);  
  179.     }  
  180.     function adjustDisp(){//高度自适应，取相应的数据   
  181.         var avalibleHeight = $(window).height()-250;  
  182.         if(avalibleHeight>260){  
  183.             pageSize = Math.floor((avalibleHeight-27)/26);  
  184.             $("#grid_newApply").css("height",avalibleHeight);  
  185.         }  
  186.      }  
  187.       
  188.     function combo1(){  
  189.         $.ajax({  
  190.             type:"post",  
  191.             url: "sick3/hellohello.htm",  
  192.             cache: false,  
  193.             error:function(){  
  194.                 // alert("没有该病人记录！");   
  195.             },  
  196.             success: function(data){  
  197.                 if(data=="fail"){  
  198.                     alert("错误");  
  199.                 }else{  
  200.                     //alert(data[0].contents);   
  201.                     var json = eval("{" + data + "}");  
  202.                     //alert(json[0].contents);   
  203.                     for(var i=0;i<json.length;i++){  
  204.                         // alert(json[i].contents);   
  205.                         combo_intype.addOption(i,json[i].contents);  
  206.                     }  
  207.                 }  
  208.             }  
  209.         });  
  210. }  
  211.         function combo2(){  
  212.         $.ajax({  
  213.             type:"post",  
  214.             url: "sick3/combo2.htm",  
  215.             cache: false,  
  216.             error:function(){  
  217.                 //alert("没有该病人记录！");   
  218.             },  
  219.             success: function(data){  
  220.                 if(data=="fail"){  
  221.                     alert("错误");  
  222.                 }else{  
  223.                     //alert(data[0].contents);   
  224.                     var json = eval("{" + data + "}");  
  225.                     //alert(json[0].contents);   
  226.                     for(var i=0;i<json.length;i++){  
  227.                         //alert(json[i].contents);   
  228.                         combo_unite.addOption(i,json[i].contents);  
  229.                     }  
  230.                 }  
  231.             }  
  232.         });  
  233. }  
  234.     function dosearch(){  
  235.     loadCount_no();  
  236.     }  
		<script>
		var mygrid;
		var combo_unite;
		var myCalendar;
		var combo_intype;
		var intype='';
		var insheetno='';
		var date1='';
		var date2='';
		var unitname='';
		var sheetmname='';
		var checkmname='';
		$(document).ready(function(){
	    	adjustDisp();
			mygrid = new dhtmlXGridObject('grid_storein');
			mygrid.enableAutoWidth(true);
			mygrid.setImagePath("dhtmlxGrid/codebase/imgs/");
			mygrid.setSkin("dhx_custom");
			mygrid.setHeader("入库单号,往来单位,入库类型,制单人,制单日期,金额,审核人,审核时间,记账人,记账日期,备注");
			mygrid.setInitWidths("*,*,*,*,*,*,*,*,*,*,*");
			mygrid.setColTypes("ro,ro,ro,ro,ro,ro,ro,ro,ro,ro,ro");
			mygrid.setColAlign("left,left,left,left,left,left,left,left,left,left,left");
			mygrid.init();
			combo_intype = new dhtmlXCombo("combo_zone1", "alfa1", 105);
			combo_intype.enableFilteringMode(true);
			//combo_intype.attachEvent("comboFilter",comboFilter(13, json, 'combo_intype', 'py', true, true));
 		 	combo_unite = new dhtmlXCombo("combo_zone2", "alfa2", 155);
			combo_unite.enableFilteringMode(true);
			//combo_unite.attachEvent("comboFilter",comboFilter(13, json, 'combo_intype', 'py', true, true));
		 	//  y.addOption([[1, 'aaaa', "color:red"], [2, 'bbbb'], [3, 'cccc'], [4, 'dddd'], [5, 'eeee']]);
		 	combo1();
			combo2();
		  	//alert('1');
			myCalendar = new dhtmlXCalendarObject(["date1", "date2"]);
			loadCount_no();                                                                                                                                                                                                                                                                       
		});
		
		function loadCount_no(){
		    intype=combo_intype.getComboText();// 取得页面上条件，入库类型  作为查询条件传到后台
			insheetno=$('#insheetno').val();// 取得页面上条件，入库单号   作为查询条件传到后台
			date1=$('#date1').val();// 取得页面上条件，入库日期1   作为查询条件传到后台
			date2=$('#date2').val();// 取得页面上条件，入库日期2   作为查询条件传到后台
			unitname=combo_unite.getComboText();// 
			// alert($('#sheetmname').val());
			sheetmname=$('#sheetmname').val();// 取得页面上条件，制单人
			checkmname=$('#checkmname').val();// 取得页面上条件，审核人
			if(intype==''||intype==null){
				intype='';
			}
			if(insheetno==''||insheetno==null){
				insheetno='';
			}
			if(date1==''||insheetno==null){
				date1='';
			}
			if(date2==''||insheetno==null){
				date2='';
			}
			if(unitname==''||unitname==null){
				unitname='';
			}
			if(sheetmname==''||sheetmname==null){
				sheetmname='';
			}
			if(checkmname==''||checkmname==null){
				checkmname='';
			}
			//intype = encodeURI(encodeURI(intype));
			//insheetno = encodeURI(encodeURI(insheetno));		
			//unitname = encodeURI(encodeURI(unitname));
			//date1 = encodeURI(encodeURI(date1));
			//date2 = encodeURI(encodeURI(date2));
			//sheetmname = encodeURI(encodeURI(sheetmname));		
			//checkmname = encodeURI(encodeURI(checkmname));
			
			intype=encodeURIComponent(intype);
			insheetno=encodeURIComponent(insheetno);
			unitname=encodeURIComponent(unitname);
			date1=encodeURIComponent(date1);
			date2=encodeURIComponent(date2);
			sheetmname=encodeURIComponent(sheetmname);
			checkmname=encodeURIComponent(checkmname);
			$.ajax({
				async:false,
				cache:false,
				type:"GET",
				url:"sick3/getCounttnb.htm?intype="+intype+"&insheetno="+insheetno+"&unitname="+unitname+"&date1="+date1+"&date2="+date2+"&sheetmname="+sheetmname+"&checkmname="+checkmname,
				error:function(){
					alert("服务器内部错误！");
				},
				success:function(data){ 
					if(data=='fail'){
						alert("获取数据失败！");
					}else{
						//alert(data);
						 createPagination_no(data);
					}
				}
			});
		}

		var pageSize = 10;
		var pageIndex = 0;
		function createPagination_no(pageCount){//创建分页标签，pageCount为返回的记录数
			if(pageCount==0){
				pageCount=0;
			}
			//分页，pageCount是总条目数，这是必选参数，其它参数都是可选 
			$("#pagination").pagination(pageCount, { 
				callback: pageCallback_no, 
				prev_text: '上一页', //上一页按钮里text 
				next_text: '下一页', //下一页按钮里text 
				items_per_page: pageSize, //显示条数 
				num_display_entries: 6, //连续分页主体部分分页条目数 
				current_page: pageIndex, //当前页索引 
				num_edge_entries: 2 //两侧首尾分页条目数 
			});
		}
		function pageCallback_no(index, jq){//翻页回调
			pageIndex = index; //当前页索引
			loadgrid_no(index); 
			return false;
		}

		//条件查询获取当前页未处理报表
		function loadgrid_no(index){
			intype=combo_intype.getComboText();
			insheetno=$('#insheetno').val();
			date1=$('#date1').val();
			date2=$('#date2').val();
			unitname=combo_unite.getComboText();
			sheetmname=$('#sheetmname').val();
			checkmname=$('#checkmname').val();
			if(intype==''||intype==null){
				intype='';
			}
			if(insheetno==''||insheetno==null){
				insheetno='';
			}
			if(date1==''||insheetno==null){
				date1='';
			}
			if(date2==''||insheetno==null){
				date2='';
			}
			if(unitname==''||unitname==null){
				unitname='';
			}
			if(sheetmname==''||sheetmname==null){
				sheetmname='';
			}
			if(checkmname==''||checkmname==null){
				checkmname='';
			}
			//intype = encodeURI(encodeURI(intype));
			//insheetno = encodeURI(encodeURI(insheetno));		
			//unitname = encodeURI(encodeURI(unitname));
			//date1 = encodeURI(encodeURI(date1));
			//date2 = encodeURI(encodeURI(date2));
			//sheetmname = encodeURI(encodeURI(sheetmname));		
			//checkmname = encodeURI(encodeURI(checkmname));
			
			intype=encodeURIComponent(intype);
			insheetno=encodeURIComponent(insheetno);
			unitname=encodeURIComponent(unitname);
			date1=encodeURIComponent(date1);
			date2=encodeURIComponent(date2);
			sheetmname=encodeURIComponent(sheetmname);
			checkmname=encodeURIComponent(checkmname);
			
			if(index!=0){
				index = pageIndex;
			}
			
			//mygrid.clearAndLoad("sick3/loadtnb.htm");
			//alert('3');
			//alert(intype);
			mygrid.clearAndLoad("sick3/loadtnb.htm?index="+index+"&size="+pageSize+"&intype="+intype+"&insheetno="+insheetno+"&unitname="+unitname+"&date1="+date1+"&date2="+date2+"&sheetmname="+sheetmname+"&checkmname="+checkmname);
		}
		function adjustDisp(){//高度自适应，取相应的数据
			var avalibleHeight = $(window).height()-250;
			if(avalibleHeight>260){
				pageSize = Math.floor((avalibleHeight-27)/26);
				$("#grid_newApply").css("height",avalibleHeight);
			}
		 }
		
		function combo1(){
			$.ajax({
				type:"post",
				url: "sick3/hellohello.htm",
				cache: false,
				error:function(){
					// alert("没有该病人记录！");
				},
				success: function(data){
					if(data=="fail"){
						alert("错误");
					}else{
						//alert(data[0].contents);
						var json = eval("{" + data + "}");
						//alert(json[0].contents);
						for(var i=0;i<json.length;i++){
							// alert(json[i].contents);
							combo_intype.addOption(i,json[i].contents);
						}
					}
				}
			});
	}
			function combo2(){
			$.ajax({
				type:"post",
				url: "sick3/combo2.htm",
				cache: false,
				error:function(){
					//alert("没有该病人记录！");
				},
				success: function(data){
					if(data=="fail"){
						alert("错误");
					}else{
						//alert(data[0].contents);
						var json = eval("{" + data + "}");
						//alert(json[0].contents);
						for(var i=0;i<json.length;i++){
							//alert(json[i].contents);
							combo_unite.addOption(i,json[i].contents);
						}
					}
				}
			});
	}
		function dosearch(){
		loadCount_no();
		}

文本框，combo的html略，放table里设计界面更整洁，

[html] view plaincopyprint?
  1. <div id="grid_storein" style="height: 389px;  900px;"></div>  
  2. <div id="pagination" style="position: relative; margin-left: 580px"></div>  
<div id="grid_storein" style="height: 389px;  900px;"></div>
<div id="pagination" style="position: relative; margin-left: 580px"></div>

后台action代码（传中文get请求需要转码）

[java] view plaincopyprint?
  1.     @RequestMapping(value = "getCounttnb", method = RequestMethod.GET)  
  2.     public void getCount(HttpServletRequest request,HttpServletResponse response,String status) throws IOException {  
  3.         response.setContentType("text/html;charset=utf-8");  
  4.         request.setCharacterEncoding("utf-8");  
  5.         List<Map> list = null;  
  6.         DBOperator db = null;  
  7.         PrintWriter pw = response.getWriter();  
  8.         int count = 0;  
  9.         String intype = request.getParameter("intype");  
  10. //      System.out.println(intype);   
  11.         String insheetno = request.getParameter("insheetno");  
  12.         String date1 = request.getParameter("date1");  
  13.         String date2 = request.getParameter("date2");  
  14.         String unitname = request.getParameter("unitname");  
  15.         String sheetmname = request.getParameter("sheetmname");  
  16.         String checkmname = request.getParameter("checkmname");  
  17.         try {  
  18.             db = new DBOperator();  
  19.             String sql="select  count(*) as count from (" +  
  20.                 "select s.insheetno,s.unitname,s.intype,s.sheetmname," +  
  21.                 "to_char(s.sheetdate, 'yyyy-MM-dd'),d.inqty * d.pprice as price," +  
  22.                 "s.checkmname,to_char(s.checkdate, 'yyyy-MM-dd'),s.regmname,to_char(s.regdate, 'yyyy-MM-dd'),s.comments " +  
  23.                 "from mw_insheets s, mw_insheets_details d where s.insheetno = d.insheetid ";  
  24.             if(insheetno!=null &&!"".equals(insheetno)){  
  25. //              insheetno=URLEncoder.encode(insheetno, "utf-8");   
  26.                 insheetno=new String(insheetno.getBytes("iso-8859-1"), "utf-8");  
  27.                 sql+=" and s.insheetno='"+insheetno+"'";  
  28.             }  
  29.             if(intype!=null &&!"".equals(intype)){  
  30. //              intype=URLEncoder.encode(intype, "utf-8");   
  31.                 intype=new String(intype.getBytes("iso-8859-1"), "utf-8");  
  32.                 sql+=" and s.intype='"+intype+"'";  
  33.                 System.out.println(intype);  
  34.             }  
  35.             if(date1!=null &&!"".equals(date1)){  
  36.                 sql += " and to_char(s.sheetdate,'yyyy-mm-dd')>='"+date1+"'";  
  37.             }  
  38.             if(date2!=null &&!"".equals(date2)){  
  39.                 sql += " and to_char(s.sheetdate,'yyyy-mm-dd')<='"+date2+"'";  
  40.             }  
  41.             if(unitname!=null &&!"".equals(unitname)){  
  42. //              unitname=URLEncoder.encode(unitname, "utf-8");   
  43.                 unitname=new String(unitname.getBytes("iso-8859-1"), "utf-8");  
  44.                 sql+=" and s.unitname='"+unitname+"'";  
  45.             }  
  46.             if(sheetmname!=null &&!"".equals(sheetmname)){  
  47. //              sheetmname=URLEncoder.encode(sheetmname, "utf-8");   
  48.                 sheetmname=new String(sheetmname.getBytes("iso-8859-1"), "utf-8");  
  49.                 sql+=" and s.sheetmname='"+sheetmname+"'";  
  50.             }  
  51.             if(checkmname!=null &&!"".equals(checkmname)){  
  52. //              checkmname=URLEncoder.encode(checkmname, "utf-8");   
  53.                 checkmname=new String(checkmname.getBytes("iso-8859-1"), "utf-8");  
  54.                 sql+=" and s.checkmname='"+checkmname+"'";  
  55.             }            
  56.                 sql+=")";  
  57.             list = db.find(sql);  
  58.             db.commit();  
  59.             count=Integer.valueOf(String.valueOf(list.get(0).get("count")));  
  60.             pw.print(count);  
  61.         }catch(Exception e){  
  62.             e.printStackTrace();  
  63.             pw.print("fail");  
  64.         }finally{  
  65.             db.freeCon();  
  66.         }  
  67.         pw.flush();  
  68.         pw.close();  
  69.     }  
  70.      //返回疾病结果   
  71.     @RequestMapping(value = "loadtnb", method = RequestMethod.GET)  
  72.     public void loadGrid(HttpServletRequest request,HttpServletResponse response,String status) throws IOException {  
  73.         response.setContentType("text/html;charset=utf-8");  
  74.         request.setCharacterEncoding("utf-8");  
  75.         int Pagesize = Integer.parseInt(request.getParameter("size"));  
  76.         int index = Integer.parseInt(request.getParameter("index"));  
  77.         String intype = request.getParameter("intype");  
  78.         String insheetno = request.getParameter("insheetno");  
  79.         String date1 = request.getParameter("date1");  
  80.         String date2 = request.getParameter("date2");  
  81.         String unitname = request.getParameter("unitname");  
  82.         String sheetmname = request.getParameter("sheetmname");  
  83.         String checkmname = request.getParameter("checkmname");  
  84.         String pagingSql1 = "select OHYEAH.* from (select OHNO.*,rownum no from ("; // 用于分页// 段1   
  85.         String pagingSql2 = ") OHNO where rownum <= ?) OHYEAH where no > ?"; // 用于分页段2   
  86.         String sql = "select s.insheetno,s.unitname,s.intype,s.sheetmname,to_char(s.sheetdate,'yyyy-MM-dd') as sheetdate," +  
  87.                 "d.inqty*d.pprice as price,s.checkmname,to_char(s.checkdate,'yyyy-MM-dd') as checkdate,s.regmname,to_char(s.regdate,'yyyy-MM-dd') as regdate,s.comments " +  
  88.                 "from mw_insheets s,mw_insheets_details d where s.insheetno=d.insheetid ";  
  89.         if(insheetno!=null &&!"".equals(insheetno)){  
  90. //          insheetno=URLEncoder.encode(insheetno, "utf-8");   
  91.             insheetno=new String(insheetno.getBytes("iso-8859-1"), "utf-8");  
  92.             sql+=" and s.insheetno='"+insheetno+"'";  
  93.         }  
  94.         if(intype!=null &&!"".equals(intype)){  
  95. //          intype=URLEncoder.encode(intype, "utf-8");   
  96.             intype=new String(intype.getBytes("iso-8859-1"), "utf-8");  
  97.             sql+=" and s.intype='"+intype+"'";  
  98.             System.out.println(intype);  
  99.         }  
  100.         if(date1!=null &&!"".equals(date1)){  
  101.             sql += " and to_char(s.sheetdate,'yyyy-mm-dd')>='"+date1+"'";  
  102.         }  
  103.         if(date2!=null &&!"".equals(date2)){  
  104.             sql += " and to_char(s.sheetdate,'yyyy-mm-dd')<='"+date2+"'";  
  105.         }  
  106.         if(unitname!=null &&!"".equals(unitname)){  
  107. //          unitname=URLEncoder.encode(unitname, "utf-8");   
  108.             unitname=new String(unitname.getBytes("iso-8859-1"), "utf-8");  
  109.             sql+=" and s.unitname='"+unitname+"'";  
  110.         }  
  111.         if(sheetmname!=null &&!"".equals(sheetmname)){  
  112. //          sheetmname=URLEncoder.encode(sheetmname, "utf-8");   
  113.             sheetmname=new String(sheetmname.getBytes("iso-8859-1"), "utf-8");  
  114.             sql+=" and s.sheetmname='"+sheetmname+"'";  
  115.         }  
  116.         if(checkmname!=null &&!"".equals(checkmname)){  
  117. //          checkmname=URLEncoder.encode(checkmname, "utf-8");   
  118.             checkmname=new String(checkmname.getBytes("iso-8859-1"), "utf-8");  
  119.             sql+=" and s.checkmname='"+checkmname+"'";  
  120.         }  
  121.         DBOperator db = null;  
  122.         try {  
  123.             db = new DBOperator();  
  124.             List  list = db.find(pagingSql1+sql+pagingSql2,new Object[]{Pagesize*index+Pagesize,Pagesize*index});  
  125.             response.setContentType("text/xml;charset=utf-8");  
  126.             PrintWriter pw = null;  
  127.             pw = response.getWriter();  
  128.             String vmpagckage = "com/cpinfo/learn/template/";  
  129.             String vmname = "hello.vm";  
  130.             String vm = VelocityUtils.generateGridVm(vmpagckage, vmname,"MW_hello", list);  
  131.             pw.print(vm);  
  132.             pw.flush();  
  133.             pw.close();  
  134.         }catch(Exception e){  
  135.             e.printStackTrace();  
  136.         }finally{  
  137.             db.freeCon();  
  138.         }  
  139.     }  
	@RequestMapping(value = "getCounttnb", method = RequestMethod.GET)
	public void getCount(HttpServletRequest request,HttpServletResponse response,String status) throws IOException {
		response.setContentType("text/html;charset=utf-8");
		request.setCharacterEncoding("utf-8");
		List<Map> list = null;
		DBOperator db = null;
		PrintWriter pw = response.getWriter();
		int count = 0;
		String intype = request.getParameter("intype");
//		System.out.println(intype);
		String insheetno = request.getParameter("insheetno");
		String date1 = request.getParameter("date1");
		String date2 = request.getParameter("date2");
		String unitname = request.getParameter("unitname");
		String sheetmname = request.getParameter("sheetmname");
		String checkmname = request.getParameter("checkmname");
		try {
			db = new DBOperator();
			String sql="select  count(*) as count from (" +
				"select s.insheetno,s.unitname,s.intype,s.sheetmname," +
				"to_char(s.sheetdate, 'yyyy-MM-dd'),d.inqty * d.pprice as price," +
				"s.checkmname,to_char(s.checkdate, 'yyyy-MM-dd'),s.regmname,to_char(s.regdate, 'yyyy-MM-dd'),s.comments " +
				"from mw_insheets s, mw_insheets_details d where s.insheetno = d.insheetid ";
			if(insheetno!=null &&!"".equals(insheetno)){
//				insheetno=URLEncoder.encode(insheetno, "utf-8");
				insheetno=new String(insheetno.getBytes("iso-8859-1"), "utf-8");
				sql+=" and s.insheetno='"+insheetno+"'";
			}
	        if(intype!=null &&!"".equals(intype)){
//	        	intype=URLEncoder.encode(intype, "utf-8");
	        	intype=new String(intype.getBytes("iso-8859-1"), "utf-8");
	        	sql+=" and s.intype='"+intype+"'";
	        	System.out.println(intype);
	        }
	        if(date1!=null &&!"".equals(date1)){
	        	sql += " and to_char(s.sheetdate,'yyyy-mm-dd')>='"+date1+"'";
	        }
	        if(date2!=null &&!"".equals(date2)){
	        	sql += " and to_char(s.sheetdate,'yyyy-mm-dd')<='"+date2+"'";
	        }
	        if(unitname!=null &&!"".equals(unitname)){
//	        	unitname=URLEncoder.encode(unitname, "utf-8");
	        	unitname=new String(unitname.getBytes("iso-8859-1"), "utf-8");
	        	sql+=" and s.unitname='"+unitname+"'";
	        }
	        if(sheetmname!=null &&!"".equals(sheetmname)){
//	        	sheetmname=URLEncoder.encode(sheetmname, "utf-8");
	        	sheetmname=new String(sheetmname.getBytes("iso-8859-1"), "utf-8");
	        	sql+=" and s.sheetmname='"+sheetmname+"'";
	        }
	        if(checkmname!=null &&!"".equals(checkmname)){
//	        	checkmname=URLEncoder.encode(checkmname, "utf-8");
	        	checkmname=new String(checkmname.getBytes("iso-8859-1"), "utf-8");
	        	sql+=" and s.checkmname='"+checkmname+"'";
	        }	       
	        	sql+=")";
			list = db.find(sql);
			db.commit();
			count=Integer.valueOf(String.valueOf(list.get(0).get("count")));
			pw.print(count);
		}catch(Exception e){
			e.printStackTrace();
			pw.print("fail");
		}finally{
			db.freeCon();
		}
		pw.flush();
		pw.close();
	}
	 //返回疾病结果
	@RequestMapping(value = "loadtnb", method = RequestMethod.GET)
	public void loadGrid(HttpServletRequest request,HttpServletResponse response,String status) throws IOException {
		response.setContentType("text/html;charset=utf-8");
		request.setCharacterEncoding("utf-8");
	    int Pagesize = Integer.parseInt(request.getParameter("size"));
		int index = Integer.parseInt(request.getParameter("index"));
		String intype = request.getParameter("intype");
		String insheetno = request.getParameter("insheetno");
		String date1 = request.getParameter("date1");
		String date2 = request.getParameter("date2");
		String unitname = request.getParameter("unitname");
		String sheetmname = request.getParameter("sheetmname");
		String checkmname = request.getParameter("checkmname");
		String pagingSql1 = "select OHYEAH.* from (select OHNO.*,rownum no from ("; // 用于分页// 段1
		String pagingSql2 = ") OHNO where rownum <= ?) OHYEAH where no > ?"; // 用于分页段2
		String sql = "select s.insheetno,s.unitname,s.intype,s.sheetmname,to_char(s.sheetdate,'yyyy-MM-dd') as sheetdate," +
				"d.inqty*d.pprice as price,s.checkmname,to_char(s.checkdate,'yyyy-MM-dd') as checkdate,s.regmname,to_char(s.regdate,'yyyy-MM-dd') as regdate,s.comments " +
				"from mw_insheets s,mw_insheets_details d where s.insheetno=d.insheetid ";
		if(insheetno!=null &&!"".equals(insheetno)){
//			insheetno=URLEncoder.encode(insheetno, "utf-8");
			insheetno=new String(insheetno.getBytes("iso-8859-1"), "utf-8");
			sql+=" and s.insheetno='"+insheetno+"'";
		}
        if(intype!=null &&!"".equals(intype)){
//        	intype=URLEncoder.encode(intype, "utf-8");
        	intype=new String(intype.getBytes("iso-8859-1"), "utf-8");
        	sql+=" and s.intype='"+intype+"'";
        	System.out.println(intype);
        }
        if(date1!=null &&!"".equals(date1)){
        	sql += " and to_char(s.sheetdate,'yyyy-mm-dd')>='"+date1+"'";
        }
        if(date2!=null &&!"".equals(date2)){
        	sql += " and to_char(s.sheetdate,'yyyy-mm-dd')<='"+date2+"'";
        }
        if(unitname!=null &&!"".equals(unitname)){
//        	unitname=URLEncoder.encode(unitname, "utf-8");
        	unitname=new String(unitname.getBytes("iso-8859-1"), "utf-8");
        	sql+=" and s.unitname='"+unitname+"'";
        }
        if(sheetmname!=null &&!"".equals(sheetmname)){
//        	sheetmname=URLEncoder.encode(sheetmname, "utf-8");
        	sheetmname=new String(sheetmname.getBytes("iso-8859-1"), "utf-8");
        	sql+=" and s.sheetmname='"+sheetmname+"'";
        }
        if(checkmname!=null &&!"".equals(checkmname)){
//        	checkmname=URLEncoder.encode(checkmname, "utf-8");
        	checkmname=new String(checkmname.getBytes("iso-8859-1"), "utf-8");
        	sql+=" and s.checkmname='"+checkmname+"'";
        }
		DBOperator db = null;
		try {
			db = new DBOperator();
			List  list = db.find(pagingSql1+sql+pagingSql2,new Object[]{Pagesize*index+Pagesize,Pagesize*index});
			response.setContentType("text/xml;charset=utf-8");
			PrintWriter pw = null;
			pw = response.getWriter();
			String vmpagckage = "com/cpinfo/learn/template/";
			String vmname = "hello.vm";
			String vm = VelocityUtils.generateGridVm(vmpagckage, vmname,"MW_hello", list);
			pw.print(vm);
			pw.flush();
			pw.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			db.freeCon();
		}
	}




【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/wzh123/p/3470582.html