重大技术需求系统七
核心代码
package Dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import net.sf.json.JSONObject;
import net.sf.json.JSONArray;
import java.util.Calendar;
import util.DBUtil;
import util.FormBean;
public class SearchDao {
//综合查询
public static String SearchAsAll(FormBean bean) {
String ans="";
int temp=0;
int k=0;
JSONArray jsonarray=new JSONArray();
Connection con=DBUtil.getConn();
Statement state=null;
String sql="select * from t_dcwjxx";
if(bean.getJGMC().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" JGMC like '%"+bean.getJGMC()+"%'";
k=1;
}else {
sql=sql+" and JGMC like '%"+bean.getJGMC()+"%'";
}
}
if(bean.getGKGLBM().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" GKGLBM like '%"+bean.getGKGLBM()+"%'";
k=1;
}else {
sql=sql+" and GKGLBM like '%"+bean.getGKGLBM()+"%'";
}
}
if(bean.getSZDY().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" SZDY like '%"+bean.getSZDY()+"%'";
k=1;
}else {
sql=sql+" and SZDY like '%"+bean.getSZDY()+"%'";
}
}
if(bean.getFRDB().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" FRDB like '%"+bean.getFRDB()+"%'";
k=1;
}else {
sql=sql+" and FRDB like '%"+bean.getFRDB()+"%'";
}
}
if(bean.getLXR().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" LXR like '%"+bean.getLXR()+"%'";
k=1;
}else {
sql=sql+" and LXR like '%"+bean.getLXR()+"%'";
}
}
if(bean.getJGSX().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" JGSX like '%"+bean.getJGSX()+"%'";
k=1;
}else {
sql=sql+" and JGSX like '%"+bean.getJGSX()+"%'";
}
}
if(bean.getJSXQMC().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" JSXQMC like '%"+bean.getJSXQMC()+"%'";
k=1;
}else {
sql=sql+" and JSXQMC like '%"+bean.getJSXQMC()+"%'";
}
}
if(bean.getGJZ().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" GJZ like '%"+bean.getGJZ()+"%'";
k=1;
}else {
sql=sql+" and GJZ like '%"+bean.getGJZ()+"%'";
}
}
if(bean.getNTRZJZE().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" NTRZJZE like '%"+bean.getNTRZJZE()+"%'";
k=1;
}else {
sql=sql+" and NTRZJZE like '%"+bean.getNTRZJZE()+"%'";
}
}
if(bean.getJSXQJJFS().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" JSXQJJFS like '%"+bean.getJSXQJJFS()+"%'";
k=1;
}else {
sql=sql+" and LXR like '%"+bean.getJSXQJJFS()+"%'";
}
}
if(bean.getYJLX().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" YJLX like '%"+bean.getYJLX()+"%'";
k=1;
}else {
sql=sql+" and YJLX like '%"+bean.getYJLX()+"%'";
}
}
if(bean.getXKFL().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" XKFL like '%"+bean.getXKFL()+"%'";
k=1;
}else {
sql=sql+" and XKFL like '%"+bean.getXKFL()+"%'";
}
}
if(bean.getXQJSSSLY().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" XQJSSSLY like '%"+bean.getXQJSSSLY()+"%'";
k=1;
}else {
sql=sql+" and XQJSSSLY like '%"+bean.getXQJSSSLY()+"%'";
}
}
if(bean.getXQJSYYHY().trim().length()>0) {
if(temp==0) {
sql=sql+" where";
}
if(k==0) {
sql=sql+" XQJSYYHY like '%"+bean.getXQJSYYHY()+"%'";
k=1;
}else {
sql=sql+" and XQJSYYHY like '%"+bean.getXQJSYYHY()+"%'";
}
}
ResultSet res=null;
try {
JSONObject jsonob=new JSONObject();
state=con.createStatement();
res=state.executeQuery(sql);
while(res.next()) {
jsonob.put("wjid", res.getInt("WJID"));
jsonob.put("jgmc", res.getString("JGMC"));
jsonob.put("frdb", res.getString("FRDB"));
jsonob.put("qsxqnf", res.getInt("QSXQNF"));
jsonob.put("jzxqnf", res.getInt("JZXQNF"));
jsonob.put("jsxqmc", res.getString("JSXQMC"));
jsonob.put("gjz", res.getString("GJZ"));
jsonarray.add(jsonob);
}
}catch(Exception e) {
e.printStackTrace();
}
ans=jsonarray.toString();
return ans;
}
//分类查询
public static String SearchAsKind(FormBean bean) {
String ans="";
JSONArray jsonarray=new JSONArray();
Connection con=DBUtil.getConn();
Statement state=null;
String sql="select * from t_dcwjxx";
if(bean.getJGMC().trim().length()>0) {
sql=sql+" where JGMC like '%"+bean.getJGMC()+"%'";
}
else if(bean.getGKGLBM().trim().length()>0) {
sql=sql+" where GKGLBM like '%"+bean.getGKGLBM()+"%'";
}
else if(bean.getSZDY().trim().length()>0) {
sql=sql+" where SZDY like '%"+bean.getSZDY()+"%'";
}
else if(bean.getFRDB().trim().length()>0) {
sql=sql+" where FRDB like '%"+bean.getFRDB()+"%'";
}
else if(bean.getLXR().trim().length()>0) {
sql=sql+" where LXR like '%"+bean.getLXR()+"%'";
}
else if(bean.getJGSX().trim().length()>0) {
sql=sql+" where JGSX like '%"+bean.getJGSX()+"%'";
}
else if(bean.getJSXQMC().trim().length()>0) {
sql=sql+" where JSXQMC like '%"+bean.getJSXQMC()+"%'";
}
else if(bean.getGJZ().trim().length()>0) {
sql=sql+" where GJZ like '%"+bean.getGJZ()+"%'";
}
else if(bean.getNTRZJZE().trim().length()>0) {
sql=sql+" where NTRZJZE like '%"+bean.getNTRZJZE()+"%'";
}
else if(bean.getJSXQJJFS().trim().length()>0) {
sql=sql+" where JSXQJJFS like '%"+bean.getJSXQJJFS()+"%'";
}
else if(bean.getYJLX().trim().length()>0) {
sql=sql+" where YJLX like '%"+bean.getYJLX()+"%'";
}
else if(bean.getXKFL().trim().length()>0) {
sql=sql+" where XKFL like '%"+bean.getXKFL()+"%'";
}
else if(bean.getXQJSSSLY().trim().length()>0) {
sql=sql+" where XQJSSSLY like '%"+bean.getXQJSSSLY()+"%'";
}
else if(bean.getXQJSYYHY().trim().length()>0) {
sql=sql+" where XQJSYYHY like '%"+bean.getXQJSYYHY()+"%'";
}
ResultSet res=null;
try {
JSONObject jsonob=new JSONObject();
state=con.createStatement();
res=state.executeQuery(sql);
while(res.next()) {
jsonob.put("wjid", res.getInt("WJID"));
jsonob.put("jgmc", res.getString("JGMC"));
jsonob.put("frdb", res.getString("FRDB"));
jsonob.put("qsxqnf", res.getInt("QSXQNF"));
jsonob.put("jzxqnf", res.getInt("JZXQNF"));
jsonob.put("jsxqmc", res.getString("JSXQMC"));
jsonob.put("gjz", res.getString("GJZ"));
jsonarray.add(jsonob);
}
}catch(Exception e) {
e.printStackTrace();
}
ans=jsonarray.toString();
return ans;
}
}
增设统计查询，分类查询