This tutorial demonstrate using JPA2.0 with JQPL query with untype result and hibernate as vendor provider. This kind of query specifically useful in reporting or join table.
Required libraries:
- Spring libraries
- h2database
- hibernate-entitymanager
- hibernate-validator
- hibernate-jpa-2.0-api
Step 1: schema.sql
CREATE TABLE PRODUCT (
ID INT NOT NULL AUTO_INCREMENT
, PRODUCTNAME VARCHAR(60) NOT NULL
, PRICE DOUBLE NOT NULL
, PRIMARY KEY (ID)
, CHECK ( PRICE <= 100)
);
CREATE TABLE PRODUCT_TAG (
ID INT NOT NULL AUTO_INCREMENT
, PRODUCT_ID INT NOT NULL
, TAG VARCHAR(60) NOT NULL
, UNIQUE UQ_CONTACT_TEL_DETAIL_1(PRODUCT_ID, TAG)
, PRIMARY KEY (ID)
, CONSTRAINT FK_PRODUCT_TAG_1 FOREIGN KEY (PRODUCT_ID)
REFERENCES PRODUCT(ID) ON DELETE CASCADE
);
insert into product (PRODUCTNAME, PRICE) values ('Product A', 12.06);
insert into product (PRODUCTNAME, PRICE) values ('Product B', 60.12);
insert into product (PRODUCTNAME, PRICE) values ('Product C', 26.01);
insert into product_tag (PRODUCT_ID, TAG) values (1, 'electronic');
insert into product_tag (PRODUCT_ID, TAG) values (1, 'cameras');
insert into product_tag (PRODUCT_ID, TAG) values (2, 'electronic');
insert into product_tag (PRODUCT_ID, TAG) values (2, 'computer');
insert into product_tag (PRODUCT_ID, TAG) values (3, 'home');
Step 2: Product.java
@Entity
@Table(name = "product")
public class Product implements Serializable {
private static final long serialVersionUID = 1L;
private Long id;
private String productName;
private double price;
private Set<ProductTag> productTags = new HashSet<ProductTag>();
public Product() {}
public Product(String productName, double price) {
this.price = price;
this.productName = productName;
}
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
@Column(name = "PRODUCTNAME")
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
@Column(name = "PRICE")
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
@OneToMany(mappedBy="product",
cascade=CascadeType.ALL,
orphanRemoval=true)
public Set<ProductTag> getProductTags() {
return productTags;
}
public void setProductTags(Set<ProductTag> productTags) {
this.productTags = productTags;
}
@Override
public String toString() {
return "Product [id=" + id + ", productName="
+ productName + ", price=" + price + "]";
}
}
Step 3: ProductTag.java
@Entity
@Table(name = "product_tag")
public class ProductTag implements Serializable
{
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
@Column(name="TAG")
private String tag;
@ManyToOne
@JoinColumn(name="PRODUCT_ID")
private Product product;
public ProductTag() {}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getTag() {
return tag;
}
public void setTag(String tag) {
this.tag = tag;
}
public Product getProduct() {
return product;
}
public void setProduct(Product product) {
this.product = product;
}
@Override
public String toString() {
return "ProductTag [id=" + id + ", tag=" + tag
+ ", product=" + product + "]";
}
}
Step 4: ProductService.java (interface)
public interface ProductService {
public List<Product> findAll();
public Product findById(Long id);
public List<Product> findProductsWithTag();
public List<ProductsTagReport> findProductsTagReport();
public Product save(Product Product);
public void delete(Product Product);
public Product findByProductName(String productName);
}
Step 5: ProductServiceImpl.java
To use UntypeQuery, EntityManager call method createQuery with JPQL parameter and specify with class type. To set parameter call .setParameter() just like JDBC and support ? variable and named parameter. If getting single result used getSingleResult otherwise getResultList.
There are 2 options to used untypeQuery,
First Options : Select the columns within JPQL statement, An iterator result will be return. In order to get each record, you will required to iterate each item, and an array objects will represent the columns data you had selected in JPQL statement. See findProductsWithTag() method example.
Second Options : Create a new POJO and specific the columns that you need to mapping. Insert the new object with qualify namespace and constructor with JPQL syntax. See findProductsTagReport() method example
@Service("jpaProductService")
@Repository
@Transactional
public class ProductServiceImpl implements ProductService {
@PersistenceContext
EntityManager em;
@Transactional(readOnly=true)
public List<Product> findProductsWithTag() {
String jpql = "select p.productName, p.price, t.tag from Product p " +
+ "left join p.productTags t where t.tag = 'electronic'";
List results = em.createQuery(sql).getResultList();
int ctr = 0;
for ( Iterator i = results.iterator(); i.hasNext(); )
{
Object[] record = (Object[]) i.next();
System.out.print("Product name: " + record[0]);
System.out.print("Product price: " + record[1]);
System.out.print("Product tag: " + record[2]);
System.out.println("\n");
}
return results;
}
@Transactional(readOnly=true)
public List<ProductsTagReport> findProductsTagReport() {
String jpql = "select new com.codeomitted.orm.ProductsTagReport("
+ " p.productName, p.price, t.tag) "
+ " from Product p "
+ " left join p.productTags t where t.tag = 'electronic' ";
List<ProductsTagReport> reports = em.createQuery(jpql, ProductsTagReport.class);
return reports;
}
@Transactional(readOnly=true)
public List<Product> findAll() {
// code omitted
}
@Transactional(readOnly=true)
public Product findById(Long id) {
// code omitted
}
public Product save(Product product) {
// code omitted
}
public void delete(Product product) {
// code omitted
}
@Transactional(readOnly=true)
public Product findByProductName(String productName) {
// code omitted
}
}
Step 5: ProductsTagReport.java
public class ProductsTagReport {
private String productName;
private double price;
private String tag;
public ProductsTagReport(String productName, double price, String tag) {
this.productName = productName;
this.price = price;
this.tag = tag;
}
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getTag() {
return tag;
}
public void setTag(String tag) {
this.tag = tag;
}
@Override
public String toString() {
return "ProductAndTagSummary [productName="
+ productName + ", price= "
+ price + ", tag=" + tag + "]";
}
}
Step 6: applicationContext.xml
Since I’m using LocalContainerEntityManagerFactoryBean, therefore no persistent.xml required when using packagesToScan property.
<context:annotation-config />
<context:component-scan base-package="com.codeomitted.orm" />
<tx:annotation-driven/>
<jdbc:embedded-database id="dataSource" type="H2">
<jdbc:script location="classpath:orm/product.sql"/>
</jdbc:embedded-database>
<bean id="transactionManager"
class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory" ref="emf" />
</bean>
<bean id="emf"
class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
</property>
<property name="packagesToScan" value="com.codeomitted.orm" />
<property name="jpaProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.H2Dialect</prop>
<prop key="hibernate.jdbc.fetch_size">50</prop>
<prop key="hibernate.jdbc.batch_size">19</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
Step 6: Main.java
Execute the program.
public static void main(String[] args) {
GenericXmlApplicationContext ctx = new GenericXmlApplicationContext();
ctx.load("classpath:orm-02-applictionContext.xml");
ctx.refresh();
ProductService service = ctx.getBean("jpaProductService",ProductService.class);
List<Product> list = service.findProductsWithTag();
List<ProductsTagReport> list = service.findProductsTagReport();
for (ProductsTarReport record: list) {
System.out.println(record);
}
ctx.close();
}
JPA – UntypeResult