Fetch primary key generated by sequence using jdbctemplate / jdbc

Question

The situation is as follows:

I have one table(GUI_FILTER) and one sequence(GUI_FILTER_SEQ) I am inserting data using springjdbctemplate, also can use core jdbc as well. In the insert query I am retrieving sequence.nextval and also using a keyholder to fetch that back to java as below. I have tried several ways like using

returnRowCount = namedJdbctmplt.update(sql.toString(), namedParameters, keyHolder, keyColumnNames);

returnRowCount = jdbctemplate.update(psc, keyHolder);

both are failing,

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00936: missing expression



StringBuilder sql = new StringBuilder();
        sql.append(" Insert Into MAPS_AMPS.AMPS_GUI_FILTER ");
        sql.append(" (FILTER_PRESET_ID,USER_ID,FILTER_PAGE_TXT,FILTER_DISPLY_NM,FILTER_DATA_TXT,ROW_CREATE_TMS,LAST_UPDT_TMS ) ");
        sql.append(" values(Select MAPS_AMPS.AMPS_GUI_FILTER_SEQ.NEXTVAL,?,?,?,?,CURRENT_TIMESTAMP ,null from dual) ");
        final String sqlQuery = sql.toString();

If I remove the sequence generation part Select MAPS_AMPS.AMPS_GUI_FILTER_SEQ.NEXTVAL to something constant like 30 or so it is working fine.

Is there any way to get sequence value as insert command and also at the same time fetch the primary key using jdbc ?

Complete code is as below.

@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT, rollbackFor = DataAccessException.class)
public Long addFilterPreset(final String userId, final String filterPageCd,
        final String filterPresetDisplayName,
        final String serializedTaskBrowserSearchObj) {

    logger.debug("Start of FilterPresetDaoImpl - addFilterPreset() method");

    if(!countSavedFilterPresets(userId,filterPageCd))
        return null;

    //StringBuilder sql = new StringBuilder();
    Map<String, Object> parameterMap = new HashMap<String, Object>();

    //sql.append(" Insert Into MAPS_AMPS.AMPS_GUI_FILTER ");
    //sql.append(" (FILTER_PRESET_ID,USER_ID,FILTER_PAGE_TXT,FILTER_DISPLY_NM,FILTER_DATA_TXT,ROW_CREATE_TMS,LAST_UPDT_TMS ) ");
    //sql.append( "Values( Select MAPS_AMPS.AMPS_GUI_FILTER_SEQ.NEXTVAL,:userId,:filterPageCd,:filterPresetDisplayName,:filterPresetData,CURRENT_TIMESTAMP ,null)" );

    /*parameterMap.put("userId", userId);
    parameterMap.put("filterPageCd", filterPageCd);
    parameterMap.put("filterPresetDisplayName", filterPresetDisplayName);
    parameterMap.put("filterPresetData", serializedTaskBrowserSearchObj);
    */
    StringBuilder sql = new StringBuilder();
    sql.append(" Insert Into MAPS_AMPS.AMPS_GUI_FILTER ");
    sql.append(" (FILTER_PRESET_ID,USER_ID,FILTER_PAGE_TXT,FILTER_DISPLY_NM,FILTER_DATA_TXT,ROW_CREATE_TMS,LAST_UPDT_TMS ) ");
    sql.append(" values(Select MAPS_AMPS.AMPS_GUI_FILTER_SEQ.NEXTVAL,?,?,?,?,CURRENT_TIMESTAMP ,null from dual) ");
    final String sqlQuery = sql.toString();

    logger.info("SQL Add Filter Preset Query : {}", sqlQuery);
    int returnRowCount = 0;
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final String[] keyColumnNames = {"FILTER_PRESET_ID"};
    SqlParameterSource namedParameters = new MapSqlParameterSource(parameterMap);

    PreparedStatementCreator psc = new PreparedStatementCreator()   {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection)
                throws SQLException     {
            PreparedStatement ps = connection.prepareStatement(sqlQuery, keyColumnNames);
            ps.setString(1, userId);
            ps.setString(2, filterPageCd);
            ps.setString(3, filterPresetDisplayName);
            ps.setString(4, serializedTaskBrowserSearchObj);
            return ps;
        }
    };

    try{
        long startValue = System.currentTimeMillis();
        //returnRowCount = namedJdbctmplt.update(sql.toString(), namedParameters, keyHolder, keyColumnNames);
        returnRowCount = jdbctemplate.update(psc, keyHolder);
        long endValue = System.currentTimeMillis();
        logger.info("Filter Preset added in {} seconds", (endValue - startValue)/1000);
    }catch(Exception e){
        logger.info("Exception while adding filter preset - " + "userId : "+userId+" filterPageCd : "+filterPageCd +" filterPresetData : "+serializedTaskBrowserSearchObj);
    }finally{
        logger.debug("End of FilterPresetDaoImpl - addFilterPreset() method");
    }
    if (returnRowCount > 0)
        return keyHolder.getKey().longValue();
    return null;
}

Error

score 1 · Answer 1 · edited May 23 '17 at 10:29

You don't need the VALUES keyword when inserting a result from query.

StringBuilder sql = new StringBuilder();
        sql.append(" Insert Into MAPS_AMPS.AMPS_GUI_FILTER ");
        sql.append(" (FILTER_PRESET_ID,USER_ID,FILTER_PAGE_TXT,FILTER_DISPLY_NM,FILTER_DATA_TXT,ROW_CREATE_TMS,LAST_UPDT_TMS ) ");
        sql.append(" (Select MAPS_AMPS.AMPS_GUI_FILTER_SEQ.NEXTVAL,?,?,?,?,CURRENT_TIMESTAMP ,null from dual) ");
        final String sqlQuery = sql.toString();

To get an inserted value try using RETURNING clause: some details are discussed in this post Oracle's RETURNING INTO usage in Java (JDBC, Prepared Statement)

However, you can use RETURNING clause for INSERT only if you use the VALUES keyword, so you can't have both. As it's discussed here: PLSQL Insert into with subquery and returning clause (Oracle)

Fetch primary key generated by sequence using jdbctemplate / jdbc

1 Answers1