post/2022-06/jdbctemplate-id/

How to Create an ID with AUTO_INCREMENT on Oracle? AUTO_INCREMENT is a feature that allows you to insert rows into your database using sequential numbers. It eliminates the need for an IDENTITY column on your table, but if you want to set auto_increment values in your schema, you have to manually ID with AUTO_INCREMENT on Oracle. oracle auto_increment -- For oracle 12c or later -- auto inc create table t1 ( c1 NUMBER GENERATED by default on null as IDENTITY, c2 VARCHAR2(10) ); -- More options for initial value and increment value create table t1 ( c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1), c2 VARCHAR2(10) ); AUTO_INCREMENT is one of the database features which enables the developers to create a primary key field in MySQL. This column can be used for database operations like inserting statements and select. The primary key table can consist of only one value per row.

I am not familiar with Oracle and I understand that it does not support auto incremental ID like mysql.

But Oracle can do it via SEQUENCE.

Create SEQUENCE

CREATE SEQUENCE MY_SEQUENCE_ID
    MINVALUE 1
    MAXVALUE 9999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Get the auto-incrementing ID before inserting data

@SuppressWarnings("unchecked")
@Test
@Rollback(false)
@Transactional
public void test() throws SQLException {

NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

// Generate the next ID
Long nextId = namedParameterJdbcTemplate.queryForObject("SELECT MY_SEQUENCE_ID.nextval FROM DUAL", Collections.EMPTY_MAP, Long.class);

      String sql = "INSERT INTO TEST_USER(ID, NAME) VALUES(:id, :name)";

      Map<String, Object> params = new HashMap<>();
      params.put("id", nextId);
      params.put("name", "Hello");

      int rowsAffected = namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource(params));

      log.info("rowsAffected = {}, id={}", rowsAffected, nextId);

      // Output: rowsAffected = 1, id=12
}

springcloud-community / springcloud-comment

post/2022-06/jdbctemplate-id/ #2

How to get the auto-incremented ID when using JdbcTemplate - Spring Cloud

Create SEQUENCE

Get the auto-incrementing ID before inserting data