8

两个程序：

1. labstaff1.java

   import java.sql.*;
   import java.io.*;
   import java.util.*;
   import java.math.*;
   
   public class labstaff1 {
       static {
           try {
               // (1) Load the DB2 Driver
               Class.forName("com.ibm.db2.jcc.DB2Driver"); // (1)
           }
           catch (Exception e) {
               System.exit(1);
           }
       }
   
       public static void main(String args[]) throws Exception {
           int mydeptno = 0;
           String deptno = "";
           String outline = " ";
           String name = " ";
           String job = " ";
           String salary = "";
           String intext = "\n ID       NAME      SALARY\n";
           String indash = "--------  --------  --------------\n";
           String blanks = "                                                        ";
   
           // (2) Define the variable SQLWarn that is used for SQLWarnings
           SQLWarning SQLWarn = null;
   
           // (3) Connect to the DB2 Database SAMPLE
           Connection sample = DriverManager.getConnection("jdbc:db2://192.168.80.128:50000/sample", "db2admin", "db2admin");
           System.out.println("\n Set AutoCommit off");
           sample.setAutoCommit(false);
           System.out.println("\n Autocommit off");
   
           try {
               System.out.println("\n Enter the Department number\n");
               BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
               String s;
               s = in.readLine();
               deptno = s.substring(0, 2);
               mydeptno = Integer.parseInt(deptno);
           } catch (Exception e) {
               e.printStackTrace();
               System.exit(0);
           }
   
           try {
               // (4) Instantiate the PreparedStatement object named stmt
               PreparedStatement stmt = sample.prepareStatement(
                       "select id, name, salary from JLU.staff where Dept = ?");
   
               // (5) Set the parameter in the PreparedStatement object stmt to mydeptno
               stmt.setInt(1, mydeptno);
   
               // (6) Declare the ResultSet object rs and assign the results of the SQL select statement
               ResultSet rs = stmt.executeQuery();
   
               // (7) If SQLWarning occurs, display the warning
               if ((SQLWarn = stmt.getWarnings()) != null) {
                   System.out.println("\n Value of SQLWarn on single row insert to DEP is: \n");
                   System.out.println(SQLWarn);
               }
   
               // (8) Use the ResultSet next() method to retrieve the first row of the ResultSet
               boolean more = rs.next();
               System.out.println(intext);
               System.out.println(indash);
   
               while (more) {
                   name = rs.getString(1);
                   job = rs.getString(2);
                   salary = rs.getString(3);
                   outline = (name + blanks.substring(0, 10 - name.length())) +
                           (job + blanks.substring(0, 10 - job.length())) +
                           (salary + blanks.substring(0, 12 - salary.length()));
                   System.out.println("\n" + outline);
   
                   // (9) Retrieve the next row of the Result Set
                   more = rs.next();
               }
           }
           catch (Exception e) {
               System.exit(1);
           }
       }
   }
   
   
   

2. employeeInsertGUI_v4.java

   import javax.swing.*;
   import javax.swing.table.TableCellRenderer;
   import java.awt.*;
   import java.sql.*;
   import java.util.ArrayList;
   
   public class employeeInsertGUI_v4 {
       ArrayList<String> columns;
       ArrayList<String> types;
       Connection connection;
       ArrayList<ArrayList<String>> dataToInsert;
       ArrayList<ArrayList<String>> data;
       String table;
       JScrollPane scrollPane;
   
       public employeeInsertGUI_v4() {
           connection = connectToDB();
           scrollPane = new JScrollPane();
           table = "TEMPL";
           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++) {
                       row.add(resultSet.getString(i + 1));
                   }
                   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 {
                           sql.append("?");
                       }
   
                       if (i != row.size() - 1) {
                           sql.append(", ");
                       }
                   }
                   sql.append(")");
                   PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
                   int index = 1;
                   for (int i = 0; i < row.size(); i++) {
                       if (row.get(i).isEmpty()) {
                           continue;
                       } else if (types.get(i).equals("VARCHAR") ||
                               types.get(i).equals("CHAR") ||
                               types.get(i).equals("DATE")) {
                           preparedStatement.setString(index, row.get(i));
                       } else if (types.get(i).equals("INTEGER")) {
                           preparedStatement.setInt(index, Integer.parseInt(row.get(i)));
                       } else if (types.get(i).equals("SMALLINT")) {
                           preparedStatement.setShort(index, Short.parseShort(row.get(i)));
                       } else if (types.get(i).equals("DECIMAL")) {
                           preparedStatement.setBigDecimal(index, new java.math.BigDecimal(row.get(i)));
                       }
                       index++;
                   }
                   preparedStatement.executeUpdate();
               }
               JOptionPane.showMessageDialog(null, "插入成功");
               reloadData();
           } catch (SQLException e) {
               handleSQLError(e);
           } catch (NumberFormatException e) {
               String message = e.getMessage();
               if (message.contains("out of range")) {
                   message = "发生溢出：当转换为一个数字型数据类型时，数据溢出";
               } else {
                   message = "类型错误：类型不匹配";
               }
               JOptionPane.showMessageDialog(null, message);
           }
       }
   
       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];
           }
   
           // 将null值显示为"空"
           // 添加监视器，修改单元格时自动更新data
           JTable dataTable = new JTable(data, columns.toArray()) {
               @Override
               public Component prepareRenderer(TableCellRenderer renderer, int row, int column) {
                   Object value = getModel().getValueAt(row, column);
                   if (value == null) {
                       JPanel component = new JPanel();
                       component.setBackground(new Color(0x87CEFA));
                       JLabel label = new JLabel("空");
                       label.setForeground(Color.WHITE);
                       label.setFont(new Font("宋体", Font.BOLD, 16));
                       label.setHorizontalAlignment(JLabel.CENTER);
                       label.setVerticalAlignment(JLabel.CENTER);
                       component.add(label);
                       component.setLayout(new GridLayout(1, 1));
                       return component;
                   } else {
                       return super.prepareRenderer(renderer, row, column);
                   }
               }
           };
           scrollPane.setViewportView(dataTable);
       }
   
       private void showDataTable() {
           JFrame frame = new JFrame("lab8");
           JPanel panel = new JPanel();
   
           scrollPane.setSize(800, 800);
           panel.add(scrollPane);
   
           // 插入按钮
           JButton insertButton = new JButton("插入单行");
           JButton insertMultiButton = new JButton("插入多行");
           JButton insertBySubqueryButton = new JButton("根据子查询插入");
           JButton saveEditButton = new JButton("保存修改");
   
           // 绑定事件
           insertButton.addActionListener(e -> handleInsertButton());
           insertMultiButton.addActionListener(e -> handleInsertMultiButton());
           insertBySubqueryButton.addActionListener(e -> handleInsertBySubqueryButton());
           saveEditButton.addActionListener(e -> handleSaveEditButton());
   
           // 将按钮添加到面板
           Panel buttonPanel = new Panel();
           buttonPanel.add(insertButton);
           buttonPanel.add(insertMultiButton);
           buttonPanel.add(insertBySubqueryButton);
           buttonPanel.add(saveEditButton);
           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 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 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 subquery = JOptionPane.showInputDialog("请输入子查询");
           if (subquery == null) {
               return;
           }
           try {
               Statement statement = connection.createStatement();
               String sql = "INSERT INTO " + table + " ";
               sql += subquery;
               statement.executeUpdate(sql);
               JOptionPane.showMessageDialog(null, "插入成功");
           } catch (SQLException e) {
               handleSQLError(e);
           }
           reloadData();
       }
   
   
       private void handleSaveEditButton() {
           //遍历scrollPanel下label的值并打印
           Component[] components = scrollPane.getViewport().getComponents();
           JTable table = (JTable) components[0];
           int rowCount = table.getRowCount();
           int columnCount = table.getColumnCount();
           ArrayList<ArrayList<String>> data = new ArrayList<>();
           for (int i = 0; i < rowCount; i++) {
               ArrayList<String> row = new ArrayList<>();
               for (int j = 0; j < columnCount; j++) {
                   row.add((String) table.getValueAt(i, j));
               }
               data.add(row);
           }
           try {
               Statement statement = connection.createStatement();
               for (int i = 0; i < rowCount; i++) {
                   // 跳过未修改的行
                   boolean isModified = false;
                   for (int j = 0; j < columnCount; j++) {
                       if (data.get(i).get(j) == null && this.data.get(i).get(j) == null) {
                           continue;
                       }
                       if (!data.get(i).get(j).equals(this.data.get(i).get(j))) {
                           isModified = true;
                           break;
                       }
                   }
                   if (!isModified) {
                       continue;
                   }
   
                   // 更新
                   StringBuilder sql = new StringBuilder("UPDATE " + this.table + " SET ");
                   for (int j = 0; j < columnCount; j++) {
                       if (data.get(i).get(j) == null ||
                               data.get(i).get(j).isEmpty()) {
                           sql.append(columns.get(j)).append(" = NULL");
                       } else {
                           sql.append(columns.get(j)).append(" = ?");
                       }
                       if (j != columnCount - 1) {
                           sql.append(", ");
                       }
                   }
                   sql.append(" WHERE ");
                   for (int j = 0; j < columnCount; j++) {
                       if (this.data.get(i).get(j) == null ||
                               this.data.get(i).get(j).isEmpty()) {
                           sql.append(columns.get(j)).append(" IS NULL");
                       } else {
                           sql.append(columns.get(j)).append(" = ?");
                       }
                       if (j != columnCount - 1) {
                           sql.append(" AND ");
                       }
                   }
                   PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
                   int index = 1;
                   for (int j = 0; j < columnCount; j++) {
                       if (data.get(i).get(j) == null ||
                               data.get(i).get(j).isEmpty()) {
                           continue;
                       } else if (types.get(j).equals("VARCHAR") ||
                               types.get(j).equals("CHAR") ||
                               types.get(j).equals("DATE")) {
                           preparedStatement.setString(index, data.get(i).get(j));
                       } else if (types.get(j).equals("INTEGER")) {
                           preparedStatement.setInt(index, Integer.parseInt(data.get(i).get(j)));
                       } else if (types.get(j).equals("SMALLINT")) {
                           preparedStatement.setShort(index, Short.parseShort(data.get(i).get(j)));
                       } else if (types.get(j).equals("DECIMAL")) {
                           preparedStatement.setBigDecimal(index, new java.math.BigDecimal(data.get(i).get(j)));
                       }
                       index++;
                   }
                   for (int j = 0; j < columnCount; j++) {
                       if (this.data.get(i).get(j) == null ||
                               this.data.get(i).get(j).isEmpty()) {
                           continue;
                       } else if (types.get(j).equals("VARCHAR") ||
                               types.get(j).equals("CHAR") ||
                               types.get(j).equals("DATE")) {
                           preparedStatement.setString(index, this.data.get(i).get(j));
                       } else if (types.get(j).equals("INTEGER")) {
                           preparedStatement.setInt(index, Integer.parseInt(this.data.get(i).get(j)));
                       } else if (types.get(j).equals("SMALLINT")) {
                           preparedStatement.setShort(index, Short.parseShort(this.data.get(i).get(j)));
                       } else if (types.get(j).equals("DECIMAL")) {
                           preparedStatement.setBigDecimal(index, new java.math.BigDecimal(this.data.get(i).get(j)));
                       }
                       index++;
                   }
                   preparedStatement.executeUpdate();
               }
               JOptionPane.showMessageDialog(null, "修改成功");
               reloadData();
           } catch (SQLException e) {
               handleSQLError(e);
           }
       }
   
       private void handleSQLError(SQLException e) {
           int errorCode = e.getErrorCode();
           String message;
           if (errorCode == -407) {
               message = "违反约束：不能把NULL值插到定义为NOT NULL的列中";
           } else if (errorCode == -413) {
               message = "发生溢出：当转换为一个数字型数据类型时，数据溢出";
           } else {
               message = e.getMessage();
           }
           JOptionPane.showMessageDialog(null, "插入失败：" + message);
       }
   
       public static void main(String[] args) {
           new employeeInsertGUI_v4();
       }
   }
   

运行结果：
labstaff1.java:

employeeInsertGUI_v4.java:

需要注意的点：

1. 首先是填空的程序题，所填入的语句应该有所印象，我总结如下：

   //加载DB2驱动，这个填空要填过很多次，需要注意一下
   Class.forName("com.ibm.db2.jcc.DB2Driver");
   
   //定义用于SQLWarnings的变量SQLWarn
   SQLWarning SQLWarn = null;
   
   //填入端口账号密码和数据库建立连接
   Connection sample = DriverManager.getConnection("jdbc:db2://192.168.80.128:50000/sample", "db2admin", "db2admin");
   
   //实例化PreparedStatement对象，更加安全
   PreparedStatement stmt = sample.prepareStatement(
                       "select id, name, salary from JLU.staff where Dept = ?");
   
   //将PreparedStatement对象stmt中的参数设置为mydeptno
   stmt.setInt(1, mydeptno);
   
   //声明ResultSet对象rs并分配SQL select语句的结果
   ResultSet rs = stmt.executeQuery();
   
   //如果发生SQLWarning，则显示警告
   if ((SQLWarn = stmt.getWarnings()) != null) {
                   System.out.println("\n Value of SQLWarn on single row insert to DEP is: \n");
                   System.out.println(SQLWarn);
               }
   
   //使用ResultSet next（）方法检索ResultSet的第一行
   boolean more = rs.next();
   
   //检索结果集的下一行
   more = rs.next();
   

2. 其次是关于游标的学习，需要了解一下,下一个实验也用到了:
   
   游标滚动，可以通过ResultSet.TYPE_SCROLL_INSENSITIVE来设置；游标关闭rs.close()，不等于事务提交，其分配的内存空间被释放，但是锁没被释放；如果是commit，全部释放掉。