java小练习员工管理系统

使用JDBC访问数据库
分层开发：前台：调用后台并输出结果后台：使用JDBC访问数据库并返回结果
提取工具类DBUtil，复用代码
使用Properties类读取属性文件
使用log4j记录日志
使用连接池提高效率

一、创建数据库（创建表emp）

二、idea创建一些框架的类

EmployeeDao：接口
EmployeeDaoImp：接口实现类
Employee：实体
Test：前台测试
DButil：连接数据库工具类
jdbc.properties：配置参数文本
log4j.properties：日志参数文本

使用的jar包：log4j-1.2.8.jar mysql-connector-java-8.0.27.jar

三、结尾另外新增设置连接池

ConnectionPool：连接数据库的连接池
DButil需稍作修改

四、代码

创建表emp

CREATE TABLE `emp` (
  `EMPNO` int NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int DEFAULT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY `FK_DEPTNO` (`DEPTNO`),
  KEY `index_sal_hiredate` (`SAL`,`HIREDATE`),
  ConSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

插入数据语句忘记保存了。

那个deptno外键是因为还有其他表，因为没有用到所以不多展示。

idea整体框架

（DButil_Pool、TestProperties这两个类可忽略）

创建接口EmployeeDao

package com.dao;

import com.entity.Employee;

import java.util.List;


public interface EmployeeDao {
    
    public List findAll();

    
    public Employee findById(int empno);

    
    public int save(Employee emp);

    
    public int update(Employee emp);

    
    public int delete(int empno);



}

创建实现类EmployeeDaoImp

package com.dao.imp;

import com.dao.EmployeeDao;
import com.entity.Employee;
import com.util.DButil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDaoImp implements EmployeeDao {
    @Override
    public List findAll() {
        Connection conn =null;
        Statement stmt=null;
        ResultSet rs=null;

        List list=new ArrayList();
        try{
            conn=DButil.getConnection();
            stmt=conn.createStatement();

            String sql="select * from emp";
            rs=stmt.executeQuery(sql);
            while (rs.next()){
                list.add(new Employee(rs.getInt("empno"),rs.getString("ename"),
                        rs.getString("job"),rs.getInt("mgr"),rs.getDate("hiredate"),
                        rs.getDouble("sal"),rs.getDouble("comm"),rs.getInt("deptno")));
            }

        }catch (SQLException e){
            e.printStackTrace();
        }
        finally {
            DButil.closeAll(conn,stmt,rs);
        }

        return list;
    }

    @Override
    public Employee findById(int empno) {
        Connection conn =null;
        Statement stmt=null;
        ResultSet rs=null;
        Employee employee=null;
        try{
            conn=DButil.getConnection();
            stmt=conn.createStatement();

            String sql="select * from emp where empno='"+empno+"'";
            rs=stmt.executeQuery(sql);
            if (rs.next()){
                employee=new Employee(rs.getInt("empno"),rs.getString("ename"),
                        rs.getString("job"),rs.getInt("mgr"),rs.getDate("hiredate"),
                        rs.getDouble("sal"),rs.getDouble("comm"),rs.getInt("deptno"));
            }

        }catch (SQLException e){
            e.printStackTrace();
        }
        finally {
            DButil.closeAll(conn,stmt,rs);
        }

        return employee;
    }

    @Override
    public int save(Employee emp) {
        String sql="insert into emp values(?,?,?,?,?,?,?,?)";
        Object[] objects={emp.getEmpno(),emp.getEname(),emp.getJob(),emp.getMgr(),
                new java.sql.Date((emp.getHiredate().getTime())),emp.getSal(),emp.getComm()
                ,emp.getDeptno()};
        return DButil.executeUpdate(sql,objects);
    }

    @Override
    public int update(Employee emp) {
        String sql="UPDATE emp set job=?,sal=?,deptno=? where empno=?";
        Object[] objects={emp.getJob(),emp.getSal(),emp.getDeptno(),emp.getEmpno()};
        return DButil.executeUpdate(sql,objects);
    }

    @Override
    public int delete(int empno) {
        String sql="DELETE from emp where empno=?";
        Object[] objects={empno};
        return DButil.executeUpdate(sql,objects);


    }
}

创建实体类Employee

package com.entity;

import java.util.Date;


public class Employee {
    private int empno;
    private String ename;
    private String job;
    private int mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private int deptno;


    public Employee() {
    }

 
    public Employee(String ename, String job, int mgr, Date hiredate, Double sal, Double comm, int deptno) {
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }

    public Employee(int empno, String ename, String job, int mgr, Date hiredate, Double sal, Double comm, int deptno) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }

    public Employee(int empno, String ename, String job, Double sal, int deptno) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.sal = sal;
        this.deptno = deptno;
    }

    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 String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public Double getSal() {
        return sal;
    }

    public void setSal(Double sal) {
        this.sal = sal;
    }

    public Double getComm() {
        return comm;
    }

    public void setComm(Double comm) {
        this.comm = comm;
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "empno=" + empno +
                ", ename='" + ename + ''' +
                ", job='" + job + ''' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }
}

创建工具类DButil

package com.util;

import org.apache.log4j.Logger;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;



public abstract class DButil {

    private static Logger logger=Logger.getLogger(DButil.class.getName());

    private DButil(){

    }
    static String driver;
    static String url;
    static String user;
    static String password;
static {
    Properties properties =new Properties();
    InputStream inputStream=DButil.class.getResourceAsStream("/jdbc.properties");
    try {
        properties.load(inputStream);
        logger.info("成功读取jdbc配置文件"+properties);

    } catch (IOException e) {
        e.printStackTrace();
        logger.error("读取jdbc配置文件错误"+e.toString());
    }

    driver=properties.getProperty("driver");
    url=properties.getProperty("url");
    user=properties.getProperty("user");
    password=properties.getProperty("password");
}
    
    public static Connection getConnection(){

        Connection conn=null;

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url,user,password);
            logger.info("连接数据库成功"+conn);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException e) {
            e.printStackTrace();
            logger.error("连接数据库失败" + e.toString());
        }
        return conn;

    }

    
    public static void closeAll(Connection conn, Statement st,ResultSet rs){
        try {
            if(rs!=null)
                rs.close();
              logger.info("关闭结果集");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(st!=null)
                st.close();
            logger.info("关闭statement");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null)
                conn.close();
            logger.info("关闭连接");
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    
    public static int executeUpdate(String sql,Object []objects){
        Connection conn =null;
        Statement stmt=null;
        ResultSet rs=null;
        PreparedStatement pstmt=null;
        int n=0;
        try{
            conn=DButil.getConnection();

            pstmt=conn.prepareStatement(sql);

            for (int i = 0; i  
 
  创建Test测试类（主方法） 
 
package com.test;

import com.dao.EmployeeDao;
import com.dao.imp.EmployeeDaoImp;
import com.entity.Employee;

import java.text.ParseException;
import java.text.SimpleDateFormat;

import java.sql.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;


public class Test {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        int n;
        while (true){
            System.out.println("1.遍历");
            System.out.println("2.查找");
            System.out.println("3.退出");
            System.out.println("4.添加");
            System.out.println("5.修改");
            System.out.println("6.删除");
            n=sc.nextInt();
            switch (n){
                case 1: findAll();break;
                case 2: findById();break;
                case 3:return;
                case 4: addEmp();break;
                case 5:update();break;
                case 6:delete();break;

            }
            System.out.println("输入任意键继续");
            sc.nextLine();
            sc.nextLine();
        }

    }

    private static void delete() {
        Scanner sc= new Scanner(System.in);

        System.out.println("编号:");
        int empno=sc.nextInt();

        if((new EmployeeDaoImp().delete(empno))>0){
            System.out.println("删除成功");
        }else System.out.println("删除失败");
    }

    private static void update() {
        Scanner sc= new Scanner(System.in);

        System.out.println("编号:");
        int empno=sc.nextInt();
        System.out.println("姓名:");
        String ename=sc.next();
        System.out.println("岗位:");
        String job=sc.next();
       System.out.println("薪水:");
        Double sal=sc.nextDouble();
        System.out.println("部门编号:");
        int deptno=sc.nextInt();

        if((new EmployeeDaoImp().update(new Employee(empno,ename,job,sal,deptno)))>0){
            System.out.println("修改成功");
        }else System.out.println("修改失败");
    }

    private static void addEmp() {
        Scanner sc= new Scanner(System.in);

        System.out.println("编号:");
         int empno=sc.nextInt();
        System.out.println("姓名:");
        String ename=sc.next();
        System.out.println("岗位:");
        String job=sc.next();
        System.out.println("上级编号:");
        int mgr=sc.nextInt();
        System.out.println("入职时间:");
        SimpleDateFormat ss=new SimpleDateFormat("yyyy-MM-dd");
        String str=sc.next();
        Date hiredate=null;
        try {
            hiredate=new Date(ss.parse(str).getTime());
        } catch (ParseException e) {
            e.printStackTrace();
        }

        System.out.println("薪水:");
        Double sal=sc.nextDouble();
        System.out.println("补贴:");
        Double comm=sc.nextDouble();
        System.out.println("部门编号:");
        int deptno=sc.nextInt();

        if((new EmployeeDaoImp().save(new Employee(empno,ename,job,mgr,hiredate,sal,comm,deptno)))>0){
            System.out.println("添加成功");
        }else System.out.println("添加失败");
    }

    private static void findById() {
        EmployeeDao ed=new EmployeeDaoImp();
        Scanner sc = new Scanner(System.in);
        System.out.println("编号：");
        int empno=sc.nextInt();
        Employee employee=ed.findById(empno);
        if (employee ==null){
            System.out.println("找无此员工");
        }else {
            System.out.println("编号t" + "姓名t" + "岗位t" + "上级t" + "编号t" + "入职时间t" + "薪水t" + "补贴t" + "部门t" + "编号");
            System.out.println(employee.getEmpno() + "t" + employee.getEname() + "t" + employee.getJob() + "t" + employee.getMgr() + "t" +
                    employee.getHiredate() + "t" + employee.getSal() + "t" + employee.getComm() + "t" + employee.getDeptno());
        }



    }

    private static void findAll() {
        EmployeeDao ed=new EmployeeDaoImp();
        List list=ed.findAll();
        Employee employee=null;
        System.out.println("编号t" + "姓名t" + "岗位t" + "上级t" + "编号t" + "入职时间t" + "薪水t" + "补贴t" + "部门t" + "编号");
        Iterator it = list.iterator();
        while (it.hasNext()){
            employee=it.next();
            System.out.println(employee.getEmpno() + "t" + employee.getEname() + "t" + employee.getJob() + "t" + employee.getMgr() + "t" +
                    employee.getHiredate() + "t" + employee.getSal() + "t" + employee.getComm() + "t" + employee.getDeptno());
        }
    }
}
 
 
  jdbc.properties配置文件 
 url=jdbc:mysql://127.0.0.1:3306/students_2021?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
user=root
password=1234
driver=com.mysql.cj.jdbc.Driver 
 
 
  log4j.properties配置文件（日志级别可更改下，因为前面有写一些info、debug等，这是后来我设置了的error） 
 log4j.rootLogger=error,logfile

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout

log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=f:/log4j.log
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %l %F %p %m%n 
 
 
  结尾新增设置连接池 
 
package com.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.linkedList;

public class ConnectionPool {

    private static linkedList list = new linkedList();
    static String url="jdbc:mysql://127.0.0.1:3306/students_2021?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    static String user="root";
    static String password="1234";
    static String driver="com.mysql.cj.jdbc.Driver";

    static {
        for (int i = 0; i <3 ; i++) {
           list.add(newConnection());
        }
    }


    public static Connection newConnection(){
        Connection conn=null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static Connection getConnection(){
        if (list.size()>0){
            return list.removeFirst();
        }else return newConnection();
    }
    public static void returnConnection(Connection conn){
         if (list.size()<10){
             list.addLast(conn);
         }else {
             try {
                 conn.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
    }







}
 
 
  DButil修改地方 
 public static Connection getConnection(){

    Connection conn=ConnectionPool.getConnection();
    logger.info("连接数据库成功"+conn);
    return conn;
}
 
 
 四、运行展示 
 
 界面粗糙到不能再粗糙了。。。

java小练习员工管理系统

Java相关栏目本月热门文章