Apache Empire-db 让您可以不受限制地使用 Java 中的关系型 DBMS，从而释放数据库系统的全部功能。
忘掉 OR-Mapping、Entity Beans、Lazy vs Eager、Bytecode Proxies、TupleQuery 和黑盒魔法，重新获得完全的 SQL 自由。
没有注释的疯狂，没有映射文件的痛苦，没有花哨的东西来表示你的数据模型。只是简单的旧 Java API。
以最简单、最直观的方式在代码中创建最复杂的 SQL，并且“无附加条件”（字面意思！）
// 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, BRAND.WMI)
.joinLeft(MODEL.ID, SALES.MODEL_ID, 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
|
详细点击标题
100% 开源。100% 免费。轻巧直接。
支持 Oracle、SQLServer、PostgreSQL、MySQL、HsqlDB、Derby、H2 等…