How to translate into EJB QL?

Posted on 2009-04-22
Last Modified: 2013-11-24
i've got problem translating following query to be eligible to use within entity manager. Basically it should take details given in language from second table in one query. If no enty, then columns are null. This works fine in SQL but have no idea how to make it work in EJB QL...

Reg. db schema, in category table category_id is PK, category_lang_detail table got category_id + language_code as PK. Relation one to many.

SELECT c.category_id, c.category_name, c.sort_order,

                    cld.category_name AS lang_name, cld.sort_order AS lang_sort_order

                    FROM category c 

                    LEFT OUTER JOIN category_lang_detail cld  

                    ON c.category_id = cld.category_id AND 

                    cld.language_code = 'EN'

                    WHERE c.category_id IN(1,2,3)

                   AND parent_category = 100 AND 

                    (cld.visible = true OR cld.visible IS NULL) 

                    ORDER BY cld.sort_order, c.sort_order ASC

Open in new window

Question by:szczecin
    1 Comment

    Accepted Solution

    I would try the following:

    select c from Category c left join c.langDetail cld
    where cld.languageCode = 'EN'
    and = 100
    and (cld.visible = true or cld.visible is null)
    order by cld.sortOrder, c.sortOrder ASC

    I haven't done a ton of left joins in JPA/Hibernate, but I've done a few over the years.  Optionally, replace the parent category reference with...

    and c.parentCategory = :pCat

    then pass in the parent category object with "setParameter" on the query object.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    fizzArray  challenge 1 28
    scoresClump  challenge 31 85
    Unable to open debugger port in Intellij idea 6 36
    endX challenge 2 30
    Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
    Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
    The viewer will learn how to implement Singleton Design Pattern in Java.
    This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now