How to get auto generated Id after inserting to database?

[Majid Azimi]

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]

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]

May I ask which database you are connecting to?

PostgreSQL 9.2 

[Lars Aaberg]

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]

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]

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]

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]

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]

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]

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]

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.