JAVA JDBC实现的职工管理系统（Console版）（期中java课程设计）

                     

一.说在前面的话

        不知不觉就到了大三的期中了，java也基本算是入了门。这不，老师布置了java的课程设计。（废话......）

涉及的java主要知识点：java基础知识，接口，JDBC。

三个package：ui，dao，vo（自行脑补包名，由于专业的特殊性包名不能随意暴露，您懂得）。分别代表了UI层、数据库操作层（包括链接和断开）、职工信息（也就是Employee类）。

二.上代码

1.职工信息类（package：com.XXX.vo。包含Employee.class）

package com.XXX.vo;

public class Employee {
	private int eId;    //职工id号
	private String eName;//职工姓名
	private String eIdno;//职工身份证号
	private long eTel;   //职工电话
	private String eDept;//职工部门
	private String eJob; //职工职位
	private String eMail;//职工email
	public int getEId() {
		return eId;
	}
	public void setEId(int id) {
		eId = id;
	}
	public String getEName() {
		return eName;
	}
	public void setEName(String name) {
		eName = name;
	}
	public String getEIdno() {
		return eIdno;
	}
	public void setEIdno(String idno) {
		eIdno = idno;
	}
	public long getETel() {
		return eTel;
	}
	public void setETel(int tel) {
		eTel = tel;
	}
	public String getEDept() {
		return eDept;
	}
	public void setEDept(String dept) {
		eDept = dept;
	}
	public String getEJob() {
		return eJob;
	}
	public void setEJob(String job) {
		eJob = job;
	}
	public String getEMail() {
		return eMail;
	}
	public void setEMail(String mail) {
		eMail = mail;
	}
	
	/**
	 * 职工类的构造函数
	 * @param eId
	 * @param eName
	 * @param eIdno
	 * @param eTel
	 * @param eDept
	 * @param eJob
	 * @param eMail
	 */
	public Employee(int eId, String eName, String eIdno, long eTel, String eDept,
			String eJob, String eMail) {
		super();
		this.eId = eId;
		this.eName = eName;
		this.eIdno = eIdno;
		this.eTel = eTel;
		this.eDept = eDept;
		this.eJob = eJob;
		this.eMail = eMail;
	}
	/**
	 * 职工类只有一个参数的构造函数
	 * @param eId
	 */
	public Employee(int eId)//eId的构造函数
	{
		this.eId = eId;
	}
}

3.UI层（package：com.XXX.ui。包含：MainUI.java  EmpUI.java）

MainUI.java

package com.XXX.ui;

import java.util.Scanner;

public class MainUI {
	public void menu() {
		System.out.println("职工基础信息管理（employee-crud）");
		System.out.println("");
		System.out.println("请输入1-5进行操作");
		System.out.println("1.添加新员工");
		System.out.println("2.检索员工信息");
		System.out.println("3.更新员工信息");
		System.out.println("4.删除员工信息");
		System.out.println("5.退出系统");
		System.out.println("请输入您的选择：");
	}
	/*
	 * 入口main函数
	 */
	public static void main(String[] args) {

		while (true) {    
			new MainUI().menu();
			try {               
				Scanner scan = new Scanner(System.in);
				int i;
				i = scan.nextInt();
				switch (i) {
				case 1:
					new EmpUI().insertUi();
					break;
				case 2:
					new EmpUI().selectUi();
					break;
				case 3:
					new EmpUI().updateUi();
					break;
				case 4:
					new EmpUI().deleteUi();
					break;
				case 5:
					System.out.println("本系统已退出");
					System.exit(0);
					break;
				default:
					System.out.println("请输入数字1-5");
					break;
				}
			} catch (java.util.InputMismatchException e) {
				System.out.println("请输入数字");
			}

		}
	}
}

EmpUI.java

package com.XXX.ui;

import java.util.Scanner;
import com.XXX.dao.EmployeeDaoImp;
import com.XXX.vo.Employee;

public class EmpUI {
	/**
	 * 更新员工信息的操作界面
	 */
	public void updateUi() {
		System.out.println("员工信息更新界面");

		// 从键盘输入要更新的员工编号，以及更新后的其他六项信息
		System.out.println("输入要更新的员工编号：");
		Scanner scan = new Scanner(System.in);
		int eid = scan.nextInt();
		boolean bool = new EmployeeDaoImp().check(eid);// 判断是否存在此eid，若存在，则执行更新，否则提示警告
		if (bool) {
			System.out.println("输入要更新的员工姓名：");
			String ename = scan.next();
			System.out.println("输入要更新的员工身份证号：");
			String eidno = scan.next();
			if (!(new EmployeeDaoImp().checkIdno(eidno))) {
				System.out.println("输入要更新的员工电话：");
				long etel = scan.nextLong();
				System.out.println("输入要更新的员工部门：");
				String edept = scan.next();
				System.out.println("输入要更新的员工职位：");
				String ejob = scan.next();
				System.out.println("输入要更新的员工email：");
				String email = scan.next();
				Employee emp = new Employee(eid, ename, eidno, etel, edept, ejob,email);
				boolean bool2 = new EmployeeDaoImp().updateEmp(emp);
				if (bool2) {
					System.out.println("更新成功");
				} else {
					System.out.println("更新失败");
				}
			}else{
				System.out.println("输入身份证号与已知员工省份证号重复！");
			}
			
		} else {
			System.out.println("此员工不存在");
		}
	}

	/**
	 * 删除员工的操作界面
	 */
	public void deleteUi() {
		System.out.println("输入要删除的员工编号：");
		Scanner scan = new Scanner(System.in);
		int eid = scan.nextInt();
		boolean bool = new EmployeeDaoImp().check(eid);
		if (bool) {
			boolean bool2 = new EmployeeDaoImp().deleteEmp(eid);
			if (bool2) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} else {
			System.out.println("此员工不存在");
		}
	}

	/**
	 * 添加员工的界面
	 */
	public void insertUi() {

		System.out.println("输入要插入的员工编号：");
		Scanner scan = new Scanner(System.in);
		int eid = scan.nextInt();
		boolean bool = new EmployeeDaoImp().check(eid);
		if (bool) {
			System.out.println("此员工编号已经存在");
		} else {
			System.out.println("输入要插入的员工姓名：");
			String ename = scan.next();
			System.out.println("输入要插入的员工身份证号：");
			String eidno = scan.next();
			if (!(new EmployeeDaoImp().checkIdno(eidno))){
				System.out.println("输入要插入的员工电话：");
				long etel = scan.nextLong();
				System.out.println("输入要插入的员工部门：");
				String edept = scan.next();
				System.out.println("输入要插入的员工职位：");
				String ejob = scan.next();
				System.out.println("输入要插入的员工email：");
				String email = scan.next();

				Employee emp = new Employee(eid, ename, eidno, etel, edept, ejob,email);

				boolean bool2 = new EmployeeDaoImp().insertEmp(emp);
				if (bool2) {
					System.out.println("插入成功");
				} else {
					System.out.println("插入失败");
				}
			}else{
				System.out.println("插入的身份证号与已知的身份证号重复！");
			}
			
		}
	}

	/**
	 * 检索员工信息的界面
	 */
	public void selectUi() {
		System.out.println("1.显示全部职工信息");
		System.out.println("2.根据id查询职工信息");
		Scanner scan1 = new Scanner(System.in);
		int i = scan1.nextInt();
		switch (i) {
		case 1:
			boolean bool1 =  new EmployeeDaoImp().showAll();
			if (!bool1) {
				System.out.println("显示全部信息失败！");
			}
			break;
		case 2:
			System.out.println("请输入要查询的员工号");
			Scanner scan = new Scanner(System.in);
			int eid = scan.nextInt();
			boolean bool = new EmployeeDaoImp().check(eid);
			if (bool) {
				new EmployeeDaoImp().selectEmp(eid);
				
			} else {
				System.out.println("该员工不存在");
			}
		default:
			break;
		}	
	}
	
}

从MainUI里面的switch里面跳转到EmpUI里面的增删改查的UI界面。

3.数据库操作层（package：com.XXX.dao 包含：DBConnectonUtil.java EmployeeImp.java EmployeeImpDao.java）

DBConnectionUtil.java

package com.XXX.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class DBConnectionUtil {
	public Connection getConnection(){
		Connection connection = null;
		String username = "scott";
		String password = "tiger";
        String url = "jdbc:oracle:thin:@localhost:1521:XXX";//XXX自行替换为自己的
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection(url,username,password);
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	public void release( ResultSet resultSet,PreparedStatement preparedStatement,Connection connection){
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
	}
}

EmployeeImp.java

package com.XXX.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import com.inspur.vo.Employee;
/*
 * 实现IEmployeeDao接口
 * 实现IEmployeeDao接口已有7个函数接口，新增两个private函数：drawTable()，pad()；
 */
public class EmployeeDaoImp implements IEmployeeDao{
	private static final int COLUMN_WIDTH = 17;

	@Override
	public boolean updateEmp(Employee employee) {

		PreparedStatement preparedStatement = null;
		Connection connection = new DBConnectionUtil().getConnection();
		String sql = "update employee set ename=?,eidno=?,etel=?,edept=?,ejob=?,email=? where eid=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1,employee.getEName());
			preparedStatement.setString(2, employee.getEIdno());
			preparedStatement.setLong(3, employee.getETel());
			preparedStatement.setString(4, employee.getEDept());
			preparedStatement.setString(5,employee.getEJob());
			preparedStatement.setString(6, employee.getEMail());
			preparedStatement.setInt(7, employee.getEId());
			int flag = preparedStatement.executeUpdate();
			if (flag != 0)
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			new DBConnectionUtil().release(null, preparedStatement, connection);
		}
		return false;
	}
	/*
	 * 删除学生信息（非 Javadoc）
	 * @see com.inspur.dao.IEmployeeDao#deleteEmp(int)
	 */
	@Override
	public boolean deleteEmp(int eid) {
		PreparedStatement preparedStatement = null;
		Connection connection = new DBConnectionUtil().getConnection();
		String sql = "delete from employee where eid=?";
		try{
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1,eid);
			int flag = preparedStatement.executeUpdate();
			if (flag != 0)
				return true;
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			new DBConnectionUtil().release(null, preparedStatement, connection);
		}
		return false;
	}
	/*
	 * 插入学生信息（非 Javadoc）
	 * @see com.inspur.dao.IEmployeeDao#insertEmp(com.inspur.pojo.Employee)
	 */
	@Override
	public boolean insertEmp(Employee employee) {

		PreparedStatement preparedStatement = null;
		Connection connection = new DBConnectionUtil().getConnection();
		String sql = "insert into employee (eid,ename,eidno,etel,edept,ejob,email)values(?,?,?,?,?,?,?)";
		try{
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, employee.getEId());
			preparedStatement.setString(2, employee.getEName());
			preparedStatement.setString(3, employee.getEIdno());
			preparedStatement.setLong(4, employee.getETel());
			preparedStatement.setString(5, employee.getEDept());
			preparedStatement.setString(6, employee.getEJob());
			preparedStatement.setString(7,employee.getEMail());
			int flag = preparedStatement.executeUpdate();
			if (flag != 0)
				return true;
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			new DBConnectionUtil().release(null, preparedStatement, connection);
		}
		return false;
	}
	/*
	 * 查询学生信息（非 Javadoc）
	 * @see com.inspur.dao.IEmployeeDao#selectEmp(int)
	 */
	@Override
	public Employee selectEmp(int eid) {

		Employee employee = null;
		PreparedStatement preparedStatement = null;
		ResultSet result = null;
		Connection connection = new DBConnectionUtil().getConnection();
		String sql = "select * from employee where eid=?";
		try {
			preparedStatement= connection.prepareStatement(sql);
			preparedStatement.setInt(1, eid);
			result = preparedStatement.executeQuery();
			drawTable(result);
			while (result.next()) {
				int eId=result.getInt(1);
				String ename=result.getString(2);
				String eidno=result.getString(3);
				long etel=result.getLong(4);
				String edept=result.getString(5);
				String ejob=result.getString(6);
				String email=result.getString(7);
				employee=new Employee(eId,ename,eidno,etel,edept,ejob,email);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}finally{
			new DBConnectionUtil().release(result, preparedStatement, connection);
		}
		
		return employee;
	}
	/*
	 * 核对eid看是否有重复（非 Javadoc）
	 * @see com.inspur.dao.IEmployeeDao#check(int)
	 */
	@Override
	public boolean check(int eid) {

		PreparedStatement preparedStatement = null;
		Connection connection = null;
		ResultSet result =null;
		String sql = "select * from employee where eid=?";
		try{
			connection = new DBConnectionUtil().getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, eid);
			result = preparedStatement.executeQuery();
			while(result.next()){
				return true;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			new DBConnectionUtil().release(result, preparedStatement, connection);
		}
		return false;
	}
	@Override
	public boolean showAll() {

		Connection connection = null;
		java.sql.Statement statement = null;
		ResultSet result = null;
		String sql = "select * from employee";
		try {
			connection = new DBConnectionUtil().getConnection();
			statement = connection.createStatement();
			result = statement.executeQuery(sql);//直接执行sql语句
			drawTable(result);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			new DBConnectionUtil().release(result, null, connection);
		}
		return false;
	}
	
	/*
	 * 核对身份证信息是否与已有的员工身份证信息重复（非 Javadoc）
	 * @see com.inspur.dao.IEmployeeDao#checkIdno(java.lang.String)
	 */
	@Override
	public boolean checkIdno(String eidno) {
		PreparedStatement preparedStatement = null;
		Connection connection = new DBConnectionUtil().getConnection();
		String sql = "select * from employee where eidno=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, eidno);
			ResultSet result = preparedStatement.executeQuery();
			while(result.next()){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return false;
	}
	/*
	 * 根据传来的结果集绘制图表结构
	 */
	private void drawTable(ResultSet result){//函数参数为传来的结果集
		
		try{
			ResultSetMetaData resultSetMetaData = result.getMetaData();//读取元数据。
			int columnCount = resultSetMetaData.getColumnCount();
			int length = columnCount * COLUMN_WIDTH+6;//表格的宽度
			//绘制表格结构
			StringBuilder sb1 = new StringBuilder();
			for (int i = 0; i < length; i++) {
				sb1.append("=");
			}
			System.out.println();
			System.out.println(sb1.toString());
			for (int i = 0; i < columnCount; i++) {
				System.out.print(pad(resultSetMetaData.getColumnName(i + 1))+"|");
			}
			System.out.println();
			System.out.println(sb1.toString());
			while (result.next()) {
				String[] row = new String[columnCount];
				for (int i = 0; i < columnCount; i++) {
					row[i] = result.getString(i + 1);
					System.out.print(pad(row[i])+"|");
				}
				System.out.println();
			}
			System.out.println(sb1.toString());
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	/*
	 * 在s后面添加空格，总是使其列宽的长度为25.
	 */
	private String pad(String s){
		int padCount = COLUMN_WIDTH - s.length();
		StringBuilder sb = new StringBuilder();
		sb.append(s);
		for (int i = 0; i < padCount; i++) {
			sb.append(" ");
		}
		return sb.toString();
	}
}

其中我写了个drawTable（），用于根据结果集绘制表格结构，类似于在查询分析器里执行select语句得到的图表结构。里面有些许的算法，就是调用了我写的pad（），也就是先规定好每一列的宽度，然后减去读取元数据（比如获得表的某个列名）得到的长度，这个长度也就是在获得字符串后面加的“=”（空格）数，使得表格可以整齐的展现出来。其实主要目的是想用到从结果集ResultSet读取元数据的知识点。

EmployeeDao.java

<span style="font-family:Microsoft YaHei;font-size:18px;">package com.XXX.dao;
import com.XXX.vo.Employee;

/* 接口
 * 函数个数：7
 * boolean updateEmp()
 * boolean deleteEmp()
 * boolean insertEmp()
 * Employee selectEmp()
 * boolean check()
 */
public interface IEmployeeDao {
	public boolean updateEmp(Employee employee);//更新
	public boolean deleteEmp(int eid);//删除
	public boolean insertEmp(Employee employee);//插入
	public Employee selectEmp(int eid);//查询
	public boolean check(int eid);//核对id（员工id工号）
	public boolean showAll();//显示全部表
	public boolean checkIdno(String eidno);//核对idno（员工身份证号）
	
}
</span>

定义一个接口。

三.说在后面的话

1.先上几张图片

2.关于一些配置

数据库用的Oracle数据库，在Eclipse或者IDEA编写程序时，需引入Oracle数据库的jar包。