SMBMS
Basic architecture
database
Database architecture
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-vli3iozs-1626496921096) (C: \ users \ Hasee \ appdata \ roaming \ typora user images \ image-20210715140247552. PNG)]
Create table statement
CREATE DATABASE `smbms`; USE `smbms`; DROP TABLE IF EXISTS `smbms_address`; CREATE TABLE `smbms_address` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID', `contact` VARCHAR(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Contact name', `addressDesc` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Receiving address details', `postCode` VARCHAR(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Zip code', `tel` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Contact telephone', `createdBy` BIGINT(20) DEFAULT NULL COMMENT 'creator', `creationDate` DATETIME DEFAULT NULL COMMENT 'Creation time', `modifyBy` BIGINT(20) DEFAULT NULL COMMENT 'Modifier', `modifyDate` DATETIME DEFAULT NULL COMMENT 'Modification time', `userId` BIGINT(20) DEFAULT NULL COMMENT 'user ID', PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `smbms_address`(`id`,`contact`,`addressDesc`,`postCode`,`tel`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`,`userId`) VALUES (1,'Wang Li','44 dongjiaomin lane, Dongcheng District, Beijing','100010','13678789999',1,'2016-04-13 00:00:00',NULL,NULL,1),(2,'Zhang Hongli','3 Danling street, Haidian District, Beijing','100000','18567672312',1,'2016-04-13 00:00:00',NULL,NULL,1),(3,'Ren Zhiqiang','23 Art Museum back street, Dongcheng District, Beijing','100021','13387906742',1,'2016-04-13 00:00:00',NULL,NULL,1),(4,'Cao Ying','14 Chaoyangmen South st, Chaoyang District, Beijing','100053','13568902323',1,'2016-04-13 00:00:00',NULL,NULL,2),(5,'Li Hui','No. 3, Nansan lane, Sanlihe Road, Xicheng District, Beijing','100032','18032356666',1,'2016-04-13 00:00:00',NULL,NULL,3),(6,'Guo Qiang Wang','No. 18, Jinma Industrial Zone, Gaoliying Town, Shunyi District, Beijing','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 'Primary key ID', `billCode` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Bill code', `productName` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Trade name', `productDesc` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Product description', `productUnit` VARCHAR(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Commodity unit', `productCount` DECIMAL(20,2) DEFAULT NULL COMMENT 'Quantity of goods', `totalPrice` DECIMAL(20,2) DEFAULT NULL COMMENT 'Total goods', `isPayment` INT(10) DEFAULT NULL COMMENT 'Paid or not (1: unpaid 2: paid)', `createdBy` BIGINT(20) DEFAULT NULL COMMENT 'Creator( userId)', `creationDate` DATETIME DEFAULT NULL COMMENT 'Creation time', `modifyBy` BIGINT(20) DEFAULT NULL COMMENT 'Updater( userId)', `modifyDate` DATETIME DEFAULT NULL COMMENT 'Update time', `providerId` BIGINT(20) DEFAULT NULL COMMENT 'supplier ID', PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `smbms_bill`(`id`,`billCode`,`productName`,`productDesc`,`productUnit`,`productCount`,`totalPrice`,`isPayment`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`,`providerId`) VALUES (2,'BILL2016_002','Soap, soap, medicated soap','Daily Necessities-soap ','block','1000.00','10000.00',2,1,'2016-03-23 04:20:40',NULL,NULL,13),(3,'BILL2016_003','soybean oil','food-Edible oil','Jin','300.00','5890.00',2,1,'2014-12-14 13:02:03',NULL,NULL,6),(4,'BILL2016_004','Olive oil','food-Imported edible oil','Jin','200.00','9800.00',2,1,'2013-10-10 03:12:13',NULL,NULL,7),(5,'BILL2016_005','Detergent','Daily Necessities-Kitchen cleaning','bottle','500.00','7000.00',2,1,'2014-12-14 13:02:03',NULL,NULL,9),(6,'BILL2016_006','American almond','food-nut','bag','300.00','5000.00',2,1,'2016-04-14 06:08:09',NULL,NULL,4),(7,'BILL2016_007','Bath liquid, essential oil','Daily Necessities-Bath class','bottle','500.00','23000.00',1,1,'2016-07-22 10:10:22',NULL,NULL,14),(8,'BILL2016_008','Stainless steel plate and bowl','Daily Necessities-kitchenware','individual','600.00','6000.00',2,1,'2016-04-14 05:12:13',NULL,NULL,14),(9,'BILL2016_009','Plastic cup','Daily Necessities-glass','individual','350.00','1750.00',2,1,'2016-02-04 11:40:20',NULL,NULL,14),(10,'BILL2016_010','Bean paste','food-Seasoning','bottle','200.00','2000.00',2,1,'2013-10-29 05:07:03',NULL,NULL,8),(11,'BILL2016_011','Sea blue','Drinks-National Wine','bottle','50.00','10000.00',1,1,'2016-04-14 16:16:00',NULL,NULL,1),(12,'BILL2016_012','Chivas','Drinks-imported wine','bottle','20.00','6000.00',1,1,'2016-09-09 17:00:00',NULL,NULL,1),(13,'BILL2016_013','Great Wall red wine','Drinks-red wine','bottle','60.00','800.00',2,1,'2016-11-14 15:23:00',NULL,NULL,1),(14,'BILL2016_014','Thai fragrant rice','food-rice','Jin','400.00','5000.00',2,1,'2016-10-09 15:20:00',NULL,NULL,3),(15,'BILL2016_015','Northeast rice','food-rice','Jin','600.00','4000.00',2,1,'2016-11-14 14:00:00',NULL,NULL,3),(16,'BILL2016_016','Coca Cola','Drinks','bottle','2000.00','6000.00',2,1,'2012-03-27 13:03:01',NULL,NULL,2),(17,'BILL2016_017','pulsation','Drinks','bottle','1500.00','4500.00',2,1,'2016-05-10 12:00:00',NULL,NULL,2),(18,'BILL2016_018','Wow, ha ha','Drinks','bottle','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 'Primary key ID', `proCode` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Supplier code', `proName` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Supplier name', `proDesc` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Detailed description of supplier', `proContact` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Supplier Contact ', `proPhone` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'contact number', `proAddress` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'address', `proFax` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Fax', `createdBy` BIGINT(20) DEFAULT NULL COMMENT 'Creator( userId)', `creationDate` DATETIME DEFAULT NULL COMMENT 'Creation time', `modifyDate` DATETIME DEFAULT NULL COMMENT 'Update time', `modifyBy` BIGINT(20) DEFAULT NULL COMMENT 'Updater( userId)', PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `smbms_provider`(`id`,`proCode`,`proName`,`proDesc`,`proContact`,`proPhone`,`proAddress`,`proFax`,`createdBy`,`creationDate`,`modifyDate`,`modifyBy`) VALUES (1,'BJ_GYS001','Beijing sanmutang Trading Co., Ltd','Long term partner, main products:Maotai, Wuliangye, Langjiu, Jiugui Liquor, Luzhou Laojiao, laimao liquor, French red wine, etc','Zhang Guoqiang','13566667777','Yufangyuan North Road, Fengtai District, Beijing','010-58858787',1,'2013-03-21 16:52:07',NULL,NULL),(2,'HB_GYS001','Shijiazhuang Shuaiyi Food Trade Co., Ltd','Long term partner, main products:Beverage, water beverage, plant protein beverage, snack food, fruit juice beverage, functional beverage, etc','Jun Wang','13309094212','Xinhua District, Shijiazhuang, Hebei Province','0311-67738876',1,'2016-04-13 04:20:40',NULL,NULL),(3,'GZ_GYS001','Shenzhen taixiang Rice Industry Co., Ltd','First time partner, main product: Liangji Jinlun rice,Longlun fragrant rice, etc','Zheng Chenghan','13402013312','Huafeng building, 6006 Shennan Avenue, Futian District, Shenzhen, Guangdong','0755-67776212',1,'2014-03-21 16:56:07',NULL,NULL),(4,'GZ_GYS002','Shenzhen xilaike Trading Co., Ltd','Long term partner, main product: fried nuts.Preserved fruit.Natural flower tea.Nutritious bean.Specialty food.Imported food.Seafood snacks.Preserved meat','Lini','18599897645','Fulong Industrial Zone, Shenzhen, Guangdong B2 West of building 3','0755-67772341',1,'2013-03-22 16:52:07',NULL,NULL),(5,'JS_GYS001','Xinghua Jiamei condiment factory','Long term partner, main products: natural spices, chicken essence, compound seasoning','Xu Guoyang','13754444221','Linhu Industrial Zone, Xinghua City, Jiangsu Province','0523-21299098',1,'2015-11-22 16:52:07',NULL,NULL),(6,'BJ_GYS002','Beijing nafur Edible Oil Co., Ltd','Long term partner, main products: camellia oil, soybean oil, peanut oil, olive oil, etc','Ma Ying','13422235678','Building 1, Zhujiang Dijing, Chaoyang District, Beijing','010-588634233',1,'2012-03-21 17:52:07',NULL,NULL),(7,'BJ_GYS003','Beijing Guoliang Edible Oil Co., Ltd','First time partner, main products: peanut oil, soybean oil, small grinding oil, etc','Wang Chi','13344441135','Beijing Daxing Qingyundian Development Zone','010-588134111',1,'2016-04-13 00:00:00',NULL,NULL),(8,'ZJ_GYS001','Cixi Guanghe green food factory','Long term partner, main products: bean paste, soybean paste, sweet flour paste, pepper, garlic and other agricultural products','Xue Shengdan','18099953223','Zhou Xiang Xiao an Cun, Cixi, Ningbo City, Zhejiang Province','0574-34449090',1,'2013-11-21 06:02:07',NULL,NULL),(9,'GX_GYS001','Youbai Trading Co., Ltd','Long term partner, main product: daily chemical products','Li Liguo','13323566543','42 Xiuxiang Avenue, Nanning, Guangxi-1 number','0771-98861134',1,'2013-03-21 19:52:07',NULL,NULL),(10,'JS_GYS002','Nanjing huotoujun Information Technology Co., Ltd','Long term partner, main products: stainless steel kitchenware, etc','Ms. Chen','13098992113','New town headquarters building, No. 1, Pukou Avenue, Pukou District, Nanjing, Jiangsu A Room 903, block','025-86223345',1,'2013-03-25 16:52:07',NULL,NULL),(11,'GZ_GYS003','Guangzhou Baiyun Meixing hardware products factory','Long term partner, main products: sponge mattress, cushion, cushion, sponge pillow, headrest, etc','Liang Tian','13562276775','No. 20, Fulong Road, Zhongluotan Town, Baiyun District, Guangzhou','020-85542231',1,'2016-12-21 06:12:17',NULL,NULL),(12,'BJ_GYS004','Beijing Longsheng Daily Chemical Technology Co., Ltd','Long term partner, main products: daily chemical environmental cleaning agent, home washing monopoly, washing supplies network, wall mold remover, wall mold remover, etc','Sun Xin','13689865678','Jiugong, Daxing District, Beijing','010-35576786',1,'2014-11-21 12:51:11',NULL,NULL),(13,'SD_GYS001','Shandong Haoke Huaguang United Development Co., Ltd','Long term partner, main products: laundry soap, washing powder, laundry liquid, detergent, killing class, soap, etc','Wu Hongzhuan','13245468787','21 Renhe street, Jibei Industrial Zone, Jiyang, Shandong','0531-53362445',1,'2015-01-28 10:52:07',NULL,NULL),(14,'JS_GYS003','Wuxi xiyuankun firm','Long term partner, main products: Wholesale sales of daily chemical products','Zhou Yiqing','18567674532','Shengan West Road, Wuxi, Jiangsu','0510-32274422',1,'2016-04-23 11:11:11',NULL,NULL),(15,'ZJ_GYS002','Le Pai daily necessities factory','Long term partner, main products: all kinds of medium and high-grade plastic cups, plastic Le Kou water cups (sealed cups), fresh-keeping cups (fresh box), advertising cups and gift cups','Wang Shijie','13212331567','Yidong Road, Yiwu City, Jinhua City, Zhejiang Province','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 'Primary key ID', `roleCode` VARCHAR(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Role code', `roleName` VARCHAR(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Role name', `createdBy` BIGINT(20) DEFAULT NULL COMMENT 'creator', `creationDate` DATETIME DEFAULT NULL COMMENT 'Creation time', `modifyBy` BIGINT(20) DEFAULT NULL COMMENT 'Modifier', `modifyDate` DATETIME DEFAULT NULL COMMENT 'Modification time', PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `smbms_role`(`id`,`roleCode`,`roleName`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES (1,'SMBMS_ADMIN','system administrator',1,'2016-04-13 00:00:00',NULL,NULL),(2,'SMBMS_MANAGER','manager',1,'2016-04-13 00:00:00',NULL,NULL),(3,'SMBMS_EMPLOYEE','Ordinary staff',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 'Primary key ID', `userCode` VARCHAR(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'User code', `userName` VARCHAR(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'User name', `userPassword` VARCHAR(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'User password', `gender` INT(10) DEFAULT NULL COMMENT 'Gender (1):Female, 2:(male)', `birthday` DATE DEFAULT NULL COMMENT 'date of birth', `phone` VARCHAR(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'mobile phone', `address` VARCHAR(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'address', `userRole` BIGINT(20) DEFAULT NULL COMMENT 'User role (from role table)-role id)', `createdBy` BIGINT(20) DEFAULT NULL COMMENT 'Creator( userId)', `creationDate` DATETIME DEFAULT NULL COMMENT 'Creation time', `modifyBy` BIGINT(20) DEFAULT NULL COMMENT 'Updater( userId)', `modifyDate` DATETIME DEFAULT NULL COMMENT 'Update time', PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `smbms_user`(`id`,`userCode`,`userName`,`userPassword`,`gender`,`birthday`,`phone`,`address`,`userRole`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES (1,'admin','system administrator','1234567',1,'1983-10-10','13688889999','207 Chengfu Road, Haidian District, Beijing',1,1,'2013-03-21 16:52:07',NULL,NULL),(2,'liming','Li Ming','0000000',2,'1983-12-10','13688884457','9 Qianmen East st, Dongcheng District, Beijing',2,1,'2014-12-31 19:52:09',NULL,NULL),(5,'hanlubiao','Han Lubiao','0000000',2,'1984-06-05','18567542321','12 Beichen center, Chaoyang District, Beijing',2,1,'2014-12-31 19:52:09',NULL,NULL),(6,'zhanghua','Zhang Hua','0000000',1,'1983-06-15','13544561111','61 Xueyuan Road, Haidian District, Beijing',3,1,'2013-02-11 10:51:17',NULL,NULL),(7,'wangyang','Wang Yang','0000000',2,'1982-12-31','13444561124','16th floor, brilliant international, Xierqi, Haidian District, Beijing',3,1,'2014-06-11 19:09:07',NULL,NULL),(8,'zhaoyan','Zhao Yan','0000000',1,'1986-03-07','18098764545','Building 10, Huilongguan community, Haidian District, Beijing',3,1,'2016-04-21 13:54:07',NULL,NULL),(10,'sunlei','Sun Lei','0000000',2,'1981-01-04','13387676765','12th floor, Xinyue community, Guanzhuang, Chaoyang District, Beijing',3,1,'2015-05-06 10:52:07',NULL,NULL),(11,'sunxing','Sun Xing','0000000',2,'1978-03-12','13367890900','10 Jianguomen South st, Chaoyang District, Beijing',3,1,'2016-11-09 16:51:17',NULL,NULL),(12,'zhangchen','Zhang Chen','0000000',1,'1986-03-28','18098765434','Building 13, North Berlin Philharmonic phase III, Guanzhuang intersection, Chaoyang District',3,1,'2016-08-09 05:52:37',1,'2016-04-14 14:15:36'),(13,'dengchao','Deng Chao','0000000',2,'1981-11-04','13689674534','Building 10, Beihang family hospital, Haidian District, Beijing',3,1,'2016-07-11 08:02:47',NULL,NULL),(14,'yangguo','Guo Yang','0000000',2,'1980-01-01','13388886623','Building 20, jasmine garden, Beiyuanjiayuan, Chaoyang District, Beijing',3,1,'2015-02-01 03:52:07',NULL,NULL),(15,'zhaomin','Zhao Min','0000000',1,'1987-12-04','18099897657','Building 12, District 3, Tiantongyuan, Changping District, Beijing',2,1,'2015-09-12 12:02:12',NULL,NULL);
Preparation for project construction
- Create a maven project
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-lyd73udh-1626496921099) (C: \ users \ Hasee \ appdata \ roaming \ typora user images \ image-20210715140800799. PNG)]
web.xml configuration
<?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">
</web-app>
- Guide Package
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-ttflbgo5-1626496921101) (C: \ users \ Hasee \ appdata \ roaming \ typora user images \ image-20210715141434747. PNG)]
-
Configure tomcat
-
At porm jar package required for XML import
<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>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.34</version>
</dependency>
<!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency><!-- https://mvnrepository.com/artifact/taglibs/standard -->
<dependency>
<groupId>taglibs</groupId>
<artifactId>standard</artifactId>
<version>1.1.2</version>
</dependency>
</dependencies>
- Create package structure
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-euxyrqm8-1626496921104) (C: \ users \ Hasee \ appdata \ roaming \ typora user images \ image-20210715142706742. PNG)]
-
idea connection database
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-p3m8gl3h-1626496921106) (C: \ users \ Hasee \ appdata \ roaming \ typora user images \ image-20210715142959904. PNG)]
-
Writing entity classes
ORM mapping: table class mapping
User class
import java.util.Date;
public class User {
private Integer id; //id
private String userCode; //User code
private String userName; //User name
private String userPassword; //User password
private Integer gender; //Gender
private Date birthday; //date of birth
private String phone; //Telephone
private String address; //address
private Integer userRole; //User role
private Integer createdBy; //creator
private Date creationDate; //Creation time
private Integer modifyBy; //Updater
private Date modifyDate; //Update time
private Integer age;//Age
private String userRoleName; //User role name
public String getUserRoleName() {
return userRoleName;
}
public void setUserRoleName(String userRoleName) {
this.userRoleName = userRoleName;
}
public Integer getAge() {
Date date = new Date();
Integer age = date.getYear()-birthday.getYear();
return age;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserCode() {
return userCode;
}
public void setUserCode(String userCode) {
this.userCode = userCode;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPassword() {
return userPassword;
}
public void setUserPassword(String userPassword) {
this.userPassword = userPassword;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Integer getUserRole() {
return userRole;
}
public void setUserRole(Integer userRole) {
this.userRole = userRole;
}
public Integer getCreatedBy() {
return createdBy;
}
public void setCreatedBy(Integer createdBy) {
this.createdBy = createdBy;
}
public Date getCreationDate() {
return creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public Integer getModifyBy() {
return modifyBy;
}
public void setModifyBy(Integer modifyBy) {
this.modifyBy = modifyBy;
}
public Date getModifyDate() {
return modifyDate;
}
public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
}
Bill class
import java.math.BigDecimal;
import java.util.Date;
public class Bill {
private Integer id; //id
private String billCode; //Bill code
private String productName; //Trade name
private String productDesc; //Product description
private String productUnit; //Commodity unit
private BigDecimal productCount; //Quantity of goods
private BigDecimal totalPrice; //Total amount
private Integer isPayment; //Whether to pay
private Integer providerId; //Supplier ID
private Integer createdBy; //creator
private Date creationDate; //Creation time
private Integer modifyBy; //Updater
private Date modifyDate;//Update time
private String providerName;//Supplier name
public String getProviderName() {
return providerName;
}
public void setProviderName(String providerName) {
this.providerName = providerName;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBillCode() {
return billCode;
}
public void setBillCode(String billCode) {
this.billCode = billCode;
}
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
public String getProductDesc() {
return productDesc;
}
public void setProductDesc(String productDesc) {
this.productDesc = productDesc;
}
public String getProductUnit() {
return productUnit;
}
public void setProductUnit(String productUnit) {
this.productUnit = productUnit;
}
public BigDecimal getProductCount() {
return productCount;
}
public void setProductCount(BigDecimal productCount) {
this.productCount = productCount;
}
public BigDecimal getTotalPrice() {
return totalPrice;
}
public void setTotalPrice(BigDecimal totalPrice) {
this.totalPrice = totalPrice;
}
public Integer getIsPayment() {
return isPayment;
}
public void setIsPayment(Integer isPayment) {
this.isPayment = isPayment;
}
public Integer getProviderId() {
return providerId;
}
public void setProviderId(Integer providerId) {
this.providerId = providerId;
}
public Integer getCreatedBy() {
return createdBy;
}
public void setCreatedBy(Integer createdBy) {
this.createdBy = createdBy;
}
public Date getCreationDate() {
return creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public Integer getModifyBy() {
return modifyBy;
}
public void setModifyBy(Integer modifyBy) {
this.modifyBy = modifyBy;
}
public Date getModifyDate() {
return modifyDate;
}
public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
}
Role class
import java.util.Date;
public class Role {
private Integer id; //id
private String roleCode; //Role code
private String roleName; //Role name
private Integer createdBy; //creator
private Date creationDate; //Creation time
private Integer modifyBy; //Updater
private Date modifyDate;//Update time
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getRoleCode() {
return roleCode;
}
public void setRoleCode(String roleCode) {
this.roleCode = roleCode;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public Integer getCreatedBy() {
return createdBy;
}
public void setCreatedBy(Integer createdBy) {
this.createdBy = createdBy;
}
public Date getCreationDate() {
return creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public Integer getModifyBy() {
return modifyBy;
}
public void setModifyBy(Integer modifyBy) {
this.modifyBy = modifyBy;
}
public Date getModifyDate() {
return modifyDate;
}
public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
}
Provider class
import java.util.Date;
public class Provider {
private Integer id; //id
private String proCode; //Supplier code
private String proName; //Supplier name
private String proDesc; //Supplier description
private String proContact; //Supplier Contact
private String proPhone; //Supplier telephone
private String proAddress; //Supplier address
private String proFax; //Supplier fax
private Integer createdBy; //creator
private Date creationDate; //Creation time
private Integer modifyBy; //Updater
private Date modifyDate;//Update time
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getProCode() {
return proCode;
}
public void setProCode(String proCode) {
this.proCode = proCode;
}
public String getProName() {
return proName;
}
public void setProName(String proName) {
this.proName = proName;
}
public String getProDesc() {
return proDesc;
}
public void setProDesc(String proDesc) {
this.proDesc = proDesc;
}
public String getProContact() {
return proContact;
}
public void setProContact(String proContact) {
this.proContact = proContact;
}
public String getProPhone() {
return proPhone;
}
public void setProPhone(String proPhone) {
this.proPhone = proPhone;
}
public String getProAddress() {
return proAddress;
}
public void setProAddress(String proAddress) {
this.proAddress = proAddress;
}
public String getProFax() {
return proFax;
}
public void setProFax(String proFax) {
this.proFax = proFax;
}
public Integer getCreatedBy() {
return createdBy;
}
public void setCreatedBy(Integer createdBy) {
this.createdBy = createdBy;
}
public Date getCreationDate() {
return creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public Integer getModifyBy() {
return modifyBy;
}
public void setModifyBy(Integer modifyBy) {
this.modifyBy = modifyBy;
}
public Date getModifyDate() {
return modifyDate;
}
public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
}
-
Write basic public classes
-
Database configuration file
Write dB in resources Properties configuration file
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=utf-8 username=root password=123456
-
Write a common class for the database
package com.dong.dao; import java.io.IOException; 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 code blocks are initialized when the class is loaded static { Properties properties = new Properties(); //Read the corresponding resources through the class loader InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties"); try { properties.load(is); } catch (IOException e) { e.printStackTrace(); } driver = properties.getProperty("driver"); driver = properties.getProperty("url"); driver = properties.getProperty("username"); driver = properties.getProperty("password"); } //Get connection to database public static Connection getConnection(){ Connection connection = null; try { Class.forName(driver); connection = DriverManager.getConnection(url, username, password); } catch (Exception e) { e.printStackTrace(); } return connection; } //Write query public class public static ResultSet execute(Connection connection,String sql,Object[] parms,ResultSet resultSet,PreparedStatement preparedStatement) throws SQLException { //Precompiled sql can be executed directly later preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < parms.length; i++) { //setObject placeholders start with 1 and arrays start with 0 preparedStatement.setObject(i+1,parms); } resultSet = preparedStatement.executeQuery(); return resultSet; } //Prepare public methods for addition, deletion and modification public static int execute(Connection connection,String sql,Object[] parms,PreparedStatement preparedStatement) throws SQLException { preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < parms.length; i++) { //setObject placeholders start with 1 and arrays start with 0 preparedStatement.setObject(i+1,parms); } int updateRows = preparedStatement.executeUpdate(); return updateRows; } //Close the connection and free up resources public static boolean closeResource(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet){ boolean flag = true; if (resultSet!=null){ try { resultSet.close(); //GC recovery resultSet = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } if (preparedStatement!=null){ try { preparedStatement.close(); //GC recovery preparedStatement = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } if (connection!=null){ try { connection.close(); //GC recovery connection = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } return flag; } }- Write character encoding filter
package com.dong.filter; import javax.servlet.*; 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"); request.setCharacterEncoding("utf-8"); chain.doFilter(request,response); } public void destroy() { } }- On the web Writing filter mappings in XML
<filter> <filter-name>CharacterEncodingFilter</filter-name> <filter-class>com.dong.filter.CharacterEncodingFilter</filter-class> </filter> <filter-mapping> <filter-name>CharacterEncodingFilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping>- Import static resources
Realization of login function
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-gk6szlvv-1626496921108) (C: \ users \ Hasee \ appdata \ roaming \ typora user images \ image-20210715161803595. PNG)]
-
Write front page
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head lang="en">
<meta charset="UTF-8">
<title>System login - Supermarket order management system</title>
<link type="text/css" rel="stylesheet" href="${pageContext.request.contextPath }/css/style.css" />
<script type="text/javascript">
</script>
</head>
<body class="login_bg">
<section class="loginBox">
<header class="loginHeader">
<h1>Supermarket order management system</h1>
</header>
<section class="loginCont">
<form class="loginForm" action="${pageContext.request.contextPath }/login.do" name="actionForm" id="actionForm" method="post" >
<div class="info">${error}</div>
<div class="inputbox">
<label>user name:</label>
<input type="text" class="input-text" id="userCode" name="userCode" placeholder="enter one user name" required/>
</div>
<div class="inputbox">
<label>password:</label>
<input type="password" id="userPassword" name="userPassword" placeholder="Please input a password" required/>
</div>
<div class="subBtn">
<input type="submit" value="Sign in"/>
<input type="reset" value="Reset"/>
</div>
</form>
</section>
</section>
</body>
</html>
Set the front page as the first page
Web. In Web inf XML add the following code to realize the function of setting the home page
<!--Set home page-->
<welcome-file-list>
<welcome-file>login.jsp</welcome-file>
</welcome-file-list>
Realization of login function
Write the user login interface of dao layer UserDao
public User getLoginUser(Connection connection,String userCode,String userPassword) throws SQLException;
Write the implementation class UserDaoImpl of UserDao interface
public class UserDaoImpl implements UserDao {
//Get the user to log in
public User getLoginUser(Connection connection, String userCode,String userPassword) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
User user = null;
if (connection!=null){
String sql = "select * from smbms_user where userCode=?";
Object[] params = {userCode};
//System.out.println(userPassword);
rs = BaseDao.execute(connection, pstm, 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.setCreationDate(rs.getTimestamp("creationDate"));
user.setModifyBy(rs.getInt("modifyBy"));
user.setModifyDate(rs.getTimestamp("modifyDate"));
}
BaseDao.closeResource(null,pstm,rs);
if (!user.getUserPassword().equals(userPassword))
user=null;
}
return user;
}
}
Write business layer interface
Create a user package under service and a UserService interface
public interface UserService {
//User login
public User login(String userCode,String password);
}
Write the implementation class of the business layer interface
Create the UserServiceImpl class in the user package of the service
public class UserServiceImpl implements UserService {
//The business layer will call the Dao layer, so we need to introduce the Dao layer;
private UserDao userDao;
public UserServiceImpl(){
userDao = new UserDaoImpl();
}
public User login(String userCode, String password) {
Connection connection = null;
User user = null;
try {
connection = BaseDao.getConnection();
//Call the corresponding specific database operation through the business layer
user = userDao.getLoginUser(connection, userCode,password);
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeResource(connection,null,null);
}
return user;
}
}
Writing servlets
Create the user package in the Servlet package, and create the LoginServlet class in the user package
public class LoginServlet extends HttpServlet {
//Servlet: control layer, calling business layer code
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("LoginServlet--start....");
//Get user name and password
String userCode = req.getParameter("userCode");
String userPassword = req.getParameter("userPassword");
//Compare with the password in the database and call the business layer;
UserService userService = new UserServiceImpl();
User user = userService.login(userCode, userPassword); //The person who logged in has been found out here
System.out.println(userCode);
System.out.println(userPassword);
if (user!=null){ //If you find this person, you can log in
//Put the user's information into the Session;
req.getSession().setAttribute(Constants.USER_SESSION,user);
//Jump to home page
resp.sendRedirect("jsp/frame.jsp");
}else {//No such person found, unable to log in
//Forward it back to the login page and prompt it that the user name or password is wrong;
req.setAttribute("error","Incorrect user name or password");
req.getRequestDispatcher("login.jsp").forward(req,resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
}
Register Servlet
<servlet>
<servlet-name>updatePwd</servlet-name>
<servlet-class>com.dong.servlet.user.UserServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>updatePwd</servlet-name>
<url-pattern>/jsp/user.do</url-pattern>
</servlet-mapping>
Login function optimization
Logout function:
Remove the session and return to the login page
Create the logoutservlet class in the user package of the Servlet package
public class LogoutServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//Remove the user's constants USER_ SESSION
req.getSession().removeAttribute(Constants.USER_SESSION);
resp.sendRedirect("/login.jsp");//Return to login page
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
}
On the web Register in XML
<servlet>
<servlet-name>LogoutServlet</servlet-name>
<servlet-class>com.dong.servlet.user.LogoutServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>LogoutServlet</servlet-name>
<url-pattern>/jsp/logout.do</url-pattern>
</servlet-mapping>
Set automatic logoff
After 30 minutes, the session will automatically fail
<session-config> <session-timeout>30</session-timeout> </session-config>
Login interception
Create the SysFilter class in the filter
public class SysFilter implements Filter {
public void init(FilterConfig filterConfig) throws ServletException {
}
public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws IOException, ServletException {
HttpServletRequest request = (HttpServletRequest) req;
HttpServletResponse response = (HttpServletResponse) resp;
//Filter, get users from Session,
User user = (User) request.getSession().getAttribute(Constants.USER_SESSION);
if (user==null){ //Has been removed or logged off, or is not logged in
response.sendRedirect("/error.jsp");
}else {
chain.doFilter(req,resp);
}
}
public void destroy() {
}
}
On the web Register in XML
<!--User login filter-->
<filter>
<filter-name>SysFilter</filter-name>
<filter-class>com.kuang.filter.SysFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>SysFilter</filter-name>
<url-pattern>/jsp/*</url-pattern>
</filter-mapping>
Implementation of password modification
- Write the interface for users of dao layer to modify password UserDao
Write the following code for the UserDao interface
//Modify current user password public int updatePwd(Connection connection, int id, String password) throws SQLException;
- Write the UserDao interface implementation class UserDaoImpl
public int updatePwd(Connection connection, int id, String password) throws SQLException {
PreparedStatement pstm = null;
int execute = 0;
if (connection!=null){
String sql = "update smbms_user set userPassword = ? where id = ?";
Object params[] = {password,id};
execute = BaseDao.execute(connection, sql, params, pstm);
BaseDao.closeResource(null,pstm,null);
}
return execute; }
-
Write business layer interface
Add the following code to the UserService interface in the user package of the service
//Change password according to user ID public boolean updatePwd(int id, String pwd);
- Write the business layer interface implementation class
Add the following code to the UserServiceImpl class in the user package of the service
public boolean updatePwd(int id, String pwd) {
Connection connection = null;
boolean flag = false;
//Change Password
try {
connection = BaseDao.getConnection();
if (userDao.updatePwd(connection,id,pwd)>0){
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeResource(connection,null,null);
}
return flag;
}
-
Writing Servlet classes
Create the UserServlet class in the user package in the Servlet package
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);
}else if (method.equals("pwdmodify")){
this.pwdModify(req, resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
//Change Password
public void updatePwd(HttpServletRequest req, HttpServletResponse resp){
//Get the ID from the Session;
Object o = req.getSession().getAttribute(Constants.USER_SESSION);
String newpassword = req.getParameter("newpassword");
System.out.println("UserServlet:"+newpassword);
boolean flag = false;
System.out.println(o!=null);
System.out.println(StringUtils.isNullOrEmpty(newpassword));
if (o!=null && newpassword!=null){
UserService userService = new UserServiceImpl();
flag = userService.updatePwd(((User) o).getId(), newpassword);
if (flag){
req.setAttribute("message","The password is changed successfully. Please exit and log in with a new password");
//Password modification succeeded. Remove the current Session
req.getSession().removeAttribute(Constants.USER_SESSION);
}else {
req.setAttribute("message","Password modification failed");
//Password modification succeeded. Remove the current Session
}
}else {
req.setAttribute("message","There is a problem with the new password");
}
try {
req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//Verify the old password. There is a user's password in the session
public void pwdModify(HttpServletRequest req, HttpServletResponse resp){
//Get the ID from the Session;
Object o = req.getSession().getAttribute(Constants.USER_SESSION);
String oldpassword = req.getParameter("oldpassword");
System.out.println(oldpassword);
//Universal Map: result set
Map<String, String> resultMap = new HashMap<String,String>();
if (o==null){ //The session is invalid. The session has expired
resultMap.put("result","sessionerror");
}else if (StringUtils.isNullOrEmpty(oldpassword)){ //The password entered is empty
resultMap.put("result","error");
}else {
String userPassword = ((User) o).getUserPassword(); //Password of user in Session
if (oldpassword.equals(userPassword)){
resultMap.put("result","true");
}else {
resultMap.put("result","false");
}
}
try {
resp.setContentType("application/json");
PrintWriter writer = resp.getWriter();
//JSON tool class of JSONArray Alibaba, format conversion
/*
resultMap = ["result","sessionerror","result","error"]
Json Format = {key: value}
*/
writer.write(JSONArray.toJSONString(resultMap));
writer.flush();
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
User management implementation
1. Get the number of users
(1) Write an interface to query the total number of people UserDao
Write the following code for the UserDao interface
//Total number of query users public int getUserCount(Connection connection,String username ,int userRole)throws SQLException;
(2) UserDao interface implementation class UserDaoImpl
Add the following code to the implementation class
public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
//Query the total number of users by user name or role
PreparedStatement pstm = 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<Object>();//Store our parameters
if (!StringUtils.isNullOrEmpty(username)){
sql.append(" and u.userName like ?");
list.add("%"+username+"%"); //index:0
}
if (userRole>0){
sql.append(" and u.userRole = ?");
list.add(userRole); //index:1
}
//How to convert List to array
Object[] params = list.toArray();
System.out.println("UserDaoImpl->getUserCount:"+sql.toString()); //Output the last complete SQL statement
rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
if (rs.next()){
count = rs.getInt("count"); //Get the final quantity from the result set
}
BaseDao.closeResource(null,pstm,rs);
}
return count;
}
(3) Write business layer interface
UserSe in user package of service
//Number of query records public int getUserCount(String username,int userRole);
(4) Write the business layer interface implementation class
Add the following code to the UserServiceImpl class in the user package of the service
public int getUserCount(String username, int userRole) {
Connection connection = null;
int count = 0;
try {
connection = BaseDao.getConnection();
count = userDao.getUserCount(connection, username, userRole);
} catch (SQLException e) {
e.printStackTrace();
} finally {
BaseDao.closeResource(connection,null,null);
}
return count;
}
2. Get user list
(1) Write UserDao
Write the following code for the UserDao interface
//Query by criteria - userList public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)throws Exception;
(2) UserDao interface implementation class UserDaoImpl
Add the following code to the implementation class
public List getUserList(Connection connection, String userName,int userRole,int currentPageNo, int pageSize)
throws Exception {
PreparedStatement pstm = null;
ResultSet rs = null;
List 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 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);
}
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, pstm, 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, pstm, rs);
}
return userList;
}
(3) Write business layer interface
Add the following code to the UserService interface in the user package of the service
//Query user list by criteria
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
(4) Write the business layer interface implementation class
Add the following code to the UserServiceImpl class in the user package of the service
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{
BaseDao.closeResource(connection, null, null);
}
return userList;
}
3. Get role operation
(1) Write interface RoleDao
public interface RoleDao {
//Get role list
public List<Role> getRoleList(Connection connection) throws SQLException;
}
(2) RoleDao interface implementation class RoleDaoImpl
Add the following code to the implementation class
public class RoleDaoImpl implements RoleDao {
//Get role list
public List<Role> getRoleList(Connection connection) throws SQLException {
PreparedStatement pstm = null;
ResultSet resultSet = null;
ArrayList<Role> roleList = new ArrayList<Role>();
if (connection!=null){
String sql = "select * from smbms_role";
Object[] params = {};
resultSet = BaseDao.execute(connection, pstm, resultSet, sql, params);
while (resultSet.next()){
Role _role = new Role();
_role.setId(resultSet.getInt("id"));
_role.setRoleCode(resultSet.getString("roleCode"));
_role.setRoleName(resultSet.getString("roleName"));
roleList.add(_role);
}
BaseDao.closeResource(null,pstm,resultSet);
}
return roleList;
}
}
(3) Write business layer interface
Create the role package in the service, and then create the RoleService interface. Add the following code
//Get role list public List<Role> getRoleList();
(4) Write the business layer interface implementation class
Add the following code to the RoleServiceImpl class in the role package of the service
public class RoleServiceImpl implements RoleService {
//Introducing Dao
private RoleDao roleDao;
public RoleServiceImpl() {
roleDao = new RoleDaoImpl();
}
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 {
BaseDao.closeResource(connection,null,null);
}
return roleList;
}
}
4. Write Servlet class
Add the UserServlet class in the user package in the Servlet package as follows
//Add an if judgment
@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);
}else if (method.equals("pwdmodify")&&method!=null){
this.pwdModify(req, resp);
}else if (method.equals("query")&&method!=null){
this.query(req, resp);
}
}
//query method
public void query(HttpServletRequest req, HttpServletResponse resp){
//Query user list
//Obtain data from the front end;
String queryUserName = req.getParameter("queryname");
String temp = req.getParameter("queryUserRole");
String pageIndex = req.getParameter("pageIndex");
int queryUserRole = 0;
//Get user list
UserServiceImpl userService = new UserServiceImpl();
List<User> userList = null;
//The first time you go to this request, it must be the first page with a fixed page size;
int pageSize = 5; //You can add these to the configuration file for later modification;
int currentPageNo = 1;
if (queryUserName ==null){
queryUserName = "";
}
if (temp!=null && !temp.equals("")){
queryUserRole = Integer.parseInt(temp); //Assign a value to the query! 0,1,2,3
}
if (pageIndex!=null){
currentPageNo = Integer.parseInt(pageIndex);
}
//Get the total number of users (paging: Previous page, next page)
int totalCount = userService.getUserCount(queryUserName, queryUserRole);
//Total pages support
PageSupport pageSupport = new PageSupport();
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
pageSupport.setTotalCount(totalCount);
int totalPageCount = ((int)(totalCount/pageSize))+1;
//Control first and last pages
//If the page is smaller than 1, the first page will be displayed
if (currentPageNo<1){
currentPageNo = 1;
}else if (currentPageNo>totalPageCount){ //The current page is larger than the last page;
currentPageNo = totalPageCount;
}
//Get user list display
userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize);
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);
//Back to front end
try {
req.getRequestDispatcher("userlist.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
(pageIndex);
}
//Get the total number of users (paging: Previous page, next page)
int totalCount = userService.getUserCount(queryUserName, queryUserRole);
//Total pages support
PageSupport pageSupport = new PageSupport();
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
pageSupport.setTotalCount(totalCount);
int totalPageCount = ((int)(totalCount/pageSize))+1;
//Control first and last pages
//If the page is smaller than 1, the first page will be displayed
if (currentPageNo<1){
currentPageNo = 1;
}else if (currentPageNo>totalPageCount){ //The current page is larger than the last page;
currentPageNo = totalPageCount;
}
//Get user list display
userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize);
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);
//Back to front end
try {
req.getRequestDispatcher("userlist.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}