Sql2o with postgres, No results were returned by the query

Question

The other questions I've found with this title all deal with non-SELECT queries. I'm using Java 8, Sql2o 1.5.4, and 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 FROM users WHERE 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 and 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 I execute the SQL directly against the database I get the expected record, and team_id is NULL in this case.

When I run the test, I get the following exception:

org.sql2o.Sql2oException: Database error: No results were returned by the query.

    at org.sql2o.Query$ResultSetIterableBase.<init>(Query.java:332)
    at org.sql2o.Query$10.<init>(Query.java:412)
    at org.sql2o.Query.executeAndFetchLazy(Query.java:412)
    at org.sql2o.Query.executeAndFetchFirst(Query.java:480)
    at org.sql2o.Query.executeAndFetchFirst(Query.java:469)
    at services.UserService.getUserById(UserService.java:24)
    at services.UserServiceTest.returnsBiffUser(UserServiceTest.java:25)
Caused by: org.postgresql.util.PSQLException: No results were returned by the query.
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:115)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
    at org.sql2o.Query$ResultSetIterableBase.<init>(Query.java:328)

Why is this happening? How can I fix it? My PGService tests are passing and it is created from a DBCP2 BasicDataSource. Please let me know if you need more detail.

Answer 1

The issue was not in the UserService, it was in my PGService class. Which 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 and explanation below, from the Sql2o Google group fixed my problem and is tightly related to the error surrounding RETURNING syntax when using postgres.

The "syntax error at or near RETURNING" is caused by an incompatibility between the way sql2o is handling automatically generated keys in the database and the postgres jdbc driver. When using postgres, that exception is thrown when sql2o checks if there was generated any keys in the db. The solution is to never check for keys, except when you explicitly expect there to be generated a key.

This is handled in sql2o by the PostgresQuirks class. So, when creating you Sql2o instance, use one of the constructor overloads that takes a Quriks instance as parameter:

Sql2o sql2o = new Sql2o(bds, new PostgresQuirks());

That should fix it! And make sure to remove the '--' after your query.

Anyways, I hope this helps anyone who encounters this issue in the future.

Cheers.