JPA Native SQL Query

This tutorial demonstrate using JPA2.0 with createNativeQuery and hibernate as vendor provider.

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)
);


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);

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;
	
	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;
	}

	@Override
	public String toString() {
		return "Product [id=" + id + ", productName=" 
                + productName + ", price=" + price + "]";
	}
	
}

Step 3: ProductService.java (interface)

public interface ProductService {

	public List<Product> findAll();
	
	public Product findById(Long id);
	
	public Product save(Product Product);
	
	public void delete(Product Product);
	
	public Product findByProductName(String productName);
}

Step 4: ProductServiceImpl.java

To use NativeQuery, EntityManager call method createNativeQuery with parameter sql statement, and specify with class type. If getting single result used getSingleResult otherwise getResultList. ** Named parameter binding is not support in native query.

@Service("jpaProductService")
@Repository
@Transactional
public class ProductServiceImpl implements ProductService {

  @PersistenceContext
  EntityManager em;

  @SuppressWarnings("unchecked")	
  @Transactional(readOnly=true)
  public List<Product> findAll() {
	String sql = "select * from Product";
	Query query = em.createNativeQuery(sql, Product.class);
	List<Product> list = query.getResultList();
	return list;
  }

  @Transactional(readOnly=true)
  public Product findById(Long id) {
 	String sql = "select * from Product where id = ?";
	Query query = em.createNativeQuery(sql, Product.class);
	query.setParameter(1, id);
        return (Product) query.getSingleResult();
  }

  public Product save(Product product) {
 	return em.merge(product);
  }

  public void delete(Product product) {
	em.remove(product);
  }

  @Transactional(readOnly=true)
  public Product findByProductName(String productName) {
	String sql = "select * from Product where PRODUCTNAME like ?";
	Query query = em.createNativeQuery(sql, Product.class);
	query.setParameter(1, '%' + productName + '%');
	return (Product)query.getSingleResult();
  }
}

Step 5: 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-01-applictionContext.xml");
  ctx.refresh();
		
  ProductService service = ctx.getBean("jpaProductService",ProductService.class);
  List<Product> list = service.findAll();
		
  for (Product product: list) {
	System.out.println(product);
  }
		
  Product c = new Product("Product Z", 11.11);
  service.save(c);

  c = service.findByProductName("Product Z");
  System.out.println(c);
		
  System.out.println(service.findById(1L));
  ctx.close();
		 
}