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
CRUD with UpdatableRecords
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Your database application probably consists of 50% - 80% CRUD, whereas only the remaining 20% - 50% of querying is actual querying. Most often, you will operate on records of tables without using any advanced relational concepts. This is called CRUD for
CRUD always uses the same patterns, regardless of the nature of underlying tables. This again, leads to a lot of boilerplate code, if you have to issue your statements yourself. Like Hibernate / JPA and other ORMs, jOOQ facilitates CRUD using a specific API involving org.jooq.UpdatableRecord types.
Primary keys and updatability
In normalised databases, every table has a primary key by which a tuple/record within that table can be uniquely identified. In simple cases, this is a (possibly auto-generated) number called ID. But in many cases, primary keys include several non-numeric columns. An important feature of such keys is the fact that in most databases, they are enforced using an index that allows for very fast random access to the table. A typical way to access / modify / delete a book is this:
-- Inserting uses a previously generated key value or generates it afresh INSERT INTO BOOK (ID, TITLE) VALUES (5, 'Animal Farm'); -- Other operations can use a previously generated key value SELECT * FROM BOOK WHERE ID = 5; UPDATE BOOK SET TITLE = '1984' WHERE ID = 5; DELETE FROM BOOK WHERE ID = 5;
Normalised databases assume that a primary key is unique "forever", i.e. that a key, once inserted into a table, will never be changed or re-inserted after deletion. In order to use jOOQ's CRUD operations correctly, you should design your database accordingly.
Table of contents
- 4.12.1.
- Simple CRUD
- 4.12.2.
- Records' internal flags
- 4.12.3.
- IDENTITY values
- 4.12.4.
- Navigation methods
- 4.12.5.
- Non-updatable records
- 4.12.6.
- Optimistic locking
- 4.12.7.
- Batch execution
- 4.12.8.
- CRUD SPI: RecordListener
| previous : next |
References to this page
- Different use cases for jOOQ
- jOOQ and JPA
- Auto-attach Records
- Optimistic Locking
- Return all columns on store
- Return Identity Value On Store
- Updatable Primary Keys
- The SELECT statement
- Primary key
- Readonly columns
- Serializability
- POJOs
- IDENTITY values
- Non-updatable records
- Optimistic locking
- CRUD SPI: RecordListener
- DAOs
- Exception handling
- Synthetic readonly ROWIDs
- Readonly columns
Feedback
Do you have any feedback about this page? We'd love to hear it!