上机指导第七章

àì夳堔傛蜴生んèń 2024-03-30 16:44 64阅读 0赞

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

![c858401ba9de4c69f0d00c7bccee5bb5.png][]

图 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;

[c858401ba9de4c69f0d00c7bccee5bb5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d11237b2013b47ceb2e63783f4ff449c.png

上机指导第七章

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关第二章上机指导

相关上机指导第十二章

相关上机指导第十一章

相关上机指导第十章

相关上机指导第七章

相关上机指导第六章

相关上机指导第四章

相关第七章作业

相关 C#上机第七周任务1 继承

相关第七章数学