I need help in writing queries in EJB 3.0. I am used to normal select statements in plain java. But I hear that in EJB 3.0, the syntax of writing a custom query is different.
Please help.
In EJB 3.0, you can write custom queries. These are called "Native" Queries. The syntax of native queries are almost the same.
There is a special query type called EJB-QL or "Object Query Language". Instead of selecting columns and rows, you select an object or a set of objects of the same type.
An example is given below:
SELECT OBJECT(P) FROM Products P
Here the Products reference to an entity bean in your EJB project. You then type cast the returned list to the Products class and extract the properties for column values.
The importance of EJB-QL is such that you are selecting object rows and not data rows. This query technology moves the traditional SQL one step further.
The underlying architecture is such that, the programmer writes "program centric" queries. i. e. the programmer only need to know the properties of objects and use them in the query. The programmer does not write database specific SQL query syntax such as special functions that are given by different database vendors.
The middleware that you use for EJB takes care of translating the queries to native SQL statements in a way the underlying database understands.
TekGirl is correct. For example the following Object Query:
SELECT OBJECT(P) FROM Products P
Translates to the following native SQL query
SELECT * FROM Products P
As you can see, a plain SQL statement is executed, and the middleware is responsible of filling objects of "Products" and returning it to the caller.
From your information I gather that In reality, EJB QL is another abstraction over the plain SQL statements. Interesting, how  about joins? How is that handled in EJB QL?Â
Thank you in advance for helping me understand the same!
Assuming you have a products table and a product type table. If you want to find the products that are of type "Imported", the EJB QL is given below:
SELECT OBJECT(P) FROM Products P WHERE P.productType.typeName='Imported'
Here you can see a major difference from the standard SQL syntax. You do normal "traverse" by using the "getter" that are defined in your entity bean.
The entity bean Product has a ManyToOne relationship with the ProductType bean. Therefore you just access the ProductType via the exposed property of the Product bean.
As you can see EJB QL makes it simple and more object oriented.
The corresponding SQL statement for the object query is given below:
SELECT P.* from Products P, ProductType t
WHERE P.productType = t.id
AND t.typeName='Imported'
The underlying middleware will automatically create the native query that is required by the database.
Thank you TekGirl and Dimaz for your wonderful explanation on EJB QL and how it functions on joins!
Forgive me for pushing but, how about selecting arbitrary fields only instead of all values ?
Is that supported by EJB QL ?
Yes. EJB QL fully supports selecting partial columns. The drawback here is that in this case it will return a list of "Object" type.
You will need to typecast it to an array of Objects and again re-typecast each value in the array to an array of objects.
Then each object needs to be type cast to the required data type.
Wonderful! Thank you Dimaz for that input. Now I understand how to write EJB QL and how easy it is!
Thank you again!
