To Hibernate or Not?

[AhFai Chan]

I intent to wrap stored procedures around my db-instance and developers can use the stored procedures to access information. It worked for me using JavaFX and PHP

My new proposed environment is    browser <-> webapp (probably JSP)   <->  servlet (probably Swing, but not XML) <-> JDBC <->  db-instance

On the one hand, I was thinking I should map all the fields in stored procedures to Hibernate, on the other hand, I am trying to minimize the components and frameworks and APIs in the ecosphere. If later, I will be the one doing the object to relational mapping.

So, what is better, use Hibernate or just plain old Java in Swing to execute the stored procedures and get the data?

[Tim Holloway]

Better yet, don't use stored procedures.

There is no universal stored procedure language the way there is for SQL. If you had worked for Amazon and written tons of stored Oracle procedures you would have been left having to explain why hundreds of hours would have to be budgeted to convert them all to be PostgreSQL stored procedures (or Java code). Unlike Java, stored procedures are not "write-once/run anywhere".

Stored procedures also load down the database server with computational loads, There are often dozens of application servers sharing one DBMS server. Where you you think all the work should go?

Plus historically, stored procedures, being kept in the database rather than in files, have not been very friendly to source-code control systems.

And finally, splitting logic between application servers and the DBMS server (stored procedures) can lead to time-wasting "treasure hunts" where maintenance personnel have to chase back and forth figuring out what gets done where.

Stored procedures have their place, but they should be the exception, not the first choice.


Hibernate - with or without stored procedures - works best with high-volume complex database operations. There's quite a bit of overhead in cranking up and running an ORM. Though the same can be said for Java versus Python or JavaScript. If you have the load to justify it, it can more than pay for itself, but first you should consider whether the added complexity is worth it.

[Tim Cooke]

I have yet to work on a project where Hibernate has not been a hindrance to development.

[Tim Holloway]

Tim Cooke wrote:I have yet to work on a project where Hibernate has not been a hindrance to development.

I'd give you one of mine, but they still owe me money on it.

It has compound keys, really gnarly table joins and a high enough throughput rate that the ORM dynamic optimizers can pull ahead of raw JDBC in processing speed. Hibernate JPA is the only reason I had time to work on the UI and business logic instead of screaming at broken SQL.

ORMs are not for everything, but when they fit, they can really shine.

[Tim Moores]

AhFai Chan wrote:what is better, use Hibernate or just plain old Java in Swing to execute the stored procedures and get the data?

That's really several choices wrapped into one. The first is stored procs vs. no stored procs, but that decision seems to have been made already. The second is ORM vs. no ORM. The third is proprietary ORM (such as Hibernate) vs. JPA.

[Rob Spoor]

Tim Moores wrote:The third is proprietary ORM (such as Hibernate) vs. JPA.

That should be a no-brainer. Hibernate implements the JPA spec, and there isn't much that can be done in Hibernate that isn't part of JPA. The advantage of using JPA is that migrating to a different implementation becomes a lot easier. I've migrated a WebLogic application that used TopLink to Spring Boot with Hibernate. The database layer required just a few small changes, mostly in cases where we used some TopLink specific behaviour.

[Tim Holloway]

To clarify what Rob said, Hibernate comes in 2 flavors:

o Legacy Hibernate, the original product. A proprietary product.

o Hibernate JPA, which conforms to the JEE Java Persistence Architecture (JPA) of the Enterprise Java Beans (EJB) specification 3.0

I do not recommend using Legacy Hibernate. I don't know how much longer it will be an active product. The Hibernate.org group were one of the major contributors to the creation of JPA and at this point, if there's something missing from either flavor, it's more likely to be unimplemented in legacy Hibernate.

You can easily tell which flavor you're working with. In legacy Hibernate, you work with Hibernate Sessions (Not to be confused with JEE HttpSessions!). In JPA, you work with EntityManagers.

I once had a product that was using Apache OpenJPA and I needed a feature that didn't function properly in that particular version. Since the project was Maven-based, I edited the POM, changing the persistence library dependencies and made a small change in the ORM config file. It took less than 15 minutes to switch the app from running under OpenJPA to running under Hibernate JPA. Such is the power of using an industry standard spec.

[Dan Howard]

I would recommend Persim instead https://github.com/sproket/Persism

[Tim Cooke]

Dan Howard wrote:I would recommend Persim instead https://github.com/sproket/Persism

Why?

[Tim Holloway]

Persim is not JPA-compliant.

It is, however, much lighter than a JPA-compliant ORM could be, since it does not attempt to replicate all of the functions that JPA supports. It also has the virtue of being able to construct (they claim) ORM classes by introspecting an existing database (as long as it's one of the supported ones - JPA works with any database that has a JDBC driver).

This means that it's not a bad option for embedded systems. Although I should note that there's already a (non-JPA) ORM in popular use on Android devices.

[Rob Spoor]

Tim Cooke wrote:

Dan Howard wrote:I would recommend Persim instead https://github.com/sproket/Persism

Why?

Because he wrote it
See Persism 1.2.0 Released - a zero ceremony ORM for Java.

[Tim Holloway]

Rob Spoor wrote:Because he wrote it

[Tim Cooke]

Rob Spoor wrote:Because he wrote it

Ha ha, good detective work Rob.

[AhFai Chan]

Tim Holloway wrote:Better yet, don't use stored procedures.
There is no universal stored procedure language the way there is for SQL.

Thank-you, everyone, for giving me your insight and experience.

Tim, couple of things, there's no universal SQL either, there are different SQL dialects, PL/SQL won't run in MS SQL and MS SQL won't run on MySQL e.g.

select a,b,d c from table order by 2,3

will run on Oracle, but not the other.
Another thing, there will come a time when I have to run SQL analytics for tens of thousands of rows and return discrete values, no ORM mappings there, just values.

I can't make UI look good even with Springboot and CSS ...

[Tim Holloway]

True. There is a standard SQL, however. There is no standard stored procedure language.

Much of the non-standard SQL has been obsoleted by later additions to the standard, but there are still going to be cases where that isn't so.

ORMs can be useful there since if you're using JPQL and not native queries, some of the remaining quirks will be normalized in JPQL so that switching DBMS vendors can be made less painful. Also you can now put the actual query strings into the Entity definitions, making them easier to locate and maintain, as opposed to having them splattered all over the persistence code.

You can also use JPA to return single-column and/or single-value results as well as returning only selected columns for those 150-column field tables where you only need 6 columns. Though sometimes defining a View in the database itself is more efficient both for the app and the database server itself.

As for ugly UI's, that is my problem too. I'm not an artist and even with the most powerful tools in the world, what I produce is laughed at. It's a problem because efficient, secure, and reliable software isn't visible to Management, but they get all excited if some sloppy person can produce beautiful web pages.

[Dan Howard]

>> Persim is not JPA-compliant.

True.


>> It also has the virtue of being able to construct (they claim) ORM classes by introspecting an existing database (as long as it's one of the supported ones - JPA works with any database that has a JDBC driver).

No Persism doesn't claim that. You can use existing tools to generate classes. It doesn't require or use the JPA annotations. It uses only a few of it's own which are optional - only required if something can't be discovered by Persism automatically.

Persism is designed to be zero dependency and very low friction.

Persism supports Derby, Firebird, H2, HSQLDB, Informix, MSAccess, MSSQL, MySQL/MariaDB, Oracle (12+), PostgreSQL, SQLite.