Need To Write A Ejb Ql

Asked By 160 points N/A Posted on -

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.

Best Answer by TekGirl
Answered By 0 points N/A #92959

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:


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.

Answered By 0 points N/A #92960

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.

Answered By 0 points N/A #92961

TekGirl is correct. For example the following Object Query:


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.

Answered By 160 points N/A #92963

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!

Best Answer
Best Answer
Answered By 0 points N/A #92965

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.

Answered By 0 points N/A #92966

The corresponding SQL statement for the object query is given below:

SELECT P.* from Products P, ProductType t

WHERE P.productType =

AND t.typeName='Imported'

The underlying middleware will automatically create the native query that is required by the database.

Answered By 160 points N/A #92968

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 ?

Answered By 0 points N/A #92969

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.

Answered By 160 points N/A #92970

Wonderful! Thank you Dimaz for that input. Now I understand how to write EJB QL and how easy it is!

Thank you again!

Login/Register to Answer

Related Questions