Search code examples
postgresqlspring-bootjpaquerydsl

Why this QueryDSL query without @Transactional will cause Unable to acquire JDBC Connection

SpringBoot Application throws Exception

Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection

after several request to an api

public Response getChainObjectDepartments(@RequestBody List<UUID> ids) {
  val ud = QUserDepartmentPlain.userDepartmentPlain;
  val d = QDepartmentPlain.departmentPlain;
  val result = jpaQueryFactory.select(d).from(d).join(ud).on(ud.departmentId.eq(d.id)).where(ud.userId.in(ids)).transform(
      groupBy(ud.userId).as(GroupBy.list(d))
  );
  return Response.ok(result);
}

I use PostgreSQL and pgAdmin shows lots of the session's last query is generate by this QueryDSL query:

enter image description here

    select
        userdepart1_."user_id" as col_0_0_,
        department0_."id" as col_1_0_,
        department0_."id" as id1_29_,
        department0_."charge_leader_id" as charge_11_29_,
        department0_."dingtalk_id" as dingtalk2_29_,
        department0_."is_audit" as is_audi12_29_,
        department0_."is_enable" as is_enabl3_29_,
        department0_."name" as name4_29_,
        department0_."parent_id" as parent_i5_29_,
        department0_."remark" as remark6_29_,
        department0_."sort" as sort7_29_,
        department0_."tz_create" as tz_creat8_29_,
        department0_."tz_retire" as tz_retir9_29_,
        department0_."tz_update" as tz_upda10_29_
    from
        "organization"."department" department0_
    inner join
        "organization"."rel_user_department" userdepart1_
            on (
                userdepart1_."department_id"=department0_."id"
            )
    where
        userdepart1_."user_id" in (
            ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
        )

When all the session fills with this query, application start to throw exception Unable to acquire JDBC Connection

Finally, I solve the problem by add @Transactional to the function(I don't add @Transactional to function only have select query in general). But I still don't know what's wrong with this QueryDSL query, Why It must have @Transactional.

Solution

  • Actually, this issue is known. There is a connection leakage when using FetchableQueryBase#transform outside of a transaction.

    Check here: https://github.com/querydsl/querydsl/issues/3089