Java作业-13

java数据库操作

如何定义一个ForkJoinTask?RecursiveAction和RecursiveTask的区别是什么?

继承它的两个子类之一：RecursiveAction 或 RecursiveTask。

• RecursiveAction
  用于执行没有返回结果的任务。
  需要重写 compute() 方法来实现任务的逻辑。

  import java.util.concurrent.RecursiveAction;
  
  public class MyRecursiveAction extends RecursiveAction {
      @Override
      protected void compute() {
          // 任务逻辑
          // 如果需要，分解任务并调用 invokeAll
      }
  }

• RecursiveTask
  用于有返回结果的任务。
  需要重写 compute() 方法，但这次会返回一个结果。

  import java.util.concurrent.RecursiveTask;
  
  public class MyRecursiveTask extends RecursiveTask<Integer> {
      @Override
      protected Integer compute() {
          // 任务逻辑
          // 如果需要，分解任务并调用 invokeAll
          // 返回计算结果
          return someResult;
      }
  }

• 返回值: RecursiveAction 不产生任何返回结果，而 RecursiveTask 用于计算需要返回结果的任务。

• 使用场景: 根据任务是否需要返回结果来选择。如果只是执行一些操作（如排序、打印等），而不需要结果，则使用 RecursiveAction。如果需要从任务中获取结果（如计算总和、寻找最大值等），则使用 RecursiveTask。

描述下面的JDBC接口:Driver、Connection、Statement和ResultSet

• Driver
  Driver 接口是 JDBC 驱动程序的一部分，用于管理与特定数据库类型的连接。
  使用 JDBC 连接数据库时，首先需要加载并注册特定数据库的 JDBC 驱动。这通常通过调用 Class.forName() 方法实现。

• Connection:
  Connection 接口代表数据库的一个连接。
  使用 DriverManager 获取 Connection 实例，需要提供数据库 URL、用户名和密码。
  Connection 用于管理事务（如提交或回滚）并创建执行 SQL 语句的 Statement 对象。

• Statement:
  Statement 接口用于执行 SQL 语句并返回结果。
  它有几个子接口，如 PreparedStatement 和 CallableStatement，分别用于执行预编译 SQL 和调用存储过程。
  Statement 对象是通过 Connection 对象创建的。

• ResultSet
  ResultSet 接口表示 SQL 查询的结果。
  它允许从结果集中逐行读取数据，并对数据进行操作。
  ResultSet 提供了多种方法来访问不同数据类型的列值。

如何加载一个JDBC 驱动程序? MySQL、Access 和Oracle 的驱动程序类是什么?

在 JDBC 4.0 之前，通常使用 Class.forName() 方法来显式加载 JDBC 驱动程序。自 JDBC 4.0 起，大多数 JDBC 驱动程序使用 Java SE 服务提供者机制自动加载，因此通常不需要显式调用 Class.forName()。

• MySQL: com.mysql.jdbc.Driver 或 com.mysql.cj.jdbc.Driver（较新的版本）
• Access: 由于没有官方 JDBC 驱动，通常使用第三方库如 UCanAccess (net.ucanaccess.jdbc.UcanaccessDriver)
• Oracle: oracle.jdbc.driver.OracleDriver

如何创建一个数据库的连接? MySQL、Access 和 Oracle 的URL 是什么?

使用 DriverManager.getConnection(url, username, password) 方法创建数据库连接。

• MySQL: “jdbc:mysql://host:port/databaseName”
• Access: “jdbc:ucanaccess://path_to_database_file” （使用 UCanAccess 驱动）
• Oracle: “jdbc:oracle:thin:@host:port:SID”

如何创建一个Statement 对象?如何执行一个SQL语句?

• 通过已经建立的 Connection 对象创建 Statement 对象。例如：
  Statement stmt = conn.createStatement();

• 使用 Statement 对象执行 SQL 语句。例如：
  -执行查询：ResultSet rs = stmt.executeQuery(“SELECT * FROM table_name”);
  -执行更新：int rowsAffected = stmt.executeUpdate(“UPDATE table_name SET column_name = value”);

如何获取 ResultSet中的值?

遍历 ResultSet 对象，并使用适当的 getter 方法获取列值。例如：

while (rs.next()) {
    String columnValue = rs.getString("column_name");
    // 其他处理
}

(并行求和)使用 Fork/Join 实现以下方法，对一个线性表求和

public static double paralelSum(double[] Tist)
编写一个测试程序，对一个大小为9000 000的double 值求和。

import java.util.concurrent.RecursiveTask;
import java.util.concurrent.ForkJoinPool;
import java.util.Random;

public class SumTask extends RecursiveTask<Double> {
    private final double[] array;
    private final int start;
    private final int end;
    private static final int THRESHOLD = 1000; // 可调整的阈值

    public SumTask(double[] array, int start, int end) {
        this.array = array;
        this.start = start;
        this.end = end;
    }

    @Override
    protected Double compute() {
        if (end - start <= THRESHOLD) {
            // 直接计算
            double sum = 0;
            for (int i = start; i < end; i++) {
                sum += array[i];
            }
            return sum;
        } else {
            // 分解任务
            int middle = start + (end - start) / 2;
            SumTask left = new SumTask(array, start, middle);
            SumTask right = new SumTask(array, middle, end);
            left.fork(); // 异步执行
            double rightResult = right.compute(); // 同步执行
            double leftResult = left.join(); // 等待左侧任务完成
            return leftResult + rightResult;
        }
    }
}

public class ParallelSum {
    public static double parallelSum(double[] array) {
        ForkJoinPool pool = new ForkJoinPool();
        SumTask task = new SumTask(array, 0, array.length);
        return pool.invoke(task);
    }
}

public class TestParallelSum {
    public static void main(String[] args) {
        double[] array = new double[9000000];
        Random random = new Random();
        for (int i = 0; i < array.length; i++) {
            array[i] = random.nextDouble();
        }

        double sum = ParallelSum.parallelSum(array);
        System.out.println("Sum: " + sum);
    }
}

(访问并更新表 Staff)编写一个程序，浏览、插和更新存储在一个数据库中的职员信息，如图32-27a 所示。View 按钮用于显示具有指定 ID 的记录。Insert 按钮插入一条新的记录。Update按钮更新一条指定ID 的记录。按如下方式创建职工表 Staff

Staff.java

package com.example;

public class Staff {
    private String id;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getMi() {
        return mi;
    }

    public void setMi(String mi) {
        this.mi = mi;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getState() {
        return state;
    }

    public void setState(String state) {
        this.state = state;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    private String lastName;
    private String firstName;
    private String mi;
    private String address;
    private String city;
    private String state;
    private String telephone;
    private String email;
}

StaffDao.java

package com.example;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StaffDao {
    private Connection connection;

    public StaffDao(String url, String user, String password) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        this.connection = DriverManager.getConnection(url, user, password);
    }

    public Staff findStaffById(String id) throws SQLException {
        String sql = "SELECT id, lastName, firstName, mi, address, city, state, telephone, email FROM staff WHERE id = ?;";
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, id);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    return mapRowToStaff(resultSet);
                }
            }
        }
        return null;
    }

    public int insertStaff(Staff staff) throws SQLException {
        String sql = "INSERT INTO staff (id, lastName, firstName, mi, address, city, state, telephone, email) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            mapStaffToStatement(staff, preparedStatement);
            return preparedStatement.executeUpdate();
        }
    }

    public int updateStaff(Staff staff) throws SQLException {
        String sql = "UPDATE staff SET lastName = ?, firstName = ?, mi = ?, address = ?, city = ?, state = ?, telephone = ?, email = ? WHERE id = ?;";
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            mapStaffToStatement(staff, preparedStatement);
            preparedStatement.setString(9, staff.getId());
            return preparedStatement.executeUpdate();
        }
    }

    // Helper methods to map between ResultSet/PreparedStatement and Staff object
    private Staff mapRowToStaff(ResultSet resultSet) throws SQLException {
        Staff staff = new Staff();
        staff.setId(resultSet.getString("id"));
        staff.setLastName(resultSet.getString("lastName"));
        staff.setFirstName(resultSet.getString("firstName"));
        staff.setMi(resultSet.getString("mi"));
        staff.setAddress(resultSet.getString("address"));
        staff.setCity(resultSet.getString("city"));
        staff.setState(resultSet.getString("state"));
        staff.setTelephone(resultSet.getString("telephone"));
        staff.setEmail(resultSet.getString("email"));
        return staff;
    }

    private void mapStaffToStatement(Staff staff, PreparedStatement preparedStatement) throws SQLException {
        preparedStatement.setString(1, staff.getId());
        preparedStatement.setString(2, staff.getLastName());
        preparedStatement.setString(3, staff.getFirstName());
        preparedStatement.setString(4, staff.getMi());
        preparedStatement.setString(5, staff.getAddress());
        preparedStatement.setString(6, staff.getCity());
        preparedStatement.setString(7, staff.getState());
        preparedStatement.setString(8, staff.getTelephone());
        preparedStatement.setString(9, staff.getEmail());
    }

    public List<Staff> findAllStaff() throws SQLException {
        List<Staff> staffList = new ArrayList<>();
        String sql = "SELECT id, lastName, firstName, mi, address, city, state, telephone, email FROM staff;";
        try (Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(sql)) {
            while (resultSet.next()) {
                staffList.add(mapRowToStaff(resultSet));
            }
        }
        return staffList;
    }

    // Close connection
    public void close() throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

StaffApp.java

package com.example;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javafx.application.Application;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.geometry.Pos;
import javafx.scene.Scene;
import javafx.scene.chart.BarChart;
import javafx.scene.chart.CategoryAxis;
import javafx.scene.chart.NumberAxis;
import javafx.scene.chart.PieChart;
import javafx.scene.chart.XYChart;
import javafx.scene.control.*;
import javafx.scene.layout.HBox;
import javafx.scene.layout.VBox;
import javafx.scene.text.Text;
import javafx.stage.Stage;

public class StaffApp extends Application {
    private static final double MIN_WIDTH = 320.0;
    private static final double MIN_HEIGHT = 220.0;
    private StaffDao staffDao;

    // UI组件
    private TextField idField, lastNameField, firstNameField, miField, addressField, cityField, stateField,
            telephoneField, emailField;
    private Button viewBtn, insertBtn, updateBtn, clearBtn, showChartBtn;

    @Override
    public void start(Stage stage) throws Exception {
        // 初始化数据库连接
        staffDao = new StaffDao("jdbc:mysql://127.0.0.1:3306/Staff", "root", "");

        VBox vBox = new VBox(10);
        vBox.setMinWidth(MIN_WIDTH);
        vBox.setMinHeight(MIN_HEIGHT);

        // 添加UI组件
        addComponents(vBox);

        // 设置场景和舞台
        Scene scene = new Scene(vBox);
        stage.setScene(scene);
        stage.setTitle("Staff Management");
        stage.show();
    }

    private void addComponents(VBox vBox) {
        // 标题
        Text title = new Text("Record found");
        vBox.getChildren().add(title);

        // id框
        idField = new TextField();
        Label idLabel = new Label("ID", idField);
        idLabel.setContentDisplay(ContentDisplay.RIGHT);
        vBox.getChildren().add(idLabel);

        // 信息行
        HBox infoBox = new HBox(10);
        lastNameField = new TextField();
        Label lastNameLabel = new Label("Last Name", lastNameField);
        lastNameLabel.setContentDisplay(ContentDisplay.RIGHT);

        firstNameField = new TextField();
        Label firstNameLabel = new Label("First Name", firstNameField);
        firstNameLabel.setContentDisplay(ContentDisplay.RIGHT);

        miField = new TextField();
        Label miLabel = new Label("MI", miField);
        miLabel.setContentDisplay(ContentDisplay.RIGHT);

        infoBox.getChildren().addAll(lastNameLabel, firstNameLabel, miLabel);
        vBox.getChildren().add(infoBox);

        // 地址
        addressField = new TextField();
        Label addressLabel = new Label("Address", addressField);
        addressLabel.setContentDisplay(ContentDisplay.RIGHT);

        vBox.getChildren().add(addressLabel);

        // 城市和州行
        HBox briefAddressBox = new HBox(10);
        cityField = new TextField();
        Label cityLabel = new Label("City", cityField);
        cityLabel.setContentDisplay(ContentDisplay.RIGHT);

        stateField = new TextField();
        Label stateLabel = new Label("State", stateField);
        stateLabel.setContentDisplay(ContentDisplay.RIGHT);

        briefAddressBox.getChildren().addAll(cityLabel, stateLabel);
        vBox.getChildren().add(briefAddressBox);

        // 电话
        telephoneField = new TextField();
        Label telephoneLabel = new Label("Telephone", telephoneField);
        telephoneLabel.setContentDisplay(ContentDisplay.RIGHT);
        vBox.getChildren().add(telephoneLabel);

        // Email
        emailField = new TextField();
        Label emailLabel = new Label("Email", emailField);
        emailLabel.setContentDisplay(ContentDisplay.RIGHT);
        vBox.getChildren().add(emailLabel);

        // 控制按钮
        HBox controlBox = new HBox(10);
        controlBox.setAlignment(Pos.CENTER);

        viewBtn = new Button("View");
        insertBtn = new Button("Insert");
        updateBtn = new Button("Update");
        clearBtn = new Button("Clear");
        showChartBtn = new Button("Show Charts");

        controlBox.getChildren().addAll(viewBtn, insertBtn, updateBtn, clearBtn, showChartBtn);
        vBox.getChildren().add(controlBox);

        // 设置事件处理
        setupEventHandlers();
    }

    private void setupEventHandlers() {
        viewBtn.setOnAction(event -> viewStaff());
        insertBtn.setOnAction(event -> insertStaff());
        updateBtn.setOnAction(event -> updateStaff());
        clearBtn.setOnAction(event -> clearFields());
        showChartBtn.setOnAction(event -> showCharts());
    }

    private void viewStaff() {
        String id = idField.getText();
        try {
            Staff staff = staffDao.findStaffById(id);
            if (staff != null) {
                updateUIWithStaffInfo(staff);
            } else {
                clearFields();
                // 显示员工不存在的消息
                showAlert("No Record Found", "No staff record found with ID: " + id);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // 显示数据库错误消息
        }
    }

    private void insertStaff() {
        Staff staff = createStaffFromFields();
        if (staff == null)
            return; // 校验失败
        try {
            int result = staffDao.insertStaff(staff);
            if (result > 0) {
                showAlert("Success", "Staff inserted successfully!");
            } else {
                showAlert("Failed", "Failed to insert staff.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // 显示数据库错误消息
        }
    }

    private void updateStaff() {
        Staff staff = createStaffFromFields();
        if (staff == null)
            return; // 校验失败
        try {
            int result = staffDao.updateStaff(staff);
            if (result > 0) {
                showAlert("Success", "Staff updated successfully!");
            } else {
                showAlert("Failed", "Failed to update staff.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // 显示数据库错误消息
        }
    }

    private void clearFields() {
        // 清空所有文本字段
        idField.clear();
        lastNameField.clear();
        firstNameField.clear();
        miField.clear();
        addressField.clear();
        cityField.clear();
        stateField.clear();
        telephoneField.clear();
        emailField.clear();
    }

    private Staff createStaffFromFields() {
        String id = idField.getText().trim();
        String lastName = lastNameField.getText().trim();
        String firstName = firstNameField.getText().trim();
        String mi = miField.getText().trim();
        String address = addressField.getText().trim();
        String city = cityField.getText().trim();
        String state = stateField.getText().trim();
        String telephone = telephoneField.getText().trim();
        String email = emailField.getText().trim();

        // 输入验证
        if (id.isEmpty() || lastName.isEmpty() || firstName.isEmpty() || address.isEmpty() || city.isEmpty()
                || state.isEmpty() || telephone.isEmpty() || email.isEmpty()) {
            showAlert("Input Error", "All fields must be filled out.");
            return null;
        }

        // 创建Staff对象
        Staff staff = new Staff();
        staff.setId(id);
        staff.setLastName(lastName);
        staff.setFirstName(firstName);
        staff.setMi(mi);
        staff.setAddress(address);
        staff.setCity(city);
        staff.setState(state);
        staff.setTelephone(telephone);
        staff.setEmail(email);

        return staff;
    }

    private void updateUIWithStaffInfo(Staff staff) {
        if (staff == null)
            return;

        idField.setText(staff.getId());
        lastNameField.setText(staff.getLastName());
        firstNameField.setText(staff.getFirstName());
        miField.setText(staff.getMi());
        addressField.setText(staff.getAddress());
        cityField.setText(staff.getCity());
        stateField.setText(staff.getState());
        telephoneField.setText(staff.getTelephone());
        emailField.setText(staff.getEmail());
    }

    private void showCharts() {
        try {
            List<Staff> staffList = staffDao.findAllStaff();
            Map<String, Integer> cityCount = new HashMap<>();

            for (Staff staff : staffList) {
                String city = staff.getCity();
                cityCount.put(city, cityCount.getOrDefault(city, 0) + 1);
            }

            showPieChart(cityCount);
            showBarChart(cityCount);
        } catch (SQLException e) {
            e.printStackTrace();
            // 显示数据库错误消息
        }
    }

    private void showPieChart(Map<String, Integer> cityCount) {
        Stage chartStage = new Stage();
        VBox chartBox = new VBox();

        List<PieChart.Data> pieChartDataList = new ArrayList<>();
        for (Map.Entry<String, Integer> entry : cityCount.entrySet()) {
            pieChartDataList.add(new PieChart.Data(entry.getKey(), entry.getValue()));
        }
        ObservableList<PieChart.Data> dataObservableList = FXCollections.observableArrayList(pieChartDataList);
        PieChart pieChart = new PieChart(dataObservableList);
        pieChart.setTitle("City Count");
        chartBox.getChildren().add(pieChart);

        Scene scene = new Scene(chartBox, 500, 400);
        chartStage.setScene(scene);
        chartStage.setTitle("Pie Chart");
        chartStage.show();
    }

    private void showBarChart(Map<String, Integer> cityCount) {
        Stage chartStage = new Stage();
        VBox chartBox = new VBox();

        CategoryAxis xAxis = new CategoryAxis();
        NumberAxis yAxis = new NumberAxis();
        BarChart<String, Number> barChart = new BarChart<>(xAxis, yAxis);
        xAxis.setLabel("City");
        yAxis.setLabel("Count");

        XYChart.Series<String, Number> series = new XYChart.Series<>();
        for (Map.Entry<String, Integer> entry : cityCount.entrySet()) {
            series.getData().add(new XYChart.Data<>(entry.getKey(), entry.getValue()));
        }
        barChart.getData().add(series);

        chartBox.getChildren().add(barChart);

        Scene scene = new Scene(chartBox, 500, 400);
        chartStage.setScene(scene);
        chartStage.setTitle("Bar Chart");
        chartStage.show();
    }

    private void showAlert(String title, String message) {
        Alert alert = new Alert(Alert.AlertType.INFORMATION);
        alert.setTitle(title);
        alert.setHeaderText(null);
        alert.setContentText(message);
        alert.showAndWait();
    }

    @Override
    public void stop() throws Exception {
        if (staffDao != null) {
            staffDao.close();
        }
    }
}