Many fish in the sea...

There are many fish in the Relational-Database-Access sea, but who's the Mackerel and who's the Shark?

The most common approach for DBMS access is called Object-Relational-Mapping (ORM) which essentially maps database tables to java classes and table columns to class properties or fields. ORM implementations are mostly built on the standards JDO and JPA and there are many available products such as Hibernate, OpenJPA, Cayenne and more.

But OR-Mapping has many limitations by design. As it largely shields you from SQL, you have little control over statements other than over the Where part of the query. On the Java side you are largely limited to work with Entity objects rather than specific query results in an "All or Nothing" manner. Maintaining Annotations or Mapping files is a pain and requires special tools. For Relations you need to decide between Lazy and Eager fetching which - no matter what you pick - will be sometimes good and sometimes bad. Metadata access is cumbersome and minimalistic. Furthermore, the query APIs provided are unintuitive and largely limited to simple entity queries (with no control over the "select" part) and they struggle with ad hoc joins, subqueries and anything that has to do with aggregation. Not to mention Union and Intersection queries. In the Coding Horror blog Object-Relational Mapping has even been called the Vietnam of Computer Science

Yes - in fairness - modern ORMs have found ways to mitigate some of the conceptual shortcomings and provide "workarounds", but this comes at a price. Just take a look at examples for getCriteriaBuilder() or createTupleQuery() and it will make your head spin. Seriously JPA?

Empire-db however, is a different type of fish. It is not an OR-Mapper in the classical sense, as it does not even attempt to rely on traditional Java Beans (POJOs) for data interchange, yet providing equivalent features as ORMs do. And it generally relies more on the developer to actively do something rather than doing things automatically in the background.

Even though it supports traditional Java Beans (POJOs), this is not the preferred approach. Instead it provides Records which essentially are "Dynamic Beans" where you have a generic getter / setter which takes the column as parameter. The record can be loaded as an entire table row, or partially with selected columns or even combine fields from different tables as a "logical" record. But the real advantage comes with the ability not just to control all aspects of your SQL statements, but also to do that in a simple and intuitive way so that you are actually capable of using everything a modern DBMS has to offer, such as various column functions, joins of any type (inner, outer, cross) with any kind of join restriction, joins on subqueries, aggregation, and so on. All that without having to fall back to SQL in String literals or the need for getting a special University degree.

The advantage becomes especially apparent in scenarios with large data models, complex relations, sophisticated query demands. And this is also where metadata becomes as - or sometimes even more - important than the actual data itself. With Metadata you can achieve so much, from writing generic functions to building or improving user interfaces.

Mackerel or Shark? You decide.

Tell me more... That's is too much!

Can your ORM do that?

Compare your current data persistence solution with what Empire-db has to offer.

Building queries

The query building engine is the heart of Empire-db. Empire-db can build DQL, DML and DDL statements in the "flavour" of the DBMS used.

Query data (DQL)

Selecting individual columns from tables and views and allow renaming e.g. to match bean property names
Using column functions in selection like e.g. concat, coalesce, trim, substring, length, case ... when, convert, etc.
Allowing aggregation queries with aggregation functions (sum, avg, min, max) as well as Group By and Having
Joining of entity types using any type of join constraints, not just Foreign-Key relations
Joining with subqueries in any depth
Where constraints with column functions, logical combinations (and / or), subqueries, etc.
Limit and offset (skipping) Rows in the query result, independent from whether directly supported by DBMS or not

Give me an example

The following example queries employees with their total payments in the previous year and the percentage of their payments in comparison to the total payments of their respective department. As you can see the query takes two subqueries which are both joined with the employees query.

Java

// Define shortcuts for tables used - not necessary but convenient
SampleDB.Employees   EMP = db.EMPLOYEES;
SampleDB.Departments DEP = db.DEPARTMENTS;
SampleDB.Payments    PAY = db.PAYMENTS;
// last year as literal
int lastYear = LocalDate.now().getYear()-1;
// Employee payments query (QEP)
DBCommand qepCmd = context.createCommand()
   .select(PAY.EMPLOYEE_ID, PAY.AMOUNT.sum().qualified())
   .where (PAY.YEAR.is(lastYear))
   .groupBy(PAY.EMPLOYEE_ID);
DBQuery QEP = new DBQuery(qepCmd, "qep");
// Department payments query (QDP)
DBCommand qdpCmd = context.createCommand()
   .select(EMP.DEPARTMENT_ID, PAY.AMOUNT.sum().qualified())
   .join  (PAY.EMPLOYEE_ID.on(EMP.ID))
   .where (PAY.YEAR.is(lastYear))
   .groupBy(EMP.DEPARTMENT_ID);
DBQuery QDP = new DBQuery(qdpCmd, "qdp");
// Now calculate the percentage of the departments total payments
DBColumnExpr PCT_OF_DEP_COST = QEP.column(PAY.AMOUNT.sum())
                                  .multiplyWith(100)
                                  .divideBy(QDP.column(PAY.AMOUNT.sum()));
// Create the employee query
DBCommand cmd = context.createCommand()
   .select(EMP.ID, EMP.FIRST_NAME, EMP.LAST_NAME, DEP.NAME.as("DEPARTMENT"))
   .select(QEP.column(PAY.AMOUNT.sum()), PCT_OF_DEP_COST.as("PCT_OF_DEPARTMENT_COST"))
   // join Employee with Department
   .join(EMP.DEPARTMENT_ID.on(DEP.ID))
   // Join with Subqueries
   .joinLeft(EMP.ID.on(QEP.column(PAY.EMPLOYEE_ID)))
   .joinLeft(DEP.ID.on(QDP.column(EMP.DEPARTMENT_ID)))
   // Order by
   .orderBy(DEP.NAME.desc())
   .orderBy(EMP.LAST_NAME);

SQL

SELECT t2.ID, t2.FIRST_NAME, t2.LAST_NAME, t1.NAME AS DEPARTMENT
     , qep.AMOUNT_SUM, qep.AMOUNT_SUM*100/qdp.AMOUNT_SUM AS PCT_OF_DEPARTMENT_COST
FROM EMPLOYEES t2
     INNER JOIN DEPARTMENTS t1 ON t1.ID = t2.DEPARTMENT_ID
     LEFT JOIN (SELECT t3.EMPLOYEE_ID, sum(t3.AMOUNT) AS AMOUNT_SUM
                FROM PAYMENTS t3
                WHERE t3.YEAR=2021
                GROUP BY t3.EMPLOYEE_ID
              ) qep ON qep.EMPLOYEE_ID = t2.ID
     LEFT JOIN (SELECT t2.DEPARTMENT_ID, sum(t3.AMOUNT) AS AMOUNT_SUM
                FROM PAYMENTS t3
                     INNER JOIN EMPLOYEES t2 ON t2.ID = t3.EMPLOYEE_ID
                WHERE t3.YEAR=2021
                GROUP BY t2.DEPARTMENT_ID
              ) qdp ON qdp.DEPARTMENT_ID = t1.ID
ORDER BY t1.NAME DESC, t2.LAST_NAME

Hint: If PreparedStatements are enabled, literals will be replaced by statement parameters (?)

OK, got it

Manipulating data (DML)

Simple insert, update and delete statements involving one entity-type
Update statements involving multiple entity-types with joins (if supported by DBMS)
Insert Into statements from any kind of query

Give me an example

This is an example of an update statement that also contains a join. Please note the difference in DBMS syntax between SQL-Server, PostgreSQL and Oracle.

Java

// create command
DBCommand cmd = context.createCommand()
    // increase model base prices by 5% and set sales info
    .set  (MODEL.BASE_PRICE.to(MODEL.BASE_PRICE.multiplyWith(105).divideBy(100).round(2)))
    .set  (MODEL.SALES_INFO.to("Price update "+LocalDate.now().toString()))
    // join with BRANDS
    .join (MODEL.WMI.on(BRAND.WMI))
    // on all Volkswagen with Diesel engine
    .where(BRAND.NAME.upper().like("VOLKSWAGEN"))
    .where(MODEL.ENGINE_TYPE.is(EngineType.D));

// execute Update statement
int count = context.executeUpdate(cmd);
log.info("{} models affected", count);

SQL-Server PostgreSQL Oracle

// Microsoft SQLServer syntax
UPDATE t2
SET BASE_PRICE=round(t2.BASE_PRICE*105/100,2), SALES_INFO='Price update 2022-03-03'
FROM MODEL t2
     INNER JOIN BRAND t1 ON t1.WMI = t2.WMI
WHERE upper(t1.NAME) LIKE 'VOLKSWAGEN' AND t2.ENGINE_TYPE='D'

// PostgreSQL syntax
UPDATE MODEL t0
SET BASE_PRICE=round(t2.BASE_PRICE*105/100,2), SALES_INFO='Price update 2022-03-03'
FROM MODEL t2
     INNER JOIN BRAND t1 ON t1.WMI = t2.WMI
WHERE t0.ID=t2.ID
  AND upper(t1.NAME) LIKE 'VOLKSWAGEN' AND t2.ENGINE_TYPE='D'

// Oracle syntax
MERGE INTO MODEL t2
USING (SELECT t2.ID, round(t2.BASE_PRICE*105/100,2) AS COL_0
       FROM MODEL t2 INNER JOIN BRAND t1 ON t1.WMI = t2.WMI
       WHERE upper(t1.NAME) LIKE 'VOLKSWAGEN' AND t2.ENGINE_TYPE='D') q0
   ON ( q0.ID=t2.ID)
WHEN MATCHED THEN UPDATE 
SET BASE_PRICE=q0.COL_0, SALES_INFO='Price update 2022-03-03'

Hint: If PreparedStatements are enabled, literals will be replaced by statement parameters (?)

OK, got it

DDL-operations

Create statements for tables, views and relations
Alter table statements to add, modify and remove columns
Drop statements for removing tables, views and relations

Give me some examples

DBMSHandler dbms = context.getDbms();
DBSQLScript script = new DBSQLScript(context);
// Get DDL script for creating the entire database
db.getCreateDDLScript(script);
// CREATE TABLE NewTable (ID INT IDENTITY(1, 1) NOT NULL, PRIMARY KEY (ID));
DBTable NEW_TABLE = new DBTable("NewTable", db);
NEW_TABLE.addIdentity("ID", null);
dbms.getDDLScript(DDLActionType.CREATE, NEW_TABLE, script);
// ALTER TABLE EMPLOYEES ADD NewColumn NVARCHAR(20) NULL; 
DBColumn NEW_COLUMN = EMPLOYEES.addColumn("NewColumn", DataType.VARCHAR, 20, false);
dbms.getDDLScript(DDLActionType.CREATE, NEW_COLUMN, script);
// ALTER TABLE EMPLOYEES ALTER COLUMN EMAIL NVARCHAR(80);
EMPLOYEES.EMAIL.setSize(80); 
dbms.getDDLScript(DDLActionType.ALTER, EMPLOYEES.EMAIL, script);
// ALTER TABLE EMPLOYEES DROP COLUMN GENDER;
dbms.getDDLScript(DDLActionType.DROP, EMPLOYEES.GENDER, script);
// DROP TABLE NewTable;
dbms.getDDLScript(DDLActionType.DROP, NEW_TABLE, script);

OK, got it

Reading and modifying data

Building queries is one thing, but it's far from being everything. It is certainly not convenient to build an insert or update statement from scratch every time you want to add or modify an entity, even more so as other aspects like identity management and concurrency control have to be considered.
And as different situations have different needs, it's always good to have a choice. So please choose:

Records	Records Records are the best Java type for performing CRUD operations in Empire-db. Records allow field modification and data conversion. Records also deal with identity management and concurrency control a.k.a Optimistic Locking. Give me some examples DBRecord record = new DBRecord(context, EMPLOYEES); // read record with identity column primary key record.read(55); // read record with multi-column primary key record.read(DBRecord.key(55, 2021, 12)); // read with constraints record.read(EMPLOYEES.FIRST_NAME.is("Anna").and(EMPLOYEES.LAST_NAME.is("Smith"))); // read record identified by a subquery record.read(EMPLOYEES.ID.is(cmd)); // read record partially with only firstname, lastname and salary record.read(DBRecord.key(55), PartialMode.INCLUDE, EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME, EMPLOYEES.SALARY); // create a new record record.create(); // modify a record record.set(EMPLOYEES.FIRST_NAME, "Fred") .set(EMPLOYEES.LAST_NAME, "Flintstone"); // insert or update a record record.update(); // delete a record record.delete(); // read a list of records List<DBRecord> list = context.getUtils().queryRecordList(cmd, EMPLOYEES); // read a list of Employee records List<EmployeeRecord> list = context.getUtils().queryRecordList(cmd, EMPLOYEES, EmployeeRecord.class); OK, got it
DataListEntry	DataListEntry A DataListEntry is a lightweight form of data container providing the same interface as a Record but being read-only. You can easily mix columns from various entity-types and query either a single item or a list of items. We also recommend to subclass DataListEntry in order to enrich it with additional getters methods. Give me an example DBUtils utils = context.getUtils(); // query a single data entry DataListEntry item = utils.queryDataEntry(cmd); // query a list of data entries List<DataListEntry> list = utils.queryDataList(cmd); // Subclass DataListEntry example public static class EmployeeInfo extends DataListEntry { private static final long serialVersionUID = 1L; private final SampleDB db; // Constructor public EmployeeInfo(DataListHead head, Object[] values) { super(head, values); this.db = head.getDatabase(SampleDB.class); } // Add additional getters public String getDisplayName() { return getString(db.EMPLOYEES.FIRST_NAME).substring(1)+". " +getString(db.EMPLOYEES.LAST_NAME); } } // query a single EmployeeInfo EmployeeInfo item = utils.queryDataEntry(cmd, EmployeeInfo.class); // query a list of EmployeeInfos List<EmployeeInfo> list = utils.queryDataList(cmd, EmployeeInfo.class); // query a list of EmployeeInfos limited to 10 items maximum List<EmployeeInfo> list = utils.queryDataList(cmd, EmployeeInfo.class, 0, 10); OK, got it
Java Beans (POJOs)	Java Beans (POJOs) Traditional Java-Beans can be used to hold query results and exchange data with records in both directions. Give me some examples DBUtils utils = context.getUtils(); // query entity bean using primary key Employee employee = utils.queryBean(Employee.class, EMPLOYEES, DBRecord.key(55)); // query entity bean with constraints Employee employee = utils.queryBean(Employee.class, EMPLOYEES.FIRST_NAME.is("Anna") .and(EMPLOYEES.LAST_NAME .is("Smith"))); // query entity bean list from query List<Employee> list = utils.queryBeanList(cmd, Employee.class, EMPLOYEES, null); // query result bean from query QueryResult result = utils.queryBean(cmd, QueryResult.class); // query result bean list from query List<QueryResult> list = utils.queryBeanList(cmd, QueryResult.class, null); OK, got it
Simple data	Simple data Very often you just want to query something simple like a single value or a simple list. The DBUtils class provides many methods that will provide you with that data based on your query statement. Give me an example DBUtils utils = context.getUtils(); // query a single value, don't fail if no result Object value = utils.querySingleValue(cmd, false); // query a single decimal BigDecimal value = utils.querySingleValue(cmd, BigDecimal.class, false); // query a single integer, fail if no result int number = utils.querySingleInt(cmd); // query a single integer default to -1 int number = utils.querySingleInt(cmd, -1); // query a single String String text = utils.querySingleString(cmd); // query a list of Strings List<String> list = utils.querySimpleList(String.class, cmd); // query a single row of data Object[] row = utils.querySingleRow(cmd); // query a set of options consisting of a value + text pair Options options = utils.queryOptionList(cmd); // query the number of rows in the result of a query int rowCount = utils.queryRowCount(cmd); OK, got it

Going beyond the data

In Empire-db the data is just one side of the coin. The other equally important side is metadata

Metadata is useful for many things in data processing. It is for example very useful to create generic functions that can be used with multiple entity-types like e.g. for data synchronization. But one of the most consequential applications of metadata is when using it to build a UI of listings, input forms and the like. What you are using metadata for is your business, but when it comes to metadata Empire-db has a lot to offer.

Metadata types

Empire-db supports more that just (trivial) metadata which is already provided with the data model definition like the data-type or the maximum number of characters of a column. We generally distinguish the following types of metadata:

Static model metadata

Static model metadata is provided together with the data-model and usually attached to a particular table or view column.

The column title used for e.g. for labels and table headers. This can also be a message key used for internationalization
A control-render-type that indicates which type of UI-widget should be used for rendering this column (for value input as well as display)
Number formatting options like e.g. number of fraction digits and whether or not to use a thousands separator
The unit of a column value like e.g. for currencies $, € or £
Additional HTML style-classes when rendering values in HTML

Additionally more user-defined metadata can easily be added to columns using the setAttribute() method.

Contextual metadata

Context specific metadata may depend on the user, the value of other fields or any kind of business logic. This is provided via the record (entity)

The set of context-specific options (allowed values) for a particular field in the given context
Whether or not a field is visible in this context
Whether or not a field is read-only in this context
Whether or not a field is mandatory in this context

Metadata Use-Case: building a UI form from metadata

This is what an input form for an Employee looks like when rendered by JavaServerFaces (JSF) using the empire-db-jsf extentions module:

Salutation:
Firstname:
Lastname:
Date of Birth:	[yyyy-MM-dd]
Department:
Gender:
Phone:
E-Mail:
Salary p.a.:	USD
Retired:
Last change:	Saturday, February 26, 2022 2:37:03 PM CET

Hint: Fields in yellow and with asterisk are mandatory fields.

And this is the corresponding JSF xhtml code for the input form:

xhtml

<!-- input form using empire-db controls -->
<e:record value="#{page.employeeRecord}">
<div class="formPanel">
    <e:formGrid mode="grid">
        <e:control column="#{db.EMPLOYEES.SALUTATION}"/>
        <e:control column="#{db.EMPLOYEES.FIRST_NAME}"/>
        <e:control column="#{db.EMPLOYEES.LAST_NAME}"/>
        <e:control column="#{db.EMPLOYEES.DATE_OF_BIRTH}"/>
        <e:control column="#{db.EMPLOYEES.DEPARTMENT_ID}"/>
        <e:control column="#{db.EMPLOYEES.GENDER}"/>
        <e:control column="#{db.EMPLOYEES.PHONE_NUMBER}"/>
        <e:control column="#{db.EMPLOYEES.EMAIL}"/>
        <e:control column="#{db.EMPLOYEES.SALARY}"/>
        <e:control column="#{db.EMPLOYEES.RETIRED}"/>
        <e:control column="#{db.EMPLOYEES.UPDATE_TIMESTAMP}"/> 
    </e:formGrid>
</div>
</e:record>

Final word

In software development things get complicated. Sometimes sooner, sometimes later, but they inevitably do.
What was once meant to be a "Simple Database", may well turn into a monster over the years, with ever more tables, views, columns, data.

So how do you tame the monster?

We believe the key to that are Simplicity and Flexibility.

By Simplicity we mean that there should be a strong correlation between your code and the SQL statements that are generated. This improves code readability and maintainability. And it makes coding easier. With Empire-db it is often easier to write a statement in code than typing it directly in SQL.
By Flexibility we mean that one can easily modify and extend the underlying functionality, even without deep knowledge of the underlying framework. In Empire-db you achieve this by simply subclassing the database, table, record, utils, context, dbms-handler, etc. Using only pure OO functionality, allows you to change or add application specific behaviour or add more user-defined metadata to your model.

One more Use-Case

In practice you almost never have "the one query" on a set of data. Rather your query often depends on the context and certain conditions for which you might need to dynamically select columns, add constraints and joins or set the row order.

Suppose your application provides a view of car dealers. A user might decide to list them all, or choose to set any of three possible filters: Country, Brand and/or the Minimum Annual Turnover. Depending on which of those filters the user chooses, different constraints and joins need to be added to the statement and even the columns that should be displayed (i.e. selected) differ. So how do you dynamically build such a query statement?

OK, show me how to do it

Java

void dealerQuery(String country, String brand, BigDecimal minTurnover) {        
    // create a command
    DBCommand cmd = context.createCommand();
    // select car dealer info
    cmd.select(DEALER.COMPANY_NAME, DEALER.CITY, DEALER.COUNTRY);
    // Constrain to country?
    if (country!=null)
        cmd.where (DEALER.COUNTRY.likeUpper(country));
    // Constrain to brand?
    if (brand!=null) {
        // Single brand
        cmd.join(DEALER.ID, DEALER_BRANDS.DEALER_ID)
           .where(BRAND.NAME.likeUpper(brand+"%"));
        // select the BRAND NAME
        cmd.select(BRAND.NAME);
    } else {
        // Show all brands a dealer distributes, separated by comma
        DBCommand qryCmd = context.createCommand()
            .select (DEALER_BRANDS.DEALER_ID, BRAND.NAME.strAgg(", ").qualified())
            .join   (DEALER_BRANDS.WMI, BRAND.WMI)
            .groupBy(DEALER_BRANDS.DEALER_ID);
        DBQuery qry = new DBQuery(qryCmd, "qbrands");
        // join with dealer query
        cmd.join(DEALER.ID, qry.column(DEALER_BRANDS.DEALER_ID));
        // select all brands as a list
        cmd.select(qry.column(BRAND.NAME.strAgg(", ")).as("ALL_BRANDS"));
    }
    // Constrain to min turnover? If so, select turnover
    if (minTurnover!=null) {
        // create subquery for sales
        DBCommand qryCmd = context.createCommand()
            .select(SALES.DEALER_ID, SALES.PRICE.sum().qualified())
            .where(SALES.YEAR.is(LocalDate.now().getYear()-1))
            .groupBy(SALES.DEALER_ID)
            .having(SALES.PRICE.sum().isMoreOrEqual(minTurnover));
        DBQuery qry = new DBQuery(qryCmd, "qsales");
        DBColumn PRICE_SUM = qry.column(SALES.PRICE.sum());
        // join with dealer query
        cmd.join(DEALER.ID, qry.column(SALES.DEALER_ID));
        // select the turnover
        cmd.select(PRICE_SUM.as("TURNOVER"));
        // order by turnover descending
        cmd.orderBy(PRICE_SUM.desc());
    }
    // order by
    cmd.orderBy(DEALER.COMPANY_NAME);
    
    // Done. For curiosity, get the row count
    int dealerCount = context.getUtils().queryRowCount(cmd);
    log.info("Query will return {} dealers", dealerCount);
    
    // Finally, execute the query and print the result
    List<DataListEntry> list = context.getUtils().queryDataList(cmd);
    for (DataListEntry item : list)
         System.out.println(item);
}

SQL (just one possiblity)

SELECT t3.COMPANY_NAME, t3.CITY, t3.COUNTRY, 
     qbrands.NAME_STRAGG AS ALL_BRANDS, qsales.PRICE_SUM AS TURNOVER
FROM DEALER t3
     INNER JOIN (SELECT t4.DEALER_ID, string_agg(t1.NAME,'|') AS NAME_STRAGG
                 FROM DEALER_BRANDS t4 INNER JOIN BRAND t1 ON t1.WMI = t4.WMI
                 GROUP BY t4.DEALER_ID
               ) qbrands ON qbrands.DEALER_ID = t3.ID
     INNER JOIN (SELECT t5.DEALER_ID, sum(t5.PRICE) AS PRICE_SUM
                 FROM SALES t5
                 WHERE t5.YEAR=2021
                 GROUP BY t5.DEALER_ID
                 HAVING sum(t5.PRICE)>=100000
               ) qsales ON qsales.DEALER_ID = t3.ID
WHERE ucase(t3.COUNTRY) LIKE ucase('USA')
ORDER BY qsales.PRICE_SUM DESC, t3.COMPANY_NAME

OK, that's cool.