Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Shows how to get generated key from Postgres with sql2o
Sql2o sql2o = new Sql2o(url, user, pass);
// Get generated key from SERIAL column
@Test
public void testKeyKeyOnSerial() {
String createTableSql = "create table test_serial_table (id serial primary key, val varchar(20))";
sql2o.createQuery(createTableSql).executeUpdate();
String insertSql = "insert into test_serial_table(val) values ('something')";
Long key = sql2o.createQuery(insertSql, true).executeUpdate().getKey(Long.class);
assertThat(key, equalTo(1L));
key = sql2o.createQuery(insertSql, true).executeUpdate().getKey(Long.class);
assertThat(key, equalTo(2L));
}
// Get generated key from integer column with inserted sequence value
@Test
public void testGetKeyOnSequence(){
String createSequenceSql = "create sequence testseq";
connection.createQuery(createSequenceSql).executeUpdate();
String createTableSql = "create table test_seq_table (id integer primary key, val varchar(20))";
connection.createQuery(createTableSql).executeUpdate();
String insertSql = "insert into test_seq_table(id, val) values (nextval('testseq'), 'something')";
Long key = connection.createQuery(insertSql, true).executeUpdate().getKey(Long.class);
assertThat(key, equalTo(1L));
key = connection.createQuery(insertSql, true).executeUpdate().getKey(Long.class);
assertThat(key, equalTo(2L));
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment