CODEWITHSUNDEEP
derbyspring-jdbc
Luigi Blu
Luigi Blu

Reputation: 129

Derby - LAST_INSERT_ID() doesn't work with JDBC

I can't get last insert id using JdbcTemplate with spring framework, before using last_insert_id() function i have searched for a lot of other ways, but doesn't work with it. By the way, my table has 

private static final String insertResponse =
        "INSERT INTO response (" +
        " idReceiver) " +
        "VALUES (?)";

public static void saveResponse(Response response, User user) {
    dataSource = getDataSource();



    JdbcTemplate template = new JdbcTemplate(dataSource);

     //"select id from users where username ='"+ user.getUsername +"'";
    // define query arguments
    Object[] params = new Object[] { 1 };
    int[] responseTypes = new int[] { Types.INTEGER };
    int row = template.update(insertResponse, params, responseTypes);
    if (row >0){
    SqlRowSet rowSet = template.queryForRowSet("SELECT LAST_INSERT_ID() AS id");
    int lastInsertedID=0;
    if (rowSet.next())
        lastInsertedID = rowSet.findColumn("id");
    System.out.println("last insert row is : "+lastInsertedID);
}

My response table was create by this command: 

CREATE TABLE RESPONSE (
   ID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY 
   (START WITH 1, INCREMENT BY 1),
   idReceiver INT NOT NULL
 );

When I run this code my JVM says "hey guy, you have this exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT LAST_INSERT_ID() AS id]; nested exception is java.sql.SQLSyntaxErrorException: Syntax error: Encountered "" at line 1, column 29."

Upvotes: 0

Views: 477

Answers (1)

Luigi Blu
Luigi Blu

Reputation: 129

If someone had my same problem, i've solved it, following this answer..

    KeyHolder keyHolder = new GeneratedKeyHolder();
    template.update(
    new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(java.sql.Connection connection) throws SQLException {
             PreparedStatement ps =
            connection.prepareStatement(insertResponse, new String[] {"id"});
            ps.setInt(1, 1);
            return ps;
          }
        },
        keyHolder);

    System.out.println("last inserted id is "+keyHolder.getKey());

Upvotes: 1

Related Questions