JPA Max Function

In this section, you will learn how to develop a jpa max function.

JPA Max Function

JPA Max Function

     

In this section, you will learn how to develop a jpa max function. JPA max function retrieves data from the database table field which is maximum of given field.

JPA Max Function:

Query query=em.createQuery
("SELECT p FROM Product p WHERE p.price <
(SELECT MAX(p.price) FROM p)"
);

This query retrieves all product which is less than maximum price of product.

Developing this application, you need the following artifacts:

  1. Database Table: product
  2. Model Class: Product.java
  3. Main Class: JPAMaxFunction.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: JPAMaxFunction.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 JPAMaxFunction {

/**
* @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();
entr.begin();
Query query=em.createQuery("SELECT p FROM Product p WHERE p.price < (SELECT MAX(p.price) FROM p)");
List pList=query.getResultList();
Iterator pIterator=pList.iterator();
while(pIterator.hasNext()){
Product pro=(Product)pIterator.next();
System.out.print("Item name:"+pro.getItemName());
System.out.print(" Price:"+pro.getPrice());
System.out.println();
}
entr.commit();
}
finally{
em.close();
}
}
}

Output:

log4j:WARN No appenders could be found for logger
 (org.hibernate.cfg.annotations.Version).

log4j:WARN Please initialize the log4j system properly.

Hibernate: select product0_.id as id1_, product0_.itemName
as itemName1_, product0_.price as price1_ from product
product0_ where product0_.price<(select MAX(product0_.price)
from product product0_)

Item name:Pen Price:225.0

Item name:Book Price:625.0

Item name:T.V. Price:6000.0