Is there a way to extract primary key(or ROWID) using NamedParameterJdbcTemplate and GeneratedKeyHolder?

Question

I am trying to extract ROWID or the primary key using Spring's NamedParameterJdbcTemplate and GeneratedKeyHolder.

I am trying to do something like this.

MapSqlParameterSource parameters = new MapSqlParameterSource()
                .addValue("param1", value1)
                .addValue("param2", value2);
KeyHolder keyHolder = new GeneratedKeyHolder();
namedParameterJdbcTemplate.update("INSERT INTO TABLE(ID, col1, col2)"
                + "VALUES(TABLE.TABLE_SEQ.NEXTVAL, :param1, :param2)",
                parameters, keyHolder);

After executing above query when I try to do keyHolder.getKey().longValue() it is throwing below exception.

HTTP Status 500 - Request processing failed; nested exception is org.springframework.dao.DataRetrievalFailureException: The generated key is not of a supported numeric type. Unable to cast [oracle.sql.ROWID] to [java.lang.Number]

When I went through this http://docs.oracle.com/cd/B28359_01/java.111/b31224/datacc.htm I understand (i hope i did) that ojdbc is not mapping oracle RowId to java RowId.

Can any one suggest is there any way to extract the key? (Yes it can be done using PreparedStatement but it is making my code bit ugly to read and manipulate on some conditions). Your suggestions are much appreciated.

Goldarned

I don't know if you have the same problem but you check this link https://mcmap.net/q/1014240/-nextval-jdbc-insert-problem . – Abwatt

ROWID is not number type, please try to get string. – Tuner

Answer 1

You have to use this

namedParameterJdbcTemplate.update("INSERT INTO TABLE(ID, col1, col2)"
            + "VALUES(TABLE.TABLE_SEQ.NEXTVAL, :param1, :param2)",
            parameters, keyHolder, new String[]{"ID"});

Foretaste

its not working for table having more than 7 columns do you have any solution for this issue – Varistor

@BhargavModi What's the exception? – Foretaste

Can you show your table structure and how you use NamedParameterJdbcTemplate? – Foretaste

Can you remove "RETURNING INPUTREQUESTID"? – Foretaste

This is probably only typo, but I notice you miss the '(' after VALUES – Foretaste

the query is cured and corrected but if I use 7 columns for insert than it's working fine as expected.but if I make it to 8 column than it give exception – Varistor

The exception seems to come from the jdbc driver. Which version of Oracle driver do you use? Have you tried to up to newer version? – Foretaste

oracle 11 g jdbc driver and I had found that issue is related with jdbc but I could not find the solution for that or m not allowed to change it to higher version so searching through programming solution. [here the useful link similar to my problem] (https://mcmap.net/q/769276/-jdbc-oracle-arrayindexoutofboundsexception/2749470) – Varistor

Answer 2

Here is a fully working example: Assuming Database is Oracle and column name which store generated Id is "GENERATED_ID" ( Can be any name)

       public Integer insertRecordReturnGeneratedId(final MyObject obj)
        {
        final String INSERT_QUERY = "INSERT INTO MY_TABLE  VALUES(GENERATED_ID_SEQ.NEXTVAL, :param1, :param2)";
        try
            {
                MapSqlParameterSource parameters = new MapSqlParameterSource().addValue( "param1", obj.getField1() ).addValue( "param2",  obj.getField1() ) ;
                final KeyHolder holder = new GeneratedKeyHolder();
                this.namedParameterJdbcTemplate.update( INSERT_QUERY, parameters, holder, new String[] {"GENERATED_ID" } );
                Number generatedId = holder.getKey();
               // Note: USING holder.getKey("GENERATED_ID") IS ok TOO.
                return generatedId.intValue();
            }
            catch( DataAccessException dataAccessException )
            {
    }
    }

Haga