// 這裡統一使用大寫 如果不是大寫的話後面會自動轉換為大寫
// 用于查詢資料庫中的全部快遞（總數+新增），待取件快遞（總數+新增）
public static final String SQL_CONSOLE = "SELECT COUNT(ID) data1_size, COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) data1_day, COUNT(STATUS=0 OR NULL) data2_size, COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) data2_day FROM EXPRESS";
// 用于查詢資料庫中的所有快遞資訊
public static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
// 用于分頁查詢資料庫中的快遞資訊
public static final String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS LIMIT ?,?";
// 通過取件碼查詢快遞資訊
public static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE=?";
// 通過快遞單号查詢快遞資訊
public static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER=?";
// 通過錄入人手機号查詢快遞資訊
public static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE=?";
// 通過使用者手機号查詢使用者所有快遞
public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM EXPRESS WHERE USERPHONE=?";
// 錄入快遞
public static final String SQL_INSERT = "INSERT INTO EXPRESS (NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,NOW(),0,?)";
// 快遞修改
public static final String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?";
// 快遞的狀态碼改變（取件）
public static final String SQL_UPDATE_STATUS = "UPDATE EXPRESS SET STATUS=1,OUTTIME=NOW(),CODE=NULL WHERE CODE=?";
// 快遞的删除
public static final String SQL_DELETE = "DELETE FROM EXPRESS WHERE ID=?";
5，Dao編寫+Dao測試
5.1 ExpressDao實作類編寫
package com.xxy.dao.impl;
import com.xxy.bean.Express;
import com.xxy.dao.BaseExpressDao;
import com.xxy.util.DruidUtil;
import com.xxy.exception.DuplicateCodeException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExpressDaoMysql implements BaseExpressDao {
// 這裡統一使用大寫 如果不是大寫的話後面會自動轉換為大寫
// 用于查詢資料庫中的全部快遞（總數+新增），待取件快遞（總數+新增）
public static final String SQL_CONSOLE = "SELECT COUNT(ID) data1_size, COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) data1_day, COUNT(STATUS=0 OR NULL) data2_size, COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) data2_day FROM EXPRESS";
// 用于查詢資料庫中的所有快遞資訊
public static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
// 用于分頁查詢資料庫中的快遞資訊
public static final String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS LIMIT ?,?";
// 通過取件碼查詢快遞資訊
public static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE=?";
// 通過快遞單号查詢快遞資訊
public static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER=?";
// 通過錄入人手機号查詢快遞資訊
public static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE=?";
// 通過使用者手機号查詢使用者所有快遞
public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM EXPRESS WHERE USERPHONE=?";
// 錄入快遞
public static final String SQL_INSERT = "INSERT INTO EXPRESS (NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,NOW(),0,?)";
// 快遞修改
public static final String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?";
// 快遞的狀态碼改變（取件）
public static final String SQL_UPDATE_STATUS = "UPDATE EXPRESS SET STATUS=1,OUTTIME=NOW(),CODE=NULL WHERE CODE=?";
// 快遞的删除
public static final String SQL_DELETE = "DELETE FROM EXPRESS WHERE ID=?";
/**
* 用于查詢資料庫中的全部快遞（總數+新增），待取件快遞（總數+新增）
*
* @return [{size:總數,day:新增},{size:總數,day:新增}]
*/
@Override
public List<Map<String, Integer>> console() {
List<Map<String, Integer>> data = new ArrayList<>();
// 1 擷取資料庫連接配接
Connection connection = DruidUtil.getConnection();
PreparedStatement statement = null;// 變量向上抽取，便于釋放資源
ResultSet result = null;
try {
// 2 預編譯SQL語句
statement = connection.prepareStatement(SQL_CONSOLE);
// 3 填充參數(可選)
// 4 執行SQL語句
result = statement.executeQuery();
// 5 獲得執行結果
if (result.next()) {
int data1_size = result.getInt("data1_size");
int data1_day = result.getInt("data1_day");
int data2_size = result.getInt("data2_size");
int data2_day = result.getInt("data2_day");
Map data1 = new HashMap();
data1.put("data1_size", data1_size);
data1.put("data1_day", data1_day);
Map data2 = new HashMap();
data2.put("data2_size", data2_size);
data2.put("data2_day", data2_day);
data.add(data1);
data.add(data2);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6 釋放資源
DruidUtil.close(connection, statement, result);
}
return data;
}
/**
* 用于查詢所有快遞
*
* @param limit 是否分頁的标記，true表示分頁。false表示查詢所有快遞
* @param offset SQL語句的起始索引
* @param pageNumber 頁查詢的數量
* @return 快遞的集合
*/
@Override
public List<Express> findAll(boolean limit, int offset, int pageNumber) {
ArrayList<Express> data = new ArrayList<>();
//1. 擷取資料庫的連接配接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet result = null;
//2. 預編譯SQL語句
try {
if(limit) {
state = conn.prepareStatement(SQL_FIND_LIMIT);
//3. 填充參數(可選)
state.setInt(1,offset);
state.setInt(2,pageNumber);
} else {
state = conn.prepareStatement(SQL_FIND_ALL);
}
//4. 執行SQL語句
result = state.executeQuery();
//5. 擷取執行的結果
while(result.next()) {
int id = result.getInt("id");
String number = result.getString("number");
String username = result.getString("username");
String userPhone = result.getString("userPhone");
String company = result.getString("company");
String code = result.getString("code");
Timestamp inTime = result.getTimestamp("inTime");
Timestamp outTime = result.getTimestamp("outTime");
int status = result.getInt("status");
String sysPhone = result.getString("sysPhone");
Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
data.add(e);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
//6. 資源的釋放
DruidUtil.close(conn,state,result);
}
return data;
}
/**
* 根據快遞單号，查詢快遞資訊
*
* @param number 單号
* @return 查詢的快遞資訊，單号不存在時傳回null
*/
@Override
public Express findByNumber(String number) {
// 1 擷取資料庫連接配接
Connection connection = DruidUtil.getConnection();
PreparedStatement statement = null;
ResultSet result = null;
try {
// 2 預編譯SQL語句
statement = connection.prepareStatement(SQL_FIND_BY_NUMBER);
// 3 填充參數(可選)
statement.setString(1, number);
// 4 執行SQL語句
result = statement.executeQuery();
// 5 獲得執行結果
if (result.next()) {
int id = result.getInt("id");
String username = result.getString("username");
String userPhone = result.getString("userPhone");
String company = result.getString("company");
String code = result.getString("code");
Timestamp inTime = result.getTimestamp("inTime");
Timestamp outTime = result.getTimestamp("outTime");
int status = result.getInt("status");
String sysPhone = result.getString("sysPhone");
Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
return e;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6 釋放資源
DruidUtil.close(connection, statement, result);
}
return null;
}
/**
* 根據快遞取件碼，查詢快遞資訊
*
* @param code 取件碼
* @return 查詢的快遞資訊，取件碼不存在時傳回null
*/
@Override
public Express findByCode(String code) {
// 1 擷取資料庫連接配接
Connection connection = DruidUtil.getConnection();
PreparedStatement statement = null;
ResultSet result = null;
try {
// 2 預編譯SQL語句
statement = connection.prepareStatement(SQL_FIND_BY_CODE);
// 3 填充參數(可選)
statement.setString(1, code);
// 4 執行SQL語句
result = statement.executeQuery();
// 5 獲得執行結果
if (result.next()) {
int id = result.getInt("id");
String number = result.getString("number");
String username = result.getString("username");
String userPhone = result.getString("userPhone");
String company = result.getString("company");
Timestamp inTime = result.getTimestamp("inTime");
Timestamp outTime = result.getTimestamp("outTime");
int status = result.getInt("status");
String sysPhone = result.getString("sysPhone");
Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
return e;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6 釋放資源
DruidUtil.close(connection, statement, result);
}
return null;
}
/**
* 根據使用者手機号碼，查詢他所有的快遞資訊
*
* @param userPhone 手機号碼
* @return 查詢的快遞資訊清單
*/
@Override
public List<Express> findByUserPhone(String userPhone) {
List<Express> data = new ArrayList<>();
// 1 擷取資料庫連接配接
Connection connection = DruidUtil.getConnection();
PreparedStatement statement = null;
ResultSet result = null;
try {
// 2 預編譯SQL語句
statement = connection.prepareStatement(SQL_FIND_BY_USERPHONE);
// 3 填充參數(可選)
statement.setString(1, userPhone);
// 4 執行SQL語句
result = statement.executeQuery();
// 5 獲得執行結果
while (result.next()) {
int id = result.getInt("id");
String number = result.getString("number");
String username = result.getString("username");
String company = result.getString("company");
String code = result.getString("code");
Timestamp inTime = result.getTimestamp("inTime");
Timestamp outTime = result.getTimestamp("outTime");
int status = result.getInt("status");
String sysPhone = result.getString("sysPhone");
Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
data.add(e);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6 釋放資源
DruidUtil.close(connection, statement, result);
}
return data;
}
/**
* 根據錄入人手機号碼，查詢錄入的所有記錄
*
* @param sysPhone 手機号碼
* @return 查詢的快遞資訊清單
*/
@Override
public List<Express> findBySysPhone(String sysPhone) {
List<Express> data = new ArrayList<>();
// 1 擷取資料庫連接配接
Connection connection = DruidUtil.getConnection();
PreparedStatement statement = null;
ResultSet result = null;
try {
// 2 預編譯SQL語句
statement = connection.prepareStatement(SQL_FIND_BY_SYSPHONE);
// 3 填充參數(可選)
statement.setString(1, sysPhone);
// 4 執行SQL語句
result = statement.executeQuery();
// 5 獲得執行結果
while (result.next()) {
int id = result.getInt("id");
String number = result.getString("number");
String username = result.getString("username");
String userPhone = result.getString("userPhone");
String company = result.getString("company");
String code = result.getString("code");
Timestamp inTime = result.getTimestamp("inTime");
Timestamp outTime = result.getTimestamp("outTime");
int status = result.getInt("status");
Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
data.add(e);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6 釋放資源
DruidUtil.close(connection, statement, result);
}
return data;
}
/**
* 快遞的錄入
*
* @param e 要錄入的快遞對象
* @return 錄入的結果，true表示成功，false表示失敗
*/
@Override
public boolean insert(Express e) throws DuplicateCodeException {
//1. 連接配接的擷取
Connection conn = DruidUtil.getConnection();
//2. 預編譯SQL語句
PreparedStatement state = null;
try {
state = conn.prepareStatement(SQL_INSERT);
//3. 填充參數
state.setString(1,e.getNumber());
state.setString(2,e.getUsername());
state.setString(3,e.getUserphone());
state.setString(4,e.getCompany());
state.setString(5,e.getCode());
state.setString(6,e.getSysPhone());
//4. 執行SQL語句,并擷取執行結果
return state.executeUpdate()>0?true:false;
} catch (SQLException e1) {
/*throwables.printStackTrace();*/
if(e1.getMessage().endsWith("for key 'code'")){
//是因為取件碼重複,而出現了異常
DuplicateCodeException e2 = new DuplicateCodeException(e1.getMessage());
throw e2;
}else{
e1.printStackTrace();
}
}finally {
//5. 釋放資源
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 快遞的修改
*
* @param id 要修改的快遞id
* @param newExpress 新的快遞對象（number，company,username,userPhone）
* @return 修改的結果，true表示成功，false表示失敗
*/
@Override
public boolean update(int id, Express newExpress) {
//1. 連接配接的擷取
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
//2. 預編譯SQL語句
try {
state = conn.prepareStatement(SQL_UPDATE);
state.setString(1,newExpress.getNumber());
state.setString(2,newExpress.getUsername());
state.setString(3,newExpress.getCompany());
state.setInt(4,newExpress.getStatus());
state.setInt(5,id);
return state.executeUpdate()>0?true:false;
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 更改快遞的狀态為1，表示取件完成
*
* @param code 要修改的快遞取件碼
* @return 修改的結果，true表示成功，false表示失敗
*/
@Override
public boolean updateStatus(String code) {
//1. 連接配接的擷取
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
//2. 預編譯SQL語句
try {
state = conn.prepareStatement(SQL_UPDATE_STATUS);
state.setString(1, code);
return state.executeUpdate()>0?true:false;
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 根據id，删除單個快遞資訊
*
* @param id 要删除的快遞id
* @return 删除的結果，true表示成功，false表示失敗
*/
@Override
public boolean delete(int id) {
//1. 連接配接的擷取
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
//2. 預編譯SQL語句
try {
state = conn.prepareStatement(SQL_DELETE);
state.setInt(1,id);
return state.executeUpdate()>0?true:false;
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
}
5.2 Dao測試
07-項目訓練_流程與快遞表格建立~Express Service編寫一，主要流程二，編碼過程
Ctrl+Shift+T：生成測試類
07-項目訓練_流程與快遞表格建立~Express Service編寫一，主要流程二，編碼過程
這裡提供了部分測試的代碼實作
package com.xxy.dao.impl;
import com.xxy.bean.Express;
import com.xxy.exception.DuplicateCodeException;
import org.junit.Test;
import java.util.List;
import java.util.Map;
import static org.junit.Assert.*;
public class ExpressDaoMysqlTest {
ExpressDaoMysql dao = new ExpressDaoMysql();
@Test
public void console() {
List<Map<String, Integer>> console = dao.console();
System.out.println(console);
}
@Test
public void findAll() {
// List<Express> all = dao.findAll(true, 2, 3);// 從2開始查3個(offset從0開始)
List<Express> all = dao.findAll(false, 0, 0);
System.out.println(all);
}
@Test
public void findByNumber() {
Express e = dao.findByNumber("103");
System.out.println(e);
}
@Test
public void findByCode() {
Express e = dao.findByCode("123456");
System.out.println(e);
}
@Test
public void findByUserPhone() {
List<Express> byUserPhone = dao.findByUserPhone("13838411438");
System.out.println(byUserPhone);
}
@Test
public void findBySysPhone() {
List<Express> bySysPhone = dao.findBySysPhone("16666666666");
System.out.println(bySysPhone);
}
@Test
public void insert() {
Express e = new Express("123","迪盧克","18516955565","順豐快遞","123456","18888888888");
boolean result = false;
try {
result = dao.insert(e);
} catch (DuplicateCodeException e1) {
System.out.println("捕擷取件碼重複異常");
}
System.out.println(result);
}
@Test
public void update() {
}
@Test
public void updateStatus() {
}
@Test
public void delete() {
}
}
package com.xxy.exception;
public class DuplicateCodeException extends Exception {
/**
* Constructs a new exception with {@code null} as its detail message.
* The cause is not initialized, and may subsequently be initialized by a
* call to {@link #initCause}.
*/
public DuplicateCodeException() {
}
/**
* Constructs a new exception with the specified detail message. The
* cause is not initialized, and may subsequently be initialized by
* a call to {@link #initCause}.
*
* @param message the detail message. The detail message is saved for
* later retrieval by the {@link #getMessage()} method.
*/
public DuplicateCodeException(String message) {
super(message);
}
}
package com.xxy.util;
import java.util.Random;
public class RandomUtil {
private static Random r = new Random();
public static int getCode() { return r.nextInt(900000) + 100000; }
}
