该楼层疑似违规已被系统折叠 隐藏此楼查看此楼
想问各位大神一个关于多条件查询的问题,我是从网上找到了一个比较完整的代码,然后自己改了改,它使用的是sql语句拼接的方法,现在sql语句基本能拼接对,但是在执行的时候老是提示这样的错误:java.lang.RuntimeException: java.sql.SQLException: Your statment hasn't any parameter! 这个错误在网上搜都搜不到,困扰我很久了,恳请各位帮帮忙吧!代码如下:
1.拼接sql语句的类
public List> queryperson(String...val)throws Exception { Connection conn=null; PreparedStatement psmt=null; ResultSet rs=null; Db2 db2=new Db2(); try{ String pname=val[0]; String pnumber=val[1]; String xl=val[2]; String jszc=val[3]; String bsal=val[4]; String esal=val[5]; System.out.println(pname); System.out.println(pnumber); System.out.println(xl); System.out.println(jszc); System.out.println(bsal); System.out.println(esal); conn=db2.ConnAcc(); List pars=new ArrayList<>(); StringBuilder sql=new StringBuilder("select * from X where 1=1"); if(pname != null && !pname.trim().isEmpty()){ sql.append(" and PNAME="); sql.append(pname); pars.add(pname); } if(pnumber != null && !pnumber.trim().isEmpty()){ sql.append(" and PNUMBER="); sql.append(pnumber); pars.add(pnumber); } if(xl != null && !xl.trim().isEmpty()){ sql.append(" and XL="); sql.append(xl); pars.add(xl); } if(jszc != null && !jszc.trim().isEmpty()){ sql.append(" and JSZC="); sql.append(jszc); pars.add(jszc); } if(bsal != null && !bsal.trim().isEmpty()){ sql.append(" and BSAL>="); sql.append(bsal); pars.add(bsal); } if(esal != null && !esal.trim().isEmpty()){ sql.append(" and ESAL<="); sql.append(esal); pars.add(esal); } System.out.println(sql); System.out.println(pars); psmt=conn.prepareStatement(sql.toString()); int index=1; for (Object parm : pars) { psmt.setObject(index++, parm); } rs=psmt.executeQuery(); ResultSetMetaData rsmd=rs.getMetaData(); int count=rsmd.getColumnCount(); int init_size=((int)(count/0.75))+2; List> rows=new ArrayList<>(); Map ins=null; while(rs.next()){ ins=new HashMap<>(init_size); for(int i=1;i<=count;i++){ ins.put(rsmd.getColumnLabel(i).toLowerCase(),rs.getString(i)); } rows.add(ins); } return rows; }catch(SQLException e){ throw new RuntimeException(e); } //return null; }
2.servlet类
public class a1servlet extends HttpServlet{ public void init(ServletConfig config) throws ServletException{ super.init(config);} protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String pname=request.getParameter("qpname"); String pnumber=request.getParameter("qpumber"); String xl=request.getParameter("qxl"); String jszc=request.getParameter("qjszc"); String bsal=request.getParameter("bsal"); String esal=request.getParameter("esal"); String val[]={pname,pnumber,xl,jszc,bsal,esal}; PersonServices service=new PersonServices(); try { List> rows=service.queryperson(val); request.setAttribute("rows", rows); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println(pname); System.out.println(pnumber); System.out.println(xl); System.out.println(jszc); System.out.println(bsal); System.out.println(esal); }
3.jsp界面body部分
查询条件 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
姓名 | 身份证 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
学历 | ==不限==高中以下高中专科本科 | 技术职称 | ==不限==初级职称中级职称副高级职称高级职称 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
工资[B] | 工资[E] |
代码有点长,想知道哪里错了,谢谢各位!