0

I have a SQL query which need to be executed but due to Hibernate I see some issues while execution.

My Query is set in Property key - sampleQuery as below

SELECT * FROM (SELECT lcl.line_nr line_nr, lcl.LINE_NR_SHRT_DESC_TXT LINE_NR_SHRT_DESC_TXT, mf.FSC_CD FSC_CD, mf.LCL_PRFL_CD LCL_PRFL_CD, NVL(mfi.SML_img_mrktg_fsc_path_txt, NVL (mfi.SML_IMG_MRKTG_CNCPT_PATH_TXT, '')) AS small, NVL (mfi.lrg_img_mrktg_fsc_path_txt, NVL(mfi.LRG_IMG_MRKTG_CNCPT_PATH_TXT, '')) AS large, NVL (mfi.XL_img_mrktg_fsc_path_txt, NVL (mfi.XL_IMG_MRKTG_CNCPT_PATH_TXT, '')) AS xl FROM lcl_line_nr_lang lcl, mrkt_fsc mf, mrkt_fsc_img mfi WHERE lcl.mrkt_id = :mrkt_id AND lcl.lang_cd =:lang_cd AND lcl.cmpgn_nr =:cmpgn_nr AND lcl.cmpgn_yr_nr =:cmpgn_yr_nr AND lcl.mrkt_id = mf.mrkt_id AND lcl.item_fsc_nr = mf.item_fsc_nr AND mf.mrkt_id = mfi.mrkt_id(+)AND mf.mrkt_fsc_id = mfi.mrkt_fsc_id(+)) a WHERE (a.large = '' OR a.large IS NULL OR a.xl = '' OR a.xl IS NULL OR a.small = '' OR a.small IS NULL)

DAOImpl Class - I am trying to execute it like this -

            try
            {
            String queryString =appQueryLoad.getProperty("sampleQuery");
            Query fetchQuery=sessionODS.createSQLQuery(queryString);
            logger.info("Query to fetch ctgry details :"+queryString);

            fetchQuery.setParameter("mrkt_id",imt.getMrktId());
            fetchQuery.setParameter("cmpgn_nr","1");
            fetchQuery.setParameter("cmpgn_yr_nr","2014");
            fetchQuery.setParameter("lang_cd","uk_UK");

            fetchQuery.setCacheable(true);
            logger.debug(fetchQuery.list());
            sessionODS.getTransaction().commit();
            }
            catch(Exception e){
            logger.error("Error in retrieving the Shades details",e);
            sessionODS.getTransaction().rollback();
            }

The problem now is in the execute Statement where logger.debug(fetchQuery.list()); is throwing an exception as below when I debug.

Hibernate:SELECT * FROM (SELECT lcl.line_nr line_nr, lcl.LINE_NR_SHRT_DESC_TXT LINE_NR_SHRT_DESC_TXT, mf.FSC_CD FSC_CD, mf.LCL_PRFL_CD LCL_PRFL_CD, NVL(mfi.SML_img_mrktg_fsc_path_txt, NVL (mfi.SML_IMG_MRKTG_CNCPT_PATH_TXT, '')) AS small, NVL (mfi.lrg_img_mrktg_fsc_path_txt, NVL(mfi.LRG_IMG_MRKTG_CNCPT_PATH_TXT, '')) AS large, NVL (mfi.XL_img_mrktg_fsc_path_txt, NVL (mfi.XL_IMG_MRKTG_CNCPT_PATH_TXT, '')) AS xl FROM lcl_line_nr_lang lcl, mrkt_fsc mf, mrkt_fsc_img mfi WHERE lcl.mrkt_id = :mrkt_id AND lcl.lang_cd =:lang_cd AND lcl.cmpgn_nr =:cmpgn_nr AND lcl.cmpgn_yr_nr =:cmpgn_yr_nr AND lcl.mrkt_id = mf.mrkt_id AND lcl.item_fsc_nr = mf.item_fsc_nr AND mf.mrkt_id = mfi.mrkt_id(+)AND mf.mrkt_fsc_id = mfi.mrkt_fsc_id(+)) a WHERE (a.large = '' OR a.large IS NULL OR a.xl = '' OR a.xl IS NULL OR a.small = '' OR a.small IS NULL)

I tried to run this on my Toad and I got 1 row as result for the passed parameters but in Java I see issue while executing. Can you please help me with executing this query which has lot of Joins.

Do let me know if more details needed.

Technical Info: I use Struts-2 framework with Hibernate.

Improve this question
2
  • @Roman - Hi. This is where I have shared the query and parameters with the issue. It is kinda related as the question is based on same concept. I will remember this going forward. Sorry. Commented Jun 10, 2014 at 18:05
  • Roman, Clockwork-mus, Elliot - I still don't have the solution yet. I thought in this question I clearly mentioned the requirement along with my current code. Let me clear that this is not a deliberate action from me to make a duplicate entry. Commented Jun 15, 2014 at 4:26

1 Answer 1

Reset to default
0 
final Session session = sessionFactory.openSession();
String str =  " SELECT * FROM (SELECT lcl.line_nr line_nr, lcl.LINE_NR_SHRT_DESC_TXT LINE_NR_SHRT_DESC_TXT, mf.FSC_CD FSC_CD, mf.LCL_PRFL_CD LCL_PRFL_CD, NVL(mfi.SML_img_mrktg_fsc_path_txt, NVL (mfi.SML_IMG_MRKTG_CNCPT_PATH_TXT, '')) AS small, NVL (mfi.lrg_img_mrktg_fsc_path_txt, NVL(mfi.LRG_IMG_MRKTG_CNCPT_PATH_TXT, '')) AS large, NVL (mfi.XL_img_mrktg_fsc_path_txt, NVL (mfi.XL_IMG_MRKTG_CNCPT_PATH_TXT, '')) AS xl FROM lcl_line_nr_lang lcl, mrkt_fsc mf, mrkt_fsc_img mfi WHERE lcl.mrkt_id = mrkt_id AND lcl.lang_cd = lang_cd AND lcl.cmpgn_nr = cmpgn_nr AND lcl.cmpgn_yr_nr = cmpgn_yr_nr AND lcl.mrkt_id = mf.mrkt_id AND lcl.item_fsc_nr = mf.item_fsc_nr AND mf.mrkt_id = mfi.mrkt_id(+)AND mf.mrkt_fsc_id = mfi.mrkt_fsc_id(+)) a WHERE (a.large = '' OR a.large IS NULL OR a.xl = '' OR a.xl IS NULL OR a.small = '' OR a.small IS NULL)" ;
SQLQuery q = session.createSQLQuery(str);
List<Object[]> entities = q.list();
for (Object[] entity : entities) {
    for (Object entityCol : entity) {
        System.out.print(" " + entityCol);
    }
    System.out.println("");
}

try this out. i hope it will help full to you

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

How to pass those 4 parameters? Can you define that too please?
simple way by adding them into our existing string for example a.small='"+value+"' where value that come in your method
public List<Objecy[]> getList(String a , String b) { final Session session = sessionFactory.openSession(); String str = "select * from table where a='"+a+"' and b='"+b+"'" ; SQLQuery q = session.createSQLQuery(str); List<Object[]> entities = q.list(); for (Object[] entity : entities) { for (Object entityCol : entity) { System.out.print(" " + entityCol); } System.out.println(""); } }
above code will help you how to pass parameter to query
You answer seem little confusing to me. As I not able to understand why you have not mentioned anything about parameter-assigning in your Answer post. the above comment looks clear but it is not working. :( If you could, can you give me a snippet for the same Query which I have posted.. where the parameters are - mrkt_id, cmpgn_nr, cmpgn_yr_nr, lang_cd
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.