目录
第5节 复杂条件查询功能
复杂条件查询功能_分析
复杂条件分页查询
复杂条件查询功能_代码实现1
复杂条件查询功能_代码实现2
UserDaoImpl.java
package cn.itcast.dao.impl;
import cn.itcast.dao.UserDao;
import cn.itcast.domain.User;
import cn.itcast.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class UserDaoImpl implements UserDao {
private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
@Override
public List<User> findAll() {
//使用JDBC操作数据库...
//1.定义sql
String sql = "select * from user";
List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
return users;
}
@Override
public User findUserByUsernameAndPassword(String username, String password) {
try {
String sql = "select * from user where username = ? and password = ?";
User user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), username, password);
return user;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
@Override
public void add(User user) {
//1.定义sql
String sql = "insert into user values(null,?,?,?,?,?,?,null,null)";
//2.执行sql
template.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail());
}
@Override
public void delete(int id) {
//1.定义sql
String sql = "delete from user where id = ?";
//2.执行sql
template.update(sql, id);
}
@Override
public User findById(int id) {
String sql = "select * from user where id = ?";
return template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
}
@Override
public void update(User user) {
String sql = "update user set name = ?,gender = ? ,age = ? , address = ? , qq = ?, email = ? where id = ?";
template.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail(), user.getId());
}
@Override
public int findTotalCount(Map<String, String[]> condition) {
//1.定义模板初始化sql
String sql = "select count(*) from user where 1 = 1 ";
StringBuilder sb = new StringBuilder(sql);
//2.遍历map
Set<String> keySet = condition.keySet();
//定义参数的集合
List<Object> params = new ArrayList<Object>();
for (String key : keySet) {
//排除分页条件参数
if ("currentPage".equals(key) || "rows".equals(key)) {
continue;
}
//获取value
String value = condition.get(key)[0];
//判断value是否有值
if (value != null && !"".equals(value)) {
//有值
sb.append(" and " + key + " like ? ");
params.add("%" + value + "%");//？条件的值
}
}
System.out.println(sb.toString());
System.out.println(params);
return template.queryForObject(sb.toString(), Integer.class, params.toArray());
}
@Override
public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
String sql = "select * from user where 1 = 1 ";
StringBuilder sb = new StringBuilder(sql);
//2.遍历map
Set<String> keySet = condition.keySet();
//定义参数的集合
List<Object> params = new ArrayList<Object>();
for (String key : keySet) {
//排除分页条件参数
if ("currentPage".equals(key) || "rows".equals(key)) {
continue;
}
//获取value
String value = condition.get(key)[0];
//判断value是否有值
if (value != null && !"".equals(value)) {
//有值
sb.append(" and " + key + " like ? ");
params.add("%" + value + "%");//？条件的值
}
}
//添加分页查询
sb.append(" limit ?,? ");
//添加分页查询参数值
params.add(start);
params.add(rows);
sql = sb.toString();
System.out.println(sql);
System.out.println(params);
return template.query(sql, new BeanPropertyRowMapper<User>(User.class), params.toArray());
}
}UserServiceImpl.java
package cn.itcast.service.impl;
import cn.itcast.dao.UserDao;
import cn.itcast.dao.impl.UserDaoImpl;
import cn.itcast.domain.PageBean;
import cn.itcast.domain.User;
import cn.itcast.service.UserService;
import java.util.List;
import java.util.Map;
public class UserServiceImpl implements UserService {
private UserDao dao = new UserDaoImpl();
@Override
public List<User> findAll() {
//调用Dao完成查询
return dao.findAll();
}
@Override
public User login(User user) {
return dao.findUserByUsernameAndPassword(user.getUsername(), user.getPassword());
}
@Override
public void addUser(User user) {
dao.add(user);
}
@Override
public void deleteUser(String id) {
dao.delete(Integer.parseInt(id));
}
@Override
public User findUserById(String id) {
return dao.findById(Integer.parseInt(id));
}
@Override
public void updateUser(User user) {
dao.update(user);
}
@Override
public void delSelectedUser(String[] ids) {
if (ids != null && ids.length > 0) {
//1.遍历数组
for (String id : ids) {
//2.调用dao删除
dao.delete(Integer.parseInt(id));
}
}
}
@Override
public PageBean<User> findUserByPage(String _currentPage, String _rows, Map<String, String[]> condition) {
int currentPage = Integer.parseInt(_currentPage);
int rows = Integer.parseInt(_rows);
if (currentPage <= 0) {
currentPage = 1;
}
//1.创建空的PageBean对象
PageBean<User> pb = new PageBean<User>();
//2.设置参数
pb.setCurrentPage(currentPage);
pb.setRows(rows);
//3.调用dao查询总记录数
int totalCount = dao.findTotalCount(condition);
pb.setTotalCount(totalCount);
//4.调用dao查询List集合
//计算开始的记录索引
int start = (currentPage - 1) * rows;
List<User> list = dao.findByPage(start, rows, condition);
pb.setList(list);
//5.计算总页码
int totalPage = (totalCount % rows) == 0 ? totalCount / rows : (totalCount / rows) + 1;
pb.setTotalPage(totalPage);
return pb;
}
}FindUserByPageServlet.java
package cn.itcast.web.servlet;
import cn.itcast.domain.PageBean;
import cn.itcast.domain.User;
import cn.itcast.service.UserService;
import cn.itcast.service.impl.UserServiceImpl;
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.Map;
@WebServlet("/findUserByPageServlet")
public class FindUserByPageServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
//1.获取参数
String currentPage = request.getParameter("currentPage");//当前页码
String rows = request.getParameter("rows");//每页显示条数
if (currentPage == null || "".equals(currentPage)) {
currentPage = "1";
}
if (rows == null || "".equals(rows)) {
rows = "5";
}
//获取条件查询参数
Map<String, String[]> condition = request.getParameterMap();
//2.调用service查询
UserService service = new UserServiceImpl();
PageBean<User> pb = service.findUserByPage(currentPage, rows, condition);
System.out.println(pb);
//3.将PageBean存入request
request.setAttribute("pb", pb);
request.setAttribute("condition", condition);//将查询条件存入request
//4.转发到list.jsp
request.getRequestDispatcher("/list.jsp").forward(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
}
加油~
扫一扫
1321
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
