0

I'm trying to get acquainted with JDBC basics throuhg Oracle's "The Java™ Tutorials" and now I got stuck right here: https://docs.oracle.com/javase/tutorial/jdbc/basics/jdbcrowset.html.

For my examples, I exploit MySQL Server with the latest version of Connector/J (mysql-connector-java-8.0.16.jar). So far, the driver worked as expected. It gets connection to MySQL Server, creates database, tables, populates them with data and fills ordinary ResultSet objects with the retrieved data. However, as soon as I try to create JdbcRowSet object and perform execute(), I get SQL exception reporting: "No suitable driver found..."

So, now I'm in doubts: whether JdbcRowSet (as well as CachedRowSet / JoinRowSet / FilteredRowSet / WebRowSet) is just not implemented by Connector/J driver, or I'm doing something wrong? Or maybe this functionality is no longer supported in JDK 11?

Here is an example:

import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetProvider;
import java.sql.*;
import java.util.Properties;

public class JdbcRowSetTest {

    public static void main(String[] args) {
        String connectionURL = 
            "jdbc:mysql://localhost:3306/testdb?serverTimezone=Europe/Moscow";
        String userName = "root";
        String password = "root";

        try {
            Properties connectionProps = new Properties();
            connectionProps.put("user", userName);
            connectionProps.put("password", password);

            System.out.println("Connect to database...");
            Connection conn = 
                DriverManager.getConnection(connectionURL, connectionProps);

            System.out.println("Retrieve and process data using ResultSet...");
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from COFFEES");
            while (rs.next()) {
                String coffeeName = rs.getString("COF_NAME");
                float price = rs.getFloat("PRICE");
                System.out.println(coffeeName + ", " + price);
            }

            System.out.println("Now, update the data using JdbcRowSet...");
            JdbcRowSet jdbcRs = RowSetProvider.newFactory().createJdbcRowSet();
            jdbcRs.setUrl(connectionURL);
            jdbcRs.setUsername(userName);
            jdbcRs.setPassword(password);
            jdbcRs.setCommand("select * from COFFEES");
            // it's where SQLException is thrown
            jdbcRs.execute();

            jdbcRs.absolute(2);
            jdbcRs.updateFloat("PRICE", 10.99f);
            jdbcRs.updateRow();

            System.out.println("After updating the 2nd row:");
            //... view updated table

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

And it's the output:

Connect to database...
Retrieve and process data using ResultSet...
Colombian, 7.99
Espresso, 9.99
Now, update the data using ResultSet...
java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/testdb?serverTimezone=Europe/Moscow
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
    at java.sql.rowset/com.sun.rowset.JdbcRowSetImpl.connect(JdbcRowSetImpl.java:643)
    at java.sql.rowset/com.sun.rowset.JdbcRowSetImpl.prepare(JdbcRowSetImpl.java:654)
    at java.sql.rowset/com.sun.rowset.JdbcRowSetImpl.execute(JdbcRowSetImpl.java:556)
    at JdbcRowSetTest.main(JdbcRowSetTest.java:37)

Updated (possible solution):

As the commentators mentioned bellow, the problem described here has nothing to do with MySQL implementing rowset or not. For me, it seems to be just a bug of JDK 11.0.1, because as soon as I updated for the newer version of JDK 12.0.1, the problem dissipated, and now JdbcRowSet object is created without any MySQLException.

escudero380
  • 536
  • 2
  • 7
  • 25
  • Have you tried simply `new JdbcRowSetImpl(conn); jdbcRs.setCommand("select * from COFFEES"); jdbcRs.execute();`? Using the connection created by the `DriverManager`? – nortontgueno May 19 '19 at 15:17
  • it doesn't work either (actually, `new JdbcRowSetImpl(conn)` doesn't get compiled in jdk 11. – escudero380 May 19 '19 at 15:24
  • That's weird, checking the [source code](http://cr.openjdk.java.net/~malenkov/8022746.8.1/jdk/src/share/classes/com/sun/rowset/JdbcRowSetImpl.java.html) of `JdbcRowSetImpl` I can see this constructor and also a call to `DriverManager` – nortontgueno May 19 '19 at 15:27
  • Try to debug the implementation and see if there is something wrong going on – nortontgueno May 19 '19 at 15:27
  • Per [this comment](https://stackoverflow.com/questions/56090869/java-sql-sqlexception-resultset-is-not-updatable-with-jdbcrowset#comment98822793_56090869) you might try MySQL Connector/J version 5.1.47 and see if it works any better. – Gord Thompson May 19 '19 at 16:01
  • I tried this code with Connector/J 5.1.47. The same negative result. – escudero380 May 19 '19 at 16:49
  • 2
    This has nothing to do with MySQL implementing rowset or not (for the record, it doesn't, rowsets are - in my opinion - a dead specification and you'd probably be better off using something else), as this uses the reference implementation included in Java. As far as I can tell this might be a classloading problem introduced with the module system (or another problem related to modules), but I would need to do some testing to be sure. – Mark Rotteveel May 19 '19 at 17:21
  • "No suitable driver found" suggests that the JDBC driver isn't on the class path. I see Marks' comment about the module system but that doesn't change class loading. – Alan Bateman May 20 '19 at 06:26
  • What type of JDBC driver are we talking about? If you assume, MySQL Connector/J driver is not on the classpath, then it's wrong assumption, because otherwise connection with the database would NOT have been established at all, and I would NOT have been able to retrieve and process the data while using ordinary ResultSet object. – escudero380 May 20 '19 at 07:39
  • In the Oracle's Tutorials it's said that each JDBC driver might have its own JdbcRowSet implementation. What I have installed at the moment on my local machine is: Java SE Development Kit 11.0.1 + MySQL Server 8.0.16 + Connector/J 8.0.16 driver. Should I use some other extra libriries to get things working and what are these libriries are? – escudero380 May 20 '19 at 07:40
  • @AlanBateman Given the code first successfully connects using `DriverManager` directly, and then doesn't through the rowset, it would be indicative of a classloader issue as `DriverManager` checks classloader access from its caller. And given the rowset reference implementation is (probably) loaded using the bootstrap classloader and not with the application classloader, it might mean that drivers are not accessible (at least: that is my working theory). – Mark Rotteveel May 20 '19 at 10:21
  • @escudero380 Except for the Oracle JDBC driver, I'm not aware of any other JDBC driver that includes its own rowset implementation. – Mark Rotteveel May 20 '19 at 10:28
  • 2
    I just tested my theory, but it didn't work out. Both with rowset and with drivermanager directly it was able to use the JDBC driver Are you using a module-info.java? If so, what is its content? – Mark Rotteveel May 20 '19 at 10:50
  • @MarkRotteveel No, I didn't place the code above into any package or module. What do you mean by that: _"Are you using a module-info.java?"_ – escudero380 May 20 '19 at 11:21
  • So guys, I've just tried to update for the newer version of JDK 12.0.1, and now it works as expected...well, almost... the JdbcRowSet is created, but doesn't want to be updated reporting that "ResultSet is not updatable". After some researching, I found out that this problem might be another bug, introduced when the version 5.x of Connector/J was tweaked for version 8.x. (see [link](https://stackoverflow.com/questions/56090869/java-sql-sqlexception-resultset-is-not-updatable-with-jdbcrowset) for possible solution). – escudero380 May 21 '19 at 08:34

0 Answers0