Running SQL query instead of EJB QL

[mike82]

I can't translate following query into EJB QL:

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

So I decided to run it as common SQL. What is the best way to do it in Seam? I tried:

@Resource(mappedName="PostgresDataSource")
javax.sql.DataSource ds;

but ds is always null.

[jamesjmp]

Hi!
I advise you to use a native query. For example:

String sql ="...your SQL query...";
Query sqlQuery = entityManager.createNativeQuery(sql,Data.class);

List<Data> dataList = sqlQuery.getResultList();

Hope it helps you!

[mike82]

this is what I was looking for! thanks a lot. I just changed it so I don't specify class of the results and take it from Object[] when iterating throught the results list.

[jamesjmp]

That´s ok as well, I forgot to say you that it is not compulsory to specify a data class. So this

Query sqlQuery = entityManager.createNativeQuery(sql);

works fine.