Native SQL Query Examples 

Prerequisites

The example queries assume the following entity and mappings:

@Entity

@Table(name = "TMP_DEP")

@NamedNativeQueries( {

      @NamedNativeQuery(name = "getDepartmentWithId10SQL_class", query = "select * from TMP_DEP D where D.ID = 10", resultClass = Department.class),

      @NamedNativeQuery(name = "getDepartmentWithId10SQL_mapping", query = "select * from TMP_DEP D where D.ID = 10", resultSetMapping = "departmentByClass"),

      @NamedNativeQuery(name = "getDepartmentName", query = "select name as \"HUTZLIPUTZ\" from TMP_DEP D where D.ID = 10", resultSetMapping = "departmentNameOnly"),

      @NamedNativeQuery(name = "getDepartmentFieldByField", query = "select id as \"D_ID\", name as \"D_NAME\", version as \"D_VERSION\" from TMP_DEP D where D.ID = 10", resultSetMapping = "departmentByFields") })

@SqlResultSetMappings( {

      @SqlResultSetMapping(name = "departmentByClass", entities = { @EntityResult(entityClass = Department.class) }),

      @SqlResultSetMapping(name = "departmentNameOnly", columns = { @ColumnResult(name = "HUTZLIPUTZ") }),

      @SqlResultSetMapping(name = "departmentByFields", entities = { @EntityResult(entityClass = Department.class, fields = {

            @FieldResult(name = "id", column = "D_ID"),

            @FieldResult(name = "name", column = "D_NAME"),

            @FieldResult(name = "version", column = "D_VERSION") }) }) })

 

public classDepartment implements Serializable {

   public void setId(int id) {

      this._id = id;

   }

 

   private int _id;

 

   private String _name;

 

   @Id

   public int getId() {

      return _id;

   }

 

   public voidsetName(String aName) {

      _name = aName;

   }

 

   @Basic

   public String getName() {

      return _name;

   }

}

 

Example

Dynamic Native Query Using Class-mapping

public Department getDepartmentViaDynamicNativeQueryNoMapping() {

Query query = em.createNativeQuery("select * from TMP_DEP D where D.ID = 10", Department.class);

   return(Department) query.getSingleResult();

  }

Dynamic Native Query Using Result Set Mapping with Class-mapping

public Department getDepartmentViaDynamicNativeQueryClassMapping() {

   Query query = em.createNativeQuery("select * from TMP_DEP D where D.ID = 10", "departmentByClass");

   return(Department) query.getSingleResult();

   }

Named Native Query with Class-mapping
public Department getDepartmentViaNamedNativeQueryNoMapping() {

   Query query = em.createNamedQuery("getDepartmentWithId10SQL_class");

   return(Department) query.getSingleResult();

   }  

Named Native Query Using Result Set Mapping with Class-mapping

public Department geDepartmenttViaNamedNativeQueryClassMapping() {

   Query query = em.createNamedQuery("getDepartmentWithId10SQL_mapping");

   return(Department) query.getSingleResult();

   }

Named Native Query Using Result Set Mapping with Column-mapping

public String getNameViaNamedNativeQueryColumnMapping() {

   Query query = em.createNamedQuery("getDepartmentName");

   return(String) query.getSingleResult();

   }

Named Native Query Using Result Set Mapping Using Entity-result (defined by field by field)

public Department getDepartmentByFieldsViaNamedNativeQueryColumnMapping() {

   Query query = em.createNamedQuery("getDepartmentFieldByField");

   return(Department) query.getSingleResult();

   }