Java Tutorial/JPA/Native Query

Execute Native Query

File: Employee.java

   <source lang="java">

import java.util.ArrayList; import java.util.Collection; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.NamedNativeQuery; import javax.persistence.OneToMany; import javax.persistence.OneToOne; import javax.persistence.Table; @Entity @Table(name="EMP") @NamedNativeQuery(

   name="myQuery",
   query = "SELECT emp1.emp_id, emp1.name, 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;
   private String name;
   
   @OneToOne
   private Address address;
   
   @ManyToOne
   @JoinColumn(name="DEPT_ID")
   private Department department;
   
   @ManyToOne
   @JoinColumn(name="MANAGER_ID")
   private Employee manager;
   
   @OneToMany(mappedBy="manager")
   private Collection<Employee> directs = new ArrayList<Employee>();
   public int getId() {
       return id;
   }
   
   public void setId(int id) {
       this.id = id;
   }
   
   public String getName() {
       return name;
   }
   
   public void setName(String name) {
       this.name = name;
   }
   
   public Address getAddress() {
       return address;
   }
   
   public void setAddress(Address address) {
       this.address = address; 
   }
   
   public Department getDepartment() {
       return department;
   }
   
   public void setDepartment(Department department) {
       this.department = department;
   }
   
   public Collection<Employee> getDirects() {
       return directs;
   }
   
   public Employee getManager() {
       return manager;
   }
   
   public void setManager(Employee manager) {
       this.manager = manager;
   }

}

@Entity

class Department {
   @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
   private int id;
   private String name;
   public int getId() {
       return id;
   }
   
   public void setId(int id) {
       this.id = id;
   }
   
   public String getName() {
       return name;
   }
   
   public void setName(String deptName) {
       this.name = deptName;
   }
   public String toString() {
       return "Department id: " + getId() + 
              ", name: " + getName();
   }

}

@Entity class Address {

   @Id
   private int id;
   private String street;
   private String city;
   private String state;
   private String zip;
   
   public int getId() {
       return id;
   }
   
   public void setId(int id) {
       this.id = id;
   }
   
   public String getStreet() {
       return street;
   }
   
   public void setStreet(String address) {
       this.street = address;
   }
   public String getCity() {
       return city;
   }
   public void setCity(String city) {
       this.city = city;
   }
   public String getState() {
       return state;
   }
   public void setState(String state) {
       this.state = state;
   }
   public String getZip() {
       return zip;
   }
   public void setZip(String zip) {
       this.zip = zip;
   }
   public String toString() {
       return "Address id: " + getId() + 
              ", street: " + getStreet() +
              ", city: " + getCity() +
              ", state: " + getState() +
              ", zip: " + getZip();
   }

}</source>

Named Native Query

File: Employee.java

   <source lang="java">

import java.util.ArrayList; import java.util.Collection; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.NamedNativeQuery; import javax.persistence.OneToMany; import javax.persistence.OneToOne; import javax.persistence.Table; @Entity @Table(name="EMP") @NamedNativeQuery(

   name="myQuery",
   query = "SELECT emp1.emp_id, emp1.name, 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;
   private String name;
   
   @OneToOne
   private Address address;
   
   @ManyToOne
   @JoinColumn(name="DEPT_ID")
   private Department department;
   
   @ManyToOne
   @JoinColumn(name="MANAGER_ID")
   private Employee manager;
   
   @OneToMany(mappedBy="manager")
   private Collection<Employee> directs = new ArrayList<Employee>();
   public int getId() {
       return id;
   }
   
   public void setId(int id) {
       this.id = id;
   }
   
   public String getName() {
       return name;
   }
   
   public void setName(String name) {
       this.name = name;
   }
   
   public Address getAddress() {
       return address;
   }
   
   public void setAddress(Address address) {
       this.address = address; 
   }
   
   public Department getDepartment() {
       return department;
   }
   
   public void setDepartment(Department department) {
       this.department = department;
   }
   
   public Collection<Employee> getDirects() {
       return directs;
   }
   
   public Employee getManager() {
       return manager;
   }
   
   public void setManager(Employee manager) {
       this.manager = manager;
   }

}

@Entity

class Department {
   @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
   private int id;
   private String name;
   public int getId() {
       return id;
   }
   
   public void setId(int id) {
       this.id = id;
   }
   
   public String getName() {
       return name;
   }
   
   public void setName(String deptName) {
       this.name = deptName;
   }
   public String toString() {
       return "Department id: " + getId() + 
              ", name: " + getName();
   }

}

@Entity class Address {

   @Id
   private int id;
   private String street;
   private String city;
   private String state;
   private String zip;
   
   public int getId() {
       return id;
   }
   
   public void setId(int id) {
       this.id = id;
   }
   
   public String getStreet() {
       return street;
   }
   
   public void setStreet(String address) {
       this.street = address;
   }
   public String getCity() {
       return city;
   }
   public void setCity(String city) {
       this.city = city;
   }
   public String getState() {
       return state;
   }
   public void setState(String state) {
       this.state = state;
   }
   public String getZip() {
       return zip;
   }
   public void setZip(String zip) {
       this.zip = zip;
   }
   public String toString() {
       return "Address id: " + getId() + 
              ", street: " + getStreet() +
              ", city: " + getCity() +
              ", state: " + getState() +
              ", zip: " + getZip();
   }

}</source>

Native Insert Statement With Parameter

File: Employee.java

   <source lang="java">

import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Employee {

   @Id
   @Column(name="EMP_ID")
   private int id;
   private String name;
   public int getId() {
       return id;
   }
   
   public void setId(int id) {
       this.id = id;
   }
   
   public String getName() {
       return name;
   }
   
   public void setName(String name) {
       this.name = name;
   }
  

}</source>