Available in versions: Dev (3.22) | Latest (3.21) | 3.20 | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12
Working with in-memory results
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A Result created in memory is no different from a Result fetched from a database. All of the usual API is available. This section shows a few common use-cases.
Converting to other data types
Most of the methods that read text based formats produce String typed fields. Using jOOQ's data type conversion capabilities, such results can be converted into TableRecords, ad-hoc records, POJOs, or just individual values:
Result<Record> result = create.fetchFromCSV(csv);
// Convert to a generated TableRecord type. Fields are matched by name.
Result<BookRecord> books = result.into(BOOK);
// Convert to an ad-hoc record type. Fields are matched by name.
Result<Record2<Integer, String>> titles = result.into(BOOK.ID, BOOK.TITLE);
// Map to POJOs
List<Book> list = result.into(Book.class);
// Extract individual columns
List<Integer> ids = result.getValues("ID", Integer.class);
Using in-memory results in SQL queries
With DSL.table(Result), any Result can be turned into a table expression based on the VALUES() table constructor, e.g. to join ad-hoc data with database tables:
SELECT v.TITLE, AUTHOR.LAST_NAME
FROM (
VALUES (1, 1, '1984'),
(2, 1, 'Animal Farm')
) AS v (ID, AUTHOR_ID, TITLE)
JOIN AUTHOR
ON AUTHOR.ID = v.AUTHOR_ID
Table<Record3<Integer, Integer, String>> v = table(
create.fetchFromCSV(csv)
.into(BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE)
);
create.select(v.field(BOOK.TITLE), AUTHOR.LAST_NAME)
.from(v)
.join(AUTHOR)
.on(AUTHOR.ID.eq(v.field(BOOK.AUTHOR_ID)))
.fetch();
Converting the data to the desired types first, as above, ensures that the VALUES() constructor produces correctly typed bind values.
Mocking
When implementing a MockDataProvider, results have to be created in memory. Any of the methods in this section can be used for this:
// A DSLContext without a connection, used only to create results
DSLContext mock = DSL.using(SQLDialect.POSTGRES);
MockDataProvider provider = ctx -> new MockResult[] {
new MockResult(2, mock.fetchFromTXT(
"+---+-----------+\n"
+ "| ID|TITLE |\n"
+ "+---+-----------+\n"
+ "| 1|1984 |\n"
+ "| 2|Animal Farm|\n"
+ "+---+-----------+\n"
))
};
For a more declarative approach, consider using the MockFileDatabase, which uses the same text based formats.
Importing
In-memory results can be imported into a database table using the Loader API:
create.loadInto(BOOK)
.loadRecords(create.fetchFromCSV(csv))
.fieldsCorresponding()
.execute();
Converting between formats
Reading a format into a Result, and then exporting it again, can be used to convert between formats:
// Convert CSV to JSON String json = create.fetchFromCSV(csv).formatJSON(); // Convert a text table to an HTML table String html = create.fetchFromTXT(text).formatHTML();
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!