For those of us on Java, I've grown to love http://jdbi.org/. JDBI has a lot of features that are convenient (e.g. auto-mapping of columns to a POJO), but synthesizes DAOs for you from interfaces annotated with SQL queries, e.g.
@RegisterMapperFactory(BeanMapperFactory.class)
public interface TripDAO {
@SqlQuery("SELECT trip_start AS start, MAX(timestamp) AS end FROM location_updates WHERE trip_start = :start GROUP BY trip_start ORDER BY trip_start DESC")
public Trip trip(@Bind("start") Date tripId);
}
And allows you to plug in providers for mixing in Optional types, PGgeometries, whatever you can fit into an interface.
I've been using JDBI for a new project for about a month or so and I like it a lot as well. I will say that the documentation is a bit sparse though, or at the least hard to find by digging through blog posts. When I figure out how to do what I'm trying to do however, I love it.
For example, it took me a while to find out that @CreateSqlObject was a thing and that it solved any hackery I was trying to do to have my DAOs reference one another. Or if you google "jdbi transactions," you aren't led to a page that actually shows you how to use @Transaction. I feel like the only thing holding back widespread use is some better documentation.
I'll agree with that. I had to really dig through the user group to find answers to a lot of things. e.g. @SingleValueResult if you want to return an Optional<Item> from a query.
Been using http://commons.apache.org/proper/commons-dbutils for the same purpose. Works well when I don't need the slede-hammer a full ORM-framework can be. Will look into JDBI as well next time.
I've benchmarked it and it adds roughly a 3% on top of raw JDBC, and allows different styles of usage. You can have your pojos annotated and get mapping for free, or (what I like) you can extract your SQL queries in XML files, name them and refer them from code with sql.insert("namedQuery", params);
It is super smart when it comes to mapping/aggregations and it is highly extensible to allow the transparent use of custom mappers and logics. Highly recommended.
I used mybatis at a former company. I liked it, but on the spectrum of ORM-like frameworks, it had just enough non-code configuration behind it that junior developers, or developers who aren't working in that type of code very often, struggle with how to get new data objects persisted. In my opinion, this stemmed from using XML files to define the mappers and queries. Most people just never knew where to look for that stuff, or exactly how it should be used.
In the end, I try to pick the option that is easiest to read and understand. I've had a terrible time with JPA/Hibernate (I hate that the ORM ends up affecting your schema at all), I had a good experience with Mybatis, and so far I'm really liking JDBI (although see my other comment here about documentation). I don't want raw JDBC, but JDBI seems to be the right tradeoff of power/ease of understanding. I think that the annotated use of mybatis would get you this as well, but I haven't used it.
I have also used MyBatis in few projects and recommend it for applications that are database centric. I would use ORM only if the requirements call for object persistence and be DB/SQL agnostic.
