Java web实现综合查询+SQL语句拼接

首先展示图形界：

界面比较简单，但麻雀虽小五脏俱全。因为数据库只有六种数据类型，所以最多添加六个查询框。

测试以下问题：删除方式是从上往下开始的，如果删除之后会有问题。（后续改进ing）

若干个并且，或，不含在一起执行如果顺序不对会报错（sql语句拼接执行有问题）。

如果还有问题，敬请指正。

sql语句的拼接推荐（如果有更好的请忽略，可以先看一看拼接的方法再实现方法，不要一上来就抱住一团慢慢啃，例如我。。。嘿嘿）：

http://codingdict.com/article/6952

废话不多说，上代码：

Bean 层（基础层）：

 1 public class Bean {
 2     
 3     private String name;
 4     private String sex;
 5     private String minzu;
 6     private String zhucetime;
 7     private String age;
 8     private String mianmao;
 9     private String fuwuleibie;
10     public String getName() {
11         return name;
12     }
13     public void setName(String name) {
14         this.name = name;
15     }
16     public String getSex() {
17         return sex;
18     }
19     public void setSex(String sex) {
20         this.sex = sex;
21     }
22     public String getMinzu() {
23         return minzu;
24     }
25     public void setMinzu(String minzu) {
26         this.minzu = minzu;
27     }
28     public String getZhucetime() {
29         return zhucetime;
30     }
31     public void setZhucetime(String zhucetime) {
32         this.zhucetime = zhucetime;
33     }
34     public String getAge() {
35         return age;
36     }
37     public void setAge(String age) {
38         this.age = age;
39     }
40     public String getMianmao() {
41         return mianmao;
42     }
43     public void setMianmao(String mianmao) {
44         this.mianmao = mianmao;
45     }
46     public String getFuwuleibie() {
47         return fuwuleibie;
48     }
49     public void setFuwuleibie(String fuwuleibie) {
50         this.fuwuleibie = fuwuleibie;
51     }
52     public Bean(String name, String sex, String minzu, String zhucetime, String age, String mianmao,
53             String fuwuleibie) {
54         super();
55         this.name = name;
56         this.sex = sex;
57         this.minzu = minzu;
58         this.zhucetime = zhucetime;
59         this.age = age;
60         this.mianmao = mianmao;
61         this.fuwuleibie = fuwuleibie;
62     }
63     public Bean() {
64         // TODO Auto-generated constructor stub
65     }
66

DBUtil层 emm。。。关闭的方法有点多，但是使用的就只有一个，懒得删除了：

  1 package com.DBUtil;
  2 
  3 import java.beans.Statement;
  4 import java.sql.Connection;
  5 import java.sql.DriverManager;
  6 import java.sql.PreparedStatement;
  7 import java.sql.ResultSet;
  8 import java.sql.SQLException;
  9 
 10 public class DBUtil {
 11     public static String db_url="jdbc:mysql://localhost:3306/db_database01?serverTimezone=UTC&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false";
 12     public static String db_user="root";
 13     public static String db_password="101032";
 14     public static Connection getConn()  {
 15         Connection conn=null;
 16         try {
 17             Class.forName("com.mysql.cj.jdbc.Driver");
 18             try {
 19                 conn=DriverManager.getConnection(db_url, db_user, db_password);
 20             } catch (SQLException e) {
 21                 // TODO Auto-generated catch block
 22                 e.printStackTrace();
 23             }
 24             System.out.println("驱动链接加载成功！");
 25         } catch (ClassNotFoundException e) {
 26             // TODO Auto-generated catch block
 27             e.printStackTrace();
 28         }
 29         return conn;
 30         
 31     }
 32     
 33     public static void close(Statement state,Connection conn) {
 34         if(state!=null) {
 35             try {
 36                 ((Connection) state).close();
 37             } catch (SQLException e) {
 38                 // TODO Auto-generated catch block
 39                 e.printStackTrace();
 40             }
 41 
 42             
 43         }
 44         if(conn!=null) {
 45             try {
 46                 conn.close();
 47             } catch (SQLException e) {
 48                 // TODO Auto-generated catch block
 49                 e.printStackTrace();
 50             }
 51         }
 52     }
 53     
 54     public static void close(PreparedStatement state,Connection conn) {
 55         if(state!=null) {
 56             try {
 57                 ((Connection) state).close();
 58             } catch (SQLException e) {
 59                 // TODO Auto-generated catch block
 60                 e.printStackTrace();
 61             }
 62             
 63         }
 64         if(conn!=null) {
 65             try {
 66                 conn.close();
 67             } catch (SQLException e) {
 68                 // TODO Auto-generated catch block
 69                 e.printStackTrace();
 70             }
 71         }
 72     }
 73     
 74     public static void close(ResultSet rs,Statement state,Connection conn) {
 75         if(rs!=null) {
 76             try {
 77                 rs.close();
 78             } catch (SQLException e) {
 79                 // TODO Auto-generated catch block
 80                 e.printStackTrace();
 81             }
 82         }
 83         if(state!=null) {
 84             try {
 85                 ((Connection) state).close();
 86             } catch (SQLException e) {
 87                 // TODO Auto-generated catch block
 88                 e.printStackTrace();
 89             }
 90         }
 91         if(conn!=null) {
 92             try {
 93                 conn.close();
 94             } catch (SQLException e) {
 95                 // TODO Auto-generated catch block
 96                 e.printStackTrace();
 97             }
 98         }
 99     }
100 
101     @SuppressWarnings("static-access")
102     public static void main(String[] args) throws SQLException {
103         DBUtil dbu=new DBUtil();
104         dbu.getConn();
105     }
106 
107     public static void close(java.sql.Statement state, Connection conn) {
108         // TODO Auto-generated method stub
109         if(state!=null)
110         {
111             try {
112                 state.close();
113             } catch (SQLException e) {
114                 // TODO Auto-generated catch block
115                 e.printStackTrace();
116             }
117         }
118         if(conn!=null) {
119             try {
120                 conn.close();
121             } catch (SQLException e) {
122                 // TODO Auto-generated catch block
123                 e.printStackTrace();
124             }
125         }
126     }
127 }

Dao层（由于是之前的程序基础上改写的，有点乱，就该查询来说无用的程序比较多）：

在多条件查询的方法当中sql语句不是在Dao层生成的，而是在Servlet 层拼接好之后传递过来的。

  1 import java.sql.Connection;
  2 import java.sql.ResultSet;
  3 import java.sql.SQLException;
  4 import java.sql.Statement;
  5 import java.util.ArrayList;
  6 import java.util.List;
  7 
  8 import com.Bean.Bean;
  9 import com.DBUtil.DBUtil;
 10 
 11 public class Dao {
 12     
 13     //遍历
 14         public static List<Bean> show() {
 15             List<Bean>list =new ArrayList<>();
 16             Connection conn=DBUtil.getConn();
 17             String sql="select * from tb_qingnian";
 18             Statement pstmt = null;
 19             try {
 20                 pstmt = conn.createStatement();
 21                 ResultSet rs=pstmt.executeQuery(sql);
 22                 Bean use=null;
 23                 while(rs.next()) {
 24                     String name=rs.getString("name");
 25                     String sex=rs.getString("sex");
 26                     String minzu=rs.getString("minzu");
 27                     String zhucetime=rs.getString("zhucetime");
 28                     String age=rs.getString("age");
 29                     String mianmao=rs.getString("mianmao");
 30                     String fuwuleibie=rs.getString("fuwuleibie");
 31                     use=new Bean(name,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
 32                     list.add(use);
 33                 }
 34             } catch (SQLException e) {
 35                 // TODO Auto-generated catch block
 36                 e.printStackTrace();
 37             }
 38             finally {
 39                 DBUtil.close(pstmt, conn);
 40                 }
 41             
 42             return list;
 43             }
 44         
 45         //添加
 46         public static boolean insert(Bean e) {
 47             System.out.println("添加中");
 48             String sql="insert into tb_qingnian(name,sex,minzu,zhucetime,age,mianmao,fuwuleibie) values('"+e.getName()+"','"+e.getSex()+"','"+e.getMinzu()+"','"+e.getZhucetime()+"','"+e.getAge()+"','"+e.getMianmao()+"','"+e.getFuwuleibie()+"')";
 49             Connection conn=DBUtil.getConn();
 50             Statement state=null;
 51             
 52             try {
 53                 state= conn.createStatement();
 54                 state.executeUpdate(sql);
 55             } catch (SQLException e1) {
 56                 // TODO Auto-generated catch block
 57                 e1.printStackTrace();
 58             }
 59             finally {
 60                 DBUtil.close(state, conn);
 61             }
 62             return false;
 63             
 64         }
 65         
 66         //修改
 67         public static boolean update(Bean e) {
 68             System.out.println("修改中");
 69             String sql="update tb_qingnian set sex='"+e.getSex()+"',mianmao='"+e.getMianmao()+"',fuwuleibie='"+e.getFuwuleibie()+"'   where name='"+e.getName()+"' ";
 70             Connection conn=DBUtil.getConn();
 71             Statement pstm=null;
 72             try {
 73                 pstm=conn.createStatement();
 74                 pstm.executeUpdate(sql);
 75             } catch (SQLException e1) {
 76                 // TODO Auto-generated catch block
 77                 
 78                 e1.printStackTrace();
 79             }finally{
 80                 DBUtil.close(pstm, conn);
 81                 }
 82             return false;
 83             
 84         }
 85         
 86         //删除 按照姓名
 87         public static boolean delete(String name) {
 88             String sql="delete from tb_qingnian where name='"+name+"'"  ;
 89             Connection conn=DBUtil.getConn();
 90             Statement pstm=null;
 91             try {
 92                 pstm=conn.createStatement();
 93                 pstm.executeUpdate(sql);
 94             } catch (SQLException e) {
 95                 // TODO Auto-generated catch block
 96                 e.printStackTrace();
 97             }
 98             finally {
 99                 DBUtil.close(pstm, conn);
100             }
101             
102             return false;
103         }
104         
105         //按姓名查询 可模糊查询 
106         public static List<Bean> search(String info) {
107             List<Bean>list =new ArrayList<>();
108             Connection conn=DBUtil.getConn();
109             String sql="select * from tb_qingnian where name like'%"+info+"%'";
110             Statement pstmt = null;
111             try {
112                 pstmt = conn.createStatement();
113                 ResultSet rs=pstmt.executeQuery(sql);
114                 Bean use=null;
115                 while(rs.next()) {
116                     String name1=rs.getString("name");
117                     String sex=rs.getString("sex");
118                     String minzu=rs.getString("minzu");
119                     String zhucetime=rs.getString("zhucetime");
120                     String age=rs.getString("age");
121                     String mianmao=rs.getString("mianmao");
122                     String fuwuleibie=rs.getString("fuwuleibie");
123                     use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
124                     list.add(use);
125                 }
126             } catch (SQLException e) {
127                 // TODO Auto-generated catch block
128                 e.printStackTrace();
129             }
130             finally {
131                 DBUtil.close(pstmt, conn);
132                 }
133             
134             return list;
135             
136         }
137         
138         
139         //多条件综合查询 代码执行   使用list进行遍历操作
140         public static List<Bean> searchs(String sql) {
141             List<Bean>list =new ArrayList<>();
142             Connection conn=DBUtil.getConn();
143             //String sql="select * from tb_qingnian where name like'%"+info+"%'";
144             Statement pstmt = null;
145             try {
146                 pstmt = conn.createStatement();
147                 ResultSet rs=pstmt.executeQuery(sql);
148                 Bean use=null;
149                 while(rs.next()) {
150                     String name1=rs.getString("name");
151                     String sex=rs.getString("sex");
152                     String minzu=rs.getString("minzu");
153                     String zhucetime=rs.getString("zhucetime");
154                     String age=rs.getString("age");
155                     String mianmao=rs.getString("mianmao");
156                     String fuwuleibie=rs.getString("fuwuleibie");
157                     use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
158                     list.add(use);
159                 }
160             } catch (SQLException e) {
161                 // TODO Auto-generated catch block
162                 e.printStackTrace();
163             }
164             finally {
165                 DBUtil.close(pstmt, conn);
166                 }
167             
168             return list;
169             
170         }
171         
172         
173         
174         
175         
176         
177          //性别
178         public static List<Bean> search1(String info) {
179             List<Bean>list =new ArrayList<>();
180             Connection conn=DBUtil.getConn();
181             String sql="select * from tb_qingnian where sex like'%"+info+"%'";
182             Statement pstmt = null;
183             try {
184                 pstmt = conn.createStatement();
185                 ResultSet rs=pstmt.executeQuery(sql);
186                 Bean use=null;
187                 while(rs.next()) {
188                     String name1=rs.getString("name");
189                     String sex=rs.getString("sex");
190                     String minzu=rs.getString("minzu");
191                     String zhucetime=rs.getString("zhucetime");
192                     String age=rs.getString("age");
193                     String mianmao=rs.getString("mianmao");
194                     String fuwuleibie=rs.getString("fuwuleibie");
195                     use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
196                     list.add(use);
197                 }
198             } catch (SQLException e) {
199                 // TODO Auto-generated catch block
200                 e.printStackTrace();
201             }
202             finally {
203                 DBUtil.close(pstmt, conn);
204                 }
205             
206             return list;
207         }
208         //民族
209         public static List<Bean> search2(String info) {
210             List<Bean>list =new ArrayList<>();
211             Connection conn=DBUtil.getConn();
212             String sql="select * from tb_qingnian where minzu like'%"+info+"%'";
213             Statement pstmt = null;
214             try {
215                 pstmt = conn.createStatement();
216                 ResultSet rs=pstmt.executeQuery(sql);
217                 Bean use=null;
218                 while(rs.next()) {
219                     String name1=rs.getString("name");
220                     String sex=rs.getString("sex");
221                     String minzu=rs.getString("minzu");
222                     String zhucetime=rs.getString("zhucetime");
223                     String age=rs.getString("age");
224                     String mianmao=rs.getString("mianmao");
225                     String fuwuleibie=rs.getString("fuwuleibie");
226                     use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
227                     list.add(use);
228                 }
229             } catch (SQLException e) {
230                 // TODO Auto-generated catch block
231                 e.printStackTrace();
232             }
233             finally {
234                 DBUtil.close(pstmt, conn);
235                 }
236             
237             return list;
238         }
239         //政治面貌
240         public static List<Bean> search3(String info) {
241             List<Bean>list =new ArrayList<>();
242             Connection conn=DBUtil.getConn();
243             String sql="select * from tb_qingnian where mianmao like'%"+info+"%'";
244             Statement pstmt = null;
245             try {
246                 pstmt = conn.createStatement();
247                 ResultSet rs=pstmt.executeQuery(sql);
248                 Bean use=null;
249                 while(rs.next()) {
250                     String name1=rs.getString("name");
251                     String sex=rs.getString("sex");
252                     String minzu=rs.getString("minzu");
253                     String zhucetime=rs.getString("zhucetime");
254                     String age=rs.getString("age");
255                     String mianmao=rs.getString("mianmao");
256                     String fuwuleibie=rs.getString("fuwuleibie");
257                     use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
258                     list.add(use);
259                 }
260             } catch (SQLException e) {
261                 // TODO Auto-generated catch block
262                 e.printStackTrace();
263             }
264             finally {
265                 DBUtil.close(pstmt, conn);
266                 }
267             
268             return list;
269         }
270         //服务类别
271         public static List<Bean> search4(String info) {
272             List<Bean>list =new ArrayList<>();
273             Connection conn=DBUtil.getConn();
274             String sql="select * from tb_qingnian where fuwuleibie like'%"+info+"%'";
275             Statement pstmt = null;
276             try {
277                 pstmt = conn.createStatement();
278                 ResultSet rs=pstmt.executeQuery(sql);
279                 Bean use=null;
280                 while(rs.next()) {
281                     String name1=rs.getString("name");
282                     String sex=rs.getString("sex");
283                     String minzu=rs.getString("minzu");
284                     String zhucetime=rs.getString("zhucetime");
285                     String age=rs.getString("age");
286                     String mianmao=rs.getString("mianmao");
287                     String fuwuleibie=rs.getString("fuwuleibie");
288                     use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
289                     list.add(use);
290                 }
291             } catch (SQLException e) {
292                 // TODO Auto-generated catch block
293                 e.printStackTrace();
294             }
295             finally {
296                 DBUtil.close(pstmt, conn);
297                 }
298             
299             return list;
300         }
301         //注册时间
302         public static List<Bean> search5(String info) {
303             List<Bean>list =new ArrayList<>();
304             Connection conn=DBUtil.getConn();
305             String sql="select * from tb_qingnian where zhucetime like'%"+info+"%'";
306             Statement pstmt = null;
307             try {
308                 pstmt = conn.createStatement();
309                 ResultSet rs=pstmt.executeQuery(sql);
310                 Bean use=null;
311                 while(rs.next()) {
312                     String name1=rs.getString("name");
313                     String sex=rs.getString("sex");
314                     String minzu=rs.getString("minzu");
315                     String zhucetime=rs.getString("zhucetime");
316                     String age=rs.getString("age");
317                     String mianmao=rs.getString("mianmao");
318                     String fuwuleibie=rs.getString("fuwuleibie");
319                     use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
320                     list.add(use);
321                 }
322             } catch (SQLException e) {
323                 // TODO Auto-generated catch block
324                 e.printStackTrace();
325             }
326             finally {
327                 DBUtil.close(pstmt, conn);
328                 }
329             
330             return list;
331         }
332         
333         
334         
335         public static void main(String[] args) {
336             Bean e=new Bean("李","男","汉","2015-02-10","24","党员","志愿者");
337             insert(e);
338             List<Bean> list =show();
339             for(int i=0;i<list.size();i++) {
340                 Bean s=list.get(i);
341                 System.out.println(s.getName());
342             }
343             String a="李四";
344             //delete(a);
345         }
346 
347 
348 }

Servlet 层通过jsp传递过来的参数来拼接sql语句然后执行sql语句（前面的查询通过了Service 层来传递，就该方法直接调用了Dao层的方法），查询信息：

  1 package com.Servlet;
  2 
  3 import java.io.IOException;
  4 import java.io.UnsupportedEncodingException;
  5 import java.util.List;
  6 
  7 import javax.servlet.ServletException;
  8 import javax.servlet.annotation.WebServlet;
  9 import javax.servlet.http.HttpServlet;
 10 import javax.servlet.http.HttpServletRequest;
 11 import javax.servlet.http.HttpServletResponse;
 12 
 13 import com.Bean.Bean;
 14 import com.Dao.Dao;
 15 
 16 
 17 /**
 18  * Servlet implementation class Servlet
 19  */
 20 @WebServlet("/Servlet")
 21 public class Servlet extends HttpServlet {
 22     private static final long serialVersionUID = 1L;
 23     //创建service层
 24     Service service=new Service();
 25     
 26 protected void service(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {
 27         //通过method 传递参数获取需要调用的方法
 28         req.setCharacterEncoding("utf-8");
 29         String method=req.getParameter("method");
 30         if("insert".equalsIgnoreCase(method)) {
 31             insert(req,resp);
 32         }else if("update".equals(method)) {
 33             update(req,resp);
 34         }else if("delete".equals(method)) {
 35             Delete(req,resp);
 36         }
 37         else if("show".equals(method)) {
 38             show(req,resp);
 39           }
 40         else if("search".equals(method)) {
 41             search(req,resp);
 42         }
 43         else if("searchs".equals(method)) {
 44             searchs(req,resp);
 45         }
 46         }
 47 
 48 // 多条件并发式查询
 49    private void searchs(HttpServletRequest req, HttpServletResponse resp) {
 50     // TODO Auto-generated method stub
 51     // TODO Auto-generated method stub
 52             try {
 53                 req.setCharacterEncoding("utf-8");
 54             } catch (UnsupportedEncodingException e) {
 55                 // TODO Auto-generated catch block
 56                 e.printStackTrace();
 57             }
 58             
 59             String type1=req.getParameter("type1");   //获取查找的信息的参数 如 姓名，性别等
 60             String type2=req.getParameter("type2");
 61             String type3=req.getParameter("type3");
 62             String type4=req.getParameter("type4");
 63             String type5=req.getParameter("type5");
 64             String type6=req.getParameter("type6");
 65             String sql="select * from tb_qingnian where 1=1 ";  //最基础的sql语句，在此基础上进行拼接
 66 
 67             String SearchBox1=req.getParameter("SearchBox1");   //获取需要查找的信息
 68             
 69             String method1=req.getParameter("method1");   //获取查找的方式  模糊查找或者精确查找
 70             String method2=req.getParameter("method2"); 
 71             String method3=req.getParameter("method3");
 72             String method4=req.getParameter("method4");
 73             String method5=req.getParameter("method5");
 74             String method6=req.getParameter("method6");
 75             
 76             String con2=req.getParameter("con2");    //获取与前一个条件的关系  与  and  或 or 非  not
 77             String con3=req.getParameter("con3");     
 78             String con4=req.getParameter("con4");
 79             String con5=req.getParameter("con5");
 80             String con6=req.getParameter("con6");
 81             System.out.println(con2);
 82             System.out.println(con3);
 83             System.out.println(con4);
 84             System.out.println(con5);
 85             System.out.println(con6);
 86 
 87             //sql语句的拼接操作
 88             //1  
 89             if(type1!=null) {
 90 
 91                 if(!SearchBox1.equals("")&&SearchBox1!=null) {
 92                     if(method1.equals("mohu"))
 93                     {
 94                         sql=sql+"and "+type1+" like '%"+SearchBox1+"%'";}
 95                     else {
 96                     sql=sql+"and "+type1+"='"+SearchBox1+"'";
 97                     }
 98                 }
 99             }
100             System.out.println(sql);
101             //2
102             if(type2!=null) {
103                 String SearchBox2=req.getParameter("SearchBox2");
104                 System.out.println(SearchBox2);
105                 if(!SearchBox2.equals("")&&SearchBox2!=null) {
106                     if(method2.equals("mohu"))
107                     {
108                         sql=sql+" "+con2+" "+type2+" like '%"+SearchBox2+"%'";}
109                     else {
110                     sql=sql+" "+con2+" "+type2+"='"+SearchBox2+"'";
111                     }
112                 }
113             }
114             System.out.println(sql);
115             //3
116             if(type3!=null) {
117                 String SearchBox3=req.getParameter("SearchBox3");
118                 System.out.println(SearchBox3);
119                 if(!SearchBox3.equals("")&&SearchBox3!=null) {
120                     if(method3.equals("mohu"))
121                     {
122                         sql=sql+" "+con3+" "+type3+" like '%"+SearchBox3+"%'";}
123                     else {
124                     sql=sql+" "+con3+" "+type3+"='"+SearchBox3+"'";
125                     }
126                 }
127             }
128             //4
129             if(type4!=null) {
130                 
131                 String SearchBox4=req.getParameter("SearchBox4");
132                 System.out.println(SearchBox4);
133                 if(!SearchBox4.equals("")&&SearchBox4!=null) {
134                     if(method4.equals("mohu"))
135                     {
136                         sql=sql+" "+con4+" "+type4+" like '%"+SearchBox4+"%'";}
137                     else {
138                     sql=sql+" "+con4+" "+type4+"='"+SearchBox4+"'";
139                     }
140                 }
141             }
142             //5
143             if(type5!=null) {
144                 String SearchBox5=req.getParameter("SearchBox5");
145                 System.out.println(SearchBox5);
146                 if(!SearchBox5.equals("")&&SearchBox5!=null) {
147                     if(method5.equals("mohu"))
148                     {
149                         sql=sql+" "+con5+" "+type5+" like '%"+SearchBox5+"%'";}
150                     else {
151                     sql=sql+" "+con5+" "+type5+"='"+SearchBox5+"'";
152                     }
153                 }
154             }
155             
156             //6
157             if(type6!=null) {
158                 String SearchBox6=req.getParameter("SearchBox6");
159                 System.out.println(SearchBox6);
160                 if(!SearchBox6.equals("")&&SearchBox6!=null) {
161                     if(method6.equals("mohu"))
162                     {
163                         sql=sql+" "+con6+" "+type6+" like '%"+SearchBox6+"%'";}
164                     else {
165                     sql=sql+" "+con6+" "+type6+"='"+SearchBox6+"'";
166                     }
167                 }
168             }
169             
170             
171             
172             List<Bean> list=Dao.searchs(sql);
173             if(!list.isEmpty()) {
174             req.setAttribute("list", list);
175             
176             try {
177                 req.getRequestDispatcher("show.jsp").forward(req, resp);
178             } catch (ServletException e) {
179                 // TODO Auto-generated catch block
180                 e.printStackTrace();
181             } catch (IOException e) {
182                 // TODO Auto-generated catch block
183                 e.printStackTrace();
184             }
185             }else {
186                 try {
187                     req.getRequestDispatcher("error.jsp").forward(req, resp);
188                 } catch (ServletException e) {
189                     // TODO Auto-generated catch block
190                     e.printStackTrace();
191                 } catch (IOException e) {
192                     // TODO Auto-generated catch block
193                     e.printStackTrace();
194                 }
195             }
196 }
197 
198 
199 // 查询信息
200 private void search(HttpServletRequest req, HttpServletResponse resp) {
201     // TODO Auto-generated method stub
202     try {
203         req.setCharacterEncoding("utf-8");
204     } catch (UnsupportedEncodingException e) {
205         // TODO Auto-generated catch block
206         e.printStackTrace();
207     }
208     String info=req.getParameter("name");
209     String search=req.getParameter("search");
210     //按姓名查找
211     if(search.equals("name")) {
212         List<Bean> list=service.search(info);
213         req.setAttribute("list", list);
214     }
215     //按性别查询
216     else if(search.equals("sex")) {
217         List<Bean> list=service.search1(info);
218         req.setAttribute("list", list);
219     }
220     //按民族查询
221     else if(search.equals("minzu")) {
222         List<Bean> list=service.search2(info);
223         req.setAttribute("list", list);
224     }
225     //按政治面貌查询
226     else if(search.equals("mianmao")) {
227         List<Bean> list=service.search3(info);
228         req.setAttribute("list", list);
229     }
230     //按服务类别查询 可模糊查询
231     else if(search.equals("fuwuleibie")) {
232         List<Bean> list=service.search4(info);
233         req.setAttribute("list", list);
234     }
235     //按注册时间查询 可模糊查询
236     else if(search.equals("zhucetime")) {
237         List<Bean> list=service.search5(info);
238         req.setAttribute("list", list);
239     }
240     
241     try {
242         req.getRequestDispatcher("show.jsp").forward(req, resp);
243     } catch (ServletException e) {
244         // TODO Auto-generated catch block
245         e.printStackTrace();
246     } catch (IOException e) {
247         // TODO Auto-generated catch block
248         e.printStackTrace();
249     }
250     
251 }
252 
253   //遍历数据库
254 private void show(HttpServletRequest req, HttpServletResponse resp) {
255     // TODO Auto-generated method stub
256     try {
257         req.setCharacterEncoding("utf-8");
258     } catch (UnsupportedEncodingException e) {
259         // TODO Auto-generated catch block
260         e.printStackTrace();
261     }
262     List<Bean> list= service.show();
263     req.setAttribute("list", list);
264     try {
265         req.getRequestDispatcher("show.jsp").forward(req, resp);
266     } catch (ServletException e) {
267         // TODO Auto-generated catch block
268         e.printStackTrace();
269     } catch (IOException e) {
270         // TODO Auto-generated catch block
271         e.printStackTrace();
272     }
273     
274 }
275    //删除信息
276 private void Delete(HttpServletRequest req, HttpServletResponse resp) {
277     // TODO Auto-generated method stub
278     try {
279         req.setCharacterEncoding("utf-8");
280     } catch (UnsupportedEncodingException e) {
281         // TODO Auto-generated catch block
282         e.printStackTrace();
283     }
284     String name=req.getParameter("name");
285     System.out.println(name);
286     service.delete(name);
287     try {
288         req.getRequestDispatcher("main.jsp").forward(req, resp);
289     } catch (ServletException e) {
290         // TODO Auto-generated catch block
291         e.printStackTrace();
292     } catch (IOException e) {
293         // TODO Auto-generated catch block
294         e.printStackTrace();
295     }
296     
297 }
298 
299 private void update(HttpServletRequest req, HttpServletResponse resp) {
300     // TODO Auto-generated method stub
301     try {
302         req.setCharacterEncoding("utf-8");
303     } catch (UnsupportedEncodingException e) {
304         // TODO Auto-generated catch block
305         e.printStackTrace();
306     }
307     String name=req.getParameter("name");
308     String sex=req.getParameter("sex");
309     String mianmao=req.getParameter("mianmao");
310     //多个选项，变化为String类型存储
311     String[] list=req.getParameterValues("fuwuleibie");
312     String fuwuleibie="";
313     for(int i=0;i<list.length;i++) {
314         fuwuleibie+=list[i];
315         if(i!=list.length-1) {
316             fuwuleibie+="，";
317         }
318     }
319     
320     Bean use=new Bean(name,sex,"","","",mianmao,fuwuleibie);
321     System.out.println(use.getSex());
322     System.out.println("servlet 修改");
323     service.update(use);
324     try {
325         req.getRequestDispatcher("Servlet?method=show").forward(req, resp);
326     } catch (ServletException e) {
327         // TODO Auto-generated catch block
328         e.printStackTrace();
329     } catch (IOException e) {
330         // TODO Auto-generated catch block
331         e.printStackTrace();
332     }
333     
334 }
335 
336 private void insert(HttpServletRequest req, HttpServletResponse resp) {
337     // TODO Auto-generated method stub
338         try {
339             req.setCharacterEncoding("utf-8");
340         } catch (UnsupportedEncodingException e) {
341             // TODO Auto-generated catch block
342             e.printStackTrace();
343         }
344         String name=req.getParameter("name");
345         String sex=req.getParameter("sex");
346         String minzu=req.getParameter("minzu");
347         String zhucetime=req.getParameter("zhucetime");
348         String age=req.getParameter("age");
349         String mianmao=req.getParameter("mianmao");
350         String[] list=req.getParameterValues("fuwuleibie");
351         String fuwuleibie="";
352         for(int i=0;i<list.length;i++) {
353             fuwuleibie+=list[i];
354             if(i!=list.length-1) {
355                 fuwuleibie+="，";
356             }
357         }
358         System.out.println(fuwuleibie);
359         Bean e=new Bean(name,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
360         if(service.insert(e)) {
361             try {
362                 req.setAttribute("message","添加成功");
363                 req.getRequestDispatcher("Servlet?method=show").forward(req, resp);
364             } catch (ServletException e1) {
365                 // TODO Auto-generated catch block
366                 e1.printStackTrace();
367             } catch (IOException e1) {
368                 // TODO Auto-generated catch block
369                 e1.printStackTrace();
370             }
371         }else {
372             try {
373                 
374                 req.setAttribute("message", "添加重复,请重新输入");  
375                 req.getRequestDispatcher("insert.jsp").forward(req, resp);
376             } catch (ServletException e1) {
377                 // TODO Auto-generated catch block
378                 e1.printStackTrace();
379             } catch (IOException e1) {
380                 // TODO Auto-generated catch block
381                 e1.printStackTrace();
382             }
383         }
384 }
385   
386 
387 }

jsp界面：

对于应且或者不含的三个条件的选择框的 value 值直接设置为了 “and” “or” “and not ”，在拼接时可以直接获取值进行拼接

查找界面:

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <!DOCTYPE html>
 4 <html>
 5 <head>
 6 <meta charset="UTF-8">
 7 <title>Insert title here</title>
 8 </head>
 9 <body>
10 
11 <div align="center">  
12 <!--              &#43: + &#45 : -   -->
13             <button onclick="insert_row()" type="button" >&#43</button>     
14             <button onclick="deleteRow(this)" type="button" >&#45</button>
15     <form action="Servlet?method=searchs" method="post"> 
16         <table name="tbl" id="tbl">
17             <tr>
18                 <td>
19                     <select id="type1" name="type1">
20                         <option value="name">姓名</option>
21                         <option value="minzu">民族</option>
22                         <option value="sex">性别</option>
23                         <option value="mianmu">政治面目</option>
24                         <option value="fuwuleibie">服务类别</option>
25                         <option value="zhucetime">注册时间</option>
26                     </select>
27                     <input type="text" id="SearchBox1" name="SearchBox1">
28                     <select id="method1"  name="method1">
29                         <option value="jingque">精确</option>
30                         <option value="mohu">模糊</option>
31                     </select>
32                     
33 <!--                     <select id="con"  name="con"> -->
34 <!--                         <option value="and">并且</option> -->
35 <!--                         <option value="or">或者</option> -->
36 <!--                         <option value="no">不含</option> -->
37 <!--                     </select> -->
38                                 
39                 </td>
40             </tr>
41         </table>
42         
43         <div id="SearchButton">
44             <button id="Button" type="submit" >查询</button>
45         </div>
46        <div>
47                <a href="Servlet?method=show">信息浏览</a>
48          </div>
49      </form>
50 </div>
51 </body>
52 <script type="text/javascript">
53     var i=1;
54     var first=1;
55     function insert_row(){
56         if(i<6){
57         i++;
58         R=tbl.insertRow(); //insertRow() 方法用于在表格中的指定位置插入一个新行。
59         C=R.insertCell();  //insertCell() 方法用于在 HTML 表的一行的指定位置插入一个空的 <td> 元素。
60          C.innerHTML = 
61             "<select id='con"+i+"'  name='con"+i+"'>"+
62             "<option value='and'>并且</option>"+
63             "<option value='or'>或者</option>"+
64             "<option value=' and  not'>不含</option>"+
65         "</select>"+
66         "<select id='type"+i+"' name='type"+i+"'>"+
67          "<option value='name' >姓名</option>"+
68          "<option value='sex' >性别</option>"+
69          "<option value='age'>年龄</option>"+
70          "<option value='mianmao'>政治面目</option>"+
71          "<option value='zhucetime'>注册时间</option>"+
72          "<option value='fuwuleibie'>服务类别</option>"+
73          "</select>"+"&nbsp;<input id='SearchBox"+i+"' name='SearchBox"+i+"' />"+
74          "<select id='method"+i+"' name='method"+i+"'>"+
75             "<option value='jingque'>精确</option>"+
76             "<option value='mohu'>模糊</option>"+
77           "</select>"
78         }else{
79             alert("查询条件冲突！");
80         }
81     
82     }
83     
84     function deleteRow(obj){
85         if(i>1){
86          alert("确定删除一行吗？");
87          tbl.deleteRow(obj.parentElement.parentElement.rowIndex);
88          
89          i--;
90         }
91     }
92 
93 </script>
94 
95 </body>
96 </html>

显示界面 (需要用到list 遍历，需要 jstl.jar)：

可能会有收费，多找找总会有免费的。（万能的度娘在上见证，总会找到的。。嘿嘿）

 1 <%@ page language="java" contentType="text/html; charset=utf-8"
 2     pageEncoding="utf-8"%>
 3     <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
 4 <!DOCTYPE html>
 5 <html>
 6 <head>
 7 <meta charset="utf-8">
 8 <title>遍历志愿者信息</title>
 9 </head>
10 <body>
11 <div align="center">
12 <h1>志愿者信息浏览：</h1>
13 <a href="indexfind.jsp">返回</a>
14 <form action="Servlet?method=show" method="post" onsubmit="return check()">
15 <table>
16 <tr>
17 <td>姓名</td>
18 <td>性别</td>
19 <td>民族</td>
20 <td>注册时间</td>
21 <td>年龄</td>
22 <td>面貌</td>
23 <td>服务类别</td>
24  <td align="center" colspan="2">操作</td>
25 </tr>
26 <c:forEach items="${list}" var="item">
27 <tr>
28  <td>${item.name}</td>
29  <td>${item.sex}</td>
30  <td>${item.minzu}</td>
31  <td>${item.zhucetime}</td>
32  <td>${item.age}</td>
33  <td>${item.mianmao}</td>
34  <td>${item.fuwuleibie}</td>
35  <td><a href="update.jsp?name=${item.name}&sex=${item.sex}&mianmao=${item.mianmao}&fuwuleibie=${item.fuwuleibie}">修改</a></td>
36 <!--  <td><a href="javaScript:if(confirm('确定删除吗?')){location.href='Servlet?method=delete'}">删除</a></td>  -->
37 <%-- <td><a href="javaScript:if(confirm('确定删除吗?')){location.href='delete.jsp'?name=${item.name}}">删除</a></td>  --%>
38 <td> <a href="delete.jsp?minzu=${item.minzu}&zhucetime=${item.zhucetime}&age=${item.age}&name=${item.name}&sex=${item.sex}&mianmao=${item.mianmao}&fuwuleibie=${item.fuwuleibie}">删除</a>
39 </tr>
40 </c:forEach>
41 </table>
42 </form>
43 </div>

如果没有查找到信息会跳转到一个提示界面。

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <!DOCTYPE html>
 4 <html>
 5 <head>
 6 <meta charset="UTF-8">
 7 <title>错误信息展示</title>
 8 </head>
 9 <body>
10 <div align="center">
11 <a href="indexfind.jsp">返回</a>
12 <div>
13 <h3>未找到查询信息</h3>
14 </div>
15 </div>
16 </body>
17 </html>

package com.Servlet;
import java.io.IOException;import java.io.UnsupportedEncodingException;import java.util.List;
import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;
import com.Bean.Bean;import com.Dao.Dao;

/** * Servlet implementation class Servlet */@WebServlet("/Servlet")public class Servlet extends HttpServlet {private static final long serialVersionUID = 1L; //创建service层Service service=new Service();protected void service(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {//通过method 传递参数获取需要调用的方法req.setCharacterEncoding("utf-8");String method=req.getParameter("method");if("insert".equalsIgnoreCase(method)) {insert(req,resp);}else if("update".equals(method)) {update(req,resp);}else if("delete".equals(method)) {Delete(req,resp);}else if("show".equals(method)) {show(req,resp); }else if("search".equals(method)) {search(req,resp);}else if("searchs".equals(method)) {searchs(req,resp);}}
// 多条件并发式查询 private void searchs(HttpServletRequest req, HttpServletResponse resp) {// TODO Auto-generated method stub// TODO Auto-generated method stubtry {req.setCharacterEncoding("utf-8");} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();}String type1=req.getParameter("type1"); //获取查找的信息的参数如姓名，性别等String type2=req.getParameter("type2");String type3=req.getParameter("type3");String type4=req.getParameter("type4");String type5=req.getParameter("type5");String type6=req.getParameter("type6");String sql="select * from tb_qingnian where 1=1 "; //最基础的sql语句，在此基础上进行拼接
String SearchBox1=req.getParameter("SearchBox1"); //获取需要查找的信息String method1=req.getParameter("method1"); //获取查找的方式模糊查找或者精确查找String method2=req.getParameter("method2"); String method3=req.getParameter("method3");String method4=req.getParameter("method4");String method5=req.getParameter("method5");String method6=req.getParameter("method6");String con2=req.getParameter("con2"); //获取与前一个条件的关系与 and 或 or 非 notString con3=req.getParameter("con3"); String con4=req.getParameter("con4");String con5=req.getParameter("con5");String con6=req.getParameter("con6");System.out.println(con2);System.out.println(con3);System.out.println(con4);System.out.println(con5);System.out.println(con6);
//sql语句的拼接操作//1 if(type1!=null) {
if(!SearchBox1.equals("")&&SearchBox1!=null) {if(method1.equals("mohu")){sql=sql+"and "+type1+" like '%"+SearchBox1+"%'";}else {sql=sql+"and "+type1+"='"+SearchBox1+"'";}}}System.out.println(sql);//2if(type2!=null) {String SearchBox2=req.getParameter("SearchBox2");System.out.println(SearchBox2);if(!SearchBox2.equals("")&&SearchBox2!=null) {if(method2.equals("mohu")){sql=sql+" "+con2+" "+type2+" like '%"+SearchBox2+"%'";}else {sql=sql+" "+con2+" "+type2+"='"+SearchBox2+"'";}}}System.out.println(sql);//3if(type3!=null) {String SearchBox3=req.getParameter("SearchBox3");System.out.println(SearchBox3);if(!SearchBox3.equals("")&&SearchBox3!=null) {if(method3.equals("mohu")){sql=sql+" "+con3+" "+type3+" like '%"+SearchBox3+"%'";}else {sql=sql+" "+con3+" "+type3+"='"+SearchBox3+"'";}}}//4if(type4!=null) {String SearchBox4=req.getParameter("SearchBox4");System.out.println(SearchBox4);if(!SearchBox4.equals("")&&SearchBox4!=null) {if(method4.equals("mohu")){sql=sql+" "+con4+" "+type4+" like '%"+SearchBox4+"%'";}else {sql=sql+" "+con4+" "+type4+"='"+SearchBox4+"'";}}}//5if(type5!=null) {String SearchBox5=req.getParameter("SearchBox5");System.out.println(SearchBox5);if(!SearchBox5.equals("")&&SearchBox5!=null) {if(method5.equals("mohu")){sql=sql+" "+con5+" "+type5+" like '%"+SearchBox5+"%'";}else {sql=sql+" "+con5+" "+type5+"='"+SearchBox5+"'";}}}//6if(type6!=null) {String SearchBox6=req.getParameter("SearchBox6");System.out.println(SearchBox6);if(!SearchBox6.equals("")&&SearchBox6!=null) {if(method6.equals("mohu")){sql=sql+" "+con6+" "+type6+" like '%"+SearchBox6+"%'";}else {sql=sql+" "+con6+" "+type6+"='"+SearchBox6+"'";}}}List<Bean> list=Dao.searchs(sql);if(!list.isEmpty()) {req.setAttribute("list", list);try {req.getRequestDispatcher("show.jsp").forward(req, resp);} catch (ServletException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}else {try {req.getRequestDispatcher("error.jsp").forward(req, resp);} catch (ServletException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

// 查询信息private void search(HttpServletRequest req, HttpServletResponse resp) {// TODO Auto-generated method stubtry {req.setCharacterEncoding("utf-8");} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();}String info=req.getParameter("name");String search=req.getParameter("search");//按姓名查找if(search.equals("name")) {List<Bean> list=service.search(info);req.setAttribute("list", list);}//按性别查询else if(search.equals("sex")) {List<Bean> list=service.search1(info);req.setAttribute("list", list);}//按民族查询else if(search.equals("minzu")) {List<Bean> list=service.search2(info);req.setAttribute("list", list);}//按政治面貌查询else if(search.equals("mianmao")) {List<Bean> list=service.search3(info);req.setAttribute("list", list);}//按服务类别查询可模糊查询else if(search.equals("fuwuleibie")) {List<Bean> list=service.search4(info);req.setAttribute("list", list);}//按注册时间查询可模糊查询else if(search.equals("zhucetime")) {List<Bean> list=service.search5(info);req.setAttribute("list", list);}try {req.getRequestDispatcher("show.jsp").forward(req, resp);} catch (ServletException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}
//遍历数据库private void show(HttpServletRequest req, HttpServletResponse resp) {// TODO Auto-generated method stubtry {req.setCharacterEncoding("utf-8");} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();}List<Bean> list= service.show();req.setAttribute("list", list);try {req.getRequestDispatcher("show.jsp").forward(req, resp);} catch (ServletException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}} //删除信息private void Delete(HttpServletRequest req, HttpServletResponse resp) {// TODO Auto-generated method stubtry {req.setCharacterEncoding("utf-8");} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();}String name=req.getParameter("name");System.out.println(name);service.delete(name);try {req.getRequestDispatcher("main.jsp").forward(req, resp);} catch (ServletException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}
private void update(HttpServletRequest req, HttpServletResponse resp) {// TODO Auto-generated method stubtry {req.setCharacterEncoding("utf-8");} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();}String name=req.getParameter("name");String sex=req.getParameter("sex");String mianmao=req.getParameter("mianmao");//多个选项，变化为String类型存储String[] list=req.getParameterValues("fuwuleibie");String fuwuleibie="";for(int i=0;i<list.length;i++) {fuwuleibie+=list[i];if(i!=list.length-1) {fuwuleibie+="，";}}Bean use=new Bean(name,sex,"","","",mianmao,fuwuleibie);System.out.println(use.getSex());System.out.println("servlet 修改");service.update(use);try {req.getRequestDispatcher("Servlet?method=show").forward(req, resp);} catch (ServletException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}
private void insert(HttpServletRequest req, HttpServletResponse resp) {// TODO Auto-generated method stubtry {req.setCharacterEncoding("utf-8");} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();}String name=req.getParameter("name");String sex=req.getParameter("sex");String minzu=req.getParameter("minzu");String zhucetime=req.getParameter("zhucetime");String age=req.getParameter("age");String mianmao=req.getParameter("mianmao");String[] list=req.getParameterValues("fuwuleibie");String fuwuleibie="";for(int i=0;i<list.length;i++) {fuwuleibie+=list[i];if(i!=list.length-1) {fuwuleibie+="，";}}System.out.println(fuwuleibie);Bean e=new Bean(name,sex,minzu,zhucetime,age,mianmao,fuwuleibie);if(service.insert(e)) {try {req.setAttribute("message","添加成功");req.getRequestDispatcher("Servlet?method=show").forward(req, resp);} catch (ServletException e1) {// TODO Auto-generated catch blocke1.printStackTrace();} catch (IOException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}}else {try {req.setAttribute("message", "添加重复,请重新输入"); req.getRequestDispatcher("insert.jsp").forward(req, resp);} catch (ServletException e1) {// TODO Auto-generated catch blocke1.printStackTrace();} catch (IOException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}}}
}

posted @ 2019-12-03 17:21 不懂就要问！阅读(2891) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

半暖时光丶

Java web实现综合查询+SQL语句拼接

公告