如下面图片所示:进行模糊查询
前端的页面代码: 注意:请选择那里的value="" 一定要写
<body>
<form action="SearchStuServlet" method="post">
<table border="1px" width="700" align="center" >
<tr >
<td colspan="8">
按姓名查询:<input type="text" name = "sname">
按性别查询:<select name ="sex">
<option value ="">--请选择--</option>
<option value ="男">男</option>
<option value ="女">女</option>
</select>
<input type="submit" value="查询">
<a href="add.jsp">添加</a>
</td>
</tr>
…………
</table>
</form>
</body>
SearchStuServlet的代码:
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//编码格式
request.setCharacterEncoding("utf-8");
//获取页面填写的信息
String sname = request.getParameter("sname");
String sex = request.getParameter("sex");
System.out.println(sex+""+sname);
//获取数据库中的数据
StuService service = new StuServiceImpl();
List<Student> stu = service.searchStu(sname, sex);
//将获取的数据存储到session中
HttpSession session = request.getSession();
session.setAttribute("list", stu);
//跳转页面
request.getRequestDispatcher("list.jsp").forward(request, response);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
这里要分析一下:
如果只有姓名 ,select * from stu where sname like ? ;
如果只有性别 , select * from stu where gender = ?
如果两个都有 select * from stu where sname like ? and gender=?
如果两个都没有就查询所有。
Dao层代码:注意在这里只能用list集合来接收这个参数,因为参数的个数不是固定的
@Override
public List<Student> searchStudent(String sname, String sgender) throws SQLException {
System.out.println("现在要执行模糊查询了,收到的name ="+sname + "==genser=="+sgender);
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
//String sql = "select * from stu where sname=? or sgender=?";
/*
* 这里要分析一下:
* 如果只有姓名 ,select * from stu where sname like ? ;
* 如果只有性别 , select * from stu where gender = ?
* 如果两个都有 select * from stu where sname like ? and gender=?
* 如果两个都没有就查询所有。
*/
String sql = "select * from stu where 1=1 ";
List<String> list = new ArrayList<String> ();
//判断有没有姓名, 如果有,就组拼到sql语句里面
if(sname!=null && sname.length()!=0){
sql = sql +" and sname like ?";
list.add("%"+sname+"%");
}
//判断有没有性别,有的话,就组拼到sql语句里面。
if(sex!=null && sex.length()!=0){
sql = sql +" and sex = ?";
list.add(sex);
}
return queryRunner.query(sql, new BeanListHandler<Student>(Student.class),list.toArray());
}
说明:这里的runner.query()的第三个参数是可变参数,可以接收0个参数,即查询全部,可以接收一个参数,即查询性别或者姓名,可以接收两个参数,即性别和姓名连起来一起进行模糊查询 可变参数用数组接收,所以list转为数组
如果Dao层不用runner.query() 的可变的参数。直接用if else也是可以写出来模糊查询的 ,思路都是一样的
代码如下:只不过比较麻烦
/**
* 模糊查询
*/
public List<Student> searchStu(String sname, String sex) throws SQLException {
System.out.println("sanme="+sname+"sex="+sex);
ComboPooledDataSource dataSource = new ComboPooledDataSource();
QueryRunner queryRunner = new QueryRunner(dataSource);
String sql = "select * from ddd where 1=1";
//如果两个都有 select * from stu where sname like ? and gender=?
if((sname!=null && sname.length()!=0)&&(sex!=null && sex.length()!=0)){
sql = sql +" and sname like ? and sex=?";
sname = "%"+sname+"%";
return queryRunner.query(sql, new BeanListHandler<Student>(Student.class),sname,sex);
}else if((sname!=null && sname.length()!=0)||(sex!=null && sex.length()!=0)){
//如果只有姓名 ,select * from stu where sname like ? ;
if(sname!=null && sname.length()!=0){
sql = sql +" and sname like ? ";
sname = "%"+sname+"%";
return queryRunner.query(sql, new BeanListHandler<Student>(Student.class),sname);
}
// 如果只有性别 , select * from stu where gender = ?
if(sex!=null && sex.length()!=0){
sql = sql +" and sex=?";
return queryRunner.query(sql, new BeanListHandler<Student>(Student.class),sex);
}
}else{
//如果两个都没有就查询所有。
return queryRunner.query(sql, new BeanListHandler<Student>(Student.class));
}
return null;
}