以字符界面为载体的基于注解开发的购物系统
目录
6.数据库交互的Service层和Dao层设计-----------Service层实现
项目背景
在基本掌握了JavaSE的基本知识，以及MySQL数据库，JDBC编程等，为了能够将所学知识结合起来，在不涉及前端
知识的情况下，实现了一款纯字符界面的收银台系统。
项目需求
登录注册
管理端：用户管理/商品管理
用户端：商品浏览/订单管理/下单支付
涉及技术
集合框架
注解技术
MySQL数据库
DataSource连接数据库
JDBC编程
项目环境
IntelliJ IDEA 2019.2 x64
项目实现
1.准备数据库
在设计的构想中，需要帐号信息，商品信息，订单信息，订单项共四张表，如下：
-- 账号信息
drop table if exists `account`;
create table if not exists `account`
(
id int primary key auto_increment comment '帐号编号',
username varchar(12) not null comment '帐号',
password varchar(128) not null comment '密码',
name varchar(32) not null comment '姓名',
account_type int default 1 not null comment '帐号类型 1 管理员 2 客户',
account_status int default 1 not null comment '帐号状态 1 启用 2 锁定'
);
-- 商品信息
drop table if exists `goods`;
create table if not exists `goods`
(
id int primary key auto_increment comment '商品编号',
name varchar(128) not null comment '商品名称',
introduce varchar(256) default '暂无' not null comment '商品简介',
stock int not null comment '商品库存',
unit varchar(12) not null comment '库存单位',
price int not null comment '商品价格，单位：分',
discount int default 100 not null comment '商品折扣，[0,100]'
);
-- 订单
drop table if exists `order`;
create table if not exists `order`
(
id varchar(32) primary key comment '订单编号',
account_id int not null comment '帐号编号',
account_name varchar(12) not null comment '帐号',
create_time datetime not null comment '创建时间',
finish_time datetime default null comment '完成时间',
actual_amount int not null comment '实际金额，单位：分',
total_money int not null comment '总金额，单位：分',
order_status int not null comment '支付状态 1 待支付 2 完成'
);
-- 订单项
drop table if exists `order_item`;
create table if not exists `order_item`
(
id int primary key auto_increment comment '订单条目编号',
order_id varchar(32) not null comment '订单编号',
goods_id int not null comment '商品编号',
goods_name varchar(128) not null comment '商品名称',
goods_introduce varchar(256) default '暂无' not null comment '商品简介',
goods_num int not null comment '商品数量',
goods_unit varchar(12) not null comment '库存单位',
goods_price int not null comment '商品价格，单位：分',
goods_discount int default 100 not null comment '商品折扣，[0,100]'
);
其中，订单和订单项在数据库中是一对多的关系。
2.项目结构的分层设计
本项目使用如图所示的架构模式，最上层的字符界面交互层完成对命令的接收和解析，业务逻辑层根据解析命令得到的内容进行业务处理，与数据库访问层进行交互，最后数据库访问层完成对数据库的操作并返回操作结果。
3.创建项目
创建一个maven项目，并导入依赖的jar包。
<dependencies>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.6</version>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-codec/commons-codec -->
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.10</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.18</version>
<scope>provided</scope>
</dependency>
</dependencies>搭建文件目录结构如下：
4.实体类的创建
实体类的创建严格按照表设计来，包括属性名，这样会避免因为数据库的列名和实体类属性名命名不一致导致的诸多问题。
并且，因为使用了lombok，所以我们可以使用@Data注解，来快速生成一个类的get/set以及toString方法，简化代码。
在创建实体类之前，先创建3个枚举类，用于为实体类的一些属性（如账户状态的启用/封停）设置状态，如下：
a.账户状态---启用/封停：
package com.mos.common;
import lombok.Getter;
import lombok.ToString;
@Getter
@ToString
public enum AccountStatus {
UNLOCK(1,"启用"),LOCK(2,"封停");
private int flag;
private String desc;
AccountStatus(int flg,String desc) {
this.desc = desc;
this.flag = flg;
}
//查找值为flag 的状态 是启用 还是封停
public static AccountStatus valueOf(int flag) {
for(AccountStatus accountStatus :values()) {
if(accountStatus.flag == flag) {
return accountStatus;
}
}
throw new RuntimeException("AccountStatus flag " + flag + " not found.");
}
}
b.账户类型---管理员/客户
package com.mos.common;
import lombok.Getter;
import lombok.ToString;
@Getter
@ToString
public enum AccountType {
ADMIN(1,"管理员"),CUSTOMER(2,"客户");
private int flag;
private String desc;
AccountType(int flag,String desc) {
this.flag = flag;
this.desc = desc;
}
public static AccountType valueOf(int flag) {
for (AccountType accountTypes : values()) {
if(accountTypes.flag == flag) {
return accountTypes;
}
}
throw new RuntimeException("AccountType flag " + flag + " not found.");
}
}
c.订单状态---待支付/支付完成
package com.mos.common;
import lombok.Getter;
import lombok.ToString;
@Getter
@ToString
public enum OrderStatus {
PAYING(1, "待支付"), OK(2, "完成");
private final int flag;
private final String desc;
OrderStatus(int flag, String desc) {
this.flag = flag;
this.desc = desc;
}
//传入flg 为1 或者2 返回状态的对象 OrderStatus
public static OrderStatus valueOf(int flag) {
for (OrderStatus orderStatus : OrderStatus.values()) {
if (orderStatus.flag == flag) {
return orderStatus;
}
}
throw new RuntimeException("Not found flag = " + flag +
" in OrderStatus");
}
}
接下来创建实体类
代码如下：
帐号类：
package com.mos.entity;
import com.mos.common.AccountStatus;
import com.mos.common.AccountType;
import lombok.Data;
@Data
public class Account {
private Integer id;
private String username;
private String password;
private String name;
private AccountType accountType;//账户类型
private AccountStatus accountStatus;//账户状态
}
商品类：
这里需要注意的是，因为后面需要打印出商品列表，所以对该类的toString方法进行了重写，以便于我们按照需求将商品属性打印在控制台。
并且，因为后面的订单系统需要用到buyNum（需要购买货物的数量），所以需要添加此属性在类中。
package com.mos.entity;
import lombok.Data;
@Data
public class Goods {
private Integer id;//商品id
private String name;//商品名称
private String introduce;//商品简介
private Integer stock;//库存
private String unit;//库存个数
private Integer price;//价格
private Integer discount;//折扣
//需要购买的货物的数量
private Integer buyNum;
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append("【商品信息】:").append("\n")
.append("【商品编号】").append(this.getId()).append("\n")
.append("【商品名称】").append(this.getName()).append("\n")
.append("【商品简介】").append(this.getIntroduce()).append("\n")
.append("【商品库存】").append(this.getStock()).append(this.getUnit()).append("\n")
.append("【商品价格】") .append(String.format("%.2f", 1.00D * this.getPrice() / 100)).append(" (元) ").append("\n")
.append("【商品折扣】").append(this.getDiscount()).append("折").append("\n");
sb.append("======================================================");
return sb.toString();
}
}
订单类
订单类就稍微麻烦一点，主要有以下几点：
a.订单中的部分属性是时间格式，因为要存放到数据库中，所以要对其进行一些处理。
b.订单类也需要对toString进行重写，便于后面打印到控制台上。
c.为了显示出优惠金额，所以额外新增两个方法用于显示出优惠金额。
package com.mos.entity;
import com.mos.common.OrderStatus;
import lombok.Data;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
@Data
public class Order {
private String id;
private Integer accountId;
private String accountName;
private LocalDateTime createTime;
private LocalDateTime finishTime;
private Integer actualAmount;
private Integer totalMoney;
private OrderStatus orderStatus;
//存放订单项
private List<OrderItem> orderItemList = new ArrayList<>();
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append("【订单信息】*************************************").append("\n");
sb.append("\t").append("【用户名称】：").append(this.getAccountName()).append("\n");
sb.append("\t").append("【订单编号】：").append(this.getId()).append("\n");
sb.append("\t").append("【订单状态】：").append(this.getOrderStatus().getDesc()).append("\n");
sb.append("\t").append("【创建时间】：").append(this.timeToString(this.getCreateTime())).append("\n");
if (this.getOrderStatus() == OrderStatus.OK) {
sb.append("\t").append("【完成时间】：")
.append(this.timeToString(this.getFinishTime())).append("\n");
}
sb.append("【订单明细】*************************************").append("\n");
sb.append("\t编号 名称 数量 单位 单价（元）").append("\n");
int i = 0;
for (OrderItem orderItem : this.getOrderItemList()) {
sb.append("\t").append(++i).append(". ")
.append(orderItem.getGoodsName()).append(" ")
.append(orderItem.getGoodsNum()).append(" ")
.append(orderItem.getGoodsUnit()).append(" ")
.append(this.moneyToString(orderItem.getGoodsPrice())).append(" ")
.append("\n");
}
sb.append("【订单金额】*************************************").append("\n");
sb.append("\t").append("【总金额】：").append(this.moneyToString(this.getTotalMoney()))
.append(" 元 ").append("\n");
sb.append("\t").append("【优惠金额】：").append(this.moneyToString(this.getDiscount()))
.append(" 元 ").append("\n");
sb.append("\t").append("【应支付金额】：").append(this.moneyToString(this.getActualAmount()))
.append(" 元 ").append("\n");
return sb.toString();
}
private String moneyToString(int money) {
return String.format("%.2f", 1.00D * money / 100);
}
private String timeToString(LocalDateTime time) {
return DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss").format(time);
}
//优惠
public Integer getDiscount() {
return this.getTotalMoney() - this.getActualAmount();
}
}
订单项类：
package com.mos.entity;
import lombok.Data;
@Data
public class OrderItem {
private Integer id;
private String orderId;
private Integer goodsId;
private String goodsName;
private String goodsIntroduce;
private Integer goodsNum;
private String goodsUnit;
private Integer goodsPrice;
private Integer goodsDiscount;
}
5.命令的设计
设计一个Subject类用于对登录对象进行监视，并提供一个set/get方法
package com.mos.cmd.command;
import com.mos.entity.Account;
public class Subject {
private Account account;
public void setAccount(Account account) {
this.account = account;
}
public Account getAccount() {
return account;
}
}
因为所有的类都要创建Scanner对象，为了提高代码复用率，所以创建一个顶级接口，在此接口中生成一个Scanner对象，并且定义一个统一的抽象方法在实现类中用于执行命令，参数为Subject类的对象。
package com.mos.cmd.command;
import java.util.Scanner;
public interface Command {
Scanner scanner = new Scanner(System.in);
void execute(Subject subject);
}
在command目录下，创建一个impl目录专门用来存放此接口的实现类，首先提供一个抽象类实现上面的接口，在此类中启动各种service，并且提供三个方法，分别针对不同的想要输出的信息进行分类输出。
package com.mos.cmd.command.impl;
import com.mos.cmd.command.Command;
import com.mos.service.AccountService;
import com.mos.service.GoodsService;
import com.mos.service.OrderService;
public abstract class AbstractCommand implements Command {
public AccountService accountService;
public GoodsService goodsService;
public OrderService orderService;
public AbstractCommand() {
this.accountService = new AccountService();
this.goodsService = new GoodsService();
this.orderService = new OrderService();
}
protected final void warningPrintln(String message) {
System.out.println("【警告】："+ message);
}
protected final void errorPrintln(String message) {
System.out.println("【错误】："+ message);
}
protected final void hitPrintln(String message) {
System.out.println(">> "+ message);
}
}
再往下，就是各种命令，根据功能不同，有以下结构：
接下来，就是我这个项目所用的创新点，使用注解对各种命令进行标记，之后再根据反射进行解析，完成对命令的分类。
首先，创建四个注解类：
AdminCommand,CustomerCommand,EntranceCommand三个注解类都是用于标记命令的分类，分别对应管理员命令，客户命令，入口命令，而CommandMeta注解用于对命令的细节进行解析。
其中，@RetentionPolicy.RUNTIME的意思是，注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在；
Target(ElementType.TYPE):意为此注解用于类上面。
此时，提供一个Commands类将所有的命令分类放在三个map中，再根据输入的字符指令调用不同的命令。
package com.mos.cmd.command.impl;
import com.mos.cmd.command.Command;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.annotation.CustomerCommand;
import com.mos.cmd.command.annotation.EntranceCommand;
import com.mos.cmd.command.impl.account.AccountBrowseCommand;
import com.mos.cmd.command.impl.account.AccountPasswordResetCommand;
import com.mos.cmd.command.impl.account.AccountStatusSetCommand;
import com.mos.cmd.command.impl.common.AboutCommand;
import com.mos.cmd.command.impl.common.HelpCommand;
import com.mos.cmd.command.impl.common.QuitCommand;
import com.mos.cmd.command.impl.entrance.LoginCommand;
import com.mos.cmd.command.impl.entrance.RegisterCommand;
import com.mos.cmd.command.impl.goods.GoodsBrowseCommand;
import com.mos.cmd.command.impl.goods.GoodsPutAwayCommand;
import com.mos.cmd.command.impl.goods.GoodsSoldOutCommand;
import com.mos.cmd.command.impl.goods.GoodsUpdateCommand;
import com.mos.cmd.command.impl.order.OrderBrowseCommand;
import com.mos.cmd.command.impl.order.OrderPayCommand;
import java.util.*;
public class Commands {
// 先将三种命令分别存放在对应的Map集合中
public static Map<String, Command> ADMIN_COMMANDS = new HashMap<>();
public static Map<String, Command> CUSTOMER_COMMANDS = new HashMap<>();
public static Map<String, Command> ENTRANCE_COMMANDS = new HashMap<>();
//存放所有命令的集合
private static final Set<Command> COMMANDS = new HashSet<>();
private static final Command CACHED_HELP_COMMANDS;
static {
Collections.addAll(COMMANDS,
new AccountBrowseCommand(),
new AccountPasswordResetCommand(),
new AccountStatusSetCommand(),
new AboutCommand(),
//将helpCommand进行缓存
CACHED_HELP_COMMANDS = new HelpCommand(),
new HelpCommand(),
new QuitCommand(),
new LoginCommand(),
new RegisterCommand(),
new GoodsBrowseCommand(),
new GoodsPutAwayCommand(),
new GoodsSoldOutCommand(),
new GoodsUpdateCommand(),
new OrderBrowseCommand(),
new OrderPayCommand());
for (Command command : COMMANDS) {
//利用反射，将命令进行分类到不同的map
Class<?> cls = command.getClass();
AdminCommand adminCommand = cls.getDeclaredAnnotation(AdminCommand.class);
CustomerCommand customerCommand = cls.getDeclaredAnnotation(CustomerCommand.class);
EntranceCommand entranceCommand = cls.getDeclaredAnnotation(EntranceCommand.class);
CommandMeta commandMeta = cls.getDeclaredAnnotation(CommandMeta.class);
if (commandMeta == null) {
continue;
}
String commandKey = commandMeta.name();
if (adminCommand != null) {
ADMIN_COMMANDS.put(commandKey, command);
}
if (customerCommand != null) {
CUSTOMER_COMMANDS.put(commandKey, command);
}
if (entranceCommand != null) {
ENTRANCE_COMMANDS.put(commandKey, command);
}
}
}
//得到缓存的命令帮助
public static Command getCachedHelpCommands() {
return CACHED_HELP_COMMANDS;
}
public static Command getAdminCommand(String commandKey) {
return getCommand(commandKey, ADMIN_COMMANDS);
}
public static Command getCustomerCommand(String commandKey) {
return getCommand(commandKey, CUSTOMER_COMMANDS);
}
public static Command getEntranceCommand(String commandKey) {
return getCommand(commandKey, ENTRANCE_COMMANDS);
}
public static Command getCommand(String commandKey, Map<String, Command> commandMap) {
//遍历相应的Map，根据commandkey得到对应的value
return commandMap.getOrDefault(commandKey, CACHED_HELP_COMMANDS);
}
}
之后，根据每个命令的所属类型对其进行注解，并在控制台打印出输入指令之后的动作：
package com.mos.cmd.command.impl.account;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.impl.AbstractCommand;
import com.mos.entity.Account;
import java.util.List;
@CommandMeta(
name = "CKZH",
desc = "查看账号",
group = "账号信息"
)
@AdminCommand
public class AccountBrowseCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("账户浏览");
List<Account> accountList = this.accountService.queryAllAccount();
if(accountList.isEmpty()) {
System.out.println("暂且没有账号存在");
}else {
System.out.println("------------------账号信息列表---------------------");
System.out.println("| 编号 | 姓名 | 账号 | 密码 | 类型 | 状态 |");
for (Account account: accountList ) {
String str = new StringBuilder().append("| ").append(account.getId()).append(" ")
.append("| ").append(account.getName()).append(" ")
.append("| ").append(account.getUsername()).append(" ")
.append("| ").append("******").append(" ")
.append("| ").append(account.getAccountType().getDesc()).append(" ")
.append("| ").append(account.getAccountStatus().getDesc()).append(" ")
.append("| ").toString();
System.out.println(str);
}
System.out.println("--------------------------------------------");
}
}
}
package com.mos.cmd.command.impl.account;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.impl.AbstractCommand;
@CommandMeta(
name = "CZMM",
desc = "重置密码",
group = "账号信息"
)
@AdminCommand
public class AccountPasswordResetCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("密码重置");
}
}
package com.mos.cmd.command.impl.account;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.impl.AbstractCommand;
@CommandMeta(
name = "QTZH",
desc = "启停账号",
group = "账号信息"
)
@AdminCommand
public class AccountStatusSetCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("启停账号");
}
}
package com.mos.cmd.command.impl.common;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.annotation.CustomerCommand;
import com.mos.cmd.command.annotation.EntranceCommand;
import com.mos.cmd.command.impl.AbstractCommand;
@CommandMeta(
name = "GYXT",
desc = "关于系统",
group = "公共命令"
)
@EntranceCommand
@AdminCommand
@CustomerCommand
public class AboutCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("关于系统");
System.out.println("作者：MOS");
System.out.println("时间：20190803");
}
}
帮助页面，因为要分情况并且有条理地打印出来所需要的信息，是很重要的一部分，所以写了很多注释 -.-
package com.mos.cmd.command.impl.common;
import com.mos.cmd.command.Command;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.annotation.CustomerCommand;
import com.mos.cmd.command.annotation.EntranceCommand;
import com.mos.cmd.command.impl.AbstractCommand;
import com.mos.cmd.command.impl.Commands;
import com.mos.entity.Account;
import java.util.*;
@CommandMeta(
name = "BZXX",
desc = "帮助信息",
group = "公共命令"
)
@EntranceCommand
@AdminCommand
@CustomerCommand
public class HelpCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("帮助信息");
Account account = subject.getAccount();
if (account == null) {
entranceHelp();
} else {
//当登录成功之后 ，根据账户类型提供一个帮助页面
switch (account.getAccountType()) {
case CUSTOMER:
customerHelp();
break;
case ADMIN:
adminHelp();
break;
default:
}
}
}
public void entranceHelp() {
printHelp("欢迎", Commands.ENTRANCE_COMMANDS.values());
}
public void adminHelp() {
printHelp("管理端", Commands.ADMIN_COMMANDS.values());
}
public void customerHelp() {
printHelp("客户端", Commands.CUSTOMER_COMMANDS.values());
}
//Map.values()方法的返回值为Collection 这里存放的是command 每个command都有CommandMeta
public void printHelp(String title, Collection<Command> collection) {
System.out.println("***********" + title + "*************");
//组装：将分组作为key值 将描述desc和name作为value值
// key=入口命令 value 登录(DL)
Map<String, List<String>> helpInfo = new HashMap<>();
for (Command command : collection) {
CommandMeta commandMeta = command.getClass().getDeclaredAnnotation(CommandMeta.class);
String group = commandMeta.group();
//第一个参数是key lambda表达式写法
/* List<String> funcs = helpInfo.computeIfAbsent(group, s -> new ArrayList<>());
// 登录 ("DL","LOGIN")
funcs.add(commandMeta.desc() + "(" + (commandMeta.name()) + ")");//*/
List<String> funcs = helpInfo.get(group);
if (funcs == null) {
funcs = new ArrayList<>();
helpInfo.put(group, funcs);
}
//funcs.add(commandMeta.desc() + "(" + commandMeta.name() + ")");
funcs.add(commandMeta.desc() + "(" + commandMeta.name() + ")");
}
int i = 0;
//Map中采用Entry内部类来表示一个映射项，映射项包含Key和Value
//Map.Entry里面包含getKey()和getValue()方法
//entrySet 键-值 对的集合
for (Map.Entry<String, List<String>> entry : helpInfo.entrySet()) {
i++;
System.out.println(i + ". " + entry.getKey());//公共命令
int j = 0;
for (String item : entry.getValue()) {// 上面的 funcs.add：commandMeta.desc()+ commandMeta.name()
j++;
System.out.println("\t" + (i) + "." + (j) + " " + item);
}
}
System.out.println("输入菜单括号后面的编号（忽略大小写），进行下一步操作");
System.out.println("*************************************************");
}
private String join(String[] array) {
StringBuilder sb = new StringBuilder();
for (String item : array) {
sb.append(item).append(", ");
}
sb.setLength(sb.length() - 2);
return sb.toString();
}
}
package com.mos.cmd.command.impl.common;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.annotation.CustomerCommand;
import com.mos.cmd.command.annotation.EntranceCommand;
import com.mos.cmd.command.impl.AbstractCommand;
@CommandMeta(
name = "TCXT",
desc = "退出系统",
group = "公共命令"
)
@EntranceCommand
@AdminCommand
@CustomerCommand
public class QuitCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("退出系统,欢迎下次使用");
this.scanner.close();
System.exit(0);//正常退出
}
}
package com.mos.cmd.command.impl.entrance;
import com.mos.cmd.command.Command;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.annotation.EntranceCommand;
import com.mos.cmd.command.impl.AbstractCommand;
import com.mos.common.AccountStatus;
import com.mos.entity.Account;
@CommandMeta(
name = "DL",
desc = "登录",
group = "入口命令"
)
@EntranceCommand
public class LoginCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("登录");
Account account = subject.getAccount();
if(account != null) {
System.out.println("已经登录过了");
return;
}
System.out.println("请输入用户名：");
String username = Command.scanner.nextLine();
System.out.println("请输入密码：");
String password = Command.scanner.nextLine();
//去数据查询是否有此账户，有返回，无返回null
account = this.accountService.login(username,password);
if(account != null && account.getAccountStatus() == AccountStatus.UNLOCK) {
System.out.println(account.getAccountType().getDesc()+"登录成功");
subject.setAccount(account);
}else {
System.out.println("登录失败，密码或者用户名错误");
}
}
}
package com.mos.cmd.command.impl.entrance;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.annotation.EntranceCommand;
import com.mos.cmd.command.impl.AbstractCommand;
import com.mos.common.AccountStatus;
import com.mos.common.AccountType;
import com.mos.entity.Account;
@CommandMeta(
name = "ZC",
desc = "注册",
group = "入口命令"
)
@EntranceCommand
public class RegisterCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("注册");
System.out.println("请输入用户名：");
String username = this.scanner.nextLine();
//检查用户名是否一致 AccountService --> AccountDao
System.out.println("请输入密码：");
String password1 = scanner.nextLine();
System.out.println("请再次输入密码：");
String password2 = scanner.nextLine();
if(!password1.equals(password2)) {
System.out.println("两次输入的密码不一致");
return;
}
System.out.println("请输入姓名：");
String name = scanner.nextLine();
System.out.println("请输入账户类型：1.管理员 2.客户");
int accountTypeFlag = scanner.nextInt();
AccountType accountType = AccountType.valueOf(accountTypeFlag);
final Account account = new Account();
account.setUsername(username);
account.setPassword(password1);
account.setAccountType(accountType);
account.setName(name);
account.setAccountStatus(AccountStatus.UNLOCK);
boolean effect = this.accountService.register(account);
if(effect) {
System.out.println(accountType+"恭喜注册成功");
}else {
System.out.println("注册失败");
}
}
}
package com.mos.cmd.command.impl.goods;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.annotation.CustomerCommand;
import com.mos.cmd.command.impl.AbstractCommand;
import com.mos.entity.Goods;
import java.util.List;
@CommandMeta(
name = "LLSP",
desc = "浏览商品",
group = "商品信息"
)
@AdminCommand
@CustomerCommand
public class GoodsBrowseCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("商品浏览");
List<Goods> goodsList = this.goodsService.queryAllGoods();
if(goodsList.isEmpty()) {
System.out.println("没有任何的商品");
}else {
for (Goods goods : goodsList) {
System.out.println(goods);
}
}
}
}
package com.mos.cmd.command.impl.goods;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.impl.AbstractCommand;
import com.mos.entity.Goods;
@CommandMeta(
name = "SJSP",
desc = "上架商品",
group = "商品信息"
)
@AdminCommand
public class GoodsPutAwayCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("上架商品");
System.out.println("请输入商品名称：");
String name = scanner.next();
System.out.println("请输入商品简介：");
String introduce = scanner.next();
System.out.println("请输入商品单位：个，包，箱，瓶，千克");
String unit = scanner.next();
System.out.println("请输入商品库存：");
int stock = scanner.nextInt();
System.out.println("请输入商品价格：单位：元");
//存入之前 把小数转换为整数存放到数据库 取的时候除以100就行
int price = new Double(100 * scanner.nextDouble()).intValue();
System.out.println("请输入商品折扣：75表示75折");
int discount = scanner.nextInt();
Goods goods = new Goods();
goods.setName(name);
goods.setIntroduce(introduce);
goods.setStock(stock);
goods.setUnit(unit);
goods.setPrice(price);
goods.setDiscount(discount);
boolean effect = this.goodsService.putAwayGoods(goods);
if(effect) {
System.out.println("上架成功");
}else{
System.out.println("上架失败");
}
}
}
package com.mos.cmd.command.impl.goods;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.impl.AbstractCommand;
import com.mos.entity.Goods;
@CommandMeta(
name = "XJSP",
desc = "下架商品",
group = "商品信息"
)
@AdminCommand
public class GoodsSoldOutCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("下架商品");
//根据商品下架的编号，先去 查询 是否有此商品 如果有通过
// this.goodsService.soldOutGoods(goodsId); 去操作DAO层
//进行 删除数据库 对应项
System.out.println("请输入下架商品编号：");
int goodsId = scanner.nextInt();
Goods goods = this.goodsService.getGoods(goodsId);
if (goods == null) {
warningPrintln("下架商品不存在");
} else {
System.out.println("下架商品如下：");
System.out.println(goods);
hitPrintln("确认是否下架（y/n yes/no）: ");
String confirm = scanner.next();
if ("y".equalsIgnoreCase(confirm) || "yes".equalsIgnoreCase(confirm)) {
boolean effect = this.goodsService.soldOutGoods(goodsId);
if (effect) {
System.out.println("商品成功下架");
} else {
errorPrintln("商品下架失败，稍后重试");
}
}
}
}
}
package com.mos.cmd.command.impl.goods;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.AdminCommand;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.impl.AbstractCommand;
import com.mos.entity.Goods;
@CommandMeta(
name = "GXSP",
desc = "更新商品",
group = "商品信息"
)
@AdminCommand
public class GoodsUpdateCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("更新商品信息");
System.out.println("请输入需要更新的商品编号：");
int goodsId = scanner.nextInt();
Goods goods = this.goodsService.getGoods(goodsId);
if(goods == null) {
System.out.println("您输入的编号不存在");
}else {
System.out.println("商品的原信息如下：");
System.out.println(goods);
hitPrintln("请输入更新的商品简介:");
String introduce = scanner.next();
hitPrintln("请输入更新商品库存：");
int stock = scanner.nextInt();
hitPrintln("请输入更新的商品单位：个，包，箱，瓶，千克");
String unit = scanner.next();
hitPrintln("请输入更新商品价格：单位：元");
//存入之前 把小数转换为整数存放到数据库 取的时候除以100就行
int price = new Double(100 * scanner.nextDouble()).intValue();
hitPrintln("请输入更新商品折扣：75表示75折");
int discount = scanner.nextInt();
System.out.println("请输入是否更新(y/n)");
String confirm = scanner.next();
if("y".equalsIgnoreCase(confirm)) {
goods.setIntroduce(introduce);
goods.setUnit(unit);
goods.setStock(stock);
goods.setPrice(price);
goods.setDiscount(discount);
//去更新数据库
boolean effect = this.goodsService.updateGoods(goods);
if(effect){
System.out.println("更新商品成功");
}else {
System.out.println("更新失败");
}
}
}
}
}
package com.mos.cmd.command.impl.order;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.annotation.CustomerCommand;
import com.mos.cmd.command.impl.AbstractCommand;
import com.mos.entity.Order;
import java.util.List;
@CommandMeta(
name = "CKDD",
desc = "查看订单",
group = "我的订单"
)
@CustomerCommand
public class OrderBrowseCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {
System.out.println("我的订单列表：");
List<Order> orderList = this.orderService.queryOrderByAccount(subject.getAccount().getId());
if (orderList.isEmpty()) {
System.out.println("暂时没有订单");
} else {
for (Order order : orderList) {
System.out.println("-------------------- 开始分割线 ------------------------");
System.out.println(order);
System.out.println("-------------------- 结束分割线 ------------------------");
}
}
}
}
package com.mos.cmd.command.impl.order;
import com.mos.cmd.command.Subject;
import com.mos.cmd.command.annotation.CommandMeta;
import com.mos.cmd.command.annotation.CustomerCommand;
import com.mos.cmd.command.impl.AbstractCommand;
import com.mos.common.OrderStatus;
import com.mos.entity.Goods;
import com.mos.entity.Order;
import com.mos.entity.OrderItem;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
@CommandMeta(
name = "ZFDD",
desc = "支付订单",
group = "我的订单"
)
@CustomerCommand
public class OrderPayCommand extends AbstractCommand {
@Override
public void execute(Subject subject) {//货物id 账户List<Goods>,String int userid,userAccout
System.out.println("请输入你要购买的货物id以" +
"及数量多个货物之间使用,号隔开：格式：1-8,3-5");
String string = scanner.nextLine();
String[] strings = string.split(",");
List<Goods> goodsList = new ArrayList<>();
for (String str : strings ) {//1-8
String[] strings2 = str.split("-");
Goods goods = this.goodsService.getGoods(Integer.parseInt(strings2[0]));
goods.setBuyNum(Integer.parseInt(strings2[1]));
goodsList.add(goods);
}
Order order = new Order();
order.setId(String.valueOf(System.currentTimeMillis()));
order.setAccountId(subject.getAccount().getId());
order.setAccountName(subject.getAccount().getName());
order.setCreateTime(LocalDateTime.now());
order.setOrderStatus(OrderStatus.PAYING);
int totalMoney = 0;
int actualAmount = 0;
//计算需要的总金额
for (Goods goods : goodsList) {
OrderItem orderItem = new OrderItem();
orderItem.setOrderId(order.getId());
orderItem.setGoodsId( goods.getId());
orderItem.setGoodsName(goods.getName());
orderItem.setGoodsUnit(goods.getUnit());
orderItem.setGoodsPrice(goods.getPrice());
orderItem.setGoodsDiscount(goods.getDiscount());
orderItem.setGoodsNum(goods.getBuyNum());
orderItem.setGoodsIntroduce(goods.getIntroduce());
order.getOrderItemList().add(orderItem);
int currentMoney = goods.getPrice()*goods.getBuyNum();
totalMoney += currentMoney;
actualAmount += currentMoney*goods.getDiscount() / 100;
}
order.setActualAmount(actualAmount);
order.setTotalMoney(totalMoney);
/*
* 加入订单项当中
* */
System.out.println(order);
System.out.println("以上为订单信息，请支付：zf");
String confirm = scanner.next();
if("zf".equalsIgnoreCase(confirm)) {
order.setFinishTime(LocalDateTime.now());
order.setOrderStatus(OrderStatus.OK);
boolean effect = this.orderService.commitOrder(order);
if(effect) { //说明插入订单和订单项成功
//将goods表中的 具体货物数量更新
for (Goods goods : goodsList) {
boolean isUpdate = this.goodsService.updateGoodsAfterPay(goods,goods.getBuyNum());
if(isUpdate) {
System.out.println("库存已经更新");
}
}
}
}else {
System.out.println("您未成功支付！");
}
}
}
到此为止，操作页面及命令的设计已经结束，下面便开始实现与数据库的交互。
6.数据库交互的Service层和Dao层设计-----------Service层实现
这里，使用了Service层作为启动Dao层的方式，而不是直接将上层与数据库操作相连，在上面的AbstractCommand类被实现时，启动所有的服务Service，并在Service中调用Dao层。
分别针对账户、商品和订单的三种Service的代码如下：
package com.mos.service;
import com.mos.dao.AccountDao;
import com.mos.entity.Account;
import java.util.List;
public class AccountService {
private AccountDao accountDao;
public AccountService() {
this.accountDao = new AccountDao();
}
public boolean register(Account account) {
return this.accountDao.register(account);
}
public Account login(String username, String password ) {
return this.accountDao.login(username,password);
}
public List<Account> queryAllAccount(){
return this.accountDao.queryAccount();
}
}package com.mos.service;
import com.mos.dao.GoodsDao;
import com.mos.entity.Goods;
import java.util.List;
public class GoodsService {
private GoodsDao goodsDao;
public GoodsService() {
this.goodsDao = new GoodsDao();
}
public boolean putAwayGoods(Goods goods) {
return this.goodsDao.insertGoods(goods);
}
public boolean soldOutGoods(int goodsId) {
return this.goodsDao.deleteGoods(goodsId);
}
public boolean updateGoods(Goods goods) {
return this.goodsDao.modifyGoods(goods);
}
public Goods getGoods(int goodsId) {
return this.goodsDao.queryGoodsById(goodsId);
}
public List<Goods> queryAllGoods() {
return this.goodsDao.queryAllGoods();
}
public boolean updateGoodsAfterPay(Goods goods,int goodsNum) {
return this.goodsDao.updateGoodsAfterPay(goods,goodsNum);
}
}
package com.mos.service;
import com.mos.dao.BaseDao;
import com.mos.dao.OrderDao;
import com.mos.entity.Order;
import java.util.List;
public class OrderService extends BaseDao {
private GoodsService goodsService;
private OrderDao orderDao;
public OrderService() {
this.goodsService = new GoodsService();
this.orderDao = new OrderDao();
}
public List<Order> queryOrderByAccount(Integer accountId) {
return this.orderDao.queryOrderByAccount(accountId);
}
public boolean commitOrder(Order order) {
//提交订单
return this.orderDao.insertOrder(order);
}
}
然后，准备实现Dao层。
7.实现Dao层
先准备一个BaseDao类，用于提供几个公用方法，用于提高代码重用性。
这里我们使用了数据源MysqlDataSource，需要注意的是，因为我们要用到中文，所以需要设置字符集类型为UTF-8。
package com.mos.dao;
import com.mysql.cj.jdbc.MysqlDataSource;
import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BaseDao {
private static volatile DataSource dataSource;
//采用基本实现
private DataSource getDataSource() throws SQLException {
if (dataSource == null) {
synchronized(DataSource.class) {
if (dataSource == null) {
dataSource = new MysqlDataSource();//MySql数据源
String host = "127.0.0.1";
String port = "3306";
((MysqlDataSource) dataSource).setUrl("jdbc:mysql://" + (host + ":" + port) + "/wanda");
((MysqlDataSource) dataSource).setUser("root");
((MysqlDataSource) dataSource).setPassword("177458");
((MysqlDataSource) dataSource).setCharacterEncoding("UTF-8");
}
}
}
return dataSource;
}
protected Connection getConnection(boolean autoCommit) throws SQLException {
//获取连接
Connection connection = this.getDataSource().getConnection();
connection.setAutoCommit(autoCommit);
return connection;
}
protected void closeResource(ResultSet resultSet, PreparedStatement statement, Connection connection) {
//结果 -> 命令 -> 连接
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public String getSql(String sqlName) {
System.out.println("=====sqlName:"+sqlName);
//InputStream 是字节流
try (InputStream in = this.getClass()
.getClassLoader()
//用来获取配置文件，方法传入的参数是一个路径
.getResourceAsStream("script/" + sqlName.substring(1) + ".sql");
// 从1 开始提取的原因是：sqlName: @query_order_by_account 去掉@符号
) {
if (in == null) {
throw new RuntimeException("load sql " + sqlName + " failed");
} else {
//InputStreamReader :字节流 通向字符流的桥梁
try (InputStreamReader isr = new InputStreamReader(in);
//BufferedReader -> 从字符输入流中读取文本并缓冲字符
BufferedReader reader = new BufferedReader(isr)) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append(reader.readLine());
String line;
while (( line = reader.readLine()) != null) {
stringBuilder.append(" ").append(line);
}
return stringBuilder.toString();
}
}
} catch (IOException e) {
throw new RuntimeException("load sql " + sqlName + " failed");
}
}
}
因为涉及到多表查询，为了方便操作，使用了一个配置文件。
//@query_order_by_account 配置文件名
select o1.id as order_id,
o1.account_id as account_id,
o1.create_time as create_time,
o1.finish_time as finish_time,
o1.actual_amount as actual_amount,
o1.total_money as total_money,
o1.order_status as order_status,
o1.account_name as account_name,
o2.id as item_id,
o2.goods_id as goods_id,
o2.goods_name as goods_name,
o2.goods_introduce as goods_introduce,
o2.goods_num as goods_num,
o2.goods_unit as goods_unit,
o2.goods_price as goods_price,
o2.goods_discount as goods_discount
from `order` as o1
left join order_item as o2 on
o1.id = o2.order_id
where o1.account_id = ? order by order_id;然后是几个Dao类的具体实现，在类中为每个命令都提供一个方法，便于在调用命令时执行对应的方法：
package com.mos.dao;
import com.mos.common.AccountStatus;
import com.mos.common.AccountType;
import com.mos.entity.Account;
import org.apache.commons.codec.digest.DigestUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class AccountDao extends BaseDao {
/**
* 登录
*
* @return
*/
public Account login(String username, String password) {
//连接数据库
Connection connection = null;
//先将Sql语句进行预编译存储到PreparedStatement对象 可以使用此对象多次高效地执行改语句
// 适合用于多次执行的SQL语句，可以防止SQL注入 预编译好之后，不用再次编译，提高了效率
// 最终会生成一个ResultSet对象 支持批处理 适合批量操作
// 该对象将生成具有给定类型和并发性的 ResultSet 对象；
PreparedStatement statement = null;
ResultSet resultSet = null;
Account account = null;
try {
connection = this.getConnection(true);
String sql = "select id, username, password, name,account_type, account_status from account where username=? and password=?";
statement = connection.prepareStatement(sql);
statement.setString(1, username);
statement.setString(2, DigestUtils.md5Hex(password));
resultSet = statement.executeQuery();
if (resultSet.next()) {
account = this.extractAccount(resultSet);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeResource(resultSet, statement, connection);
}
return account;
}
/**
* 注册
*
* @param account
* @return
*/
public boolean register(Account account) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
boolean effect = false;
try {
connection = this.getConnection(true);
String sql = "insert into account (username, password, name, account_type, account_status) values (?,?,?,?,?)";
statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
statement.setString(1, account.getUsername());
statement.setString(2, DigestUtils.md5Hex(account.getPassword()));
statement.setString(3, account.getName());
statement.setInt(4, account.getAccountType().getFlag());
statement.setInt(5, account.getAccountStatus().getFlag());
effect = (statement.executeUpdate() == 1);
//获取自增主键到集合resultSet
resultSet = statement.getGeneratedKeys();
if (resultSet.next()) {
Integer id = resultSet.getInt(1);
account.setId(id);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeResource(resultSet, statement, connection);
}
return effect;
}
/**
* 检查重名
*
* @param userName
* @return
*/
public boolean checkUserName(String userName) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = this.getConnection(true);
String sql = "select * from account where username=?";
statement = connection.prepareStatement(sql);
statement.setString(1, userName);
resultSet = statement.executeQuery();
if (resultSet.next()) {
return true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeResource(resultSet, statement, connection);
}
return false;
}
public List<Account> queryAccount() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Account> accountList = new ArrayList<>();
try {
connection = this.getConnection(true);
String sql = "select id, username, password, name, account_type, account_status from account";
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
accountList.add(this.extractAccount(resultSet));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeResource(resultSet, statement, connection);
}
return accountList;
}
private Account extractAccount(ResultSet resultSet) throws SQLException {
Account account = new Account();
account.setId(resultSet.getInt("id"));
account.setUsername(resultSet.getString("username"));
account.setPassword(resultSet.getString("password"));
account.setName(resultSet.getString("name"));
account.setAccountType(AccountType.valueOf(resultSet.getInt("account_type")));
account.setAccountStatus(AccountStatus.valueOf(resultSet.getInt("account_status")));
return account;
}
}
package com.mos.dao;
import com.mos.entity.Goods;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class GoodsDao extends BaseDao {
//上架商品
public boolean insertGoods(Goods goods) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
boolean effect = false;
try {
String sql = "insert into goods (name,introduce, stock, unit, price, discount) value (?,?,?,?,?,?)";
connection = this.getConnection(true);
statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
statement.setString(1, goods.getName());
statement.setString(2, goods.getIntroduce());
statement.setInt(3, goods.getStock());
statement.setString(4, goods.getUnit());
statement.setInt(5, goods.getPrice());
statement.setInt(6, goods.getDiscount());
effect = statement.executeUpdate() == 1;
resultSet = statement.getGeneratedKeys();
if (resultSet.next()) {
goods.setId(resultSet.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeResource(resultSet, statement, connection);
}
return effect;
}
//更新商品
public boolean modifyGoods(Goods goods) {
//名称，简介，价格，折扣，库存
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = this.getConnection(true);
String sql = "update goods set name=? , introduce =?, stock=? , price=?, discount =? where id= ?";
statement = connection.prepareStatement(sql);
statement.setString(1, goods.getName());
statement.setString(2, goods.getIntroduce());
statement.setInt(3, goods.getStock());
statement.setInt(4, goods.getPrice());
statement.setInt(5, goods.getDiscount());
statement.setInt(6, goods.getId());
return statement.executeUpdate() == 1;
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeResource(resultSet, statement, connection);
}
return false;
}
//下架商品
public boolean deleteGoods(int goodsId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = this.getConnection(true);
String sql = "delete from goods where id=?";
statement = connection.prepareStatement(sql);
statement.setInt(1, goodsId);
return statement.executeUpdate() == 1;
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeResource(resultSet, statement, connection);
}
return false;
}
//遍历goods表 查询所有的商品 存放至List<Goods>
public List<Goods> queryAllGoods() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Goods> goodsArrayList = new ArrayList<>();
try {
connection = this.getConnection(true);
String sql = "select id, name, introduce, stock, unit, price, discount from goods ";
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
Goods goods = this.extractGoods(resultSet);
if (goods != null) {
goodsArrayList.add(goods);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeResource(resultSet, statement, connection);
}
return goodsArrayList;
}
//通过id查找 对应的货物
public Goods queryGoodsById(int goodsId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = this.getConnection(true);
String sql = "select id, name, introduce, stock, unit, price, discount from goods where id =?";
statement = connection.prepareStatement(sql);
statement.setInt(1, goodsId);
resultSet = statement.executeQuery();
if (resultSet.next()) {
return extractGoods(resultSet);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeResource(resultSet, statement, connection);
}
return null;
}
//将集合汇中的数据进行解析
private Goods extractGoods(ResultSet resultSet) throws SQLException {
Goods goods = new Goods();
goods.setId(resultSet.getInt("id"));
goods.setName(resultSet.getString("name"));
goods.setIntroduce(resultSet.getString("introduce"));
goods.setStock(resultSet.getInt("stock"));
goods.setUnit(resultSet.getString("unit"));
goods.setPrice(resultSet.getInt("price"));
goods.setDiscount(resultSet.getInt("discount"));
return goods;
}
//支付之后 更新货物库存
public boolean updateGoodsAfterPay(Goods goods,int goodsNum) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = this.getConnection(true);
String sql = "update goods set stock=? where id= ?";
statement = connection.prepareStatement(sql);
statement.setInt(1, goods.getStock() - goodsNum);
statement.setInt(2, goods.getId());
return statement.executeUpdate() == 1;
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeResource(resultSet, statement, connection);
}
return false;
}
}
package com.mos.dao;
import com.mos.common.OrderStatus;
import com.mos.entity.Order;
import com.mos.entity.OrderItem;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class OrderDao extends BaseDao {
/**
* 插入订单
* @param order
* @return
*/
public boolean insertOrder(Order order) {
Connection connection = null;
PreparedStatement statement = null;
String insertOrderSql = "insert into `order`(id, account_id, create_time, finish_time, actual_amount, total_money, order_status, account_name) values (?,?,?,?,?,?,?,?)";
String insertOrderItemSql = "insert into order_item(order_id, goods_id, goods_name, goods_introduce, goods_num, goods_unit, goods_price, goods_discount) VALUES (?,?,?,?,?,?,?,?)";
boolean effect = false;
try {
connection = this.getConnection(false);
statement = connection.prepareStatement(insertOrderSql);
statement.setString(1, order.getId());
statement.setInt(2, order.getAccountId());
statement.setTimestamp(3, Timestamp.valueOf(order.getCreateTime()));
statement.setTimestamp(4, order.getFinishTime() == null ? null : Timestamp.valueOf(order.getCreateTime()));
statement.setInt(5, order.getActualAmount());
statement.setInt(6, order.getTotalMoney());
statement.setInt(7, order.getOrderStatus().getFlag());
statement.setString(8, order.getAccountName());
effect = statement.executeUpdate() == 1;
statement = connection.prepareStatement(insertOrderItemSql);
for (OrderItem orderItem : order.getOrderItemList()) {
statement.setString(1, orderItem.getOrderId());
statement.setInt(2, orderItem.getGoodsId());
statement.setString(3, orderItem.getGoodsName());
statement.setString(4, orderItem.getGoodsIntroduce());
statement.setInt(5, orderItem.getGoodsNum());
statement.setString(6, orderItem.getGoodsUnit());
statement.setInt(7, orderItem.getGoodsPrice());
statement.setInt(8, orderItem.getGoodsDiscount());
//批量执行sql语句
statement.addBatch();
}
//提交一批要执行的更新命令
int[] effects = statement.executeBatch();
for (int i : effects) {
effect = i == 1;
}
if (effect) {
connection.commit();
} else {
try {
//回滚操作
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
} catch (SQLException e) {
e.printStackTrace();
if (connection != null) {
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
} finally {
this.closeResource(null, statement, connection);
}
return effect;
}
private void extractOrder(final Order order, ResultSet resultSet) throws SQLException {
order.setId(resultSet.getString("order_id"));
order.setAccountId(resultSet.getInt("account_id"));
order.setCreateTime(resultSet.getTimestamp("create_time").toLocalDateTime());
Timestamp finishTime = resultSet.getTimestamp("finish_time");
if (finishTime != null) {
order.setFinishTime(finishTime.toLocalDateTime());
}
order.setActualAmount(resultSet.getInt("actual_amount"));
order.setTotalMoney(resultSet.getInt("total_money"));
order.setOrderStatus(OrderStatus.valueOf(resultSet.getInt("order_status")));
order.setAccountName(resultSet.getString("account_name"));
}
private OrderItem extractOrderItem(ResultSet resultSet) throws SQLException {
OrderItem orderItem = new OrderItem();
orderItem.setId(resultSet.getInt("item_id"));
orderItem.setGoodsId(resultSet.getInt("goods_id"));
orderItem.setGoodsName(resultSet.getString("goods_name"));
orderItem.setGoodsIntroduce(resultSet.getString("goods_introduce"));
orderItem.setGoodsNum(resultSet.getInt("goods_num"));
orderItem.setGoodsUnit(resultSet.getString("goods_unit"));
orderItem.setGoodsPrice(resultSet.getInt("goods_price"));
orderItem.setGoodsDiscount(resultSet.getInt("goods_discount"));
return orderItem;
}
// 订单浏览 预留
public List<Order> queryOrderByAccount(Integer accountId) {
List<Order> orderList = new ArrayList<>();
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = this.getConnection(true);
String sql = this.getSql("@query_order_by_account");
statement = connection.prepareStatement(sql);
statement.setInt(1, accountId);
//根据accountId 进行查询数据库结果 到resultSet 集合当中
resultSet = statement.executeQuery();
Order order = null;
while (resultSet.next()) {
if (order == null) {
order = new Order();
this.extractOrder(order, resultSet);
orderList.add(order);
}
String orderId = resultSet.getString("order_id");
//不相同重新生成一个订单对象 添加到订单的List
if (!orderId.equals(order.getId())) {
order = new Order();
this.extractOrder(order, resultSet);
orderList.add(order);
}
//往当前订单内添加 具体的订单项 orderItem
OrderItem orderItem = this.extractOrderItem(resultSet);
order.getOrderItemList().add(orderItem);
}
} catch (SQLException e) {
e.printStackTrace();
if (connection != null) {
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
} finally {
this.closeResource(resultSet, statement, connection);
}
return orderList;
}
}
8.演示
到此，整个项目的代码都已经编写完毕，接下来进行一些测试：
管理员注册
用户注册
登录
关于系统
上架商品
浏览商品
支付订单
查看订单
到此为止，收银台系统项目基本完成，再有需要完善的地方我会继续更新。
293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
