--- url: /notes/my-sql/购物商城/index.md --- # 购物商城 ## 数据库设计 * 初始化表 ```sql -- 用户信息表（账号、密码、姓名、角色id、状态） #userInformation（{4}user、password、uname、[1]roleid、status） drop table if exists userInformation; create table userInformation( `user` varchar(18) primary key, `password` varchar(255) not null, uname varchar(12) not null unique, roleid int not null, `status` int not null )default charset=utf8; -- 用户角色表（角色id、角色名称、菜单列表） #userRole（[1]roleid、rolename、menulist） drop table if exists userRole; create table userRole( roleid int primary key, rolename varchar(12) not null unique, menulist varchar(255) not null )default charset=utf8; -- 商品信息表（{5}商品id、名称、类型id、品牌、价格、生产日期、失效日期） #commodityInformation（commodityid、commodityname、[2]typeid、brand、price、productiondate、expirationdate） drop table if exists commodityInformation; create table commodityInformation( commodityid int auto_increment primary key, commodityname varchar(255) not null unique, typeid int not null, brand varchar(255) not null, price double(12,2) not null, productiondate date not null, expirationdate date not null )default charset=utf8; -- 商品类型表（类型id、类型名称） #commodityTypes（[2]typeid、commoditypename） drop table if exists commodityTypes; create table commodityTypes( typeid int primary key, commoditypename varchar(255) not null unique )default charset=utf8; -- 购物车表（自编号、用户账号、商品id、商品名称、价格、数量、金额） #shopCar（shopcarid、{4}user、{5}commodityid、commodityname、price、amount、money） drop table if exists shopCar; create table shopCar( shopcarid int auto_increment primary key, `user` varchar(18) not null unique, commodityid int not null, commodityname varchar(255) not null, price double(12,2) not null, amount int not null, money double(12,2) not null )default charset=utf8; -- 订单表（订单编号、总金额、下单日期、用户账号） #order（[3]orderid、summoney、orderdate、{4}user） drop table if exists `order`; create table `order`( orderid int primary key, summoney double(12,2) not null, orderdate date not null, `user` varchar(18) not null )default charset=utf8; -- 订单明细表（自编号、订单编号、商品id、商品名称、价格、数量、金额） #orderDetails（orderDetailsid、[3]orderid、{5}commodityid、commodityname、price、amount、money） drop table if exists orderDetails; create table orderDetails( orderDetailsid int auto_increment primary key, orderid int not null, commodityid int not null, commodityname varchar(255) not null, price double(12,2) not null, amount int not null, money double(12,2) not null )default charset=utf8; /* # 关联用户角色表的角色id alter table userInformation add constraint fk_roleid foreign key(roleid) references userRole(userRole); # 关联商品类型表的类型id alter table commodityInformation add constraint fk_typeid foreign key(typeid) references commodityTypes(typeid); # 关联用户信息表的用户名 alter table shopCar add constraint fk_user foreign key(`user`) references userInformation(`user`); # 关联商品信息表的商品id alter table shopCar add constraint fk_commodityid foreign key(commodityid) references commodityInformation(commodityid); # 关联购物车表的用户名 alter table `order` add constraint fk_user foreign key(`user`) references shopCar(`user`); # 关联购物车表的商品id alter table orderDetails add constraint fk_commodityid foreign key(commodityid) references shopCar(commodityid); */ ``` * 初始化数据 ```sql /*用户角色表（角色id、角色名称、菜单列表） 0 超级管理员 1 卖家 2 买家 3 黑名单 userRole（[1]roleid、rolename、menulist）*/ insert into userRole values(0,'超级管理员','商品管理|订单管理|用户管理|商品列表|购物|个人信息管理'); insert into userRole values(1,'商家','商品管理|订单管理|商品列表|个人信息管理'); insert into userRole values(2,'买家','商品列表|购物|个人信息管理'); /*用户信息表（账号、密码、姓名、角色id、状态） userInformation（[4]user、password、uname、[1]roleid、status）*/ insert into userInformation values('root','root','马化腾',0,1); insert into userInformation values('admin1','123456','苹果',1,1); insert into userInformation values('admin2','123456','鸿星尔克',1,1); insert into userInformation values('admin3','123456','妮维雅',1,1); insert into userInformation values('buyer1','123','张三',2,1); insert into userInformation values('buyer2','123','李四',2,1); insert into userInformation values('buyer3','123','王五',2,1); /*商品类型表（类型id、类型名称） commodityTypes（[2]typeid、commoditypename）*/ insert into commodityTypes values(1001,'数码'); insert into commodityTypes values(1002,'服装'); insert into commodityTypes values(1003,'洗护'); /*商品信息表（商品id、名称、类型id、品牌、价格、生产日期、失效日期） commodityInformation（[3]commodityid、commodityname、[2]typeid、brand、price、productiondate、expirationdate）*/ insert into commodityInformation values(default,'iPhoneX',1001,'苹果',8900,date_sub(now(),interval 1 year),date_add(now(),interval 99 year)); insert into commodityInformation values(default,'iPad2021',1001,'苹果',2890,date_sub(now(),interval 90 day),date_add(now(),interval 90 year)); insert into commodityInformation values(default,'AirPods',1001,'苹果',1200,date_sub(now(),interval 2 year),date_add(now(),interval 20 year)); insert into commodityInformation values(default,'t恤',1002,'鸿星尔克',120.5,date_sub(now(),interval 60 day),date_add(now(),interval 10 year)); insert into commodityInformation values(default,'黑裤子',1002,'鸿星尔克',200,date_sub(now(),interval 70 day),date_add(now(),interval 10 year)); insert into commodityInformation values(default,'鞋子',1002,'鸿星尔克',230,date_sub(now(),interval 80 day),date_add(now(),interval 10 year)); insert into commodityInformation values(default,'洗面奶',1001,'妮维雅',8900,date_sub(now(),interval 90 day),date_add(now(),interval 1 year)); insert into commodityInformation values(default,'护发素',1001,'妮维雅',8900,date_sub(now(),interval 90 day),date_add(now(),interval 1 year)); insert into commodityInformation values(default,'眼霜',1001,'妮维雅',8900,date_sub(now(),interval 90 day),date_add(now(),interval 1 year)); select * from userRole; select * from userInformation; select * from commodityTypes select * from commodityInformation; ``` # 具体实现 * pojo 数据库映射表 ```java package com.onlinestore.pojo; import lombok.*; /** * @author 涂鏊飞tu_aofei@163.com * @description: 数据库映射表-用户表 类描述 * @create 2021-09-01 16:59 */ @Data @NoArgsConstructor @AllArgsConstructor public class UserInformation { private String user; private String password; private String uname; private Integer roleid; private Integer status; } package com.onlinestore.pojo; import lombok.*; /** * @author 涂鏊飞tu_aofei@163.com * @description: 用户角色表 类描述 * @create 2021-09-01 17:06 */ @Data @NoArgsConstructor @AllArgsConstructor public class UserRole { private Integer roleid; private String rolename; private String menulist; } package com.onlinestore.pojo; import lombok.*; import java.util.Date; /** * @author 涂鏊飞tu_aofei@163.com * @description: 商品信息映射 类描述 * @create 2021-09-01 17:07 */ @Data @NoArgsConstructor @AllArgsConstructor public class CommodityInformation { private Integer commodityid; private String commodityname; private Integer typeid; private String brand; private Double price; private Date productiondate; private Date expirationdate; @Override public String toString() { return "商品id=" + commodityid + ", 名称='" + commodityname + '\'' + ", 类型id=" + typeid + ", 品牌='" + brand + '\'' + ", 价格=" + price + ", 生产日期=" + productiondate + ", 失效日期=" + expirationdate; } } package com.onlinestore.pojo; import lombok.*; /** * @author 涂鏊飞tu_aofei@163.com * @description: 商品类型映射 类描述 * @create 2021-09-01 17:09 */ @Data @NoArgsConstructor @AllArgsConstructor public class CommodityTypes { private Integer typeid; private String commoditypename; } package com.onlinestore.pojo; import lombok.*; /** * @author 涂鏊飞tu_aofei@163.com * @description: 购物车映射 类描述 * @create 2021-09-01 17:10 */ @Data @NoArgsConstructor @AllArgsConstructor public class ShopCar { private Integer shopcarid; private String user; private Integer commodityid; private String commodityname; private Double price; private Integer amount; private Double money; @Override public String toString() { return "编号=" + shopcarid + ", 用户账号='" + user + '\'' + ", 商品id=" + commodityid + ", 商品名称='" + commodityname + '\'' + ", 价格=" + price + ", 数量=" + amount + ", 金额=" + money; } } package com.onlinestore.pojo; import lombok.*; import java.util.Date; /** * @author 涂鏊飞tu_aofei@163.com * @description: 订单表映射 类描述 * @create 2021-09-01 17:13 */ @Data @NoArgsConstructor @AllArgsConstructor public class Order { private String orderid; private Double summoney; private Date orderdate; private String user; @Override public String toString() { return "订单编号=" + orderid + ", 总金额=" + summoney + ", 下单日期=" + orderdate + ", 用户账号='" + user + '\'' ; } } package com.onlinestore.pojo; import lombok.*; /** * @author 涂鏊飞tu_aofei@163.com * @description: 订单明细表映射 类描述 * @create 2021-09-01 17:14 */ @Data @NoArgsConstructor @AllArgsConstructor public class OrderDetails { private Integer orderDetailsid; private String orderid; private Integer commodityid; private String commodityname; private Double price; private Integer amount; private Double money; @Override public String toString() { return "订单明细编号=" + orderDetailsid + ", 订单编号=" + orderid + ", 商品id=" + commodityid + ", 商品名称='" + commodityname + '\'' + ", 价格=" + price + ", 数量=" + amount + ", 金额=" + money; } } ``` * dao接口 数据访问层 ```java package com.onlinestore.dao; import com.onlinestore.pojo.UserInformation; import com.onlinestore.pojo.UserRole; import org.apache.commons.dbutils.handlers.BeanHandler; import java.sql.SQLException; import java.util.List; public interface UserInformationDao { // 注册 public void register(UserInformation userInformation) throws SQLException; // 登录 public Integer login(String user,String password) throws SQLException; // 获取用户状态 public Integer getState(String user) throws SQLException; // 获取用户操作菜单 public String[] getMenu(String account) throws SQLException; } package com.onlinestore.dao; import com.onlinestore.pojo.ShopCar; import java.sql.SQLException; import java.util.List; public interface ShopCarDao { // 加入购物车 public Integer updateShopCar(Integer no, String account, Integer count) throws SQLException; // 展示购物车 public List queryUserShopCar(String account) throws SQLException; // 提交订单后，删除指定用户的购物车记录 public Integer deleteUserShopCar(String account) throws SQLException; } package com.onlinestore.dao; import com.onlinestore.pojo.OrderDetails; import java.sql.SQLException; import java.util.List; public interface OrderDetailsDao { public Integer addOrderDetails(String account) throws SQLException; public List queryAllOrderDetails(String account) throws SQLException; } package com.onlinestore.dao; import com.onlinestore.pojo.Order; import java.sql.SQLException; import java.text.ParseException; import java.util.List; public interface OrderDao { // 提交订单 public Integer updateOrder(String user) throws SQLException, ParseException; // 查看用户订单 public List queryUserOrder(String user) throws SQLException; } package com.onlinestore.dao; import com.onlinestore.pojo.CommodityInformation; import java.sql.SQLException; import java.util.List; import java.util.Map; public interface CommodityInformationDao { // 查询所有商品 public List queryAll() throws SQLException; // 查询所有商品编号 public Map quaryAllCommodityId(Integer commodityid) throws SQLException; } ``` * impl dao接口实现类 ```java package com.onlinestore.dao.impl; import com.onlinestore.dao.UserInformationDao; import com.onlinestore.pojo.UserInformation; import com.onlinestore.pojo.UserRole; import com.onlinestore.utils.JDBCUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.ArrayHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import java.sql.SQLException; import java.util.Arrays; import java.util.List; /** * @author 涂鏊飞tu_aofei@163.com * @description: 用户注册和登录 类描述 * @create 2021-09-01 17:26 */ public class UserInformationDaoImpl implements UserInformationDao { private QueryRunner runner; public UserInformationDaoImpl() { runner = JDBCUtils.getQueryRunner(); } // 注册 @Override public void register(UserInformation userInformation) throws SQLException { String sql = "insert into userInformation values(?,?,?,?,?)"; Object[] params = { userInformation.getUser(), userInformation.getPassword(), userInformation.getUname(), userInformation.getRoleid(), userInformation.getStatus() }; runner.update(sql, params); } @Override public Integer login(String user, String password) throws SQLException { Integer result = 0; String sql = "select count(*) from userInformation where `user`=? and `password`=?"; Object[] params = {user, password}; ScalarHandler handler = new ScalarHandler<>(); Long count = runner.query(sql, handler, params); Integer state = this.getState(user); if (count == 1) { result = 1; // 成功 } else if (state == 0) { result = 0; // 失败 } else { result = -1;// 禁用-1 成功1 失败0 } return result; } // 查询用户状态 1启用 0禁用 @Override public Integer getState(String account) throws SQLException { String sql = "select `status` from userInformation where user=?"; Object[] prams = {account}; ScalarHandler handler = new ScalarHandler<>(); Integer state = runner.query(sql, handler, prams); if (state == null) { return 0; } return state; } // 获取用户菜单 @Override public String[] getMenu(String account) throws SQLException { String sql = "select `menulist` from userrole where `roleid`=(select `roleid` from userInformation where `user`=?)"; Object[] params = {account}; ScalarHandler handler = new ScalarHandler(); String query = runner.query(sql, handler, params); return query.split("\\|"); } } package com.onlinestore.dao.impl; import com.onlinestore.dao.ShopCarDao; import com.onlinestore.pojo.CommodityInformation; import com.onlinestore.pojo.ShopCar; import com.onlinestore.pojo.UserInformation; import com.onlinestore.utils.JDBCUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import java.sql.SQLException; import java.util.List; /** * @author 涂鏊飞tu_aofei@163.com * @description: 购物车 类描述 * @create 2021-09-02 15:40 */ public class ShopCarDaoImpl implements ShopCarDao { private QueryRunner runner; public ShopCarDaoImpl() { runner = JDBCUtils.getQueryRunner(); } // 购物车添加 @Override public Integer updateShopCar(Integer no, String account, Integer count) throws SQLException { String sql = "select * from commodityInformation where commodityid=?"; Object[] params = {no}; BeanHandler handler = new BeanHandler<>(CommodityInformation.class); CommodityInformation query = runner.query(sql, handler, params); String updatesql = "insert into `shopCar` values(default,?,?,?,?,?,?)"; Object[] updateParams = { account, no, query.getCommodityname(), query.getPrice(), count, query.getPrice() * count }; int updateFlag = runner.update(updatesql, updateParams); return updateFlag; } // 查询用户购物车 @Override public List queryUserShopCar(String account) throws SQLException { String sql = "select * from shopCar where `user`=?"; Object[] params={account}; BeanListHandler handler = new BeanListHandler<>(ShopCar.class); List query = runner.query(sql, handler,params); return query; } // 提交订单后删除购物车记录 @Override public Integer deleteUserShopCar(String account) throws SQLException { String sql="delete from shopCar where `user`=?"; Object[] params={account}; int updateFlag = runner.update(sql, params); return updateFlag; } } package com.onlinestore.dao.impl; import com.onlinestore.dao.OrderDetailsDao; import com.onlinestore.pojo.Order; import com.onlinestore.pojo.OrderDetails; import com.onlinestore.pojo.ShopCar; import com.onlinestore.utils.JDBCUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.ArrayHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import java.sql.Array; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /** * @author 涂鏊飞tu_aofei@163.com * @description: TODO 类描述 * @create 2021-09-03 11:05 */ public class OrderDetailsDaoImpl implements OrderDetailsDao { private QueryRunner runner; public OrderDetailsDaoImpl() { runner = JDBCUtils.getQueryRunner(); } // 生成订单详细表 @Override public Integer addOrderDetails(String account) throws SQLException { String polymericQuerySql = "select o.orderid,s.commodityid,s.commodityname,s.price,s.amount,s.money from `shopCar` s inner join `order` o on s.`user`=o.`user` where o.`user`=?"; Object[] polymericQueryParams = {account}; BeanListHandler handler = new BeanListHandler<>(OrderDetails.class); // 获取订单详细信息映射 List queryDetails = runner.query(polymericQuerySql, handler, polymericQueryParams); String insertSql = null; Object[] insertSqlParams = null; int updateFlag = 0; // 循环插入数据给订单详细表 for (OrderDetails queryDetail : queryDetails) { insertSql = "insert into `orderdetails` values(default,?,?,?,?,?,?)"; insertSqlParams = new Object[]{ queryDetail.getOrderid(), queryDetail.getCommodityid(), queryDetail.getCommodityname(), queryDetail.getPrice(), queryDetail.getAmount(), queryDetail.getMoney() }; updateFlag += runner.update(insertSql, insertSqlParams); } return updateFlag; } // 查询订单明细 error @Override public List queryAllOrderDetails(String account) throws SQLException { // List orders = new OrderDaoImpl().queryUserOrder(account); // String sql = ""; // Object[] params={}; // List> lists= new ArrayList<>(); // BeanListHandler handler=new BeanListHandler<>(OrderDetails.class); // for (Order order : orders) { // sql = "select * from `order` where `orderid`=?"; // params= new Object[]{order.getOrderid()}; // System.out.println(params); // 空 // System.out.println(order); // List query = runner.query(sql, handler, params); // lists.add(query); // } String sql= "select *from `order` o inner join `orderdetails` ord on o.orderid=ord.orderid"; BeanListHandler handler=new BeanListHandler<>(OrderDetails.class); List query = runner.query(sql, handler); return query; } } package com.onlinestore.dao.impl; import com.onlinestore.dao.OrderDao; import com.onlinestore.pojo.Order; import com.onlinestore.utils.JDBCUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import javax.naming.spi.ObjectFactoryBuilder; import java.sql.SQLException; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import java.util.Random; import java.util.logging.SimpleFormatter; /** * @author 涂鏊飞tu_aofei@163.com * @description: 订单表 类描述 * @create 2021-09-02 17:19 */ public class OrderDaoImpl implements OrderDao { private QueryRunner runner; public OrderDaoImpl() { runner = JDBCUtils.getQueryRunner(); } // 根据用户信息,提交订单 @Override public Integer updateOrder(String user) throws SQLException, ParseException { String sumMoneysql = "select sum(`money`) from shopCar where `user`=?"; ScalarHandler handler = new ScalarHandler<>(); Object[] params_sum = { user }; Double sumMoney = runner.query(sumMoneysql, handler, params_sum); // 查找不到记录，返回0 if (sumMoney == null || sumMoney == 0.0) { return 0; } String orederDateTime = (new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())).concat(String.valueOf(Math.round(new Random().nextInt(900)+100))); Long orederDateTimeLong=Long.parseLong(orederDateTime); Object[] params_insert = { orederDateTimeLong, sumMoney, new SimpleDateFormat("yy-MM-dd HH:mm:ss").format(new Date()), user // 订单日期 }; String insertSql = "insert into `order` values(?,?,?,?)"; int updateFlag = runner.update(insertSql, params_insert); return updateFlag; } // 查询订单信息 @Override public List queryUserOrder(String user) throws SQLException { String sql = "select * from `order` where `user`=?"; BeanListHandler handler = new BeanListHandler<>(Order.class); Object[] params = {user}; List query = runner.query(sql, handler, params); return query; } } package com.onlinestore.dao.impl; import com.onlinestore.dao.CommodityInformationDao; import com.onlinestore.pojo.CommodityInformation; import com.onlinestore.utils.JDBCUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Random; /** * @author 涂鏊飞tu_aofei@163.com * @description: 展示商品列表 类描述 * 删除 商品id 自编号 使用truncate * @create 2021-09-02 14:55 */ public class CommodityInformationDaoImpl implements CommodityInformationDao { private QueryRunner runner; public CommodityInformationDaoImpl() { runner = JDBCUtils.getQueryRunner(); } @Override public List queryAll() throws SQLException { String sql = "select * from commodityInformation"; BeanListHandler handler = new BeanListHandler<>(CommodityInformation.class); List list = runner.query(sql, handler); return list; } @Override public Map quaryAllCommodityId(Integer orderid) throws SQLException { String sql = "select `commodityid` from commodityInformation"; MapListHandler handler = new MapListHandler(); List> query = runner.query(sql, handler); Map returnMap = new HashMap<>(); for (int i = 0; i < query.size(); i++) { for (String s : query.get(i).keySet()) { // 查找key加上随机数，value为商品编号 returnMap.put(s.concat(String.valueOf(new Random().nextInt())), Integer.parseInt(query.get(i).get(s).toString())); } } return returnMap; } } ``` * service接口 业务逻辑层 ```java package com.onlinestore.service; import com.onlinestore.pojo.UserInformation; import java.sql.SQLException; import java.util.List; public interface UserInformationService { public void register(UserInformation userInformation) throws SQLException; public Integer login(String user,String password) throws SQLException; public String[] getMenu(String account) throws SQLException; } package com.onlinestore.service; import com.onlinestore.pojo.ShopCar; import java.sql.SQLException; import java.util.List; public interface ShopCarService { public Integer updateShopCar(Integer no,String account,Integer count) throws SQLException; public List queryUserShopCar(String account) throws SQLException; public Integer deleteUserShopCar(String account) throws SQLException; } package com.onlinestore.service; import com.onlinestore.pojo.Order; import java.sql.SQLException; import java.text.ParseException; import java.util.List; public interface OrderService { public Integer updateOrder(String user) throws SQLException, ParseException; public List queryUserOrder(String user) throws SQLException; } package com.onlinestore.service; import com.onlinestore.pojo.OrderDetails; import java.sql.SQLException; import java.util.List; public interface OrderDetailsService { public Integer addOrderDetails(String account) throws SQLException; public List queryAllOrderDetails(String account) throws SQLException; } package com.onlinestore.service; import com.onlinestore.pojo.CommodityInformation; import java.sql.SQLException; import java.util.List; import java.util.Map; public interface CommodityInformationService { public List queryAll() throws SQLException; public Map quaryAllCommodityId(Integer commodityid) throws SQLException; } ``` * impl service接口实现类 ```java package com.onlinestore.service.impl; import com.onlinestore.dao.UserInformationDao; import com.onlinestore.dao.impl.UserInformationDaoImpl; import com.onlinestore.pojo.UserInformation; import com.onlinestore.service.UserInformationService; import java.sql.SQLException; /** * @author 涂鏊飞tu_aofei@163.com * @description: 用户逻辑层 用户登录和注册 类描述 * @create 2021-09-01 17:27 */ public class UserInformationServiceImpl implements UserInformationService { private UserInformationDao userInformationDao; public UserInformationServiceImpl() { userInformationDao = new UserInformationDaoImpl(); } @Override public void register(UserInformation ui) throws SQLException { userInformationDao.register(ui); } @Override public Integer login(String user, String password) throws SQLException { Integer loginFlag = userInformationDao.login(user, password); return loginFlag; } @Override public String[] getMenu(String account) throws SQLException { return userInformationDao.getMenu(account); } } package com.onlinestore.service.impl; import com.onlinestore.dao.ShopCarDao; import com.onlinestore.dao.impl.ShopCarDaoImpl; import com.onlinestore.pojo.ShopCar; import com.onlinestore.service.ShopCarService; import java.sql.SQLException; import java.util.List; /** * @author 涂鏊飞tu_aofei@163.com * @description: TODO 类描述 * @create 2021-09-02 15:56 */ public class ShopCarServiceImpl implements ShopCarService { private ShopCarDao shopCarDao; public ShopCarServiceImpl() { shopCarDao = new ShopCarDaoImpl(); } @Override public Integer updateShopCar(Integer no, String account, Integer count) throws SQLException { return shopCarDao.updateShopCar(no, account, count); } @Override public List queryUserShopCar(String account) throws SQLException { return shopCarDao.queryUserShopCar(account); } @Override public Integer deleteUserShopCar(String account) throws SQLException { return shopCarDao.deleteUserShopCar(account); } } package com.onlinestore.service.impl; import com.onlinestore.dao.OrderDao; import com.onlinestore.dao.impl.OrderDaoImpl; import com.onlinestore.pojo.Order; import com.onlinestore.service.OrderService; import java.sql.SQLException; import java.text.ParseException; import java.util.List; /** * @author 涂鏊飞tu_aofei@163.com * @description: TODO 类描述 * @create 2021-09-02 19:02 */ public class OrderServiceImpl implements OrderService { private OrderDao orderDao; public OrderServiceImpl() { orderDao = new OrderDaoImpl(); } @Override public Integer updateOrder(String user) throws SQLException, ParseException { return orderDao.updateOrder(user); } @Override public List queryUserOrder(String user) throws SQLException { return orderDao.queryUserOrder(user); } } package com.onlinestore.service.impl; import com.onlinestore.dao.OrderDetailsDao; import com.onlinestore.dao.impl.OrderDetailsDaoImpl; import com.onlinestore.pojo.OrderDetails; import com.onlinestore.service.OrderDetailsService; import java.sql.SQLException; import java.util.List; /** * @author 涂鏊飞tu_aofei@163.com * @description: TODO 类描述 * @create 2021-09-03 11:44 */ public class OrderDetailsServiceImpl implements OrderDetailsService { private OrderDetailsDao orderDetails; public OrderDetailsServiceImpl() { orderDetails = new OrderDetailsDaoImpl(); } @Override public Integer addOrderDetails(String account) throws SQLException { return orderDetails.addOrderDetails(account); } @Override public List queryAllOrderDetails(String account) throws SQLException { return orderDetails.queryAllOrderDetails(account); } } package com.onlinestore.service.impl; import com.onlinestore.dao.CommodityInformationDao; import com.onlinestore.dao.impl.CommodityInformationDaoImpl; import com.onlinestore.pojo.CommodityInformation; import com.onlinestore.service.CommodityInformationService; import java.sql.SQLException; import java.util.List; import java.util.Map; /** * @author 涂鏊飞tu_aofei@163.com * @description: TODO 类描述 * @create 2021-09-02 15:00 */ public class CommodityInformationServiceImpl implements CommodityInformationService { private CommodityInformationDao commodityInformationDao; public CommodityInformationServiceImpl() { commodityInformationDao = new CommodityInformationDaoImpl(); } @Override public List queryAll() throws SQLException { return commodityInformationDao.queryAll(); } @Override public Map quaryAllCommodityId(Integer commodityid) throws SQLException { return commodityInformationDao.quaryAllCommodityId(commodityid); } } ``` * utils 数据库工具类 ```java package com.onlinestore.utils; import com.alibaba.druid.pool.DruidDataSourceFactory; import org.apache.commons.dbutils.QueryRunner; import javax.sql.DataSource; import java.sql.CallableStatement; import java.sql.Connection; import java.util.Properties; /** * @author 涂鏊飞tu_aofei@163.com * @description: 数据库操作工具类 类描述 * @create 2021-09-01 17:18 */ public class JDBCUtils { private static DataSource ds; static { try { //1.加载配置文件 Properties pro = new Properties(); pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties")); //2.获取DataSource ds = DruidDataSourceFactory.createDataSource(pro); } catch (Exception e){ ds = null; } } private static Connection getConnection(){ Connection con; try { con = ds.getConnection(); } catch (Exception ex){ con = null; } return con; } /* 获取 QueryRunner */ public static QueryRunner getQueryRunner(){ QueryRunner runner = new QueryRunner(ds); return runner; } public static CallableStatement getStatement(String sql) throws Exception{ CallableStatement call = getConnection().prepareCall(sql); return call; } } ``` * ui 测试类 ```java package com.onlinestore.ui; import com.onlinestore.dao.UserInformationDao; import com.onlinestore.pojo.*; import com.onlinestore.service.*; import com.onlinestore.service.impl.*; import java.sql.SQLException; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; import org.junit.Test; import javax.management.relation.InvalidRoleValueException; /** * @author 涂鏊飞tu_aofei@163.com * @description: 测试类 类描述 * @create 2021-09-01 17:27 */ public class TestDemo { private static Scanner in; private static UserInformationService service; private static CommodityInformationService service_commodity; private static ShopCarService shopCarService; private static OrderService orderService; private static OrderDetailsService orderDetailsService; static { in = new Scanner(System.in); service = new UserInformationServiceImpl(); service_commodity = new CommodityInformationServiceImpl(); shopCarService = new ShopCarServiceImpl(); orderService = new OrderServiceImpl(); orderDetailsService = new OrderDetailsServiceImpl(); } @Test public void testDemo() { new TestDemo().start(); } private void start() { System.out.println("1. 登录 | 2. 注册"); System.out.println("请选择："); String choose = in.nextLine(); if (choose.equals("1")) { login(); } else if (choose.equals("2")) { System.out.println("1. 注册买家"); System.out.println("2. 注册商家"); String chooseTwoMenu = in.nextLine(); if (chooseTwoMenu.equals("1")) { registerBuyer(); } else if (chooseTwoMenu.equals("2")) { registerBusiness(); } else if (!choose.equals("1") || !choose.equals("2")) { throw new InputMismatchException("输入类型不匹配，请输入正确的数字！"); } } else if (!choose.equals("1") || !choose.equals("2")) { throw new InputMismatchException("输入类型不匹配，请输入正确的数字！"); } } private static void registerBuyer() { try { System.out.println("请输入新账号："); String account = in.next(); System.out.println("请输入密码："); String pwd = in.next(); System.out.println("请输入姓名："); String uname = in.next(); service.register(new UserInformation(account, pwd, uname, 1, 1)); System.out.println("买家注册成功!"); } catch (SQLException throwables) { throwables.printStackTrace(); } } private static void registerBusiness() { try { System.out.println("请输入新账号："); String account = in.next(); System.out.println("请输入密码："); String pwd = in.next(); System.out.println("请输入姓名："); String uname = in.next(); service.register(new UserInformation(account, pwd, uname, 2, 1)); System.out.println("商家注册成功!"); } catch (SQLException throwables) { throwables.printStackTrace(); } } private static void login() { try { System.out.println("请输入账号："); String account = in.next(); System.out.println("请输入密码："); String pwd = in.next(); Integer loginFlag = service.login(account, pwd); if (loginFlag == 1) { System.out.println("登陆成功!"); flow(account); } else if (loginFlag == 0) { System.out.println("账号或密码错误!"); } else { System.out.println("您已被禁用!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } //执行流程 private static void flow(String account) throws Exception { showHomeMenu(account); System.out.println("请选择菜单项[1 退出]："); String commChoose = in.next(); switch (commChoose) { case "商品列表": showCommodityList(); shopping(account); // 购物 System.out.println("选购完毕！，您的购物车的商品如下："); queryShopCar(account); System.out.println("是否结算?[y/n]"); commitOrder(account); //提交订单 break; case "查看购物车": System.out.println("您的购物车的商品如下："); queryShopCar(account); System.out.println("是否结算?[y/n]"); commitOrder(account); //提交订单 break; case "个人信息管理": break; case "1": System.exit(1); default: throw new InputMismatchException("输入类型不匹配，请输入正确的字符串！"); } } // 提交订单 private static void commitOrder(String account) throws Exception { String settleAccounts = in.next(); if (settleAccounts.equals("y") || settleAccounts.equals("Y")) { //结算 Integer orderInsertFlag = orderService.updateOrder(account);//提交订单 if (orderInsertFlag > 0) { System.out.println("订单已提交，订单数目:" + orderInsertFlag); } else { System.out.println("您的购物车为空！，无法生成订单！"); } // 生成订单明细表 Integer orderDetailsUpdateFlag = orderDetailsService.addOrderDetails(account); if (orderDetailsUpdateFlag > 0) { System.out.println("订单明细已生成，数目:" + orderDetailsUpdateFlag); } // 删除购物车记录 Integer deleteUserShopCarFlag = shopCarService.deleteUserShopCar(account); if (deleteUserShopCarFlag > 0) { System.out.println("用户" + account + "购物车记录已删除，更新记录条数:" + deleteUserShopCarFlag); } // 查看订单 queryOrder(account); } else { flow(account); } } // 查看订单，明细表 private static void queryOrder(String account) throws Exception { System.out.println("1. 查看您的所有订单"); System.out.println("2. 查看您的所有订单明细"); System.out.println("3. 返回主界面"); Integer orderChoose = in.nextInt(); switch (orderChoose) { case 1: System.out.println("已提交的订单如下："); List orderList = orderService.queryUserOrder(account); for (Order order : orderList) { System.out.println(order); } queryOrder(account);// 回调 break; case 2: System.out.println("已提交的订单详情如下："); List lists = orderDetailsService.queryAllOrderDetails(account); for (OrderDetails list : lists) { System.out.println(list); } queryOrder(account);// 回调 break; case 3: flow(account); // 返回主页面 break; default: System.out.println("输入错误！，请重新输入："); queryOrder(account); } } // 查看购物车 private static void queryShopCar(String account) throws SQLException { List shopCars = shopCarService.queryUserShopCar(account); shopCars.forEach(System.out::println); } // 购买商品 private static void shopping(String account) throws Exception { while (true) { System.out.println("请选择中意的商品编号..."); Integer no = in.nextInt(); Map maps = service_commodity.quaryAllCommodityId(no); if (maps.isEmpty() || !maps.containsValue(no)) { // throw new NullPointerException("商品编号不存在！"); System.out.println("商品编号不存在！"); shopping(account); // 继续购物 } System.out.println("请选择要购买的商品数量..."); Integer count = in.nextInt(); Integer flag = shopCarService.updateShopCar(no, account, count); System.out.println("加入购物车" + flag + "件商品"); System.out.println("是否继续购物[y/n]"); String continues = in.next(); if (!(continues.equals("y") || continues.equals("Y"))) { break; } showCommodityList(); } } // 展示首页 private static void showHomeMenu(String account) throws SQLException { String[] menu = service.getMenu(account); System.out.println("---------------------------------------"); System.out.println("-----------欢迎来到网购系统首页----------"); for (int i = 0; i < menu.length; i++) { System.out.println((i + 1) + "." + menu[i]); } System.out.println("---------------------------------------"); } // 商品列表 private static void showCommodityList() throws SQLException { List commodityInformations = service_commodity.queryAll(); for (CommodityInformation ci : commodityInformations) { System.out.println("------------------------------------------------------------------------------------------------------------------"); System.out.println(ci); } } } ```