Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Simple CRUD
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
If you're using jOOQ's code generator, it will generate org.jooq.UpdatableRecord implementations for every table that has a primary key. When fetching such a record form the database, these records are "attached" to the Configuration that created them. This means that they hold an internal reference to the same database connection that was used to fetch them. This connection is used internally by any of the following methods of the UpdatableRecord:
// Refresh a record from the database. void refresh() throws DataAccessException; // Store (insert or update) a record to the database. int store() throws DataAccessException; // Delete a record from the database int delete() throws DataAccessException;
See the manual's section about serializability for some more insight on "attached" objects.
Storing
Storing a record will perform an INSERT statement or an UPDATE statement. In general, new records are always inserted, whereas records loaded from the database are always updated. This is best visualised in code:
// Create a new record
BookRecord book1 = create.newRecord(BOOK);
// Insert the record: INSERT INTO BOOK (TITLE) VALUES ('1984');
book1.setTitle("1984");
book1.store();
// Update the record: UPDATE BOOK SET PUBLISHED_IN = 1984 WHERE ID = [id]
book1.setPublishedIn(1948);
book1.store();
// Get the (possibly) auto-generated ID from the record
Integer id = book1.getId();
// Get another instance of the same book
BookRecord book2 = create.fetchOne(BOOK, BOOK.ID.eq(id));
// Update the record: UPDATE BOOK SET TITLE = 'Animal Farm' WHERE ID = [id]
book2.setTitle("Animal Farm");
book2.store();
Some remarks about storing:
- jOOQ sets only modified values in INSERT statements or UPDATE statements. This allows for default values to be applied to inserted records, as specified in CREATE TABLE DDL statements.
- When store() performs an INSERT statement, jOOQ attempts to load any generated keys from the database back into the record. For more details, see the manual's section about IDENTITY values.
- In addition to loading identity values, store() can also be configured to refresh the entire record. See the returnAllOnUpdatableRecord setting for details
- When loading records from POJOs, jOOQ will assume the record is a new record. It will hence attempt to INSERT it.
- When you activate optimistic locking, storing a record may fail, if the underlying database record has been changed in the mean time.
Deleting
Deleting a record will remove it from the database. Here's how you delete records:
// Get a previously inserted book BookRecord book = create.fetchOne(BOOK, BOOK.ID.eq(5)); // Delete the book book.delete();
Refreshing
Refreshing a record from the database means that jOOQ will issue a SELECT statement to refresh all record values that are not the primary key. This is particularly useful when you use jOOQ's optimistic locking feature, in case a modified record is "stale" and cannot be stored to the database, because the underlying database record has changed in the mean time.
In order to perform a refresh, use the following Java code:
// Fetch an updatable record from the database BookRecord book = create.fetchOne(BOOK, BOOK.ID.eq(5)); // Refresh the record book.refresh();
CRUD and SELECT statements
CRUD operations can be combined with regular querying, if you select records from single database tables, as explained in the manual's section about SELECT statements. For this, you will need to use the selectFrom() method from the DSLContext:
// Loop over records returned from a SELECT statement
for (BookRecord book : create.fetch(BOOK, BOOK.PUBLISHED_IN.eq(1948))) {
// Perform actions on BookRecords depending on some conditions
if ("Orwell".equals(book.fetchParent(Keys.FK_BOOK_AUTHOR).getLastName())) {
book.delete();
}
}
Feedback
Do you have any feedback about this page? We'd love to hear it!