Derby INSERT SELECT null pointer exception
I'm writing a Java application that's working with Apache Derby via JDBC. I'm having problems with the code in the following snippet:
byte md5[] = md5sum(file);
PreparedStatement s = con.prepareStatement("INSERT INTO input_files (job_ID, hash) SELECT job_id, ? FROM job WHERE job_name = ?");
s.setBytes(1, md5);
s.setString(2, jobName);
s.executeUpdate();
s.close();
This inserts into the following tables:
CREATE TABLE input_files
(
hash char(16) for bit data,
job_id integer REFERENCES job,
PRIMARY KEY(job_id, hash)
);
CREATE TABLE job
(
job_id integer PRIMARY KEY GENERATED ALWAYS as IDENTITY,
job_name character varying(50) UNIQUE NOT NULL,
#other fields
);
The idea is that there is an internal integer job_id
which is used internally by the database, but we want to reference it as much as possible with the human-readable String job_name
This consistently throws the following exception.
java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(UnknownSource)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at [line 3 of snippet above]
As you can see, this is thrown from the line where the prepared statement is created - it doesn't get as far as executing it. What am I doing wrong? The exact same prepared statement works fine with PostgreSQL.
[edit] Derby.log:
----------------------------------------------------------------
2010-08-18 08:47:08.779 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.6.1.0 - (938214): instance a816c00e-012a-8461-611c-0000046700d0
on database directory /path/to/myDatabase with class loader sun.misc.Launcher$AppClassLoader@6d6f0472
Database Class Loader started - derby.database.classpath=''
2010-08-18 08:47:12.067 GMT Thread[SwingWorker-pool-1-thread-1,5,main] (XID = 316), (SESSIONID = 1), (DATABASE = myDatabase), (DRDAID = null), Cleanup action starting
2010-08-18 08:47:12.067 GMT Thread[SwingWorker-pool-1-thread-1,5,main] (XID = 316), (SESSIONID = 1), (DATABASE = myDatabase), (DRDAID = null), Failed Statement is: INSERT INTO input_files (job_id, hash) SELECT job_id, ? FROM job WHERE job_name = ?
java.lang.NullPointerException
at org.apache.derby.impl.sql.compile.BitTypeCompiler.storable(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultC开发者_如何学Goolumn.checkStorableExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(Unknown Source)
at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at [line 3 of snippet]
Cleanup action completed
2010-08-18 08:47:12.084 GMT:
Shutting down instance a816c00e-012a-8461-611c-0000046700d0 with class loader sun.misc.Launcher$AppClassLoader@6d6f0472
----------------------------------------------------------------
and unrolled exception:
java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at [line 3 of snippet above]
Caused by: java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 26 more
Caused by: java.lang.NullPointerException
at org.apache.derby.impl.sql.compile.BitTypeCompiler.storable(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(Unknown Source)
at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 19 more
SQLState: XJ001
Error code: 0
Message: Java exception: ': java.lang.NullPointerException'.
Cause: java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at [line 3 of snippet above]
Caused by: java.lang.NullPointerException
at org.apache.derby.impl.sql.compile.BitTypeCompiler.storable(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(Unknown Source)
at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 19 more
Cause: java.lang.NullPointerException
at org.apache.derby.impl.sql.compile.BitTypeCompiler.storable(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(Unknown Source)
at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at [line 3 of snippet above]
A null pointer exception in this context - especially if the same JDBC code works with another DBMS - looks like you've hit a bug in the Derby JDBC driver. Even if you'd made a mistake and passed erroneous data or a null pointer, the driver should be detecting that and reporting an error, not crashing.
I suspect you won't be able to jam the literal hash code directly into the select column list, because Derby doesn't understand that you want to put a literal value there, rather than a column name.
A reasonable workaround might be to re-code your application to perform the INSERT statement from inside a loop which is reading the values from the SELECT. Something along the lines of:
updateStatement = prepareStatement("insert into input_files (job_id, hash) values (?,?)")
ResultSet rs = executeQuery("select job_id from job where job_name = ?")
while (rs.next())
updateStatement.setString(1, rs.getString(1))
updateStatement.setBytes(2, md5sum)
updateStatement.executeUpdate()
PreparedStatement s = con.prepareStatement("INSERT INTO input_files (job_ID, hash) SELECT job_id, ? FROM job WHERE job_name = ?");
"job_ID" vs "job_id" match the case and see if that corrects the problem.
精彩评论