JavaWeb-综合案例(用户信息)-学习笔记06【复杂条件查询功能】

￡神魔★判官ぃ 2022-10-30 01:24 243阅读 0赞

>  *  **[Java后端 学习路线 笔记汇总表【黑马程序员】][Java_ _]**
> 
> 1.  [JavaWeb-综合案例(用户信息)-学习笔记01【列表查询】][JavaWeb-_-_01]
> 2.  [JavaWeb-综合案例(用户信息)-学习笔记02【登录功能】][JavaWeb-_-_02]
> 3.  [JavaWeb-综合案例(用户信息)-学习笔记03【添加删除修改功能】][JavaWeb-_-_03]
> 4.  [JavaWeb-综合案例(用户信息)-学习笔记04【删除选中功能】][JavaWeb-_-_04]
> 5.  [JavaWeb-综合案例(用户信息)-学习笔记05【分页查询功能】][JavaWeb-_-_05]
> 6.  [JavaWeb-综合案例(用户信息)-学习笔记06【复杂条件查询功能】][JavaWeb-_-_06]

**目录**

第5节 复杂条件查询功能

复杂条件查询功能\_分析

复杂条件查询功能\_代码实现1

复杂条件查询功能\_代码实现2

UserDaoImpl.java

UserServiceImpl.java

FindUserByPageServlet.java

--------------------

# 第5节 复杂条件查询功能 #

## 复杂条件查询功能\_分析 ##

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70][]
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 1][]
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 2][]

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 3][] 复杂条件分页查询

## 复杂条件查询功能\_代码实现1 ##

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 4][]
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 5][]

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 6][]

## 复杂条件查询功能\_代码实现2 ##

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 7][]

### 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);
        }
    }

> ![2021021620595683.png][]
> 
> 加油~

[Java_ _]: https://blog.csdn.net/weixin_44949135/article/details/113773297
[JavaWeb-_-_01]: https://blog.csdn.net/weixin_44949135/article/details/113823440
[JavaWeb-_-_02]: https://blog.csdn.net/weixin_44949135/article/details/113822709
[JavaWeb-_-_03]: https://blog.csdn.net/weixin_44949135/article/details/113824663
[JavaWeb-_-_04]: https://blog.csdn.net/weixin_44949135/article/details/113826067
[JavaWeb-_-_05]: https://blog.csdn.net/weixin_44949135/article/details/113826657
[JavaWeb-_-_06]: https://blog.csdn.net/weixin_44949135/article/details/113827460
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70]: /images/20221024/50a870c59b164ebda2214898e6dba8b3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 1]: /images/20221024/d6005845ce024f5da3cfd088507d502f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 2]: /images/20221024/1770ae73d8b64b4190b55cb2cfe44f8e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 3]: /images/20221024/edbe7fdc117c4fefb60b9e6e819ae7c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 4]: /images/20221024/485ae188fb45473e887ae3605bcc2640.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 5]: /images/20221024/4983cec81bee4ad493236a65dfffa169.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 6]: /images/20221024/1d25084ea84a48adb91a537408e6f4f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk0OTEzNQ_size_16_color_FFFFFF_t_70 7]: /images/20221024/7c78a5a25a734db99ced6e8f1fc5985a.png
[2021021620595683.png]: /images/20221024/9d94fb041e1a46f1bb11ece1796557ba.png