花了点时间运用java开发三层架构手撸一个员工信息管理系统,涉及到mysql表查询和登录密码的验证,主要用来练习JDBC操作数据库表
实现的功能:
- 密码登录验证
- 查询所有员工信息
- 添加新员工
- 根据编号修改员工的其他信息
- 根据员工编号删除员工信息
- 查询表中员工总记录数
- 退出系统
项目基本结构:
1、建表(admin和emp2)
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
password VARCHAR(20)
);
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
hiredate DATE,
sal DOUBLE
);
INSERT INTO admin(username,password)VALUES('admin','123');
INSERT INTO admin(username,password)VALUES('doudou','321');
SELECT * FROM admin;
INSERT INTO emp2(ename,job,hiredate,sal)VALUES('张三','人事经理','2021-3-4',19000);
INSERT INTO emp2(ename,job,hiredate,sal)VALUES('李四',' Java开发','2020-4-5',18000);
SELECT * FROM emp2;
SELECT COUNT(*) FROM emp2;
2、entity实体类
Admin.java
package Entity;
public class Admin {
private int id;
private String username;
private String password;
public Admin() {
}
public Admin(String username, String password) {
this.username = username;
this.password = password;
}
public Admin(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Admin{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
Emp.java
package Entity;
import java.util.Date;
public class Emp {
private int eid;
private String ename;
private String job;
private Date hiredate;
private double sal;
public Emp() {
}
public Emp(String ename, String job, Date hiredate, double sal) {
this.ename = ename;
this.job = job;
this.hiredate = hiredate;
this.sal = sal;
}
public Emp(int eid, String ename, String job, Date hiredate, double sal) {
this.eid = eid;
this.ename = ename;
this.job = job;
this.hiredate = hiredate;
this.sal = sal;
}
public int getEid() {
return eid;
}
public void setEid(int eid) {
this.eid = eid;
}
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 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;
}
@Override
public String toString() {
return "Emp{" +
"eid=" + eid +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", hiredate=" + hiredate +
", sal=" + sal +
'}';
}
}
3、Dao层 AdminDaoImpl.java
package Dao.Impl;
import Dao.AdminDao;
import Entity.Admin;
import Utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class AdminDaoImpl implements AdminDao {
@Override
public boolean find(Admin admin) {
Connection conn = JDBCUtils.getConn();
PreparedStatement pst = null;
ResultSet rs = null;
String sql = "select * from admin";
boolean flag = false;
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
while (rs.next()){
int id = rs.getInt(1);
String username = rs.getString(2);
String password = rs.getString(3);
if (admin.getUsername().equals(username) && admin.getPassword().equals(password)){
flag = true;
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs,pst,conn);
}
return flag;
}
}
EmpDaoImpl.java
package Dao.Impl;
import Dao.EmpDao;
import Entity.Emp;
import Utils.DateUtils;
import Utils.JDBCUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class EmpDaoImpl implements EmpDao {
Connection conn = null;
PreparedStatement pst = null;
//返回 增删改 影响的行数
int i = 0;
//添加新员工
@Override
public int add(Emp emp) {
conn = JDBCUtils.getConn();
String sql = "insert into emp2(ename,job,hiredate,sal) values(?,?,?,?)";
try {
pst = conn.prepareStatement(sql);
pst.setString(1,emp.getEname());
pst.setString(2,emp.getJob());
pst.setDate(3, DateUtils.dateToSqlDate(emp.getHiredate()));
pst.setDouble(4,emp.getSal());
i = pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(null,pst,conn);
}
return i;
}
//根据员工编号删除员工信息
@Override
public int del(int eid) {
conn = JDBCUtils.getConn();
String sql = "delete from emp2 where eid = ?";
try {
PreparedStatement pst = conn.prepareStatement(sql);
pst.setInt(1,eid);
i = pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(null,pst,conn);
}
return i;
}
//根据员工编号修改员工的其他信息
@Override
public int update(Emp emp) {
conn = JDBCUtils.getConn();
String sql = "update emp2 set ename = ?,job = ?,hiredate = ?,sal = ? where eid = ?";
try {
PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1,emp.getEname());
pst.setString(2,emp.getJob());
pst.setDate(3,DateUtils.dateToSqlDate(emp.getHiredate()));
pst.setDouble(4,emp.getSal());
pst.setInt(5,emp.getEid());
int i = pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(null,pst,conn);
}
return i;
}
//根据员工编号查询
@Override
public Emp findByEid(int eid) {
Emp emp = null;
conn = JDBCUtils.getConn();
String sql = "select * from emp2 where eid = ?";
try {
PreparedStatement pst = conn.prepareStatement(sql);
pst.setInt(1,eid);
ResultSet rs = pst.executeQuery();
while (rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
String job = rs.getString(3);
Date date = rs.getDate(4);
double sal = rs.getDouble(5);
emp = new Emp(id,name,job,date,sal);
}
} catch (SQLException e) {
e.printStackTrace();
}
return emp;
}
//查询所有员工信息
@Override
public List<Emp> findAll() {
Emp emp = null;
List<Emp> list = new ArrayList<>();
conn = JDBCUtils.getConn();
String sql = "select * from emp2";
try {
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
while (rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
String job = rs.getString(3);
Date date = rs.getDate(4);
double sal = rs.getDouble(5);
emp = new Emp(id,name,job,date,sal);
list.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//查询表中员工总记录数
@Override
public int findCount() {
conn = JDBCUtils.getConn();
String sql = "select count(*) as jilu from emp2";
int jilu = 0;
try {
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
while (rs.next()){
jilu = rs.getInt("jilu");
}
} catch (SQLException e) {
e.printStackTrace();
}
return jilu;
}
}
4、Service层 AdminService.java
package Service.Impl;
import Dao.AdminDao;
import Dao.Impl.AdminDaoImpl;
import Entity.Admin;
import Service.AdminService;
public class AdminServiceImpl implements AdminService {
public boolean find(Admin admin){
AdminDao adminDao = new AdminDaoImpl();
boolean b = adminDao.find(admin);
return b;
}
}
EmpService.java
package Service.Impl;
import Dao.EmpDao;
import Dao.Impl.EmpDaoImpl;
import Entity.Emp;
import Service.EmpService;
import java.util.List;
public class EmpServicempl implements EmpService {
@Override
public int add(Emp emp) {
EmpDao empDao = new EmpDaoImpl();
int i = empDao.add(emp);
return i;
}
@Override
public int del(int eid) {
EmpDao empDao = new EmpDaoImpl();
int i = empDao.del(eid);
return i;
}
@Override
public int update(Emp emp) {
EmpDao empDao = new EmpDaoImpl();
int i = empDao.update(emp);
return i;
}
@Override
public Emp findByEid(int eid) {
EmpDao empDao = new EmpDaoImpl();
Emp emp = empDao.findByEid(eid);
return emp;
}
@Override
public List<Emp> findAll() {
EmpDao empDao = new EmpDaoImpl();
List<Emp> all = empDao.findAll();
return all;
}
@Override
public int findCount() {
EmpDao empDao = new EmpDaoImpl();
int count = empDao.findCount();
return count;
}
}
4、View层 test.java
package View;
import Entity.Admin;
import Entity.Emp;
import Service.AdminService;
import Service.EmpService;
import Service.Impl.AdminServiceImpl;
import Service.Impl.EmpServicempl;
import Utils.DateUtils;
import java.util.List;
import java.util.Scanner;
public class test {
private static EmpService es = new EmpServicempl();
private static Scanner scanner = new Scanner(System.in);
public static void main(String[] args) {
System.out.println("**************登录验证***************");
System.out.print("请输入用户名:");
String uname = scanner.next();
System.out.print("请输入密码:");
String upwd = scanner.next();
Admin admin = new Admin(uname,upwd);
AdminService adminService = new AdminServiceImpl();
boolean b = adminService.find(admin);
if (b){
System.out.println("恭喜你,登录成功");
int num = 1;
while (true){
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("0、退出系统");
System.out.print("请选择:");
num = scanner.nextInt();
switch (num){
case 1:
System.out.println("*****************查询所有员工信息*******************");
findAll();
break;
case 2:
System.out.println("*****************根据员工编号查询*******************");
findById();
break;
case 3:
System.out.println("********************添加新员工*********************");
add();
break;
case 4:
System.out.println("*****************根据编号修改员工的其他信息************");
update();
break;
case 5:
System.out.println("*****************根据员工编号删除员工信息*************");
delete();
break;
case 6:
System.out.println("*******************查询表中员工总记录数****************");
findCount();
break;
case 0:
System.out.println("谢谢使用!正在退出系统......");
System.exit(0);
default:
System.out.println("请输入正确选项!!!");
}
}
}else {
System.out.println("登录失败,请重新登录!");
}
}
public static void findAll(){
List<Emp> list = es.findAll();
for (Emp emp : list) {
System.out.println(emp);
}
}
public static void findById(){
System.out.print("请输入员工编号:");
int id = scanner.nextInt();
Emp emp = es.findByEid(id);
System.out.println(emp);
}
public static void add(){
System.out.print("请输入员工姓名:");
String name = scanner.next();
System.out.print("请输入职业:");
String job = scanner.next();
System.out.print("请输入入职时间:");
String hiredate = scanner.next();
System.out.print("请输入薪资:");
double sal = scanner.nextDouble();
Emp emp1 = new Emp(name,job, DateUtils.strToDate(hiredate),sal);
int i = es.add(emp1);
if (i == 1){
System.out.println("员工信息添加成功!!!");
}else {
System.out.println("员工信息添加失败!!!");
}
}
public static void update(){
System.out.print("请输入需要修改员工信息的编号:");
int numm = scanner.nextInt();
Emp byEid = es.findByEid(numm);
System.out.print("请输入员工姓名:");
String name2 = scanner.next();
byEid.setEname(name2);
System.out.print("请输入职业:");
String job2 = scanner.next();
byEid.setJob(job2);
System.out.print("请输入入职时间:");
String hiredate2 = scanner.next();
byEid.setHiredate(DateUtils.strToUtil(hiredate2));
System.out.print("请输入薪资:");
double sal2 = scanner.nextDouble();
byEid.setSal(sal2);
es.update(byEid);
}
public static void delete(){
System.out.print("请输入需要修改员工信息的编号:");
int nummm = scanner.nextInt();
int del = es.del(nummm);
if (del == 1){
System.out.println("员工信息删除成功!!!");
}else {
System.out.println("员工信息删除失败!!!");
}
}
public static void findCount(){
int count = es.findCount();
System.out.println("emp表中一共有" + count + "条记录");
}
}
5、两个工具类
JDBCUtils工具类用于方便连接数据库,关闭连接
DateUtils工具类方便进行日期格式的转换,调用工具类可以直接String类型字符串转换成java.util.Date类型或者java.sql.Date类型
JDBCUtils.java
package Utils;
import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
//读取properties配置文件获取创建连接需要参数
private static Properties properties = new Properties();
// private static ThreadLocal<Connection> threadLocal = new InheritableThreadLocal<>();
//加载JDBC驱动
static {
try {
properties.load(new FileInputStream("src/db.properties"));
Class.forName(properties.getProperty("driver"));
} catch (Exception e) {
e.printStackTrace();
}
}
//获取数据库连接方法
public static Connection getConn() {
/*Connection conn = threadLocal.get();
try {
if (conn == null){
conn = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
threadLocal.set(conn);
}
} catch (SQLException e) {
e.printStackTrace();
}
return conn;*/
Connection conn = null;
try {
conn = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//关闭连接资源方法
public static void close(ResultSet rs, PreparedStatement pst, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
DateUtils.java
package Utils;
import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
public class DateUtils {
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//日期 string---->java.util.Date
public static java.util.Date strToUtil(String str){
java.util.Date date = null;
try {
date = sdf.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return date;
}
//日期 string----->java.sql.Date
public static Date strToDate(String str){
Date date = null;
try {
java.util.Date parse = sdf.parse(str);
date = new Date(parse.getTime());
} catch (ParseException e) {
e.printStackTrace();
}
return date;
}
//日期 java.util.Date----->java.sql.Date
public static Date dateToSqlDate(java.util.Date date){
Date date1 = new Date(date.getTime());
return date1;
}
}
6、db.properties配置文件
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/myschool
username = root
password = 123456
运行界面:
随手写的一个控制台界面员工信息管理系统,如有错误欢迎大佬指正!