Need to write a EJB QL

Asked By Sophie 160 points N/A Posted on - 05/13/2011

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.

• Status: Open
• Question Views: 1691
• Answer Count: 9
• Vote Up 16 Vote Down

• Answer Accepted: Yes
• Question Category: Java

Best Answer by TekGirl

Go To Solution

Answered By DimaZ 0 points N/A #92959

Need to write a EJB QL

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.

About DimaZ

Questions
0

Answers
28

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Java

Answered By TekGirl 5 points N/A #92960

Need to write a EJB QL

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.

About TekGirl

Questions
0

Answers
23

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Java

Answered By DimaZ 0 points N/A #92961

Need to write a EJB QL

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.

About DimaZ

Questions
0

Answers
28

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Java

Answered By Sophie 160 points N/A #92963

Need to write a EJB QL

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!

About Sophie

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Java

Best Answer

Best Answer

Answered By TekGirl 5 points N/A #92965

Need to write a EJB QL

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.

About TekGirl

Questions
0

Answers
23

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Java

Answered By DimaZ 0 points N/A #92966

Need to write a EJB QL

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.

About DimaZ

Questions
0

Answers
28

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Java

Answered By Sophie 160 points N/A #92968

Need to write a EJB QL

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 ?

About Sophie

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Java

Answered By DimaZ 0 points N/A #92969

Need to write a 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.

About DimaZ

Questions
0

Answers
28

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Java

Answered By Sophie 160 points N/A #92970

Need to write a EJB QL

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

Thank you again!

About Sophie

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Java