JPA Min Function
In this section, you will learn about the min function of JPA aggregate function. It find the minimum value of given field. It return a single result.
JPA Min Function:
Query query=em.createQuery( "SELECT p FROM Product p WHERE p.price=(SELECT MIN(p.price) FROM p)"); |
The above query you get the product which have the price is equals to minimum product price.
Developing this application, you need the following artifacts:
- Database Table: product
- Model Class: Product.java
- Main Class: JPAMinFunction.java
Database Table: product
CREATE TABLE `product` ( `id` int(11) NOT NULL auto_increment, `itemName` varchar(50) NOT NULL, `price` double NOT NULL, PRIMARY KEY (`id`) ) |
Model Class: Product.java
/** * */ package roseindia; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.Table; /** * @author Administrator * */ @Entity @Table(name="product") public class Product { @Id @GeneratedValue private int id; /** * @return the id */ public int getId() { return id; } /** * @param id the id to set */ public void setId(int id) { this.id = id; } @Column(name="itemName",length=50,nullable=false) private String itemName; /** * @return the itemName */ public String getItemName() { return itemName; } /** * @param itemName the itemName to set */ public void setItemName(String itemName) { this.itemName = itemName; } @Column(name="price") private double price; /** * @return the price */ public double getPrice() { return price; } /** * @param price the price to set */ public void setPrice(double price) { this.price = price; } } |
Main Class: JPAMinFunction.java
/** * */ package roseindia; import java.util.Iterator; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.EntityTransaction; import javax.persistence.Persistence; import javax.persistence.Query; /** * @author Administrator * */ public class JPAMinFunction { /** * @param args */ public static void main(String[] args) { // TODO Auto-generated method stub EntityManagerFactory emf=Persistence.createEntityManagerFactory("jpa"); EntityManager em=emf.createEntityManager(); try{ EntityTransaction entr=em.getTransaction(); Query query=em.createQuery("SELECT p FROM Product p WHERE p.price=(SELECT MIN(p.price) FROM p)"); entr.begin(); List pList=query.getResultList(); Iterator pIterator=pList.iterator(); while(pIterator.hasNext()){ Product pro=(Product)pIterator.next(); System.out.println("Item name:"+pro.getItemName()); System.out.println("Price:"+pro.getPrice()); } entr.commit(); } finally{ em.close(); } } }
Output:
log4j:WARN No appenders could be
found for logger log4j:WARN Please initialize the log4j system properly. Hibernate: select product0_.id as id1_, product0_.itemName
as Item name:Pen Price:225.0
|