Querydsl fetchCount() & fetch() NullPointerException, Connection is closed

Question

My goal is to implement dao method within pagination, sorting and filtering. For pagination I need get firstly count and then set offset & limit and get result (so get just one "page" from database):

@Slf4j
@Repository
public class UserJdbcRepository {

    private final SQLQueryFactory queryFactory;

    @Autowired
    public UserJdbcRepository(DataSource dataSource) {
        Configuration configuration = new Configuration(new OracleTemplates());
        configuration.setExceptionTranslator(new SpringExceptionTranslator());
        this.queryFactory = new SQLQueryFactory(configuration, dataSource);
    }

    public Page<User> findAll(BooleanExpression predicate, Pageable pageable) {
        QUser u = new QUser("u");

        SQLQuery<Tuple> sql = queryFactory
                .select(u.userId, // omitted)
                .from(u)
                .where(predicate);

        long count = sql.fetchCount();
        List<Tuple> results = sql.fetch();
        // Conversion List<Tuple> to List<User> omitted
        return new PageImpl<>(users, pageable, count);
    }
}

fetchCount() is executed correctly, but fetch() is throwing NullPointerException:

java.lang.NullPointerException: null
at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502) ~[querydsl-sql-4.4.0.jar:na]

From debug I found that root cause is in com.querydsl.sql.AbstractSQLQuery:

java.sql.SQLException: Connection is closed

If I create second (the same as first one) query sql2, then it is working (of course):

SQLQuery<Tuple> sql2 = queryFactory
            .select(... // same as first one)


long count = sql.fetchCount();
List<Tuple> results = sql2.fetch();

My question is if connection should be really closed after fetchCount() is called? Or do I have some misconfiguration?

I have SpringBoot 2.4.5; spring-data-commons 2.5.0; Oracle driver ojdbc8 21.1.0.0; QueryDSL 4.4.0

    <dependency>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-sql</artifactId>
        <version>${querydsl.version}</version>
        <scope>compile</scope>
    </dependency>
    <dependency>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-sql-spring</artifactId>
        <version>${querydsl.version}</version>
        <scope>compile</scope>
    </dependency>

    <plugin>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-maven-plugin</artifactId>
            <version>${querydsl.version}</version>
            <executions>
                <execution>
                    <goals>
                        <goal>export</goal>
                    </goals>
                </execution>
            </executions>
            <configuration>
                <jdbcDriver>oracle.jdbc.OracleDriver</jdbcDriver>
                <jdbcUrl>jdbc:oracle:thin:@//localhost:1521/XE</jdbcUrl>
                <jdbcUser>user</jdbcUser>
                <jdbcPassword>password</jdbcPassword>
                <sourceFolder>${project.basedir}/src/main/java</sourceFolder>
                <targetFolder>${project.basedir}/src/main/java</targetFolder>
                <packageName>org.project.backend.repository.querydsl</packageName>
                <schemaToPackage>true</schemaToPackage>
                <schemaPattern>project</schemaPattern>
                <tableNamePattern>
                    // omitted
                </tableNamePattern>
            </configuration>    
    <plugin>

score 0 · Answer 1 · answered Apr 29 '21 at 20:11

Issue is caused by missconfiguration. QueryDSL doc contains Spring integration section, where is mentioned that SpringConnectionProvider must be used. So I changed my constructor and it is working now as expected:

@Autowired
public UserJdbcRepository(DataSource dataSource) {
    Configuration configuration = new Configuration(new OracleTemplates());
    configuration.setExceptionTranslator(new SpringExceptionTranslator());

    // wrong: this.queryFactory = new SQLQueryFactory(configuration, dataSource);

    Provider<Connection> provider = new SpringConnectionProvider(dataSource);
    this.queryFactory = new SQLQueryFactory(configuration, provider);
}

I also found there is useful method fetchResults() containing count for pagination purpose (so not needed to explicitly call fetchCount()):

    public Page<User> findAll(BooleanExpression predicate, Pageable pageable) {
    QUser u = new QUser("u");

    SQLQuery<Tuple> sql = queryFactory
            .select(u.userId, // omitted)
            .from(u)
            .where(predicate);

    sql.offset(pageable.getOffset());
    sql.limit(pageable.getPageSize());

    QueryResults<Tuple> queryResults = sql.fetchResults();
    long count = queryResults.getTotal();
    List<Tuple> results = queryResults.getResults();
    // Conversion List<Tuple> to List<User> omitted
    return new PageImpl<>(users, pageable, count);
}

Querydsl fetchCount() & fetch() NullPointerException, Connection is closed

1 Answers1