Background

I have a database with many duplicate table schemas.

A quick example is the following two objects. Notice that ORDER is the exact same as INVOICE and ORDER_LINE is the same as INVOICE_LINE.

ORDER            ORDER_LINE        vs.    INVOICE            INVOICE_LINE             
- id             - parent_id              - id               - parent_id  
- total          - line_number            - total            - line_number           
- approved       - total                  - approved         - total
                 - product                                   - product

This is simplified, but in reality, there are nearly 20 different “objects” that are duplicated like this. Each “object” is about 15 tables. Each table has around 300 columns. (This is an ERP)

In other words, an Order is about 15 tables. We have 19 other objects (ex: Invoice, Return, Quotation, etc. that have the exact same tables and columns). There is no way for these to ever diverge, all tables are always consistent across all objects.

Goal

I’d like to use jOOQ in the following way:

1. Be able to write queries once and use them across the different object tables. I would like to write a query like the following and reuse it across all of these duplicated objects.

// NOT DESIRED
var orderQuery = create.select(ORDER.ID)
                       .from(ORDER)
                       .where(ORDER.TOTAL.eq(100));

var invoiceQuery = create.select(INVOICE.ID)
                         .from(INVOICE)
                         .where(INVOICE.TOTAL.eq(100));



// DESIRED
// Query can be written and applied to any of the 20 objects
var sqlQuery = create.select(xxxx.ID)
                     .from(xxxx)
                     .where(xxxx.TOTAL.eq(100));

2. Be able to query for any of the 20 objects and map it to a common POJO.