在Hibernate中执行原生SQL查询的问题 [英] Issue with executing native SQL query in Hibernate
本文介绍了在Hibernate中执行原生SQL查询的问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个需要执行的SQL查询，但是由于Hibernate，我在执行时看到一些问题。
我的查询在属性键 - sampleQuery
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小，NVL（mfi.lrg_img_mrktg_fsc_path_txt，NVL（mfi.LRG_IMG_MRKTG_CNCPT_PATH_TXT， ''））AS大，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。大的IS NULL或a.xl =''OR a.xl IS NULL OR a.small =''OR a.small IS NULL）
DAOImpl类 - 我试图像这样执行 -
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（在检索阴影细节时出错，e）;
sessionODS.getTransaction（）。rollback（）;
}
现在的问题出现在执行语句中，其中 logger.debug（ fetchQuery.list（））;
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小，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 =''或a.larg e IS NULL OR a.xl =''OR a.xl IS NULL OR a.small =''OR a.small IS NULL）
我试着在我的Toad上运行这个，我得到了1行作为结果，但是在Java中，我看到了执行时的问题。
你可以帮我执行这个查询，它有很多连接。
如果需要更多细节，请告诉我。
技术信息：我在Hibernate中使用Struts-2框架。
解决方案
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小，NVL（mfi.lrg_img_mrktg_fsc_path_txt，NVL（mfi.LRG_IMG_MRKTG_CNCPT_PATH_TXT， ''））AS大，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 =''或a.large IS NULL或a.xl =''或a.xl IS NULL或a.small =''或a.small一片空白） ;
SQLQuery q = session.createSQLQuery（str）;
List< Object []> entities = q.list（）; （Object [] entity：entities）
{
for（object entityCol：entity）{
System.out.print（+ entityCol）;
}
System.out.println（）;
}
试试这个。我希望这会对你有帮助
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.
解决方案
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
这篇关于在Hibernate中执行原生SQL查询的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
