简单的增删改查

Fruit

1 项目准备

1.1创建一个web项目

在这里插入图片描述

1.3项目起步测试

在这里插入图片描述

1.4数据库fruit及表fruit、user的创建

在这里插入图片描述

fruit表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80029
 Source Host           : localhost:3306
 Source Schema         : fruit

 Target Server Type    : MySQL
 Target Server Version : 80029
 File Encoding         : 65001

 Date: 06/10/2022 14:53:13
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for fruit
-- ----------------------------
DROP TABLE IF EXISTS `fruit`;
CREATE TABLE `fruit`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `category` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `varieties` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `origin` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `weight` int(0) NULL DEFAULT NULL,
  `price` double NOT NULL,
  `synopsis` tinytext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 63 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of fruit
-- ----------------------------
INSERT INTO `fruit` VALUES (1, '苹果', '红富士', '山东', 1000, 16, '果面洁净、色泽鲜艳、酸甜适度,品质优良');
INSERT INTO `fruit` VALUES (2, '梨', '皇冠梨', '河北', 800, 9, '好吃');
INSERT INTO `fruit` VALUES (3, '苹果', '红富士', '河北', 900, 14, '好吃');
INSERT INTO `fruit` VALUES (4, '苹果', '红富士', '山东', 1000, 12, '好吃');
INSERT INTO `fruit` VALUES (31, '梨', '皇冠梨', '河北', 800, 9, '1');
INSERT INTO `fruit` VALUES (32, '苹果', '红富士', '河北', 800, 12, '2');
INSERT INTO `fruit` VALUES (33, '梨', '皇冠梨', '河北', 150, 9, '3');
INSERT INTO `fruit` VALUES (34, '苹果', '红富士苹果', '河北', 800, 8, '1');
INSERT INTO `fruit` VALUES (36, '苹果', '红富士苹果', '河北', 800, 8, '1');
INSERT INTO `fruit` VALUES (37, '苹果', '红富士苹果', '河北', 800, 8, '1');
INSERT INTO `fruit` VALUES (38, '苹果', '红富士苹果', '河北', 800, 8, '1');
INSERT INTO `fruit` VALUES (40, '苹果', '皇冠梨', '河北', 150, 12, '1');
INSERT INTO `fruit` VALUES (41, '苹果', '皇冠梨', '河北', 150, 9, '1');
INSERT INTO `fruit` VALUES (43, '梨', '皇冠梨', '河北', 800, 12, '2');
INSERT INTO `fruit` VALUES (44, '梨', '皇冠梨', '河北', 800, 12, '2');
INSERT INTO `fruit` VALUES (46, '苹果', '红富士苹果', '河北', 800, 9, '1');
INSERT INTO `fruit` VALUES (47, '梨', '皇冠梨', '河北', 150, 9, '4444');
INSERT INTO `fruit` VALUES (50, '梨', '皇冠梨', '河北', 800, 9, '123');
INSERT INTO `fruit` VALUES (51, '', '', '', 0, 0, '');
INSERT INTO `fruit` VALUES (52, '梨', '皇冠梨', '河北', 800, 9, '1111111111');
INSERT INTO `fruit` VALUES (53, '苹果', '红富士苹果', '河北', 800, 12, '1222222');
INSERT INTO `fruit` VALUES (54, '梨', '皇冠梨', '河北', 800, 12, '33333');
INSERT INTO `fruit` VALUES (55, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (56, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (57, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (58, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (59, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (60, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (61, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (62, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');

SET FOREIGN_KEY_CHECKS = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

user表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80029
 Source Host           : localhost:3306
 Source Schema         : fruit

 Target Server Type    : MySQL
 Target Server Version : 80029
 File Encoding         : 65001

 Date: 06/10/2022 14:53:20
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `password` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'Anna', '123');
INSERT INTO `user` VALUES (2, 'lily', '123456');

SET FOREIGN_KEY_CHECKS = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

1.5基本配置

1.5.1 项目所需要的jar包

在这里插入图片描述

1.5.2 c3p0数据库连接数据库

在这里插入图片描述

1）连接数据库xml配置

<c3p0-config>
    <!--使用默认的配置读取连接池对象-->
    <default-config>
        <!--连接参数-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/fruit?useSSL=true&amp;characterEncoding=UTF8</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <!-- 连接池参数 -->
        <!--初始化申请的连接数量-->
        <property name="initialPoolSize">5</property>
        <!--最大的连接数量-->
        <property name="maxPoolSize">10</property>
        <!--超时时间-->
        <property name="checkoutTimeout">3000</property>
    </default-config>
</c3p0-config>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2）工具类

package com.fc.utils;


import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Jdbc工具类
 */
public class JdbcUtilsOnC3P0 {
    // 提取资源
    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;
    // 获取C3P0连接池对象
    private static ComboPooledDataSource pool = new ComboPooledDataSource();

    // 获取连接
    public static Connection getConnection() {
        try {
            connection = pool.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }

    // 关闭方法
    public static void close(Connection connection) {
        close(resultSet, statement, connection);
    }

    public static void close(Statement statement) {
        close(resultSet, statement, connection);
    }

    public static void close(Statement statement, Connection connection) {
        close(resultSet, statement, connection);
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

2 字符编码过滤器

通用字符编码过滤器的分析：

网站，需要向后台提交中文的数据（有可能是GET也有可能是POST）。中文处理根据不同的请求方式，处理的方式也是不一样的。

需要调用request.getParameter();

方法接收数据，但是这个时候无论是get还是post接收的数据都是存在乱码。

现在调用request.getParameter()方法无论是get还是post请求提交的中文，都没有乱码。

package com.fc.Filter;

import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@WebFilter(urlPatterns = "/*",initParams = {
        @WebInitParam(name="characterEncoding",value = "UTF8"),
        @WebInitParam(name="contextType",value = "text/html;charset=UTF-8")
})
public class EncodingFilter implements Filter {

    //声明编码集和文本内容
    private static  String characterEncoding;
    private static  String contextType;

    @Override
    public void init(FilterConfig filterConfig) throws ServletException {
        characterEncoding = filterConfig.getInitParameter("characterEncoding");
        contextType=filterConfig.getInitParameter("contextType");
    }

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        //把请求对象和响应对象强转为支持http协议的对象
        HttpServletRequest request=(HttpServletRequest)servletRequest;
        HttpServletResponse response=(HttpServletResponse)servletResponse;
        request.setCharacterEncoding(characterEncoding);
        response.setContentType(contextType);
        filterChain.doFilter(request,response);
    }

    @Override
    public void destroy() {

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

3 登录功能实现

3.1 登录过滤器

(1) 强转，让请求支持HTTP协议

(2) 获取session

(3) 获取请求URI

(4) 判断是否是登录页面，是登录页面则放行进入登录页面

(5) 已登录（session及session中的属性不为空，说明是已登录状态），则放行

(6) 未登录，则直接跳转至登录页面

 注意：
 getSession(boolean create)意思是返回当前reqeust中的HttpSession
 如果当前reqeust中的HttpSession 为null; 当create为true，就创建一个新的Session，否则返回null；
1
2
3

代码实现

package com.fc.Filter;

import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;

/**
 * 自动登录过滤器
 */
@WebFilter("/*")
public class AutoLoginFilter implements Filter {
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {

    }

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        //强转，让请求支持HTTP协议
        HttpServletRequest request=(HttpServletRequest)servletRequest;
        HttpServletResponse response=(HttpServletResponse) servletResponse;
        //获取session
        HttpSession session = request.getSession(false);
        //获取请求URI
        String uri = request.getRequestURI();
        //判断是否是登录页面，是登录页面则放行进入登录页面
         if (uri.endsWith("/login.jsp")||uri.endsWith("/login")){
             filterChain.doFilter(request,response);
         }else if (session!=null&&session.getAttribute("user")!=null){
             //已登录（session及session中的属性不为空，说明是已登录状态），则放行
             filterChain.doFilter(request,response);
        }else {
             //未登录，则直接跳转至登录页面
             response.sendRedirect("login.jsp");
         }
    }

    @Override
    public void destroy() {

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

3.2登录jsp页面

<%--
  Created by IntelliJ IDEA.
  User: HP
  Date: 2022/10/7
  Time: 8:18
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>登录</title>
    <!-- 最新版本的 Bootstrap 核心 CSS 文件 -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
    <style type="text/css">
        body{
            background-color: aquamarine;
        }
        .login{
            width: 500px;
            margin: 150px auto;
        }
    </style>
</head>
<body>
<form class="form-horizontal"  action="${pageContext.request.contextPath}/login" method="post">
    <div class="main">
        <div class="login">
            <div class="form-group">
                <label for="inputEmail3" class="col-sm-2 control-label">用户名</label>
                <div class="col-sm-10">
                    <input type="text" class="form-control" id="inputEmail3" placeholder="请输入用户名" name="username">
                </div>
            </div>
            <div class="form-group">
                <label for="inputPassword3" class="col-sm-2 control-label">密&nbsp;&nbsp;&nbsp;码</label>
                <div class="col-sm-10">
                    <input type="password" class="form-control" id="inputPassword3" placeholder="请输入密码" name="password">
                </div>
            </div>
            <div class="form-group">
                <div class="col-sm-offset-2 col-sm-10">
                    <button type="submit" class="btn btn-default">登录</button>
                </div>
            </div>
        </div>
    </div>
</form>
</body>
</html>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

3.3登录代码实现

package com.fc.servlet;

import com.fc.bean.User;
import com.fc.utils.JdbcUtilsOnC3P0;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

@WebServlet("/login")
public class LoginServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //获取参数
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        //连接数据库
        //获取核心类对象
        QueryRunner queryRunner = new QueryRunner();
        //获取数据库连接
        Connection connection = JdbcUtilsOnC3P0.getConnection();
        //准备SQL语句
        String sql="select * from user where username= ? and password= ?";
       //准备参数
        Object[] params={username,password};
        List<User> user =null;
        //执行SQL语句并获取实体类对象
        try {
             user = queryRunner.query(connection, sql, new BeanListHandler<>(User.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        if (user!=null){
            //登录成功
            HttpSession session = req.getSession(true);
            //设置过期时间
            session.setMaxInactiveInterval(60*60);
            //设置属性键值对
            session.setAttribute("user",user);
            //获取一个cookie
            Cookie cookie = new Cookie("JSESSIONID", session.getId());
            //设置cookie的过期时间
            cookie.setMaxAge(60*60);
            //将cookie发送到浏览器
            resp.addCookie(cookie);
            //跳转到主页
            resp.sendRedirect("index.jsp");
        }else{
            //登录失败
            resp.sendRedirect("login.jsp");
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
       doGet(req, resp);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

4 水果信息展示

4.1 页面信息实体类（PageInfo）

变量信息：

int pageCount;//总页数
int totalCount;//总数据量，从数据库获取
int pageSize;//每页显示的数据量
int pageNo;//当前页，从前端获取
List list;//每一页中的所有数据

package com.fc.bean;

import java.util.List;

/**
 * 分页信息类
 * @param 泛型，指明我们要查询的内容
 */
public class PageInfo<T> {
    private int pageCount;//总页数
    private  int totalCount;//总数据量，从数据库获取
    private  int pageSize;//每页显示的数据量
    private  int pageNo;//当前页，从前端获取
    //泛型类
    private List<T> list;//每一页中的所有数据
    public PageInfo() {
    }
    public PageInfo(int totalCount, int pageSize, int pageNo, List<T> list) {
        this.totalCount = totalCount;
        this.pageSize = pageSize;
        this.pageNo = pageNo;
        this.list = list;
        //判断总数据量能否整除每一条显示的数据
        if(totalCount%pageSize==0){
            this.pageCount=totalCount/pageSize;
        }else {
            this.pageCount=totalCount/pageSize+1;
        }
    }
    @Override
    public String toString() {
        return "PageInfo{" +
                "pageCount=" + pageCount +
                ", totalCount=" + totalCount +
                ", pageSize=" + pageSize +
                ", pageNo=" + pageNo +
                ", list=" + list +
                '}';
    }

    public int getPageCount() {
        return pageCount;
    }

    public void setPageCount(int pageCount) {
        this.pageCount = pageCount;
    }

    public int getTotalCount() {
        return totalCount;
    }

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

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getPageNo() {
        return pageNo;
    }

    public void setPageNo(int pageNo) {
        this.pageNo = pageNo;
    }

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

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

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

4.2页面信息service

package com.fc.service;

import com.fc.bean.Fruit;
import com.fc.bean.PageInfo;
import com.fc.dao.PageDao;

import java.util.List;

public class PageService {
    public PageInfo<Fruit> getPageInfo(String pageNo,int pageSize){
        //判断当前页码是否存在
        if (pageNo==null){
            pageNo="1";
        }
        //转型，把String类型的当前页转为int类型
        int currentPage = Integer.parseInt(pageNo);
        //获取每页中的第一条数据-->start
        int start=(currentPage-1)*pageSize;
        //创建一个对象，用来进行数据库相关的操作
        PageDao pageDao = new PageDao();
        //获取每一页中的所有数据
        List<Fruit> list = pageDao.getDataList(start, pageSize);
        //获取总数据量
        int totalCount = pageDao.getTotalCount();
        //创建一个分页信息对象
        PageInfo<Fruit> pageInfo = new PageInfo<>(totalCount, pageSize, currentPage, list);
        //进项传递
        return  pageInfo;

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

4.3页面信息dao(数据访问层，用来进行数据库相关的操作)

package com.fc.dao;

import com.fc.bean.Fruit;
import com.fc.utils.JdbcUtilsOnC3P0;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 数据访问层，用来进行数据库相关的操作
 */
public class PageDao {
    //获取核心类对象
    private QueryRunner queryRunner = new QueryRunner();
    //获取数据库的连接
    private Connection connection = JdbcUtilsOnC3P0.getConnection();
    /**
     *获取指定页中的所有数据
     *
     * @param start 指定页的起始
     * @param pageSize 每页查询的条数
     * @return 返回一个包含实体类对象的list集合
     */
    public List<Fruit> getDataList(int start, int pageSize) {
        //准备sql语句
        String sql="select * from fruit limit ?,?";
        //准备参数
        Object[] params={start,pageSize};
        //提取list集合
        List<Fruit> fruits=null;
        try {
            //执行sql语句并获取list结果集
            fruits= queryRunner.query(connection, sql, new BeanListHandler<>(Fruit.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return fruits;
    }

    /**
     * 获取总数据量
     * @return 总数据量
     */
    public int getTotalCount() {
        // 准备SQL语句
        String sql = "select * from fruit";
        // 提取List
        List< Fruit> list = null;
        try {
            // 执行SQL语句并获取所有数据对应的List集合
            list = queryRunner.query(connection, sql, new BeanListHandler<>(Fruit.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 获取集合中元素的个数并返回
        return list.size();
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

4.4页面信息controller（控制层）

写Servlet,用于和前端进行交互

package com.fc.controller;

import com.fc.bean.Fruit;
import com.fc.bean.PageInfo;
import com.fc.service.PageService;

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;
/**
 * 控制层，写Servlet。用于和前端进行交互
 */
@WebServlet("/page")
public class PageServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 从前端获取参数
        String pageNo = req.getParameter("pageNo");
        // 声明每页显示的条数
        int pageSize = 5;
        // 声明一个水果服务对象
        PageService pageService = new PageService();
        // 从服务对象中获取我需要的PageInfo
        PageInfo<Fruit> pageInfo = pageService.getPageInfo(pageNo, pageSize);
        //给域对象中设置属性参数键值对
        req.setAttribute("pageInfo",pageInfo);
        System.out.println(pageInfo);
        //转发到主页面展示
        req.getRequestDispatcher("index.jsp").forward(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet (req, resp);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

4.5页面信息jsp

<%--
  Created by IntelliJ IDEA.
  User: HP
  Date: 2022/10/13
  Time: 12:54
  To change this template use File | Settings | File Templates.
--%>
<%@ page import="org.apache.commons.dbutils.QueryRunner" %>
<%@ page import="com.fc.utils.JdbcUtilsOnC3P0" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.util.List" %>
<%@ page import="com.fc.bean.Fruit" %>
<%@ page import="org.apache.commons.dbutils.handlers.BeanListHandler" %>
<%@ page import="java.sql.SQLException" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
  <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>水果查询</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
    <script src="js/jquery-3.5.1.js"></script>
    <style type="text/css">
      .fruits-list{
        width: 500px;
        margin: 60px auto;
      }
      table{
        text-align: center;
      }
      th{
        text-align: center;
      }
      a{
        text-decoration: none;
      }
    </style>
  </head>
  <body>
  <div class="main">
    <div class="fruits-list">
      <a href="add.jsp">添加水果</a>
      <table class="table table-striped table-bordered table-hover" >
        <tr>
          <th>ID</th>
          <th>类别</th>
          <th>品种</th>
          <th>产地</th>
          <th>总重量(公斤)</th>
          <th>单价(元/公斤)</th>
          <th>删除</th>
          <th>详情</th>
        </tr>
        <c:forEach var="fruit" items="${pageInfo.list}">
        <tr>
          <td>${fruit.getId()}</td>
          <td>${fruit.getCategory()}</td>
          <td>${fruit.getVarieties()}</td>
          <td>${fruit.getOrigin()}</td>
          <td>${fruit.getWeight()}</td>
          <td>${fruit.getPrice()}</td>
          <td><a href="delete?id=${fruit.getId()}" class="glyphicon glyphicon-trash" ></a></td>
          <td><a href="detail?id=${fruit.getId()}" class="glyphicon glyphicon-edit"></a></td>
        </tr>
        </c:forEach>
        <form action="page" method="get">
          <table align="center">
            <tr>
              <%--首页和上一页--%>
              <c:if test="${pageInfo.pageNo == 1}">
                <td><a href="javascript:return false;" style="color: orangered">首页</a></td>
                <td><a href="javascript:return false;" style="color: orangered">上一页</a></td>
              </c:if>
              <c:if test="${pageInfo.pageNo != 1}">
                <td><a href="page?pageNo=1">首页</a></td>
                <td><a href="page?pageNo=${pageInfo.pageNo - 1}">上一页</a></td>
              </c:if>
              <%--当前页--%>
              <td>
                <c:forEach var="pageNo" begin="1" end="${pageInfo.pageCount}">
                  <c:if test="${pageNo == pageInfo.pageNo}">
                    <input type="submit" name="pageNo" value="${pageNo}" disabled>
                  </c:if>
                  <c:if test="${pageNo != pageInfo.pageNo}">
                    <input type="submit" name="pageNo" value="${pageNo}">
                  </c:if>
                </c:forEach>
              </td>
              <%--下一页和尾页--%>
              <c:if test="${pageInfo.pageNo == pageInfo.pageCount}">
                <td><a href="javascript:return false;" style="color: orangered">下一页</a></td>
                <td><a href="javascript:return false;" style="color: orangered">尾页</a></td>
              </c:if>
              <c:if test="${pageInfo.pageNo != pageInfo.pageCount}">
                <td><a href="page?pageNo=${pageInfo.pageNo + 1}">下一页</a></td>
                <td><a href="page?pageNo=${pageInfo.pageCount}">尾页</a></td>
              </c:if>
            </tr>
          </table>
        </form>
    </div>
  </div>
  </body>
</html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

4.6实现效果展示

在这里插入图片描述

5 详情信息展示

jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>详情页面</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
    <style type="text/css">
        .detail{
            width: 800px;
            margin: 0 auto;
        }
    </style>
</head>
<body>
<div class="main">
    <div class="detail">
        <%--<a href="${pageContext.request.contextPath}/fruits">返回上一页</a>--%>
        <table class="table table-striped table-hover" >
            <tr>
                <td>ID</td>
                <td>${fruit.id}</td>
            </tr>
            <tr>
                <td>类别</td>
                <td>${fruit.category}</td>
            </tr>
            <tr>
                <td>品种</td>
                <td>${fruit.varieties}</td>
            </tr>
            <tr>
                <td>产地</td>
                <td>${fruit.origin}</td>
            </tr>
            <tr>
                <td>总重量(公斤)</td>
                <td>${fruit.weight}</td>
            </tr>
            <tr>
                <td>单价(元/公斤)</td>
                <td>${fruit.price}</td>
            </tr>
            <tr>
                <td>详情</td>
                <td>${fruit.synopsis}</td>
            </tr>
        </table>
    </div>
</div>
</body>
</html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

servlet

package com.fc.servlet;

import com.fc.bean.Fruit;
import com.fc.utils.JdbcUtilsOnC3P0;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

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.sql.Connection;
import java.sql.SQLException;

@WebServlet("/detail")
public class DetailFruitServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
        //获取参数id
        Integer id = Integer.parseInt(req.getParameter("id"));
        System.out.println (id);
        //连接数据库，根据id，进行水果信息排查
        QueryRunner queryRunner = new QueryRunner ();
        Connection connection = JdbcUtilsOnC3P0.getConnection ();
        String sql="select * from fruit where id=?";
        Object[] params={id};
        Fruit fruit=null;
        try {
            fruit = queryRunner.query (connection, sql, new BeanHandler<Fruit> (Fruit.class), params);
        } catch ( SQLException e ) {
            e.printStackTrace ();
        }
        if (fruit!=null){
            //给域对象中设置属性参数键值对
            req.setAttribute ("fruit",fruit);
            System.out.println (fruit);
            //转发到详情页面
            req.getRequestDispatcher ("detail.jsp").forward (req,resp);
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet (req, resp);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

6 删除一条数据

package com.fc.servlet;

import com.fc.utils.JdbcUtilsOnC3P0;
import org.apache.commons.dbutils.QueryRunner;

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.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;

@WebServlet("/delete")
public class DeleteFruitServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        PrintWriter out = resp.getWriter();
        String new_id = req.getParameter ("id");
        //连接数据库，根据id进行排查
        QueryRunner queryRunner = new QueryRunner ();
        Connection connection = JdbcUtilsOnC3P0.getConnection ();
        String sql = "delete from fruit where id = ?";
        System.out.println (new_id);
        Object[] params={new_id};
        int affectedRows = 0;
        try {
            affectedRows=queryRunner.update (connection,sql,params);
        } catch ( SQLException e ) {
            e.printStackTrace ();
        }
        System.out.println (affectedRows);
        //判断是否存在
        if (affectedRows!=0){
            out.print ("删除成功！");
        }else {
            out.print ("删除失败");
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet (req, resp);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

7 添加一条数据

jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>添加水果页面</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
    <script src="js/jquery-3.5.1.js"></script>
    <style type="text/css">
        .add-fruits{
            width: 500px;
            margin: 0 auto;
        }
    </style>
    <script type="text/javascript">

        function add() {
            var category= $("input[name='category']").val();
            var varieties = $("input[name='varieties']").val();
            var origin = $("input[name='origin']").val();
            var weight = $("input[name='weight']").val();
            var price = $("input[name='price']").val();
            var synopsis = $("#synopsis").val();

            $.ajax({
                url:"add",
                type:"post",
                data: {
                    category:category,
                    varieties:varieties,
                    origin:origin,
                    weight:weight,
                    price:price,
                    synopsis:synopsis
                },
                success:function () {
                   // $("#addFruits")[0].reset();
                    alert("添加成功");
                    window.location.href="${pageContext.request.contextPath}/index.jsp";
                },
                error:function () {
                    alert("添加失败")
                }
            });
        }
    </script>
</head>
<body>
<div class="main">
    <div class="add-fruits">
        <form class="form-horizontal" id="addFruits">
            <div class="form-group">
                <label for="inputEmail3" class="col-sm-2 control-label">产&nbsp;&nbsp;品</label>
                <div class="col-sm-10">
                    <input type="text" class="form-control" id="inputEmail3" placeholder="请输入产品名" name="category" >
                </div>
            </div>
            <div class="form-group">
                <label for="inputPassword3" class="col-sm-2 control-label">品&nbsp;&nbsp;种</label>
                <div class="col-sm-10">
                    <input type="text" class="form-control" id="inputPassword3" placeholder="请输入品种" name="varieties" >
                </div>
            </div>
            <div class="form-group">
                <label for="inputPassword3" class="col-sm-2 control-label">产&nbsp;&nbsp;地</label>
                <div class="col-sm-10">
                    <input type="text" class="form-control" id="inputPassword3" placeholder="请输入产地名" name="origin" >
                </div>
            </div>
            <div class="form-group">
                <label for="inputPassword3" class="col-sm-2 control-label">重&nbsp;&nbsp;量</label>
                <div class="col-sm-10">
                    <input type="text" class="form-control" id="inputPassword3" placeholder="请输入总重量" name="weight" >
                </div>
            </div>
            <div class="form-group">
                <label for="inputPassword3" class="col-sm-2 control-label">价&nbsp;&nbsp;格</label>
                <div class="col-sm-10">
                    <input type="text" class="form-control" id="inputPassword3" placeholder="请输入价格" name="price" >
                </div>
            </div>
            <div class="form-group">
                <label for="inputPassword3" class="col-sm-2 control-label">详细信息</label>
                <div class="col-sm-10">
                    <textarea class="form-control" rows="3" placeholder="请输入详细信息" name="synopsis" id="synopsis"></textarea>
                </div>
            </div>

            <div class="form-group">
                <div class="col-sm-offset-2 col-sm-10">
                    <button type="button" class="btn btn-default" onclick="add()">提交</button>
                    <button type="reset" class="btn btn-default">重置</button>
                </div>
            </div>
        </form>
    </div>
</div>
</body>
</html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

servlet

package com.fc.servlet;

import com.fc.bean.Fruit;
import com.fc.utils.JdbcUtilsOnC3P0;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;

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.io.PrintWriter;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;

@WebServlet("/add")
public class AddFruitServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        try {
            PrintWriter out = resp.getWriter();
            //获取参数
            Map<String, String[]> map = req.getParameterMap ();
            //声明一个水果类对象
            Fruit fruit = new Fruit ();
            //使用BeanUtils进行封装
            BeanUtils.populate (fruit,map);
            //操作数据库
            QueryRunner queryRunner = new QueryRunner ();
            // 获取数据库的连接
            Connection connection = JdbcUtilsOnC3P0.getConnection();
            // 准备SQL语句
            String sql = "insert into fruit(category,varieties,origin,weight,price,synopsis) values(?,?,?,?,?,?)";
            // 准备参数
            Object[] params = {fruit.getCategory (),fruit.getVarieties (),fruit.getOrigin (),fruit.getWeight (),fruit.getPrice (),fruit.getSynopsis ()};
            // 执行SQL语句
            int affectedRows = queryRunner.update(connection, sql, params);
//            System.out.println (affectedRows);
            if (affectedRows > 0) {
                System.out.println ("q");
//                resp.sendRedirect("index.jsp");
                req.getRequestDispatcher ("index.jsp").forward (req,resp);
            } else {
                out.print ("添加失败!");
            }
        } catch ( IllegalAccessException  |InvocationTargetException e ) {
            e.printStackTrace ();
        } catch ( SQLException e ) {
            e.printStackTrace ();
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet (req, resp);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

取数据库的连接
Connection connection = JdbcUtilsOnC3P0.getConnection();
// 准备SQL语句
String sql = “insert into fruit(category,varieties,origin,weight,price,synopsis) values(?,?,?,?,?,?)”;
// 准备参数
Object[] params = {fruit.getCategory (),fruit.getVarieties (),fruit.getOrigin (),fruit.getWeight (),fruit.getPrice (),fruit.getSynopsis ()};
// 执行SQL语句
int affectedRows = queryRunner.update(connection, sql, params);
// System.out.println (affectedRows);
if (affectedRows > 0) {
System.out.println (“q”);
// resp.sendRedirect(“index.jsp”);
req.getRequestDispatcher (“index.jsp”).forward (req,resp);
} else {
out.print (“添加失败!”);
}
} catch ( IllegalAccessException |InvocationTargetException e ) {
e.printStackTrace ();
} catch ( SQLException e ) {
e.printStackTrace ();
}
}

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    doGet (req, resp);
}
1
2
3
4

}

相关阅读:
手写一个博客平台 ~ 第七天
 UE5 Lyra中的UI层级与资产管理
 CompletableFuture 异常与事务【无标题】
An工具介绍之宽度工具、变形工具与套索工具
 JavaScript——变量
 vue flvjs 播放视频
 Elasticsearch索引操作(七)
Opencv与python实现多目标跟踪（一） - PaddleDetection目标检测
 【Android进阶】14、顶部应用栏
 设计原则五:依赖倒置原则
原文地址：https://blog.csdn.net/Angeliaz/article/details/127380709

简单的增删改查

文章目录

Fruit

1 项目准备

1.1创建一个web项目

1.2Tomcat热部署

1.3项目起步测试

1.4数据库fruit及表fruit、user的创建

fruit表

user表

1.5基本配置

1.5.1 项目所需要的jar包

1.5.2 c3p0数据库连接数据库

2 字符编码过滤器

3 登录功能实现

3.1 登录过滤器

代码实现

3.2登录jsp页面

3.3登录代码实现

4 水果信息展示

4.1 页面信息实体类（PageInfo）

4.2页面信息service

4.3页面信息dao(数据访问层，用来进行数据库相关的操作)

4.4页面信息controller（控制层）

4.5页面信息jsp

4.6实现效果展示

5 详情信息展示

jsp

servlet

6 删除一条数据

7 添加一条数据

jsp

servlet