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.

I don't know if you have the same problem but you check this link http://stackoverflow.com/questions/4496336/nextval-jdbc-insert-problem . — Calin Andrei, Jul 12 '12 at 12:13

Wins · Accepted Answer · 2015-01-22T14:46:27.387

21

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"});

edited Jan 22 '15 at 14:46

answered Jul 13 '12 at 03:41

Wins

3,420
4
36
70

3

its not working for table having more than 7 columns do you have any solution for this issue – Bhargav Modi Jan 22 '15 at 09:47
1

@BhargavModi What's the exception? – Wins Jan 22 '15 at 14:39
Can you show your table structure and how you use NamedParameterJdbcTemplate? – Wins Jan 23 '15 at 17:08
Can you remove "RETURNING INPUTREQUESTID"? – Wins Jan 26 '15 at 03:39
This is probably only typo, but I notice you miss the '(' after VALUES – Wins Jan 27 '15 at 13:44
1

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 – Bhargav Modi Jan 27 '15 at 14:20
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? – Wins Jan 27 '15 at 14:24
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] (http://stackoverflow.com/q/277744/2749470) – Bhargav Modi Jan 27 '15 at 14:33

supernova · Answer 2 · 2016-08-06T19:34:29.667

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 )
            {
    }
    }

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

2 Answers2

Linked