java - 如何更新 JdbcRowSet 对象？

Question

我想用一个 JdbcRowSet 对象执行以下 SQL 命令:

INSERT INTO Authors (FirstName,LastName) VALUES ('Sue', 'Smith')

我知道我可以使用 Connection 和 Satements 对象执行，但我想使用接口(interface) JdbcRowSet 执行此操作，因为默认情况下一个 JdbcRowSet 对象是可更新和可滚动的。

我的代码是:

public class JdbcRowSetTest
{
    public static final String DATABASE_URL = "jdbc:mysql://localhost/books";
    public static final String USERNAME = "Ezazel";
    public static final String PASSWORD = "Ezazel";

    public JdbcRowSetTest()
    {
        try
        {
            JdbcRowSet rowSet = new JdbcRowSetImpl();
            rowSet.setUrl( DATABASE_URL );
            rowSet.setUsername( USERNAME );
            rowSet.setPassword( PASSWORD );
            rowSet.setCommand( "INSERT INTO Authors (FirstName,LastName) VALUES ('Sue', 'Smith')" );
            rowSet.execute();
        }
        catch( SQLException e )
        {
        }
    }

    public static void main( String[] args )
    {
        JdbcRowSetTest app = new JdbcRowSetTest ();
    }
}

SQLException 错误:

java.sql.SQLException: Can not issue data manipulation statements with executeQuery().
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.StatementImpl.checkForDml(StatementImpl.java:502)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2224)
    at com.sun.rowset.JdbcRowSetImpl.execute(JdbcRowSetImpl.java:582)
    at JdbcRowSetTest.(JdbcRowSetTest.java:23)
    at JdbcRowSetTest.main(JdbcRowSetTest.java:53)

Answer 1

您不能使用 JdbcRowSet 来执行这样的插入语句。如果这是您想要的，那么您应该使用普通的 Statement 或 PreparedStatement。

RowSet.setCommand仅用于查询:

Sets this RowSet object's command property to the given SQL query. This property is optional when a rowset gets its data from a data source that does not support commands, such as a spreadsheet.
Parameters:
cmd - the SQL query that will be used to get the data for this RowSet object; may be null

如果您真的想使用行集，那么您可以按照可更新 ResultSet 记录的方式更新或插入新行。 :

更新:

to update a column value in the current row. In a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the NAME column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.

rs.absolute(5); // moves the cursor to the fifth row of rs
rs.updateString("NAME", "AINSWORTH"); // updates the
// NAME column of row 5 to be AINSWORTH
rs.updateRow(); // updates the row in the data source

用于插入:

to insert column values into the insert row. An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table using the method insertRow.

rs.moveToInsertRow(); // moves cursor to the insert row
rs.updateString(1, "AINSWORTH"); // updates the
// first column of the insert row to be AINSWORTH
rs.updateInt(2,35); // updates the second column to be 35
rs.updateBoolean(3, true); // updates the third column to true
rs.insertRow();
rs.moveToCurrentRow();

请注意，根据我的经验，javax.sql.rowset 的引用实现通常不会按预期工作。使用普通 JDBC 可能会更好。