HAVING Clause in JPQL (Java Persistence Query Language)

In this section, you will see to use of HAVING Clause in jpql (Java Persistence Query Language).

HAVING Clause: The HAVING clause is used to combine with the GROUP BY clause. It can be used with the SELECT statement to filter the records. A HAVING clause restricts the query results of a GROUP BY in the SelectExpression. This clause is applied to each group of grouped table, much as a WHERE clause is applied to a select list. If you don't use GROUP BY clause, the HAVING clause is applied to entire query result as a single group.

You need the following artifacts:

Database table: student
Model Class: Student.java
Main Class: JPAHavingClause.java

Table: student

CREATE TABLE `student` (
`id` int(11) NOT NULL auto_increment,
`sname` varchar(40) NOT NULL,
`sroll` int(11) NOT NULL,
`scourse` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
)

Model Class: Student.java

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package jpacrud;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

/**
*
* @author Administrator
*/
@Entity
@Table(name="student")

@NamedQueries({
@NamedQuery(name="readAllRecords",query="SELECT st FROM Student st"),
@NamedQuery(name="updateRecord",query="UPDATE Student st SET st.sname= ?1 WHERE st.sroll= ?2")
})
public class Student implements java.io.Serializable {

@Id
@GeneratedValue
private int id;

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

@Column(name="sname",length=40,nullable=false)
private String sname;

public String getSname() {
return sname;
}

public void setSname(String sname) {
this.sname = sname;
}

@Column(name="sroll",nullable=false)
private int sroll;

public int getSroll() {
return sroll;
}

public void setSroll(int sroll) {
this.sroll = sroll;
}

@Column(name="scourse",length=10,nullable=false)
private String scourse;

public String getScourse() {
return scourse;
}

public void setScourse(String scourse) {
this.scourse = scourse;
}

}

Main Class: JPAHavingClause.java

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package jpacrud;

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 JPAHavingClause {
public static void main(String arg[]){
EntityManagerFactory emf=Persistence.createEntityManagerFactory("netjpa");
EntityManager em=emf.createEntityManager();
try{
EntityTransaction entr=em.getTransaction();
entr.begin();
Query query=em.createQuery("SELECT st FROM Student st GROUP BY st.sname HAVING st.sname IN ('vinod', 'Ravi', 'Pinku')");
List stHavingList=query.getResultList();
Iterator stHavingIterator=stHavingList.iterator();
while(stHavingIterator.hasNext()){
Student stu=(Student)stHavingIterator.next();
System.out.print("Name:"+stu.getSname());
System.out.print(" Roll:"+stu.getSroll());
System.out.print(" Course:"+stu.getScourse());
System.out.println();
}
entr.commit();
}
catch(Exception ex){
System.out.println(ex.getMessage());
}
finally{
em.close();
}
}

}

Output:

init:
deps-jar:
compile-single:
run-single:
log4j:WARN No appenders could be found for logger (org.hibernate.cfg.annotations.Version).
log4j:WARN Please initialize the log4j system properly.
Hibernate: select student0_.id as id0_, student0_.scourse as scourse0_, student0_.sname as sname0_, student0_.sroll as sroll0_ from student student0_ group by student0_.sname having student0_.sname in ('vinod' , 'Ravi' , 'Pinku')

Name:Ravi Roll:102 Course:M.Tech.
Name:Vinod Roll:101 Course:MCA
BUILD SUCCESSFUL (total time: 6 seconds)

Download Application

HAVING Clause in JPQL (Java Persistence Query Language)

HAVING Clause in JPQL (Java Persistence Query Language)

HAVING Clause in JPQL (Java Persistence Query Language)

Tutorials