Chapter 11 nativequeries example modification for MySQL users

[Himai Minh]

Hi, for all MySQL users,
here is the modification I need to make in Employee.java class:

@Entity
@Table(name="EMP")
@NamedNativeQuery(
    name="orgStructureReportingTo",
   //  Oracle-specific SQL string
  //   query="SELECT emp_id, name, salary, manager_id, dept_id, address_id " +
      //    "FROM emp " +
      //  "START WITH manager_id = ? " +
       //  "CONNECT BY PRIOR emp_id = manager_id",
    //MySQL specific SQL string
       query="SELECT emp_id, name, salary, manager_id, dept_id, address_id " +
                 "FROM emp " +
               "WHERE  manager_id = ? ",
    //JPQL            
  // query = "SELECT emp1.emp_id, emp1.name, emp1.salary, emp1.manager_id, " +
   //        "emp1.dept_id, emp1.address_id " +
   //        "FROM EMP emp1, EMP emp2 " +
   //         "WHERE ((emp2.EMP_ID = ?) AND (emp2.EMP_ID = emp1.MANAGER_ID))",
    resultClass=Employee.class
)
public class Employee {
    @Id
    @Column(name="EMP_ID")
    private int id;
  ....

Since MySQL cannot interpret the Oracle specific SQL, I need to use a simple MySQL SQL.
If we use the Oracle SQL for MySQL, the createNamedQuery method will not work and it outputs an error :

Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'manager_id = 1 CONNECT BY PRIOR emp_id = manager_id' at line 1
Error Code: 1064
Call: SELECT emp_id, name, salary FROM emp START WITH manager_id = ? CONNECT BY PRIOR emp_id = manager_id
bind => [1 parameter bound]
Query: ReadAllQuery(referenceClass=Employee sql="SELECT emp_id, name, salary FROM emp START WITH manager_id = ? CONNECT BY PRIOR emp_id = manager_id")
at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378)...

[K. Tsang]

There's a "with" clause in standard sql however MySQL somewhat doesn't fully support the "with recursive"

http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server

http://mysqlserverteam.com/with-recursive-and-mysql/