JPA coexistence with legacy native SQL

Question

I see always more java web applications using ORM frameworks for mapping entities to the database and apparently making the serialization of objects easier.

This looks good and usually involves a lot of code like:

@Entity
@Table(name="Flight")
public class Flight implements Serializable {
    Long id;

    @Id
    public Long getId() { return id; }

    public void setId(Long id) { this.id = id; }
}  

The above maps the Flight POJO to a database table named Flight, this seems quite reasonable for a new application designed from ground up.

However is it a viable solution to use an ORM solution like JPA when an application has to be developed with legacy tables and logic?

In other words is it possible to use ORM tables and legacy tables together? How can I map legacy tables into POJO?

I have seen similar questions like legacy tables to jpa2 entities and generate object @entities from database however they all talk about tools to "reverse engineer" tables to objects. Shouldn't there be an option to do it by hand and once mapped correctly let the ORM framework manage them? It all seems like a hack doing what I am asking for however transforming legacy databases to ORM managed ones should be the rule in my opinion.

Thank you

Answer 1

Yes, you can use ORM for existing databases. I don't know how flexible JPA is these days (haven't kept with all the spec changes), but i know that Hibernate is very flexible when it comes to matching the objects to the existing tables (supporting a wide variety of legacy configurations). There's nothing stopping you from using ORM and straight JDBC side-by-side (possibly some sync issues if you are managing updates through both channels). in fact, for some scenarios straight JDBC may make sense (e.g. ad hoc reporting). again, i'm not sure what all JPA provides, but i do know that a hibernate Session allows you access to the underlying JDBC connection so that you can run native SQL if/when you need to.

Answer 2

The term non ORM database is not correct, a database may be relational (RDBMS), object oriented, document oriented, etc... ORM means Object relational mapping, It only refers to a kind of framework which are intended to ease RDBMS usage with object oriented languages.

You can perfectly use together JPA and native sql to query a database, you can even easily generate all your database entities from your database schema using JPA tools.

BUT

you have to carefully pay attention to those facts :

The ORM will manage an in-memory projection state of your database as java entities, the collection of managed entity is managed by an object 'EntityManager' (or Session in hibernate). This object won't be aware of operations made using native sql, neither the ones in the program nor the ones in the database (eventual trigger or stored proc). The state of your entities may so be inconsistent with the database. You can manually refresh the entities state but you have to care about it.

You should execute native query using JPA api to take advantage of underlying datasource connection pooling. You could otherwise use native JDBC statements if you really need it (i don't really see why) but you should also perform it against the same datasource. In this case you won't be able to take advantage of transactional context propagation (you will use your own sql connection for native sql queries) if using a container managed entityManager

Answer 3

Have a look at it from a different perspective: More and more web applications are not using "direct SQL" any longer. This enlarges the "distance" between the web developer and SQL/RDBMS. It creates a gap.

There are even libraries which go in the opposite direction: No more HQL, just plain SQL. For example, there is ANORM, and this means "Anorm is Not an Object Relational Mapper".

The given example looks fine and easy. But the whole story is not just table definitions and insert/update/delete. Later you have to query your data, and you may want to query it as fast as possible using all features of your database (window aggregate functions etc.), and at this point ORM could be tricky.

As for your question: I suggest inspecting the current data model and queries carefully. Write some prototypes to see whether/how both ways to access data co-exist (caching issues, integration issues etc.). If you cannot switch completely to an ORM mapper, it might be easier to stick with "plain old SQL", and that's OK. It's not the mapper that matters: At the end of the day it's the SQL commands which arrive at your SQL server.

And make sure that you can use the most recent version of JPA. One of the earlier versions did not even feature case insensitive sorting. Generally speaking as the version is often defined by your data center (= you have no choice), check if this version does support all the features you need.