Java web实现综合查询+SQL语句拼接
首先展示图形界：
界面比较简单，但麻雀虽小五脏俱全。因为数据库只有六种数据类型，所以最多添加六个查询框。
测试以下问题： 删除方式是从上往下开始的，如果删除之后会有问题。（后续改进ing）
若干个并且，或，不含在一起执行如果顺序不对会报错（sql语句拼接执行有问题）。
如果还有问题，敬请指正。
sql语句的拼接推荐（如果有更好的请忽略，可以先看一看拼接的方法再实现方法，不要一上来就抱住一团慢慢啃，例如我。。。嘿嘿）：
http://codingdict.com/article/6952
废话不多说，上代码：
Bean 层（基础层）：
public class Bean {
private String name;
private String sex;
private String minzu;
private String zhucetime;
private String age;
private String mianmao;
private String fuwuleibie;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getMinzu() {
return minzu;
}
public void setMinzu(String minzu) {
this.minzu = minzu;
}
public String getZhucetime() {
return zhucetime;
}
public void setZhucetime(String zhucetime) {
this.zhucetime = zhucetime;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getMianmao() {
return mianmao;
}
public void setMianmao(String mianmao) {
this.mianmao = mianmao;
}
public String getFuwuleibie() {
return fuwuleibie;
}
public void setFuwuleibie(String fuwuleibie) {
this.fuwuleibie = fuwuleibie;
}
public Bean(String name, String sex, String minzu, String zhucetime, String age, String mianmao,
String fuwuleibie) {
super();
this.name = name;
this.sex = sex;
this.minzu = minzu;
this.zhucetime = zhucetime;
this.age = age;
this.mianmao = mianmao;
this.fuwuleibie = fuwuleibie;
}
public Bean() {
// TODO Auto-generated constructor stub
}
DBUtil层 emm。。。关闭的方法有点多，但是使用的就只有一个，懒得删除了 ：
package com.DBUtil; import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; public class DBUtil {
public static String db_url="jdbc:mysql://localhost:3306/db_database01?serverTimezone=UTC&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false";
public static String db_user="root";
public static String db_password="101032";
public static Connection getConn() {
Connection conn=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
try {
conn=DriverManager.getConnection(db_url, db_user, db_password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("驱动链接加载成功！");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn; } public static void close(Statement state,Connection conn) {
if(state!=null) {
try {
((Connection) state).close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} }
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} public static void close(PreparedStatement state,Connection conn) {
if(state!=null) {
try {
((Connection) state).close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} }
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} public static void close(ResultSet rs,Statement state,Connection conn) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(state!=null) {
try {
((Connection) state).close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} @SuppressWarnings("static-access")
public static void main(String[] args) throws SQLException {
DBUtil dbu=new DBUtil();
dbu.getConn();
} public static void close(java.sql.Statement state, Connection conn) {
// TODO Auto-generated method stub
if(state!=null)
{
try {
state.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Dao层（由于是之前的程序基础上改写的，有点乱，就该查询来说无用的程序比较多）：
在多条件查询的方法当中sql语句不是在Dao层生成的，而是在Servlet 层拼接好之后传递过来的。
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List; import com.Bean.Bean;
import com.DBUtil.DBUtil; public class Dao { //遍历
public static List<Bean> show() {
List<Bean>list =new ArrayList<>();
Connection conn=DBUtil.getConn();
String sql="select * from tb_qingnian";
Statement pstmt = null;
try {
pstmt = conn.createStatement();
ResultSet rs=pstmt.executeQuery(sql);
Bean use=null;
while(rs.next()) {
String name=rs.getString("name");
String sex=rs.getString("sex");
String minzu=rs.getString("minzu");
String zhucetime=rs.getString("zhucetime");
String age=rs.getString("age");
String mianmao=rs.getString("mianmao");
String fuwuleibie=rs.getString("fuwuleibie");
use=new Bean(name,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
list.add(use);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtil.close(pstmt, conn);
} return list;
} //添加
public static boolean insert(Bean e) {
System.out.println("添加中");
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()+"')";
Connection conn=DBUtil.getConn();
Statement state=null; try {
state= conn.createStatement();
state.executeUpdate(sql);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
finally {
DBUtil.close(state, conn);
}
return false; } //修改
public static boolean update(Bean e) {
System.out.println("修改中");
String sql="update tb_qingnian set sex='"+e.getSex()+"',mianmao='"+e.getMianmao()+"',fuwuleibie='"+e.getFuwuleibie()+"' where name='"+e.getName()+"' ";
Connection conn=DBUtil.getConn();
Statement pstm=null;
try {
pstm=conn.createStatement();
pstm.executeUpdate(sql);
} catch (SQLException e1) {
// TODO Auto-generated catch block e1.printStackTrace();
}finally{
DBUtil.close(pstm, conn);
}
return false; } //删除 按照姓名
public static boolean delete(String name) {
String sql="delete from tb_qingnian where name='"+name+"'" ;
Connection conn=DBUtil.getConn();
Statement pstm=null;
try {
pstm=conn.createStatement();
pstm.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtil.close(pstm, conn);
} return false;
} //按姓名查询 可模糊查询
public static List<Bean> search(String info) {
List<Bean>list =new ArrayList<>();
Connection conn=DBUtil.getConn();
String sql="select * from tb_qingnian where name like'%"+info+"%'";
Statement pstmt = null;
try {
pstmt = conn.createStatement();
ResultSet rs=pstmt.executeQuery(sql);
Bean use=null;
while(rs.next()) {
String name1=rs.getString("name");
String sex=rs.getString("sex");
String minzu=rs.getString("minzu");
String zhucetime=rs.getString("zhucetime");
String age=rs.getString("age");
String mianmao=rs.getString("mianmao");
String fuwuleibie=rs.getString("fuwuleibie");
use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
list.add(use);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtil.close(pstmt, conn);
} return list; } //多条件综合查询 代码执行 使用list进行遍历操作
public static List<Bean> searchs(String sql) {
List<Bean>list =new ArrayList<>();
Connection conn=DBUtil.getConn();
//String sql="select * from tb_qingnian where name like'%"+info+"%'";
Statement pstmt = null;
try {
pstmt = conn.createStatement();
ResultSet rs=pstmt.executeQuery(sql);
Bean use=null;
while(rs.next()) {
String name1=rs.getString("name");
String sex=rs.getString("sex");
String minzu=rs.getString("minzu");
String zhucetime=rs.getString("zhucetime");
String age=rs.getString("age");
String mianmao=rs.getString("mianmao");
String fuwuleibie=rs.getString("fuwuleibie");
use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
list.add(use);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtil.close(pstmt, conn);
} return list; } //性别
public static List<Bean> search1(String info) {
List<Bean>list =new ArrayList<>();
Connection conn=DBUtil.getConn();
String sql="select * from tb_qingnian where sex like'%"+info+"%'";
Statement pstmt = null;
try {
pstmt = conn.createStatement();
ResultSet rs=pstmt.executeQuery(sql);
Bean use=null;
while(rs.next()) {
String name1=rs.getString("name");
String sex=rs.getString("sex");
String minzu=rs.getString("minzu");
String zhucetime=rs.getString("zhucetime");
String age=rs.getString("age");
String mianmao=rs.getString("mianmao");
String fuwuleibie=rs.getString("fuwuleibie");
use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
list.add(use);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtil.close(pstmt, conn);
} return list;
}
//民族
public static List<Bean> search2(String info) {
List<Bean>list =new ArrayList<>();
Connection conn=DBUtil.getConn();
String sql="select * from tb_qingnian where minzu like'%"+info+"%'";
Statement pstmt = null;
try {
pstmt = conn.createStatement();
ResultSet rs=pstmt.executeQuery(sql);
Bean use=null;
while(rs.next()) {
String name1=rs.getString("name");
String sex=rs.getString("sex");
String minzu=rs.getString("minzu");
String zhucetime=rs.getString("zhucetime");
String age=rs.getString("age");
String mianmao=rs.getString("mianmao");
String fuwuleibie=rs.getString("fuwuleibie");
use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
list.add(use);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtil.close(pstmt, conn);
} return list;
}
//政治面貌
public static List<Bean> search3(String info) {
List<Bean>list =new ArrayList<>();
Connection conn=DBUtil.getConn();
String sql="select * from tb_qingnian where mianmao like'%"+info+"%'";
Statement pstmt = null;
try {
pstmt = conn.createStatement();
ResultSet rs=pstmt.executeQuery(sql);
Bean use=null;
while(rs.next()) {
String name1=rs.getString("name");
String sex=rs.getString("sex");
String minzu=rs.getString("minzu");
String zhucetime=rs.getString("zhucetime");
String age=rs.getString("age");
String mianmao=rs.getString("mianmao");
String fuwuleibie=rs.getString("fuwuleibie");
use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
list.add(use);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtil.close(pstmt, conn);
} return list;
}
//服务类别
public static List<Bean> search4(String info) {
List<Bean>list =new ArrayList<>();
Connection conn=DBUtil.getConn();
String sql="select * from tb_qingnian where fuwuleibie like'%"+info+"%'";
Statement pstmt = null;
try {
pstmt = conn.createStatement();
ResultSet rs=pstmt.executeQuery(sql);
Bean use=null;
while(rs.next()) {
String name1=rs.getString("name");
String sex=rs.getString("sex");
String minzu=rs.getString("minzu");
String zhucetime=rs.getString("zhucetime");
String age=rs.getString("age");
String mianmao=rs.getString("mianmao");
String fuwuleibie=rs.getString("fuwuleibie");
use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
list.add(use);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtil.close(pstmt, conn);
} return list;
}
//注册时间
public static List<Bean> search5(String info) {
List<Bean>list =new ArrayList<>();
Connection conn=DBUtil.getConn();
String sql="select * from tb_qingnian where zhucetime like'%"+info+"%'";
Statement pstmt = null;
try {
pstmt = conn.createStatement();
ResultSet rs=pstmt.executeQuery(sql);
Bean use=null;
while(rs.next()) {
String name1=rs.getString("name");
String sex=rs.getString("sex");
String minzu=rs.getString("minzu");
String zhucetime=rs.getString("zhucetime");
String age=rs.getString("age");
String mianmao=rs.getString("mianmao");
String fuwuleibie=rs.getString("fuwuleibie");
use=new Bean(name1,sex,minzu,zhucetime,age,mianmao,fuwuleibie);
list.add(use);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
DBUtil.close(pstmt, conn);
} return list;
} public static void main(String[] args) {
Bean e=new Bean("李","男","汉","2015-02-10","24","党员","志愿者");
insert(e);
List<Bean> list =show();
for(int i=0;i<list.size();i++) {
Bean s=list.get(i);
System.out.println(s.getName());
}
String a="李四";
//delete(a);
} }
Servlet 层 通过jsp传递过来的参数来拼接sql语句 然后执行sql语句（前面的查询通过了Service 层来传递，就该方法直接调用了Dao层的方法），查询信息：
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 stub
try {
req.setCharacterEncoding("utf-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.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 非 not
String 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);
//
if(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);
//
if(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+"'";
}
}
}
//
if(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+"'";
}
}
}
//
if(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+"'";
}
}
} //
if(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 block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else {
try {
req.getRequestDispatcher("error.jsp").forward(req, resp);
} catch (ServletException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} // 查询信息
private void search(HttpServletRequest req, HttpServletResponse resp) {
// TODO Auto-generated method stub
try {
req.setCharacterEncoding("utf-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.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 block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } //遍历数据库
private void show(HttpServletRequest req, HttpServletResponse resp) {
// TODO Auto-generated method stub
try {
req.setCharacterEncoding("utf-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.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 block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} }
//删除信息
private void Delete(HttpServletRequest req, HttpServletResponse resp) {
// TODO Auto-generated method stub
try {
req.setCharacterEncoding("utf-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.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 block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } private void update(HttpServletRequest req, HttpServletResponse resp) {
// TODO Auto-generated method stub
try {
req.setCharacterEncoding("utf-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.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 block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } private void insert(HttpServletRequest req, HttpServletResponse resp) {
// TODO Auto-generated method stub
try {
req.setCharacterEncoding("utf-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.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 block
e1.printStackTrace();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}else {
try { req.setAttribute("message", "添加重复,请重新输入");
req.getRequestDispatcher("insert.jsp").forward(req, resp);
} catch (ServletException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
} }
jsp界面：
对于 应且 或者 不含的三个条件的选择框的 value 值 直接设置为了 “and” “or” “and not ”，在拼接时可以直接获取值进行拼接
查找界面:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body> <div align="center">
<!-- +: + - : - -->
<button onclick="insert_row()" type="button" >+</button>
<button onclick="deleteRow(this)" type="button" >-</button>
<form action="Servlet?method=searchs" method="post">
<table name="tbl" id="tbl">
<tr>
<td>
<select id="type1" name="type1">
<option value="name">姓名</option>
<option value="minzu">民族</option>
<option value="sex">性别</option>
<option value="mianmu">政治面目</option>
<option value="fuwuleibie">服务类别</option>
<option value="zhucetime">注册时间</option>
</select>
<input type="text" id="SearchBox1" name="SearchBox1">
<select id="method1" name="method1">
<option value="jingque">精确</option>
<option value="mohu">模糊</option>
</select> <!-- <select id="con" name="con"> -->
<!-- <option value="and">并且</option> -->
<!-- <option value="or">或者</option> -->
<!-- <option value="no">不含</option> -->
<!-- </select> --> </td>
</tr>
</table> <div id="SearchButton">
<button id="Button" type="submit" >查询</button>
</div>
<div>
<a href="Servlet?method=show">信息浏览</a>
</div>
</form>
</div>
</body>
<script type="text/javascript">
var i=1;
var first=1;
function insert_row(){
if(i<6){
i++;
R=tbl.insertRow(); //insertRow() 方法用于在表格中的指定位置插入一个新行。
C=R.insertCell(); //insertCell() 方法用于在 HTML 表的一行的指定位置插入一个空的 <td> 元素。
C.innerHTML =
"<select id='con"+i+"' name='con"+i+"'>"+
"<option value='and'>并且</option>"+
"<option value='or'>或者</option>"+
"<option value=' and not'>不含</option>"+
"</select>"+
"<select id='type"+i+"' name='type"+i+"'>"+
"<option value='name' >姓名</option>"+
"<option value='sex' >性别</option>"+
"<option value='age'>年龄</option>"+
"<option value='mianmao'>政治面目</option>"+
"<option value='zhucetime'>注册时间</option>"+
"<option value='fuwuleibie'>服务类别</option>"+
"</select>"+" <input id='SearchBox"+i+"' name='SearchBox"+i+"' />"+
"<select id='method"+i+"' name='method"+i+"'>"+
"<option value='jingque'>精确</option>"+
"<option value='mohu'>模糊</option>"+
"</select>"
}else{
alert("查询条件冲突！");
} } function deleteRow(obj){
if(i>1){
alert("确定删除一行吗？");
tbl.deleteRow(obj.parentElement.parentElement.rowIndex); i--;
}
} </script> </body>
</html>
显示界面 (需要用到list 遍历，需要 jstl.jar)：
可能会有收费，多找找总会有免费的。（万能的度娘在上见证，总会找到的。 。嘿嘿）
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>遍历志愿者信息</title>
</head>
<body>
<div align="center">
<h1>志愿者信息浏览：</h1>
<a href="indexfind.jsp">返回</a>
<form action="Servlet?method=show" method="post" onsubmit="return check()">
<table>
<tr>
<td>姓名</td>
<td>性别</td>
<td>民族</td>
<td>注册时间</td>
<td>年龄</td>
<td>面貌</td>
<td>服务类别</td>
<td align="center" colspan="2">操作</td>
</tr>
<c:forEach items="${list}" var="item">
<tr>
<td>${item.name}</td>
<td>${item.sex}</td>
<td>${item.minzu}</td>
<td>${item.zhucetime}</td>
<td>${item.age}</td>
<td>${item.mianmao}</td>
<td>${item.fuwuleibie}</td>
<td><a href="update.jsp?name=${item.name}&sex=${item.sex}&mianmao=${item.mianmao}&fuwuleibie=${item.fuwuleibie}">修改</a></td>
<!-- <td><a href="javaScript:if(confirm('确定删除吗?')){location.href='Servlet?method=delete'}">删除</a></td> -->
<%-- <td><a href="javaScript:if(confirm('确定删除吗?')){location.href='delete.jsp'?name=${item.name}}">删除</a></td> --%>
<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>
</tr>
</c:forEach>
</table>
</form>
</div>
如果没有查找到信息会跳转到一个提示界面。
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>错误信息展示</title>
</head>
<body>
<div align="center">
<a href="indexfind.jsp">返回</a>
<div>
<h3>未找到查询信息</h3>
</div>
</div>
</body>
</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();}}}
}
最新文章
- R自动数据收集第二章HTML笔记1(主要关于handler处理器函数和帮助文档所有示例)
- PHP变量入门教程（3）global 关键字
- QT 对话框二
- freebsd镜像作用和vmware服务开启
- No.012 Integer to Roman
- 首次发布App，In-App Purchase 无法submit for review 问题的解决方案
- Unity3D 集合插件目录
- SQL之存储过程,仿数组
- android混淆打包配置（忽略第三方jar)
- django-装饰器实现PV统计
- 《Language Implementation Patterns》之 强类型规则
- Python处理正则表达式超时的办法
- 简单的纯js三级联动
- 解决js的 Math取正弦值 余弦值不准确的问题
- Eclipse 手动增加linker library
- jenkins多个slave遇到的坑
- Shell Trap信号管理
- android studio AIDL 编译时 错误：找不到符号
- Web 前端 注意知识点
- vue axios的使用