Java native query - com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

Question

I get following error: "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Card.findPrefix'"

for:

    @NamedNativeQueries({
        @NamedNativeQuery(name = "Card.findPrefix",
                query = "SELECT DISTINCT(FLOOR(c

    .number/10000)) FROM Card c")
    })

    public List<Integer> findPrefix(){
            Query q = em.createNativeQuery("Card.findPrefix");
            try{
                return q.getResultList();
            }catch(Exception ex){
                ex.printStackTrace();
                return null;
            }
        }

I can not understand where is my mistake, because when I type this query directly to Mysql it works perfectly.

Answer 1

Use should use createNamedQuery, instead of createNativeQuery.

From EntityManager JavaDoc

createNativeQuery(String sqlString)

Create an instance of Query for executing a native SQL statement, e.g., for update or delete.

createNamedQuery(String name)

Create an instance of Query for executing a named query (in the Java Persistence query language or in native SQL).

So in your code you are executing query name 'Find....' as SQL query.

The right one:

Query q = em.createNamedQuery("Card.findPrefix");