Description:
When using CONCUR_UPDATABLE to query a view with a derived value , NPE occurs in UpdatableResultSet causing SQLException
this is because when a view with a derived value is used, Resultmetadata field dbname is null for derived columns
thus in UpdatableResultSet.java checkUpdatability() causes NPE thus causing SQLException
How to repeat:
create table user (id int,name varchar(10));
insert into user values (1,'a');
create table age (id int,age int);
insert into age values (1,20);
create view user_age as select name,ifnull(age,0) age from user inner join age on user.id = age.id;
then run the below standalone code
package com;
import java.sql.*;
import java.util.logging.Logger;
public class Test {
public static void main(String[] args) {
try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "user", "pass");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = stmt.executeQuery("select * from user_age")
) {
if (resultSet.next())
System.out.println(resultSet.getString("age"));
} catch (SQLException sqle) {
sqle.printStackTrace();
}
}
}
output will be
java.sql.SQLException
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)
at com.Test.main(Test.java:14)
on further debugging found that this was caused becuase of NPE in UpdatableResultSet.java 286 as the otherDbName was null
Description: When using CONCUR_UPDATABLE to query a view with a derived value , NPE occurs in UpdatableResultSet causing SQLException this is because when a view with a derived value is used, Resultmetadata field dbname is null for derived columns thus in UpdatableResultSet.java checkUpdatability() causes NPE thus causing SQLException How to repeat: create table user (id int,name varchar(10)); insert into user values (1,'a'); create table age (id int,age int); insert into age values (1,20); create view user_age as select name,ifnull(age,0) age from user inner join age on user.id = age.id; then run the below standalone code package com; import java.sql.*; import java.util.logging.Logger; public class Test { public static void main(String[] args) { try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "user", "pass"); Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet resultSet = stmt.executeQuery("select * from user_age") ) { if (resultSet.next()) System.out.println(resultSet.getString("age")); } catch (SQLException sqle) { sqle.printStackTrace(); } } } output will be java.sql.SQLException at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) at com.Test.main(Test.java:14) on further debugging found that this was caused becuase of NPE in UpdatableResultSet.java 286 as the otherDbName was null