java - 带有postgres的sql2o，查询没有返回结果

Question

我在这个标题中发现的其他问题都涉及非 SELECT 查询。我正在使用 Java 8、Sql2o 1.5.4 和 postgresql 9.5.3。

我的用户服务看起来像:

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;
    }
}

我的用户看起来像:

public class User {
    private int id;
    private String firstname;
    private String lastname;
    private String email;
    private String passhash;
    private int teamId;
    /*getters and setters*/
}

我的测试看起来像:

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");
    }
}

当我直接对数据库执行 SQL 时，我得到了预期的记录，在这种情况下 team_id 为 NULL。

当我运行测试时，出现以下异常:

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)

为什么会这样？我该如何解决？我的 PGService 测试通过了，它是从 DBCP2 BasicDataSource 创建的。如果您需要更多详细信息，请告诉我。

Answer 1

问题不在 UserService 中，而是在我的 PGService 类中。这是:

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();
    }
}

下面的修复和解释来自 Sql2o Google 组，修复了我的问题，并且与使用 postgres 时围绕 RETURNING 语法的错误密切相关。

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.

无论如何，我希望这对以后遇到此问题的任何人有所帮助。

干杯。