Can you use stored procedures in EJB 3.0?

[flb]

I've read that Hibernate, Toplink and probably some other persistence frameworks support stored procedures but I haven't seen anything in the JSR220 specs related to stored procedures.

So can you use them in EJB 3.0 in conjunction with the EntityManager? Will it be a vendor specific extension?

PS: I'm not a fan of Stored Procs but if you work for some clients you don't have a choice...

[treespace]

Stored procedures have performance benefits and also provide a uniform implementation when you have Java and other languages using the same resources. If some client insists on using stored procedures use JDBC. You can call stored procedures from POJOs or old-style EJB code. Cash client check as usual.

If a client insists on stored procedures exclusivesly, recommend a native language to develop their app. PL/SQL for example. If they want to use EJB 3.0 for persistence, cash the check, and use session beans. You could also explain that it's an idiotic requirement and walk.

Seems to me the "out" parameters of stored procedures will make it difficult to generalize, and it's even more egregious than native SQL in terms of portability. You can call stored procedures using JDBC when the exceptional case arises, but I will lose no sleep if they never consider them for entity manager.

[redijedi]

You can use stored procs using the NamedNativeQuery syntax. Check out http://docs.jboss.org/ejb3/app-server/HibernateEntityManager/reference/en/html_single/index.html#query_native.

Most devs that are dead set against stored procs do not have an open mind. Stored procs offer an interface into the db that is consistent. They also let your dba/dbd do his job and ensure correct operation/isolation of the underlying schema as well as give him the control that he requires to do his job optimally. You do not need to use out parameters, you'd just return resultsets that you'll map onto an object. It's a fine choice for those companies that require fine grained control over database access, query optimization, blah, blah... At my company we run a lot (I mean a lot) of simultaneous transactions. We appreciate the control that we have using stored procs. We've had trouble with giving over complete control to ORMs with deadlocking. Of course, this won't happen for most applications, but most applications are not highly concurrent. The point is that when you knock (or see someone knocking) a technology like stored procs, they usually don't really understand the uses (good and/or bad) of the technology. Just think of it as another tool that you'll be better off understanding even if you never use it again.

[treespace]

>> Most devs that are dead set against stored procs do not have an open mind.

Too strong. I think also that he was just saying he was not a "fan" versus anti-stored-procedure.

>> Stored procs offer an interface into the db that is consistent.

That is to say they provide a "uniform implementation" which I noted as beneficial.

>> They also let your dba/dbd do his job and ensure correct operation/isolation of the underlying schema as well as give him the control that he requires to do his job optimally.

Stored procedures do not ensure anything other than a lack of portability. That aside the provide the opportunity for skilled database application programmers (a DBA or DBD may or may not fall under that heading) to use the native procedural language to great benefit.

>> You do not need to use out parameters, you'd just return resultsets that you'll map onto an object.

You need to use out parameters if the existing library is littered with them. It doesn't help to declare them optional after the fact.

>> It's a fine choice for those companies that require fine grained control over database access, ...

It's a fine alternative to POJO persistence using EJB 3.0 entity beans. I'm choosing the latter every time, given the choice, with a sprinkling of native stored procedure calls if necessary. They are competing strategies.

>> The point is that when you knock (or see someone knocking) a technology like stored procs, they usually don't really understand the uses (good and/or bad) of the technology.

I would agree that detractors are usually sacrificing the benefits of control and consistency at the alter of portability. Usually a worthy sacrifice but as mentioned, stored procedures definitely have their place.

[treespace]

FTB, here's an example of an EJB stored procedure. You can use these from, say, a session bean:

Query query = em.createNativeQuery("exec foo()" );

List bars = query.getResultList();

[redijedi]

I agress with most of what you've said.

>> Stored procedures do not ensure anything other than a lack of portability.

I do know that this is always brought up, but it is irrelevant in most cases. Enterprises do not switch out their database vendors nearly often enough for this to be a remote care. If you are developing components for resale, that's another story. But if you are building code for a company that serves an explicit need, portability should not be on your top ten priority list. There are far more important matters to address first.

Not to get off topic, but I don't see a great benefit to having developers learn a sql language that is not true sql. I contend that the odds are greater that your object layer has a much better chance of moving from java to .net than your db moving from sql server to oracle. Considering this, just learn SQL. It's not hard. It's the single most transitive skill for a developer to have.