6: employeeInsertGUI_v2
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.*;
import java.sql.*;
import java.util.ArrayList;
public class employeeInsertGUI_v2 {
ArrayList<String> columns;
ArrayList<String> types;
Connection connection;
ArrayList<ArrayList<String>> dataToInsert;
ArrayList<ArrayList<String>> data;
String table;
JTable dataTable;
public employeeInsertGUI_v2() {
connection = connectToDB();
table = "TEMPLEM";
dataTable = new JTable();
reloadData();
showDataTable();
}
private void reloadData() {
try {
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM " + table);
// 获取列名
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
columns = new ArrayList<>();
for (int i = 0; i < columnCount; i++) {
columns.add(metaData.getColumnName(i + 1));
}
// 获取列类型
types = new ArrayList<>();
for (int i = 0; i < columnCount; i++) {
types.add(metaData.getColumnTypeName(i + 1));
}
// 获取数据
data = new ArrayList<>();
while (resultSet.next()) {
ArrayList<String> row = new ArrayList<>();
for (int i = 0; i < columnCount; i++) {
String value = resultSet.getString(i + 1);
// 检查空值并替换为"空"
row.add(value != null ? value : "空");
}
data.add(row);
}
reloadDataTable();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e.getMessage());
}
}
private Connection connectToDB() {
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
connection = DriverManager.getConnection(
"jdbc:db2://192.168.80.128:50000/sample",
"db2admin",
"db2admin"
);
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "连接数据库失败：" + e.getMessage());
}
return connection;
}
// 插入任意条记录
private void insertRows(String table) {
try {
Statement statement = connection.createStatement();
// 批量插入
for (ArrayList<String> row : dataToInsert) {
StringBuilder sql = new StringBuilder("INSERT INTO " + table + " VALUES (");
for (int i = 0; i < row.size(); i++) {
if (row.get(i).isEmpty()) {
sql.append("NULL");
} else if (types.get(i).equals("VARCHAR")) {
sql.append("'").append(row.get(i)).append("'");
} else {
sql.append(row.get(i));
}
if (i != row.size() - 1) {
sql.append(", ");
}
}
sql.append(")");
System.out.println(sql);
statement.addBatch(sql.toString());
}
statement.executeBatch();
connection.commit();
JOptionPane.showMessageDialog(null, "插入成功");
// 刷新数据
reloadData();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "插入失败：" + e.getMessage());
}
}
private void reloadDataTable() {
Object[][] data;
if (!this.data.isEmpty()) {
data = new Object[this.data.size()][this.data.getFirst().size()];
for (int i = 0; i < this.data.size(); i++) {
for (int j = 0; j < this.data.get(i).size(); j++) {
data[i][j] = this.data.get(i).get(j);
}
}
} else {
data = new Object[0][0];
}
dataTable.setModel(new javax.swing.table.DefaultTableModel(
data,
columns.toArray()
));
}
private void showDataTable() {
JFrame frame = new JFrame("employeeInsertGUI_v2");
JPanel panel = new JPanel();
JScrollPane scrollPane = new JScrollPane(dataTable);
scrollPane.setSize(800, 800);
panel.add(scrollPane);
// 插入按钮
Panel buttonPanel = getChoosePanel();
panel.add(buttonPanel);
panel.setLayout(new BoxLayout(panel, BoxLayout.Y_AXIS));
frame.setContentPane(panel);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.pack();
frame.setVisible(true);
}
private Panel getChoosePanel() {
JButton insertButton = new JButton("插入单行");
JButton insertMultiButton = new JButton("插入多行");
JButton insertBySubqueryButton = new JButton("根据子查询插入");
JButton deleteButton = new JButton("基于条件删除");
// 绑定事件
insertButton.addActionListener(e -> handleInsertButton());
insertMultiButton.addActionListener(e -> handleInsertMultiButton());
insertBySubqueryButton.addActionListener(e -> handleInsertBySubqueryButton());
deleteButton.addActionListener(e -> showDeleteGUI());
// 将按钮添加到面板
Panel buttonPanel = new Panel();
buttonPanel.add(insertButton);
buttonPanel.add(insertMultiButton);
buttonPanel.add(insertBySubqueryButton);
buttonPanel.add(deleteButton);
return buttonPanel;
}
private void showInsertMultiGUI(int rowCount) {
JFrame frame = new JFrame();
JPanel panel = new JPanel();
JPanel labels = new JPanel();// 列名
JPanel[] inputs = new JPanel[rowCount]; // 输入框
for (String column : columns) {
labels.add(new Label(column));
}
for (int i = 0; i < rowCount; i++) {
inputs[i] = new JPanel();
for (int j = 0; j < columns.size(); j++) {
inputs[i].add(new TextField());
}
}
panel.add(labels);
for (int i = 0; i < rowCount; i++) {
panel.add(inputs[i]);
}
labels.setLayout(new GridLayout(0, labels.getComponentCount()));
for (int i = 0; i < rowCount; i++) {
inputs[i].setLayout(new GridLayout(0, inputs[i].getComponentCount()));
}
panel.setLayout(new BoxLayout(panel, BoxLayout.Y_AXIS));
// 添加确定按钮和取消按钮
JButton confirmButton = new JButton("确定");
JButton cancelButton = new JButton("取消");
confirmButton.addActionListener(e -> {
dataToInsert = new ArrayList<>();
for (int i = 0; i < rowCount; i++) {
ArrayList<String> row = new ArrayList<>();
for (int j = 0; j < columns.size(); j++) {
row.add(((TextField) inputs[i].getComponent(j)).getText());
}
dataToInsert.add(row);
}
insertRows(table);
frame.dispose();
});
cancelButton.addActionListener(e -> frame.dispose());
JPanel buttonPanel = new JPanel();
buttonPanel.add(confirmButton);
buttonPanel.add(cancelButton);
panel.add(buttonPanel);
buttonPanel.setLayout(new BoxLayout(buttonPanel, BoxLayout.X_AXIS));
panel.setLayout(new BoxLayout(panel, BoxLayout.Y_AXIS));
frame.setContentPane(panel);
frame.pack();
frame.setVisible(true);
}
private void showDeleteGUI() {
String subqueryCondition = JOptionPane.showInputDialog("请输入删除条件，例如：SEX = 'MALE'");
if (subqueryCondition == null || subqueryCondition.trim().isEmpty()) {
return;
}
JFrame frame = new JFrame("Delete Rows");
JPanel panel = new JPanel();
// 获取符合子查询条件的数据
ArrayList<ArrayList<String>> dataToDelete = new ArrayList<>();
try {
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM " + table + " WHERE " + subqueryCondition);
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
ArrayList<String> row = new ArrayList<>();
for (int i = 0; i < columnCount; i++) {
row.add(resultSet.getString(i + 1));
}
dataToDelete.add(row);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e.getMessage());
return;
}
// 创建表格来显示这些数据
Object[][] data = new Object[dataToDelete.size()][columns.size() + 1];
for (int i = 0; i < dataToDelete.size(); i++) {
data[i][0] = false; // Checkbox
for (int j = 0; j < columns.size(); j++) {
data[i][j + 1] = dataToDelete.get(i).get(j);
}
}
JTable deleteTable = new JTable(new DefaultTableModel(data, columns.toArray()) {
public Class<?> getColumnClass(int column) {
return (column == 0) ? Boolean.class : String.class;
}
});
JScrollPane scrollPane = new JScrollPane(deleteTable);
panel.add(scrollPane);
// 添加删除按钮
JButton deleteButton = new JButton("删除选中行");
deleteButton.addActionListener(e -> {
deleteSelectedRows(deleteTable);
frame.dispose();
});
panel.add(deleteButton);
frame.add(panel);
frame.pack();
frame.setVisible(true);
}
private void handleInsertButton() {
showInsertMultiGUI(1);
}
private void handleInsertMultiButton() {
// 获取用户输入
String input = JOptionPane.showInputDialog("请输入要插入的行数");
if (input == null) {
return;
}
int rowCount = Integer.parseInt(input);
showInsertMultiGUI(rowCount);
}
private void handleInsertBySubqueryButton() {
// 获取用户输入的子查询条件
String subqueryCondition = JOptionPane.showInputDialog("请输入子查询条件");
if (subqueryCondition == null) {
return;
}
try {
Statement statement = connection.createStatement();
// 构建完整的插入语句，使用子查询从 EMPLOYEE 表中选择数据
String sql = "INSERT INTO " + table + " SELECT * FROM JLU.EMPLOYEE WHERE " + subqueryCondition;
statement.executeUpdate(sql);
JOptionPane.showMessageDialog(null, "插入成功");
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "插入失败：" + e.getMessage());
}
reloadData();
}//EMPNO = '200340'
private void deleteSelectedRows(JTable table) {
try {
DefaultTableModel model = (DefaultTableModel) table.getModel();
Statement statement = connection.createStatement();
for (int i = model.getRowCount() - 1; i >= 0; i--) {
Boolean selected = (Boolean) model.getValueAt(i, 0);
if (selected) {
String key = model.getValueAt(i, 1).toString(); // Assuming first column is the key
String sql = "DELETE FROM " + this.table + " WHERE EMPNO = '" + key + "'";
statement.executeUpdate(sql);
model.removeRow(i);
}
}
JOptionPane.showMessageDialog(null, "删除完成");
reloadData();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "删除失败：" + e.getMessage());
}
}
public static void main(String[] args) {
new employeeInsertGUI_v2();
}
}
运行结果：
显示空白的对比：
前：
后：
关于这一题，几个需要注意的地方：
-
按照PPT的意思，删除需要符合用户需求，那么就需要用户输入删除条件，这里我使用的是子查询，即用户输入子查询条件，然后根据子查询条件勾选删除。勾选的时候选中的肯定需要是标记主变量，这样才具有唯一标识性。但是子查询的时候可以选择多个属性。
-
题目还要求将结果集中所有为空的列值都以“空”的形式显示。那么我的选择就是修改reloadData()方法，将所有的空值都替换为“空”。这里我使用的是遍历二维数组，然后将空值替换为“空”。
这里题目着重提示了如下的方法，也需要记忆一下：
参与讨论
(Participate in the discussion)
参与讨论