Apache Empire-db


	Full SQL Freedom for Java
	Version 3.2.0 Released 2024-06-25

Apache Empire-db lets you utilize Relational-DBMS in Java without limitations, thus allowing to unleash the full power of the database-system.
Forget OR-Mapping, Entity Beans, Lazy vs Eager, Bytecode Proxies, TupleQuery and black box magic and reclaim your full SQL freedom.
No Annotation madness, No Mapping-File pain, No fancy stuff to represent your data model. Just simple old, plain old Java API.
Create even the most complex SQL in code in the simplest and most intuitive way, with "No Strings attached" (literally!)
Improve Compile-Time-Safety and code maintainability. Write DBMS independent code. Benefit from Identity-management and concurrency control. Perform DDL operations. Access Metadata. You name it…
Clever Object Model lets you customize and extend almost every aspect of its API by simple subclassing and overriding. No rocket science!
100% Open-Source. 100% Free. Lightweight and straightforward.
Support for Oracle, SQLServer, PostgreSQL, MySQL, HsqlDB, Derby, H2 and more…

Natural selection

Here's a little appetizer showing a query of car model sales:

Java

// create a command
DBCommand cmd = context.createCommand()
   .select  (BRAND.NAME, MODEL.SPECIFICATION, MODEL.BASE_PRICE)
   .select  (SALES.MODEL_ID.count(), SALES.PRICE.avg())
   .select  (SALES.PRICE.avg().minus(MODEL.BASE_PRICE.avg()).round(2).as("DIFFERENCE"))
   .join    (MODEL.WMI.on(BRAND.WMI))
   .joinLeft(MODEL.ID.on(SALES.MODEL_ID).and(SALES.YEAR.is(2021)))  // only year 2021
   .where   (MODEL.ENGINE_TYPE.in(EngineType.P, EngineType.H, EngineType.E)) // Petrol, Hybrid, Electric
   .where   (MODEL.BASE_PRICE.isGreaterThan(30000))
   .groupBy (BRAND.NAME, MODEL.SPECIFICATION, MODEL.BASE_PRICE)
   .having  (SALES.MODEL_ID.count().isGreaterThan(5))   // more than 5 sales
   .orderBy (BRAND.NAME.desc(), MODEL.SPECIFICATION.asc());

// Return a list of Java beans (needs matching fields constructor or setter methods)
// This is just one of several options to obtain and process query results
List<QueryResult> list = context.getUtils().queryBeanList(cmd, QueryResult.class, null);
log.info("queryBeanList returned {} items", list.size());

SQL

SELECT t1.NAME, t2.SPECIFICATION, t2.BASE_PRICE
   , count(t5.MODEL_ID), avg(t5.PRICE)
   , round(avg(t5.PRICE)-avg(t2.BASE_PRICE),2) AS DIFFERENCE
FROM MODEL t2
     INNER JOIN BRAND t1 ON t1.WMI = t2.WMI
     LEFT JOIN SALES t5 ON t5.MODEL_ID = t2.ID AND t5.YEAR=2021
WHERE t2.ENGINE_TYPE IN ('P', 'H', 'E') AND t2.BASE_PRICE>30000
GROUP BY t1.NAME, t2.SPECIFICATION, t2.BASE_PRICE
HAVING count(t5.MODEL_ID)>5
ORDER BY t1.NAME DESC, t2.SPECIFICATION

Want to know more?
Then click no further than here