bwajtr/java-persistence-frameworks-comparison

Comparison of non-JPA SQL mapping frameworks for Java (Jooq, Spring JDBCTemplate, MyBatis, EBean, JDBI, Speedment, sql2o)

java-persistence-frameworks-comparison's Issues

EBean implementation

Implement scenarios using EBean

MyBatis Implementation

Implement scenarios using MyBatis

Apache Cayenne

Consider adding https://github.com/apache/cayenne/

Code Sample: https://github.com/rumatoest/jdblender/blob/master/cayenne/src/main/java/jdblender/cayenne/RunnerCayenne.java

Incorrect query to calculate a project's monthly cost

Thanks for doing this comparison.

I've stumbled upon this query here:

java-persistence-frameworks-comparison/src/main/java/com/clevergang/dbtests/repository/impl/jooq/JooqDataRepositoryImpl.java

Lines 227 to 263 in f6b3396

	CommonTableExpression<Record4<Integer, String, BigDecimal, String>> project_info =
	name("project_info")
	.fields("project_pid", "project_name", "monthly_cost", "company_name")
	.as(select(PROJECT.PID, PROJECT.NAME, EMPLOYEE.SALARY, COMPANY.NAME)
	.from(PROJECT)
	.join(PROJECTEMPLOYEE).on(PROJECT.PID.eq(PROJECTEMPLOYEE.PROJECT_PID))
	.join(EMPLOYEE).on(PROJECTEMPLOYEE.EMPLOYEE_PID.eq(EMPLOYEE.PID))
	.leftJoin(DEPARTMENT).on(EMPLOYEE.DEPARTMENT_PID.eq(DEPARTMENT.PID))
	.leftJoin(COMPANY).on(DEPARTMENT.COMPANY_PID.eq(COMPANY.PID))
	);
	Field<BigDecimal> monthly_cost = project_info.field("monthly_cost", BigDecimal.class);
	Field<?> project_name = project_info.field("project_name");
	Field<?> company_name = project_info.field("company_name");
	CommonTableExpression<Record2<Integer, BigDecimal>> project_cost =
	name("project_cost")
	.fields("project_pid", "total_cost")
	.as(select(project_info.field("project_pid", Integer.class), sum(monthly_cost))
	.from(project_info)
	.groupBy(project_info.field("project_pid"))
	);
	Field<BigDecimal> total_cost = project_cost.field("total_cost", BigDecimal.class);
	SelectSeekStep1<? extends Record4<?, BigDecimal, ?, BigDecimal>, ?> query = create
	.with(project_info)
	.with(project_cost)
	.select(project_name, total_cost, company_name, sum(monthly_cost).as("company_cost"))
	.from(project_info)
	.join(project_cost).using(project_info.field("project_pid"))
	.where(total_cost.greaterThan(new BigDecimal(totalCostBoundary)))
	.groupBy(project_name, total_cost, company_name)
	.orderBy(company_name);
	return query.fetchInto(ProjectsWithCostsGreaterThanOutput.class);

I'm sure there's an easier way to write this with jOOQ (or with all other solutions, as the query does contain a bit of redundancy), but first, I'd like to point out that your using a many to many relationship between employee and project seems to hint at the query being wrong as an employee who is assigned to several projects will produce more than their actual costs. I suspect a weighted average should be used instead.

Once this has been done, I guess that by using window functions, you will achieve a much more straightforward (and faster!) solution in all frameworks.

I hope this helps

MyBatis

My be you must update Mybatis, cause many improvement current release....

Missing explicit gradle project name

Gradle is missing explicit project name, therefore builds in Teamcity generate weird jar artifact names...

sql2o implementation

See http://www.sql2o.org/

There is integration with Spring too: http://www.sql2o.org/docs/spring/

Jdbi 3.x comparison

Hi. This repository was a popular link on reddit.com/r/java today.

At the time you reviewed these frameworks, you used Jdbi 2.77, while Jdbi 3 was still in beta. The most recent release is 3.6.0, and we've done a massive overhaul of our docs at jdbi.org.

Would you be willing to do a comparison with the current version of Jdbi, given the current state of our documentation?

jdbi implementation

I took a quick look at jdbi - if it's comparable framework to JDBCTemplate then prepare implementation in jdbi

QueryDSL SQL implementation

Check here: https://github.com/querydsl/querydsl/tree/master/querydsl-sql

Modality ORM

Hi.

This is a request for the comparison to include the Modality ORM.

Thanks.

WRT Ebean ToMany "lazy fetch" and N + 1

Hi,

So there is a comment in the readme about Ebean OneToMany and "lazy fetch" that I'll just respond to a little bit.

Ebean query language allows us to fetch lazy, fetch eagerly, or fetch eagerly with a separate query ... for each path. The important point here around with orm query language design is if there is support for fetch eagerly with a separate query which in Ebean we call fetchQuery. This matters if we care about sql cartesian product &/or honoring max rows in sql (database side max rows vs client side max rows).

That is, an ORM that only has fetch (and not a fetchQuery) will produce sql cartesian product when fetching multiple ToMany paths. It will also perform client side row limiting with a query that has maxRows + fetching a ToMany path.

That is, ORM in general, it is building graphs. As such the paths of the graphs can be cardinality ToOne or ToMany. If we are building a graph with 2 (or more) paths that are ToMany and doing that as a single sql statement this produces a sql cartesian product. Ebean's query language and implementation is specifically designed to handle this (and always honor max rows in sql because that is an important database optimisation).

https://ebean.io/docs/query/fetch
https://ebean.io/docs/query/fetch#rules

In comparison to JPQL which only has fetch join - I view that as one of it's major design flaws.

but you can still be hit by the N+1

Lazy loading in Ebean by default is 1 + N/10, but the point is to use the query language to specify the paths to fetch (or automatic query tuning based on profiling).

partial objects

This isn't mentioned but the other major aspect of ORM query language is the question of supporting "partial objects" (only fetching the properties that we need).

e.g. Find customer by id ... with id, name only
e.g. Find customer by id ... with id, name + fetch billingAddress
e.g. Find customer by id ... with all properties

With Ebean we can specify this using FetchGroup https://ebean.io/docs/query/fetchgroup ... or on the query itself. FetchGroup effectively specifies the "projection of the graph".

Speedment implementation

https://github.com/speedment/speedment

Spring Data JPA

Quote:
'So we dropped JPA completely, started using JDBCTemplate and discovered that we can deliver apps sooner (which was kind of surprising), they are a lot faster (thanks to effective use of DB) and much more robust... This was really relaxing and we do not plan to return to JPA at all... (yes, even for CRUD applications!)'

That is fine. I just want to ask one question :) Before you dropped JPA, have you tried JPA... with Spring Data?
http://projects.spring.io/spring-data-jpa/
http://www.baeldung.com/the-persistence-layer-with-spring-data-jpa
http://docs.spring.io/spring-data/data-jpa/docs/1.1.x/reference/html/#jpa.query-methods.query-creation
or switched before that? ;)

spring-data-jdbc-repository implementation

Not sure if it makes sense to do implementation using this library, but we should at least check it:

https://github.com/jirutka/spring-data-jdbc-repository