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

HAVING Clause in JPQL (Java Persistence Query Language)

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:

  1. Database table: student
  2. Model Class: Student.java
  3. 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