spring boot get insert row id using jdbctemplate

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
package com.example;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.SQLException;
import java.sql.Statement;

@Service
public class JdbcService {

    @Autowired
    private final JdbcTemplate jdbcTemplate;

    @Transactional
    public long insert(String sqlQuery) throws SQLException {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int insertsCount = this.jdbcTemplate.update(c -> c.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS), keyHolder);
        if (insertsCount == 1) {
            Number assignedKey = keyHolder.getKey();
            if (assignedKey == null) {
                throw new SQLException("Row id getting error.");
            }
            return assignedKey.longValue();  // returns inserted row id
        }
        throw new SQLException("Expected one inserted row.");
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
// import org.springframework.jdbc.core.JdbcTemplate;
// import org.springframework.jdbc.support.GeneratedKeyHolder;
// import org.springframework.jdbc.support.KeyHolder;
// import java.sql.Statement;

String sqlQuery = "INSERT INTO `my_table` (`column_1`, `column_2`) VALUES ('aaa', 'bbb')";

KeyHolder keyHolder = new GeneratedKeyHolder();
int insertsCount = this.jdbcTemplate.update(c -> c.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS), keyHolder);

if (insertsCount == 1) {
    Number assignedKey = keyHolder.getKey();
    long rowId = assignedKey.longValue();
}