FetchOne -> org.jooq.exception.TooManyRowsException (using primary key)

2,045 views
Skip to first unread message

ems...@emssik.eu

unread,
Dec 10, 2015, 3:30:38 AM12/10/15
to jOOQ User Group
Hello,

jooqVersion := "3.7.0"
PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu 
Scala

this:

def get_existing_record()(implicit sql_conn: EmsSQLConnection) = {
    val LT = Tables.TBL_LOANS_TEMP
    val LK = Tables.TBL_LINKAGE
    jooq.dsl
      .select()
      .from(LT)
      .whereExists(
        jooq.dsl
          .select(LK.FLD_LINKAGE_ID)
          .from(LK)
          .where(LK.FLD_CAT_ID === LT.FLD_CAT_ID and LK.FLD_DB_ID === LT.FLD_DB_ID)
      )
      .fetchOne()
  }

give me this exception (cat_id + db_id) are a primary key.

09:06:42.145 [DEBUG] Query executed           : Total: 1.437ms
09:06:42.146 [DEBUG] Fetched result           : +---------------------------------+---------+----------+---------+-------------+-----------+-----------+-------------+---------------+
09:06:42.146 [DEBUG]                          : |fld_cat_id                       |fld_db_id|fld_agenda|fld_total|fld_available|fld_on_site|fld_unknown|fld_queue_len|fld_next_return|
09:06:42.146 [DEBUG]                          : +---------------------------------+---------+----------+---------+-------------+-----------+-----------+-------------+---------------+
09:06:42.146 [DEBUG]                          : |40...@lic867.sowa.w.bibliotece.pl|U70      |66        |        1|            1|          0|          0|            0|               |
09:06:42.146 [DEBUG]                          : +---------------------------------+---------+----------+---------+-------------+-----------+-----------+-------------+---------------+
09:06:42.146 [DEBUG] Finishing                : Total: 2.224ms, +0.786ms
09:06:42.165 [ERROR] Cursor returned more than one result
org.jooq.exception.TooManyRowsException: Cursor returned more than one result
        at org.jooq.impl.Utils.fetchOne(Utils.java:1220) ~[jooq-3.7.0.jar:na]
        at org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:493) ~[jooq-3.7.0.jar:na]
        at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2735) ~[jooq-3.7.0.jar:na]


I see only one data row here.

Any ideas how fix this?

Regards.

Daniel Roziecki

Lukas Eder

unread,
Dec 10, 2015, 3:37:10 AM12/10/15
to jooq...@googlegroups.com
Indeed, only one record is logged by the call to Cursor.fetchOne(), but the Cursor does seem to have more records.

Are you sure you're selecting only one record? Your query looks like you're selecting pretty much every record in the LT table (for which there exists a corresponding record in the LK table). What are you getting when you run the query in a SQL tool?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Daniel Roziecki

unread,
Dec 10, 2015, 3:51:28 AM12/10/15
to jOOQ User Group
Ehh,

you are absolutly right. I should use LIMIT or fetchAny. I thought fetchOne works like sql with LIMIT 1.

Regards.

DR

Lukas Eder

unread,
Dec 10, 2015, 4:12:23 AM12/10/15
to jooq...@googlegroups.com
OK, thanks for the feedback.

I suggest you use LIMIT in addition to fetchOne() if you want to fetch only the first result. fetchAny() will still make the database materialise all the rows but then fetches only the first... With LIMIT 1, the database actually knows you're interested in only one row.

Daniel Roziecki

unread,
Dec 10, 2015, 4:36:19 AM12/10/15
to jOOQ User Group
Ofc,  limit + fetchOne is better.

Thx.
Reply all
Reply to author
Forward
0 new messages