JavaWeb入门到入土_Day05

于 2022-10-17 15:21:45 发布
阅读量86 收藏
点赞数
分类专栏： JavaWeb 文章标签： 数据库 mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ReiZhoo/article/details/127169631
版权

Javaweb

15. SMBMS（超市订单管理系统）

SMBMS

数据库

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

架构设计图

架构设计图

15.1 项目搭建

  1. 搭建一个maven web项目
  2. 配置Tomcat
  3. 测试项目是否可以跑起来
  4. 导入项目中所需jar包
    • servlet-api
    • jsp-api
    • mysql-connector-java
    • jstl-api
    • standard
  5. 创建项目包结构
    • pojo实体类
    • dao数据库操作
    • service服务层
    • servlet接口对象
    • filter过滤器
    • util工具类
  6. 编写实体类
    • ORM映射：表-类映射
  7. 编写基础公共类
    • 数据库配置文件 db.properties
      driver:com.mysql.jdbc.Driver
url:jdbc:mysql://localhost:3306/smbms?useSSL=true&useUnicode=true&characterEncoding=utf-8
username=root
password=123456
    • 编写数据库的公共类 BaseDao.java
    • 编写字符编码过滤器 CharacterEncodingFilter.java
  8. 导入静态资源

pom.xml

  <dependencies>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>servlet-api</artifactId>
      <version>2.5</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>javax.servlet.jsp-api</artifactId>
      <version>2.3.3</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>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
  </dependencies>

BaseDao.java

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

// 操作数据库的公共类
public class BaseDao {
    private static String driver;
    private static String url;
    private static String userName;
    private static String password;

	// 静态代码块，类加载的时候就初始化了
    static {
        Properties properties = new Properties();
        // 通过类加载器读取对应的资源
        InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
        try {
            properties.load(is);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            userName = properties.getProperty("username");
            password = properties.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

	// 获取数据库的链接
    public static Connection getConnection() {
        Connection con = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, userName, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

	// 编写查询公共方法
    public static ResultSet execute(Connection con, PreparedStatement ps, ResultSet rs, String sql, Object[] params) throws SQLException {
        ps = con.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            //setObject占位符下标从1开始，而数组从0开始
            ps.setObject(i + 1, params[i]);
        }
        rs = ps.executeQuery();
        return rs;
    }

	// 编写增删改公共方法
    public static int execute(Connection con, PreparedStatement ps, String sql, Object[] params) throws SQLException {
    	// 预编译的sql，在后面直接执行就可以了
        ps = con.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            //setObject占位符下标从1开始，而数组从0开始
            ps.setObject(i + 1, params[i]);
        }
        return ps.executeUpdate();
    }
    
	// 释放资源
    public static boolean closeResource(Connection con, PreparedStatement ps, ResultSet rs) {
        boolean closeFlag = true;
        if (rs != null) {
            try {
                rs.close();
                rs = null;   // GC回收
            } catch (SQLException e) {
                e.printStackTrace();
                closeFlag = false;
            }
        }
        if (ps != null) {
            try {
                ps.close();
                ps = null;   // GC回收
            } catch (SQLException e) {
                e.printStackTrace();
                closeFlag = false;
            }
        }
        if (con != null) {
            try {
                con.close();
                con = null;   // GC回收
            } catch (SQLException e) {
                e.printStackTrace();
                closeFlag = false;
            }
        }
        return closeFlag;
    }

CharacterEncodingFilter.java

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import java.io.IOException;

public class CharacterEncodingFilter implements Filter {
    public void init(FilterConfig filterConfig) throws ServletException {}

    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        chain.doFilter(request, response);
    }

    public void destroy() {}
}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
                      http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0"
         metadata-complete="true">

    <filter>
        <filter-name>characterEncoding</filter-name>
        <filter-class>com.wang.filter.CharacterEncodingFilter</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>characterEncoding</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>
</web-app>

15.2 登录功能实现

登录流程

  1. 编写前端页面
  2. 设置登录页面web.xml
    <welcome-file-list>
   <welcome-file>login.jsp</welcome-file>
</welcome-file-list>
  3. dao层登录用户的接口
    import com.wang.pojo.User;

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

// 得到要登录的用户
public interface UserDao {
    User getLoginUser(Connection con, String userCode) throws SQLException;
}
  4. dao层接口的实现类
    import com.wang.dao.BaseDao;
import com.wang.dao.user.UserDao;
import com.wang.pojo.User;

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

public class UserDaoImpl implements UserDao {
    public User getLoginUser(Connection con, String userCode) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        if (con != null) {
            String sql = "select * from smbms_user where userCode = ?";
            Object[] params = {userCode};
            rs = BaseDao.execute(con, ps, rs, sql, params);
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("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.getInt("createdBy"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setCreationDate(rs.getTimestamp("creationDate"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            //关闭
            BaseDao.closeResource(null, ps, rs);
        }
        return user;
    }
}
  5. 业务层接口
    import com.wang.pojo.User;

public interface UserService {
    // 用户登录
    public User login(String userCode, String password);
}
  6. 业务层实现类
    import com.wang.dao.BaseDao;
import com.wang.dao.user.UserDao;
import com.wang.dao.user.impl.UserDaoImpl;
import com.wang.pojo.User;
import com.wang.service.user.UserService;

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

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

    public UserServiceImpl() {
        userDao = new UserDaoImpl();
    }

    public User login(String userCode, String password) {
        Connection con = null;
        User user = null;
        try {
            con = BaseDao.getConnection();
            // 通过业务层调用对应的具体的数据库操作
            user = userDao.getLoginUser(con, userCode);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeResource(con, null, null);
        }
        return user;
    }
}
  7. 编写servlet
    import com.wang.pojo.User;
import com.wang.service.user.UserService;
import com.wang.service.user.impl.UserServiceImpl;
import com.wang.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;

// Servlet：控制层调用业务层代码
public class LoginServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("LoginServlet---start....");
        // 获取用户前端输入的用户名和密码
        String userCode = req.getParameter("userCode");
        String userPassword = req.getParameter("userPassword");
        // 调用业务层和数据库中的密码进行对比
        UserService userService = new UserServiceImpl();
        User user = userService.login(userCode, userPassword);   // 这里已经把登陆的用户查出来了
        if (user != null) {   // 查有此人，将用户的信息放到session中
        	// 将用户的信息放到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 {
        this.doGet(req, resp);
    }
}
  8. 注册Servlet
     <servlet>
    <servlet-name>LoginServlet</servlet-name>
    <servlet-class>com.wang.servlet.LoginServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>LoginServlet</servlet-name>
    <url-pattern>/login.do</url-pattern>
</servlet-mapping>
  9. 测试访问

15.3 注销及权限过滤

注销：移除session，并返回登录界面
LogoutServlet.java

import com.wang.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 {
        //移除session
        req.getSession().removeAttribute(Constants.USER_SESSION);
        //跳转登录页面
        resp.sendRedirect(req.getContextPath() + "/login.jsp");
    }

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

注册退出接口

    <servlet>
        <servlet-name>LogoutServlet</servlet-name>
        <servlet-class>com.wang.servlet.LogoutServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>LogoutServlet</servlet-name>
        <url-pattern>/user/logout</url-pattern>
    </servlet-mapping>

权限过滤：
SysFilter.java

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

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

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

    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        HttpServletRequest req = (HttpServletRequest) request;
        HttpServletResponse resp = (HttpServletResponse) response;

        User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);
        if (user == null) {
            //被移除 或注销 或没有登录
            resp.sendRedirect("/smbms/error.jsp");
            return;
        }
        chain.doFilter(req, resp);
    }

    public void destroy() {}
}

注册权限过滤器接口

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

测试验证功能实现

15.4 密码修改实现

  1. 导入前端素材pwdmodify.jsp
    <li><a href="${pageContext.request.contextPath }/jsp/pwdmodify.jsp">密码修改</a></li>
  2. 从底层向上写项目（数据库—Dao层—Service—Servlet—前端JSP）
  3. UserDao接口
    // 修改密码
int updatePwd(Connection con, int id, String password) throws SQLException;
  4. UserDao接口实现
    public int updatePwd(Connection con, int id, String password) throws SQLException {
	int updateCount = 0;
	if (con != null) {
		PreparedStatement ps = null;
		String sql = "update smbms_user set userPassword = ? where id = ?";
		Object[] params = {password, id};
		updateCount = BaseDao.execute(con, ps, sql, params);
		BaseDao.closeResource(null, ps, null);
	}
	return updateCount;
}
  5. UserService接口
    // 修改密码
boolean updatePwd(int id, String password);
  6. UserService接口实现
    public boolean updatePwd(int id, String password) {
	Connection con = null;
	boolean updateFlag = false;
	try {
		con = BaseDao.getConnection();
		int updateCount = userDao.updatePwd(con, id, password);
		if (updateCount > 0) {
			updateFlag = true;
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		BaseDao.closeResource(con, null, null);
	}
    return updateFlag;         
}
  7. 编写servlet
    import com.mysql.jdbc.StringUtils;
import com.wang.pojo.User;
import com.wang.service.user.UserService;
import com.wang.service.user.impl.UserServiceImpl;
import com.wang.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;

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

	@Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doGet(req, resp);
   }
   
    protected void updatePwd(HttpServletRequest req, HttpServletResponse resp) throws Exception {
   	 	// 从session中获取用户id
    	Object obj = req.getSession().getAttribute(Constants.USER_SESSION);
        // 获取入参中的新密码
        String newPassWord = req.getParameter("newpassword");
        if (obj == null || StringUtils.isNullOrEmpty(newPassWord)) {
            req.setAttribute("message", "新密码不可为空~");
       } else {
            UserService userService = new UserServiceImpl();
            boolean flag = userService.updatePwd(((User) obj).getId(), newPassWord);
            if (flag) {
                req.setAttribute("message", "密码修改成功，请退出，使用新密码登录~");
                //修改成功，移除session
                req.getSession().removeAttribute(Constants.USER_SESSION);
           } else {
                req.setAttribute("message", "密码修改失败~");
           }
       }
        req.getRequestDispatcher("pwdmodify.jsp").forward(req, resp);
   }
}
  8. 注册Servlet
    <servlet>
    <servlet-name>UserServlet</servlet-name>
    <servlet-class>com.wang.servlet.UserServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>UserServlet</servlet-name>
    <url-pattern>/jsp/user.do</url-pattern>
</servlet-mapping>
  9. 测试验证功能

15.5 Ajax验证旧密码实现

  1. 导入fastjson依赖包
    <dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>fastjson</artifactId>
  <version>1.2.79</version>
</dependency>
  2. UserServlet中添加方法
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    String method = req.getParameter("method");
    if (method != null) {
        if ("savepwd".equals(method)) {
            updatePwd(req, resp);
        } else if ("checkoldpwd".equals(method)) {
            checkOldPwd(req, resp);
        }
    }
}

// 验证旧密码
protected void checkOldPwd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    // 从session中获取旧密码
    Object obj = req.getSession().getAttribute(Constants.USER_SESSION);
    // 获取请求中的旧密码
    String oldPassWord = req.getParameter("oldpassword");
    Map<String, String> resultMap = new HashMap<String, String>();
    if (obj == null) {
        // session过期
        resultMap.put("result", "sessionerror");
    } else if (StringUtils.isNullOrEmpty(oldPassWord)) {
        // 旧密码输入为空
        resultMap.put("result", "error");
    } else {
        if (oldPassWord.equals(((User) obj).getUserPassword())) {
            // 旧密码输入正确
            resultMap.put("result", "true");
        } else {
            // 旧密码输入有误
            resultMap.put("result", "false");
        }
    }

    resp.setContentType("application/json");
    PrintWriter writer = resp.getWriter();
    writer.write(JSON.toJSONString(resultMap));
    writer.flush();
    writer.close();
}

15.6 用户管理底层实现

思路：
在这里插入图片描述

  1. 导入分页的工具类
  2. 用户列表页面导入（用户列表 userlist.jsp 和分页页面userlist.jsp）

15.6.1 获取用户数量

  1. UserDao
    // 根据用户名或者角色查询用户总数
public int getUserCount(Connection con, String userName, int userRole) throws SQLException;
  2. UserDaoImpl
    public int getUserCount(Connection con, String userName, int userRole) throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    int count = 0;
    if (con != null) {
        StringBuffer sql = new StringBuffer("select count(1) as 'count' from smbms_user u, smbms_role r where u.userRole=r.id");
        List<Object> list = new ArrayList<Object>();
        if (!StringUtils.isNullOrEmpty(userName)) {
            sql.append(" and u.userName like ?");
            list.add("%" + userName + "%");
        }
        if (userRole > 0) {
            sql.append(" and r.id = ?");
            list.add(userRole);
        }
        Object[] params = list.toArray();
        rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
        if (rs.next()) {
            count = rs.getInt("count");
        }
        BaseDao.closeResource(null, ps, rs);
    }
    return count;
}
  3. UserService
     //查询记录数
publiuc int getUserCount(String userName, int userRole);
  4. UserServiceImpl
    public int getUserCount(String userName, int userRole) {
    Connection con = null;
    int count = 0;
    try {
        con = BaseDao.getConnection();
        count = userDao.getUserCount(con, userName, userRole);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        BaseDao.closeResource(con, null, null);
    }
    return count;
}

15.6.2 获取用户列表

  1. UserDao
    //获取用户列表
public List<User> getUserList(Connection con, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException;
  2. UserDaoImpl
    public List<User> getUserList(Connection con, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    List<User> userList = new ArrayList<User>();
    if (con != null) {
        StringBuffer sql = new StringBuffer("select u.*,r.roleName as `userRoleName` from smbms_user u,smbms_role r where u.userRole=r.id");
        List<Object> list = new ArrayList<Object>();   // sql语句的参数列表
        if (!StringUtils.isNullOrEmpty(userName)) {
            sql.append(" and u.userName like ?");
            list.add("%" + userName + "%");
        }
        if (userRole > 0) {
            sql.append(" and r.id = ?");
            list.add(userRole);
        }
        /*
        mysql 分页使用limit startIndex, pageSize
        比如现在一共13条数据，每页最大容量是5
        0,5 01234 第一页
        5,5 56789 第二页
        10,3 10,11,12 第三页
        */
        sql.append(" order by u.creationDate desc limit ?, ?");
        currentPageNo = (currentPageNo - 1) * pageSize;
        list.add(currentPageNo);
        list.add(pageSize);
        Object[] params = list.toArray();   // 将sql的参数列表转换成对象数组
        rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getInt("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.setUserRoleName(rs.getString("userRoleName"));
            userList.add(user);
        }
        BaseDao.closeResource(null, ps, rs);
    }
    return userList;
}
  3. UserService
    // 根据条件查询用户列表 
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
  4. UserServiceImpl
    public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
    Connection con = null;
    List<User> userList = null;
    try {
        con = BaseDao.getConnection();
        userList = userDao.getUserList(con, queryUserName, queryUserRole, currentPageNo, pageSize);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        BaseDao.closeResource(con, null, null);
    }
    return userList;
}

15.6.3 获取角色操作

  为了职责统一，可以把角色的操作单独放在一个包中，和POJO类对应

  1. RoleDao
    package com.wang.dao.role;

import com.wang.pojo.Role;

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

public interface RoleDao {
	// 获取角色列表
    public List<Role> getRoleList(Connection con) throws SQLException;
}
  2. RoleDaoImpl
    package com.wang.dao.role.impl;

import com.wang.dao.BaseDao;
import com.wang.dao.role.RoleDao;
import com.wang.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 {
    public List<Role> getRoleList(Connection con) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Role> roleList = new ArrayList<Role>();   // 结果集
        if (con != null) {
            String sql = "select id, roleName, roleCode from smbms_role";
            Object[] params = {};
            rs = BaseDao.execute(con, ps, rs, sql, params);
            while (rs.next()) {
                Role role = new Role();
                role.setId(rs.getInt("id"));
                role.setRoleCode(rs.getString("roleCode"));
                role.setRoleName(rs.getString("roleName"));
                roleList.add(role);
            }
            BaseDao.closeResource(null, ps, rs);
        }
        return roleList;
    }
}
  3. RoleService
    package com.wang.service.role;

import com.wang.pojo.Role;

import java.util.List;

public interface RoleService {
    // 获取角色列表
    public List<Role> getRoleList();
}
  4. RoleServiceImpl
    package com.wang.service.role.impl;

import com.wang.dao.BaseDao;
import com.wang.dao.role.RoleDao;
import com.wang.dao.role.impl.RoleDaoImpl;
import com.wang.pojo.Role;
import com.wang.service.role.RoleService;

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

public class RoleServiceImpl implements RoleService {
    private RoleDao roleDao;

	// 通过无参构造实例化对象
    public RoleServiceImpl() {
        roleDao = new RoleDaoImpl();
    }

    public List<Role> getRoleList() {
        Connection con = null;
        List<Role> roleList = null;   // 要返回的结果集
        try {
            con = BaseDao.getConnection();
            roleList = roleDao.getRoleList(con);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeResource(con, null, null);
        }
        return roleList;
    }
}

15.6.4 用户显示的Servlet

  1. 获取用户前端的数据（查询）
  2. 判断请求是否需要执行，看参数的值判断
  3. 为了实现分页，需要计算出当前页面、总页数和页面大小。。
  4. 用户列表展示
  5. 返回前端
    protected void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //获取入参
        String queryName = req.getParameter("queryname");
        String temp = req.getParameter("queryUserRole");
        String pageIndex = req.getParameter("pageIndex");
        //角色
        int queryUserRole = 0;
        //每页最大容量可以前端传入，也可以写成配置项，这里暂时写死
        int pageSize = 5;
        int currentPageNo = 1;
        //参数处理
        if (temp != null && temp.length() > 0) {
            queryUserRole = Integer.parseInt(temp);
        }
        if (pageIndex != null && pageIndex.length() > 0) {
            currentPageNo = Integer.parseInt(pageIndex);
        }

        UserService userService = new UserServiceImpl();
        //获取用户总数(上一页，下一页)
        int totalCount = userService.getUserCount(queryName, queryUserRole);
        //总页数支持
        PageSupport pageSupport = new PageSupport();
        pageSupport.setCurrentPageNo(currentPageNo);
        pageSupport.setPageSize(pageSize);
        pageSupport.setTotalCount(totalCount);

        int totalPageCount = pageSupport.getTotalPageCount();
        //控制首页和尾页
        if (currentPageNo < 0) {
            currentPageNo = 0;
        } else if (currentPageNo > totalPageCount) {
            currentPageNo = totalPageCount;
        }
        //查询用户列表
        List<User> userList = userService.getUserList(queryName, queryUserRole, currentPageNo, pageSize);
        //获取角色列表
        RoleService roleService = new RoleServiceImpl();
        List<Role> roleList = roleService.getRoleList();

        req.setAttribute("userList", userList);
        req.setAttribute("roleList", roleList);
        req.setAttribute("totalCount", totalCount);
        req.setAttribute("currentPageNo", currentPageNo);
        req.setAttribute("totalPageCount", totalPageCount);
        req.setAttribute("queryUserName", queryName);
        req.setAttribute("queryUserRole", queryUserRole);

        req.getRequestDispatcher("userlist.jsp").forward(req, resp);
    }

15.7 架构分析及方法学习

架构分析及方法学习

ReiZhoo
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值