caused by:sql.SQLSyntaxErrorException:user lacks privilege or object not found:FROM_UNIXTIME

Question

SELECT p.patient_id as patientId,p.user_id as userId,u.first_name as first_name

I want this query to run in my junit test case.I know hsql does not support this and i need a solution for this.pls help

Answer 1

This is a MySQL query and therefore if you want to run it as is, it will have to be run as a native query. You can use code similar to the following:

String query = "SELECT p.patient_id as patientId ...";
Query q = em.createNativeQuery(query);
List<Object[]> rows = q.getResultList();
for (Object[] r: rows) {
    // process each row as your unit test requires
}

If you need to use a parameterized native query, then you will have to use positional parameters. Refer to the following Stack Overflow question for an example:

How to create a native query with named parameters?