文章目录
- 任务10
- 任务11
- 【任务11.1】根据项目需求，设计数据库表，用于存储匹配物流数据，要求明确表达出三个匹配物流对象的关系。即发货中、送货中、已签收的对应关系。
- 【任务11.2】实现匹配日志信息的数据库保存和查询功能，修改日志业务类LogRecService，在该类中增加两个方法，分别用于保存匹配的日志信息和查询匹配的日志信息
- 【任务11.3】实现匹配物流信息的数据库保存和查询功能，修改物流业务类TransportService，在该类中增加两个方法，分别用于保存匹配的物流信息和查询匹配的物流信息
- 【任务11.4】在com.qst.dms.dos下创建测试类DBDemo，测试匹配的日志、物流信息的数据库保存和读写功能
- 【任务11.5】将数据的数据库保存和读取功能，集成到MenuDriver，并进行测试
任务10
【任务10.1.1】安装Mysql数据库，创建项目所需的数据库dms_学号、表及用户名、密码等，并搭建数据访问的基础环境(JDBC)
根据项目需求，设计数据库表，用于存储匹配日志，要求明确表达出两个匹配日志的关系。及如果知道一个登入日志，需要能够查到对应的登出日志。
【任务10.1.2】加载JDBC包
【任务10.1.3】编写mysql.properties文件
在config子目录下创建文件mysql.properties
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai
user = root
password = 123456
【任务10.1.4】编写Config类
在com.qst.dms.util包下创建Config.java
//属性文件
private static Properties p = null;
static {
try {
p = new Properties();
//加载配置类
p.load(new FileInputStream("config/mysql.properties"));
} catch (Exception e) {
e.printStackTrace();
}
}
//获取配置类的参数
public static String getValue(String key) {
return p.get(key).toString();
}
【任务10.2】编写访问数据库的工具类DBUtil.java
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
/**
* 得到数据库连接
*/
public Connection getConnection() throws ClassNotFoundException,
SQLException, InstantiationException, IllegalAccessException {
// 通过Config获取Mysql数据库配置信息
String driver = Config.getValue("driver");
String url = Config.getValue("url");
String user = Config.getValue("user");
String pwd = Config.getValue("password");
try {
// 指定驱动程序
Class.forName(driver);
// 建立数据库连结
conn = DriverManager.getConnection(url, user, pwd);
conn.setAutoCommit(false);
return conn;
} catch (Exception e) {
// 如果连接过程出现异常，抛出异常信息
throw new SQLException("驱动错误或连接失败！");
}
}
//手动提交事务
public void commitAll() {
try {
if(conn!=null&&!conn.isClosed())
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//手动回滚事务
public void rollbackAll() {
try {
if(conn!=null&&!conn.isClosed())
conn.rollback();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 释放资源
*/
public void closeAll() {
// 如果rs不空，关闭rs
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 如果pstmt不空，关闭pstmt
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 如果conn不空，关闭conn
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 执行SQL语句，可以进行查询
*/
public ResultSet executeQuery(String preparedSql, Object[] param) {
// 处理SQL,执行SQL
try {
// 得到PreparedStatement对象
pstmt = conn.prepareStatement(preparedSql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
// 为预编译sql设置参数
pstmt.setObject(i + 1, param[i]);
}
}
// 执行SQL语句
rs = pstmt.executeQuery();
} catch (SQLException e) {
// 处理SQLException异常
e.printStackTrace();
}
return rs;
}
/**
* 执行SQL语句，可以进行增、删、改的操作，不能执行查询
*/
public int executeUpdate(String preparedSql, Object[] param) {
int num = 0;
// 处理SQL,执行SQL
try {
// 得到PreparedStatement对象
pstmt = conn.prepareStatement(preparedSql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
// 为预编译sql设置参数
pstmt.setObject(i + 1, param[i]);
}
}
// 执行SQL语句
num = pstmt.executeUpdate();
} catch (SQLException e) {
// 处理SQLException异常
e.printStackTrace();
}
return num;
}
程序设计
package com.qst.dms.util;
import java.sql.*;
public class DBUtil {
static Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
/**
* 得到数据库连接
*/
public static Connection getConnection() throws ClassNotFoundException,
SQLException, InstantiationException, IllegalAccessException {
// 通过Config获取Mysql数据库配置信息
String driver = Config.getValue("driver");
String url = Config.getValue("url");
String user = Config.getValue("user");
String pwd = Config.getValue("password");
// 指定驱动程序
Class.forName(driver);
// 建立数据库连结
conn = DriverManager.getConnection(url, user, pwd);
conn.setAutoCommit(false);//----------
return conn;
}
//手动提交事务
public void commitAll() {
try {
if(conn!=null&&!conn.isClosed())
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//手动回滚事务
public void rollbackAll() {
try {
if(conn!=null&&!conn.isClosed())
conn.rollback();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 释放资源
*/
public void closeAll() {
// 如果rs不空，关闭rs
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 如果pstmt不空，关闭pstmt
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 如果conn不空，关闭conn
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 执行SQL语句，可以进行查询
*/
public ResultSet executeQuery(String preparedSql, Object[] param) {
// 处理SQL,执行SQL
try {
// 得到PreparedStatement对象
pstmt = conn.prepareStatement(preparedSql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
// 为预编译sql设置参数
pstmt.setObject(i + 1, param[i]);
}
}
// 执行SQL语句
rs = pstmt.executeQuery();
} catch (SQLException e) {
// 处理SQLException异常
e.printStackTrace();
}
return rs;
}
/**
* 执行SQL语句，可以进行增、删、改的操作，不能执行查询
*/
public int executeUpdate(String preparedSql, Object[] param) {
int num = 0;
// 处理SQL,执行SQL
try {
// 得到PreparedStatement对象
pstmt = conn.prepareStatement(preparedSql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
// 为预编译sql设置参数
pstmt.setObject(i + 1, param[i]);
}
}
// 执行SQL语句
num = pstmt.executeUpdate();
} catch (SQLException e) {
if (e instanceof SQLIntegrityConstraintViolationException) {
System.out.println("主键冲突，跳过当前记录");
return 0;
} else {
// 处理其他SQL异常
e.printStackTrace();
}
}
return num;
}
}
任务11
【任务11.1】根据项目需求，设计数据库表，用于存储匹配物流数据，要求明确表达出三个匹配物流对象的关系。即发货中、送货中、已签收的对应关系。
【任务11.2】实现匹配日志信息的数据库保存和查询功能，修改日志业务类LogRecService，在该类中增加两个方法，分别用于保存匹配的日志信息和查询匹配的日志信息
// 匹配日志信息保存到数据库，参数是集合
public void saveMatchLogToDB(ArrayList<MatchedLogRec> matchLogs) {
DBUtil db = new DBUtil();
// 获取数据库链接
// 获取匹配的登录日志
// 获取匹配的登出日志
// 保存匹配记录中的登录日志
// 保存匹配记录中的登出日志
// 保存匹配日志的ID
// 关闭数据库连接，释放资源
}
// 从数据库读匹配日志信息，返回匹配日志信息集合
public List<MatchedLogRec> readMatchedLogFromDB() {
DBUtil db = new DBUtil();
// 获取数据库链接
// 查询匹配的日志
// 获取登录记录
// 获取登出记录
// 添加匹配登录信息到匹配集合
// 关闭数据库连接，释放资源
}
程序设计
package com.qst.dms.service;
import com.qst.dms.entity.LogRec;
import com.qst.dms.entity.MatchedLogRec;
import com.qst.dms.exception.DataAnalyseException;
import com.qst.dms.gather.LogRecAnalyse;
import com.qst.dms.util.AppendObjectOutputStream;
import com.qst.dms.util.DBUtil;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Scanner;
public class LogRecService {
private static final String saveFile = "MatchedLogRec.dat";
private Scanner scanner;
public LogRecService() {
scanner = new Scanner(System.in);
}
public LogRec inputLog() {
int id, type, logType;
Date nowDate;
String address;
String user;
String ip;
while (true) {
try {
System.out.println("请输入ID标识：");
id = scanner.nextInt();
nowDate = new Date();
System.out.println("请输入地址：");
address = scanner.next();
type = LogRec.GATHER;
System.out.println("请输入登录用户名：");
user = scanner.next();
System.out.println("请输入主机IP：");
ip = scanner.next();
System.out.println("请输入登录状态（1表示登录，0表示登出）：");
logType = scanner.nextInt();
if (logType == 0 || logType == 1) {
break;
} else {
throw new IllegalArgumentException("非法的登录状态");
}
} catch (Exception e) {
System.out.println("输入错误，请重新输入");
scanner.nextLine();
}
}
return new LogRec(id, nowDate, address, type, user, ip, logType);
}
public void showLog(List<LogRec> logRecs) {
System.out.println("日志信息：");
for (LogRec logRec : logRecs) {
System.out.println(logRec);
}
}
// 匹配日志信息输出，参数是集合
public void showMatchLog(List<MatchedLogRec> matchLogs) {
System.out.println("匹配日志信息：");
for (MatchedLogRec matchLog : matchLogs) {
System.out.println(matchLog);
}
}
// 保存
public static void saveLogRec(List<LogRec> matchLogs) {
try {
AppendObjectOutputStream.setFile(new File(saveFile));
File file = AppendObjectOutputStream.getFile();
FileOutputStream fileOut = new FileOutputStream(file, true);
AppendObjectOutputStream objOut = new AppendObjectOutputStream(file);
for (LogRec matchLog : matchLogs) {
objOut.writeObject(matchLog);
}
objOut.close();
fileOut.close();
System.out.println("匹配日志信息保存成功\n");
} catch (IOException e) {
System.out.println("保存匹配日志信息发生异常：" + e.getMessage()+"\n");
}
}
//匹配
public static ArrayList<MatchedLogRec> readMatchLogRec() {
ArrayList<MatchedLogRec> matchedLogs = new ArrayList<>();
List<LogRec> logs = readLogRec();
try {
AppendObjectOutputStream.setFile(new File(saveFile));
File file = AppendObjectOutputStream.getFile();
if (!file.exists()) {
file.createNewFile();
}
FileInputStream fileIn = new FileInputStream(file);
// 创建一个ObjectInputStream对象输入流，并连接文件输入流
ObjectInputStream objIn = new ObjectInputStream(fileIn);
// 创建日志数据分析对象
LogRecAnalyse logAnalyse = new LogRecAnalyse(logs);
// 日志数据过滤
logAnalyse.doFilter();
// 日志数据匹配分析
try {
List<MatchedLogRec> objs = logAnalyse.matchData(); // 进行数据匹配
// 处理匹配的日志数据
// 判断objs集合是否是配置日志集合
if (objs instanceof List<?>) {
// 将集合强制类型转换成配置日志集合
matchedLogs = (ArrayList<MatchedLogRec>) objs;
}
} catch (DataAnalyseException e) {
System.out.println(e.getMessage());
}
objIn.close();
fileIn.close();
System.out.println("匹配日志信息读取完成\n");
} catch (IOException e) {
System.out.println("读取匹配日志信息发生异常：" + e.getMessage()+"\n");
}
return matchedLogs;
}
//显示
public static List<LogRec> readLogRec() {
List<LogRec> logs = new ArrayList<>();
try {
AppendObjectOutputStream.setFile(new File(saveFile));
File file = AppendObjectOutputStream.getFile();
if (!file.exists()) {
file.createNewFile();
}
FileInputStream fileIn = new FileInputStream(file);
// 创建一个ObjectInputStream对象输入流，并连接文件输入流
ObjectInputStream objIn = new ObjectInputStream(fileIn);
// 使用异常处理和EOFException异常处理读取结束
try {
while (true) {
LogRec log = (LogRec) objIn.readObject();
logs.add(log);
}
} catch (EOFException e) {
// 读取结束，不做任何操作
}
objIn.close();
fileIn.close();
System.out.println("日志信息读取完成\n");
} catch (IOException | ClassNotFoundException e) {
System.out.println("读取日志信息发生异常：" + e.getMessage() +"\n");
}
return logs;
}
// 匹配日志信息保存到数据库，参数是集合
public static void saveMatchLogToDB() {
ArrayList<MatchedLogRec> matchLogs =readMatchLogRec();
List<LogRec> login = new ArrayList<>();
List<LogRec> logout = new ArrayList<>();
List<LogRec> logs = readLogRec();
Connection conn = null;
try {
DBUtil db = new DBUtil();
conn = db.getConnection();
for (MatchedLogRec matchlog : matchLogs) {
int loginId = matchlog.getLogin().getId();
int logoutId = matchlog.getLogout().getId();
for (LogRec log : logs) {
if (log.getId() == loginId) {
login.add(log);
} else if (log.getId() == logoutId) {
logout.add(log);
}
}
}
// 保存匹配记录中的登录日志
String querySqllogin = "INSERT INTO log_in VALUES (?, ?, ?, ?, ?, ?, ?)";
for (LogRec log : login) {
Object[] queryParams = {log.getId(), log.getTime(), log.getAddress(), log.getType(), log.getUser(), log.getIp(), log.getLogType()};
db.executeUpdate(querySqllogin, queryParams);
}
System.out.println("保存匹配记录中的登录日志成功");
// 保存匹配记录中的登出日志
String querySqllogout = "INSERT INTO log_out VALUES (?, ?, ?, ?, ?, ?, ?)";
for (LogRec log : logout) {
Object[] queryParams = {log.getId(), log.getTime(), log.getAddress(), log.getType(), log.getUser(), log.getIp(), log.getLogType()};
db.executeUpdate(querySqllogout, queryParams);
}
System.out.println("保存匹配记录中的登出日志成功");
// 保存匹配日志的ID
String querySqlmatch = "INSERT INTO log_match VALUES (?, ?)";
for (MatchedLogRec matchlog : matchLogs) {
Object[] queryParams = {matchlog.getLogin().getId(), matchlog.getLogout().getId()};
db.executeUpdate(querySqlmatch, queryParams);
}
System.out.println("保存匹配日志的ID成功");
db.commitAll();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭数据库连接，释放资源
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static ArrayList<MatchedLogRec> readMatchedLogFromDB() {
DBUtil db = new DBUtil();
Connection conn = null;
ArrayList<MatchedLogRec> matchedLogs = new ArrayList<>();
try {
conn = db.getConnection();
// 查询匹配的日志
String querySqlMatchedLogs = "SELECT * FROM log_match";
ResultSet matchedLogsResult = db.executeQuery(querySqlMatchedLogs,null);
while (matchedLogsResult.next()) {
int loginId = matchedLogsResult.getInt("login_id");
int logoutId = matchedLogsResult.getInt("logout_id");
// 获取登录记录
LogRec login = new LogRec();
String querySqlLogin = "SELECT * FROM log_in WHERE id = ?";
Object[] loginParams = { loginId };
ResultSet loginResult = db.executeQuery(querySqlLogin, loginParams);
if (loginResult.next()) {
// 设置登录记录的属性值
login.setId(loginResult.getInt("id"));
login.setTime(loginResult.getDate("time"));
login.setAddress(loginResult.getString("address"));
login.setType(loginResult.getInt("type"));
login.setUser(loginResult.getString("user"));
login.setIp(loginResult.getString("ip"));
login.setLogType(loginResult.getInt("logtype"));
// 获取登出记录
LogRec logout = new LogRec();
String querySqlLogout = "SELECT * FROM log_out WHERE id = ?";
Object[] logoutParams = { logoutId };
ResultSet logoutResult = db.executeQuery(querySqlLogout, logoutParams);
if (logoutResult.next()) {
// 设置登出记录的属性值
logout.setId(logoutResult.getInt("id"));
logout.setTime(logoutResult.getDate("time"));
logout.setAddress(logoutResult.getString("address"));
logout.setType(logoutResult.getInt("type"));
logout.setUser(logoutResult.getString("user"));
logout.setIp(logoutResult.getString("ip"));
logout.setLogType(logoutResult.getInt("logtype"));
// 添加匹配登录信息到匹配集合
MatchedLogRec matchedLog = new MatchedLogRec( login, logout);
matchedLogs.add(matchedLog);
}
logoutResult.close();
}
loginResult.close();
}
matchedLogsResult.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭数据库连接，释放资源
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return matchedLogs;
}
}
【任务11.3】实现匹配物流信息的数据库保存和查询功能，修改物流业务类TransportService，在该类中增加两个方法，分别用于保存匹配的物流信息和查询匹配的物流信息
// 匹配物流信息保存到数据库，参数是集合
public void saveMatchTransportToDB(List<MatchedTransport> matchTrans) {}
// 从数据库中读匹配物流信息，返回匹配物流信息集合
public List<MatchedTransport> readMatchedTransportFromDB() {}
程序设计
package com.qst.dms.service;
import com.qst.dms.entity.MatchedTransport;
import com.qst.dms.entity.Transport;
import com.qst.dms.exception.DataAnalyseException;
import com.qst.dms.gather.TransportAnalyse;
import com.qst.dms.util.AppendObjectOutputStream;
import com.qst.dms.util.DBUtil;
import java.io.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Scanner;
public class TransportService {
private static final String saveFile = "MatchedTransport.dat";
private Scanner scanner;
public TransportService() {
scanner = new Scanner(System.in);
}
public Transport inputTransport() {
int transportType;
int id, type;
Date nowDate;
String address, handler, receiver;
while (true) {
try {
System.out.println("请输入ID标识：");
id = scanner.nextInt();
nowDate = new Date();
System.out.println("请输入地址：");
address = scanner.next();
type = Transport.GATHER;
System.out.println("请输入货物经手人：");
handler = scanner.next();
System.out.println("请输入收货人：");
receiver = scanner.next();
System.out.println("请输入物流状态（1表示发货中，2表示送货中，3表示已签收）：");
transportType = scanner.nextInt();
if (transportType == 1 || transportType == 2 || transportType == 3) {
break;
} else {
throw new IllegalArgumentException("非法的物流状态");
}
} catch (Exception e) {
System.out.println("输入错误，请重新输入");
scanner.nextLine();
}
}
return new Transport(id, nowDate, address, type, handler, receiver, transportType);
}
public void showTransport(List<Transport> transports) {
System.out.println("物流信息：");
for (Transport transport : transports) {
System.out.println(transport);
}
}
// 匹配物流信息输出，参数是集合
public void showMatchTransport(List<MatchedTransport> matchTrans) {
System.out.println("匹配物流信息：");
for (MatchedTransport matchTran : matchTrans) {
System.out.println(matchTran);
}
}
// 保存
public static void saveTransport(List<Transport> transports) {
try {
AppendObjectOutputStream.setFile(new File(saveFile));
File file = AppendObjectOutputStream.getFile();
FileOutputStream fileOut = new FileOutputStream(file, true);
AppendObjectOutputStream objOut = new AppendObjectOutputStream(file);
for (Transport transport : transports) {
objOut.writeObject(transport);
}
objOut.close();
fileOut.close();
System.out.println("匹配物流信息保存成功\n");
} catch (IOException e) {
System.out.println("保存匹配物流信息发生异常：" + e.getMessage() + "\n");
}
}
//匹配
public static ArrayList<MatchedTransport> readMatchTransport() {
ArrayList<MatchedTransport> matchedTransports = new ArrayList<>();
List<Transport> transports = readTransport();
try {
AppendObjectOutputStream.setFile(new File(saveFile));
File file = AppendObjectOutputStream.getFile();
if (!file.exists()) {
file.createNewFile();
}
FileInputStream fileIn = new FileInputStream(file);
// 创建一个ObjectInputStream对象输入流，并连接文件输入流
ObjectInputStream objIn = new ObjectInputStream(fileIn);
// 创建物流数据分析对象
TransportAnalyse ta = new TransportAnalyse(transports);
// 物流数据过滤
ta.doFilter();
try {
// 物流数据分析
List<MatchedTransport> objs = ta.matchData();
// 判断objs集合是否是匹配物流集合
if (objs instanceof List<?>) {
// 将集合强制类型转换成匹配物流集合
matchedTransports = (ArrayList<MatchedTransport>) objs;
}
} catch (DataAnalyseException e) {
System.out.println(e.getMessage());
}
objIn.close();
fileIn.close();
System.out.println("匹配物流信息读取完成\n");
} catch (IOException e) {
System.out.println("读取匹配物流信息发生异常：" + e.getMessage() + "\n");
}
return matchedTransports;
}
//显示
public static List<Transport> readTransport() {
List<Transport> transports = new ArrayList<>();
try {
AppendObjectOutputStream.setFile(new File(saveFile));
File file = AppendObjectOutputStream.getFile();
if (!file.exists()) {
file.createNewFile();
}
FileInputStream fileIn = new FileInputStream(file);
// 创建一个ObjectInputStream对象输入流，并连接文件输入流
ObjectInputStream objIn = new ObjectInputStream(fileIn);
// 使用异常处理和EOFException异常处理读取结束
try {
while (true) {
Transport transport = (Transport) objIn.readObject();
transports.add(transport);
}
} catch (EOFException e) {
// 读取结束，不做任何操作
}
objIn.close();
fileIn.close();
System.out.println("物流信息读取完成\n");
} catch (IOException | ClassNotFoundException e) {
System.out.println("读取物流信息发生异常：" + e.getMessage() + "\n");
}
return transports;
}
// 匹配日志信息保存到数据库，参数是集合
public static void saveMatchTransportToDB() {
ArrayList<MatchedTransport> matchTrans = readMatchTransport();
List<Transport> sendList = new ArrayList<>();
List<Transport> tranList = new ArrayList<>();
List<Transport> recList = new ArrayList<>();
List<Transport> transports = readTransport();
Connection conn = null;
try {
DBUtil db = new DBUtil();
conn = db.getConnection();
for (MatchedTransport matchTran : matchTrans){
int send = matchTran.getSend().getId();
int tran = matchTran.getTrans().getId();
int rec = matchTran.getReceive().getId();
for (Transport transport : transports){
if (transport.getId() == send){
sendList.add(transport);
}else if(transport.getId() == tran){
tranList.add(transport);
}else if(transport.getId() == rec){
recList.add(transport);
}
}
}
String querySqllogsend = "INSERT INTO send VALUES (?, ?, ?, ?, ?, ?, ?)";
for (Transport transport: sendList) {
Object[] queryParams = {transport.getId(), transport.getTime(), transport.getAddress(), transport.getType(), transport.getHandler(), transport.getReciver(), transport.getTransportType()};
db.executeUpdate(querySqllogsend, queryParams);
}
System.out.println("保存匹配物流中的发送日志成功");
String querySqllogtran = "INSERT INTO tran VALUES (?, ?, ?, ?, ?, ?, ?)";
for (Transport transport: tranList) {
Object[] queryParams = {transport.getId(), transport.getTime(), transport.getAddress(), transport.getType(), transport.getHandler(), transport.getReciver(), transport.getTransportType()};
db.executeUpdate(querySqllogtran, queryParams);
}
System.out.println("保存匹配物流中的运输日志成功");
String querySqllogrec = "INSERT INTO receive VALUES (?, ?, ?, ?, ?, ?, ?)";
for (Transport transport: recList) {
Object[] queryParams = {transport.getId(), transport.getTime(), transport.getAddress(), transport.getType(), transport.getHandler(), transport.getReciver(), transport.getTransportType()};
db.executeUpdate(querySqllogrec, queryParams);
}
System.out.println("保存匹配物流中的接收日志成功");
// 保存匹配日志的ID
String querySqlmatch = "INSERT INTO matchtrans VALUES (?, ?, ?)";
for (MatchedTransport matchtran : matchTrans) {
Object[] queryParams = {matchtran.getSend().getId(), matchtran.getTrans().getId(), matchtran.getReceive().getId()};
db.executeUpdate(querySqlmatch, queryParams);
}
System.out.println("保存匹配物流的ID成功");
db.commitAll();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭数据库连接，释放资源
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static ArrayList<MatchedTransport> readMatchedTransportFromDB() {
DBUtil db = new DBUtil();
Connection conn = null;
ArrayList<MatchedTransport> matchedtrans = new ArrayList<>();
try {
conn = db.getConnection();
// 查询匹配的日志
String querySqlmatchedtrans = "SELECT * FROM matchtrans";
ResultSet matchedtransResult = db.executeQuery(querySqlmatchedtrans,null);
while (matchedtransResult.next()) {
int sendId = matchedtransResult.getInt("sendid");
int tranId = matchedtransResult.getInt("tranid");
int recId = matchedtransResult.getInt("recid");
Transport sendList = new Transport();
String querySqlsendList = "SELECT * FROM send WHERE id = ?";
Object[] sendListParams = { sendId };
ResultSet sendResult = db.executeQuery(querySqlsendList, sendListParams);
if (sendResult.next()) {
sendList.setId(sendResult.getInt("id"));
sendList.setTime(sendResult.getDate("time"));
sendList.setAddress(sendResult.getString("address"));
sendList.setType(sendResult.getInt("type"));
sendList.setHandler(sendResult.getString("handler"));
sendList.setReciver(sendResult.getString("receiver"));
sendList.setTransportType(sendResult.getInt("trantype"));
Transport tranList = new Transport();
String querySqltranList = "SELECT * FROM tran WHERE id = ?";
Object[] tranListParams = { tranId };
ResultSet tranListResult = db.executeQuery(querySqltranList, tranListParams);
if (tranListResult.next()) {
// 设置登出记录的属性值
tranList.setId(tranListResult.getInt("id"));
tranList.setTime(tranListResult.getDate("time"));
tranList.setAddress(tranListResult.getString("address"));
tranList.setType(tranListResult.getInt("type"));
tranList.setHandler(tranListResult.getString("handler"));
tranList.setReciver(tranListResult.getString("receiver"));
tranList.setTransportType(tranListResult.getInt("trantype"));
Transport recList = new Transport();
String querySqlrecList = "SELECT * FROM receive WHERE id = ?";
Object[] recListParams = { recId };
ResultSet recListResult = db.executeQuery(querySqlrecList, recListParams);
if (recListResult.next()) {
// 设置登出记录的属性值
recList.setId(recListResult.getInt("id"));
recList.setTime(recListResult.getDate("time"));
recList.setAddress(recListResult.getString("address"));
recList.setType(recListResult.getInt("type"));
recList.setHandler(recListResult.getString("handler"));
recList.setReciver(recListResult.getString("receiver"));
recList.setTransportType(recListResult.getInt("trantype"));
// 添加匹配登录信息到匹配集合
MatchedTransport matchedLog = new MatchedTransport( sendList, tranList, recList);
matchedtrans.add(matchedLog);
}
recListResult.close();
}
tranListResult.close();
}
sendResult.close();
}
matchedtransResult.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭数据库连接，释放资源
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return matchedtrans;
}
}
【任务11.4】在com.qst.dms.dos下创建测试类DBDemo，测试匹配的日志、物流信息的数据库保存和读写功能
测试数据如下：
new LogRec(1001, new Date(), "青島",DataBase.GATHER, "zhangsan", "192.168.1.1", 1),
new LogRec(1002, new Date(), "青島", DataBase.GATHER, "zhangsan", "192.168.1.1", 0)));
new LogRec(1003, new Date(), "北京",DataBase.GATHER, "lisi", "192.168.1.6", 1),
new LogRec(1004, new Date(), "北京", DataBase.GATHER, "lisi", "192.168.1.6", 0)));
new LogRec(1005, new Date(), "济南",DataBase.GATHER, "wangwu", "192.168.1.89", 1),
new LogRec(1006, new Date(), "济南", DataBase.GATHER, "wangwu", "192.168.1.89", 0)));
new Transport(2001, new Date(), "青島",DataBase.GATHER,"zhangsan","zhaokel",1),
new Transport(2002, new Date(), "北京",DataBase.GATHER,"lisi","zhaokel",2),
new Transport(2003, new Date(), "北京",DataBase.GATHER,"wangwu","zhaokel",3)));
new Transport(2004, new Date(), "青島",DataBase.GATHER,"maliu","zhaokel",1),
new Transport(2005, new Date(), "北京",DataBase.GATHER,"sunqi","zhaokel",2),
new Transport(2006, new Date(), "北京",DataBase.GATHER,"fengba","zhaokel",3)))
程序设计
package com.qst.dms.dos;
import com.qst.dms.entity.*;
import com.qst.dms.service.LogRecService;
import com.qst.dms.service.TransportService;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class DBDemo {
public static void main(String[] args) {
// 测试数据
List<LogRec> logs = new ArrayList<>();
logs.add(new LogRec(1001, new Date(), "青島", DataBase.GATHER, "zhangsan", "192.168.1.1", 1));
logs.add(new LogRec(1002, new Date(), "青島", DataBase.GATHER, "zhangsan", "192.168.1.1", 0));
logs.add(new LogRec(1003, new Date(), "北京", DataBase.GATHER, "lisi", "192.168.1.6", 1));
logs.add(new LogRec(1004, new Date(), "北京", DataBase.GATHER, "lisi", "192.168.1.6", 0));
logs.add(new LogRec(1005, new Date(), "济南", DataBase.GATHER, "wangwu", "192.168.1.89", 1));
logs.add(new LogRec(1006, new Date(), "济南", DataBase.GATHER, "wangwu", "192.168.1.89", 0));
LogRecService.saveLogRec(logs);
LogRecService.saveMatchLogToDB();
// 从数据库读匹配日志信息
ArrayList<MatchedLogRec> matchlogs = LogRecService.readMatchedLogFromDB();
System.out.println("匹配日志信息如下：");
for(MatchedLogRec matchlog :matchlogs){
System.out.println(matchlog);
}
List<Transport> transports = new ArrayList<>();
transports.add(new Transport(2001, new Date(), "青島", DataBase.GATHER, "zhangsan", "zhaokel", 1));
transports.add(new Transport(2002, new Date(), "北京", DataBase.GATHER, "lisi", "zhaokel", 2));
transports.add(new Transport(2003, new Date(), "北京", DataBase.GATHER, "wangwu", "zhaokel", 3));
transports.add(new Transport(2004, new Date(), "青島", DataBase.GATHER, "maliu", "zhaokel", 1));
transports.add(new Transport(2005, new Date(), "北京", DataBase.GATHER, "sunqi", "zhaokel", 2));
transports.add(new Transport(2006, new Date(), "北京", DataBase.GATHER, "fengba", "zhaokel", 3));
TransportService.saveTransport(transports);
TransportService.saveMatchTransportToDB();
ArrayList<MatchedTransport> matchtrans = TransportService.readMatchedTransportFromDB();
System.out.println("匹配物流信息如下：");
for (MatchedTransport matchtran : matchtrans){
System.out.println(matchtran);
}
}
}
【任务11.5】将数据的数据库保存和读取功能，集成到MenuDriver，并进行测试
程序设计
package com.qst.dms.dos;
import com.qst.dms.entity.LogRec;
import com.qst.dms.entity.MatchedLogRec;
import com.qst.dms.entity.MatchedTransport;
import com.qst.dms.entity.Transport;
import com.qst.dms.service.LogRecService;
import com.qst.dms.service.TransportService;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class MenuDriver {
public static void main(String[] args) {
// 建立一个从键盘接收数据的扫描器
Scanner scanner = new Scanner(System.in);
// 创建一个泛型ArrayList集合存储日志数据
List<LogRec> logRecList = new ArrayList<>();
// 创建一个泛型ArrrayList集合存储物流数据
List<Transport> transportList = new ArrayList<>();
// 创建一个日志业务类
LogRecService logService = new LogRecService();
// 创建一个物流业务类
TransportService tranService = new TransportService();
// 日志数据匹配集合
List<MatchedLogRec> matchedLogs = null;
// 物流数据匹配集合
List<MatchedTransport> matchedTrans = null;
// 运行菜单选择
while (true) {
System.out.println("请选择操作：");
System.out.println("1. 采集数据");
System.out.println("2. 数据匹配");
System.out.println("3. 显示数据");
System.out.println("4. 退出");
// 接收键盘输入的选项
try {
int choice = scanner.nextInt();
int type;
switch (choice) {
case 1:
System.out.println("请输入采集数据类型：1.日志 2.物流");
// 接收键盘输入的选项
type = scanner.nextInt();
if (type == 1) {
System.out.println("正在采集日志数据，请输入正确信息，确保数据的正常采集！");
// 采集日志数据
LogRec log = logService.inputLog();
// 将采集的日志数据添加到logRecList集合中
logRecList.add(log);
LogRecService.saveLogRec(logRecList);
LogRecService.saveMatchLogToDB();
} else if (type == 2) {
System.out.println("正在采集物流数据，请输入正确信息，确保数据的正常采集！");
// 采集物流数据
Transport tran = tranService.inputTransport();
// 将采集的物流数据添加到transportList集合中
transportList.add(tran);
TransportService.saveTransport(transportList);
TransportService.saveMatchTransportToDB();
}
break;
case 2:
System.out.println("请输入匹配数据类型：1.日志 2.物流");
// 接收键盘输入的选项
type = scanner.nextInt();
if (type == 1) {
System.out.println("匹配日志数据如下：");
// 数据匹配
LogRecService.readMatchedLogFromDB();
} else if (type == 2) {
System.out.println("匹配物流数据如下：");
// 数据匹配
TransportService.readMatchedTransportFromDB();
}
break;
case 3:
System.out.println("请输入显示原始数据类型：1.日志 2.物流");
// 接收键盘输入的选项
type = scanner.nextInt();
if (type == 1) {
// 显示日志数据
LogRecService.readLogRec();
} else if (type == 2) {
// 显示物流数据
TransportService.readTransport();
}
break;
case 4:
System.out.println("谢谢使用！");
return;
default:
System.out.println("无效选项，请重新选择！");
}
} catch (Exception e) {
System.out.println("非法输入，请重新输入！");
scanner.nextLine();
}
}
}
}
测试