上机指导第七章

已知Oracle数据库中员工表（employee）的数据如图7-20所示。

图 7-20 员工表

使用 JDBC技术编程实现以下功能：

（1）添加员工信息到表中；

（2）修改员工的基本信息；

（3）根据编号删除员工；

（4）按照员工工作种类进行员工信息查询。

答：本练习需要用到以下Sql脚本。

Sql脚本：

create table EMPLOYEE

(

EMP_ID VARCHAR2(5),

EMP_NAME VARCHAR2(20),

JOB VARCHAR2(10),

SALARY NUMBER(7,2),

DEPT VARCHAR2(2)

)

;

insert into EMPLOYEE (EMP_ID, EMP_NAME, JOB, SALARY, DEPT)

values ('1', '王楠', 'clerk', 4300, '10');

insert into EMPLOYEE (EMP_ID, EMP_NAME, JOB, SALARY, DEPT)

values ('2', '张静', 'clerk', 4300, '10');

insert into EMPLOYEE (EMP_ID, EMP_NAME, JOB, SALARY, DEPT)

values ('3', '李刚', 'manager', 5000, '20');

insert into EMPLOYEE (EMP_ID, EMP_NAME, JOB, SALARY, DEPT)

values ('4', '马明', 'manager', 5000, '20');

commit;

package common.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/**

* 工具类

*

*/

public class ConnectionPool {

public static Connection getConn() {

Connection conn = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

return conn;

}

public static void close(Statement stmt, Connection conn) {

try {

if (stmt != null) {

stmt.close();

}

if (conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

public static void close(Statement stmt, ResultSet rs, Connection conn) {

try {

if (stmt != null) {

stmt.close();

stmt = null;

}

if (rs != null) {

rs.close();

}

if (conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

package dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import common.util.ConnectionPool;

import entity.Employee;

/**

* 员工维护

*/

public class EmployeeDao {

private Connection conn;

private PreparedStatement stmt;

private ResultSet result;

/**

* 添加新员工信息

*/

public void insertEmployee(Employee emp) {

try {

conn = ConnectionPool.getConn();

stmt = conn

.prepareStatement("insert into employee(emp_id,emp_name,job,salary,dept) values(?,?,?,?,?)");

stmt.setString(1, emp.getEmpId());

stmt.setString(2, emp.getEmpName());

stmt.setString(3, emp.getJob());

stmt.setDouble(4, emp.getSalary());

stmt.setString(5, emp.getDept());

// 使用prepareStatement

int cout = stmt.executeUpdate();

// 处理结果

if (cout >= 1) {

System.out.println("添加成功");

} else {

System.out.println("添加失败");

}

} catch (SQLException ex) {

ex.printStackTrace();

} finally {

ConnectionPool.close(stmt, conn);

}

}

/**

* 修改员工信息

*/

public void updateEmployee(Employee emp) {

try {

conn = ConnectionPool.getConn();

stmt = conn

.prepareStatement("update employee set emp_name=?,job=?,salary=?,dept=? where emp_id=?");

stmt.setString(1, emp.getEmpName());

stmt.setString(2, emp.getJob());

stmt.setDouble(3, emp.getSalary());

stmt.setString(4, emp.getDept());

stmt.setString(5, emp.getEmpId());

// 使用prepareStatement

int cout = stmt.executeUpdate();

// 处理结果

if (cout >= 1) {

System.out.println("修改成功");

} else {

System.out.println("修改失败");

}

} catch (SQLException ex) {

ex.printStackTrace();

} finally {

ConnectionPool.close(stmt, conn);

}

}

/**

* 根据员工编号删除员工信息

*/

public void deleteEmployeeById(String empId) {

try {

conn = ConnectionPool.getConn();

stmt = conn

.prepareStatement("delete from employee where emp_id=?");

stmt.setString(1, empId);

// 使用prepareStatement

int cout = stmt.executeUpdate();

// 处理结果

if (cout >= 1) {

System.out.println("删除成功");

} else {

System.out.println("删除失败");

}

} catch (SQLException ex) {

ex.printStackTrace();

} finally {

ConnectionPool.close(stmt, conn);

}

}

/**

* 按照员工工作种类进行员工信息查询

*/

public void queryEmpByJob(String job){

//1、加载驱动

try {

conn = ConnectionPool.getConn();

stmt = conn

.prepareStatement("select * from employee where job=?");

stmt.setString(1, job);

result = stmt.executeQuery();

//处理结果集

System.out.println("员工编号：\t\t员工姓名：\t\t工作：\t\t薪水：\t\t部门编号：");

while(result.next()){

System.out.println(result.getString("emp_id")+"\t\t"+result.getString("emp_name")+"\t\t"+result.getString("job")+"\t\t"+result.getString("salary")+"\t\t"+result.getString("dept"));

}

} catch(SQLException e){

e.printStackTrace();

} finally{

//释放资源

ConnectionPool.close(stmt, result, conn);

}

}

public static void main(String[] args) {

// 模拟新员工信息添加

Employee emp = new Employee();

emp.setEmpId("5");

emp.setEmpName("tom");

emp.setJob("clerk");

emp.setSalary(9000.00);

emp.setDept("20");

EmployeeDao empDao = new EmployeeDao();

empDao.insertEmployee(emp);

//模拟修改员工

Employee emp1 = new Employee();

emp1.setEmpId("5");

emp1.setEmpName("jizhh");

emp1.setJob("clerk");

emp1.setSalary(8000.00);

emp1.setDept("10");

empDao.updateEmployee(emp1);

//模拟删除编号为5的员工

empDao.deleteEmployeeById("5");

//模拟查询工作类别为clerk的员工信息

empDao.queryEmpByJob("clerk");

}

}

package entity;

/**

* 员工信息

*

*/

public class Employee {

/**

* 编号

*/

private String empId;

/**

* 姓名

*/

private String empName;

/**

* 工作

*/

private String job;

/**

* 薪水

*/

private double salary;

/**

* 部门编号

*/

private String dept;

public Employee() {

super();

}

public Employee(String empId, String empName, String job, double salary,

String dept) {

this.empId = empId;

this.empName = empName;

this.job = job;

this.salary = salary;

this.dept = dept;

}

public String getEmpId() {

return empId;

}

public void setEmpId(String empId) {

this.empId = empId;

}

public String getEmpName() {

return empName;

}

public void setEmpName(String empName) {

this.empName = empName;

}

public String getJob() {

return job;

}

public void setJob(String job) {

this.job = job;

}

public double getSalary() {

return salary;

}

public void setSalary(double salary) {

this.salary = salary;

}

public String getDept() {

return dept;

}

public void setDept(String dept) {

this.dept = dept;

}

}