pgonzaleznetwork pgonzaleznetwork - 4 years ago 117
SQL Question

@NamedQuery changes table/column name to upper case causing exepction

I have a table in MySql called Course and I have the following in my Course.java class

package pckt.book.ch7.jpa;

import java.io.Serializable;
import javax.persistence.*;


/**
* The persistent class for the Course database table.
*
*/
@Entity
@NamedQuery(name="Course.findAll", query="SELECT c FROM Course c")
public class Course implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private int id;

private int credits;

private String name;

@Column(name="Teacher_id")
private int teacher_id;

public Course() {
}

public int getId() {
return this.id;
}

public void setId(int id) {
this.id = id;
}

public int getCredits() {
return this.credits;
}

public void setCredits(int credits) {
this.credits = credits;
}

public String getName() {
return this.name;
}

public void setName(String name) {
this.name = name;
}

public int getTeacher_id() {
return this.teacher_id;
}

public void setTeacher_id(int teacher_id) {
this.teacher_id = teacher_id;
}

}


I'm the running the query as follows in another class

@PersistenceContext
EntityManager entityManager;

public List<Course> getCourseEntities(){
//Use named query created in Course entitiy
//using @NamedQuery annotation
TypedQuery<Course> courseQuery = entityManager.createNamedQuery("Course.findAll",Course.class);
return courseQuery.getResultList();
}


and I get the following error:

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'course_management.COURSE' doesn't exist
Error Code: 1146
Call: SELECT ID, CREDITS, NAME, Teacher_id FROM COURSE
Query: ReadAllQuery(name="Course.findAll" referenceClass=Course sql="SELECT ID, CREDITS, NAME, Teacher_id FROM COURSE")


From reading the error, it sounds the issue is that the query generated by @NamedQuery is in upper case, therefore the COURSE table is not found.

In my.cnf I have lower_case_table_names=2 but not sure if that should make a difference.

My persistance.xml is as follows

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="CourseManagementEJB">
<jta-data-source>jdbc/CourseManagement</jta-data-source>
<class>pckt.book.ch7.jpa.Course</class>
</persistence-unit>
</persistence>


jdbc/CourseManagement is a JNDI resource in GlassFish that points to a connection pool for the course_management database, where Course is a table. I know I can connect to the database successfully because I'm able to ping it.

Questions:


  1. Is it expected that queries are converted to upper case?

  2. How can I fix this issue?



I did do some research on this but wasn't able to find anything that fits my scenario. Some solutions recommended changing the table names (which I can't do) or changing the DAO classes names, which is not my case either.

Answer Source

Use backticks in the @Table annotation to make JPA use case sensitive names:

@Entity
@Table(name="`Course`")
@NamedQuery(name="Course.findAll", query="SELECT c FROM Course c")
public class Course implements Serializable { ...

Obviously, you have to use the real name as used in your database. E.g. if the name is all lower case, use:

@Table(name="`course`")
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download