Re: Embedded SQL in Java classes

Put the SQL in packages and return REF Cursors. To make development easier on your EJB guys allow them to pass Objects to the packages. You have to create corresponding types in the database, but this is simple. Its more natural for object oriented guys to pass in objects.

The best way to do this is to have a dedicated data access team that understands sql and understands your data. You can establish a contract with inputs and outputs. This way your middle tier team can go about coding, while your data access team builds the packages.

Views can be useful if you want to dynamically create objects with reflection(dont select from them). You can make views to match the objects you want to create. Then if you need to change objects all you have to do is change your views and you don't have to touch your database code. One of our modules actually uses mixed case column names in views to match to java standards. Its the 'contract' with the java team.

Putting SQL in java is a bad idea. For several reasons. First it ends up getting scattered all over the place. So its hard to debug. Second you don't know if it even compiles until runtime and third your java team needs to understand data access and work on the business logic at the same time. Its best to seperate this out. Its not just performance that can suffer. You increase the chance of simply returning bad data. I saw a case onces where the java team controlled all the sql. One of the sql statements returned many rows. They needed one. So they took the first one. The first one was not always the correct row. Received on Fri Mar 11 2005 - 15:02:25 CST