SQL 员工表操作：JDBC 实现查询、入职、调整薪资、离职和分页查询
SQL 员工表操作：JDBC 实现查询、入职、调整薪资、离职和分页查询
本文将展示如何使用 JDBC 对一个名为 'employee' 的员工表进行增删改查操作。我们将通过几个示例代码展示具体操作步骤，包括查询部门员工、办理员工入职、调整员工薪资、办理员工离职和分页查询员工。
员工表结构：
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`eno` int(0) NOT NULL,
`ename` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`salary` float(10, 2) NOT NULL,
`dname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`hiredate` date NULL DEFAULT NULL,
PRIMARY KEY (`eno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (44, 'dd', 8956.36, '开发部', '2023-02-03');
INSERT INTO `employee` VALUES (186, '小美', 4589.69, '销售部', '2023-01-23');
INSERT INTO `employee` VALUES (888, 'mm', 23456.00, 'kaifabu', '2012-11-25');
INSERT INTO `employee` VALUES (1000, '员工1000', 5000.00, '市场部', '1992-03-04');
INSERT INTO `employee` VALUES (1001, '员工1001', 3500.00, '市场部', '1988-02-18');
INSERT INTO `employee` VALUES (1002, '员工1002', 4000.00, '市场部', '1996-02-01');
INSERT INTO `employee` VALUES (1003, '员工1003', 4000.00, '市场部', '2001-08-21');
INSERT INTO `employee` VALUES (1004, '员工1004', 4000.00, '市场部', '2003-01-02');
INSERT INTO `employee` VALUES (1005, '员工1005', 4000.00, '市场部', '1996-07-28');
INSERT INTO `employee` VALUES (1006, '员工1006', 4000.00, '市场部', '1999-12-30');
INSERT INTO `employee` VALUES (1007, '员工1007', 4000.00, '市场部', '2009-05-30');
INSERT INTO `employee` VALUES (1008, '员工1008', 4000.00, '市场部', '1984-05-30');
INSERT INTO `employee` VALUES (1009, '员工1009', 4000.00, '市场部', '2004-05-07');
INSERT INTO `employee` VALUES (3308, '张三', 6000.00, '研发部', '2011-05-08');
INSERT INTO `employee` VALUES (3420, '李四', 8700.00, '研发部', '2006-11-11');
SET FOREIGN_KEY_CHECKS = 1;
1. 查询部门员工
import java.sql.*;
public class EmployeeQuery {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 1. 加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 建立数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "username", "password");
// 3. 创建Statement对象
stmt = conn.createStatement();
// 4. 执行SQL查询语句
String sql = "SELECT * FROM employee WHERE dname = '市场部'";
rs = stmt.executeQuery(sql);
// 5. 处理查询结果集
while (rs.next()) {
int eno = rs.getInt("eno");
String ename = rs.getString("ename");
float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
Date hiredate = rs.getDate("hiredate");
System.out.println("员工编号: " + eno);
System.out.println("员工姓名: " + ename);
System.out.println("员工薪资: " + salary);
System.out.println("所属部门: " + dname);
System.out.println("入职日期: " + hiredate);
System.out.println("---------------------");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6. 关闭资源
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2. 办理员工入职
import java.sql.*;
public class EmployeeEntry {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1. 加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 建立数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "username", "password");
// 3. 创建PreparedStatement对象
String sql = "INSERT INTO employee (eno, ename, salary, dname, hiredate) VALUES (?, ?, ?, ?, ?)";
pstmt = conn.prepareStatement(sql);
// 4. 设置参数
pstmt.setInt(1, 1010);
pstmt.setString(2, "员工1010");
pstmt.setFloat(3, 4500.00);
pstmt.setString(4, "销售部");
pstmt.setDate(5, Date.valueOf("2023-02-10"));
// 5. 执行SQL语句
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("员工入职办理成功");
} else {
System.out.println("员工入职办理失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6. 关闭资源
try {
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3. 调整员工薪资
import java.sql.*;
public class EmployeeSalaryAdjustment {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1. 加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 建立数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "username", "password");
// 3. 创建PreparedStatement对象
String sql = "UPDATE employee SET salary = ? WHERE eno = ?";
pstmt = conn.prepareStatement(sql);
// 4. 设置参数
pstmt.setFloat(1, 5500.00);
pstmt.setInt(2, 1001);
// 5. 执行SQL语句
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("员工薪资调整成功");
} else {
System.out.println("员工薪资调整失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6. 关闭资源
try {
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4. 员工离职
import java.sql.*;
public class EmployeeResignation {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1. 加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 建立数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "username", "password");
// 3. 创建PreparedStatement对象
String sql = "DELETE FROM employee WHERE eno = ?";
pstmt = conn.prepareStatement(sql);
// 4. 设置参数
pstmt.setInt(1, 1009);
// 5. 执行SQL语句
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("员工离职办理成功");
} else {
System.out.println("员工离职办理失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6. 关闭资源
try {
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
5. 分页查询员工
import java.sql.*;
public class EmployeePagination {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// 1. 加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 建立数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "username", "password");
// 3. 创建PreparedStatement对象
String sql = "SELECT * FROM employee LIMIT ?, ?";
pstmt = conn.prepareStatement(sql);
// 4. 设置参数
int offset = 0; // 开始位置
int rowCount = 5; // 每页行数
pstmt.setInt(1, offset);
pstmt.setInt(2, rowCount);
// 5. 执行SQL查询语句
rs = pstmt.executeQuery();
// 6. 处理查询结果集
while (rs.next()) {
int eno = rs.getInt("eno");
String ename = rs.getString("ename");
float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
Date hiredate = rs.getDate("hiredate");
System.out.println("员工编号: " + eno);
System.out.println("员工姓名: " + ename);
System.out.println("员工薪资: " + salary);
System.out.println("所属部门: " + dname);
System.out.println("入职日期: " + hiredate);
System.out.println("---------------------");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 7. 关闭资源
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
注意：
- 请将代码中的
database_name、username和password替换为您的实际数据库名称、用户名和密码。 - 以上代码示例仅供参考，您需要根据实际情况进行调整。
- 在实际应用中，建议使用 PreparedStatement 来防止 SQL 注入攻击。
- 建议在 finally 代码块中关闭所有连接和资源，以释放数据库资源。
通过以上代码示例，您可以学习如何使用 JDBC 对员工表进行操作，并根据实际需求进行扩展和优化。
原文地址: https://www.cveoy.top/t/topic/o3rp 著作权归作者所有。请勿转载和采集!