JavaWeb综合项目——快递e栈

package com.wangjiawei.dao.imp; import com.wangjiawei.bean.Express; import com.wangjiawei.dao.BaseExpressDao; import com.wangjiawei.exception.DuplicateCodeException; import com.wangjiawei.util.DruidUtil; import java.sql.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 2 * @Author: 小王同学 * 3 * @Date: 2020/10/3 14:16 * 4 */ 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_FIND_BY_PHONE_YEAR = "SELECT * FROM EXPRESS WHERE USERPHONE=? AND YEAR(NOW())-YEAR(INTIME)<1"; /** * 通过手机号查一个月内的快递 */ public static final String SQL_FIND_BY_PHONE_MONTH = "SELECT * FROM EXPRESS WHERE USERPHONE=? AND MONTH(NOW())-MONTH(INTIME)<1"; /** * 通过用户手机号和快递状态查询快递信息 */ public static final String SQL_FIND_BY_USERPHONE_AND_STATUS = "SELECT * FROM EXPRESS WHERE USERPHONE=? AND STATUS=?"; /** * 录入快递 */ 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() { ArrayList<Map<String, Integer>> data = new ArrayList<>(); Connection conn = DruidUtil.getConnection(); PreparedStatement state = null; ResultSet result = null; try { state = conn.prepareStatement(SQL_CONSOLE); result = state.executeQuery(); 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<String,Integer> data1 = new HashMap<>(); data1.put("data1_size",data1_size); data1.put("data1_day",data1_day); Map<String,Integer> data2 = new HashMap<>(); data2.put("data2_size",data2_size); data2.put("data2_day",data2_day); data.add(data1); data.add(data2); } }catch (SQLException throwables){ throwables.printStackTrace(); }finally { DruidUtil.close(conn,state,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<>(); Connection conn = DruidUtil.getConnection(); PreparedStatement state = null; ResultSet result = null; try { if (limit){ state = conn.prepareStatement(SQL_FIND_LIMIT); state.setInt(1,offset); state.setInt(2,pageNumber); }else { state = conn.prepareStatement(SQL_FIND_ALL); } result = state.executeQuery(); 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 { DruidUtil.close(conn,state,result); } return data; } /** * 查询一年内所有快递 * * @param userPhone * @return */ @Override public List<Express> findAllAmongYearByPhone(String userPhone) { ArrayList<Express> data = new ArrayList<>(); Connection conn = DruidUtil.getConnection(); PreparedStatement state = null; ResultSet result = null; try { state = conn.prepareStatement(SQL_FIND_BY_PHONE_YEAR); state.setString(1, userPhone); result = state.executeQuery(); 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 throwables){ throwables.printStackTrace(); }finally { DruidUtil.close(conn,state,result); } return data;