JavaWeb project - smbms (supermarket management project)*
Database construction
CREATE DATABASE `smbms`; use `smbms`; 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);
Initial framework of the project:
Database:
How to build the project?
Consider whether to use maven? jar package and rely on
1. Construction project preparation
- Build a maven web project
- Configure Tomcat
- Test whether the project can run
- Import the jar package required in the project;
jsp, Servlet, mysql driver, jstl, stand... [the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-td34vhhi-163868667724) (image-20211025150730101. PNG)] - Build package structure
- Writing entity classes
ORM mapping: table class mapping
2. Write basic public classes
1. Database configuration file
driver=com.mysql.jdbc.Driver url=jdbc:mariadb://localhost:3306/smbms?useSSL=false&useUnicode=true&characterEncoding=utf-8 username=root password=123456
2. Create entity class
User class
import java.util.Date;
public class User {
private Integer id;
private String userCode;
private String userName;
private String userPassword;
private Integer gender;
private Date birthday;
private String phone;
private String address;
private Integer userRole;
private Integer createBy;//creator
private Date creationDate;
private Integer modifyBy;//Updater
private Date modifyDate;//Update time
private Integer age;//Age
private String userRoleName;//User role name
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 getCreateBy() {
return createBy;
}
public void setCreateBy(Integer createBy) {
this.createBy = createBy;
}
public Integer getCreationDate() {
return creationDate;
}
public void setCreationDate(Integer 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;
}
public void setAge(Integer age) {
this.age = age;
}
public String getUserRoleName() {
return userRoleName;
}
public void setUserRoleName(String userRoleName) {
this.userRoleName = userRoleName;
}
}
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;
}
}
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;
}
}
3. Write the public class of the database
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 loaded when classes are loaded
static {
Properties properties = new Properties();
//Read the corresponding resources through the class loader
InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
//properties to read the contents of the file
try {
properties.load(is);
} catch (IOException e) {
e.printStackTrace();
}
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
}
//Get a link to the database
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, password, username);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//Write query public methods
public static ResultSet execute(Connection conn, String sql, PreparedStatement preparedStatement, Object[] params, ResultSet resultSet) throws SQLException {
//Precompiled sql can be executed directly later
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
resultSet = preparedStatement.executeQuery();
return resultSet;
}
//Prepare public methods for addition, deletion, modification and query
public static int execute(Connection conn, PreparedStatement preparedStatement, String sql, Object[] params) throws SQLException {
//Precompiled sql can be executed directly later
int updateRow;
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
updateRow = preparedStatement.executeUpdate();
return updateRow;
}
//Release resources
public static boolean closeResource(Connection conn, PreparedStatement preparedStatement, ResultSet resultset) {
boolean flag = true;
if (conn != null) {
try {
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
flag = false;
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
preparedStatement = null;
} catch (SQLException e) {
e.printStackTrace();
flag = false;
}
}
if (resultset != null) {
try {
resultset.close();
resultset = null;
} catch (SQLException e) {
e.printStackTrace();
flag = false;
}
}
return flag;
}
}
4. Write character encoding filter
import javax.servlet.*;
import java.io.IOException;
public class CharacterEncodingFilter implements Filter {
public void init(FilterConfig filterConfig) throws ServletException {
}
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
servletRequest.setCharacterEncoding("utf-8");
servletResponse.setCharacterEncoding("utf-8");
filterChain.doFilter(servletRequest,servletResponse);
}
public void destroy() {
}
}
<filter>
<filter-name>CharacterEncodingFilter</filter-name>
<filter-class>com.gulao.filter.CharacterEncodingFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>CharacterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
-
Import static resources
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-0x3k6dvx-163868667725) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmnzg4ubmv0l2nzbnoxmjmjm =, size_16, color_ffffff, t_70. PNG)]
2. Implementation of login function
- Write front page
- Set home page
1. Set welcome home page
<welcome-file-list>
<welcome-file>login.jsp</welcome-file>
</welcome-file-list>
- Write dao layer login user login interface
public User getLoginUser(Connection connection, String userCode) throws Exception; 12
- Write the implementation class of dao layer interface
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import dao.BaseDao;
import pojo.User;
public class UserDaoImpl implements UserDao{
//The persistence layer only queries the contents of the database
public User getLoginUser(Connection connection, String userCode) throws Exception{
//Prepare three objects
PreparedStatement pstm = null;
ResultSet rs = null;
User user = null;
//Determine whether the connection is successful
if(null != connection){
String sql = "select * from smbms_user where userCode=?";
Object[] params = {userCode};
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);
}
return user;
}
}
- Business layer interface
//User login public User login(String userCode, String userPassword); 123
- Business layer implementation class
import java.sql.Connection;
//import org.junit.Test;
import dao.BaseDao;
import dao.user.UserDao;
import dao.user.UserDaoImpl;
import pojo.User;
public class UserServiceImpl implements UserService{
//The business layer will call the Dao layer, so we need to introduce the Dao layer (focus)
//Only the corresponding business is processed
private UserDao userDao;
public UserServiceImpl(){
userDao = new UserDaoImpl();
}
public User login(String userCode,String userPassword) {
// TODO Auto-generated method stub
Connection connection = null;
//Call the corresponding specific database operation through the business layer
User user = null;
try {
connection = BaseDao.getConnection();
user = userDao.getLoginUser(connection, userCode);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
BaseDao.closeResource(connection, null, null);
}
return user;
}
/*@Test
public void test() {
UserServiceImpl userService = new UserServiceImpl();
String userCode = "admin";
String userPassword = "12345678";
User admin = userService.login(userCode, userPassword);
System.out.println(admin.getUserPassword());
}
*/
}
Create a constant class under the util package
package com.gulao.util;
public class Constants {
public static final String USER_SESSION = "userSession";
}
Writing servlets
import com.gulao.pojo.User;
import com.gulao.service.user.UserService;
import com.gulao.service.user.UserServiceImpl;
import com.gulao.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 LoginServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//Get the information from the login page
String userCode = req.getParameter("userCode");
String userPassword = req.getParameter("userPassword");
//Compare with the password in the database and adjust the business layer
UserService userService = new UserServiceImpl();
User user = userService.login(userCode, userPassword);
if (user != null && userPassword.equals(user.getUserPassword()){
//Put the user's information in the session
req.getSession().setAttribute(Constants.USER_SESSION,user);
//Jump to home page
resp.sendRedirect("jsp/frame.jsp");
}else {
//If the account password is wrong, you will return to the login interface and be prompted with an error
req.setAttribute("error","Wrong user name and 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>LoginServlet</servlet-name> <servlet-class>com.kuang.servlet.user.LoginServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>LoginServlet</servlet-name> <url-pattern>/login.do</url-pattern> </servlet-mapping>
- Test access to ensure the success of the above functions
- Relationships at all levels:
- Servlet layer: receive page parameters, call the business Service layer, and then forward the view or return information
- Service layer: call the corresponding database operation Dao layer
- Dao layer: the persistence layer performs specific underlying operations such as database query
3. Login function optimization
Logout function
Idea: remove the session and return to the login page
public class LogoutServlet extends HttpServlet {
public void doPOST(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//Clear session
request.getSession().removeAttribute(Constants.USER_SESSION);
response.sendRedirect(request.getContextPath()+"/login.jsp");//Return to login page
}}
Register xml
<servlet>
<servlet-name>LogoutServlet</servlet-name>
<servlet-class>com.gulao.servlet.user.LogoutServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>LogoutServlet</servlet-name>
<url-pattern>/jsp/logout.do</url-pattern>
</servlet-mapping>
4. Login interception optimization
Write a filter and register it
import java.io.IOException;
import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import pojo.User;
import util.Constants;
public class SysFilter implements Filter{
public void init(FilterConfig filterConfig) throws ServletException{
}
@Override
public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain)
throws IOException, ServletException {
// Method stub automatically generated by TODO
HttpServletRequest request = (HttpServletRequest)req;
HttpServletResponse response = (HttpServletResponse)resp;
//Filter to 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("/smbms/error.jsp");
}else {
chain.doFilter(req, resp);
}
}
@Override
public void destroy() {
// Method stub automatically generated by TODO
}
}
Register xml
<!-- User login filter -->
<filter>
<filter-name>SysFilter</filter-name>
<filter-class>com.gulao.filter.SysFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>SysFilter</filter-name>
<url-pattern>/jsp/*</url-pattern>
</filter-mapping>
Test, login, logout and permission should be OK
5. Password modification
- Import front-end material
<li><a href="${pageContext.request.contextPath }/jsp/pwdmodify.jsp">Password modification</a></li>
- Write the project from the bottom up
- UserDao interface
//Modify current user password public int updatePwd(Connection connection,int id,String password)throws SQLException, Exception;
- UserDao interface implementation class
@Override//Modify current password
public int updatePwd(Connection connection, int id, String password) throws Exception {
// Method stub automatically generated by TODO
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, pstm, sql, params);
BaseDao.closeResource(null, pstm, null);
}
return execute;
}
- UserService layer
public boolean updatePwd(int id,String password)throws SQLException, Exception;
- UserService implementation class
public boolean updatePwd(int id, String password) throws SQLException, Exception {
// Method stub automatically generated by TODO
Connection connection = null;
boolean flag = false;
//Change Password
try {
connection = BaseDao.getConnection();
if(userDao.updatePwd(connection, id, password)>0) {
flag = true;
}
} catch (SQLException e) {
// catch block automatically generated by TODO
e.printStackTrace();
} finally {
BaseDao.closeResource(connection, null, null);
}
return flag;
}
- servlet remember to realize reuse and extract methods!
Write your own mapping class and implementation class in dao layer and service layer
Below is the body of the servlet layer
public class UserServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// Method stub automatically generated by TODO
String method = req.getParameter("method");
if (method.equals("savepwd") && method != null) {
this.updatePwd(req, resp);
}
//Realize reuse~~~~~~
// If you want to add, delete, or modify a query, you can directly use if (method! = "savepwd" & & method! = null);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// Method stub automatically generated by TODO
doGet(req, resp);
}
public void updatePwd(HttpServletRequest req, HttpServletResponse resp) {
// Obtain user id through session
Object o = req.getSession().getAttribute(Constants.USER_SESSION);
String newpassword = req.getParameter("newpassword");
boolean flag = false;
if (o != null && newpassword != null) {
UserService userService = new UserServiceImpl();
try {
flag = userService.updatePwd(((User) o).getId(), newpassword);
} catch (SQLException e) {
// catch block automatically generated by TODO
e.printStackTrace();
} catch (Exception e) {
// catch block automatically generated by TODO
e.printStackTrace();
}
if (flag) {
req.setAttribute("message", "The password has been modified successfully. Please exit and log in with a new password");
// The password is modified successfully, and the session is removed (the password cannot be modified again after removal, so it is recommended not to remove it)
req.getSession().removeAttribute(Constants.USER_SESSION);
} else {
// Password modification failed
req.setAttribute("message", "Password modification failed");
}
} else {
// There is a problem with password modification
req.setAttribute("message", "There is a problem with the new password");
}
try {
req.getRequestDispatcher("/jsp/pwdmodify.jsp").forward(req, resp);
} catch (ServletException e) {
// catch block automatically generated by TODO
e.printStackTrace();
} catch (IOException e) {
// catch block automatically generated by TODO
e.printStackTrace();
}
}
}
Register xml
<servlet> <servlet-name>UserServlet</servlet-name> <servlet-class>servlet.user.UserServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>UserServlet</servlet-name> <url-pattern>/jsp/user.do</url-pattern> </servlet-mapping>
- test
6. Optimize password modification using Ajax
- Alibaba fastjson
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.68</version>
</dependency>
- Background code modification
import com.alibaba.fastjson.JSONArray;
import com.csnz.pojo.User;
import com.csnz.service.User.UserServiceImpl;
import com.csnz.util.Constants;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.jsp.PageContext;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.Map;
//Realize Servlet reuse
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")){
this.savePwd(req,resp);
}else if(method.equals("pwdmodify")){
this.verifyPwd(req,resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
//User password modification method
public void savePwd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//Get ID from Session
Object obj = req.getSession().getAttribute(Constants.USER_SESSION);
//Get the new password from the front page
String newpassword = req.getParameter("newpassword");
//First judge whether it is not empty, and then compare whether the passwords are equal
if(obj != null && newpassword != null){
User user = (User) obj;
UserServiceImpl userService = new UserServiceImpl();
//Change the password and return the result
boolean flag = userService.updatePassword(user.getId(), newpassword);
//If the password modification is successful, remove the current session
if(flag){
req.setAttribute("message","The password is modified successfully. Please log in with the new password!");
req.getSession().removeAttribute(Constants.USER_SESSION);
}else{
req.setAttribute("message","Password modification failed. The new password does not meet the specification");
}
}else{
req.setAttribute("message","New password cannot be empty!");
}
//After modification, redirect to this modification page
req.getRequestDispatcher("/jsp/pwdmodify.jsp").forward(req,resp);
}
//Method of verifying password
public void verifyPwd(HttpServletRequest req, HttpServletResponse resp)throws ServletException, IOException{
//Still get the ID from the session
Object obj = req.getSession().getAttribute(Constants.USER_SESSION);
//Get the old password from the front end
String oldpassword = req.getParameter("oldpassword");
//Store the results in the map collection for Ajax to use
Map<String, String> resultMap = new HashMap<>();
//The next step is to use result to match the Ajax code in js
if(obj == null){
//Description the session was removed or not logged in | logged out
resultMap.put("result","sessionerror");
}else if(oldpassword == null){
//The password entered in the front end is empty
resultMap.put("result","error");
}else {
//If the old password is the same as the password from the front end
if(((User)obj).getUserPassword().equals(oldpassword)){
resultMap.put("result","true");
}else{
//The password entered in the front end is different from the real password
resultMap.put("result","false");
}
}
//The above has been encapsulated, and now it needs to be passed to Ajax. The format is json, so we have to convert the format
resp.setContentType("application/json");//Change the type of application to json
PrintWriter writer = resp.getWriter();
//The purpose of JSON tool class of JSONArray Alibaba is to convert format
writer.write(JSONArray.toJSONString(resultMap));
writer.flush();
writer.close();
}
}
- test
1. User management implementation
- Import pagination tool class - PageSupport
- User list page import - userlist.jsp
2. Get the number of users
5.1 create a new pagination tool class PageSupport
public class PageSupport {
//The current page is from user input
private int currentPageNo = 1;
//Total quantity (table)
private int totalCount = 0;
//Page capacity
private int pageSize = 0;
//The total number of pages displayed is the total number of tables / single page capacity + 1
private int totalPageCount =1;
public int getCurrentPageNo() {
return currentPageNo;
}
public void setCurrentPageNo(int currentPageNo) {
if(currentPageNo>0){
this.currentPageNo = currentPageNo;
}
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
if(totalCount>0){
this.totalCount = totalCount;
this.setTotalPageCountByRs();
}
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
if(pageSize>0){
this.pageSize = pageSize;
}
}
public int getTotalPageCount() {
return totalPageCount;
}
public void setTotalPageCount(int totalPageCount) {
this.totalPageCount = totalPageCount;
}
public void setTotalPageCountByRs(){
if(this.totalCount % this.pageSize == 0){
this.totalPageCount = this.totalCount / this.pageSize;
}else if(this.totalCount % this.pageSize > 0){
this.totalPageCount = this.totalCount / this.pageSize +1;
}else{
this.totalPageCount = 0;
}
}
}
5.2 user list page import
[external chain picture transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-jkih557o-163868667726) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmnzg4ubmv0l2nzbnoxmjmjm =, size_16, color_ffff, t_70. PNG)]
5.3 obtaining the number of users involves multi table query
5.3.1 writing UserDao layer
//Query the total number of users by user name or role
public int getUserCounts(Connection conn,String username,int userRole)throws SQLException;
5.3.2 writing UserDaoImpl layer
//Query the total number of users by user name or role
@Override
public int getUserCounts(Connection conn, String username, int userRole) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
int count = 0;
if(conn!=null){
//SQL statements need to be spliced because they join multi table queries
StringBuffer sql = new StringBuffer();
//The default is the total number of joint queries between two tables
sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole = r.id");
//Create a collection to store parameters
ArrayList<Object> list = new ArrayList<>();
if(username!=null){
sql.append(" and u.userName like ?");
list.add("%"+username+"%");//The default subscript is 0
}
if(userRole>0 & userRole<4){
sql.append(" and u.userRole = ?");
list.add(userRole);//The default subscript is 1
}
//Convert list to array
Object[] arrays = list.toArray();
System.out.println("Spliced sql sentence:"+sql.toString());
//Execute sql statement
rs = BaseDao.executeQuery(conn, sql.toString(), pstm, arrays, rs);
//Traversal result set
if(rs.next()){
//Get quantity from result set
count = rs.getInt("count");
}
//Finally close the resource connection
BaseDao.closeResource(null,pstm,rs);
}
return count;
}
5.3.3 writing UserService layer
//User management - number of query records
public abstract int getUserCounts(String username,int userRole);
5.3.4 writing UserServiceImpl layer
//User management - number of query records
@Override
public int getUserCounts(String username, int userRole) {
Connection conn = null;
int userCounts = 0;
try {
//Get connection
conn = BaseDao.getConnection();
//Execute sql statement
userCounts = userDao.getUserCounts(conn, username, userRole);
}catch (SQLException e){
e.printStackTrace();
}finally {
BaseDao.closeResource(conn,null,null);
return userCounts;
}
}
5.4 get user list
5.4.1 writing UserDao layer
//Get user list according to condition query
public abstract List<User> getUserList(Connection conn,String username,int userRole,int currentPageNo,int pageSize)throws SQLException;
5.4.2 write UserDaoImpl layer
//Query the total number of users by user name or role
@Override
public int getUserCounts(Connection conn, String username, int userRole) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
int count = 0;
if(conn!=null){
//SQL statements need to be spliced because they join multi table queries
StringBuffer sql = new StringBuffer();
//The default is the total number of joint queries between two tables
sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole = r.id");
//Create a collection to store parameters
ArrayList<Object> list = new ArrayList<>();
if(username!=null){
sql.append(" and u.userName like ?");
list.add("%"+username+"%");//The default subscript is 0
}
if(userRole>0 & userRole<4){
sql.append(" and u.userRole = ?");
list.add(userRole);//The default subscript is 1
}
//Convert list to array
Object[] arrays = list.toArray();
System.out.println("Spliced sql sentence:"+sql.toString());
//Execute sql statement
rs = BaseDao.executeQuery(conn, sql.toString(), pstm, arrays, rs);
//Traversal result set
if(rs.next()){
//Get quantity from result set
count = rs.getInt("count");
}
//Finally close the resource connection
BaseDao.closeResource(null,pstm,rs);
}
return count;
}
//Get user list according to condition query
@Override
public List<User> getUserList(Connection conn, String username, int userRole, int currentPageNo, int pageSize) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
List<User> userList = new ArrayList<User>();
if(conn!=null){
//SQL statements need to be spliced because they join multi table queries
StringBuffer sql = new StringBuffer();
//The default is the total number of joint queries between two tables
sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
//Create a collection to store parameters
List<Object> list = new ArrayList<>();
if(username!=null){
sql.append(" and u.userName like ?");
list.add("%"+username+"%");//The default subscript is 0
}
if(userRole>0 & userRole<4){
sql.append(" and u.userRole = ?");
list.add(userRole);//The default subscript is 1
}
//The total number of limit StartIndex and PageSize used for paging in the database
//Current page = (current page - 1) * page size
sql.append(" order by u.creationDate DESC limit ?,?");
currentPageNo = (currentPageNo-1)*pageSize;
list.add(currentPageNo);
list.add(pageSize);
Object[] params = list.toArray();
System.out.println("getUserList Statement of"+sql.toString());
//Execute sql
rs = BaseDao.executeQuery(conn, sql.toString(), pstm, params, rs);
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.setUserRoleName(rs.getString("userRoleName"));
user.setUserRole(rs.getInt("userRole"));
userList.add(user);
}
BaseDao.closeResource(null,pstm,rs);
}
return userList;
}
5.4.3 writing UserService layer
//Query user list by criteria
public abstract List<User> getUserList(String QueryUserName,int QueryUserRole,int currentPageNo,int pageSize);
5.4.4 writing UserServiceImpl layer
//Query user list by criteria
@Override
public List<User> getUserList(String QueryUserName, int QueryUserRole, int currentPageNo, int pageSize) {
Connection conn = null;
List<User> userList = null;
//Get database connection
try {
conn = BaseDao.getConnection();
userList = userDao.getUserList(conn, QueryUserName, QueryUserRole, currentPageNo, pageSize);
}catch (SQLException e){
e.printStackTrace();
}finally {
BaseDao.closeResource(conn,null,null);
return userList;
}
}
5.5 get user role list
In order to unify responsibilities, the operations of roles are placed in a separate package, corresponding to POJO s
5.5.1 write RoleDao layer
import com.csnz.pojo.Role;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public interface RoleDao {
//Get role list
public abstract List<Role> getRoleList(Connection conn)throws SQLException;
}
5.5.2 write RoleDaoImpl layer
import com.csnz.dao.BaseDao;
import com.csnz.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 {
//Get role list
@Override
public List<Role> getRoleList(Connection conn) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
ArrayList<Role> roles = new ArrayList<>();
if(conn != null){
String sql = "select * from smbms_role";
Object[] params = {};
rs = BaseDao.executeQuery(conn, sql, pstm, params, rs);
while(rs.next()){
Role role = new Role();
role.setId(rs.getInt("id"));
role.setRoleName(rs.getString("roleName"));
role.setRoleCode(rs.getString("roleCode"));
roles.add(role);
}
}
BaseDao.closeResource(null,pstm,rs);
return roles;
}
}
5.5.3 write RoleService layer
import com.csnz.pojo.Role;
import java.util.List;
public interface RoleService {
//Get role list
public abstract List<Role> getRoleList();
}
5.5.4 write RoleServiceImpl layer
import com.csnz.dao.BaseDao;
import com.csnz.dao.Role.RoleDao;
import com.csnz.dao.Role.RoleDaoImpl;
import com.csnz.pojo.Role;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class RoleServiceImpl implements RoleService {
//Business layer calls persistence layer
private RoleDao roleDao = null;
public RoleServiceImpl(){
this.roleDao =new RoleDaoImpl();
}
@Override
public List<Role> getRoleList() {
Connection conn = null;
List<Role> roleList = null;
try {
//Get database connection
conn = BaseDao.getConnection();
roleList = roleDao.getRoleList(conn);
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeResource(conn,null,null);
return roleList;
}
}
}
5.5.5 servlets displayed by users
- Get the data of the user's front end (query)
- Whether the request needs to be executed depends on the value of the parameter
- In order to realize paging, you need to calculate the current page and total page, page size
- User list display
- Back to front end
//Key points and difficulties
private void query(HttpServletRequest req, HttpServletResponse resp) {
// Method stub automatically generated by TODO
//Query user list
//Get data from the front end
//Query user list
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;
//First, the request must go to the first page, and the page size is fixed
//Set page capacity
int pageSize = 5;//Set it in the configuration file for easy modification later
//Current page number
int currentPageNo = 1;
if(queryUserName == null){
queryUserName = "";
}
if(temp != null && !temp.equals("")){
queryUserRole = Integer.parseInt(temp);
}
if(pageIndex != null) {
currentPageNo = Integer.parseInt(pageIndex);
}
//Get total number of users (paging) Previous page: next page)
//Total quantity (table)
int totalCount = userService.getUserCount(queryUserName,queryUserRole);
//Total pages support
PageSupport pageSupport = new PageSupport();
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
pageSupport.setTotalCount(totalCount);
int totalPageCount =pageSupport.getTotalPageCount();//How many pages are there altogether
//(totalCount+pageSize-1/pageSize) rounding
// pageSupport.getTotalCount()
//System.out.println("totalCount ="+totalCount);
//System.out.println("pageSize ="+pageSize);
//System.out.println("totalPageCount ="+totalPageCount);
//Control first and last pages
//If the page is less than 1, the first page is displayed
if(currentPageNo < 1) {
currentPageNo = 1;
}else if(currentPageNo > totalPageCount) {//If the page is larger than the last page, the last page is displayed
currentPageNo =totalPageCount;
}
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) {
// catch block automatically generated by TODO
e.printStackTrace();
} catch (IOException e) {
// catch block automatically generated by TODO
e.printStackTrace();
}
}
5.6 get the servlet displayed by the user
3.6.1 obtain the data input by the user at the front end
3.6.2 judge whether the request is executed according to the value of the parameter
3.6.3 calculate the conditions required for paging (current page, total page, page capacity)
3.6.4 user list display
3.6.5 return to front-end display
Add new method in UserServlet
//Method of querying user list
public void query(HttpServletRequest req, HttpServletResponse resp){
//Query user list
//Get data from the front end
String queryUserName = req.getParameter("queryName");
String temp = req.getParameter("queryUserRole");//Values are 0, 1, 2, 3
String pageIndex = req.getParameter("pageIndex");
int queryUserRole = 0;
//Get user list
UserServiceImpl userService = new UserServiceImpl();
List<User> userList = null;
int currentPageNo = 1;
int pageSize = 5;
if(queryUserName == null){
queryUserName = "";
}
if(temp!=null && !temp.equals("")){
queryUserRole = Integer.parseInt(temp);
}
if(pageIndex!=null){
currentPageNo = Integer.parseInt(pageIndex);
}
//Get total number of users page: Previous page next page
int totalCount = userService.getUserCounts(queryUserName, queryUserRole);
//Total pages support
PageSupport pageSupport = new PageSupport();
System.out.println("Current page:"+currentPageNo);
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
System.out.println("Get total number of users"+totalCount);
pageSupport.setTotalCount(totalCount);
//Total pages
int totalPageCount = pageSupport.getTotalPageCount();
//Control first and last pages
//If the number of pages is less than 1, the first page will be displayed, and if the number of pages is greater than the last page, the last page will be displayed
if(currentPageNo<1){
currentPageNo =1;
}else if(currentPageNo>totalPageCount){
currentPageNo = totalPageCount;
}
System.out.println("return UserList Data test for"+queryUserName+":"+queryUserRole+":"+currentPageNo+":"+pageSize);
//Get user list display
userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize);
//Transfer data to the front end
System.out.println(userList);
// for (User user : userList) {
// System.out.println(user.toString());
// }
req.setAttribute("userList",userList);
RoleServiceImpl roleService = new RoleServiceImpl();
//All roles
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);
//Return to front end
try {
req.getRequestDispatcher("userlist.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
And modify the doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("savePwd")){
this.savePwd(req,resp);
}else if(method.equals("pwdmodify")){
this.verifyPwd(req,resp);
}else if(method.equals("query")){
this.query(req,resp);
}
}
5.7 user management module sub module - add user function
5.7.1: view userlist.jsp
[external chain picture transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-nytwdgme-163868667726) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmnzg4ubmv0l2nzbnoxmjmjm =, size_16, color_ffff, t_70. PNG)]
5.7.2: create (import) jsp file according to the path
5.7.3: Ideas
Specific implementation steps
Write from the bottom
dao layer
//Sub module in user management module - add user
public abstract int addUser(Connection conn,User user)throws SQLException;
12
dao layer implementation class
//Sub module in user management module - add user
public int addUser(Connection conn,User user)throws SQLException{
PreparedStatement pstm = null;
int updateRows = 0;
if(conn != null){
String sql = "insert into smbms_user (userCode,userName,userPassword,gender,birthday,phone,address,userRole,createdBy,creationDate)values(?,?,?,?,?,?,?,?,?,?)";
Object[] params ={user.getUserRole(),user.getUserName(),user.getUserPassword(),user.getGender(),user.getBirthday(),user.getPhone(),user.getAddress(),user.getUserRole(),user.getCreatedBy(),user.getCreateDate()};
//Execute sql and return execution results (number of successful statements)
updateRows= BaseDao.execute(conn,sql,pstm,params);
//Release resources
BaseDao.closeResource(null,pstm,null);
}
return updateRows;
}
1234567891011121314
service layer
//Sub module in user management module - add user
public abstract int addUser(User user);
12
The serviceImpl layer controls transactions
@Override
public boolean addUser(User user) {
Connection conn = null;
boolean flag = false;
try {
//Get database connection
conn = BaseDao.getConnection();
//Enable JDBC transaction management
conn.setAutoCommit(false);
//The Service layer calls the dao layer method to add users
int updateRows = userDao.addUser(conn, user);
conn.commit();
if(updateRows > 0){
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
conn.rollback();
}finally {
//Release connection
BaseDao.closeResource(conn,null,null);
return flag;
}
}
123456789101112131415161718192021222324
Add a method to determine whether the user code exists in the reusable Servlet class
//User management module sub module (verify whether the user code already exists)
public void ifExist(HttpServletRequest req, HttpServletResponse resp) throws IOException {
//Get the user code input by the front end
String userCode = req.getParameter("userCode");
UserServiceImpl userService = new UserServiceImpl();
User isNullUser = userService.login(userCode, "");
//Store the results in the map collection for Ajax to use
Map<String, String> resultMap = new HashMap<>();
//Determine whether this user code already exists
boolean flag = isNullUser != null ? true : false;
if(flag){
//User code exists
//Store information in map
resultMap.put("userCode","exist");
}
//The above has been encapsulated, and now it needs to be passed to Ajax. The format is json, so we have to convert the format
resp.setContentType("application/json");//Change the type of application to json
PrintWriter writer = resp.getWriter();
//The purpose of JSON tool class of JSONArray Alibaba is to convert format
writer.write(JSONArray.toJSONString(resultMap));
writer.flush();
writer.close();
}
123456789101112131415161718192021222324
And judge whether to call and execute ifExist method in doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("savePwd")){
this.savePwd(req,resp);
}else if(method.equals("pwdmodify")){
this.verifyPwd(req,resp);
}else if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("add")){
this.add(req,resp);
}else if(method.equals("getRoleList")){
this.getRoleList(req,resp);
}else if(method.equals("ifExist")){
this.ifExist(req,resp);
}
}
1234567891011121314151617
5.8 user management module sub module - delete user function
5.8.1 viewing userlist.jsp
<span><a class="deleteUser" href="javascript:;" userid=${user.id } username=${user.userName }><img src="${pageContext.request.contextPath }/images/schu.png" alt="delete" title="delete"/></a></span>
1
5.8.2 view userlist.js
//Click the delete button on the user management page to pop up the delete box (userlist.jsp)
function deleteUser(obj){
$.ajax({
type:"GET",
url:path+"/jsp/user.do",
data:{method:"deluser",uid:obj.attr("userid")},
dataType:"json",
success:function(data){
if(data.delResult == "true"){//Delete succeeded: delete row
cancleBtn();
obj.parents("tr").remove();
}else if(data.delResult == "false"){//Deletion failed
//alert("sorry, failed to delete user [" + obj.attr("username") + "]));
changeDLGContent("Sorry, delete user["+obj.attr("username")+"]fail");
}else if(data.delResult == "notexist"){
//alert("sorry, the user [" + obj.attr("username") + "] does not exist");
changeDLGContent("Sorry, user["+obj.attr("username")+"]non-existent");
}
},
error:function(data){
//alert("sorry, deletion failed");
changeDLGContent("Sorry, deletion failed");
}
});
}
Ideas JSP calls Ajax calls Servlet calls Service calls Dao calls jdbc
Then write from the bottom up
5.8.3 Dao layer
//Sub module in user management module - delete user
public abstract boolean deleteUser(Connection conn,int userId)throws SQLException;
5.8.4 Dao layer implementation class
//Sub module in user management module - delete user
@Override
public boolean deleteUser(Connection conn, int userCode)throws SQLException {
PreparedStatement pstm = null;
boolean flag = false;
if(conn != null){
String sql = "delete from smbms_user where id = ?";
Object[] params = {userCode};
//Execute sql and return execution results (number of successful statements)
int updateRows= BaseDao.execute(conn,sql,pstm,params);
if(updateRows>0){
flag = true;
}
//Release resources
BaseDao.closeResource(null,pstm,null);
}
return flag;
}
5.8.5 Service layer
//Sub module in user management module - delete user
public abstract boolean deleteUser(int userId);
12
5.8.6 the service layer implementation class should enable transactions
//Sub module in user management module - delete user
@Override
public boolean deleteUser(int userId) {
boolean flag = false;
Connection conn = null;
try {
//Get database connection
conn = BaseDao.getConnection();
//Open transaction
conn.setAutoCommit(false);
flag = userDao.deleteUser(conn, userId);
//Commit transaction
conn.commit();
}catch (Exception e){
e.printStackTrace();
//Transaction rollback
conn.rollback();
}finally {
//Release connection
BaseDao.closeResource(conn,null,null);
return flag;
}
}
5.8.7 write the delete user function of Servlet class
doGet method add
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("savePwd")){
this.savePwd(req,resp);
}else if(method.equals("pwdmodify")){
this.verifyPwd(req,resp);
}else if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("add")){
this.add(req,resp);
}else if(method.equals("getRoleList")){
this.getRoleList(req,resp);
}else if(method.equals("ifExist")){
this.ifExist(req,resp);
}else if(method.equals("delUser")){
this.deleteUser(req,resp);
}
}
123456789101112131415161718
Add delete method
//Sub module in user management module - delete user
public void deleteUser(HttpServletRequest req, HttpServletResponse resp) throws IOException {
//Get the information of the user to be deleted from the front end
String userid = req.getParameter("uid");
int delId = 0;
//First conversion
try {
delId= Integer.parseInt(userid);
}catch (Exception e){
e.printStackTrace();
delId = 0;
}
//Store the results in the map collection for Ajax to use
Map<String, String> resultMap = new HashMap<>();
if(delId<=0){
resultMap.put("delResult","notexist");
}else {
UserServiceImpl userService = new UserServiceImpl();
if(userService.deleteUser(delId)){
resultMap.put("delResult","true");
}else {
resultMap.put("delResult", "false");
}
}
//The above has been encapsulated, and now it needs to be passed to Ajax. The format is json, so we have to convert the format
resp.setContentType("application/json");//Change the type of application to json
PrintWriter writer = resp.getWriter();
//The purpose of JSON tool class of JSONArray Alibaba is to convert format
writer.write(JSONArray.toJSONString(resultMap));
writer.flush();
writer.close();
}
5.9 user management module sub module - modify user information function
Click the button to observe the url address of the page
http://localhost:8080/smbms_war_exploded/jsp/user.do?method=modify&uid=20 1
View js related code
$(".modifyUser").on("click",function(){
var obj = $(this);
window.location.href=path+"/jsp/user.do?method=modify&uid="+ obj.attr("userid");
});
1234
Or that sentence: functions are implemented from the bottom
5.9.1: Dao layer
Here, a findById method is added to query the old data and display it in the modified page
//Query user information according to user id
public abstract User findById(Connection conn,int userId)throws SQLException;
//Sub module in user management module - change user information
public abstract boolean modify(Connection conn,int id,User user)throws SQLException;
1234
5.9.2: Dao layer implementation class
//Query user information according to user id
@Override
public User findById(Connection conn, int userId) throws SQLException {
User user = null;
PreparedStatement pstm = null;
ResultSet rs = null;
if(conn != null){
String sql = "select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.id = ? and u.userRole = r.id";
Object[] params ={userId};
rs = BaseDao.executeQuery(conn, sql, pstm, params, rs);
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.setCreateDate(rs.getTimestamp("creationDate"));
user.setModifyBy(rs.getInt("modifyBy"));
user.setModifyDate(rs.getTimestamp("modifyDate"));
user.setUserRoleName(rs.getString("userRoleName"));
}
//Release resources
BaseDao.closeResource(null,pstm,rs);
}
return user;
}
//Sub module in user management module - change user information
@Override
public boolean modify(Connection conn, int id,User user) throws SQLException {
boolean flag = false;
PreparedStatement pstm = null;
if(conn != null){
//Writing sql statements
String sql = "update smbms_user set userName = ?,gender = ?,birthday =?,phone = ?,address = ?,userRole = ?,modifyBy = ?,modifyDate = ? where id = ?";
Object[] params = {user.getUserName(),user.getGender(),user.getBirthday(),user.getPhone(),user.getAddress(),user.getUserRole(),user.getModifyBy(),user.getModifyDate(),id};
//Execute sql statement
int updateRows = BaseDao.execute(conn, sql, pstm, params);
if(updateRows>0){
flag = true;
}
//Release connection
BaseDao.closeResource(null,pstm,null);
}
return flag;
}
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
5.9.3: Service layer
//Query user information according to id
public abstract User findById(int userId);
//Sub module in user management module - change user information
public abstract boolean modify(int id,User user);
1234
5.9.4: Service layer implementation class
//Query user information according to id
@Override
public User findById(int userId) {
User user = null;
Connection conn = null;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
user = userDao.findById(conn, userId);
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally {
BaseDao.closeResource(conn,null,null);
return user;
}
}
//Sub module in user management module - change user information
@Override
public boolean modify(int id,User user) {
Connection conn = null;
boolean flag = false;
try {
conn = BaseDao.getConnection();
//Open transaction
conn.setAutoCommit(false);
flag = userDao.modify(conn, id,user);
//Commit transaction
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
//Transaction rollback
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally {
//Release resources
BaseDao.closeResource(conn,null,null);
return flag;
}
}
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950
5.9.5: Servlet layer
//Function in user management module - query user information according to id
public void findById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//Get the id of the user to be modified from the front end
String uId = req.getParameter("uid");
int userId = 0;
try {
userId = Integer.parseInt(uId);
}catch (Exception e){
e.printStackTrace();
}
UserServiceImpl userService = new UserServiceImpl();
//Query the user information to change
User user = userService.findById(userId);
//Save the user information in the request and let usermodify.jsp display it
req.setAttribute("user",user);
req.getRequestDispatcher("usermodify.jsp").forward(req,resp);
}
//Sub module in user management module - change user information
public void modify(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
//Get the id of the user to be modified from the front end
String uId = req.getParameter("uid");
int userId = 0;
try {
userId = Integer.parseInt(uId);
}catch (Exception e){
e.printStackTrace();
}
//Encapsulate information from a form that modifies information
User user = new User();
user.setUserName(req.getParameter("userName"));
user.setGender(Integer.parseInt(req.getParameter("gender")));
try {
user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(req.getParameter("birthday")));
}catch (ParseException e){
e.printStackTrace();
}
user.setPhone(req.getParameter("phone"));
user.setAddress(req.getParameter("address"));
user.setUserRole(Integer.parseInt(req.getParameter("userRole")));
//Note that these two parameters are not within the scope of filling in the form
user.setModifyDate(((User)req.getSession().getAttribute(Constants.USER_SESSION)).getId());
user.ModifyDate(new Date());
UserServiceImpl userService = new UserServiceImpl();
if(userService.modify(userId,user)){
//If the execution is successful, the web page is redirected to the user management page (that is, query the list of all users)
resp.sendRedirect(req.getContextPath()+"/jsp/user.do?method=query");
}else{
//Description failed to add. Forward to this page
req.getRequestDispatcher("usermodify.jsp").forward(req,resp);
}
}
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
At the same time, the doGet method adds [the external chain picture transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-3sdaklps-163868667727) (20210508092919393. PNG)]
6.0 user management module sub module - view user information function
Click the view user information button to observe whether the URL is still a Servlet request
The idea is to query the user information according to the id and then send it to the front-end page for display
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-muir9hog-163886667728) (20210508093029197. PNG)]
Writing servlets
//Sub module of user management module - query user information
public void viewUser(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//Get the id of the user to query from the front end
String id = req.getParameter("uid");
int userId = 0;
try {
userId = Integer.parseInt(id);
}catch (Exception e){
e.printStackTrace();
userId = 0;
}
//Call the method of querying user information according to id
UserServiceImpl userService = new UserServiceImpl();
User user = userService.findById(userId);
//Send this user to the page in front of the presentation for presentation
req.setAttribute("user",user);
//Jump to the front display page
req.getRequestDispatcher("userview.jsp").forward(req,resp);
}
12345678910111213141516171819
And modify the doGet method
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-mge3bguy-163886667728) (20210508094705160. PNG)]
6: Supplier management
6.1 establish supplier Servlet
Create a new Provider package under the Servlet package and a new ProviderServlet in the package
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-vsopgyxt-163886667729) (20210508095128112. PNG)]
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 ProviderServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
super.doGet(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
}
123456789101112131415161718
If you have a Servlet, you have to register first in case you forget to register later (in web.xml)
<!--Registered supplier Servlet-->
<servlet>
<servlet-name>ProviderServlet</servlet-name>
<servlet-class>com.csnz.servlet.Provider.ProviderServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProviderServlet</servlet-name>
<url-pattern>/jsp/provider.do</url-pattern>
</servlet-mapping>
123456789
We can see what the supplier management page looks like according to the URL
http://localhost:8080/smbms_war_exploded/jsp/provider.do?method=query 1
Modify ProviderServlet according to parameters
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 ProviderServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
//Method of querying supplier list
public void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//Return to the front page display
req.getRequestDispatcher("providerlist.jsp").forward(req,resp);
}
}
123456789101112131415161718192021222324252627
[external chain picture transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-6hxyirhw-163886667729) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmnzg4ubmv0l2nzbnoxmjmjm =, size_16, color_ffff, t_70. PNG)]
You can see the frame of the current page, but there is no value to display
Next, it's similar to the user management page!
6.2 construction related packages
6.2.1 create a new ProviderDao layer and an implementation class of ProviderDao
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-z2ikthz9-163868667729) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmnzg4ubmv0l2nzbnoxmjmxmjm =, size_16, color_ffffff, t_70. PNG)]
ProviderDao interface
public interface ProviderDao {
}
12
ProviderDaoImpl implementation class
public class ProviderDaoImpl implements ProviderDao {
}
12
6.2.2 new Service layer and implementation class
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-bpfb6vqi-163868667730) (20210508103849903. PNG)]
ProviderService interface
public interface ProviderService {
}
12
ProviderServiceImpl implementation class
public class ProviderServiceImpl implements ProviderService {
}
12
6.3 display the data on the page, and first realize the function of querying supplier list data
Or that sentence: write from the bottom up
Because pagination is involved, we need to write another one
Method to query the total number of suppliers by supplier code or supplier name
6.3.1 ProviderDao layer
//Query the total number of suppliers by supplier code or supplier name
public abstract int getProviderCounts(Connection conn,String queryProCode,String queryProName)throws SQLException;
//Query supplier data list
public abstract List<Provider> getProviderList(Connection conn,String ProCode,String ProName,int currentPageNo, int pageSize)throws SQLException;
123456
6.3.2 ProviderDaoImpl layer
@Override
public int getProviderCounts(Connection conn, String queryProCode, String queryProName) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
int ProviderCounts = 0;
if(conn != null){
//Use character splicing to generate sql statements
StringBuffer sql = new StringBuffer();
sql.append("select count(1) as ProviderCount from smbms_provider");
//parameter list
ArrayList<Object> paramsList = new ArrayList<>();
//Judge whether there are 2 parameters. If yes, add and
if(queryProCode != "" && queryProName != ""){
sql.append(" where ProCode = ? and ProName like ?");
paramsList.add(queryProCode);
paramsList.add("%"+queryProName+"%");
}else if(queryProCode !="" || queryProName != ""){
sql.append(" where");
if(queryProCode != ""){
sql.append(" ProCode = ?");
paramsList.add(queryProCode);
}
if(queryProName != ""){
sql.append(" ProName like ?");
paramsList.add("%"+queryProName+"%");
}
}
//sql splicing completed
System.out.println("Test ProviderDaoImpl -> "+sql.toString());
//Parameter list conversion
Object[] params = paramsList.toArray();
//Execute sql
rs = BaseDao.executeQuery(conn, sql.toString(), pstm, params,rs);
//Traversal result set
while (rs.next()){
ProviderCounts = rs.getInt("ProviderCount");
}
//Release resources
BaseDao.closeResource(null,pstm,rs);
}
System.out.println("ProviderDaoImpl-> ProviderCounts: "+ProviderCounts);
return ProviderCounts;
}
@Override
public List<Provider> getProviderList(Connection conn, String ProCode, String ProName,int currentPageNo, int pageSize) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
List<Provider> providers = new ArrayList<Provider>();
if(conn != null){
//Suppliers do not need to join tables to query sql parameters
StringBuffer sql = new StringBuffer();
sql.append("select * from smbms_provider");
//Create a parameter list. Note that the generic type here uses Object
List<Object> paramsList = new ArrayList<>();
//SQL statement splicing parameter assignment
// If both are not empty, sql needs to be spliced
if(ProCode != "" && ProName != ""){
sql.append(" where ProCode = ? and ProName like ?");
paramsList.add(ProCode);
paramsList.add("%"+ProName+"%");
}else if(ProCode != "" || ProName != ""){
sql.append(" where");
if(ProCode != ""){
sql.append(" ProCode = ?");
paramsList.add(ProCode);
}
if(ProName != ""){
sql.append(" ProName like ?");
paramsList.add("%"+ProName+"%");
}
}
//The total number of limit StartIndex and PageSize used for paging in the database
//Current page = (current page - 1) * page size
sql.append(" order by creationDate DESC limit ?,?");
currentPageNo = (currentPageNo-1)*pageSize;
paramsList.add(currentPageNo);
paramsList.add(pageSize);
//Convert parameter list
Object[] params = paramsList.toArray();
System.out.println("Test getProviderList -> "+sql);
//Execute sql
rs = BaseDao.executeQuery(conn, sql.toString(), pstm, params,rs);
//The traversal result set is encapsulated in a vendor
while (rs.next()){
Provider provider = new Provider();
provider.setId(rs.getInt("id"));
provider.setProCode(rs.getString("proCode"));
provider.setProName(rs.getString("proName"));
provider.setProDesc(rs.getString("proDesc"));
provider.setProContact(rs.getString("proContact"));
provider.setProPhone(rs.getString("proPhone"));
provider.setProAddress(rs.getString("proAddress"));
provider.setProFax(rs.getString("proFax"));
provider.setCreatedBy(rs.getInt("createdBy"));
provider.setCreationDate(rs.getTimestamp("creationDate"));
provider.setModifyDate(rs.getTimestamp("modifyDate"));
provider.setModifyBy(rs.getInt("modifyBy"));
//Add this vendor information to the list
providers.add(provider);
}
//Release resources
BaseDao.closeResource(null,pstm,rs);
}
return providers;
}
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110
6.3.3 ProviderService layer
//Query the total number of suppliers by supplier code or supplier name
public abstract int getProviderCounts(String queryProCode,String queryProName)throws SQLException;
//Query supplier data list
public abstract List<Provider> getProviderList(String ProCode, String ProName, int currentPageNo, int pageSize)throws SQLException;
123456
6.3.4 ProviderServiceImpl layer
import com.csnz.dao.BaseDao;
import com.csnz.dao.Provider.ProviderDao;
import com.csnz.dao.Provider.ProviderDaoImpl;
import com.csnz.pojo.Provider;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class ProviderServiceImpl implements ProviderService {
//Service layer calls dao layer
private ProviderDao providerDao;
public ProviderServiceImpl(){
providerDao = new ProviderDaoImpl();
}
//Query the total number of suppliers by supplier code or supplier name
@Override
public int getProviderCounts(String queryProCode, String queryProName) throws SQLException {
Connection conn = null;
int providerCounts = 0;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
providerCounts = providerDao.getProviderCounts(conn, queryProCode, queryProName);
conn.commit();
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return providerCounts;
}
}
@Override
public List<Provider> getProviderList(String ProCode, String ProName, int currentPageNo, int pageSize) throws SQLException {
List<Provider> providerList = null;
Connection conn = null;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
providerList = providerDao.getProviderList(conn, ProCode, ProName, currentPageNo, pageSize);
conn.commit();
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return providerList;
}
}
}
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
6.3.5 improve the query method of ProviderServlet layer
//Method of querying supplier list
public void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException, SQLException {
//Get the data from providerlist.jsp
String queryProCode = req.getParameter("queryProCode");
String queryProName = req.getParameter("queryProName");
String pageIndex = req.getParameter("pageIndex");
List<Provider> providerList = null;
int currentPageNo = 1;
int pageSize = 5;
//It is also necessary to judge whether the data transmitted from the front end is empty
if (queryProCode == null) {
queryProCode = "";
}
if (queryProName == null ){
queryProName = "";
}
if(pageIndex!=null){
currentPageNo = Integer.parseInt(pageIndex);
}
ProviderServiceImpl providerService = new ProviderServiceImpl();
//Get the number of qualified information
int providerCounts = providerService.getProviderCounts(queryProCode, queryProName);
//Total pages support
PageSupport pageSupport = new PageSupport();
System.out.println("currentPageNo: "+currentPageNo);
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
System.out.println("providerCounts:"+providerCounts);
pageSupport.setTotalCount(providerCounts);
//Total pages
int totalPageCount = pageSupport.getTotalPageCount();
//Total records
int totalCount = pageSupport.getTotalCount();
//Control first and last pages
//If the number of pages is less than 1, the first page will be displayed, and if the number of pages is greater than the last page, the last page will be displayed
if(currentPageNo<1){
currentPageNo =1;
}else if(currentPageNo>totalPageCount){
currentPageNo = totalPageCount;
}
//Get qualified information
providerList= providerService.getProviderList(queryProCode, queryProName, currentPageNo, pageSize);
//Store the information in requset so that it can be displayed on the front end
req.setAttribute("providerList",providerList);
req.setAttribute("totalCount",totalCount);
req.setAttribute("currentPageNo",currentPageNo);
req.setAttribute("totalPageCount",totalPageCount);
req.setAttribute("queryProCode",queryProCode);
req.setAttribute("queryProName",queryProName);
//Return to the front page display
req.getRequestDispatcher("providerlist.jsp").forward(req,resp);
}
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253
If you have written here, congratulations. You have taken another big step to run the server presentation page
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-dklitfun-163868667730) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmnzg4ubmv0l2nzbnoxmjmjm =, size_16, color_ffff, t_70. PNG)]
6.4 realize the function of adding suppliers
View providerlist.jsp
[external chain picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-1dbjrzoh-163868667730) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmnzg4ubmv0l2nzbnoxmjmjm =, size_16, color_ffff, t_70. PNG)]
It is found that this function enters provideradd.jsp, so
Hey, hey ~ let's go directly to this JSP file to view
Find the path of the form submission
<form id="providerForm" name="providerForm" method="post" action="${pageContext.request.contextPath }/jsp/provider.do">
<input type="hidden" name="method" value="add">
12
The following is the old saying... Function (add) is implemented from the bottom
6.4.1 write Dao layer
//How to add suppliers
public abstract boolean addProvider(Connection conn,Provider provider)throws SQLException;
12
6.4.2 writing Dao layer implementation classes
//How to add suppliers
@Override
public boolean addProvider(Connection conn, Provider provider) throws SQLException {
PreparedStatement pstm = null;
boolean flag = false;
if(conn != null){
//If the connection to the database is successful, write the sql statement
String sql = "insert into smbms_provider (proCode,proName,proContact,proPhone,proAddress,proFax,proDesc,createdBy,creationDate)values(?,?,?,?,?,?,?,?,?)";
Object[] params ={provider.getProCode(),provider.getProName(),provider.getProContact(),provider.getProPhone(),provider.getProAddress(),provider.getProFax(),provider.getProDesc(),provider.getCreatedBy(),provider.getCreationDate()};
//Execute sql statement
if(BaseDao.execute(conn, sql, pstm, params)>0){
flag = true;
}
//Release resources
BaseDao.closeResource(null,pstm,null);
}
return flag;
}
12345678910111213141516171819
6.4.3 writing Service layer
//How to add suppliers
public abstract boolean addProvider(Provider provider)throws SQLException;
12
6.4.4 writing ServiceImpl layer
//How to add suppliers
@Override
public boolean addProvider(Provider provider) {
Connection conn = null;
boolean flag = false;
try {
//Get database connection
conn = BaseDao.getConnection();
//Open transaction
conn.setAutoCommit(false);
//Execution method
flag = providerDao.addProvider(conn, provider);
//Commit transaction
conn.commit();
}catch (Exception e){
e.printStackTrace();
//Transaction rollback
conn.rollback();
}finally {
//Release resources
BaseDao.closeResource(conn,null,null);
return flag;
}
}
123456789101112131415161718192021222324
6.4.5 write the add method of Servlet class
//Add vendor method
public void add(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
//Get supplier information from the front end
String proCode = req.getParameter("proCode");
String proName = req.getParameter("proName");
String proContact = req.getParameter("proContact");
String proPhone = req.getParameter("proPhone");
String proAddress = req.getParameter("proAddress");
String proFax = req.getParameter("proFax");
String proDesc = req.getParameter("proDesc");
//The following parameters can be obtained by yourself
int createdBy = ((User)req.getSession().getAttribute(Constants.USER_SESSION)).getId();
//Encapsulate information into a vendor object
Provider provider = new Provider();
provider.setProCode(proCode);
provider.setProName(proName);
provider.setProContact(proContact);
provider.setProPhone(proPhone);
provider.setProAddress(proAddress);
provider.setProFax(proFax);
provider.setProDesc(proDesc);
provider.setCreatedBy(createdBy);
provider.setCreationDate(new Date());
//Call service layer method
ProviderServiceImpl providerService = new ProviderServiceImpl();
boolean flag = providerService.addProvider(provider);
//If successful, redirect to the providerlist.jsp page
if(flag){
resp.sendRedirect(req.getContextPath()+"/jsp/provider.do?method=query");
}else{
//Failed to jump to the add page
req.getRequestDispatcher("provideradd.jsp").forward(req,resp);
}
}
123456789101112131415161718192021222324252627282930313233343536
Remember to call in the doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("add")){
this.add(req,resp);
}
}
123456789
6.5 implement supplier management - delete function
Dao layer
//How to delete a supplier
public abstract boolean deleteProvider(Connection conn,int providerId)throws SQLException;
12
Dao layer implementation class
//How to delete a supplier
@Override
public boolean deleteProvider(Connection conn, int providerId) throws SQLException {
PreparedStatement pstm = null;
boolean flag = false;
//If the connection is not empty
if(conn != null){
//Write sql
String sql = "delete from smbms_provider where id = ?";
//parameter list
Object[] params = {providerId};
//Execute sql
int updateRows = BaseDao.execute(conn, sql, pstm, params);
if(updateRows > 0){
flag = true;
}
//Release resources
BaseDao.closeResource(null,pstm,null);
}
return flag;
}
123456789101112131415161718192021
Service layer
//How to delete a supplier
public abstract boolean deleteProvider(int providerId)throws SQLException;
12
Service layer implementation class
//How to delete a supplier
@Override
public boolean deleteProvider(int providerId) {
Connection conn = null;
boolean flag = false;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
flag = providerDao.deleteProvider(conn, providerId);
conn.commit();
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return flag;
}
}
123456789101112131415161718
Add the delete vendor method of the Servlet class
//How to delete a supplier
public void deleteProvider(HttpServletRequest req, HttpServletResponse resp) throws IOException {
//Get the id of the supplier to be deleted from the front end
String proid = req.getParameter("proid");
int id = 0;
try {
id = Integer.parseInt(proid);
}catch (Exception e){
e.printStackTrace();
id = 0;
}
//Store the information in a map collection and pass it to ajax
HashMap<Object, Object> resultMap = new HashMap<>();
if(id<=0){
resultMap.put("delResult","notexist");
}else{
ProviderServiceImpl providerService = new ProviderServiceImpl();
if(providerService.deleteProvider(id)){
//If the deletion is successful
resultMap.put("delResult","true");
}else{
resultMap.put("delResult","false");
}
}
//Convert this map collection to json format
resp.setContentType("application/json");
PrintWriter out = resp.getWriter();
//The purpose of JSON tool class of JSONArray Alibaba is to convert format
out.write(JSONArray.toJSONString(resultMap));
out.flush();
out.close();
}
1234567891011121314151617181920212223242526272829303132
Modify doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("add")){
this.add(req,resp);
}else if(method.equals("delprovider")){
this.deleteProvider(req,resp);
}
}
1234567891011
6.6 realize supplier management - modify function
Click the button to observe the URL change
http://localhost:8080/smbms_war_exploded/jsp/provider.do?method=modify&proid=17 1
View the code corresponding to js
$(".modifyProvider").on("click",function(){
var obj = $(this);
window.location.href=path+"/jsp/provider.do?method=modify&proid="+ obj.attr("proid");
});
1234
Idea: we click the Modify button to jump to the modify information page and display the old information
Therefore, we need to define a method to query user information according to id
When the Modify button is clicked, the method of querying supplier information by id is called first
Display the information on the page
Click the Save button on the modify information page to modify the information
To sum up: query and display first, and then modify and save
Dao layer
//Method for querying supplier information according to supplier id
public abstract Provider findById(Connection conn,int providerId)throws SQLException;
12
Dao layer implementation class
//Method for querying supplier information according to supplier id
@Override
public Provider findById(Connection conn, int providerId) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
Provider provider = null;
//If the connection is not empty
if(conn != null){
//Writing sql statements
String sql = "select * from smbms_provider where id = ?";
//parameter list
Object[] params = {providerId};
//Execute sql
rs = BaseDao.executeQuery(conn, sql, pstm, params, rs);
//Traversal result set encapsulation
if(rs.next()){
provider = new Provider();
provider.setProCode(rs.getString("proCode"));;
provider.setProName(rs.getString("proName"));
provider.setProDesc(rs.getString("proDesc"));
provider.setProContact(rs.getString("proContact"));
provider.setProPhone(rs.getString("proPhone"));
provider.setProAddress(rs.getString("proAddress"));
provider.setProFax(rs.getString("proFax"));
provider.setCreatedBy(rs.getInt("createdBy"));
provider.setCreationDate(rs.getTimestamp("creationDate"));
provider.setModifyDate(rs.getTimestamp("modifyDate"));
provider.setModifyBy(rs.getInt("modifyBy"));
}
//Release resources
BaseDao.closeResource(null,pstm,rs);
}
return provider;
}
12345678910111213141516171819202122232425262728293031323334
Service layer
//Method for querying supplier information according to supplier id
public abstract Provider findById(int providerId)throws SQLException;
12
Service layer implementation class
//Method for querying supplier information according to supplier id
@Override
public Provider findById(int providerId) {
Connection conn = null;
Provider provider = null;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
provider = providerDao.findById(conn, providerId);
conn.commit();
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return provider;
}
}
123456789101112131415161718
findById method of Servlet layer
//Method of querying supplier information according to id
public void findById(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {
//Get id from front end
String proid = req.getParameter("proid");
int id = 0;
try {
id = Integer.parseInt(proid);
}catch (Exception e){
e.printStackTrace();
id = 0;
}
if(id>0){
ProviderServiceImpl providerService = new ProviderServiceImpl();
Provider provider = providerService.findById(id);
//Set the id so that it can be obtained when modifying the submission
provider.setId(id);
//Save supplier information to req
req.setAttribute("provider",provider);
//Return to the front display page
req.getRequestDispatcher("providermodify.jsp").forward(req,resp);
}
}
12345678910111213141516171819202122
Remember to modify the doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("add")){
this.add(req,resp);
}else if(method.equals("delprovider")){
this.deleteProvider(req,resp);
}else if(method.equals("modify")){
this.findById(req,resp);
}
}
12345678910111213
Click the modify page to display the old data normally
[external chain picture transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-vmrtya0j-163868667731) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmnzg4ubmv0l2nzbnoxmjmjm =, size_16, color_ffffff, t_70. PNG)]
Next, implement the method of saving changes
Dao layer
//How to modify supplier information
public abstract boolean modifyProvider(Connection conn,int id,Provider provider)throws SQLException;
12
Dao layer implementation class
//How to modify supplier information
@Override
public boolean modifyProvider(Connection conn, int id,Provider provider) throws SQLException {
PreparedStatement pstm = null;
boolean flag = false;
if(conn != null){
//Writing sql statements
String sql = "update smbms_provider set proCode = ?,proName = ?,proDesc = ?,proContact = ?,proPhone = ?,proAddress = ?,proFax = ?,modifyDate = ?,modifyBy = ? where id = ?";
Object[] params = {provider.getProCode(),provider.getProName(),provider.getProDesc(),provider.getProContact(),provider.getProPhone(),provider.getProAddress(),provider.getProFax(),provider.getModifyDate(),provider.getModifyBy(),id};
int updateRows = BaseDao.execute(conn, sql, pstm, params);
if(updateRows > 0){
flag = true;
}
BaseDao.closeResource(null,pstm,null);
}
return flag;
}
123456789101112131415161718
Service layer
//How to modify supplier information
public abstract boolean modifyProvider(int id,Provider provider)throws SQLException;
12
Service layer implementation class
//How to modify supplier information
@Override
public boolean modifyProvider(int id, Provider provider) {
Connection conn = null;
boolean flag = false;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
flag = providerDao.modifyProvider(conn, id, provider);
conn.commit();
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return flag;
}
}
123456789101112131415161718
Servlet class adding and modifying methods
//How to modify supplier information
public void modify(HttpServletRequest req,HttpServletResponse resp) throws IOException, ServletException {
System.out.println("enter modify ...");
//Obtain the id information of the supplier to be modified from the front end
String proId = req.getParameter("proid");
System.out.println("proId : ->"+proId.toString());
int id = 0;
try {
id = Integer.parseInt(proId);
}catch (Exception e){
e.printStackTrace();
id = 0;
}
//Get supplier information from the front end
String proCode = req.getParameter("proCode");
String proName = req.getParameter("proName");
String proContact = req.getParameter("proContact");
String proPhone = req.getParameter("proPhone");
String proAddress = req.getParameter("proAddress");
String proFax = req.getParameter("proFax");
String proDesc = req.getParameter("proDesc");
//Encapsulate into an object
Provider provider = new Provider();
provider.setProCode(proCode);
provider.setProName(proName);
provider.setProContact(proContact);
provider.setProPhone(proPhone);
provider.setProAddress(proAddress);
provider.setProFax(proFax);
provider.setProDesc(proDesc);
//The following parameters are not passed from the front end
provider.setModifyDate(new Date());
provider.setModifyBy(((User)req.getSession().getAttribute(Constants.USER_SESSION)).getId());
if(id>0){
//Execute changes
ProviderServiceImpl providerService = new ProviderServiceImpl();
if(providerService.modifyProvider(id, provider)){
//If the modification is successful, redirect to the display supplier list page
resp.sendRedirect(req.getContextPath()+"/jsp/provider.do?method=query");
}else{
//Modification failed. Forward to this modification page
req.getRequestDispatcher("providermodify.jsp").forward(req,resp);
}
}
}
123456789101112131415161718192021222324252627282930313233343536373839404142434445
And modify the doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("add")){
this.add(req,resp);
}else if(method.equals("delprovider")){
this.deleteProvider(req,resp);
}else if(method.equals("modify")){
this.findById(req,resp);
}else if(method.equals("modifyexe")){
this.modify(req,resp);
}
}
123456789101112131415
So far, the supplier management - modify function is completed!!!
6.7 implement supplier management - view function
Look at the js code first
$(".viewProvider").on("click",function(){
//To convert the bound element (a) into a jquery object, you can use the jquery method
var obj = $(this);
window.location.href=path+"/jsp/provider.do?method=view&proid="+ obj.attr("proid");
});
12345
Check the providerview.jsp page and find that we need to pass in a provider object to it
So we can get the id, find the information of this supplier and pass it into the data display page
//How to view supplier information
public void view(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {
//Get the supplier id from the front end
String proid = req.getParameter("proid");
int id = 0;
try {
id = Integer.parseInt(proid);
}catch (Exception e){
e.printStackTrace();
id = 0 ;
}
//Query by id
if(id >0){
ProviderServiceImpl providerService = new ProviderServiceImpl();
Provider provider = providerService.findById(id);
//Transfer this object to providerview.jsp for display
req.setAttribute("provider",provider);
//Redirect to presentation page
req.getRequestDispatcher("providerview.jsp").forward(req,resp);
}
}
123456789101112131415161718192021
And modify the doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("add")){
this.add(req,resp);
}else if(method.equals("delprovider")){
this.deleteProvider(req,resp);
}else if(method.equals("modify")){
this.findById(req,resp);
}else if(method.equals("modifyexe")){
this.modify(req,resp);
}else if(method.equals("view")){
this.view(req,resp);
}
}
1234567891011121314151617
7 order management
View head.jsp
<li ><a href="${pageContext.request.contextPath}/jsp/bill.do?method=query">Order management</a></li>
1
Idea:
First, write the mapping path corresponding to the Servlet of an order as / jsp/bill.do
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-3r2hq0lt-163868667732) (20210511153907481. PNG)]
BillServlet
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 BillServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
//Query order management list
public void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("enter BillList query method...");
//Return to the front page to view
req.getRequestDispatcher("billlist.jsp").forward(req,resp);
}
}
1234567891011121314151617181920212223242526
Remember to register after writing the BillServlet class
<!--Registered order management Servlet-->
<servlet>
<servlet-name>BillServlet</servlet-name>
<servlet-class>com.csnz.servlet.Bill.BillServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>BillServlet</servlet-name>
<url-pattern>/jsp/bill.do</url-pattern>
</servlet-mapping>
123456789
ok, try to access this page first
[external chain picture transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-znpwv4o5-163868667732) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmzg4ubmv0l2nzbnoxmjmxmjm =, size_16, color_ffffff, t_70. PNG)]
The page successfully displays the list of the next query order management
Write from the bottom up
7.1 construction related package structure
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (IMG ebwfwikp-163868667732) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmnzg4ubmv0l2nzbnoxmjmjm =, size_16, color_ffffff, t_70. PNG)]
7.2.1 Dao layer
//Query the total number of orders by commodity name, supplier id and payment
public abstract int getBillCount(Connection conn,String queryProductName,int queryProviderId,int queryIsPayment)throws SQLException;
//Query the order list according to commodity name, supplier id and payment
public abstract List<Bill> getBillList(Connection conn,String queryProductName,int queryProviderId,int queryIsPayment, int currentPageNo, int pageSize) throws SQLException;
12345
7.2.2 Dao layer implementation class
//Query the total number of orders by commodity name, supplier id and payment
@Override
public int getBillCount(Connection conn, String queryProductName, int queryProviderId, int queryIsPayment) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
int count = 0;
if(conn != null){
StringBuffer sql = new StringBuffer();
sql.append("select count(1) as count from smbms_bill b,smbms_provider p where b.providerId = p.id");
List<Object> paramsList = new ArrayList<>();
if(queryProductName != "") {
sql.append(" and productName like ?");
paramsList.add("%"+queryProductName+"%");
}
if(queryProviderId != 0){
sql.append(" and providerId = ?");
paramsList.add(queryProviderId);
}
if(queryIsPayment != 0){
sql.append(" and isPayment = ?");
paramsList.add(queryIsPayment);
}
Object[] params = paramsList.toArray();
rs = BaseDao.executeQuery(conn, sql.toString(), pstm, params, rs);
//Traverse the result set and fetch the quantity count from the result set
if (rs.next()){
count = rs.getInt("count");
}
//close resource
BaseDao.closeResource(null,pstm,rs);
}
return count;
}
//Query the order list according to commodity name, supplier id and payment
@Override
public List<Bill> getBillList(Connection conn, String queryProductName, int queryProviderId, int queryIsPayment, int currentPageNo, int pageSize) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
List<Bill> bills = new ArrayList<>();
if(conn != null){
System.out.println("enter BillDaoImpl...");
//Dynamic splicing string
StringBuffer sql = new StringBuffer();
//parameter list
ArrayList<Object> paramsList = new ArrayList<>();
sql.append("select b.*,p.proName as proName from smbms_bill b,smbms_provider p where b.providerId = p.id");
if(queryProductName != "" && queryProviderId != 0 && queryIsPayment != 0){
//Description: none of the three parameters is empty
sql.append(" and productName like ? and providerId = ? and isPayment = ?");
paramsList.add("%"+queryProductName+"%");
try {
paramsList.add(queryProviderId);
paramsList.add(queryIsPayment);
}catch (Exception e){
e.printStackTrace();
}
}else if(queryProductName != "" || queryProviderId != 0 || queryIsPayment != 0){
//Description some of the three parameters are not empty
if(queryProductName != ""){
sql.append(" and productName like ?");
paramsList.add("%"+queryProductName+"%");
}
if(queryProviderId != 0){
sql.append(" and providerId = ?");
paramsList.add(queryProviderId);
}
if(queryIsPayment != 0){
sql.append(" and isPayment = ?");
paramsList.add(queryIsPayment);
}
}
//The total number of limit StartIndex and PageSize used for paging in the database
//Current page = (current page - 1) * page size
sql.append(" order by b.creationDate DESC limit ?,?");
currentPageNo = (currentPageNo-1)*pageSize;
paramsList.add(currentPageNo);
paramsList.add(pageSize);
//sql splicing is completed, and the parameter list is correct
System.out.println("Test SQL --> "+sql.toString());
//Convert parameter list
Object[] params = paramsList.toArray();
//Execute sql
rs = BaseDao.executeQuery(conn, sql.toString(), pstm, params, rs);
//Traversal result set encapsulation object added to list
while (rs.next()){
Bill bill = new Bill();
bill.setId(rs.getInt("id"));
bill.setBillCode(rs.getString("billCode"));
bill.setProductName(rs.getString("productName"));
bill.setProductDesc(rs.getString("productDesc"));
bill.setProductUnit(rs.getString("productUnit"));
bill.setProductCount(rs.getBigDecimal("productCount"));
bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
bill.setIsPayment(rs.getInt("isPayment"));
bill.setCreatedBy(rs.getInt("createdBy"));
bill.setCreationDate(rs.getTimestamp("creationDate"));
bill.setModifyBy(rs.getInt("modifyBy"));
bill.setModifyDate(rs.getTimestamp("modifyDate"));
bill.setProviderId(rs.getInt("providerId"));
bill.setProviderName(rs.getString("proName"));
bills.add(bill);
}
//close resource
BaseDao.closeResource(null,pstm,rs);
}
//Return to list
return bills;
}
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111
7.2.3 Service layer
//Query the total number of orders by commodity name, supplier id and payment
public abstract int getBillCount(String queryProductName, int queryProviderId, int queryIsPayment) throws SQLException;
//Query the order list according to commodity name, supplier id and payment
public abstract List<Bill> getBillList(String queryProductName, int queryProviderId, int queryIsPayment, int currentPageNo, int pageSize) throws SQLException;
1234
7.2.4 Service layer implementation class
import com.csnz.dao.BaseDao;
import com.csnz.dao.Bill.BillDaoImpl;
import com.csnz.pojo.Bill;
import java.sql.Connection;
import java.util.List;
public class BillServiceImpl implements BillService {
private BillDaoImpl billDao;
public BillServiceImpl(){
this.billDao = new BillDaoImpl();
}
//Query the total number of orders by commodity name, supplier id and payment
@Override
public int getBillCount(String queryProductName, int queryProviderId, int queryIsPayment) {
Connection conn = null;
int billCount = 0;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
billCount = billDao.getBillCount(conn, queryProductName, queryProviderId, queryIsPayment);
conn.commit();
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return billCount;
}
}
//Query the order list according to commodity name, supplier id and payment
@Override
public List<Bill> getBillList(String queryProductName, int queryProviderId, int queryIsPayment, int currentPageNo, int pageSize) {
Connection conn = null;
List<Bill> billList = null;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
System.out.println("enter getBillList...");
billList = billDao.getBillList(conn, queryProductName, queryProviderId, queryIsPayment,currentPageNo,pageSize);
conn.commit();
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return billList;
}
}
}
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
7.2.5 Servlet layer
import com.csnz.pojo.Bill;
import com.csnz.pojo.Provider;
import com.csnz.service.Bill.BillServiceImpl;
import com.csnz.service.Provider.ProviderServiceImpl;
import com.csnz.util.PageSupport;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
public class BillServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
//Query order management list
public void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("enter BillList query method...");
//Get search information from the front end
String queryProductName = req.getParameter("queryProductName");
String queryProviderId = req.getParameter("queryProviderId");
String queryIsPayment = req.getParameter("queryIsPayment");
int proId = 0;
int isPayment = 0;
//This attribute is on the search button
String pageIndex = req.getParameter("pageIndex");
//Set the current page and the number displayed per page
int currentPageNo = 1;
int pageSize = 5;
/*test*/
System.out.println("queryProductName - > "+queryProductName);
System.out.println("queryProviderId - > "+queryProviderId);
System.out.println("queryIsPayment - > "+queryIsPayment);
//Judge the attribute passed from the front end. If it is empty (no input), assign the value to the empty string. When the dao layer implements class judgment, it must also judge according to the empty string
if (queryProductName == null){
queryProductName = "";
}
if(queryProviderId != null ){
proId = Integer.parseInt(queryProviderId);
}
if(queryIsPayment != null ){
isPayment = Integer.parseInt(queryIsPayment);
}
if(pageIndex!=null){
currentPageNo = Integer.parseInt(pageIndex);
}
//The Servlet layer calls the service layer
BillServiceImpl billService = new BillServiceImpl();
//Get order total page: Previous page next page
int totalCount = billService.getBillCount(queryProductName,proId,isPayment);
//Total pages support
PageSupport pageSupport = new PageSupport();
System.out.println("Current page:"+currentPageNo);
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
System.out.println("Get total orders"+totalCount);
pageSupport.setTotalCount(totalCount);
//Total pages
int totalPageCount = pageSupport.getTotalPageCount();
//Control first and last pages
//If the number of pages is less than 1, the first page will be displayed, and if the number of pages is greater than the last page, the last page will be displayed
if(currentPageNo<1){
currentPageNo =1;
}else if(currentPageNo>totalPageCount){
currentPageNo = totalPageCount;
}
//Query the order list according to the information in the front search box
List<Bill> billList = billService.getBillList(queryProductName, proId, isPayment,currentPageNo,pageSize);
System.out.println("Test billList -> "+billList);
//Store this list in req for front-end display
req.setAttribute("billList",billList);
//Query supplier list
ProviderServiceImpl providerService = new ProviderServiceImpl();
int totalNum = providerService.getProviderCounts("","");
List<Provider> providerList = providerService.getProviderList("", "", 1, totalNum);
//Store this supplier list in req so that the content of our search box is not empty
req.setAttribute("providerList",providerList);
req.setAttribute("queryProductName",queryProductName);
req.setAttribute("queryProviderId",proId);
req.setAttribute("queryIsPayment",isPayment);
//Pagination display data
req.setAttribute("totalCount",totalCount);
req.setAttribute("currentPageNo",currentPageNo);
req.setAttribute("totalPageCount",totalPageCount);
//Return to the front page to view
req.getRequestDispatcher("billlist.jsp").forward(req,resp);
}
}
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102
be accomplished
[external chain picture transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-l12ivgje-163868667733) (watermark, type_zmfuz3pozw5nagvpdgk, shadow_10, text_ahr0chm6ly9ibg9nlmnzg4ubmv0l2nzbnoxmjmjm =, size_16, color_ffff, t_70. PNG)]
7.3 add order function
View billadd.jsp
<form id="billForm" name="billForm" method="post" action="${pageContext.request.contextPath }/jsp/bill.do">
<input type="hidden" name="method" value="add">
12
So we know he's using the add method
Or write from the bottom up
7.3.1 Dao layer
//Add order
public abstract boolean addBill(Connection conn,Bill bill)throws SQLException;
12
7.3.2 Dao layer implementation class
//Add order
@Override
public boolean addBill(Connection conn, Bill bill) throws SQLException {
PreparedStatement pstm = null;
boolean flag = false;
if (conn != null){
String sql = "insert into smbms_bill (billCode,productName,productDesc,productUnit,productCount,totalPrice,isPayment,createdBy,creationDate,modifyBy,modifyDate,providerId)values(?,?,?,?,?,?,?,?,?,?,?,?)" ;
Object[] params = {bill.getBillCode(),bill.getProductName(),bill.getProductDesc(),bill.getProductUnit(),bill.getProductCount(),bill.getTotalPrice(),bill.getIsPayment(),bill.getCreatedBy(),bill.getCreationDate(),bill.getModifyBy(),bill.getModifyDate(),bill.getProviderId()};
int updateRows = BaseDao.execute(conn, sql, pstm, params);
if(updateRows > 0){
flag = true;
}
BaseDao.closeResource(null,pstm,null);
}
return flag;
}
12345678910111213141516
7.3.3 Service layer
//Add order
public abstract boolean addBill(Bill bill)throws SQLException;
12
7.3.4 Service layer implementation class
//Add order
@Override
public boolean addBill(Bill bill) {
boolean flag = false;
Connection conn = null;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
flag = billDao.addBill(conn, bill);
conn.commit();
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return flag;
}
}
123456789101112131415161718
7.3.5 Servlet layer
//Call the drop-down box in the query vendor billadd.jsp page
public void getproviderlist(HttpServletRequest req, HttpServletResponse resp) throws IOException {
ProviderServiceImpl providerService = new ProviderServiceImpl();
int providerCounts = providerService.getProviderCounts("", "");
List<Provider> providerList = providerService.getProviderList("", "", 1, providerCounts);
//Send the information to ajax to convert the collection into json format
resp.setContentType("application/json");
PrintWriter out = resp.getWriter();
//The purpose of JSON tool class of JSONArray Alibaba is to convert format
out.write(JSONArray.toJSONString(providerList));
out.flush();
out.close();
}
//Add order
public void add(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//Get data from the front end and encapsulate it
Bill bill = new Bill();
bill.setBillCode(req.getParameter("billCode"));
bill.setProductName(req.getParameter("productName"));
bill.setProductUnit(req.getParameter("productUnit"));
bill.setProductCount(BigDecimal.valueOf(Long.parseLong(req.getParameter("productCount"))));
bill.setTotalPrice(BigDecimal.valueOf(Long.parseLong(req.getParameter("totalPrice"))));
bill.setProviderId(Integer.parseInt(req.getParameter("providerId")));
bill.setIsPayment(Integer.parseInt(req.getParameter("isPayment")));
//The following is what the form does not have
bill.setCreatedBy(((User)req.getSession().getAttribute(Constants.USER_SESSION)).getId());
bill.setCreationDate(new Date());
bill.setModifyBy(((User)req.getSession().getAttribute(Constants.USER_SESSION)).getId());
bill.setModifyDate(new Date());
BillServiceImpl billService = new BillServiceImpl();
if(billService.addBill(bill)){
//If the addition is successful, redirect to the display order page
resp.sendRedirect(req.getContextPath()+"/jsp/bill.do?method=query");
}else{
//Add failed to forward to this add page
req.getRequestDispatcher("billadd.jsp").forward(req,resp);
}
}
12345678910111213141516171819202122232425262728293031323334353637383940
And modify the doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("getproviderlist")){
this.getproviderlist(req,resp);
}
else if(method.equals("add")){
this.add(req,resp);
}
}
123456789101112
So far, the order function is added!
7.4 realize order deletion function
js triggered by clicking the delete button
//Click the delete button on the order management page to pop up the delete box (billlist.jsp)
function deleteBill(obj){
$.ajax({
type:"GET",
url:path+"/jsp/bill.do",
data:{method:"delbill",billid:obj.attr("billid")},
dataType:"json",
success:function(data){
if(data.delResult == "true"){//Delete succeeded: delete row
cancleBtn();
obj.parents("tr").remove();
}else if(data.delResult == "false"){//Deletion failed
//alert("sorry, failed to delete order [" + obj.attr("billcc") + "]));
changeDLGContent("Sorry, delete order["+obj.attr("billcc")+"]fail");
}else if(data.delResult == "notexist"){
//alert("sorry, the order [" + obj.attr("billcc") + "] does not exist");
changeDLGContent("Sorry, order["+obj.attr("billcc")+"]non-existent");
}
},
error:function(data){
alert("Sorry, deletion failed");
}
});
}
123456789101112131415161718192021222324
7.4.1 Dao layer
//Delete order
public abstract boolean deleteBill(Connection conn,int billId)throws SQLException;
12
7.4.2 Dao layer implementation class
//Delete order
@Override
public boolean deleteBill(Connection conn, int billId) throws SQLException {
PreparedStatement pstm = null;
boolean flag = false;
if(conn != null){
String sql = "delete from smbms_bill where id = ?";
Object[] params = {billId};
int updateRows = BaseDao.execute(conn, sql, pstm, params);
if(updateRows > 0){
flag = true;
}
BaseDao.closeResource(null,pstm,null);
}
return flag;
}
12345678910111213141516
7.4.3 Service layer
//Delete order
public abstract boolean deleteBill(int billId);
12
7.4.4 Service layer implementation class
//Delete order
@Override
public boolean deleteBill(int billId) {
boolean flag = false;
Connection conn = null;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
flag = billDao.deleteBill(conn, billId);
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return flag;
}
}
123456789101112131415161718
7.4.5 Servlet layer
//Delete order
public void deleteBill(HttpServletRequest req, HttpServletResponse resp) throws IOException {
//Get the order id to be deleted from the front end
String billid = req.getParameter("billid");
int id = 0;
//transformation
try {
id = Integer.parseInt(billid);
}catch (Exception e){
e.printStackTrace();
id = 0;
}
BillServiceImpl billService = new BillServiceImpl();
//Create a map collection to store information about the success and failure of deletion and pass it to ajax
Map<Object, Object> resultMap = new HashMap<>();
if(id <= 0){
resultMap.put("delResult","notexist");
}else{
if(billService.deleteBill(id)){
System.out.println("Order deleted successfully...");
//Order deleted successfully
resultMap.put("delResult","true");
}else{
System.out.println("Failed to delete order...");
resultMap.put("delResult","false");
}
}
//Convert this map collection to json format
resp.setContentType("application/json");
PrintWriter out = resp.getWriter();
//The purpose of JSON tool class of JSONArray Alibaba is to convert format
out.write(JSONArray.toJSONString(resultMap));
out.flush();
out.close();
}
1234567891011121314151617181920212223242526272829303132333435
And modify the doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("getproviderlist")){
this.getproviderlist(req,resp);
}else if(method.equals("add")){
this.add(req,resp);
}else if(method.equals("delbill")){
this.deleteBill(req,resp);
}
}
12345678910111213
The delete order function is now complete
7.5 realize order modification function
View the corresponding js code
$(".modifyBill").on("click",function(){
var obj = $(this);
window.location.href=path+"/jsp/bill.do?method=modify&billid="+ obj.attr("billid");
});
1234
Idea: we get the order id to be modified from the front end
Query the order information and display it on the modify order page. Finally, execute the method of submitting the order form
7.5.1 Dao layer
//Obtain order information according to order id
public abstract Bill findByBillId(Connection conn,int billId)throws SQLException;
//Modify order information
public abstract boolean modifyBill(Connection conn,int billId,Bill bill)throws SQLException;
1234
7.5.2 Dao layer implementation class
//Obtain order information according to order id
@Override
public Bill findByBillId(Connection conn, int billId) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
Bill bill = new Bill();
if(conn != null){
String sql = "select b.*,p.proName as providerName from smbms_bill b,smbms_provider p where b.id = ? and b.providerId = p.id";
Object[] params = {billId};
rs = BaseDao.executeQuery(conn, sql, pstm, params, rs);
//Traverse the result set and store it in the bill object
if(rs.next()){
bill.setBillCode(rs.getString("billCode"));
bill.setProductName(rs.getString("productName"));
bill.setProductDesc(rs.getString("productDesc"));
bill.setProductUnit(rs.getString("productUnit"));
bill.setProductCount(rs.getBigDecimal("productCount"));
bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
bill.setIsPayment(rs.getInt("isPayment"));
bill.setCreatedBy(rs.getInt("createdBy"));
bill.setCreationDate(rs.getTimestamp("creationDate"));
bill.setModifyBy(rs.getInt("modifyBy"));
bill.setModifyDate(rs.getTimestamp("modifyDate"));
bill.setProviderId(rs.getInt("providerId"));
bill.setProviderName(rs.getString("providerName"));
}
BaseDao.closeResource(null,pstm,rs);
}
return bill;
}
//Modify order information
@Override
public boolean modifyBill(Connection conn, int billId, Bill bill) throws SQLException {
PreparedStatement pstm = null;
boolean flag = false;
if(conn != null){
String sql = "update smbms_bill set billCode = ?,productName =?,productDesc = ?,productUnit = ?,productCount = ? ,totalPrice = ?,isPayment = ?,createdBy = ?,creationDate = ?,modifyBy = ?,modifyDate = ?,providerId = ? where id = ?";
Object[] params = {bill.getBillCode(),bill.getProductName(),bill.getProductDesc(),bill.getProductUnit(),bill.getProductCount(),bill.getTotalPrice(),bill.getIsPayment(),bill.getCreatedBy(),bill.getCreationDate(),bill.getModifyBy(),bill.getModifyDate(),bill.getProviderId(),billId};
int updateRows = BaseDao.execute(conn, sql, pstm, params);
if (updateRows > 0) {
flag = true;
}
BaseDao.closeResource(null,pstm,null);
}
return flag;
}
12345678910111213141516171819202122232425262728293031323334353637383940414243444546
7.5.3 Service layer
//Obtain order information according to order id
public abstract Bill findByBillId(int billId)throws SQLException;
//Modify order information
public abstract boolean modifyBill(int billId, Bill bill)throws SQLException;
1234
7.5.4 Service layer implementation class
//Obtain order information according to order id
@Override
public Bill findByBillId(int billId){
Connection conn = null;
Bill bill = null;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
bill = billDao.findByBillId(conn, billId);
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return bill;
}
}
//Modify order information
@Override
public boolean modifyBill(int billId, Bill bill) {
boolean flag = false;
Connection conn = null;
try {
conn = BaseDao.getConnection();
conn.setAutoCommit(false);
flag = billDao.modifyBill(conn, billId, bill);
conn.commit();
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
BaseDao.closeResource(conn,null,null);
return flag;
}
}
123456789101112131415161718192021222324252627282930313233343536
7.5.5 Servlet layer
// Display of order information to be modified
public void modify(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//Get the order id to be modified from the front end
String billid = req.getParameter("billid");
int id = 0;
try {
id = Integer.parseInt(billid);
}catch (Exception e){
e.printStackTrace();
id = 0;
}
//Query the order information according to this id and display it on the modification page
BillServiceImpl billService = new BillServiceImpl();
Bill bill = billService.findByBillId(id);
bill.setId(id);
//Save this bill into req and forward it to the billmodify.jsp page
req.setAttribute("bill",bill);
req.getRequestDispatcher("billmodify.jsp").forward(req,resp);
}
//How to modify order information
public void modifysave(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
//Get the order id to be modified from the front end
String billid = req.getParameter("billid");
System.out.println("Orders obtained from the front end id: "+billid);
int id = 0;
try {
id = Integer.parseInt(billid);
}catch (Exception e){
e.printStackTrace();
id = 0;
}
//Query old order information by id
BillServiceImpl billService = new BillServiceImpl();
Bill bill = billService.findByBillId(id);
//Modify the corresponding order according to the modified information
bill.setBillCode(req.getParameter("billCode"));
bill.setProductName(req.getParameter("productName"));
bill.setProductUnit(req.getParameter("productUnit"));
bill.setProductCount(BigDecimal.valueOf(Double.parseDouble(req.getParameter("productCount"))));
bill.setTotalPrice(BigDecimal.valueOf(Double.parseDouble(req.getParameter("totalPrice"))));
bill.setProviderId(Integer.parseInt(req.getParameter("providerId")));
bill.setIsPayment(Integer.parseInt(req.getParameter("isPayment")));
//The following is not shown in the form
bill.setModifyBy(((User)req.getSession().getAttribute(Constants.USER_SESSION)).getId());
bill.setModifyDate(new Date());
//Execute modification statement
boolean flag = billService.modifyBill(id, bill);
if(flag){
//If the modification is successful, redirect to the order display page
resp.sendRedirect(req.getContextPath()+"/jsp/bill.do?method=query");
}else{
//Modification failed. Forward to this modification page
req.getRequestDispatcher("billmodify.jsp").forward(req,resp);
}
}
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455
And modify the doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("getproviderlist")){
this.getproviderlist(req,resp);
}else if(method.equals("add")){
this.add(req,resp);
}else if(method.equals("delbill")){
this.deleteBill(req,resp);
}else if(method.equals("modify")){
this.modify(req,resp);
}else if(method.equals("modifysave")){
this.modifysave(req,resp);
}
}
1234567891011121314151617
7.6 realize order viewing function
View the corresponding js code
$(".viewBill").on("click",function(){
//To convert the bound element (a) into a jquery object, you can use the jquery method
var obj = $(this);
window.location.href=path+"/jsp/bill.do?method=view&billid="+ obj.attr("billid");
});
12345
Idea: find the order information according to the id and send it to the front end for display
Servlet layer
//View order information
public void viewBill(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//Get the id of the order information to be viewed from the front end
String BillId = req.getParameter("billid");
int id = 0;
try {
id = Integer.parseInt(BillId);
}catch (ClassCastException e){
e.printStackTrace();
id = 0;
}
//Query order information by id
BillServiceImpl billService = new BillServiceImpl();
Bill bill = billService.findByBillId(id);
//Store information in req
req.setAttribute("bill",bill);
//Forward to billview.jsp page
req.getRequestDispatcher("billview.jsp").forward(req,resp);
}
And modify the doGet method
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if(method.equals("query")){
this.query(req,resp);
}else if(method.equals("getproviderlist")){
this.getproviderlist(req,resp);
}else if(method.equals("add")){
this.add(req,resp);
}else if(method.equals("delbill")){
this.deleteBill(req,resp);
}else if(method.equals("modify")){
this.modify(req,resp);
}else if(method.equals("modifysave")){
this.modifysave(req,resp);
}else if(method.equals("view")){
this.viewBill(req,resp);
}
}
12345678910111213141516171819
So far, all functions can be tested
Schematic flow chart of the project:
