文章目录
- 前言
- 一、背景介绍
- 二、项目目的
- 三、项目涉及技术点
- 四、项目标准
- 一、dao的编写
- 1、管理员登录的dao实现类
- 2、快递的dao实现类
- 3、用户的dao实现类
- 4、快递员的dao实现类
- 二、Controller的编写
- 总结
前言
学习Java的第一个小项目------快递e栈
一、背景介绍
做一个快递e栈
二、项目目的
1、锻炼动手能力
2、融会贯通JavaWeb技术
3、体验项目开发流程
4、做出上线级别的微信端+JavaWeb后端项目
5、为框架阶段打下前后端交互的基础
三、项目涉及技术点
html、css、js、Jquery、bootstrap、layui、layer、ajax、mysql、tomcat、servlet、filter、listener、jsp、公有云服务器。
四、项目标准
1.管理员登录 2.管理员退出 3.快件增加 4.快件删除 5.快件修改
6.快件列表查看 7.用户增加 8.用户删除 9.用户删除 10.用户列表查看
11.快递员增加 12.快递员删除 13.快递员修改 14.快递员查询…
一、dao的编写
Bean类的话就不列出,下面就做个参考,具体项目已经开源点击下方链接
链接: 点我--------------------------------<<<<<<<<<<<:_:<<<<!!
1、管理员登录的dao实现类
import com.jpx.dao.BaseAdminDao;
import com.jpx.util.DruidUtil;
import java.sql.*;
import java.util.Date;
public class BaseAdminDaoMysqlImpl implements BaseAdminDao {
private static final String SQL_UPDATE_LOGIN_TIME = "UPDATE COURIER SET LOGINTIME=NOW() WHERE USERNAME=?" ;
private static final String SQL_UPDATE_USER_LOGIN_TIME = "UPDATE USESR SET LOGINTIME=NOW() WHERE USERPHONE=?" ;
private static final String SQL_UPDATE_COURIER_LOGIN_TIME = "UPDATE COURIER SET LOGINTIME=NOW() WHERE USERPHONE=?" ;
private static final String SQL_LOGIN = "SELECT ID FROM COURIER WHERE USERNAME=? AND PASSWORD=?";
/**
* 根据用户名,更新登入时间和登录ip
*
* @param username
*/
@Override
public int updateLoginTime(String username) {
// 连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
// 编译sql
try {
state = conn.prepareStatement(SQL_UPDATE_LOGIN_TIME);
// state.setDate(1,new java.sql.Date(date.getTime()));
state.setString(1,username);
return state.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
// 释放资源
try {
conn.close();
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return -1;
}
/**
* 根据用户名,更新登入时间和登录ip
*
* @param userPhone
*/
@Override
public int updateUserLoginTime(String userPhone) {
// 连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
// 编译sql
try {
state = conn.prepareStatement(SQL_UPDATE_USER_LOGIN_TIME);
// state.setDate(1,new java.sql.Date(date.getTime()));
state.setString(1,userPhone);
return state.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
// 释放资源
try {
conn.close();
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return -1;
}
/**
* 根据用户名,更新登入时间和登录ip
*
* @param userPhone
*/
@Override
public int updateCourierLoginTime(String userPhone) {
// 连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
// 编译sql
try {
state = conn.prepareStatement(SQL_UPDATE_COURIER_LOGIN_TIME);
// state.setDate(1,new java.sql.Date(date.getTime()));
state.setString(1,userPhone);
return state.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
// 释放资源
try {
conn.close();
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return -1;
}
/**
* 根据账号密码登入
*
* @param username :用户名
* @param password :密码
* @return
*/
@Override
public boolean login(String username, String password) {
// 连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
// 编译sql
try {
state = conn.prepareStatement(SQL_LOGIN);
//填参数
state.setString(1,username);
state.setString(2,password);
rs = state.executeQuery();
// 根据查询结果返回
return rs.next();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
// 释放资源
DruidUtil.close(conn,state,rs);
}
return false;
}
}
2、快递的dao实现类
import com.jpx.bean.Express;
import com.jpx.dao.BaseExpressDao;
import com.jpx.exception.DuplicateCodeException;
import com.jpx.service.impl.BaseExpressServiceImpl;
import com.jpx.util.DruidUtil;
import java.sql.*;
import java.util.*;
public class BaseExpressDaoMysqlImpl 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_FING_ALL = "SELECT * FROM EXPRESS";
//用于分页查询数据库中所有快递的信息
public static final String SQL_FING_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_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() {
List<Map<String, Integer>> data = new ArrayList();
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
//2.编译sql语句
try {
state = conn.prepareStatement(SQL_CONSOLE);
//3.填参数
//4.执行sql语句
rs = state.executeQuery();
//获取执行结果
if (rs.next()){
int data1_size = rs.getInt("data1_size");
int data1_day = rs.getInt("data1_day");
int data2_size = rs.getInt("data2_size");
int data2_day = rs.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 {
//释放资源
DruidUtil.close(conn,state,rs);
}
return data;
}
/**
* 用于查询所有快递
*
* @param limit 是否分页的标记 ,true表示分页,false 表示查询所有快递
* @param offset SQL语句的起始索引
* @param pageNumber 页查询数量
* @return 快递的集合
*/
@Override
public List<Express> findAll(boolean limit, int offset, int pageNumber) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
List<Express> data = new ArrayList<>();
//2.编译sql语句
try {
if (limit){
state = conn.prepareStatement(SQL_FING_LIMIT);
//3.填参数
state.setInt(1,offset);
state.setInt(2,pageNumber);
}else{
state = conn.prepareStatement(SQL_FING_ALL);
}
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
while (rs.next()){
int id = rs.getInt("id");
String number = rs.getString("number");
String userName = rs.getString("userName");
String userPhone = rs.getString("userPhone");
String company = rs.getString("company");
String code = rs.getString("code");
Timestamp inTime = rs.getTimestamp("inTime");
Timestamp outTime = rs.getTimestamp("outTime");
int status = rs.getInt("status");
String sysPhone = rs.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(conn,state,rs);
}
return data;
}
/**
* 根据单号,查询快递
*
* @param number 单号
* @return 查询的快递信息,单号不存在时返回null
*/
@Override
public Express findByNumber(String number) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
//2.编译sql语句
try {
state = conn.prepareStatement(SQL_FIND_BY_NUMBER);
//3.填参数
state.setString(1,number);
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
if (rs.next()){
int id = rs.getInt("id");
String userName = rs.getString("userName");
String userPhone = rs.getString("userPhone");
String company = rs.getString("company");
String code = rs.getString("code");
Timestamp inTime = rs.getTimestamp("inTime");
Timestamp outTime = rs.getTimestamp("outTime");
int status = rs.getInt("status");
String sysPhone = rs.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(conn,state,rs);
}
return null;
}
/**
* 根据取件码,查询快递
*
* @param code 取件码
* @return 查询的快递信息,取件码不存在时返回null
*/
@Override
public Express findByCode(String code) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
//2.编译sql语句
try {
state = conn.prepareStatement(SQL_FIND_BY_CODE);
//3.填参数
state.setString(1,code);
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
if (rs.next()) {
int id = rs.getInt("id");
String number = rs.getString("number");
String userName = rs.getString("userName");
String userPhone = rs.getString("userPhone");
String company = rs.getString("company");
Timestamp inTime = rs.getTimestamp("inTime");
Timestamp outTime = rs.getTimestamp("outTime");
int status = rs.getInt("status");
String sysPhone = rs.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(conn,state,rs);
}
return null;
}
/**
* 根据手机号,查询快递
*
* @param userPhone 手机号
* @return 返回查询列表
*/
@Override
public List<Express> findByUserPhone(String userPhone) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
List<Express> data = new ArrayList<>();
//2.编译sql语句
try {
state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
//3.填参数
state.setString(1,userPhone);
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
while (rs.next()){
int id = rs.getInt("id");
String number = rs.getString("number");
String userName = rs.getString("userName");
String company = rs.getString("company");
String code = rs.getString("code");
Timestamp inTime = rs.getTimestamp("inTime");
Timestamp outTime = rs.getTimestamp("outTime");
int status = rs.getInt("status");
String sysPhone = rs.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(conn,state,rs);
}
return data;
}
/**
* 根据手机号,查询快递
*
* @param userPhone 手机号
* @param status
* @return 返回查询列表
*/
@Override
public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
List<Express> data = new ArrayList<>();
//2.编译sql语句
try {
state = conn.prepareStatement(SQL_FIND_BY_USERPHONE_AND_STATUS);
//3.填参数
state.setString(1,userPhone);
state.setInt(2,status);
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
while (rs.next()){
int id = rs.getInt("id");
String number = rs.getString("number");
String userName = rs.getString("userName");
String company = rs.getString("company");
String code = rs.getString("code");
Timestamp inTime = rs.getTimestamp("inTime");
Timestamp outTime = rs.getTimestamp("outTime");
String sysPhone = rs.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(conn,state,rs);
}
return data;
}
/**
* 根据录入手机号,查询快递
*
* @param sysPhone 手机号
* @return 返回查询列表
*/
@Override
public List<Express> findBySysPhone(String sysPhone) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
List<Express> data = new ArrayList<>();
//2.编译sql语句
try {
state = conn.prepareStatement(SQL_FIND_BY_SYSPHONE);
//3.填参数
state.setString(1,sysPhone);
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
while (rs.next()){
int id = rs.getInt("id");
String number = rs.getString("number");
String userName = rs.getString("userName");
String userPhone = rs.getString("userPhone");
String company = rs.getString("company");
String code = rs.getString("code");
Timestamp inTime = rs.getTimestamp("inTime");
Timestamp outTime = rs.getTimestamp("outTime");
int status = rs.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(conn,state,rs);
}
return data;
}
/**
* 快递的录入
* INSERT INTO EXPRESS (NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,NOW(),0,?)
* @param e 要录入的快递对象
* @return 录入的结果,true表示成功,false 表示失败
*/
@Override
public boolean insert(Express e){
//1.连接获取
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
try {
//2.预编译SQL语句
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) {
if (e1.getMessage().endsWith("for key 'code'")){
// DuplicateCodeException e2 = new DuplicateCodeException(e1.getMessage());
// throw e2;
System.out.println("取件码重复,请重新录入");
}else if(e1.getMessage().endsWith("for key 'number'")){
// DuplicateCodeException e2 = new DuplicateCodeException(e1.getMessage());
// throw e2;
System.out.println("单号重复,请重新录入");
}else {
e1.printStackTrace();
}
} finally {
//5.释放资源
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 快递的修改
* UPDATE EXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?
* @param id 要修改的快递id
* @param newExpress 新的快递对象(number,company,username,userPhone)
* @return 录入的结果,true表示成功,false 表示失败
*/
@Override
public boolean update(int id, Express newExpress) {
//连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
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 e) {
e.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 更改快递状态为1,表示取件完成
*
* @param code 要修改的快递单号
* @return 修改的结果,true表示成功,false 表示失败
*/
@Override
public boolean updateStatus(String code) {
//连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
try {
state = conn.prepareStatement(SQL_UPDATE_STATUS);
state.setString(1,code);
return state.executeUpdate()>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 根据id删除快递
*
* @param id 要删除的快递id
* @return 删除的结果,true表示成功,false 表示失败
*/
@Override
public boolean delete(int id) {
//连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
try {
state = conn.prepareStatement(SQL_DELETE);
state.setInt(1,id);
return state.executeUpdate()>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
}
3、用户的dao实现类
import com.jpx.bean.Courier;
import com.jpx.bean.User;
import com.jpx.dao.BaseUserDao;
import com.jpx.util.DruidUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class BaseUserDaoMysqlImpl implements BaseUserDao {
public static final String SQL_CONSOLE = "SELECT COUNT(ID) user_size, count(TO_DAYS(REGISTERtIME)=TO_DAYS(NOW()) OR NULL) user_day FROM USER";
public static final String SQL_FING_ALL = "SELECT * FROM USER";
public static final String SQL_FING_LIMIT = "SELECT * FROM USER LIMIT ?,?";
public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM USER WHERE USERPHONE=?";
public static final String SQL_INSERT = "INSERT INTO USER(USERNAME,USERPHONE,IDCODE,PASSWORD,REGISTERTIME) VALUES (?,?,?,?,NOW())";
public static final String SQL_UPDATE = "UPDATE user SET USERNAME=?,USERPHONE=?,IDCODE=?,PASSWORD=?,REGISTERTIME=NOW() WHERE ID=?";
public static final String SQL_UPDATE_USERNAME_AND_USERPHONE = "UPDATE user SET USERNAME=?,USERPHONE=?,REGISTERTIME=NOW() WHERE ID=?";
public static final String SQL_DELETE = "DELETE FROM USER WHERE ID=?";
/**
* 用于查询数据库中的用户人数(总数+日注册量)
*
* @return [{size:总数,day:日注册量}]
*/
@Override
public List<Map<String, Integer>> console() {
List<Map<String, Integer>> data = new ArrayList();
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
//2.编译sql语句
try {
state = conn.prepareStatement(SQL_CONSOLE);
//3.填参数
//4.执行sql语句
rs = state.executeQuery();
//获取执行结果
if (rs.next()){
int user_size = rs.getInt("user_size");
int user_day = rs.getInt("user_day");
Map courice = new HashMap();
courice.put("user_size",user_size);
courice.put("user_day",user_day);
data.add(courice);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//释放资源
DruidUtil.close(conn,state,rs);
}
return data;
}
/**
* 用于查询所有用户
*
* @param limit 是否分页的标记 ,true表示分页,false 表示查询所有用户
* @param offset SQL语句的起始索引
* @param pageNumber 页查询数量
* @return 用户的集合
*/
@Override
public List<User> findAll(boolean limit, int offset, int pageNumber) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
List<User> data = new ArrayList<>();
//2.编译sql语句
try {
if (limit){
state = conn.prepareStatement(SQL_FING_LIMIT);
//3.填参数
state.setInt(1,offset);
state.setInt(2,pageNumber);
}else{
state = conn.prepareStatement(SQL_FING_ALL);
}
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
while (rs.next()){
int id = rs.getInt("id");
String userName = rs.getString("userName");
String userPhone = rs.getString("userPhone");
String passWord = rs.getString("passWord");
Timestamp registerTime = rs.getTimestamp("registerTime");
Timestamp loginTime = rs.getTimestamp("loginTime");
User user = new User(id, userName, userPhone, passWord, registerTime, loginTime);
data.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6。释放资源
DruidUtil.close(conn,state,rs);
}
return data;
}
/**
* 根据手机号,查询用户
*
* @param userPhone 手机号
* @return 返回查询列表
*/
@Override
public User findByUserPhone(String userPhone) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
try {
state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
//3.填参数
state.setString(1,userPhone);
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
while (rs.next()){
int id = rs.getInt("id");
String userName = rs.getString("userName");
String idCode = rs.getString("idCode");
String passWord = rs.getString("passWord");
Timestamp registerTime = rs.getTimestamp("registerTime");
Timestamp loginTime = rs.getTimestamp("loginTime");
User user = new User(id, userName, userPhone, idCode, passWord,registerTime, loginTime);
return user;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6。释放资源
DruidUtil.close(conn,state,rs);
}
return null;
}
/**
* 快递的录入
*
* @param u 要录入的用户
* @return 录入的结果,true表示成功,false 表示失败
*/
@Override
public boolean insert(User u) {
//1.连接获取
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
try {
//2.预编译SQL语句
state=conn.prepareStatement(SQL_INSERT);
//3.填充参数
state.setString(1,u.getUserName());
state.setString(2,u.getUserPhone());
state.setString(3,u.getIdCode());
state.setString(4,u.getPassWord());
return state.executeUpdate()>0?true:false;
} catch (SQLException e) {
if (e.getMessage().endsWith("for key 'userName'")){
System.out.println("用户名重复,请重新录入");
}else if(e.getMessage().endsWith("for key 'userPhone'")){
System.out.println("手机号重复,请重新录入");
}else if(e.getMessage().endsWith("for key 'idCode'")){
System.out.println("身份证重复,请重新录入");
}else {
e.printStackTrace();
}
} finally {
//5.释放资源
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 用户的修改
*UPDATE user SET USERNAME=?,USERPHONE=?,IDCODE=?,PASSWORD=?,REGISTERTIME=NOW() WHERE ID=?
* @param id 要修改的用户id
* @param newUser 新的快递对象(username,userPhone,idCode,passWord)
* @return 录入的结果,true表示成功,false 表示失败
*/
@Override
public boolean update(int id, User newUser) {
//连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
try {
state = conn.prepareStatement(SQL_UPDATE);
state.setString(1,newUser.getUserName());
state.setString(2,newUser.getUserPhone());
state.setString(3,newUser.getIdCode());
state.setString(4,newUser.getPassWord());
state.setInt(5,id);
return state.executeUpdate()>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 用户的修改
*
* @param newUser 新的快递对象(username,userPhone,idCode,passWord)
* @return 录入的结果,true表示成功,false 表示失败
*/
@Override
public boolean updateUserNameAndUserPhone(int id, User newUser) {
//连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
try {
state = conn.prepareStatement(SQL_UPDATE_USERNAME_AND_USERPHONE);
state.setString(1,newUser.getUserName());
state.setString(2,newUser.getUserPhone());
state.setInt(3,id);
return state.executeUpdate()>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 根据id删除用户
*
* @param id 要删除的用户的id
* @return 删除的结果,true表示成功,false 表示失败
*/
@Override
public boolean delete(int id) {
//连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
try {
state = conn.prepareStatement(SQL_DELETE);
state.setInt(1,id);
return state.executeUpdate()>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
}
4、快递员的dao实现类
import com.jpx.bean.Courier;
import com.jpx.bean.Express;
import com.jpx.dao.BaseCourierDao;
import com.jpx.util.DruidUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class BaseCourierDaoMysqlImpl implements BaseCourierDao {
public static final String SQL_CONSOLE = "SELECT COUNT(ID) courice_size, count(TO_DAYS(REGISTERtIME)=TO_DAYS(NOW()) OR NULL) courice_day FROM COURIER";
public static final String SQL_FING_ALL = "SELECT * FROM COURIER";
public static final String SQL_FING_LIMIT = "SELECT * FROM COURIER LIMIT ?,?";
public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM COURIER WHERE USERPHONE=?";
public static final String SQL_INSERT = "INSERT INTO COURIER(USERNAME,USERPHONE,IDCODE,PASSWORD,NUMBER,REGISTERTIME) VALUES (?,?,?,?,0,NOW())";
public static final String SQL_UPDATE = "UPDATE COURIER SET USERNAME=?,USERPHONE=?,IDCODE=?,PASSWORD=?,REGISTERTIME=NOW() WHERE ID=?";
public static final String SQL_DELETE = "DELETE FROM COURIER WHERE ID=?";
/**
* 用于查询数据库中的快递员人数(总数+日注册量)
*
* @return [{size:总数,day:日注册量}]
*/
@Override
public List<Map<String, Integer>> console() {
List<Map<String, Integer>> data = new ArrayList();
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
//2.编译sql语句
try {
state = conn.prepareStatement(SQL_CONSOLE);
//3.填参数
//4.执行sql语句
rs = state.executeQuery();
//获取执行结果
if (rs.next()){
int courice_size = rs.getInt("courice_size");
int courice_day = rs.getInt("courice_day");
Map courice = new HashMap();
courice.put("courice_size",courice_size);
courice.put("courice_day",courice_day);
data.add(courice);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//释放资源
DruidUtil.close(conn,state,rs);
}
return data;
}
/**
* 用于查询所有快递
*
* @param limit 是否分页的标记 ,true表示分页,false 表示查询所有快递
* @param offset SQL语句的起始索引
* @param pageNumber 页查询数量
* @return 快递员的集合
*/
@Override
public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
List<Courier> data = new ArrayList<>();
//2.编译sql语句
try {
if (limit){
state = conn.prepareStatement(SQL_FING_LIMIT);
//3.填参数
state.setInt(1,offset);
state.setInt(2,pageNumber);
}else{
state = conn.prepareStatement(SQL_FING_ALL);
}
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
while (rs.next()){
int id = rs.getInt("id");
String userName = rs.getString("userName");
String userPhone = rs.getString("userPhone");
String idCode = rs.getString("idCode");
String passWord = rs.getString("passWord");
String number = rs.getString("number");
Timestamp registerTime = rs.getTimestamp("registerTime");
Timestamp loginTime = rs.getTimestamp("loginTime");
Courier courier = new Courier(id, userName, userPhone, idCode, passWord, number, registerTime, loginTime);
data.add(courier);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6。释放资源
DruidUtil.close(conn,state,rs);
}
return data;
}
/**
* 根据手机号,查询快递员
*
* @param userPhone 手机号
* @return 不存在返回null
*/
@Override
public Courier findByUserPhone1(String userPhone) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
try {
state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
//3.填参数
state.setString(1,userPhone);
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
while (rs.next()){
int id = rs.getInt("id");
String userName = rs.getString("userName");
String idCode = rs.getString("idCode");
String passWord = rs.getString("passWord");
String number = rs.getString("number");
Timestamp registerTime = rs.getTimestamp("registerTime");
Timestamp loginTime = rs.getTimestamp("loginTime");
Courier courier = new Courier(id, userName, userPhone, idCode, passWord, number, registerTime, loginTime);
return courier;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6。释放资源
DruidUtil.close(conn,state,rs);
}
return null;
}
/**
* 根据手机号,查询快递员
*
* @param userPhone 手机号
* @return 返回查询列表
*/
@Override
public List<Courier> findByUserPhone(String userPhone) {
//1. 获取链接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
ResultSet rs = null;
List<Courier> data = new ArrayList<>();
//2.编译sql语句
try {
state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
//3.填参数
state.setString(1,userPhone);
//4.执行sql语句
rs = state.executeQuery();
//5.获取执行结果
while (rs.next()){
int id = rs.getInt("id");
String userName = rs.getString("userName");
String idCode = rs.getString("idCode");
String passWord = rs.getString("passWord");
String number = rs.getString("number");
Timestamp registerTime = rs.getTimestamp("registerTime");
Timestamp loginTime = rs.getTimestamp("loginTime");
Courier courier = new Courier(id, userName, userPhone, idCode, passWord, number, registerTime, loginTime);
data.add(courier);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6。释放资源
DruidUtil.close(conn,state,rs);
}
return data;
}
/**
* 快递员的录入
*
* @param c 要录入的快递员对象
* @return 录入的结果,true表示成功,false 表示失败
*/
@Override
public boolean insert(Courier c) {
//1.连接获取
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
try {
//2.预编译SQL语句
state=conn.prepareStatement(SQL_INSERT);
//3.填充参数
state.setString(1,c.getUserName());
state.setString(2,c.getUserPhone());
state.setString(3,c.getIdCode());
state.setString(4,c.getPassWord());
return state.executeUpdate()>0?true:false;
} catch (SQLException e) {
if (e.getMessage().endsWith("for key 'userName'")){
System.out.println("用户名重复,请重新录入");
}else if(e.getMessage().endsWith("for key 'userPhone'")){
System.out.println("手机号重复,请重新录入");
}else if(e.getMessage().endsWith("for key 'idCode'")){
System.out.println("身份证重复,请重新录入");
}else {
e.printStackTrace();
}
} finally {
//5.释放资源
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 快递员的修改
*UPDATE COURIER SET USERNAME=?,USERPHONE=?,IDCODE=?,PASSWORD=? WHERE ID=?
* @param id 要修改的快递员id
* @param newCourier 新的快递对象(userName,userPhone,idCode,passWord)
* @return 录入的结果,true表示成功,false 表示失败
*/
@Override
public boolean update(int id, Courier newCourier) {
//连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
try {
state = conn.prepareStatement(SQL_UPDATE);
state.setString(1,newCourier.getUserName());
state.setString(2,newCourier.getUserPhone());
state.setString(3,newCourier.getIdCode());
state.setString(4,newCourier.getPassWord());
state.setInt(5,id);
return state.executeUpdate()>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
/**
* 根据id删除快递员
*
* @param id 要删除的快递员
* @return 删除的结果,true表示成功,false 表示失败
*/
@Override
public boolean delete(int id) {
//连接
Connection conn = DruidUtil.getConnection();
PreparedStatement state = null;
try {
state = conn.prepareStatement(SQL_DELETE);
state.setInt(1,id);
return state.executeUpdate()>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DruidUtil.close(conn,state,null);
}
return false;
}
}
二、Controller的编写
就不细说了点链接–>> 点我
总结
该项目是我做的第一个项目,其中学习到了许多之前没有接触的知识点,还有简单的项目开发流程.起点虽低,但希望以后成为大牛,哈哈 。
做项目的时候也遇到许许多多的问题,虽然基本上已经完成,但是还有很多细节没有完善,也希望大家可以提提建议,讨论一下,谢谢。