在数据库中建2张表
员工表和打卡信息表
create table emp(
empno int auto_increment, -- 工号
ename varchar(200) default '' not null, -- 姓名
PRIMARY key(empno)
) auto_increment = 1000
-- 打卡信息表
create table signinfo(
id int auto_increment, -- 主键
empno int default 0 not null, -- 工号
signin datetime,-- 签到时间
signout datetime, -- 签退时间
PRIMARY key(id)
)
工程目录
数据库操作层 -- DAO
image.png
测试层
image.png
EmpDao.java
import java.util.List;
import java.util.Map;
import com.neuedu.jdbc3.Emp;
import com.neuedu.jdbc3.NewSignInfo;
import com.neuedu.jdbc3.SignInfo;
/**
* 所有关于员工的操作
* @author Administrator
*/
public interface EmpDao {
// 增加一个员工
void addEmp(String name);
// 根据empno删除员工
int delEmp(int empno);
// 根据empno修改员工姓名
int updateEmp(Emp e);
// 员工打卡
int signIn(int empno);
// 查询所有打卡信息
List<SignInfo> getSignInfo();
// 查询所有打卡信息，包含姓名，工号，签到时间，签退时间
// ① 再建一个实体类 ，包含姓名，工号，签到时间，签退时间
List<NewSignInfo> getNewSignInfo();
// ② List中存Map
List<Map<String, Object>> getNewSignInfoTwo();
}
EmpDaoImpl.java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.neuedu.jdbc3.DBUtils;
import com.neuedu.jdbc3.Emp;
import com.neuedu.jdbc3.NewSignInfo;
import com.neuedu.jdbc3.SignInfo;
public class EmpDaoImpl implements EmpDao{
@Override
public void addEmp(String name) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBUtils.getInstance().getConnection();
conn.setAutoCommit(false);
ps = conn.prepareStatement("insert into emp(ename) values (?)");
ps.setString(1, name);
ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
@Override
public int delEmp(int empno) {
Connection conn = null;
PreparedStatement ps = null;
int count = 0;
try {
conn = DBUtils.getInstance().getConnection();
conn.setAutoCommit(false);
ps = conn.prepareStatement("delete from emp where empno = ?");
ps.setInt(1, empno);
count = ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return count;
}
@Override
public int updateEmp(Emp e) {
Connection conn = null;
PreparedStatement ps = null;
int count = 0;
try {
conn = DBUtils.getInstance().getConnection();
conn.setAutoCommit(false);
ps = conn.prepareStatement("update emp set ename = ? where empno = ?");
// 从传入的对象当中获取编号和姓名
ps.setString(1, e.getEname());
ps.setInt(2, e.getEmpno());
count = ps.executeUpdate();
conn.commit();
} catch (SQLException e2) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e2.printStackTrace();
} finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
}
return count;
}
@Override
public int signIn(int empno) {
/*
* 1.查询empno工号员工是否存在（根据人数是0或1）
* 2.1 不为0-- 打卡insert/签退update
* 查询empno和当天打卡记录是否存在select count(id) idcounts from signinfo where empno = ? and date_format(signin,'%Y-%m-%d') = ?
* idcounts == 0 不存在 打卡insert
* idcounts != 0 存在打卡记录update
* 2.2 为0-- 提示不能打卡
*
* 常见错误：
* ①函数或表达式的列必须起别名
* ②yyyy-MM-dd 对应 %Y-%m-%d
* ③date_format(signin,'%Y-%m-%d') = ? 错写成 date_format(signin,'%Y-%m-%d' = ?)
*/
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
int count = 0;
try {
conn = DBUtils.getInstance().getConnection();
// 1.查询工号为empno的人数，根据人数是0或1就能判断该员工是否可以打卡
ps = conn.prepareStatement("select count(empno) counts from emp where empno = ?");
ps.setInt(1, empno);
rs = ps.executeQuery();
if(rs.next()) {
count = rs.getInt("counts");
// 2.count不为0，代表有该工号员工，可以打卡/签退
if(count != 0) {
// 判断是打卡【insert】还是签退【update】
// 先查询signinfo表中是否有当天打卡记录
Date curDate = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String strDate = sdf.format(curDate);
// 3.查询该工号员工，当天是否有打卡记录
ps = conn.prepareStatement("select count(id) idcounts from signinfo where empno = ? and date_format(signin,'%Y-%m-%d') = ?");
ps.setInt(1, empno);
ps.setString(2, strDate);
rs = ps.executeQuery();
if(rs.next()) {
int idcounts = rs.getInt("idcounts");
conn.setAutoCommit(false);
if(idcounts == 0) {
// idcounts是0，表示今天没打过卡,本次操作就是正常打卡【insert】
ps = conn.prepareStatement("insert into signinfo(empno,signin) values (?,?)");
ps.setInt(1, empno);
Timestamp ts = new Timestamp(curDate.getTime());
ps.setTimestamp(2, ts);
System.out.println(empno+" 号员工签到成功");
}else {
// 非0，表示今天打过卡，本次操作就是签退【update】
ps = conn.prepareStatement("update signinfo set signout = ? where empno = ? and date_format(signin,'%Y-%m-%d') = ?");
Timestamp ts = new Timestamp(curDate.getTime());
ps.setTimestamp(1, ts);
ps.setInt(2, empno);
ps.setString(3, strDate);
System.out.println(empno+" 号员工签退成功");
}
ps.executeUpdate();
conn.commit();
}
}else {
System.out.println("抱歉，您没有录入信息，不能打卡");
}
}
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
}
return count;
}
@Override
public List<SignInfo> getSignInfo() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
// 打卡名单
List<SignInfo> list = new ArrayList<SignInfo>();
try {
conn = DBUtils.getInstance().getConnection();
ps = conn.prepareStatement("select * from signinfo");
rs = ps.executeQuery();
while(rs.next()) {
int id = rs.getInt("id");
int empno = rs.getInt("empno");
Date signin = rs.getTimestamp("signin");
Date signout = rs.getTimestamp("signout");
// 封装信息
SignInfo info = new SignInfo();
info.setId(id);
info.setEmpno(empno);
info.setSignin(signin);
info.setSignout(signout);
// 扔进list
list.add(info);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
}
return list;
}
@Override
public List<NewSignInfo> getNewSignInfo() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
// 打卡名单
List<NewSignInfo> list = new ArrayList<NewSignInfo>();
try {
conn = DBUtils.getInstance().getConnection();
ps = conn.prepareStatement("select e.empno,ename,signin,signout from signinfo s join emp e on e.empno = s.empno");
rs = ps.executeQuery();
while(rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
Date signin = rs.getTimestamp("signin");
Date signout = rs.getTimestamp("signout");
// 封装信息
NewSignInfo info = new NewSignInfo();
info.setEname(ename);
info.setEmpno(empno);
info.setSignIn(signin);
info.setSignOut(signout);
// 扔进list
list.add(info);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
}
return list;
}
@Override
public List<Map<String, Object>> getNewSignInfoTwo() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
// 打卡名单
List<Map<String, Object>> list = new ArrayList<>();
try {
conn = DBUtils.getInstance().getConnection();
ps = conn.prepareStatement("select e.empno,ename,signin,signout from signinfo s join emp e on e.empno = s.empno");
rs = ps.executeQuery();
while(rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
Date signin = rs.getTimestamp("signin");
Date signout = rs.getTimestamp("signout");
// 封装信息
Map<String, Object> map = new HashMap<String, Object>();
map.put("empno", empno);
map.put("ename", ename);
map.put("signin", signin);
map.put("signout", signout);
// 扔进list
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
}
}
return list;
}
}
DBUtils.java
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* 1.节省资源 -- 单例
* 2.开闭原则 -- 配置文件方式获取数据库信息
* 3.获取链接 -- getConnection
*
* 常见错误：
* ①配置文件必须在src下（classpath）
* ②配置信息中不要有多余的符号，例如""
* @author Administrator
*/
public class DBUtils {
private static DBUtils db;
private String url_;
private String user_;
private String password_;
private DBUtils() {
try {
// 加载配置文件信息
Properties p = new Properties();
p.load(this.getClass().getClassLoader().getResourceAsStream("db.properties"));
// getProperty通过key值获取配置文件中的value值（返回值）
url_ = p.getProperty("url");
user_ = p.getProperty("user");
password_ = p.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public static DBUtils getInstance() {
if(db == null) {
db = new DBUtils();
}
return db;
}
/**
* 获取数据库连接的方法
* @return 连接对象
* @throws SQLException
*/
public Connection getConnection() throws SQLException {
Connection conn = DriverManager.getConnection(url_, user_, password_);
return conn;
}
}
Emp.java
public class Emp {
private int empno;
private String ename;
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
}
NewSignInfo.java
import java.util.Date;
public class NewSignInfo {
private int empno;
private String ename;
private Date signIn;
private Date signOut;
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Date getSignIn() {
return signIn;
}
public void setSignIn(Date signIn) {
this.signIn = signIn;
}
public Date getSignOut() {
return signOut;
}
public void setSignOut(Date signOut) {
this.signOut = signOut;
}
}
SignInfo.java
import java.util.Date;
public class SignInfo {
private int id;
private int empno;
private Date signin;
private Date signout;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public Date getSignin() {
return signin;
}
public void setSignin(Date signin) {
this.signin = signin;
}
public Date getSignout() {
return signout;
}
public void setSignout(Date signout) {
this.signout = signout;
}
}
Test.java
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Scanner;
import com.neuedu.dao.EmpDao;
import com.neuedu.dao.EmpDaoImpl;
/**
* 常见错误：
* ① DBUtils工具类
* 配置文件名书写错误
* url错误：没有删除之前的，清缓存Project-->clean
* @author Administrator
*/
public class Test {
static EmpDao ed = new EmpDaoImpl();
public static void main(String[] args) {
while(true) {
Scanner scan = new Scanner(System.in);
System.out.println("欢迎使用java2打卡系统");
System.out.println("================");
System.out.println("1.录入员工信息");
System.out.println("2.删除员工信息");
System.out.println("3.修改员工信息");
System.out.println("4.员工打卡");
System.out.println("5.查看打卡信息");
System.out.println("6.查看打卡详细信息①");
System.out.println("7.查看打卡详细信息②");
System.out.println("8.退出");
System.out.println("-------请选择------");
String option = scan.nextLine();
switch(option) {
case "1":
System.out.println("请输入要录入信息员工的姓名");
String name = scan.nextLine();
if(name == null || name.trim().equals("")) {
System.out.println("名字不能为空");
continue;
}else {
ed.addEmp(name);
System.out.println("录入信息成功");
}
break;
case "2":
System.out.println("请输入要删除员工的工号");
int empno = scan.nextInt();
int count = ed.delEmp(empno);
if(count != 0) {
System.out.println("删除员工信息成功");
}else {
System.out.println("没有找到该工号员工");
}
break;
case "3":
System.out.println("请输入要修改员工的工号");
int empno1 = scan.nextInt();
System.out.println("请输入新名字");
scan.nextLine();// 接回车
String name1 = scan.nextLine();
// ========================
// 封装信息 -- 用一个Emp对象将需要传送数据全部封装起来，将这个对象传入对应方法
Emp emp = new Emp();
emp.setEmpno(empno1);
emp.setEname(name1);
// ========================
int count1 = ed.updateEmp(emp);
if(count1 != 0) {
System.out.println("修改员工信息成功");
}else {
System.out.println("没有找到该工号员工");
}
break;
case "4":
System.out.println("请输入打卡员工的工号");
int empno2 = scan.nextInt();
ed.signIn(empno2);
break;
case "5":
System.out.println("打卡信息如下：");
// 调用getSignInfo()得到打卡信息的list
List<SignInfo> list = ed.getSignInfo();
for(SignInfo info : list) {
System.out.println(info.getId() + "," +info.getEmpno()+","+info.getSignin()+","+info.getSignout());
}
break;
case "6":
System.out.println("打卡信息如下：");
// 调用getSignInfo()得到打卡信息的list
List<NewSignInfo> list1 = ed.getNewSignInfo();
for(NewSignInfo info : list1) {
System.out.println(info.getEname() + "," +info.getEmpno()+","+info.getSignIn()+","+info.getSignOut());
}
break;
case "7":
System.out.println("打卡信息如下：");
List<Map<String, Object>> list2 = ed.getNewSignInfoTwo();
for(Map<String, Object> map : list2) {
for(Entry<String, Object> entry : map.entrySet()) {
System.out.println(entry.getValue());
}
System.out.println("========================");
}
break;
case "8":
System.out.println("再见");
return;
}
}
}
}
db.properties
# 右键，属性--》utf-8
# 数据库配置文件 name = value的形式配置
url = jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8
user = root
password = root
