How to get auto generated Id after inserting to database?

2,330 views
Skip to first unread message

Majid Azimi

unread,
Mar 3, 2013, 8:24:05 AM3/3/13
to sq...@googlegroups.com
How to get auto generated Id after inserting to database? I have a DAO class containing this function:

public void addUser(User newUser) {
        String queryAddUser = "INSERT INTO tbl_user (user_login_name, user_password, user_name) VALUES (:user_login_name, :user_password, :user_name)";

sql2o.createQuery(queryAddUser, true)
.addColumnMapping("user_login_name", "userLoginName")
.addColumnMapping("user_password", "userPassword")
.addColumnMapping("user_name", "userName")
.addParameter("user_login_name", newUser.getUserLoginName())
.addParameter("user_password", newUser.getUserPassword())
.addParameter("user_name", newUser.getUserName())
.executeUpdate();
}

Lars Aaberg

unread,
Mar 3, 2013, 8:52:53 AM3/3/13
to sq...@googlegroups.com

Hi,

You get the generated key by calling the getKey() method after you have executed your query.

long key = sql2o.createQuery(...)
   .addParameter(...)
   .executeUpdate()
   .getKey(Long.class);

By the way, you don't need the column mappings when inserting into your database. Column mappings are only used with select-queries.

May I ask which database you are connecting to?

Regards
Lars Aaberg

--
You received this message because you are subscribed to the Google Groups "sql2o" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sql2o+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Majid Azimi

unread,
Mar 4, 2013, 2:27:15 AM3/4/13
to sq...@googlegroups.com
May I ask which database you are connecting to?
PostgreSQL 9.2 

Lars Aaberg

unread,
Mar 4, 2013, 2:50:10 AM3/4/13
to sq...@googlegroups.com
When using PostgreSQL, you need to set the QuirksMode enum when creating your sql2o instance.

sql2o = new Sql2o("jdbc:postgresql:mydb", "username", "password", QuirksMode.PostgreSQL);

When executing your query, to get the generated key, you need to specify that sql2o should return generated keys in the createQuery() method.

long key = sql2o.createQuery("insert into ...", true)
   .addParameter(...)
   .executeUpdate()
   .getKey(Long.class);

This is because the PostgreSQL JDBC driver will throw an exception if sql2o asks for the generated key when no key has been generated. 
So, when using QuirksMode.PostreSQL, sql2o will not ask for generated keys every time sql is executed, only when you specify it to do so in the createQuery method.

Regards
Lars

On Mon, Mar 4, 2013 at 8:27 AM, Majid Azimi <majid....@gmail.com> wrote:
May I ask which database you are connecting to?
PostgreSQL 9.2 

--

Y Ramesh Rao

unread,
May 8, 2013, 12:49:58 PM5/8/13
to sq...@googlegroups.com
I'm trying to use this logic but I'm unable to get the Sequence driven id from inserting values into the table.

Are there some additional changes required for postgres

Lars Aaberg

unread,
May 8, 2013, 4:26:58 PM5/8/13
to sq...@googlegroups.com
Hi,

There should be no additional logic. I have created this gist to demonstrate how to get the generated key from PostgreSQL for two different scenarios.

The first example creates a table with a SERIAL primary key column, inserts some values, and gets the generated value.
The second example creates a table with an INTEGER primary key column, inserts a value from a sequence, and fetches the generated key.

Both examples have been tested with PostgreSQL 9.1, and they work fine.

Hope it helps :)

Regards
Lars

Y Ramesh Rao

unread,
May 8, 2013, 11:24:51 PM5/8/13
to sq...@googlegroups.com
Hi Lars,

Sorry my mistake. I found out what was the issue.

I had created a trigger function in Postgres that function was returning NULL after Insert query that was the reason that I was unable to get the generated id after successful entry into the database.

Fixed It and now having more fun with Sql2o.

Thanks fir that quick reply man.

Shmuel Goldfarb

unread,
Mar 23, 2017, 11:03:00 AM3/23/17
to sql2o
Hi Lars,
I found this thread while looking for answer for the issue I'm experiancing at the moment
I try to insert to a postgres table that has generated key using sequence using the DEFAULT syntax, i.e.:

INSERT INTO public.shmuel_test_tbl( id, string_col, time_col)
VALUES
(DEFAULT,
        now
(),
        now
());

where table shmuel_test_tbl defenition is:

CREATE TABLE public.shmuel_test_tbl
(
    id integer NOT NULL DEFAULT nextval
('shmuel_test_id_seq'::regclass),
    string_col character varying
(50) ,
    time_col timestamp without time zone
,
    CONSTRAINT shmuel_tbl_pk PRIMARY KEY
(id),
    CONSTRAINT string_col_uniq UNIQUE
(string_col)
)



and I get this exception:

rg.postgresql.util.PSQLException: Cannot rollback when autoCommit is enabled.
    at org
.postgresql.jdbc.PgConnection.rollback(PgConnection.java:776)
    at org
.sql2o.Connection.rollback(Connection.java:115)
    at org
.sql2o.Connection.onException(Connection.java:56)
    at org
.sql2o.Query.executeUpdate(Query.java:526)
    at com
.github.ghost93.dal.postgres.PostgresDal.shmuelTest(PostgresDal.java:119)
    at com
.github.ghost93.api.Application.main(Application.java:28)
Exception in thread "main" org.sql2o.Sql2oException: Error in executeUpdate, No value specified for parameter 1.
    at org
.sql2o.Query.executeUpdate(Query.java:527)
    at com
.github.ghost93.dal.postgres.PostgresDal.shmuelTest(PostgresDal.java:119)
    at com
.github.ghost93.api.Application.main(Application.java:28)
Caused by: org.postgresql.util.PSQLException: No value specified for parameter 1.
    at org
.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:257)
    at org
.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:281)
    at org
.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
    at org
.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
    at org
.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
    at org
.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136)
    at org
.sql2o.Query.executeUpdate(Query.java:521)
   
... 2 more

this is my code:
ShmuelTestModel modelToInsert = new ShmuelTestModel();
    con
.createQuery(
       
"INSERT INTO public.shmuel_test_tbl(id, string_col, time_col) VALUES (DEFAULT,:str, :time)",true)
       
.bind(modelToInsert).executeUpdate();

I saw in your gist you call next_val function explicitly, which I would like to avoid...

Any chance you know what is the problem?
Thanks.

Lars Aaberg

unread,
Mar 23, 2017, 11:18:43 AM3/23/17
to sql2o

Hi!

Looks like it might be the parameter mapping between the query and your model that's the problem.

What do your model class look like?

~Lars


For more options, visit https://groups.google.com/d/optout.

goldfar...@gmail.com

unread,
Mar 23, 2017, 11:28:37 AM3/23/17
to sql2o
Here it is:
class ShmuelTestModel {
   
private String str = "Hey@"+System.currentTimeMillis();
   
private Date time = new Date(System.currentTimeMillis()-24*60*60*1000);
   
private int id;
 
}

the id field is not initialized of course because I expect the sequence to return it

goldfar...@gmail.com

unread,
Mar 31, 2017, 2:05:16 PM3/31/17
to sql2o, goldfar...@gmail.com
Ok, we I fixed it.
Problem was the sequence generated by postgres is of Long type, and I set the id to be Int in my model.

God bless super-programmers like the guy who elaborated and helped, Eliran.
Reply all
Reply to author
Forward
0 new messages