java - Sql2o with postgres, No results were returned by the query -

the other questions i've found title deal non-select queries. i'm using java 8, sql2o 1.5.4, , postgresql 9.5.3.

my userservice looks like:

public class userservice {     private final pgservice pgservice;      public userservice(pgservice _pgs) {         this.pgservice = _pgs;     }      public user getuserbyid(int id) {         string sql = "select id, firstname, lastname, email, team_id teamid users id = :id;--";         user user;         try (connection c = pgservice.getconnection()) {             user = c.createquery(sql)                     .addparameter("id", id)                     .executeandfetchfirst(user.class);         }         return user;     } } 

my user looks like:

public class user {     private int id;     private string firstname;     private string lastname;     private string email;     private string passhash;     private int teamid;     /*getters , setters*/ } 

my test looks like:

public class userservicetest {     private static userservice service;      @before     public void setup() throws configurationexception, ioexception {         this.service = new userservice(new pgservice());     }      @test     public void returnsbiffuser() {         user biff = service.getuserbyid(3);         asserttrue(biff != null && biff.getlastname() == "biff");     } } 

when execute sql directly against database expected record, , team_id null in case.

when run test, following exception:

org.sql2o.sql2oexception: database error: no results returned query.      @ org.sql2o.query$resultsetiterablebase.<init>(query.java:332)     @ org.sql2o.query$10.<init>(query.java:412)     @ org.sql2o.query.executeandfetchlazy(query.java:412)     @ org.sql2o.query.executeandfetchfirst(query.java:480)     @ org.sql2o.query.executeandfetchfirst(query.java:469)     @ services.userservice.getuserbyid(userservice.java:24)     @ services.userservicetest.returnsbiffuser(userservicetest.java:25) caused by: org.postgresql.util.psqlexception: no results returned query.     @ org.postgresql.jdbc.pgpreparedstatement.executequery(pgpreparedstatement.java:115)     @ org.apache.commons.dbcp2.delegatingpreparedstatement.executequery(delegatingpreparedstatement.java:83)     @ org.apache.commons.dbcp2.delegatingpreparedstatement.executequery(delegatingpreparedstatement.java:83)     @ org.sql2o.query$resultsetiterablebase.<init>(query.java:328) 

why happening? how can fix it? pgservice tests passing , created dbcp2 basicdatasource. please let me know if need more detail.

the issue not in userservice, in pgservice class. was:

public class pgservice {     private final sql2o connectionpool;      public pgservice() throws configurationexception, ioexception {         config cfg = loadconfig("dbconfig.json");         if (cfg == null) {             throw new configurationexception("could not load dbconfig.");         }         basicdatasource bds = new basicdatasource();         bds.setusername(cfg.getusername());         bds.setpassword(cfg.getpassword());         bds.setdriverclassname("org.postgresql.driver");         bds.seturl(cfg.geturl());         bds.setinitialsize(1);         connectionpool = new sql2o(bds);     }      public connection getconnection() {         return this.connectionpool.open();     } } 

the fix , explanation below, sql2o google group fixed problem , tightly related error surrounding returning syntax when using postgres.

the "syntax error @ or near returning" caused incompatibility between way sql2o handling automatically generated keys in database , postgres jdbc driver. when using postgres, exception thrown when sql2o checks if there generated keys in db. solution never check keys, except when explicitly expect there generated key.

this handled in sql2o postgresquirks class. so, when creating sql2o instance, use 1 of constructor overloads takes quriks instance parameter:

sql2o sql2o = new sql2o(bds, new postgresquirks());

that should fix it! , make sure remove '--' after query.

anyways, hope helps encounters issue in future.

cheers.