execute a query using entity manager

Question

I have a query to get the size of databases I have. I have written the following:

@SuppressWarnings("unchecked")
List<Long> results = (List<Long>) em.createQuery("SELECT sum( data_length + index_length ) / 1024 / 1024 \"DataBaseSizeinMB\" FROM information_schema.TABLES GROUP BY table_schema ").getResultList();

where em is the EntityManager. The query on its own runs successfully in phpadmin. But when I run it in my java code, I get exception.

EDIT: The answer is this:

List<Object> list = (List<Object>) em.createNativeQuery(
         "SELECT sum( data_length + index_length ) / 1024 / 1024 FROM information_schema.TABLES GROUP BY table_schema "
).getResultList();


for (Object record : list) {
    double sum = (double) Double.parseDouble(record.toString());
    System.out.println(sum);
}

add `StackTrace` Please .. :) – Vikrant Kashyap Mar 16 '16 at 13:24 — Vikrant Kashyap, Mar 16 '16 at 13:24

score 3 · Answer 1 · answered Mar 16 '16 at 13:32

It works on phpmyadmin because it's a native query. Try using em.createNativeQuery(). That method should be used for plain SQL queries. em.createQuery() accept queries written in JPQL (Java Persistence Query Language).

Docs of createNativeQuery(String sqlString):

@param sqlString a native SQL query string

Docs of createQuery(String qlString):

@param qlString a Java Persistence query string

score 0 · Answer 2 · answered Mar 16 '16 at 13:24

0

It probably the \"DataBaseSizeinMB\"

Try to remove this.

Additionally you should always post the Stack Trace here

answered Mar 16 '16 at 13:24

Simon Martinelli

34,053
5
48
82

execute a query using entity manager

2 Answers2