EJBQL vs SQL

[Yi Meng]

Although we can use EJBQL for entity bean select/find method in a way that is vender independent and .... many more, but since EJBQL only supports "SELECT" we still have to use vender-dependent SQL command if/when we want to do insert/update/delete in session bean or home business method of entity bean, right?
So in essence, we have to put every vender-dependent SQL command into the DD as env-entry so that we dun have to modify the code and still achieve portability.

[Bo Ling]

you are supposed to use business,create and remove methods to do the upate,insert and delete of records.

[Yi Meng]

Originally posted by Bo Ling:
you are supposed to use business,create and remove methods to do the upate,insert and delete of records.

yes, but how do you do that in business,create and remove methods of a session bean? You have to use JDBC in the case of relational database, right? And you need sql commands, but obviously sql commands are not always the same for different databases(venders).

[Boyan Wu]

I guess because the remove and update relies only on primary key, which means as long as the primary key class of the entity bean is known the underlying entity can be identified. Therefore the container can automate this process. The EJB QL is only for finder and select method. So only Select clause is defined. On more interesting point, in the specs it says the query must be defined for all finder and select methods except findByPrimaryKey. I think in the findByPrimaryKey case, the entity can be determined only on primary key so there is no need for ejb ql. But for other querys you can define various search cretiria so an ejb ql from bean developer is necessary.

[Reid M. Pinchback]

Originally posted by Yi Meng:
So in essence, we have to put every vender-dependent SQL command into the DD as env-entry so that we dun have to modify the code and still achieve portability.

That would help, but it still isn't a rock-solid guarantee that you won't have to change source code. Unfortunately ANSI SQL is a much smaller set of SQL functionality than you get from any one vendor. Most developers are used to the SQL provided by the database they use, and assume that all other databases provide virtually identical features. It is rarely true. The problem comes up when a task can be solved in database brand 'A' with one SQL statement, but in brand 'B' you need more than one statement (with possible Java logic between statements) to achieve the same result. env-entry isn't going to help you in this case. env-entry works best if you are really, really careful to stick to ANSI SQL syntax. Then you just have to tweak env-entry if a given vendor has a slightly different name for the same thing.
As somebody already pointed out, for some operations you'll be better off using a finder to locate the entities you want to operate on. The Head-First book talks about using business methods on the entity bean home for batch operations, but achieving portability can be tricky. Hopefully someday EJB-QL will be augmented to help deal with these issues.