复杂条件查询功能-分析
复杂条件查询功能-代码实现-每页数据条件查询
package com.example.day17_case.service.impl; import com.example.day17_case.dao.UserDao; import com.example.day17_case.dao.impl.UserDaoImpl; import com.example.day17_case.domain.PageBean; import com.example.day17_case.domain.User; import com.example.day17_case.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; } }
package com.example.day17_case.dao.impl; import com.example.day17_case.dao.UserDao; import com.example.day17_case.domain.User; import com.example.day17_case.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()); } }
标签:功能,String,复杂,dao,查询,user,sql,import,public From: https://www.cnblogs.com/yuzong/p/17072787.html