根据多个条件进行模糊查询
思路:判断前端传来的数据,判断那些条件是填写的,最后在Dao层判断进行sql语句拼接
效果
具体代码实现
前端
<div style="float: right"><h3 align="center" class="form-inline">当前用户:${user}欢迎你!</h3></div>
<div align="center" style="float: left">
<form class="form-inline" action="bookListByType" method="post">
<div class="form-group">
<label>图书分类</label>
<select name="book_type" class="form-control">
<option selected>请选择</option>
<option>小说</option>
<option>文学</option>
<option>传记</option>
<option>艺术</option>
<option>少人</option>
<option>经济</option>
<option>管理</option>
<option>科技</option>
</select>
</div>
<div class="form-group">
<label for="exampleInputEmail2">图书名称</label>
<input type="text" class="form-control" id="exampleInputEmail2" placeholder="请输入图书名称" name="book_name">
</div>
<div class="form-group">
<label>是否借阅</label>
<select name="is_borrow" class="form-control">
<option selected>请选择</option>
<option>已借</option>
<option>未借</option>
</select>
</div>
<input type="submit" class="btn btn-default" value="点击查询"/>
</form>
</div>
<table class="table table-hover">
<tr align="center">
<td>ID</td>
<td>图书编号</td>
<td>图书名称</td>
<td>图书类型</td>
<td>图书作者</td>
<td>出版社</td>
<td>出版日期</td>
<td>是否借阅</td>
<td>创建人</td>
<td>创建日期</td>
<td>最新更新时间</td>
<td>借阅用户ID</td>
<td colspan="2">操作</td>
</tr>
<c:forEach var="item" items="${bookInfos}">
<tr align="center">
<td>${item.book_id}</td>
<td>${item.book_code}</td>
<td>${item.book_name}</td>
<td><c:choose>
<c:when test="${item.book_type==1}">小说</c:when>
<c:when test="${item.book_type==2}">文学</c:when>
<c:when test="${item.book_type==3}">传记</c:when>
<c:when test="${item.book_type==5}">艺术</c:when>
<c:when test="${item.book_type==6}">少儿</c:when>
<c:when test="${item.book_type==7}">经济</c:when>
<c:when test="${item.book_type==8}">管理</c:when>
<c:when test="${item.book_type==8}">科技</c:when>
</c:choose>
</td>
<td>${item.book_author}</td>
<td>${item.publish_press}</td>
<td>${item.publish_date}</td>
<td>
<c:choose>
<c:when test="${item.is_borrow==1}">已借</c:when>
<c:when test="${item.is_borrow==0}">未借</c:when>
</c:choose>
</td>
<td>${item.createdBy}</td>
<td>${item.creation_time}</td>
<td>${item.last_updatetime}</td>
<td>${item.user_id}</td>
<td><a href="${pageContext.request.contextPath}/bookInfoListById?id=${item.book_id}">修改</a></td>
<td><a href="${pageContext.request.contextPath}/deleteBookInfoById?id=${item.book_id}">删除</a></td>
</tr>
</c:forEach>
</table>
servlet
package com.library.servlet;
import com.library.pojo.BookInfo;
import com.library.service.BookInfoService;
import com.library.service.impl.BookInfoServiceImpl;
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 java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@WebServlet("/bookListByType")
public class BookListByType extends HttpServlet {
BookInfoService bookInfoService = new BookInfoServiceImpl();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("utf-8");
String book_name = "未选择";
Map<String, Integer> map = new HashMap<>();
map.put("请选择", 0);
map.put("小说", 1);
map.put("文学", 2);
map.put("传记", 3);
map.put("艺术", 4);
map.put("少儿", 5);
map.put("经济", 6);
map.put("管理", 7);
map.put("科技", 8);
int book_type = map.get(req.getParameter("book_type"));
String s1 = req.getParameter("book_name");
if (!"".equals(s1)) {
book_name = req.getParameter("book_name").trim();
}
Map<String, Integer> map1 = new HashMap<>();
map1.put("请选择", -1);
map1.put("已借", 1);
map1.put("未借", 0);
int is_borrow = map1.get(req.getParameter("is_borrow"));
List<BookInfo> bookInfos = bookInfoService.BookListByType(book_type, book_name, is_borrow);
req.setAttribute("bookInfos", bookInfos);
req.getRequestDispatcher("bookinfolist.jsp").forward(req, resp);
}
}
工具类Util
package com.library.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class Utils {
private static DataSource dataSource;
// 初始化配置
static {
try {
Properties properties = new Properties();
properties.load(Utils.class.getClassLoader().getResourceAsStream("druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
// 释放资源
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 通用查询参数用Object数组存储
public static <T> List<T> executeQuery(String sql, Object[] params, Class<T> clazz) {
List<T> list = new ArrayList<>();
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
Connection connection = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
T t = clazz.newInstance();
Field[] declaredFields = clazz.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
declaredFields[i].setAccessible(true);
declaredFields[i].set(t, resultSet.getObject(declaredFields[i].getName()));
}
list.add(t);
}
} catch (SQLException | InstantiationException | IllegalAccessException e) {
e.printStackTrace();
} finally {
close(resultSet, preparedStatement, connection);
}
return list;
}
// 通用查询参数用ArrayList集合存储
public static <T> List<T> executeListQuery(String sql, ArrayList params, Class<T> clazz) {
List<T> list = new ArrayList<>();
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
Connection connection = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
if (!params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
preparedStatement.setObject(i + 1, params.get(i));
}
}
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
T t = clazz.newInstance();
Field[] declaredFields = clazz.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
declaredFields[i].setAccessible(true);
declaredFields[i].set(t, resultSet.getObject(declaredFields[i].getName()));
}
list.add(t);
}
} catch (SQLException | InstantiationException | IllegalAccessException e) {
e.printStackTrace();
} finally {
close(resultSet, preparedStatement, connection);
}
return list;
}
// 通用增删改
public static int executeUpdate(String sql, Object[] params) {
PreparedStatement preparedStatement = null;
Connection connection = null;
int num = -1;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
num = preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(null, preparedStatement, connection);
}
return num;
}
// 获取数据资源
public static DataSource getDataSource() {
return dataSource;
}
}
service
public List<BookInfo> BookListByType(int book_type, String book_name, int is_borrow) {
return bookInfoDao.BookListByType(book_type, book_name, is_borrow);
}
Dao
public List<BookInfo> BookListByType(int book_type, String book_name, int is_borrow) {
String sql = "select * from book_info where 1=1";
String ss = "%" + book_name + "%";
String type = " and book_type = ?";
String name = " and book_name like ?";
String borrow = " and is_borrow = ?";
StringBuffer stringBuffer = new StringBuffer(sql);
ArrayList params = new ArrayList<>();
if (book_type != 0) {
params.add(book_type);
stringBuffer.append(type);
}
if (!"未选择".equals(book_name)) {
params.add(ss);
stringBuffer.append(name);
}
if (is_borrow != -1) {
params.add(is_borrow);
stringBuffer.append(borrow);
}
return Utils.executeListQuery(stringBuffer.toString(), params, BookInfo.class);
}
pojo
package com.library.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class BookInfo {
private int book_id;
private String book_code;
private String book_name;
private int book_type;
private String book_author;
private String publish_press;
private String publish_date;
private int is_borrow;
private String createdBy;
private String creation_time;
private String last_updatetime;
private int user_id;
public BookInfo(String book_code, String book_name, int book_type, String book_author, String publish_press, String publish_date, int is_borrow, String createdBy, String creation_time, String last_updatetime, int user_id) {
this.book_code = book_code;
this.book_name = book_name;
this.book_type = book_type;
this.book_author = book_author;
this.publish_press = publish_press;
this.publish_date = publish_date;
this.is_borrow = is_borrow;
this.createdBy = createdBy;
this.creation_time = creation_time;
this.last_updatetime = last_updatetime;
this.user_id = user_id;
}
}