JDBC练习题1-员工打卡

作者: 伍陆柒_ | 来源:发表于2019-03-29 17:05 被阅读0次

在数据库中建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

JDBC练习题1-员工打卡

工程目录

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读