Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle PreparedStatement - NullPointerException for some developers, but not all

Project Overview

I have a project with the following attributes:

  • Database: Oracle 12c
    • 1 table: MY_TABLE
    • 2 columns:
      • ID: PK, VARCHAR2, 32-byte size limit, Default value = SYS_GUID()
      • MY_OBJ: Stores a JSON object for future use
  • Application Framework/Platform: Spring, Java 8u77

The Problem

I am attempting to run the following prepared statement which should output the newly generated key/ID for the inserted object. Unfortunately, I am continually getting a NullPointerException related to the statement.

The curious part of this is that 75% of our team is encountering the same problem, whereas the other 25% of our team has the code working just fine.


The Question(s)

Why would the following code be working properly on one machine, but returning an error on other machines?

How do I make this work on all machines?


Code

    ObjectMapper mapper = new ObjectMapper();       
    KeyHolder generatedKeyHolder = new GeneratedKeyHolder();

    jdbcOracle.update(new PreparedStatementCreator() { //Line 60: This line cited as containing NullPointerException error.

        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement("INSERT INTO MY_TABLE (MY_OBJ) VALUES (?)", new String[] {"ID"});
            try {
                ps.setString(1, mapper.writeValueAsString(myObj));
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            }
            return ps;
        }

    }, generatedKeyHolder);

    System.out.println("Generated Key: " + (String)generatedKeyHolder.getKeys().get("ID"));

Attempted Solutions

  1. We've created alternate tables with sequences instead of defaulting to SYS_GUID().
  2. We tried using System.RETURN_GENERATED_KEY instead of new String[] {"ID"}
  3. We made sure the DB permissions were identical on all machines.
  4. We confirmed that database drivers were identical on all machines.
  5. I can remove the , new String[] {"ID"} argument along with the , generatedKeyHolder argument to get the preparedStatement to run, but since I'm trying to get the generated key this obviously defeats the purpose.

Stack Trace

2016-05-04 00:05:43.148 ERROR 3156 --- [nio-8080-exec-6] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.NullPointerException] with root cause

java.lang.NullPointerException: null
    at oracle.jdbc.driver.AutoKeyInfo.initMetaDataColumnIndexes(AutoKeyInfo.java:423) ~[ojdbc7-12.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.AutoKeyInfo.initMetaData(AutoKeyInfo.java:396) ~[ojdbc7-12.1.0.jar:12.1.0.1.0]
    at oracle.jdbc.driver.OracleReturnResultSet.getMetaData(OracleReturnResultSet.java:77) ~[ojdbc7-12.1.0.jar:12.1.0.1.0]
    at org.springframework.jdbc.core.ColumnMapRowMapper.mapRow(ColumnMapRowMapper.java:52) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.jdbc.core.ColumnMapRowMapper.mapRow(ColumnMapRowMapper.java:48) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:911) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:900) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:900) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
    at com.mysite.impl.MyImpl.save(MyImpl.java:60)
like image 837
ServAce85 Avatar asked May 04 '16 03:05

ServAce85


People also ask

What is a PreparedStatement in SQL?

Interface PreparedStatement. public interface PreparedStatement extends Statement An object that represents a precompiled SQL statement. A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

What is SQLException in Java?

x - the Java input stream that contains the ASCII parameter value SQLException - if parameterIndex does not correspond to a parameter marker in the SQL statement; if a database access error occurs or this method is called on a closed PreparedStatement void setBinaryStream (int parameterIndex, InputStream x, long length) throws SQLException

What is sqlfeaturenotsupportedexception and SQLException?

The default implementation will throw SQLFeatureNotSupportedException parameterIndex - the first parameter is 1, the second is 2, ... SQLException - if parameterIndex does not correspond to a parameter marker in the SQL statement; if a database access error occurs or this method is called on a closed PreparedStatement

What is the difference between SQLException and true and false?

true if the first result is a ResultSet object; false if the first result is an update count or there is no result SQLException - if a database access error occurs; this method is called on a closed PreparedStatement or an argument is supplied to this method


1 Answers

12.1.0.1 has a bug regarding key generation. Use Oracle JDBC driver version 12.1.0.2. Link: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

like image 162
Artin Avatar answered Oct 21 '22 18:10

Artin