使用resultSet.update()将数据插入mySQL中的2个表时出错

Question

我在将数据插入一个表的文件，然后将resultSet SQL语句更改为另一个表，并插入表2的字段时出现此错误：

SEVERE:空com.mysql.jdbc.NotUpdataable:结果集不是可更新。此结果集必须来自创建的语句如果结果集类型为ResultSet.CONCUR_UPDABLE，则查询必须只选择一个表，不能使用函数，必须全部选择该表中的主键。请参阅JDBC 2.1 API规范，第5.6节了解更多详细信息。

resultSet语句设置为可更新：

statement = (Statement) connection.createStatement(ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_SCROLL_INSENSITIVE);

结果为表1和表设置雄蕊：

resultSet =  (ResultSet) statement.executeQuery(SELECT * FROM employees);
+----------------------+-----------+----------+------------+----------------------------+----------------+
| socialSecurityNumber | firstName | lastName | birthday   | employeeType               | departmentName |
+----------------------+-----------+----------+------------+----------------------------+----------------+
| 111-11-1111          | John      | Smith    | 1945-01-02 | salariedEmployee           | R&D            |
| 222-22-2222          | Sue       | Jones    | 1961-02-03 | commissionEmployee         | SALES          |
| 234435               | ciaran    | mooney   | 2013-11-28 | commissionEmployee         | Sales          |
| 333-33-3333          | Bob       | Lowis    | 1958-10-05 | basePlusCommissionEmployee | SALES          |
| 444-44-4444          | Karen     | Price    | 1972-05-25 | hourlyEmployee             | HR             |
+----------------------+-----------+----------+------------+----------------------------+----------------+

表2:

+----------------------+------------+----------------+-------+
| socialSecurityNumber | grossSales | commissionRate | bonus |
+----------------------+------------+----------------+-------+
| 222-22-2222          |      10100 |           0.05 |     0 |
| 222-22-2222          |   12234445 |            0.6 |   300 |
+----------------------+------------+----------------+-------+
2 rows in set (0.00 sec)

//using a object of class employee to update Table employees via resultSet
//manipulate the data in the resultSet, then  upadteRow to save changes on DataBase
        resultSet.moveToInsertRow();//insert a into a empty row
        resultSet.updateString("socialSecurityNumber", addEmployee.getSocialSecurityNumber());
        resultSet.updateString("firstName", addEmployee.getFirstName());
        resultSet.updateString("lastName", addEmployee.getLastName());
        resultSet.updateString("departmentName", addEmployee.getDepartment());
        resultSet.updateDate("birthday", new java.sql.Date(addEmployee.getDOB().getTime()));
        resultSet.updateString("employeeType", "commissionEmployee");
        resultSet.updateString("departmentName", addEmployee.getDepartment());

        //EXCEPTION THROWN HERE
        //new resultSet for commsionRate employee
        statement = (Statement) connection.createStatement(ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_SCROLL_INSENSITIVE);
        resultSet = (ResultSet) statement.executeQuery("SELECT * FROM commissionEmployees");
        resultSet.updateString("socialSecurityNumber", addEmployee.getSocialSecurityNumber());
        resultSet.updateInt("grossSales", (int)addEmployee.getGrossSales());
        resultSet.updateDouble("commissionRate", addEmployee.getCommissionRate());
        resultSet.updateDouble("bonus", 0);
        //uopdate the database*/
        resultSet.insertRow();
        //return cursor to orginal position
        resultSet.beforeFirst();

主键是两个表中都存在的SocialSecurityNumber字段。当我试图将resultSet更改为上面突出显示的表2时，会引发异常。。

提前感谢。。。

Answer 1

您使用错误的参数顺序调用createStatement(int resultSetType, int resultSetConcurrency)：

参数：
resultSetType——一种结果集类型；ResultSet.TYPE_FORWARD_ONLY、ResultSet.TYPE_SCROLL_INSENSITIVE或ResultSet.TYPE_SCROLL_SENSITIVE之一
resultSetConcurrency——一种并发类型；ResultSet.CONCUR_READ_ONLY或ResultSet.CONCUR_UPDATABLE 之一

所以你应该称之为：

createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

可能不一定支持类型和并发的某些组合。我不知道什么适用于MySQL驱动程序。驱动程序可能会降级（JDBC 4.1规范第15.1.2节）：

如果驱动程序不能以请求的类型和并发性返回ResultSet对象，则在确定并发之前确定适当的类型