Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Karel Bělunek
Custom SQL query in Liferay 7 ga4
July 18, 2017 2:20 AM
Answer

Karel Bělunek

Rank: New Member

Posts: 8

Join Date: June 23, 2017

Recent Posts

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
RE: Custom SQL query in Liferay 7 ga4
July 18, 2017 3:20 AM
Answer

Tomas Polesovsky

LIFERAY STAFF

Rank: Liferay Master

Posts: 653

Join Date: February 13, 2009

Recent Posts

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
RE: Custom SQL query in Liferay 7 ga4
July 18, 2017 6:52 AM
Answer

Karel Bělunek

Rank: New Member

Posts: 8

Join Date: June 23, 2017

Recent Posts

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.

Participate in the State of Liferay Community 2017. Help the community and even win some prizes!