题目要求

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)
)

图片发自简书App

工程目录

图片发自简书App

架包导入

①先在项目上右键，选择properties
②左侧选择Java Build Path后，右侧点Add External JARs...
③选择mysql-connector-java-5.1.39-bin架包
④确认

配置文件（名称db.properties，注意要在src目录下）

/数据库配置文件name = value的形式配置/
url=jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8
user = root
password = root

package com.neuedu.dao;包下接口（名称EmpDao）

package com.neuedu.dao;

import com.neuedu.jdbc3.Emp;

/**
 * 所有关于员工的操作
 * @author Administrator
 *
 */
public interface EmpDao {
    
    void addEmp(String name) ;//增加一个员工
    
    int delEmp(int empno); //根据id删除员工
    
    int updateEmp(Emp e); //修改员工信息
    
    List<Map<String,Object>> lookEmp(int empno);
    
    int signEmp(int empno);//签到、签退
}

package com.neuedu.dao;包下接口实现类（名称EmpDaoImpl）

package com.neuedu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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.Signinfo;

public class EmpDaoImpl implements EmpDao{
    
    public int addEmp(String name) {
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;//添加员工
        PreparedStatement ps1 = null;//查找刚添加的员工编号
        ResultSet rs = null;
        try {
            conn=DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("insert into emp(ename) values (?)");
            ps.setString(1, name);
            count = ps.executeUpdate();
            conn.commit();
            int empno = 0;
            ps1 = conn.prepareStatement("select empno from emp where ename =?");
            ps1.setString(1, name);
            rs =ps1.executeQuery();
            while(rs.next()) {
                empno=rs.getInt("empno");
            }
            System.out.println("您的工号为"+empno);
            
        } 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) {
            }
        }
        }
        return count;
    }
    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) {
            }
        }
        }
        return count;
    }
    public int updateEmp(Emp emp) {
        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, emp.getEname());
            ps.setInt(2, emp.getEmpno());
            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) {
            }
        }
        }
        return count;
    }
    public List<Map<String,Object>> lookEmp(int empno) {
        Map<String,Object> map = new HashMap<String,Object>();
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn=DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("select e.empno,ename,signin,signout from emp e join signinfo s on e.empno = s.empno ");
            rs =ps.executeQuery();
            boolean bl = true;
            while(rs.next()) {
                int empno1 = rs.getInt("empno");
                if(empno == empno1) {
                String ename = rs.getString("ename");
                String signin = rs.getString("signin");
                String signout = rs.getString("signout");
//                System.out.println("员工号为"+empno1+",员工名为"+ename+",签到时间"+signin+",签退时间"+signout);
                
//              Emp emp = new Emp();
//              
//              emp.setEmpno(empno1);
//              emp.setEname(ename);
//              si.setEmpno(empno1);
//              si.setSignin(signin);
//              si.setSignout(signout);
//              map.put(emp, si);
                
                map.put("  员工编号", empno1);
                map.put("  员工姓名", ename);
                map.put("  签到时间", signin);
                map.put("  签退时间", signout);
                list.add(map);
                
                bl=false;
                }
            }if(bl) {System.out.println("查无此人，请联系工作人员添加");}
        } 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) {
                
            }
        }
        
       }
        return list;
    }
    
    public int signEmp(int empno) {
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;//判断签到表格状态
        PreparedStatement ps1 = null;//签到
        PreparedStatement ps2 = null;//签退
        PreparedStatement ps3 = null;//签退时查找最后一条更新
        PreparedStatement ps4 = null;//签到时查询是否有这名员工
        ResultSet rs = null;
        try {
            conn=DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            
            ps = conn.prepareStatement("select empno,signin,signout from signinfo where empno=?");
            ps.setInt(1, empno);
            rs =ps.executeQuery();
            int empno1= 0;
            String signin = null;
            String signout = null;
            while(rs.next()) {
                signin = rs.getString("signin");
                signout = rs.getString("signout");
                empno1 = rs.getInt("empno");
                }
            if(empno1 == 0 ) {
//              System.out.println("走empno1==0这个if了");
                Integer empnoboolean=0;
                ps4 = conn.prepareStatement("select empno from emp where empno=?");
                ps4.setInt(1, empno);
                rs =ps4.executeQuery();
                while(rs.next()) {empnoboolean=rs.getInt("empno");}
                if(empnoboolean == 0) {
                    System.out.println("抱歉，没有这个员工，请联系工作人员添加");
                    if(conn != null) {
                        try {
                            conn.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                    if(ps != null) {
                        try {
                            ps.close();
                        } catch (SQLException e) {
                            
                        }
                    }
                    return 0;
                }
            }
            if(signin == null || signout != null) {
            Date d = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sdfd = sdf.format(d);
            ps1 = conn.prepareStatement("insert into signinfo(empno,signin) values(?,?)");
            ps1.setInt(1, empno);
            ps1.setString(2, sdfd);
            count = ps1.executeUpdate();
            conn.commit();
            }else if(signin != null && signout == null){
//              System.out.println("走签退这个if了");
                int id = 0;
                ps3 = conn.prepareStatement("select id from signinfo where empno = ?");
                ps3.setInt(1, empno);
                rs=ps3.executeQuery();
                while(rs.next()) {
                    id=rs.getInt("id");
                }
                
                ps2 = conn.prepareStatement("update signinfo set signout =? where id = ?");
                ps2.setInt(2, id);
                Date d1 = new Date();
                SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String sdfd1 = sdf1.format(d1);
                ps2.setString(1, sdfd1);
                ps2.executeUpdate();
                count = 2;
                conn.commit();
            }else {System.out.println("代码写错了回去改吧");}
        } 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) {
                
            }
        }
        
        }
        return count;
    }
    
}

package com.neuedu.jdbc3;包下连接类（名称DBUtils）

package com.neuedu.jdbc3;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
 * 1、节省资源--单例
 * 2、开闭原则 -- 配置文件方式获取数据库信息
 * 3、获取链接-- getConnection
 * @author Administrator
 *
 */

public class DBUtils {
    private String url ;
    private String user ;
    private String password ;
    private static DBUtils db ;
    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;
    }
}

package com.neuedu.jdbc3;包下Emp封装类（名称Emp）

package com.neuedu.jdbc3;

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;
    }
    
}

package com.neuedu.jdbc3;包下测试类（名称Test）

package com.neuedu.jdbc3;

import java.util.HashMap;
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;
/**
 * 录入信息
 * @author Administrator
 *
 */
public class Test {
    static EmpDao de = 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("======请选择======");
            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 {
                    de.addEmp(name);
                    System.out.println("录入信息成功");
                }
                break;
            case "2":
                System.out.println("请输入要删除员工的工号");
                int empno = scan.nextInt();
                int count = de.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 upname = scan.nextLine();
                Emp emp = new Emp();
                emp.setEmpno(empno1);
                emp.setEname(upname);
                int count1 = de.updateEmp(emp);
                if(count1 != 0) {
                    System.out.println("修改员工信息成功");
                }else {
                    System.out.println("没有该员工");
                }
                break;
            case "4":
                System.out.println("请输入员工工号签到");
                int empno4 = scan.nextInt();
                int count4 = de.signEmp(empno4);
                if(count4 == 1) {
                    System.out.println("签到成功");
                }else if(count4 == 2){
                    System.out.println("签退成功");
                }else {
                    System.out.println("没有您的员工信息，请联系工作人员填入");
                }
                break;
            case "5":
                System.out.println("请输入要查询信息的员工工号");
                    int id = scan.nextInt();
                    List<Map<String,Object>> list = de.lookEmp(id);
                    for(Map<String,Object> arr : list) {
                        for(Entry<String,Object> arr1 : arr.entrySet() ) {
                            System.out.print(arr1.getKey()+arr1.getValue());
                        }
                        System.out.println();
                    }
                    
                break;  
                
            case "6":
                System.out.println("欢迎使用，再见");
                return;
                
                }
            }
        }
    }