源程序: import java.sql.*;
public class EmployeeDatabase { // 数据库连接信息 private static final String URL = "jdbc:mysql://localhost:3306/employee_salaries"; private static final String USERNAME = "root"; private static final String PASSWORD = "123456";
public static void main(String[] args) {
try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)) { //删除现有的"员工"表。如果表存在,它将被删除;如果不存在,则不会产生任何影响。 dropEmployeeTable(connection); // 创建员工信息表- createEmployeeTable(connection);
// 插入数据记录 insertData(connection);
// 删除年龄超过60岁的员工记录 deleteSeniorEmployees(connection);
// 修改职称为高级且为党员的员工工资为3000 updateSalary(connection);
// 查询所有员工信息并显示到屏幕上 displayEmployees(connection);
} catch (SQLException e) { e.printStackTrace(); } } private static void dropEmployeeTable(Connection connection) throws SQLException { String dropTableQuery = "DROP TABLE IF EXISTS 员工";
try (Statement statement = connection.createStatement()) { statement.executeUpdate(dropTableQuery); System.out.println("原有员工信息表删除成功"); } }
private static void createEmployeeTable(Connection connection) throws SQLException { String createTableQuery = "CREATE TABLE IF NOT EXISTS 员工 (" + "编 INT PRIMARY KEY, " + "姓名 VARCHAR(50), " + "性别 VARCHAR(10), " + "年龄 INT, " + "职称 VARCHAR(20), " + "工资 FLOAT, " + "是否党员 BOOLEAN)";
try (Statement statement = connection.createStatement()) { statement.executeUpdate(createTableQuery); System.out.println("员工信息表创建成功"); } }
private static void insertData(Connection connection) throws SQLException { String insertQuery = "INSERT INTO 员工 (编, 姓名, 性别, 年龄, 职称, 工资, 是否党员) VALUES (?, ?, ?, ?, ?, ?, ?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(insertQuery)) { // 第一条数据记录 preparedStatement.setInt(1, 1); preparedStatement.setString(2, "张三"); preparedStatement.setString(3, "男"); preparedStatement.setInt(4, 35); preparedStatement.setString(5, "高级"); preparedStatement.setFloat(6, 5000); preparedStatement.setBoolean(7, true); preparedStatement.executeUpdate();
// 第二条数据记录 preparedStatement.setInt(1, 2); preparedStatement.setString(2, "李四"); preparedStatement.setString(3, "女"); preparedStatement.setInt(4, 45); preparedStatement.setString(5, "中级"); preparedStatement.setFloat(6, 4000); preparedStatement.setBoolean(7, false); preparedStatement.executeUpdate();
// 第三条数据记录 preparedStatement.setInt(1, 3); preparedStatement.setString(2, "王五"); preparedStatement.setString(3, "男"); preparedStatement.setInt(4, 55); preparedStatement.setString(5, "低级"); preparedStatement.setFloat(6, 2500); preparedStatement.setBoolean(7, true); preparedStatement.executeUpdate();
// 第四条数据记录 preparedStatement.setInt(1, 4); preparedStatement.setString(2, "赵六"); preparedStatement.setString(3, "女"); preparedStatement.setInt(4, 28); preparedStatement.setString(5, "中级"); preparedStatement.setFloat(6, 4500); preparedStatement.setBoolean(7, false); preparedStatement.executeUpdate();
// 第五条数据记录 preparedStatement.setInt(1, 5); preparedStatement.setString(2, "钱七"); preparedStatement.setString(3, "男"); preparedStatement.setInt(4, 32); preparedStatement.setString(5, "高级"); preparedStatement.setFloat(6, 6000); preparedStatement.setBoolean(7, true); preparedStatement.executeUpdate();
// 第六条数据记录 preparedStatement.setInt(1, 6); preparedStatement.setString(2, "周八"); preparedStatement.setString(3, "男"); preparedStatement.setInt(4, 66); preparedStatement.setString(5, "低级"); preparedStatement.setFloat(6, 3500); preparedStatement.setBoolean(7, false); preparedStatement.executeUpdate();
System.out.println("数据记录插入成功"); } }
private static void deleteSeniorEmployees(Connection connection) throws SQLException { String deleteQuery = "DELETE FROM 员工 WHERE 年龄 > 60";
try (Statement statement = connection.createStatement()) { int deletedRows = statement.executeUpdate(deleteQuery); System.out.println("已删除年龄超过60岁的员工记录,共 " + deletedRows + " 条记录"); } }
private static void updateSalary(Connection connection) throws SQLException { String updateQuery = "UPDATE 员工 SET 工资 = 3000 WHERE 职称 = '高级' AND 是否党员 = true";
try (Statement statement = connection.createStatement()) { int updatedRows = statement.executeUpdate(updateQuery); System.out.println("已更新职称为高级且为党员的员工工资为3000,共 " + updatedRows + " 条记录"); } }
private static void displayEmployees(Connection connection) throws SQLException { String selectQuery = "SELECT * FROM 员工";
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(selectQuery)) {
System.out.println("所有员工信息:"); System.out.println("编\t姓名\t性别\t年龄\t职称\t工资\t是否党员"); while (resultSet.next()) { int 编 = resultSet.getInt("编"); String 姓名 = resultSet.getString("姓名"); String 性别 = resultSet.getString("性别"); int 年龄 = resultSet.getInt("年龄"); String 职称 = resultSet.getString("职称"); float 工资 = resultSet.getFloat("工资"); boolean 是否党员 = resultSet.getBoolean("是否党员");
System.out.println(编 + "\t" + 姓名 + "\t" + 性别 + "\t" + 年龄 + "\t" + 职称 + "\t" + 工资 + "\t" + 是否党员); } } } } |