Это мой SQL-запрос,
результаты приходят правильно с Workbench. "( CAST(AES_DECRYPT(UNHEX(f.FUND_NAME), 'secret-key-54321') AS CHAR))as FUND_NAME " работает нормально.
Но после применения SQL-запроса с codeом Java запрос работает неправильно (( CAST(AES_DECRYPT(UNHEX(f.FUND_NAME), :key) AS CHAR))as FUND_NAME ) не работает.
ОШИБКА в том,
каковы наилучшие возможные варианты?
select sum(case when e.BUY_SELL = 'S' then (e.QUANTITY *-1) else e.QUANTITY end ) ,
e.FUND_ID ,
f.FUND_CODE ,
( CAST(AES_DECRYPT(UNHEX(f.FUND_NAME), 'secret-key-54321') AS CHAR))as FUND_NAME ,
fp.ID ,
fp.PORTFOLIO_CODE ,
fp.PORTFOLIO_NAME ,
f.FUND_CATEGORY
from equity_buy_sell e
inner join fund f on f.ID =e.FUND_ID
inner join fund_portfolio fp on fp.ID = e.FUND_PORTFOLIO_ID
where e.date <='2024-08-28'
and e.STATUS ='CONFIRMED'
and e.STOCK_CODE_ID =3611
group by e.FUND_ID ,e.FUND_PORTFOLIO_ID;
String sqlQuery = "select sum(case when e.BUY_SELL = 'S' then (e.QUANTITY *-1) else e.QUANTITY end ) ,
e.FUND_ID ,f.FUND_CODE ,( CAST(AES_DECRYPT(UNHEX(f.FUND_NAME), :key) AS CHAR))as FUND_NAME ,
fp.ID ,fp.PORTFOLIO_CODE ,fp.PORTFOLIO_NAME ,f.FUND_CATEGORY" +
" from equity_buy_sell e " +
" inner join fund f on f.ID =e.FUND_ID" +
" inner join fund_portfolio fp on fp.ID = e.FUND_PORTFOLIO_ID " +
" where e.date <=:date and e.STATUS =:status and e.STOCK_CODE_ID =:stock " +
" group by e.FUND_ID ,e.FUND_PORTFOLIO_ID";
Query query = session.createSQLQuery(sqlQuery);
query.setParameter("date", tradeDate);
query.setParameter("status", "CONFIRMED");
query.setParameter("stock", stockCodeId);
query.setParameter("key", key);//parameter is comming(secret-key-54321) but it not set to query properly and getting errors.
org.hibernate.MappingException: No Dialect mapping for JDBC type: -1
at org.hibernate.dialect.TypeNames.get(TypeNames.java:56)
at org.hibernate.dialect.TypeNames.get(TypeNames.java:81)
at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:368)
at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:559)
at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery(CustomLoader.java:485)
at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:501)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at com.cal.finnovation.utms.utms.dao.dealing.SplitDao.getAvailableSplitShares(SplitDao.java:248)
at com.cal.finnovation.utms.utms.model.dealing.SplitModel.createAvailableSplitSharesBeanList(SplitModel.java:301)
at com.cal.finnovation.utms.utms.action.corporateaction.SplitReceivableAction.getAvailableSplitsQty(SplitReceivableAction.java:26)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:450)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:289)
Тихон
Вопрос задан4 апреля 2024 г.