Case: user information list display

1, Requirement design analysis


     1. Requirement: adding, deleting, modifying and querying user information
     2. Design:
           1. Technology selection: servlet + jsp + MySQL + jdbc template + duird + BeanUtils + Tomcat

Note: Servlet is good at process control and transaction processing, so it is used to control the addition, deletion, modification and query of user data on the server.
JSP is good at writing dynamic pages, so we use it to write some dynamic buttons and display forms in the page.
MySQL database is used to store user data information.
JDBC template is an object provided for us in spring architecture, which is used to build a three-tier architecture of the case (interface layer, business logic layer and data access layer).
Durid is a database connection pool of Alibaba. We can use durid configuration file to connect the database with the software we use for coding.
BeanUtils is also a tool class in spring. We can use it to simplify and encapsulate data.
         

           2. Database design:
                 create database day17; -- create database
                 use day17; -- use database
create table user(- - create table
                        id   int  primary  key  auto_ Note: the element type of the database is added and modified according to the user's information type.
                        name  varchar(20)  no null,
                        gender  varchar(5),
                        age  int,
                        address  varchar(32),
                        qq  varchar(20),
                        email  varchar(50)
                 );

 
    3. development:
          1. Environment construction
                1. Create database environment
                2. Create the project and import the required jar package

 

Note: according to the analysis, we can create the project package and class first.

Under dao package, there are UserDao interface and its implementation class UserDaoImpl, which are used to encapsulate and implement the operation code for the database.

package dao;

import domain.User;

import java.util.List;
import java.util.Map;

/*
User operated DAO
 */
public interface UserDao {
    public List<User> findAll();

    User findUserByUsernameAndPassword(String username, String password);

    void add(User user);

    void delete(int i);

    User findById(int parseInt);

    void update(User user);
    //Total records queried
    int findTotalCount(Map<String, String[]> condition);
    //Paging query of records on each page
    List<User> findByPage(int start, int rows, Map<String, String[]> condition);
}
package dao.impl;
import dao.UserDao;
import domain.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import util.JDBCUtils;

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() {
        //Operating database with JDBC
        //1. Define sql
        String sql="select * from user";
        List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return users;
    }

    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. Define sql
        String sql="insert into user values(null,?,?,?,?,?,?,null,null)";
        //2. Execute sql
        template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail());
    }

    @Override
    public void delete(int id) {
        //1. Define sql
        String sql="delete from user where id = ? ";
        //2. Execute 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. Define template initialization sql
        String sql="select count(*) from user where 1 = 1 ";
        StringBuilder sb = new StringBuilder(sql);
        //2. Traverse map
        Set<String> keySet = condition.keySet();
        //Defines a collection of parameters
        List<Object> params = new ArrayList<>();
        for (String key:keySet) {
            //Exclude paging condition parameters
            if("currentPage".equals(key)||"rows".equals(key)){
                continue;
            }
            //Get value
            String value = condition.get(key)[0];
            //Judge whether value has value
            if(value !=null&& !"".equals(value)){
                //Valuable
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//? Value of condition
            }
        }

        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. Traverse map
        Set<String> keySet = condition.keySet();
        //Defines a collection of parameters
        List<Object> params = new ArrayList<>();
        for (String key:keySet) {
            //Exclude paging condition parameters
            if("currentPage".equals(key)||"rows".equals(key)){
                continue;
            }
            //Get value
            String value = condition.get(key)[0];
            //Judge whether value has value
            if(value !=null&& !"".equals(value)){
                //Valuable
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//? Value of condition
            }
        }

        //Add paged query
        sb.append("limit ?,?");
        //Add paging query parameter value
        params.add(start);
        params.add(rows);

        sql=sb.toString();
        return template.query(sql,new BeanPropertyRowMapper<User>(User.class),params.toArray());
    }
}

The User class in the domain is used to declare the User's properties and obtain modification methods, while the PageBean class is used to declare the total number of pages, current number of pages and other properties in the paging display data function.

package domain;
public class User {
    private int id;
    private String name;
    private String gender;
    private int age;
    private String address;
    private String qq;
    private String email;

    private String username;
    private String password;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getQq() {
        return qq;
    }

    public void setQq(String qq) {
        this.qq = qq;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", gender='" + gender + '\'' +
                ", age=" + age +
                ", address='" + address + '\'' +
                ", qq='" + qq + '\'' +
                ", email='" + email + '\'' +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}
package domain;
import java.util.List;
/*
Paging object
 */
public class PageBean<T> {
    private int totalCount;//Total records
    private int totalPage;//Total page number
    private List<T> list;//Data per page
    private int currentPage;//Current page number
    private int rows;//Number of records displayed per page

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        this.rows = rows;
    }

    @Override
    public String toString() {
        return "PageBean{" +
                "totalCount=" + totalCount +
                ", totalPage=" + totalPage +
                ", list=" + list +
                ", currentPage=" + currentPage +
                ", rows=" + rows +
                '}';
    }
}

The interface and implementation class in service are used to encapsulate the methods of adding, deleting, checking and modifying users, and the implementation class methods under dao package are connected to achieve the effect.

package service;

import domain.PageBean;
import domain.User;

import java.util.List;
import java.util.Map;

/*
User managed service interface
 */
public interface UserService {
    //Query all user information
    public List<User> findAll();
    //Login method
    User login(User user);
    //Save User
    void addUser(User user);
    //Delete user by id
    void deleteUser(String id);
    //Query by id
    User findUserById(String id);
    //Modify user information
    void updateUser(User user);
    //Delete selected user
    void delSelectedUser(String[] ids);
    //Paging condition query
    PageBean<User> findUserByPage(String currentPage, String rows, Map<String, String[]> condition);
}
package service.impl;
import dao.UserDao;
import dao.impl.UserDaoImpl;
import domain.PageBean;
import domain.User;
import 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(){
        //Call Dao to complete the query
        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. Traverse the array
            for (String id :ids) {
                //2. Call dao to delete
                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. Create an empty PageBean object
        PageBean<User> pb=new PageBean<>();
        //2. Set parameters
        pb.setCurrentPage(currentPage);
        pb.setRows(rows);
        //3. Call dao to query the total records
        int totalCount=dao.findTotalCount(condition);
        pb.setTotalCount(totalCount);
        //4. Call dao to query the List set
        //Index of records at the beginning of calculation
        int start =(currentPage-1)*rows;
        List<User> list=dao.findByPage(start,rows,condition);
        pb.setList(list);
        //5. Calculate the total page number
        int totalPage=(totalCount % rows) ==0 ? (totalCount/rows):(totalCount/rows+1);
        pb.setTotalPage(totalPage);
        return pb;
    }
}


JDBC utils is used to connect to the database.
         web. Various classes of servlet package are used for the server to obtain parameters from the client and complete relevant responses.

These jar packages are usually downloaded from the Internet through their own needs and copied to the WEB-INF/lib of the corresponding module to simplify the code and use ready-made methods.

  

Note: each function has an html page and a jsp page. html is used to start directly to adjust the beauty of the page. jsp needs to be connected with the servlet server-side class after the server is started, and the back-end operation will be carried out through some buttons to display dynamic data. add and update are the function pages for adding and modifying, index is the main page, login is the login page, and list is the user list data display page.

2. Comprehensive exercises
      1. Simple function

Show through code:
            1. List query:

package web.servlet;

import domain.User;
import 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.List;

@WebServlet("/userListServlet")
public class UserListServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1. Call UserService to complete the query
        UserServiceImpl service = new UserServiceImpl();
        List<User> users = service.findAll();
        //2. Store the list in the ruquest field
        request.setAttribute("users",users);
        //3. Forward to list jsp
        request.getRequestDispatcher("/list.jsp").forward(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

 

@Override
    public List<User> findAll(){
        //Call Dao to complete the query
        return dao.findAll();
    }
 public List<User> findAll() {
        //Operating database with JDBC
        //1. Define sql
        String sql="select * from user";
        List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return users;
    }

After receiving the response of querying all users, the server will first call the findAll() method in UserServiceImpl class, and then return the data queried by sql statement in dao. Finally, the returned data will be received in UserServiceImpl class and forwarded to list The JSP page is displayed to the user. The simple process is that after receiving the request of the client button, the server returns an operated data to the client page by implementing the methods of service and dao classes.

            2. Sign in:

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>
    <title>Administrator login</title>

    <!-- 1. Import CSS Global style for -->
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <!-- 2. jQuery Import, 1 is recommended.9 Version above -->
    <script src="js/jquery-2.1.0.min.js"></script>
    <!-- 3. Import bootstrap of js file -->
    <script src="js/bootstrap.min.js"></script>
    <script type="text/javascript">
        //Switching verification code
        function refreshCode() {
            //1. Get verification code picture object
            var vcode=document.getElementById("vcode");
            //2. Set its src attribute and add time stamp
            vcode.src="${pageContext.request.contextPath}/checkCodeServlet?time="+new Date().getTime();
        }
    </script>
</head>
<body>
<div class="container" style="width: 400px;">
    <h3 style="text-align: center;">Administrator login</h3>
    <form action="${pageContext.request.contextPath}/loginServlet" method="post">
        <div class="form-group">
            <label for="user">user name:</label>
            <input type="text" name="username" class="form-control" id="user" placeholder="enter one user name"/>
        </div>

        <div class="form-group">
            <label for="password">password:</label>
            <input type="password" name="password" class="form-control" id="password" placeholder="Please input a password"/>
        </div>

        <div class="form-inline">
            <label for="vcode">Verification Code:</label>
            <input type="text" name="verifycode" class="form-control" id="verifycode" placeholder="Please enter the verification code" style="width: 120px;"/>
            <a href="javascript:refreshCode()">
                <img src="${pageContext.request.contextPath}/checkCodeServlet" title="Can't see clearly, click refresh" id="vcode"/>
            </a>
        </div>
        <hr/>
        <div class="form-group" style="text-align: center;">
            <input class="btn btn btn-primary" type="submit" value="Sign in">
        </div>
    </form>

    <!-- Error message box -->
    <div class="alert alert-warning alert-dismissible" role="alert">
        <button type="button" class="close" data-dismiss="alert" >
            <span>&times;</span></button>
        <strong>${login_msg}</strong>
    </div>
</div>
</body>
</html>

Note: this code block is the jsp page of the login page, which uses an existing login page in the css jar package imported earlier. The error display information box is set, and the information box will display different information according to different error types. Because the time stamp is set behind the verification code, the verification code will not be repeated.

package web.servlet;

import domain.User;
import org.apache.commons.beanutils.BeanUtils;
import 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 javax.servlet.http.HttpSession;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.Map;

@WebServlet("/loginServlet")
public class LoginServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1. Set code
        request.setCharacterEncoding("utf-8");
        //2. Obtain data
        //2.1 obtain the verification code filled in by the user
        String verifycode = request.getParameter("verifycode");
        //3. Verification code verification
        HttpSession session = request.getSession();
        String checkcode_server = (String)session.getAttribute("CHECKCODE_SERVER");
        session.removeAttribute("CHECKCODE_SERVER");//One time verification code
        if(!checkcode_server.equalsIgnoreCase(verifycode)){
            //Incorrect verification code
            //Prompt information
            request.setAttribute("login_msg","Verification code error!");
            //Jump to login page
            request.getRequestDispatcher("/login.jsp").forward(request,response);
            return;
        }
        Map<String, String[]> map = request.getParameterMap();
        //4. Encapsulate User object
        User user = new User();
        try {
            BeanUtils.populate(user,map);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        //5. Call Service query
        UserServiceImpl service = new UserServiceImpl();
        User loginUser = service.login(user);
        //6. Judge whether the login is successful
        if(loginUser!=null){
            //Login successful
            //Save user into session
            session.setAttribute("user",loginUser);
            //Jump page
            response.sendRedirect(request.getContextPath()+"/index.jsp");
        }else {
            //Login failed
            //Prompt information
            request.setAttribute("login_msg","Wrong user name or password!");
            //Jump to login page
            request.getRequestDispatcher("/login.jsp").forward(request,response);
        }
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

Note: there are three input boxes in the login page: user account, password and verification code. The code block is the back-end server operation class of the login page. After the user enters the information, it will be transferred to the server class. Then the class encapsulates the information entered by the user and passes it to the service and dao implementation classes. After querying the relevant data, it will return to the server for judgment, If the verification code information is wrong, it will be judged directly in the servlet class for the first time and return the error information. If the user account password is wrong, the returned data that exactly matches the database is null, the prompt information of account password error will be returned to the client page and jump to the login page again. If all inputs are correct, the matching data returned by the database is not null, the information of successful login will be returned to the client page and jump to the main page after successful login.

package web.servlet;
import java.awt.Color;
import java.awt.Font;
import java.awt.Graphics;
import java.awt.image.BufferedImage;
import java.io.IOException;
import java.util.Random;
import javax.imageio.ImageIO;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
 * Verification Code
 */
@WebServlet("/checkCodeServlet")
public class CheckCodeServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
		//The server tells the browser not to cache
		response.setHeader("pragma","no-cache");
		response.setHeader("cache-control","no-cache");
		response.setHeader("expires","0");
		//Create a picture with a length of 80 and a width of 30 in memory, with a default black background
		//Parameter 1: length
		//Parameter 2: width
		//Parameter 3: color
		int width = 80;
		int height = 30;
		BufferedImage image = new BufferedImage(width,height,BufferedImage.TYPE_INT_RGB);
		//Get brush
		Graphics g = image.getGraphics();
		//Set the brush color to gray
		g.setColor(Color.GRAY);
		//Fill picture
		g.fillRect(0,0, width,height);
		//Generate 4 random verification codes, 12Ey
		String checkCode = getCheckCode();
		//Put the verification code into HttpSession
		request.getSession().setAttribute("CHECKCODE_SERVER",checkCode);
		//Set the brush color to yellow
		g.setColor(Color.YELLOW);
		//Set font size
		g.setFont(new Font("Blackbody",Font.BOLD,24));
		//Write verification code to the picture
		g.drawString(checkCode,15,25);
		//Output pictures in memory to browser
		//Parameter 1: picture object
		//Parameter 2: image format, such as PNG,JPG,GIF
		//Parameter 3: where is the picture output
		ImageIO.write(image,"PNG",response.getOutputStream());
	}
	/**
	 * Generate 4-bit random string 
	 */
	private String getCheckCode() {
		String base = "0123456789ABCDEFGabcdefg";
		int size = base.length();
		Random r = new Random();
		StringBuffer sb = new StringBuffer();
		for(int i=1;i<=4;i++){
			//Generate random values from 0 to size-1
			int index = r.nextInt(size);
			//Get the character with index in the base string
			char c = base.charAt(index);
			//Put c into StringBuffer
			sb.append(c);
		}
		return sb.toString();
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		this.doGet(request,response);
	}
}

Note: this code block is the code that generates different verification codes, and there are horizontal lines in the code pattern to prevent the robot from recognizing the code.

Note: this code block displays the main page for the user list, and has buttons such as add, delete, query, etc...

<%@page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<!-- Language of web page -->
<html lang="zh-CN">
<head>
    <!-- Specify character set -->
    <meta charset="utf-8">
    <!-- use Edge The latest browser rendering -->
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <!-- viewport Viewport: the web page can be automatically adapted according to the set width. A container is virtual inside the browser, and the width of the container is the same as that of the device.
    width: The default width is the same as the width of the device
    initial-scale: The initial zoom ratio is 1:1 -->
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- The above three meta label*must*Put it at the top, anything else*must*Follow! -->
    <title>User information management system</title>

    <!-- 1. Import CSS Global style for -->
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <!-- 2. jQuery Import, 1 is recommended.9 Version above -->
    <script src="js/jquery-2.1.0.min.js"></script>
    <!-- 3. Import bootstrap of js file -->
    <script src="js/bootstrap.min.js"></script>
    <style type="text/css">
        td, th {
            text-align: center;
        }
    </style>
    <script>
        function deleteUser(id) {
            //User safety tips
            if(confirm("Are you sure you want to delete?")){
                //Access path
                location.href="${pageContext.request.contextPath}/delUserServlet?id="+id;
            }
        }

        window.onload=function () {
            //Add a click event to the delete selected button
            document.getElementById("delSelected").onclick=function () {
                if(confirm("Are you sure you want to delete the selected entry?")){
                    var flag=false;
                    //Judge whether there is a selected item
                    var cbs = document.getElementsByName("uid");
                    for (var i = 0; i < cbs.length; i++) {
                        if(cbs[i].checked){
                            //One entry is selected
                            flag=true;
                            break;
                        }
                    }
                    if(flag){//Entries are selected to avoid null pointer exceptions
                        //Form submission
                        document.getElementById("form").submit();
                    }
                }
            }
            //1. Get the first cb
            document.getElementById("firstCb").onclick=function () {
                //2. Get all CBS in the following list
               var cbs = document.getElementsByName("uid");
               //3. Traversal
                for (var i = 0; i < cbs.length; i++) {
                    //4. Set the checked status of these cbs[i] = firstcb checked
                    cbs[i].checked=this.checked;
                }
            }
        }
    </script>
</head>
<body>
<div class="container">
    <h3 style="text-align: center">User information list</h3>
    <div style="float: left">
        <form class="form-inline" action="${pageContext.request.contextPath}/findUserByPageServlet" method="post">
            <div class="form-group">
                <label for="exampleInputName2">full name</label>
                <input type="text" name="name" value="${condition.name[0]}" class="form-control" id="exampleInputName2">
            </div>
            <div class="form-group">
                <label for="exampleInputName3">Native place</label>
                <input type="text" name="address" value="${condition.address[0]}" class="form-control" id="exampleInputName3">
            </div>
            <div class="form-group">
                <label for="exampleInputEmail2">mailbox</label>
                <input type="email" name="email" value="${condition.email[0]}" class="form-control" id="exampleInputEmail2">
            </div>
            <button type="submit" class="btn btn-default">query</button>
        </form>
    </div>
    <div style="float: right;margin:5px">
        <a class="btn btn-primary" href="${pageContext.request.contextPath}/add.jsp">Add a Contact </a>
        <a class="btn btn-primary" href="javascript:void(0);" id="delSelected">Delete selected</a>
    </div>
    <form id="form" action="${pageContext.request.contextPath}/delSelectedServlet" method="post">
    <table border="1" class="table table-bordered table-hover">
        <tr class="success">
            <th><input type="checkbox" id="firstCb"></th>
            <th>number</th>
            <th>full name</th>
            <th>Gender</th>
            <th>Age</th>
            <th>Native place</th>
            <th>QQ</th>
            <th>mailbox</th>
            <th>operation</th>
        </tr>
       <c:forEach items="${pb.list}" var="user" varStatus="s">
        <tr>
            <th><input type="checkbox" name="uid" value="${user.id}"></th>
            <td>${s.count}</td>
            <td>${user.name}</td>
            <td>${user.gender}</td>
            <td>${user.age}</td>
            <td>${user.address}</td>
            <td>${user.qq}</td>
            <td>${user.email}</td>
            <td><a class="btn btn-default btn-sm" href="${pageContext.request.contextPath}/findUserServlet?id=${user.id}">modify</a>&nbsp;
                <a class="btn btn-default btn-sm" href="javascript:deleteUser(${user.id});">delete</a></td>
        </tr>
        </c:forEach>
        <tr>
            <td colspan="8" align="center"></td>
        </tr>
    </table>
    </form>
    <div>
        <nav aria-label="Page navigation">
            <ul class="pagination">
                <c:if test="${pb.currentPage==1}">
                    <li class="disabled">
                </c:if>
                <c:if test="${pb.currentPage!=1}">
                    <li>
                </c:if>
                    <a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${pb.currentPage-1}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}" aria-label="Previous">
                        <span aria-hidden="true">&laquo;</span>
                    </a>
                </li>
              <c:forEach begin="1" end="${pb.totalPage}" var="i">
                  <c:if test="${pb.currentPage==i}">
                      <li class="active"><a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${i}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}">${i}</a></li>
                  </c:if>
                  <c:if test="${pb.currentPage!=i}">
                      <li><a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${i}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}">${i}</a></li>
                  </c:if>
              </c:forEach>
                    <c:if test="${pb.currentPage>=pb.totalPage}">
                        <li class="disabled">
                    </c:if>
                    <c:if test="${pb.currentPage<pb.totalPage}">
                        <li>
                    </c:if>
                    <a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${pb.currentPage+1}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}" aria-label="Next">
                        <span aria-hidden="true">&raquo;</span>
                    </a>
                </li>
                <span style="font-size: 25px;margin-outside: 5px">
                    common ${pb.totalCount}Records of ${pb.totalPage}page
                </span>
            </ul>
        </nav>
    </div>
</div>
</body>
</html>

             3. add to

    <a class="btn btn-primary" href="${pageContext.request.contextPath}/add.jsp">Add a Contact </a>

Note: when the user clicks the add contact button, the page will jump to add JSP (page for adding contacts). The ${pageContext.request.contextPath} after href is a dynamic address value, which can dynamically obtain the file address value under the current project.

<%@page contentType="text/html;charset=UTF-8" language="java" %>
<!-- HTML5 file-->
<!DOCTYPE html>
<!-- Language of web page -->
<html lang="zh-CN">
<head>
    <!-- Specify character set -->
    <meta charset="utf-8">
    <!-- use Edge The latest browser rendering -->
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <!-- viewport Viewport: the web page can be automatically adapted according to the set width. A container is virtual inside the browser, and the width of the container is the same as that of the device.
    width: The default width is the same as the width of the device
    initial-scale: The initial zoom ratio is 1:1 -->
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- The above three meta label*must*Put it at the top, anything else*must*Follow! -->
    <title>Add user</title>

    <!-- 1. Import CSS Global style for -->
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <!-- 2. jQuery Import, 1 is recommended.9 Version above -->
    <script src="js/jquery-2.1.0.min.js"></script>
    <!-- 3. Import bootstrap of js file -->
    <script src="js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
    <center><h3>Add contact page</h3></center>
    <form action="${pageContext.request.contextPath}/addUserServlet" method="post">
        <div class="form-group">
            <label for="name">full name:</label>
            <input type="text" class="form-control" id="name" name="name" placeholder="Please enter your name">
        </div>

        <div class="form-group">
            <label>Gender:</label>
            <input type="radio" name="gender" value="male" checked="checked"/>male
            <input type="radio" name="gender" value="female"/>female
        </div>

        <div class="form-group">
            <label for="age">Age:</label>
            <input type="text" class="form-control" id="age" name="age" placeholder="Please enter age">
        </div>

        <div class="form-group">
            <label for="address">Native place:</label>
            <select name="address" class="form-control" id="jiguan">
                <option value="Guangdong">Guangdong</option>
                <option value="Guangxi">Guangxi</option>
                <option value="Hunan">Hunan</option>
            </select>
        </div>

        <div class="form-group">
            <label for="qq">QQ: </label>
            <input type="text" class="form-control" name="qq" placeholder="Please enter QQ number"/>
        </div>

        <div class="form-group">
            <label for="email">Email: </label>
            <input type="text" class="form-control" name="email" placeholder="Please enter email address"/>
        </div>

        <div class="form-group" style="text-align: center">
            <input class="btn btn-primary" type="submit" value="Submit" />
            <input class="btn btn-default" type="reset" value="Reset" />
            <input class="btn btn-default" type="button" value="return" />
        </div>
    </form>
</div>
</body>
</html>

Note: there is an input box for all user information in the add contact page, but the regular expression has not been set in the input box, which can not control the user's input of standardized information except gender. After user input, click Submit,

package web.servlet;
import domain.User;
import org.apache.commons.beanutils.BeanUtils;
import service.UserService;
import 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.lang.reflect.InvocationTargetException;
import java.util.Map;
@WebServlet("/addUserServlet")
public class AddUserServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1. Set code
        request.setCharacterEncoding("utf-8");
        //2. Get parameters
        Map<String, String[]> map = request.getParameterMap();
        //3. Encapsulation object
        User user = new User();
        try {
            BeanUtils.populate(user,map);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        //4. Call Service to save
        UserService service=new UserServiceImpl();
        service.addUser(user);
        //5. Jump to userlistservlet
        response.sendRedirect(request.getContextPath()+"/userListServlet");
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }

The input data is encapsulated by the BeanUtils tool class in the AddUserSeervlet class and jumps to the UserListServlet class (user list class) to invoke the addUser method in the Servlce class to store the information entered by the user into the database. This completes the function of adding user data.

            4. Delete:

 <a class="btn btn-default btn-sm" href="javascript:deleteUser(${user.id});">delete</a></td>

Note: when setting the list display format, there is a delete button behind each row of data, so after clicking the delete button, a corresponding user ID will be passed in. The ID of each user is not repeated, so the delete button will delete the corresponding row of data. After clicking the delete button, you will first enter the "javascript:deleteUser(${user.id})" to indicate whether the prompt information needs to be deleted. After confirmation, the data is passed into the DelUserServlet class to receive the data. After calling the deleteUser method in service, the delete method is passed to dao, then the corresponding data in the database is deleted by the sql statement and then skipping to the list page.

package web.servlet;
import 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;
@WebServlet("/delUserServlet")
public class DelUserServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1. Get id
        String id = request.getParameter("id");
        //2. Call service to delete
        UserServiceImpl service = new UserServiceImpl();
        service.deleteUser(id);
        //3. Jump to query all servlets
        response.sendRedirect(request.getContextPath()+"/userListServlet");
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

            5. Modification:

<a class="btn btn-default btn-sm" href="${pageContext.request.contextPath}/findUserServlet?id=${user.id}">modify</a>

Note: after clicking the Modify button, you can directly enter the findUserServlet server-side class and pass in the id value of the corresponding data together. The purpose is to obtain the data of the corresponding id and encapsulate it after passing in the corresponding id.

package web.servlet;
import domain.User;
import 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;
@WebServlet("/findUserServlet")
public class FindUserServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
         //1. Get id
        String id = request.getParameter("id");
        //2. Call Service query
        UserServiceImpl service = new UserServiceImpl();
        User user=service.findUserById(id);
        //3. Save user in request
        request.setAttribute("user",user);
        //4. Forward to update jsp
        request.getRequestDispatcher("/update.jsp").forward(request,response);
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

Note: then jump to the corresponding modification page, where there must be a hidden field < input type = "hidden" name = "id" value = "${user. id}" > to determine the id of the corresponding modification, and echo the user information before modification by judgment and comparison.

<%@page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<!-- Language of web page -->
<html lang="zh-CN">
    <head>
        <!-- Specify character set -->
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>Modify user</title>

        <link href="css/bootstrap.min.css" rel="stylesheet">
        <script src="js/jquery-2.1.0.min.js"></script>
        <script src="js/bootstrap.min.js"></script>
        
    </head>
    <body>
        <div class="container" style="width: 400px;">
        <h3 style="text-align: center;">Modify contact</h3>
        <form action="${pageContext.request.contextPath}/updateUserServlet" method="post">
            <!-- Hidden domain submission id -->
            <input type="hidden" name="id" value="${user.id}">
          <div class="form-group">
            <label for="name">full name:</label>
            <input type="text" class="form-control" id="name" name="name" value="${user.name}"  readonly="readonly" placeholder="Please enter your name" />
          </div>

          <div class="form-group">
            <label>Gender:</label>
              <c:if test="${user.gender=='male'}">
                  <input type="radio" name="gender" value="male" checked />male
                  <input type="radio" name="gender" value="female"  />female
              </c:if>
              <c:if test="${user.gender=='female'}">
                  <input type="radio" name="gender" value="male" />male
                  <input type="radio" name="gender" value="female"  checked  />female
              </c:if>
              <c:if test="${user.gender==null}">
                  <input type="radio" name="gender" value="male" />male
                  <input type="radio" name="gender" value="female" />female
              </c:if>
          </div>

          <div class="form-group">
            <label for="age">Age:</label>
            <input type="text" class="form-control" value="${user.age}" id="age"  name="age" placeholder="Please enter age" />
          </div>

          <div class="form-group">
            <label for="address">Native place:</label>
             <select name="address" class="form-control" >
                 <c:if test="${user.address=='Shaanxi'}">
                     <option value="Shaanxi" selected>Shaanxi</option>
                     <option value="Beijing">Beijing</option>
                     <option value="Hunan">Hunan</option>
                 </c:if>
                 <c:if test="${user.address=='Beijing'}">
                     <option value="Shaanxi">Shaanxi</option>
                     <option value="Beijing" selected>Beijing</option>
                     <option value="Hunan">Hunan</option>
                 </c:if>
                 <c:if test="${user.address=='Hunan'}">
                     <option value="Shaanxi">Shaanxi</option>
                     <option value="Beijing">Beijing</option>
                     <option value="Hunan" selected>Hunan</option>
                 </c:if>

            </select>
          </div>

          <div class="form-group">
            <label for="qq">QQ: </label>
            <input type="text" class="form-control" value="${user.qq}" name="qq" placeholder="Please enter QQ number"/>
          </div>

          <div class="form-group">
            <label for="email">Email: </label>
            <input type="text" class="form-control" value="${user.email}" name="email" placeholder="Please enter email address"/>
          </div>

             <div class="form-group" style="text-align: center">
                <input class="btn btn-primary" type="submit" value="Submit" />
                <input class="btn btn-default" type="reset" value="Reset" />
                <input class="btn btn-default" type="button" value="return"/>
             </div>
        </form>
        </div>
    </body>
</html>

Note: this function is similar to the subsequent deletion function. Click submit to transfer in data and modify it successfully.

  3. Complex functions
            1. Delete selected:

Analysis: if it is deleted, a check box needs to be added at the beginning of each line of data, and a selection box that selects all needs to be added at the beginning of the attribute bar. If it is deselected, it will not be selected at all.

<tr class="success">
            <th><input type="checkbox" id="firstCb"></th>
            <th>number</th>
            <th>full name</th>
            <th>Gender</th>
            <th>Age</th>
            <th>Native place</th>
            <th>QQ</th>
            <th>mailbox</th>
            <th>operation</th>
        </tr>

Note: add a check box in the first row of the table at the beginning and set an id to facilitate the function of selecting all for the check box later.

 //1. Get the first cb
            document.getElementById("firstCb").onclick=function () {
                //2. Get all CBS in the following list
               var cbs = document.getElementsByName("uid");
               //3. Traversal
                for (var i = 0; i < cbs.length; i++) {
                    //4. Set the checked status of these cbs[i] = firstcb checked
                    cbs[i].checked=this.checked;
                }
            }

Note: the function of this method is to set the state of all check boxes to be the same as that of the check box in the first row (attribute row).

package web.servlet;

import 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;

@WebServlet("/delSelectedServlet")
public class DelSelectedServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1. Get all IDS
        String[] ids = request.getParameterValues("uid");
        //2. Call service to delete
        UserServiceImpl service = new UserServiceImpl();
        service.delSelectedUser(ids);

        //3. Jump to query all servlets
        response.sendRedirect(request.getContextPath()+"/userListServlet");
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

Note: after selecting the check box to be deleted and clicking delete, the selected id will be encapsulated into an array and passed into the deletion method to traverse the deletion. After deletion, jump to the list page.

             2. Paging query:

package domain;
import java.util.List;
/*
Paging object
 */
public class PageBean<T> {
    private int totalCount;//Total records
    private int totalPage;//Total page number
    private List<T> list;//Data per page
    private int currentPage;//Current page number
    private int rows;//Number of records displayed per page

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        this.rows = rows;
    }

    @Override
    public String toString() {
        return "PageBean{" +
                "totalCount=" + totalCount +
                ", totalPage=" + totalPage +
                ", list=" + list +
                ", currentPage=" + currentPage +
                ", rows=" + rows +
                '}';
    }
}

Note: paging query needs to be operated through the above attributes, so we first obtain the above information by querying the number of user information. Where rows is the fixed value after confirmation, which represents several rows of data displayed on each page. We can obtain the total number of pages totalPage through totalCount (total users) / rows. The paging function is realized through the following code.

 @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. Create an empty PageBean object
        PageBean<User> pb=new PageBean<>();
        //2. Set parameters
        pb.setCurrentPage(currentPage);
        pb.setRows(rows);
        //3. Call dao to query the total records
        int totalCount=dao.findTotalCount(condition);
        pb.setTotalCount(totalCount);
        //4. Call dao to query the List set
        //Index of records at the beginning of calculation
        int start =(currentPage-1)*rows;
        List<User> list=dao.findByPage(start,rows,condition);
        pb.setList(list);
        //5. Calculate the total page number
        int totalPage=(totalCount % rows) ==0 ? (totalCount/rows):(totalCount/rows+1);
        pb.setTotalPage(totalPage);
        return pb;
    }
<div>
        <nav aria-label="Page navigation">
            <ul class="pagination">
                <c:if test="${pb.currentPage==1}">
                    <li class="disabled">
                </c:if>
                <c:if test="${pb.currentPage!=1}">
                    <li>
                </c:if>
                    <a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${pb.currentPage-1}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}" aria-label="Previous">
                        <span aria-hidden="true">&laquo;</span>
                    </a>
                </li>
              <c:forEach begin="1" end="${pb.totalPage}" var="i">
                  <c:if test="${pb.currentPage==i}">
                      <li class="active"><a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${i}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}">${i}</a></li>
                  </c:if>
                  <c:if test="${pb.currentPage!=i}">
                      <li><a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${i}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}">${i}</a></li>
                  </c:if>
              </c:forEach>
                    <c:if test="${pb.currentPage>=pb.totalPage}">
                        <li class="disabled">
                    </c:if>
                    <c:if test="${pb.currentPage<pb.totalPage}">
                        <li>
                    </c:if>
                    <a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${pb.currentPage+1}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}" aria-label="Next">
                        <span aria-hidden="true">&raquo;</span>
                    </a>
                </li>
                <span style="font-size: 25px;margin-outside: 5px">
                    common ${pb.totalCount}Records of ${pb.totalPage}page
                </span>
            </ul>
        </nav>
    </div>

Note: after setting all page number data, the paging function will be displayed on the jsp page, and we will be prompted that the last page after the first page cannot be selected through judgment and < Li class = "disabled" > (Forbidden selection) method.


            3. Complex condition query:

<div style="float: left">
        <form class="form-inline" action="${pageContext.request.contextPath}/findUserByPageServlet" method="post">
            <div class="form-group">
                <label for="exampleInputName2">full name</label>
                <input type="text" name="name" value="${condition.name[0]}" class="form-control" id="exampleInputName2">
            </div>
            <div class="form-group">
                <label for="exampleInputName3">Native place</label>
                <input type="text" name="address" value="${condition.address[0]}" class="form-control" id="exampleInputName3">
            </div>
            <div class="form-group">
                <label for="exampleInputEmail2">mailbox</label>
                <input type="email" name="email" value="${condition.email[0]}" class="form-control" id="exampleInputEmail2">
            </div>
            <button type="submit" class="btn btn-default">query</button>
        </form>
    </div>

Note: the representation code of complex query set in the list page is as above, with three text boxes: name, native place and email. After entering the conditions you want to query, the information will be passed into the findUserByPageServlet class, which will encapsulate the entered information into a collection.

package web.servlet;

import domain.PageBean;
import domain.User;
import 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. Get parameters
        String currentPage = request.getParameter("currentPage");//Current page
        String rows = request.getParameter("rows");//Number of items displayed per page

        if(currentPage ==null || "".equals(currentPage)){
            currentPage="1";
        }
        if(rows==null||"".equals(rows)){
            rows="5";
        }
        //Get condition query parameters
        Map<String, String[]> condition = request.getParameterMap();

        //2. Call service
        UserServiceImpl service = new UserServiceImpl();
        PageBean<User> pb=service.findUserByPage(currentPage,rows,condition);

        //3. Save PageBean into request
        request.setAttribute("pb",pb);
        request.setAttribute("condition",condition);//Save the query criteria into request
        //4. Forward to list jsp
        request.getRequestDispatcher("/list.jsp").forward(request,response);

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

Note: after entering the service implementation class, the query results will be paged.

@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. Traverse map
        Set<String> keySet = condition.keySet();
        //Defines a collection of parameters
        List<Object> params = new ArrayList<>();
        for (String key:keySet) {
            //Exclude paging condition parameters
            if("currentPage".equals(key)||"rows".equals(key)){
                continue;
            }
            //Get value
            String value = condition.get(key)[0];
            //Judge whether value has value
            if(value !=null&& !"".equals(value)){
                //Valuable
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//? Value of condition
            }
        }

        //Add paged query
        sb.append("limit ?,?");
        //Add paging query parameter value
        params.add(start);
        params.add(rows);

        sql=sb.toString();
        return template.query(sql,new BeanPropertyRowMapper<User>(User.class),params.toArray());
    }

Note: however, the query process is still in the following implementation classes. The query is carried out after traversing and splicing the data entered by the user into sql statements. After the query, the query results will be returned to the list page and paginated.

@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. Traverse map
        Set<String> keySet = condition.keySet();
        //Defines a collection of parameters
        List<Object> params = new ArrayList<>();
        for (String key:keySet) {
            //Exclude paging condition parameters
            if("currentPage".equals(key)||"rows".equals(key)){
                continue;
            }
            //Get value
            String value = condition.get(key)[0];
            //Judge whether value has value
            if(value !=null&& !"".equals(value)){
                //Valuable
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//? Value of condition
            }
        }

        //Add paged query
        sb.append("limit ?,?");
        //Add paging query parameter value
        params.add(start);
        params.add(rows);

        sql=sb.toString();
        return template.query(sql,new BeanPropertyRowMapper<User>(User.class),params.toArray());
    }

 

Summary:

For the general process of this small case, you can complete the general functions first, and then add detailed functions such as paging. The ideas for completing this case can be arranged as follows:

Create database -- > connect database -- > create user attribute class -- > write login page -- > add user data in the database and add account password (account password can not be written in user attribute class) - > create dao interface class and implementation class service interface class and implementation class -- > realize the function of login authentication -- > create the home page interface (enter the user list display page) - > create the user list display page (including delete, add, find and other function buttons) --->After the user list display is realized, add and delete functions are realized -- > paging function is realized -- > condition query function is realized -- > condition query list paging function is realized -- > completed.

This blog is only for personal growth records and has no reference value.

Added by pheagey on Fri, 18 Feb 2022 19:47:03 +0200