views:

105

answers:

2

Hello!

I have a problem with the architecture of JPA 2.0/ORM, in our production system (and i believe in a lot of systems) we need the ability to change the SQL queries dynamically because slow queries and bugs in queries that was exposed only in production (heavy load and heavy data), as a result we used in stored procedures and call them from iBatis.

As i see the pattern, the best practice is to seperate between the DB layer and the application layer so i can tell to my DBA to fix buggy stored procedures/indexes tables in production without deployed new application (distribute system - long time of deployment).

In ORM/JPA 2.0 the named queries defined in the code and it's cause to the programmer to catch the whole DB problems in development/QA - very bad!

I saw in the API that the framework give an option to define native query - but in the books/tutorials the best practice is to use the named queries...

After i read the Hibernate/JPA 2.0 SPEC to see if there is a solution for this problem, i understand that there is no solution...?

Its look to me very wired that i need to define the queries hard coded in the application code layer... also to define the queries in XML descriptor and to load the XML via patch solution of hot deploy - very bad and not as standard!

Did you have design pattern/solution

Thank you all!!!

Uri.

+4  A: 

I'd advise that you do unit and performance testing before you deploy. You shouldn't be finding out about buggy or slow queries at that late juncture.

JPA/ORM is not like iBatis, as you are finding out.

"Buggy" sounds like a lack of thorough unit testing.

"Slow" sounds like your DBAs aren't checking the SQL generated by JPA. EXPLAIN PLAN for all of it. You might have indexing or schema issues on the database side that no amount of playing with JPA will fix.

Patterns aren't the solution.

duffymo
Yes but if really need to be dynamically
Uri
I agree with you!
Uri
But still if i found a bug in production, i still want to fix it on production system - and stored procedures is the only way to do it... under this requirments whats is the best solution. Thanks!
Uri
Stored procedures are not the only way to do it. If you need it to be that dynamic, you probably shouldn't be using ORM. What made you switch from iBatis in the first place?
duffymo
A: 

You should be able to have your entities read data from views as well as tables. Then the sql for the views can be altered on the fly.

Jon Martin Solaas