Fetching id field of an entity in the SQL Transaction

Question

I have the following schema.

Person(pid, pname)
Beer(bid, bname)
Likes(pid,bid)

I would like to insert a likes item. However, I am accepting the following format for the new users : (Pid, pname, bid, bname).

I would like to create a transaction for that to avoid conflict ( This is a highly simplified version of my real problem but the issue is the same). In my Person table, I set pid Auto-Increment(or Serial in Postgresql). Also the same goes for bid.

I have stuck in a point where I know the Person does not exist but the beer exists. So, I have to create a Person, then add an entity to Likes relation.

As far as I know, when I use the Autocommit(false) in dB, the transaction won't save until the commit. So, should I change the db design:

Change the auto-increment field to a normal integer, not null field.
In the transaction, after the autoCommit(false) has begun, read the last entry of the person
Increment it by one while creating the new person
Then create likes relation

Or, is there any other way around or do I miss something about transactions?

Here is what I have done so far:

try {
    String add_person_sql = "INSERT INTO Person (name) VALUES(?)";
    PreparedStatement add_person_statement = mydb.prepareStatement(add_person_sql);

    String add_likes_sql = "INSERT INTO Likes (pid, bid) VALUES(?, ?)";
    PreparedStatement add_likes_statement = mydb.prepareStatement(add_likes_sql);

    mydb.setAutoCommit(false);

    add_person_statement.setString(1, pname);
    // The problem is, without saving the person I cannot know the id of the person

    // AFAIK, this execution is not finished until commit occurs
    add_person_statement.executeQuery();
    // How can I fetch person's id
    add_likes_statement.setString(1, pid); 
    add_likes_statement.setString(2, bid);

    add_likes_statement.executeQuery();

    mydb.commit();
}
catch(Exception e){
    System.out.println(e);
    mydb.rollback();
}

"*Increment it by one while creating the new person*" do **NOT** do that. It's slow and what's even worse, it won't work properly. — , Apr 16 '19 at 06:47

score 1 · Accepted Answer · 2019-04-16T06:59:21.080

1

You can tell JDBC to return the generated ID from the insert statement, then you can use that ID to insert into the likes table:

mydb.prepareStatement(add_person_sql, new String[]{"pid"});

The second parameter tells the driver to return the generated value for the pid column.

Alternatively you can use

mydb.prepareStatement(add_person_sql, Statement.RETURN_GENERATED_KEYS);

that tells the driver to detect the auto increment columns.

Then run the insert using executeUpdate()

add_person_statement.setString(1, pname);
add_person_statement.executeUpdate();

int newPid = -1;
ResultSet idResult = add_person.getGeneratedKeys();
if (idResult.next()) {
   newPid = idResult.getInt(1);
}

add_likes_statement.setString(1, newPid); 
add_likes_statement.setString(2, bid);

add_likes_statement.executeUpdate();

mydb.commit();

edited Apr 16 '19 at 06:59

answered Apr 16 '19 at 06:51

So, the jdbc can return the value, even if the transaction is not finished(not committed) – qds52625 Apr 16 '19 at 06:52
@qds52625: yes. it's essentially using an `INSERT INTO .... RETURNING ...` statement https://www.postgresql.org/docs/current/sql-insert.html#id-1.9.3.152.5 – Apr 16 '19 at 06:55
Thanks, @a_horse_with_no_name. I have one more thing to ask: `CREATE TABLE Person( pid SERIAL, name TEXT, PRIMARY KEY (pid) );` When I try to insert in this table, JDBC returns me an error: detailMessage "ERROR: duplicate key value violates unique constraint "Person_pkey"\n Detail: Key (pid)=(1) already exists." (id=66) – qds52625 Apr 16 '19 at 07:10
@qds52625: then you inserted into the table by providing the ID value yourself and the sequence is out of sync. https://stackoverflow.com/questions/244243 – Apr 16 '19 at 07:13
Ok, thanks to you I have figured out. In the population step of the database, I was setting ids. I let the job to postgresql and it works fine. Much appreciated – qds52625 Apr 16 '19 at 07:19

Fetching id field of an entity in the SQL Transaction

1 Answers1