Custom SQL query in Liferay 7 ga4

[Karel Bělunek，修改在6 年前。]

Hello there,

I have problem with custom queries in ga4.
I have service builder generated service and it works flawless in ga3.

Now in ga4 it starts complaining with exception:

08:55:18,446 ERROR [http-nio-8080-exec-6][JDBCExceptionReporter:234] Data truncation: Truncated incorrect DOUBLE value: 'A'
com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.DataException: could not execute native bulk manipulation query

My custom query is only testing one, it simply gets row from table "employee" identified by primary key. It is the same functionality like standard generated method "getEmployee(long empid)" generated by service builder.
The generated method works, custom query stops working in ga4.
The custom query is; "select * from employee where eid=?"

The exception message states that value "A" cannot be cast to Double. It looks that ga4 for some reason uses different ORM mapping that standard methods, the "A" is value in one of columns in returned row.

Pleasy any ideas?
Am I doing something wrong or it is some kind of serious error in ga4?

Thanks for any info
Regards

[Tomas Polesovsky，修改在6 年前。]

Hi Karel,

can you please post whole stacktrace?

From the exception you are going through Hibernate, but using SQL select, not HQL. Is it a native query? Or are you using Liferay custom query, Hibernate criteria API or just custom sql string with hibernate session?

Maybe the columns are returned in different order than you expect. Can you try to explicitly list the column names in the query?

Thanks.

[Karel Bělunek，修改在6 年前。]

Oops!

I have just fid the problem, it was caused by my other native update command that uses Oracle specific string concat operator '||' that does not work in MySQL.

After fixing the command to use concat() function, everything works well.

I am just beginner in Liferay, could you please give me some advice, what approach is the best for accessing external DB data?
I do not like HQL and I prefer native SQL queries but I want to use Hibernate ORM mapping. The liferay recommended solution is to generate service using service builder and then create custom SQL queries alongside with <Entity>Finder clas and run service builder again to generate interfaces and so on.
But this approach generates also lot of unused java method like findAll().

Is it there better approach to create Liferay service that allow me to directly call native SQL queries with ability of automatic ORM mapping?

I am working on small intranet portal that reads data from external database and displays some perfomance graphs using Primefaces charts.

Thanks and regards

Karel B.