Description:
When calling ResultSet.getHoldability() using the MySQL Connector/J driver, the following exception is thrown:
java.sql.SQLFeatureNotSupportedException
However, I don't have this problem with MariaDB Connector/J. I have had a look at the MySQL Connector/J source code as well as the MariaDB Connector/J source code, and I have found differences and I think this is a bug that should be worth reporting to you.
First I analyze MySQL, in com.mysql.cj.jdbc.result.ResultSetImpl#getHoldability, did not make any processing is directly thrown an exception.
@Override
public int getHoldability() throws SQLException {
throw SQLError.createSQLFeatureNotSupportedException();
}
However, I actually don't think this behavior is consistent with MySQL's philosophy in implementing com.mysql.cj.jdbc.StatementImpl#getResultSetHoldability
@Override
public int getResultSetHoldability() throws SQLException {
return java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT;
}
From this we can see that if MySQL, without taking anything else into account, can simply change the com.mysql.cj.jdbc.result.ResultSetImpl#getHoldability code to
@Override
public int getHoldability() throws SQLException {
return java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT;
}
This ensures contextual consistency without confusing the user with "Why is it that I have no problem with System.out.println(stmt.getResultSetHoldability());, but I have a problem with System.out.println(rs.getHoldability()); has a problem, which is really strange!"
This change only involves a few lines of code, it won't have any impact on the overall system, and not throwing exceptions will also enhance the user experience.
Similarly, in my analysis of the Mariadb Connector/J I found that in org.mariadb.jdbc.client.result.Result#getHoldability they use the method I described above
@Override
public int getHoldability() {
return ResultSet.HOLD_CURSORS_OVER_COMMIT;
}
This change would:
Ensure consistency with the behavior of Statement.getResultSetHoldability()
Avoid unexpected exceptions for developers relying on the JDBC API
Align MySQL Connector/J behavior with other JDBC drivers such as MariaDB Connector/J
Require minimal code changes with no side effects on other parts of the system
As developers, we aim to make systems not only correct but also intuitive and pleasant to use. I believe this small change would contribute to a better overall experience for the MySQL Connector/J user base.
Thank you for your time and your continued work on making MySQL better.
Best regards,
Ce Lyu
How to repeat:
public static void main(String[] args) throws Exception{
Connection con = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb0?user=root&password=1234&useServerPrepStmts=false&allowMultiQueries=true&rewriteBatchedStatements=false&tinyInt1isBit=false&cacheCallableStmts=false");
stmt = con.createStatement(1003, 1007, 1);
System.out.println(stmt.getResultSetHoldability());
try {
stmt.executeUpdate("DROP TABLE IF EXISTS table0_3;");
} catch (Exception e) {
System.out.println(e);
}
try {
stmt.executeUpdate("CREATE TABLE table0_3 (Id BIGINT PRIMARY KEY, Value0 DOUBLE, Value1 INT);");
} catch (Exception e) {
System.out.println(e);
}
pstmt = con.prepareStatement("INSERT INTO table0_3 (Id, Value0, Value1) VALUES (?, ?, ?);");
pstmt.setObject(1, 1L);
pstmt.setObject(2, 1.1);
pstmt.setObject(3, Integer.MAX_VALUE);
try {
pstmt.executeUpdate();
} catch (Exception e) {
System.out.println(e);
}
stmt.addBatch("INSERT INTO table0_3 (Id, Value0, Value1) VALUES (2, 2.2, -2147483648)");
stmt.addBatch("INSERT INTO table0_3 (Id, Value0, Value1) VALUES (3, 3.3, 1000)");
try {
stmt.executeBatch();
} catch (Exception e) {
System.out.println(e);
}
try {
rs = stmt.executeQuery("SELECT * FROM table0_3;");
} catch (Exception e) {
System.out.println(e);
}
System.out.println(rs.getHoldability());
}
Suggested fix:
@Override
public int getHoldability() throws SQLException {
throw SQLError.createSQLFeatureNotSupportedException();
}
change to
@Override
public int getResultSetHoldability() throws SQLException {
return java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT;
}
Description: When calling ResultSet.getHoldability() using the MySQL Connector/J driver, the following exception is thrown: java.sql.SQLFeatureNotSupportedException However, I don't have this problem with MariaDB Connector/J. I have had a look at the MySQL Connector/J source code as well as the MariaDB Connector/J source code, and I have found differences and I think this is a bug that should be worth reporting to you. First I analyze MySQL, in com.mysql.cj.jdbc.result.ResultSetImpl#getHoldability, did not make any processing is directly thrown an exception. @Override public int getHoldability() throws SQLException { throw SQLError.createSQLFeatureNotSupportedException(); } However, I actually don't think this behavior is consistent with MySQL's philosophy in implementing com.mysql.cj.jdbc.StatementImpl#getResultSetHoldability @Override public int getResultSetHoldability() throws SQLException { return java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT; } From this we can see that if MySQL, without taking anything else into account, can simply change the com.mysql.cj.jdbc.result.ResultSetImpl#getHoldability code to @Override public int getHoldability() throws SQLException { return java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT; } This ensures contextual consistency without confusing the user with "Why is it that I have no problem with System.out.println(stmt.getResultSetHoldability());, but I have a problem with System.out.println(rs.getHoldability()); has a problem, which is really strange!" This change only involves a few lines of code, it won't have any impact on the overall system, and not throwing exceptions will also enhance the user experience. Similarly, in my analysis of the Mariadb Connector/J I found that in org.mariadb.jdbc.client.result.Result#getHoldability they use the method I described above @Override public int getHoldability() { return ResultSet.HOLD_CURSORS_OVER_COMMIT; } This change would: Ensure consistency with the behavior of Statement.getResultSetHoldability() Avoid unexpected exceptions for developers relying on the JDBC API Align MySQL Connector/J behavior with other JDBC drivers such as MariaDB Connector/J Require minimal code changes with no side effects on other parts of the system As developers, we aim to make systems not only correct but also intuitive and pleasant to use. I believe this small change would contribute to a better overall experience for the MySQL Connector/J user base. Thank you for your time and your continued work on making MySQL better. Best regards, Ce Lyu How to repeat: public static void main(String[] args) throws Exception{ Connection con = null; Statement stmt = null; PreparedStatement pstmt = null; ResultSet rs = null; con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb0?user=root&password=1234&useServerPrepStmts=false&allowMultiQueries=true&rewriteBatchedStatements=false&tinyInt1isBit=false&cacheCallableStmts=false"); stmt = con.createStatement(1003, 1007, 1); System.out.println(stmt.getResultSetHoldability()); try { stmt.executeUpdate("DROP TABLE IF EXISTS table0_3;"); } catch (Exception e) { System.out.println(e); } try { stmt.executeUpdate("CREATE TABLE table0_3 (Id BIGINT PRIMARY KEY, Value0 DOUBLE, Value1 INT);"); } catch (Exception e) { System.out.println(e); } pstmt = con.prepareStatement("INSERT INTO table0_3 (Id, Value0, Value1) VALUES (?, ?, ?);"); pstmt.setObject(1, 1L); pstmt.setObject(2, 1.1); pstmt.setObject(3, Integer.MAX_VALUE); try { pstmt.executeUpdate(); } catch (Exception e) { System.out.println(e); } stmt.addBatch("INSERT INTO table0_3 (Id, Value0, Value1) VALUES (2, 2.2, -2147483648)"); stmt.addBatch("INSERT INTO table0_3 (Id, Value0, Value1) VALUES (3, 3.3, 1000)"); try { stmt.executeBatch(); } catch (Exception e) { System.out.println(e); } try { rs = stmt.executeQuery("SELECT * FROM table0_3;"); } catch (Exception e) { System.out.println(e); } System.out.println(rs.getHoldability()); } Suggested fix: @Override public int getHoldability() throws SQLException { throw SQLError.createSQLFeatureNotSupportedException(); } change to @Override public int getResultSetHoldability() throws SQLException { return java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT; }