Hello, I suspect, you were running into this known issue here: https://github.com/jOOQ/jOOQ/issues/2374
Currently, jOOQ cannot fetch inserted IDs with plain SQL. I will have to give this issue a higher priority for jOOQ 3.2 (and 3.1.1, 3.0.2). Note, there is also DSLContext.lastID() to fetch the last inserted ID: http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#lastID() It will perform the same select as the one you've run: SELECT LAST_INSERT_ID() Cheers Lukas 2013/7/5 David Lee <[email protected]> > I could however get the generated key by using > > private Integer insertRow(String tablename, FastMap<Object> mykeys) > throws Exception { > > Class.forName("com.mysql.jdbc.Driver").newInstance(); > Connection myconnection = > DriverManager.getConnection("jdbc:mysql://" + SharedData.ADDRESS + ":3306/" > + SharedData.DATABASE, SharedData.DATABASEUSERNAME, > SharedData.DATABASEPASSWORD); > > DSLContext create = MySQLDSL.using(myconnection, SQLDialect.MYSQL); > > InsertSetStep<Record> steps = > create.insertInto(DSL.tableByName(tablename)); > > InsertSetMoreStep<Record> steps2 = null; > String keyset[] = mykeys.keySet().toArray(new String[] {}); > > for (int i = 0; i < mykeys.size(); i++) { > if (steps2 == null) { > steps2 = steps.set(DSL.fieldByName(keyset[i]), > mykeys.get(keyset[i])); > } else { > steps2 = steps2.set(DSL.fieldByName(keyset[i]), > mykeys.get(keyset[i])); > } > } > > steps2.returning().fetchOne(); > Record generatedkey = create.fetchOne("SELECT LAST_INSERT_ID()"); > myconnection.close(); > > return ((ULong) > generatedkey.getValue(DSL.fieldByName("LAST_INSERT_ID()"))).intValue(); > } > > > however now i am using raw sql > > > On Friday, July 5, 2013 11:08:23 AM UTC-7, David Lee wrote: >> >> I been stuck for hours trying to figure out why my jooq isn't returning a >> generated key >> >> private Integer insertRow(String tablename, FastMap<Object> mykeys) { >> >> InsertSetStep<Record> steps = JOOQSQL.getInstance().create()** >> .insertInto(DSL.tableByName(**tablename)); >> InsertSetMoreStep<Record> steps2 = null; >> String keyset[] = mykeys.keySet().toArray(new String[] {}); >> >> for (int i = 0; i < mykeys.size(); i++) { >> if (steps2 == null) { >> steps2 = steps.set(DSL.fieldByName(**keyset[i]), >> mykeys.get(keyset[i])); >> } else { >> steps2 = steps2.set(DSL.fieldByName(**keyset[i]), >> mykeys.get(keyset[i])); >> } >> } >> >> Record generatedkey = steps2.returning(DSL.** >> fieldByName("hiddenkey")).**fetchOne(); >> >> System.out.println(**generatedkey); >> System.out.println(**generatedkey.size()); >> >> return (Integer) generatedkey.getValue(DSL.** >> fieldByName("hiddenkey")); >> } >> >> >> >> i keep getting null >> > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
