Meaningless Drivel is fun!*
The moose likes EJB and other Java EE Technologies and the fly likes Calling Postgres Stored Function from EJB Session Bean Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Calling Postgres Stored Function from EJB Session Bean" Watch "Calling Postgres Stored Function from EJB Session Bean" New topic
Author

Calling Postgres Stored Function from EJB Session Bean

Alex Pato
Greenhorn

Joined: Nov 08, 2011
Posts: 9
Hi all,

I'd like to know whether it is possible to call a Postgres Stored Function (PLPGSQL) from within my EJB Session Bean. I've been searching for a clear example, but haven't come a clear cut example.

Been trying with a StoredFunctionCall object, but don't know what I'm doing wrong. I'm yet to try the createNativeQuery method on the EntityManager ... thought that it was more for stored procedures.

Any feedback would be appreciated.
Jimmy Clark
Ranch Hand

Joined: Apr 16, 2008
Posts: 2187
I'd like to know whether it is possible to call a Postgres Stored Function (PLPGSQL) from within my EJB Session Bean. I've been searching for a clear example, but haven't come a clear cut example.


Instead of "searching the Internet" for an example, I'd suggest spending some time thinking about the task and working your own solution. Code statements or functionality for calling external systems would not be written in the EJB classes. This is not their intended use. Session EJB are intended to execute business/domain logic and should do this via Business Object. The Business Object contains business logic and uses a Data Access Object to execute data access logic. The connection to a external system such as a relational database happens via Data Access Object and the code statements for this are in the Data Access Object, not the Session EJB or the Business Object.

If you code business logic or data access object directly in EJB classes, then you are creating a dependency directly on the EJB API in general and a "specific version" of the API as well. This is a poor design with restricted ability to handle change over time.
Alex Pato
Greenhorn

Joined: Nov 08, 2011
Posts: 9
Jimmy Clark wrote:
I'd like to know whether it is possible to call a Postgres Stored Function (PLPGSQL) from within my EJB Session Bean. I've been searching for a clear example, but haven't come a clear cut example.


Instead of "searching the Internet" for an example, I'd suggest spending some time thinking about the task and working your own solution. Code statements or functionality for calling external systems would not be written in the EJB classes. This is not their intended use. Session EJB are intended to execute business/domain logic and should do this via Business Object. The Business Object contains business logic and uses a Data Access Object to execute data access logic. The connection to a external system such as a relational database happens via Data Access Object and the code statements for this are in the Data Access Object, not the Session EJB or the Business Object.

If you code business logic or data access object directly in EJB classes, then you are creating a dependency directly on the EJB API in general and a "specific version" of the API as well. This is a poor design with restricted ability to handle change over time.

Thanks for the reply.

I think I'm just confused because I'm developing my project on Eclipse. I developed one before on Netbeans, and it was rather clear what was an entity bean and session/message driven bean. Here, with JPAs and Client modules, I'm a little lost.

The reason why I want to use stored procedures is ... I have quite a large database to manage. 200+ tables. When a table is updates, many others are as well. I thought I would save on performance IF I could reduce the amount of times the application server would have to communicate with the database. I initially used triggers in my database, but since some tables relied on the auto_generated IDs created in 'parent' tables, triggers will not suffice. Which is why I was thinking of inserting data into my databases via stored procedures.

Secondly, I would need to build a few reports. This would involve many joins and what not in order to build the reports. I haven't yet decided which technology I'm going to use to build the reports, but I've read that stored procedures are almost essential when it comes to building large reports.

Am I making sense or not? If I am, then I'm thinking a stored procedure call would require some code in the Entity Beans as well a DAOs? If you had any recommendations for report building, please let me know.

Thanks again.
Jimmy Clark
Ranch Hand

Joined: Apr 16, 2008
Posts: 2187
Sounds good. It also sounds like you are getting lost in IDE world and all the "wonderful distractions" they offer.

Using stored procedures is a best practice and should always execute more efficiently that raw SQL statements from application code.

...then I'm thinking a stored procedure call would require some code in the Entity Beans as well a DAOs.


A good first step would be to create a simple plain Java object with a main method and add a method that will connect to a database and call a stored procedure. Execute the main method from a command-line during development. Once you get this working correctly, you will be ready to look at how to hook it up with the rest of the code.

Aside, if you have a Data Acess object which is executing calls to an external system to perfrom some CRUD operations, then you do not need Entity Beans for these operations.
Andrew Moko
Ranch Hand

Joined: Dec 16, 2011
Posts: 55
Looks like the OP wants his requirements fit the EJB technology. I still remember the hype that came with design patterns most developers would complicate a simple logic just to take advantage of the patterns.

Alex Pato
Greenhorn

Joined: Nov 08, 2011
Posts: 9
So are you saying EJB technology is not an efficient way to access large databases? Of course, there are many other simpler CRUD operations that I would like to implement using EJB Session and Entity Beans ... I was under the impression its a much more robust and secure way to go about accessing a database, as opposed to a 'simple' JDBC connection, which would be more prone to error and/or security leaks, with the database connection strings being in the source code of the application. I could use the Singleton pattern to connect to the database, but I just thought EJB was much more stable and secure. By letting glassfish handle the connection, and disabling its admin console on the server, I was under the impression that it was the most secure way to execute all database transactions.

Which is why I thought of using EJB's or rather glassfish's connectivity with the database to execute my stored procedures. This way I would have the advantages of both the security and easier manageability of EJB, with the added performance of stored procedures when I would require them.

Finally, with regards to Java Report Builders, which one would you recommend?

Thanks a lot for your input. I've been unsure of which Java-based technology I should use to implement my application. Basically security and performance are the main functional requirements I should base my decision on. I've already built the database in Postgres, so aside from that, nothing else has been established/decided yet. I intend to 'encapsulate' my business logic with a Web Service layer, so that different front-end applications can access the back-end.

Best regards.
Alex Pato
Greenhorn

Joined: Nov 08, 2011
Posts: 9
Bump:

Well, I was able to call a simple stored procedure in a Session Bean using the createNativeQuery() method of the Entity Manager. I'm sure it will become quite tricky when dealing with reports (getting the result sets), so I'm going to try working on a more difficult test.

This doesn't mean that I've decided to use these technologies, I'm still waiting on your feedback with regards to EJB's efficiency when dealing with large data sources + the benefits it brings ... whether my thinking was right or not.

Also, after some research on Java-base report building technologies, I've singled out BIRT (Eclipse) and Jasper Reports. I mainly need the 'drill down' capability intact. Also noted Business intelligence (Mozilla Public License). I can only use Open-Source software.

Jimmy Clark
Ranch Hand

Joined: Apr 16, 2008
Posts: 2187
So are you saying EJB technology is not an efficient way to access large databases?


No, that is not what I am saying. I simply provided a direction to enable you to execute external stored procedures from object code, i.e. a Session EJB. This size of a database is largely irrelevant when choosing which API to use. Efficiency comes mostly from how well "you" design and code the application, the stored procedures, the database, not the public API or framework that you are using.

It is better for beginners to start off simple with basic tasks implemented without complex API. Once you get little pieces working, then you can start to bring in more complex code after some analysis and thinking.

Check out C3PO http://sourceforge.net/projects/c3p0/

BIRT is a good tool.
Alex Pato
Greenhorn

Joined: Nov 08, 2011
Posts: 9
Hi Jimmy,

Thanks for responding. Really appreciate your help.

I'm a little confused over your mentioning C3P0. I browsed through it and I don't get a clear cut picture. Are you suggesting that I use it in place of JPA (EclipseLink) and EJB Entity Beans? I'm currently intending to use the Postgres Connection Pool I created in the glassfish admin console with Entity Beans. As you stated, the efficiency of the system doesn't depend on the technologies I use but how well I implement my business logic. So I'm thinking there's another reason why you suggested C3P0 which I didn't quite catch. Could you kindly elaborate?

Now that I'm able to call Postgres Stored procedures from Session Beans, plus do simpler transactions via Entity Beans, I think that I'm just about ready to dig into my business logic. I'm going to try a simple example with BIRT, mainly testing the "drill down" facility to see if it works.

Thanks again for your responses.
Joemon Anthony
Greenhorn

Joined: May 22, 2011
Posts: 23

Hi Alex, We are using EJB3 and telling you the truth if you are looking for security and transaction then it is the best option available.


(SCJP 5, OCA 9i)
(Always interested in learning)
Alex Pato
Greenhorn

Joined: Nov 08, 2011
Posts: 9
Thanks Anthony. I've decided to use EJB3, with a few Postgres stored procedure calls when there are quite a bit of updates required. Hopefully it will do the trick. If not, I'll just use EJB to update all these tables and do away with the stored procedures altogether. I already have a few triggers, so I hope that improves the performance of my system.
Joemon Anthony
Greenhorn

Joined: May 22, 2011
Posts: 23

OK. Best of luck.
 
jQuery in Action, 2nd edition
 
subject: Calling Postgres Stored Function from EJB Session Bean