Increment id based on last person in sql

Question

Whenever I am inserting the person, I want to get the id of the last person, and automatically increment it by 1 and if it doesn't exist give id of 1. Where do I need to set this up, and what do I need to use? I think it should be done in the getter or?

Person with getters and setters

public class Person {
     int id;
     int name;
 }

Person Data Access Object


public Person addPerson(Person person) {
  String sql = "INSERT INTO people (id, name) values (?, ?);"
  jdbcTemplate.update(sql, person.getId(), person.getName());
  
  return person;
}

Which database are you using? MySQL, Postgres...or something else? — Ervin Szilagyi, Nov 27 '21 at 19:30
For Postgres you can use `SERIAL`. See: https://stackoverflow.com/questions/787722/postgresql-autoincrement — Ervin Szilagyi, Nov 27 '21 at 20:06
If using postgres you can also use sql `INSERT INTO people (name) VALUES (?) RETURNING id`. This will add new row, and tell you what is id (or other returning columns) using just single query statement. — Hitobat, Nov 27 '21 at 20:16
@ErvinSzilagyi I am using serial, but whenever the addPerson returns person in postman it says that the id is 0, but when I fetch again on get request the id is correct. — Dave, Nov 27 '21 at 20:26
@Hitobat when I added `RETURNING id` I get an error `A result was returned when none was expected` — Dave, Nov 28 '21 at 17:42
You need to use jdbcTemplate `query` or `execute` method if you want to use returning. Because update method does not allow return value. — Hitobat, Nov 28 '21 at 18:52
@Hitobat I am using spring boot, what should I return then after `POST` and `PUT` method? — Dave, Nov 28 '21 at 19:02

score 0 · Answer 1 · answered Nov 28 '21 at 19:13

It seems you need to apply back the database-generated ID to the Java object. Since you say you're using Postgres you can use a RETURNING clause to do this.

I think there's a more general way in Spring Boot but I can't remember, so there may be a better answer which covers all databases.

public Person addPerson(Person person) {
  String sql = "INSERT INTO people (name) values (?) RETURNING id";
  Object[] params = new Object[]{ person.getName() };
  int newId = jdbcTemplate.queryForObject(sql, params, Integer.class);
  person.setId(newId);
  return person;
}

Increment id based on last person in sql

1 Answers1