16

Is there a (real) difference between fetchAny() and fetchOne()? Both return exact one record. The API documentation is the same, but the implementation (on github) is different.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Appelsien Sap
  • 363
  • 1
  • 3
  • 9

2 Answers2

29

The intent of the two methods is different:

In essence, when you use fetchOne() the query must return 0 or 1 record. When you use fetchAny() the query may return any number of records, and if any record is returned by the database, the first one fetched from the JDBC result set will be returned.

Notice that fetchOne() will thus try to fetch 2 records from the JDBC driver (to decide whether TooManyRowsException needs to be thrown), while fetchAny() only fetches at most 1 record.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Any performance benefits by using fetchOne() ?Can i assume that it stops finding the record throughout the table if it has found already ? – Aston Ray Feb 26 '16 at 08:52
  • 1
    @vinaypatlolla: jOOQ will fetch at most two records from the JDBC `ResultSet` (The first one is returned, and the second one is needed to check if `fetchOne()` needs to throw `TooManyRowsException`). You should, however, still ensure that both 1) your JDBC driver doesn't cache too many rows, 2) your database doesn't actually load all rows in the cursor. Ideally, you'll use jOOQ's `limit(1)` clause, too. – Lukas Eder Feb 26 '16 at 09:07
  • Thanks @Lukas Eder .And by the way JOOQ is awsome :)I 've been using it since 2 months and helping us a lot . – Aston Ray Feb 26 '16 at 09:43
  • I have a question @Lukas Eder .The jooq generated classes were all synch with the database name and now i want to go with a new schema with same tables ie i will be working on new tables only in this schema ,when i generate classes now i see all of my classes with new schema_name .So how can i work with multiple schemas without many changes in my classes ? – Aston Ray Feb 26 '16 at 13:31
  • @vinaypatlolla maybe this helps: http://www.jooq.org/doc/latest/manual/code-generation/schema-mapping/ – Lukas Eder Feb 26 '16 at 21:19
  • Oh yes ! This is what exactly we are looking for .Thanks Lukas – Aston Ray Feb 27 '16 at 11:23
  • i give an extra point if you mention `fetchSingle` ;) – Daniel Alder Jan 17 '23 at 15:04
  • @DanielAlder: The question was about `fetchAny()` and `fetchOne()`. THough if you provide a `fetchSingle()` based answer, then I'll definitely upvote it! – Lukas Eder Jan 17 '23 at 18:41
4

The javadoc explains the difference. fetchAny() returns the first record, whereas fetchOne() expects the query to return zero or one record, and throws an exception if the query returned more than one record.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255