Java Persistence Query Language(JPQL) Shell

I am providing the project structure in NetBeans 6.5 and output screen snapshot below.

Required Steps

1. Create a new web application using NetBeans IDE
2. Add Persistence Unit - persistence.xml - to connect to Oracle using HR account. Specify TOPLINK as the persistence provider
3. Make sure Toplink library is added to libraries of the project
4. Make sure Oracle library - ojdbc14.jar is added to libraries of the project
5. Create entities - Employee.java, Job.java
6. Create EntityManagerUtil.java to get an instance of EntityManager
7. Create query.jsp
8. Deploy and run query.jsp

Job.java

import java.io.Serializable;
import java.util.Collection;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "JOBS")
public class Job implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "JOB_ID")
    private String jobId;
    @Basic(optional = false)
    @Column(name = "JOB_TITLE")
    private String jobTitle;
    @Column(name = "MIN_SALARY")
    private Integer minSalary;
    @Column(name = "MAX_SALARY")
    private Integer maxSalary;
        @OneToMany(mappedBy = "job")
    private Collection employees;

    public Job() {
    }

    public Job(String jobId) {
        this.jobId = jobId;
    }

    public Job(String jobId, String jobTitle) {
        this.jobId = jobId;
        this.jobTitle = jobTitle;
    }

    public String getJobId() {
        return jobId;
    }

    public void setJobId(String jobId) {
        this.jobId = jobId;
    }

    public String getJobTitle() {
        return jobTitle;
    }

    public void setJobTitle(String jobTitle) {
        this.jobTitle = jobTitle;
    }

    public Integer getMinSalary() {
        return minSalary;
    }

    public void setMinSalary(Integer minSalary) {
        this.minSalary = minSalary;
    }

    public Integer getMaxSalary() {
        return maxSalary;
    }

    public void setMaxSalary(Integer maxSalary) {
        this.maxSalary = maxSalary;
    }

    public Collection getEmployees() {
        return employees;
    }

    public void setEmployees(Collection employeesCollection) {
        this.employees = employeesCollection;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (jobId != null ? jobId.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in case the id fields are not set
        if (!(object instanceof Job)) {
            return false;
        }
        Job other = (Job) object;
        if ((this.jobId == null && other.jobId != null) || (this.jobId != null && !this.jobId.equals(other.jobId))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return  jobId + ":" + jobTitle + ":" + minSalary + ":" + maxSalary;
    }
}

Employee.java

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table(name = "EMPLOYEES")
public class Employee implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "EMPLOYEE_ID")
    private Integer employeeId;
    @Column(name = "FIRST_NAME")
    private String firstName;
    @Basic(optional = false)
    @Column(name = "LAST_NAME")
    private String lastName;
    @Basic(optional = false)
    @Column(name = "EMAIL")
    private String email;
    @Basic(optional = false)
    @Column(name = "HIRE_DATE")
    @Temporal(TemporalType.DATE)
    private Date hireDate;
    @Column(name = "SALARY")
    private BigDecimal salary;

    @JoinColumn(name = "JOB_ID", referencedColumnName = "JOB_ID")
    @ManyToOne(optional = false)
    private Job job;

    public Employee() {
    }

    public Employee(Integer employeeId) {
        this.employeeId = employeeId;
    }

    public Employee(Integer employeeId, String lastName, String email, Date hireDate) {
        this.employeeId = employeeId;
        this.lastName = lastName;
        this.email = email;
        this.hireDate = hireDate;
    }

    public Integer getEmployeeId() {
        return employeeId;
    }

    public void setEmployeeId(Integer employeeId) {
        this.employeeId = employeeId;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getHireDate() {
        return hireDate;
    }

    public void setHireDate(Date hireDate) {
        this.hireDate = hireDate;
    }

    public BigDecimal getSalary() {
        return salary;
    }

    public void setSalary(BigDecimal salary) {
        this.salary = salary;
    }

    public Job getJobId() {
        return job;
    }

    public void setJobId(Job jobId) {
        this.job = jobId;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (employeeId != null ? employeeId.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in case the id fields are not set
        if (!(object instanceof Employee)) {
            return false;
        }
        Employee other = (Employee) object;
        if ((this.employeeId == null && other.employeeId != null) || (this.employeeId != null && !this.employeeId.equals(other.employeeId))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return  employeeId + ":" + firstName + ":" + lastName + ":" + email + ":" + salary;
    }
}

EntityManagerUtil.java

package utils;



import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

public class EntityManagerUtil {
    private static EntityManager em=null;

    public static EntityManager getInstance() {
      if ( em == null) {
        EntityManagerFactory emf;
        emf = Persistence.createEntityManagerFactory("PU");
        em = emf.createEntityManager();
        return em;
      }
      else
        return em;
    }
}

Persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
  <persistence-unit name="PU" transaction-type="RESOURCE_LOCAL">
    <provider>oracle.toplink.essentials.PersistenceProvider</provider>
    <class>Employee</class>
    <class>Job</class>
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
      <property name="toplink.jdbc.user" value="hr"/>
      <property name="toplink.jdbc.password" value="hr"/>
      <property name="toplink.jdbc.url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
      <property name="toplink.jdbc.driver" value="oracle.jdbc.OracleDriver"/>
    </properties>
  </persistence-unit>
</persistence>

query.jsp

<%@page contentType="text/html" pageEncoding="UTF-8" import="javax.persistence.*,java.util.*"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JPQL Shell</title>
    </head>
    <body>
        <h1>JPQL Shell</h1>
        <form action="query.jsp" method="post">
            <b>Enter Query </b> <br/>
            <textarea name="query" rows="5"  cols="50">${param.query}</textarea>
            <br/>
            <input type="submit" value="Execute Query"/>
        </form>

        <p/>
        <%

          String query = request.getParameter("query");
          if ( query == null || query.length() ==  0)
              return;

          EntityManager em = utils.EntityManagerUtil.getInstance();
          List lst = null;
          try {
            Query  qry = em.createQuery(query);
            lst = qry.getResultList();
          }
          catch(Exception ex) {
              out.println("<p/>Error -> " + ex.getMessage());
              return;
          }

          out.println("<h2>Query Result </h2><table border='1'");
          for (Object obj : lst) {
              out.println("<tr>");
              if (obj instanceof Object[]) {
                  for( Object o : (Object[]) obj)
                       out.println( "<td>"  + o  + "</td>");
              }
              else {
                out.println("<td>" + obj.toString() + "</td>");
              }
  
              out.println("</tr>");
          }

        %>

    </body>
</html>