SMBMS项目讲解

JDBC复习

需要jar包的支持：

java.sql
javax.sql
mysql-connection-java…（连接驱动）
导入jar包

 <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
    </dependencies>

创建数据库

DROP TABLE `users`;


CREATE TABLE `users`(
  `id` INT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `password` VARCHAR(40) NOT NULL,
  `email` VARCHAR(60) NOT NULL,
  `birthday` DATE
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('1','张三','123456','zs@sina.com','2021-07-14');
INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('2','李四','123456','lisi@sina.com','1981-12-04');
INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('3','王五','123456','wangwu@sina.com','1982-12-04');
INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('4','赵六','123456','zhaoliu@sina.com','1987-12-05');
INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('5','钱七','123456','qianqi@sina.com','2021-07-19');
INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('6','刘八','123456','liuba@sina.com','2021-07-19');

idea连接数据库（6步曲）

package com.jdbc.test;

import java.sql.*;

public class TestJdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123456";

        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //连接数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //创建Statement
        Statement statement = connection.createStatement();
        //编写sql
        String sql="select * from users";
        //执行sql
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println("id=:"+ resultSet.getObject("id"));
            System.out.println("NAME=:"+ resultSet.getObject("NAME"));
            System.out.println("PASSWORD=:"+ resultSet.getObject("PASSWORD"));
            System.out.println("email=:"+ resultSet.getObject("email"));
            System.out.println("birthday=:"+ resultSet.getObject("birthday"));

        }
        //关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

预编译sql 防止mysql注入

public class Test2Jdbc {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8";
        String user = "root";
        String pwd = "123456";
        try {

            //1. 加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2. 连接数据库
            Connection con = DriverManager.getConnection(url, user, pwd);

            //3.sql
            String str = "insert into users(id, name, password, email, birthday) values (?,?,?,?,?)";
            //4. 预编译
            PreparedStatement statement = con.prepareStatement(str);
            statement.setInt(1,8);
            statement.setString(2,"小红");
            statement.setString(3,"123456");
            statement.setString(4,"xiaohong@sina.com");
            statement.setDate(5,new java.sql.Date(System.currentTimeMillis()));


            //5.执行sql
            int count = statement.executeUpdate();

            if (count > 0) {
                System.out.println("插入成功！");
            }
            statement.close();
            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

jdbc 事务

开启事务
事务提交 commit()
事务回滚 rollback()
关闭事务

转账：
A:1000
B:1000

A(900) —100–> B(1100)
构建数据库

CREATE TABLE `account`(
  `id` INT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `money` FLOAT NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `account` (`id`, `name`, `money`) VALUES(1,'A',1000);
INSERT INTO `account` (`id`, `name`, `money`) VALUES(2,'B',1000);
INSERT INTO `account` (`id`, `name`, `money`) VALUES(3,'C',1000);

idea

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @ClassName: Test3Jdbc
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/12/16 21:11
 * @Version: 1.0
 */
public class Test3Jdbc {

    @Test
    public void test() {

        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8";
        String user = "root";
        String pwd = "123456";
        Connection con = null;
        try {

            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url, user, pwd);


            //开启事务 这里不开启事务的话异常情况就会有问题   false是开启
            con.setAutoCommit(false);

            con.prepareStatement("update account set money=money-100 where name='A'").executeUpdate();

            //制造错误
//            int i = 1 / 0;

            con.prepareStatement("update account set money=money+100 where name='B'").executeUpdate();

            con.commit();

            System.out.println("success");

        } catch (Exception e) {
            System.out.println("error rollback");
            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

项目讲解

数据库搭建

DROP TABLE IF EXISTS `smbms_address`;
CREATE TABLE `smbms_address`  (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `contact` VARCHAR(15) NULL DEFAULT NULL COMMENT '联系人姓名',
  `addressDesc` VARCHAR(50) NULL DEFAULT NULL COMMENT '收货地址明细',
  `postCode` VARCHAR(15) NULL DEFAULT NULL COMMENT '邮编',
  `tel` VARCHAR(20) NULL DEFAULT NULL COMMENT '联系人电话',
  `createdBy` BIGINT(20) NULL DEFAULT NULL COMMENT '创建者',
  `creationDate` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
  `modifyBy` BIGINT(20) NULL DEFAULT NULL COMMENT '修改者',
  `modifyDate` DATETIME NULL DEFAULT NULL COMMENT '修改时间',
  `userId` BIGINT(20) NULL DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `smbms_address` VALUES (1, '王丽', '北京市东城区东交民巷44号', '100010', '13678789999', 1, '2016-04-13 00:00:00', NULL, NULL, 1);
INSERT INTO `smbms_address` VALUES (2, '张红丽', '北京市海淀区丹棱街3号', '100000', '18567672312', 1, '2016-04-13 00:00:00', NULL, NULL, 1);
INSERT INTO `smbms_address` VALUES (3, '任志强', '北京市东城区美术馆后街23号', '100021', '13387906742', 1, '2016-04-13 00:00:00', NULL, NULL, 1);
INSERT INTO `smbms_address` VALUES (4, '曹颖', '北京市朝阳区朝阳门南大街14号', '100053', '13568902323', 1, '2016-04-13 00:00:00', NULL, NULL, 2);
INSERT INTO `smbms_address` VALUES (5, '李慧', '北京市西城区三里河路南三巷3号', '100032', '18032356666', 1, '2016-04-13 00:00:00', NULL, NULL, 3);
INSERT INTO `smbms_address` VALUES (6, '王国强', '北京市顺义区高丽营镇金马工业区18号', '100061', '13787882222', 1, '2016-04-13 00:00:00', NULL, NULL, 3);

DROP TABLE IF EXISTS `smbms_bill`;
CREATE TABLE `smbms_bill`  (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `billCode` VARCHAR(20) NULL DEFAULT NULL COMMENT '账单编码',
  `productName` VARCHAR(20) NULL DEFAULT NULL COMMENT '商品名称',
  `productDesc` VARCHAR(50) NULL DEFAULT NULL COMMENT '商品描述',
  `productUnit` VARCHAR(10) NULL DEFAULT NULL COMMENT '商品单位',
  `productCount` DECIMAL(20, 2) NULL DEFAULT NULL COMMENT '商品数量',
  `totalPrice` DECIMAL(20, 2) NULL DEFAULT NULL COMMENT '商品总额',
  `isPayment` INT(10) NULL DEFAULT NULL COMMENT '是否支付（1：未支付 2：已支付）',
  `createdBy` BIGINT(20) NULL DEFAULT NULL COMMENT '创建者（userId）',
  `creationDate` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
  `modifyBy` BIGINT(20) NULL DEFAULT NULL COMMENT '更新者（userId）',
  `modifyDate` DATETIME NULL DEFAULT NULL COMMENT '更新时间',
  `providerId` BIGINT(20) NULL DEFAULT NULL COMMENT '供应商ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `smbms_bill` VALUES (2, 'BILL2016_002', '香皂、肥皂、药皂', '日用品-皂类', '块', 1000.00, 10000.00, 2, 1, '2016-03-23 04:20:40', NULL, NULL, 13);
INSERT INTO `smbms_bill` VALUES (3, 'BILL2016_003', '大豆油', '食品-食用油', '斤', 300.00, 5890.00, 2, 1, '2014-12-14 13:02:03', NULL, NULL, 6);
INSERT INTO `smbms_bill` VALUES (4, 'BILL2016_004', '橄榄油', '食品-进口食用油', '斤', 200.00, 9800.00, 2, 1, '2013-10-10 03:12:13', NULL, NULL, 7);
INSERT INTO `smbms_bill` VALUES (5, 'BILL2016_005', '洗洁精', '日用品-厨房清洁', '瓶', 500.00, 7000.00, 2, 1, '2014-12-14 13:02:03', NULL, NULL, 9);
INSERT INTO `smbms_bill` VALUES (6, 'BILL2016_006', '美国大杏仁', '食品-坚果', '袋', 300.00, 5000.00, 2, 1, '2016-04-14 06:08:09', NULL, NULL, 4);
INSERT INTO `smbms_bill` VALUES (7, 'BILL2016_007', '沐浴液、精油', '日用品-沐浴类', '瓶', 500.00, 23000.00, 1, 1, '2016-07-22 10:10:22', NULL, NULL, 14);
INSERT INTO `smbms_bill` VALUES (8, 'BILL2016_008', '不锈钢盘碗', '日用品-厨房用具', '个', 600.00, 6000.00, 2, 1, '2016-04-14 05:12:13', NULL, NULL, 14);
INSERT INTO `smbms_bill` VALUES (9, 'BILL2016_009', '塑料杯', '日用品-杯子', '个', 350.00, 1750.00, 2, 1, '2016-02-04 11:40:20', NULL, NULL, 14);
INSERT INTO `smbms_bill` VALUES (10, 'BILL2016_010', '豆瓣酱', '食品-调料', '瓶', 200.00, 2000.00, 2, 1, '2013-10-29 05:07:03', NULL, NULL, 8);
INSERT INTO `smbms_bill` VALUES (11, 'BILL2016_011', '海之蓝', '饮料-国酒', '瓶', 50.00, 10000.00, 1, 1, '2016-04-14 16:16:00', NULL, NULL, 1);
INSERT INTO `smbms_bill` VALUES (12, 'BILL2016_012', '芝华士', '饮料-洋酒', '瓶', 20.00, 6000.00, 1, 1, '2016-09-09 17:00:00', NULL, NULL, 1);
INSERT INTO `smbms_bill` VALUES (13, 'BILL2016_013', '长城红葡萄酒', '饮料-红酒', '瓶', 60.00, 800.00, 2, 1, '2016-11-14 15:23:00', NULL, NULL, 1);
INSERT INTO `smbms_bill` VALUES (14, 'BILL2016_014', '泰国香米', '食品-大米', '斤', 400.00, 5000.00, 2, 1, '2016-10-09 15:20:00', NULL, NULL, 3);
INSERT INTO `smbms_bill` VALUES (15, 'BILL2016_015', '东北大米', '食品-大米', '斤', 600.00, 4000.00, 2, 1, '2016-11-14 14:00:00', NULL, NULL, 3);
INSERT INTO `smbms_bill` VALUES (16, 'BILL2016_016', '可口可乐', '饮料', '瓶', 2000.00, 6000.00, 2, 1, '2012-03-27 13:03:01', NULL, NULL, 2);
INSERT INTO `smbms_bill` VALUES (17, 'BILL2016_017', '脉动', '饮料', '瓶', 1500.00, 4500.00, 2, 1, '2016-05-10 12:00:00', NULL, NULL, 2);
INSERT INTO `smbms_bill` VALUES (18, 'BILL2016_018', '哇哈哈', '饮料', '瓶', 2000.00, 4000.00, 2, 1, '2015-11-24 15:12:03', NULL, NULL, 2);

DROP TABLE IF EXISTS `smbms_provider`;
CREATE TABLE `smbms_provider`  (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `proCode` VARCHAR(20) NULL DEFAULT NULL COMMENT '供应商编码',
  `proName` VARCHAR(20) NULL DEFAULT NULL COMMENT '供应商名称',
  `proDesc` VARCHAR(50) NULL DEFAULT NULL COMMENT '供应商详细描述',
  `proContact` VARCHAR(20) NULL DEFAULT NULL COMMENT '供应商联系人',
  `proPhone` VARCHAR(20) NULL DEFAULT NULL COMMENT '联系电话',
  `proAddress` VARCHAR(50) NULL DEFAULT NULL COMMENT '地址',
  `proFax` VARCHAR(20) NULL DEFAULT NULL COMMENT '传真',
  `createdBy` BIGINT(20) NULL DEFAULT NULL COMMENT '创建者（userId）',
  `creationDate` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
  `modifyDate` DATETIME NULL DEFAULT NULL COMMENT '更新时间',
  `modifyBy` BIGINT(20) NULL DEFAULT NULL COMMENT '更新者（userId）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `smbms_provider` VALUES (1, 'BJ_GYS001', '北京三木堂商贸有限公司', '长期合作伙伴，主营产品:茅台、五粮液、郎酒、酒鬼酒、泸州老窖、赖茅酒、法国红酒等', '张国强', '13566667777', '北京市丰台区育芳园北路', '010-58858787', 1, '2013-03-21 16:52:07', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (2, 'HB_GYS001', '石家庄帅益食品贸易有限公司', '长期合作伙伴，主营产品:饮料、水饮料、植物蛋白饮料、休闲食品、果汁饮料、功能饮料等', '王军', '13309094212', '河北省石家庄新华区', '0311-67738876', 1, '2016-04-13 04:20:40', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (3, 'GZ_GYS001', '深圳市泰香米业有限公司', '初次合作伙伴，主营产品：良记金轮米,龙轮香米等', '郑程瀚', '13402013312', '广东省深圳市福田区深南大道6006华丰大厦', '0755-67776212', 1, '2014-03-21 16:56:07', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (4, 'GZ_GYS002', '深圳市喜来客商贸有限公司', '长期合作伙伴，主营产品：坚果炒货.果脯蜜饯.天然花茶.营养豆豆.特色美食.进口食品.海味零食.肉脯肉', '林妮', '18599897645', '广东省深圳市福龙工业区B2栋3楼西', '0755-67772341', 1, '2013-03-22 16:52:07', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (5, 'JS_GYS001', '兴化佳美调味品厂', '长期合作伙伴，主营产品：天然香辛料、鸡精、复合调味料', '徐国洋', '13754444221', '江苏省兴化市林湖工业区', '0523-21299098', 1, '2015-11-22 16:52:07', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (6, 'BJ_GYS002', '北京纳福尔食用油有限公司', '长期合作伙伴，主营产品：山茶油、大豆油、花生油、橄榄油等', '马莺', '13422235678', '北京市朝阳区珠江帝景1号楼', '010-588634233', 1, '2012-03-21 17:52:07', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (7, 'BJ_GYS003', '北京国粮食用油有限公司', '初次合作伙伴，主营产品：花生油、大豆油、小磨油等', '王驰', '13344441135', '北京大兴青云店开发区', '010-588134111', 1, '2016-04-13 00:00:00', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (8, 'ZJ_GYS001', '慈溪市广和绿色食品厂', '长期合作伙伴，主营产品：豆瓣酱、黄豆酱、甜面酱，辣椒，大蒜等农产品', '薛圣丹', '18099953223', '浙江省宁波市慈溪周巷小安村', '0574-34449090', 1, '2013-11-21 06:02:07', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (9, 'GX_GYS001', '优百商贸有限公司', '长期合作伙伴，主营产品：日化产品', '李立国', '13323566543', '广西南宁市秀厢大道42-1号', '0771-98861134', 1, '2013-03-21 19:52:07', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (10, 'JS_GYS002', '南京火头军信息技术有限公司', '长期合作伙伴，主营产品：不锈钢厨具等', '陈女士', '13098992113', '江苏省南京市浦口区浦口大道1号新城总部大厦A座903室', '025-86223345', 1, '2013-03-25 16:52:07', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (11, 'GZ_GYS003', '广州市白云区美星五金制品厂', '长期合作伙伴，主营产品：海绵床垫、坐垫、靠垫、海绵枕头、头枕等', '梁天', '13562276775', '广州市白云区钟落潭镇福龙路20号', '020-85542231', 1, '2016-12-21 06:12:17', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (12, 'BJ_GYS004', '北京隆盛日化科技', '长期合作伙伴，主营产品：日化环保清洗剂，家居洗涤专卖、洗涤用品网、墙体除霉剂、墙面霉菌清除剂等', '孙欣', '13689865678', '北京市大兴区旧宫', '010-35576786', 1, '2014-11-21 12:51:11', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (13, 'SD_GYS001', '山东豪克华光联合发展有限公司', '长期合作伙伴，主营产品：洗衣皂、洗衣粉、洗衣液、洗洁精、消杀类、香皂等', '吴洪转', '13245468787', '山东济阳济北工业区仁和街21号', '0531-53362445', 1, '2015-01-28 10:52:07', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (14, 'JS_GYS003', '无锡喜源坤商行', '长期合作伙伴，主营产品：日化品批销', '周一清', '18567674532', '江苏无锡盛岸西路', '0510-32274422', 1, '2016-04-23 11:11:11', NULL, NULL);
INSERT INTO `smbms_provider` VALUES (15, 'ZJ_GYS002', '乐摆日用品厂', '长期合作伙伴，主营产品：各种中、高档塑料杯，塑料乐扣水杯（密封杯）、保鲜杯（保鲜盒）、广告杯、礼品杯', '王世杰', '13212331567', '浙江省金华市义乌市义东路', '0579-34452321', 1, '2016-08-22 10:01:30', NULL, NULL);


DROP TABLE IF EXISTS `smbms_role`;
CREATE TABLE `smbms_role`  (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `roleCode` VARCHAR(15) NULL DEFAULT NULL COMMENT '角色编码',
  `roleName` VARCHAR(15) NULL DEFAULT NULL COMMENT '角色名称',
  `createdBy` BIGINT(20) NULL DEFAULT NULL COMMENT '创建者',
  `creationDate` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
  `modifyBy` BIGINT(20) NULL DEFAULT NULL COMMENT '修改者',
  `modifyDate` DATETIME NULL DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8;


INSERT INTO `smbms_role` VALUES (1, 'SMBMS_ADMIN', '系统管理员', 1, '2016-04-13 00:00:00', NULL, NULL);
INSERT INTO `smbms_role` VALUES (2, 'SMBMS_MANAGER', '经理', 1, '2016-04-13 00:00:00', NULL, NULL);
INSERT INTO `smbms_role` VALUES (3, 'SMBMS_EMPLOYEE', '普通员工', 1, '2016-04-13 00:00:00', NULL, NULL);


DROP TABLE IF EXISTS `smbms_user`;
CREATE TABLE `smbms_user`  (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `userCode` VARCHAR(15) NULL DEFAULT NULL COMMENT '用户编码',
  `userName` VARCHAR(15) NULL DEFAULT NULL COMMENT '用户名称',
  `userPassword` VARCHAR(15) NULL DEFAULT NULL COMMENT '用户密码',
  `gender` INT(10) NULL DEFAULT NULL COMMENT '性别（1:女、 2:男）',
  `birthday` DATE NULL DEFAULT NULL COMMENT '出生日期',
  `phone` VARCHAR(15) NULL DEFAULT NULL COMMENT '手机',
  `address` VARCHAR(30) NULL DEFAULT NULL COMMENT '地址',
  `userRole` BIGINT(20) NULL DEFAULT NULL COMMENT '用户角色（取自角色表-角色id）',
  `createdBy` BIGINT(20) NULL DEFAULT NULL COMMENT '创建者（userId）',
  `creationDate` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
  `modifyBy` BIGINT(20) NULL DEFAULT NULL COMMENT '更新者（userId）',
  `modifyDate` DATETIME NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `smbms_user` VALUES (1, 'admin', '111', '111111', 1, '2021-04-22', '13688889999', '北京市海淀区成府路207号', 3, 1, '2013-03-21 16:52:07', 1, '2021-04-22 17:43:02');
INSERT INTO `smbms_user` VALUES (2, 'liming', '李明', '0000000', 2, '1983-12-10', '13688884457', '北京市东城区前门东大街9号', 2, 1, '2014-12-31 19:52:09', NULL, NULL);
INSERT INTO `smbms_user` VALUES (5, 'hanlubiao', '韩路彪', '0000000', 2, '1984-06-05', '18567542321', '北京市朝阳区北辰中心12号', 2, 1, '2014-12-31 19:52:09', NULL, NULL);
INSERT INTO `smbms_user` VALUES (6, 'zhanghua', '张华', '0000000', 1, '1983-06-15', '13544561111', '北京市海淀区学院路61号', 3, 1, '2013-02-11 10:51:17', NULL, NULL);
INSERT INTO `smbms_user` VALUES (7, 'wangyang', '王洋', '0000000', 2, '1982-12-31', '13444561124', '北京市海淀区西二旗辉煌国际16层', 3, 1, '2014-06-11 19:09:07', NULL, NULL);
INSERT INTO `smbms_user` VALUES (8, 'zhaoyan', '赵燕', '0000000', 2, '2021-04-22', '18098764545', '北京市海淀区回龙观小区10号楼', 2, 1, '2016-04-21 13:54:07', 1, '2021-04-22 17:56:11');
INSERT INTO `smbms_user` VALUES (10, 'sunlei', '孙磊', '0000000', 2, '1981-01-04', '13387676765', '北京市朝阳区管庄新月小区12楼', 3, 1, '2015-05-06 10:52:07', NULL, NULL);
INSERT INTO `smbms_user` VALUES (11, 'sunxing', '孙兴', '0000000', 2, '1978-03-12', '13367890900', '北京市朝阳区建国门南大街10号', 3, 1, '2016-11-09 16:51:17', NULL, NULL);
INSERT INTO `smbms_user` VALUES (12, 'zhangchen', '张晨', '0000000', 1, '1986-03-28', '18098765434', '朝阳区管庄路口北柏林爱乐三期13号楼', 3, 1, '2016-08-09 05:52:37', 1, '2016-04-14 14:15:36');
INSERT INTO `smbms_user` VALUES (13, 'dengchao', '邓超', '0000000', 2, '1981-11-04', '13689674534', '北京市海淀区北航家属院10号楼', 3, 1, '2016-07-11 08:02:47', NULL, NULL);
INSERT INTO `smbms_user` VALUES (15, 'zhaomin', '赵敏', '0000000', 1, '1987-12-04', '18099897657', '北京市昌平区天通苑3区12号楼', 2, 1, '2015-09-12 12:02:12', NULL, NULL);
INSERT INTO `smbms_user` VALUES (18, '111', '111', '111111', 1, '2021-04-22', '15072151323', '111', 3, 1, '2021-04-22 18:13:07', NULL, NULL);

项目搭建准备工作
搭建一个maven web项目

配置tomcat

测试项目是否可以跑起来

导入项目中所需jar包

<dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>servlet-api</artifactId>
      <version>2.5</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>jsp-api</artifactId>
      <version>2.1</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet.jsp.jstl</groupId>
      <artifactId>jstl-api</artifactId>
      <version>1.2</version>
    </dependency>
    <dependency>
      <groupId>taglibs</groupId>
      <artifactId>standard</artifactId>
      <version>1.1.2</version>
    </dependency>

创建项目包结构

编写实体类

ORM映射：表-类映射

编写基础公共类

数据库配置文件

package com.smbms.dao;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class BaseDao {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    static {
        try{
            InputStream in = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver= properties.getProperty("driver");
            url= properties.getProperty("url");
            username= properties.getProperty("username");
            password= properties.getProperty("password");
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection()throws SQLException{
        return DriverManager.getConnection(url,username,password);
    }
    //编写查询共方法
    public static ResultSet execute(Connection connection,String sql,Object[] params,ResultSet resultSet,PreparedStatement preparedStatement)throws SQLException{
        //预编译的sql，在后面直接执行就可以了
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            //setObject,占位符从1开始，但是我们的数组是从0开始！
            preparedStatement.setObject(i+i,params[i]);
        }

        resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    //编写增删改公共方法
    public static int execute(Connection connection,String sql,Object[] params,PreparedStatement preparedStatement)throws SQLException{
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            preparedStatement.setObject(i+1,params[i]);
        }
        int updateRows = preparedStatement.executeUpdate();
        return updateRows;
    }
    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if(rs!=null) {
            rs.close();
        }
        if(st!=null){
            st.close();
        }
        if (conn!=null){
            conn.close();
        }
    }

}

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

编写数据库的公共类
编写字符编码过滤器
导入静态资源

登录功能实现

1.编写前端页面login.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head lang="en">
    <meta charset="UTF-8">
    <title>系统登录 - 超市订单管理系统</title>
    <link type="text/css" rel="stylesheet" href="css/style.css" />
    <script type="text/javascript">
        /* if(top.location!=self.location){
              top.location=self.location;
         } */
    </script>
</head>
<body class="login_bg">

<%
    Object attribute = request.getAttribute("error");
%>

<section class="loginBox">
    <header class="loginHeader">
        <h1>超市订单管理系统</h1>
    </header>
    <section class="loginCont">
        <form class="loginForm" action="${pageContext.request.contextPath}/login.do"  name="actionForm" id="actionForm"  method="post" >
            <div class="info"><%=attribute%></div>
            <div class="inputbox">
                <label for="userCode">用户名：</label>
                <input type="text" class="input-text" id="userCode" name="userCode" placeholder="请输入用户名" required/>
            </div>
            <div class="inputbox">
                <label for="userPassword">密码：</label>
                <input type="password" id="userPassword" name="userPassword" placeholder="请输入密码" required/>
            </div>
            <div class="subBtn">
                <input type="submit" value="登录"/>
                <input type="reset" value="重置"/>
            </div>
        </form>
    </section>
</section>
</body>
</html>

2.设置页面web.xml

<!--    设置欢迎界面-->
    <welcome-file-list>
        <welcome-file>login.html</welcome-file>
    </welcome-file-list>

3.编写dao层登录用户的接口

package com.smbms.dao.user;

import com.smbms.pojo.User;

import java.sql.Connection;
import java.sql.SQLException;

public interface UserDao {
    //得到需要登录的用户
    public User getLoginUser(Connection connection, String usercode) throws SQLException;
}

4.编写dao接口的实现类

package com.smbms.dao.user;

import com.smbms.dao.BaseDao;
import com.smbms.pojo.User;

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

public class UserDaoImpl implements UserDao{
    @Override
    public User getLoginUser(Connection con, String userCode) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        if (con != null) {
            String sql = "select id, userCode, userName, userPassword, gender, birthday, phone, address, userRole, createdBy, creationDate, modifyBy, modifyDate from smbms_user where userCode=?";
            Object[] params = {userCode};
            rs = BaseDao.execute(con, rs, ps, sql, params);
            if (rs.next()) {
                user = new User();
                user.setId(rs.getLong("id"));
                user.setUsercode(rs.getString("userCode"));
                user.setUsername(rs.getString("userName"));
                user.setUserpassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserrole(rs.getInt("userRole"));
                user.setCreatedby(rs.getLong("createdBy"));
                user.setModifyby(rs.getLong("modifyBy"));
                user.setCreationdate(rs.getTimestamp("creationDate"));
                user.setModifydate(rs.getTimestamp("modifyDate"));
            }
            //关闭
            BaseDao.release(con,ps, rs);
        }
        return user;
    }
}

5.业务层接口

package com.smbms.servlet.user;

import com.smbms.pojo.User;

import java.sql.SQLException;

public interface UserService {
    public User Login(String userCode, String userPassword) throws SQLException;
}

6.业务层实现

package com.smbms.servlet.user;

import com.smbms.dao.BaseDao;
import com.smbms.dao.user.UserDao;
import com.smbms.dao.user.UserDaoImpl;
import com.smbms.pojo.User;
import jdk.nashorn.internal.objects.annotations.Constructor;

import java.sql.Connection;
import java.sql.SQLException;

public class UserServiceImp implements UserService{
    //业务层要调用dao层，所以这里要引入dao层
    private UserDao userDao;

    public UserServiceImp() {
        userDao = new UserDaoImpl();
    }
    @Override
    public User Login(String userCode, String userPassword) throws SQLException {

        Connection con = null;
        User user = null;
        try {
            con = BaseDao.getConnection();
            user = userDao.getLoginUser(con, userCode);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.release(con,null,null);
        }
        return user;

    }
}

7.编写servlet

package com.smbms.servlet;

import com.smbms.pojo.User;
import com.smbms.service.user.UserService;
import com.smbms.service.user.UserServiceImp;
import com.smbms.util.Constants;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;

public class LoginServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("LoginServlet开始");
        //用户前端输入的用户名和密码
        String userCode = req.getParameter("userCode");
        String userPassword = req.getParameter("userPassword");
        //调用业务层
        UserService userService = new UserServiceImp();
        User user = null;
        try {
            user = userService.Login(userCode, userPassword);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (user != null) {
            //查有此人
            //将用户的信息放到session中
            req.getSession().setAttribute(Constants.USER_SESSION, user);
            //跳转到主页
            resp.sendRedirect("jsp/frame.jsp");
        } else {
            //查无此人
            //转发回登录页面，并提示它用户名或者密码错误
            req.setAttribute("error", "用户名或者密码错误");
            req.getRequestDispatcher("login.jsp").forward(req, resp);
        }
    }

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

package com.smbms.util;

public class Constants {
    public final static String USER_SESSION = "usersession";
}

9.注册servlet

10.登录功能优化

注销功能
思路：移除session，返回登录页面

package com.smbms.servlet;

import com.smbms.util.Constants;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class LogoutServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //移除用户的Constants.USER_SESSION
        req.getSession().removeAttribute(Constants.USER_SESSION);
        //返回登录页面
        resp.sendRedirect(req.getContextPath()+"/login.jsp");
    }

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

注册servlet

<servlet>
        <servlet-name>logoutServlet</servlet-name>
        <servlet-class>com.smbms.servlet.LogoutServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>logoutServlet</servlet-name>
        <url-pattern>/jsp/logout.do</url-pattern>
    </servlet-mapping>

11.登录拦截优化

编写过滤器

package com.smbms.filter;

import com.smbms.pojo.User;
import com.smbms.util.Constants;

import javax.servlet.*;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class SysFilter implements Filter {
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {

    }

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        HttpServletRequest request = (HttpServletRequest) servletRequest;
        HttpServletResponse response = (HttpServletResponse) servletResponse;
        //过滤器从Session中获取用户
        User user = (User) request.getSession().getAttribute(Constants.USER_SESSION);

        if(user == null){//user已经被移除或注销或者未登录
            response.sendRedirect("/error.jsp");
        }else{
            filterChain.doFilter(servletRequest,servletResponse);
        }
    }

    @Override
    public void destroy() {

    }
}

注册filter

<filter>
        <filter-name>sysFilter</filter-name>
        <filter-class>com.smbms.filter.SysFilter</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>sysFilter</filter-name>
        <url-pattern>/jsp/*</url-pattern>
    </filter-mapping>

密码修改

1.导入前端素材

<li><a href="${pageContext.request.contextPath }/jsp/pwdmodify.jsp">密码修改</a></li>

2.框架思路

UserDao接口

//修改当前用户密码
    public int updatePdw(Connection connection, int id, String password) throws SQLException;

UserDao接口实现

//修改当前用户密码
    @Override
    public int updatePdw(Connection connection, int id, String password) throws SQLException {
        PreparedStatement preparedStatement = null;
        int execute=0;
        if(connection!=null){
            String sql= "update smbms_user set userPassword = ? where id = ?";
            Object [] param={password,id};
            execute = BaseDao.execute(connection,sql,param,preparedStatement);
            BaseDao.release(null,preparedStatement,null);
        }
        return execute;
    }

UserService层

//根据用户id修改密码
    public boolean updatePwd(int id,String password);

UserService层实现

//根据用户id修改密码
    @Override
    public boolean updatePwd(int id, String password) {
        boolean flag= false;
        Connection connection=null;
        try {
            connection = BaseDao.getConnection();
            if(userDao.updatePdw(connection,id,password)>0){
                flag=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                BaseDao.release(connection,null,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

Servlet记得实现复用，需要提取出方法!

package com.smbms.servlet;

import com.mysql.jdbc.StringUtils;
import com.smbms.pojo.User;
import com.smbms.service.user.UserService;
import com.smbms.service.user.UserServiceImp;
import com.smbms.util.Constants;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class UserServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String method = req.getParameter("method");
        if (method.equals("savepwd") && method != null){
            this.updatePwd(req, resp);
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        super.doPost(req, resp);
    }
    public void updatePwd(HttpServletRequest req, HttpServletResponse resp){
        //从Session里面拿id
        User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);
        String newpassword = req.getParameter("newpassword");
        boolean flag=false;
        if(user!=null&&!StringUtils.isNullOrEmpty(newpassword)){
            UserService userService = new UserServiceImp();
            flag = userService.updatePwd(user.getId(), newpassword);
            if(flag){
                req.setAttribute("message", "修改密码成功，请退出，使用新密码登陆");
                //密码修改成功，移除当前Session
                req.getSession().removeAttribute(Constants.USER_SESSION);
            }else {
                System.out.println("密码修改失败");
                req.setAttribute("message", "密码修改失败");
                //密码修改成功，移除当前Session
            }
        }else {
            System.out.println("新密码有问题");
            req.setAttribute("message","新密码有问题");
        }
        try{
            resp.sendRedirect("pwdmodify.jsp");
        }catch (IOException e){
            e.printStackTrace();
        }
    }
}

注册servlet

 <servlet>
        <servlet-name>userServlet</servlet-name>
        <servlet-class>com.smbms.servlet.UserServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>userServlet</servlet-name>
        <url-pattern>/jsp/user.do</url-pattern>
    </servlet-mapping>

导入jar包

<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.75</version>
</dependency>

测试

用户管理

思路

编写工具类

package com.smbms.util;

public class PageSupport {
    //当前页码-来自于用户输入
    private int currentPageNo=1;

    //总数量（表）
    private int totalCount=0;

    //页面容量
    private int pageSize=0;

    //总页数-totalCount/pageSize(+1)
    private int totalPageCount = 1;

    public int getCurrentPageNo() {
        return currentPageNo;
    }

    public void setCurrentPageNo(int currentPageNo) {
        if(currentPageNo>0){
            this.currentPageNo = currentPageNo;
        }
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        if(totalCount>0) {
            this.totalCount = totalCount;
            //设置总页数
            this.setTotalPageCountByRs();
        }
    }

    public int getPageSize() {
        return pageSize;
    }

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

    public int getTotalPageCount() {
        return totalPageCount;
    }

    public void setTotalPageCount(int totalPageCount) {
        this.totalPageCount = totalPageCount;
    }

    public void setTotalPageCountByRs(){
        if (this.totalCount%this.pageSize==0){
            this.totalPageCount=this.totalCount/this.pageSize;
        }else if(this.totalCount%this.pageSize>0){
            this.totalPageCount=this.totalCount/this.pageSize+1;
        }else {
            this.totalPageCount=0;
        }
    }
}

UserDao

//根据用户名或角色查询用户总数
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException;

UserDaoImpl

//根据用户名或角色查询用户数量【最难理解的SQL】
    @Override
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        int count = 0;

        if (connection != null) {
            StringBuffer sql = new StringBuffer();
            sql.append("select count(1) as count from smbms_user u, smbms_role r where u.userRole = r.id ");
            ArrayList<Object> list = new ArrayList<>();//存放我们的参数

            if (!StringUtils.isNullOrEmpty(username)) {
                sql.append("and u.userName like ?");
                list.add("%" + username + "%");
            }

            if (userRole > 0) {
                sql.append("and u.userRole = ?");
                list.add(userRole);
            }

            //怎么把list转换为数组
            Object[] params = list.toArray();
            //输出最后完整的SQL语句
            System.out.println("UserDaoImpl->getUserCount：" + sql.toString());
            rs = BaseDao.execute(connection, sql.toString(), params, rs, preparedStatement);
            if (rs.next()) {
                count = rs.getInt("count");//从结果集中获取最终数量
            }

            BaseDao.release(null, preparedStatement, rs);
        }
        return count;
    }

Userservice

//查询记录数
    public int getUserCount(String username, int userRole) throws SQLException;

UserService实现

//查询记录数
    @Override
    public int getUserCount(String username, int userRole) throws SQLException {
        Connection connection =null;
        int count =0;
        try {
            connection=BaseDao.getConnection();
            count=userDao.getUserCount(connection,username,userRole);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            BaseDao.release(connection,null,null);
        }
        return count;
    }

获取用户列表

userdao

//通过条件查询-userList
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception;

userdao实现

//通过条件查询-userList
    @Override
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<>();
        if (connection != null) {
            StringBuffer sql = new StringBuffer();
            sql.append("select u.*, r.roleName as userRoleName from smbms_user u, smbms_role r where u.userRole = r.id ");
            List<Object> list = new ArrayList<>();
            if (!StringUtils.isNullOrEmpty(userName)) {
                sql.append("and u.userName like ?");
                list.add("%" + userName + "%");
            }
            if (userRole > 0) {
                sql.append("and u.userRole = ?");
                list.add(userRole);
            }
            //在数据库中，分页使用    limit   startIndex, pageSize;
            //当前页   （当前页-1）* 页面大小
            //0,5   1 0     01234
            //5,5   2 5     56789
            //10,5  3,10
            sql.append("order by creationDate DESC limit ?, ?");
            currentPageNo = (currentPageNo - 1) * pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            System.out.println("sql--->" + sql.toString());
            rs = BaseDao.execute(connection, sql.toString(), params, rs, preparedStatement);
            while (rs.next()) {
                User _user = new User();
                _user.setId(rs.getLong("id"));
                _user.setUsercode(rs.getString("userCode"));
                _user.setUsername(rs.getString("userName"));
                _user.setGender(rs.getInt("gender"));
                _user.setBirthday(rs.getDate("birthday"));
                _user.setPhone(rs.getString("phone"));
                _user.setUserrole(rs.getInt("userRole"));
                _user.setPhone(rs.getString("userRoleName"));
                userList.add(_user);
            }
            BaseDao.release(null, preparedStatement, rs);
        }
        return userList;
    }

userService

//根据条件查询用户列表
    public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);

userService实现

 //根据条件查询用户列表
    @Override
    public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
        Connection connection = null;
        List<User> userList = null;
        System.out.println("queryUserName ----> " + queryUserName);
        System.out.println("queryUserRole ----> " + queryUserRole);
        System.out.println("currentPageNo ----> " + currentPageNo);
        System.out.println("pageSize ----> " + pageSize);

        try {
            connection = BaseDao.getConnection();
            userList = userDao.getUserList(connection, queryUserName, queryUserRole, currentPageNo, pageSize);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                BaseDao.release(connection, null, null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return userList;
    }

获取用户列表

RoleDao

package com.smbms.dao.role;

import com.smbms.pojo.Role;

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

public interface RoleDao {
    //获取角色列表
    public abstract List<Role> getRoleList(Connection conn)throws SQLException;
}

RoleDaoImpl

package com.smbms.dao.role;

import com.smbms.dao.BaseDao;
import com.smbms.pojo.Role;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RoleDaoImpl implements RoleDao{
    //获取角色列表
    @Override
    public List<Role> getRoleList(Connection conn) throws SQLException {
        PreparedStatement preparedStatement =null;
        ResultSet rs=null;
        ArrayList<Role> roles = new ArrayList<>();
        if(conn!=null){
            String sql ="select * from smbms_role";
            Object[] params ={};
            rs = BaseDao.execute(conn,sql,params,rs,preparedStatement);
            while (rs.next()){
                Role role = new Role();
                role.setId(rs.getLong("id"));
                role.setRolename(rs.getString("roleName"));
                role.setRolecode(rs.getString("roleCode"));
                roles.add(role);
            }
        }
        BaseDao.release(null,preparedStatement,rs);
        return roles;
    }
}

RoleService

package com.smbms.service.role;

import com.smbms.pojo.Role;

import java.util.List;

public interface RoleService {
    //获取角色列表
    public abstract List<Role> getRoleList();
}

RoleServiceImpl

package com.smbms.service.role;

import com.smbms.dao.BaseDao;
import com.smbms.dao.role.RoleDao;
import com.smbms.dao.role.RoleDaoImpl;
import com.smbms.pojo.Role;

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

public class RoleServiceImpl implements RoleService{
    //业务层调用持久层
    private RoleDao roleDao = null;
    public RoleServiceImpl(){
        this.roleDao = new RoleDaoImpl();
    }
    //获取角色列表
    @Override
    public List<Role> getRoleList() {
        Connection connection = null;
        List<Role> roleList= null;
        try {
            //获取数据库连接
            connection = BaseDao.getConnection();
            roleList = roleDao.getRoleList(connection);
        } catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                BaseDao.release(connection,null,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return roleList;
        }
    }
}

UserServlet

1.获取用户前端的数据（查询)
2判断请求是否需要执行，看参数的值判断
3.为了实现分页，需要计算出当前页面和总页面，页面大小…
4.用户列表展示
5.返回前端
I

//查询用户列表的方法
    public void query(HttpServletRequest req, HttpServletResponse resp){
        //查询用户列表
        //从前端获取数据
        String queryUserName = req.getParameter("queryname");
        String temp = req.getParameter("queryUserRole");//值为0 、1、2、3
        String pageIndex = req.getParameter("pageIndex");
        int queryUserRole = 0;

        //获取用户列表
        UserServiceImp userService = new UserServiceImp();
        List<User> userList = null;

        int currentPageNo = 1;
        int pageSize = 5;

        if(queryUserName == null){
            queryUserName = "";
        }
        if(temp!=null && !temp.equals("")){
            queryUserRole = Integer.parseInt(temp);
        }
        if(pageIndex!=null){
            currentPageNo = Integer.parseInt(pageIndex);
        }
        //获取用户总数 分页：上一页 下一页
        int totalCount = 0;
        try {
            totalCount = userService.getUserCount(queryUserName, queryUserRole);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //总页数支持
        PageSupport pageSupport = new PageSupport();
        System.out.println("当前页："+currentPageNo);
        pageSupport.setCurrentPageNo(currentPageNo);
        pageSupport.setPageSize(pageSize);
        System.out.println("获取用户总数"+totalCount);
        pageSupport.setTotalCount(totalCount);
        //总共的页数
        int totalPageCount = pageSupport.getTotalPageCount();
        //控制首页和尾页
        //如果页数小于1，就显示第一页  页数大于 最后一页就 显示最后一页
        if(currentPageNo<1){
            currentPageNo =1;
        }else if(currentPageNo>totalPageCount){
            currentPageNo = totalPageCount;
        }
        System.out.println("返回UserList的数据测试"+queryUserName+":"+queryUserRole+":"+currentPageNo+":"+pageSize);
        //获取用户列表展示
        userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize);
        //将数据传给前端
        System.out.println(userList);
//        for (User user : userList) {
//            System.out.println(user.toString());
//        }
        req.setAttribute("userList",userList);

        RoleServiceImpl roleService = new RoleServiceImpl();
        //所有角色
        List<Role> roleList = roleService.getRoleList();
        req.setAttribute("roleList",roleList);
        req.setAttribute("totalCount",totalCount);
        req.setAttribute("currentPageNo",currentPageNo);
        req.setAttribute("totalPageCount",totalPageCount);
        req.setAttribute("queryUserName",queryUserName);
        req.setAttribute("queryUserRole",queryUserRole);

        //返回至前端
        try {
            req.getRequestDispatcher("userlist.jsp").forward(req,resp);
        } catch (ServletException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

测试