JPA Criteria Queries | Hibernate - Criteria Queries
For Explanation Watch Video :
Directory Structure
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.test</groupId>
<artifactId>JPACriteriaAPI</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>JPACriteriaAPI</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.9</maven.compiler.source>
<maven.compiler.target>1.9</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.6.8.Final</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
</dependencies>
</project>
hibernate.cfg.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-5.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/new</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">root</property>
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="hbm2ddl.auto">update</property>
</session-factory>
</hibernate-configuration>
Employee
package com.test.entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "emptab")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
private String dept;
private Double sal;
public Employee(String name, Double sal,String dept) {
super();
this.name = name;
this.dept = dept;
this.sal = sal;
}
}
HibernateUtil
package com.test.utility;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import com.test.entity.Employee;
public class HibernateUtil {
static SessionFactory factory = null;
static {
Configuration cfg = new Configuration();
cfg.configure("hibernate.cfg.xml");
cfg.addAnnotatedClass(Employee.class);
factory = cfg.buildSessionFactory();
}
public static SessionFactory getSessionFactory() {
return factory;
}
public static Session getSession() {
return factory.openSession();
}
}
App
package com.test;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import com.test.entity.Employee;
import com.test.utility.HibernateUtil;
public class App
{
public static void main( String[] args )
{
//get the SessionFactory Obj
SessionFactory factory = HibernateUtil.getSessionFactory();
//get Session Obj
Session ses = HibernateUtil.getSession();
try(factory;ses){
//create the Objects
Employee e1 = new Employee("sam",5000.0,"QA");
Employee e2 = new Employee("Jhon",6000.0,"BA");
Employee e3 = new Employee("Randy",7000.0,"DEV");
Employee e4 = new Employee("Brock",5000.0,"QA");
Employee e5 = new Employee("Angela",6000.0,"BA");
Employee e6 = new Employee("Carla",7000.0,"DEV");
Employee e7 = new Employee("Raja",5000.0,"QA");
Employee e8 = new Employee("Rani",6000.0,"BA");
Employee e9 = new Employee("Ram",7000.0,"DEV");
Employee e10 = new Employee("Sandy",5000.0,"QA");
//begin the tx
ses.beginTransaction();
ses.save(e1);
ses.save(e2);
ses.save(e3);
ses.save(e4);
ses.save(e5);
ses.save(e6);
ses.save(e7);
ses.save(e8);
ses.save(e9);
ses.save(e10);
//commit the tx
ses.getTransaction().commit();
}
}
}
Test
package com.test;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaDelete;
import javax.persistence.criteria.Root;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import com.test.entity.Employee;
import com.test.utility.HibernateUtil;
public class Test {
public static void main(String[] args) {
//get the SessionFactory Obj
SessionFactory factory = HibernateUtil.getSessionFactory();
//get Session Obj
Session ses = HibernateUtil.getSession();
try(factory;ses){
//create cb
CriteriaBuilder cb = ses.getCriteriaBuilder();
//create CQ Object
CriteriaQuery cq = cb.createQuery(Employee.class);
//create root Object
Root<Employee> root = cq.from(Employee.class);
//write the cluase conditions
cq.select(root).where(cb.and(cb.ge(root.get("id"),5),cb.le(root.get("id"),10)));
//create query object
Query query = ses.createQuery(cq);
//execute the query
List<Employee> list = query.getResultList();
//print
list.forEach(System.out::println);
//select all the emp
//create CB Obj
CriteriaBuilder cb = ses.getCriteriaBuilder();
//create CQ Object
CriteriaQuery cq = cb.createQuery(Employee.class);
//create root Object
Root<Employee> root = cq.from(Employee.class);//select * from Emp;
//create query Object
Query query = ses.createQuery(cq);
//execute the query
List<Employee> list = query.getResultList();
//print the info
list.forEach(System.out::println);
//create CB Object
CriteriaBuilder cb = ses.getCriteriaBuilder();
//create CQ Object
CriteriaQuery cq = cb.createQuery(Employee.class);
//create root
Root<Employee> root = cq.from(Employee.class);//select * from Emp;
//clause condtion
cq.select(root).where(root.get("name").in("sam","Jhon","carla")).orderBy(cb.asc(root.get("sal")));
//create query Object
Query query = ses.createQuery(cq);
//execute the query
List<Employee> list = query.getResultList();
//print the info
list.forEach(System.out::println);
//create CB Object
CriteriaBuilder cb = ses.getCriteriaBuilder();
//create CQ Object
CriteriaQuery cq = cb.createQuery(Employee.class);
//create root
Root<Employee> root = cq.from(Employee.class);//select * from Emp;
//clause condition
cq.select(root).where(cb.like(root.get("name"), "s%"));
//create query Object
Query query = ses.createQuery(cq);
//execute the query
List<Employee> list = query.getResultList();
//print the info
list.forEach(System.out::println);
//select specific multiple cols
//create CB Object
CriteriaBuilder cb = ses.getCriteriaBuilder();
//create CQ Object
CriteriaQuery cq = cb.createQuery(Object[].class);
//create root
Root<Employee> root = cq.from(Employee.class);//select * from Emp;
//clause conditon
cq.multiselect(root.get("id"),root.get("name")).where(cb.and(cb.ge(root.get("id"),5),(cb.le(root.get("id"),10)))).orderBy(cb.desc(root.get("id")));
//create query Object
Query query = ses.createQuery(cq);
//execute the query
List<Object[]> list = query.getResultList();
//print
list.forEach(roe->{
for(Object ob : roe) {
System.out.print(ob + " ");
}
System.out.println();
});
//select ename from emp where ename like 's%'
//create CB Object
CriteriaBuilder cb = ses.getCriteriaBuilder();
//create CQ Object
CriteriaQuery cq = cb.createQuery(String.class);
//create root
Root<Employee> root = cq.from(Employee.class);//select * from Emp;
//clause conditon
cq.multiselect(root.get("name")).where(cb.like(root.get("name"), "R%"));
//create query Object
Query query = ses.createQuery(cq);
//execute
List<String> list = query.getResultList();
//print
list.forEach(System.out::println);
//select count(empid) from emp
//create CB Object
CriteriaBuilder cb = ses.getCriteriaBuilder();
//create CQ Object
CriteriaQuery cq = cb.createQuery(Long.class);
//create root
Root<Employee> root = cq.from(Employee.class);//select * from Emp;
//clause conditon
cq.multiselect(cb.count(root.get("id")));
//create query obj
Query query = ses.createQuery(cq);
//execute
Long count = (Long)query.getSingleResult();
//print
System.out.println("Number of emps "+count);
//select count(eid),sum(sal),max(sal),min(sal),avg(sal) from emp
//create CB Object
CriteriaBuilder cb = ses.getCriteriaBuilder();
//create CQ Object
CriteriaQuery cq = cb.createQuery(Object[].class);
//create root
Root<Employee> root = cq.from(Employee.class);//select * from Emp;
//specific col
cq.multiselect(cb.count(root.get("id")),
cb.max(root.get("sal")),
cb.min(root.get("sal")),
cb.avg(root.get("sal")),
cb.sum(root.get("sal"))
);
//create query obj
Query query = ses.createQuery(cq);
//excute
Object[] obj = (Object[])query.getSingleResult();
//print
System.out.println("Count of emo "+obj[0]);
System.out.println("max sal "+obj[1]);
System.out.println("min of sal "+obj[2]);
System.out.println("avg of sal "+obj[3]);
System.out.println("sum of sal "+obj[4]);
//update the QA dept emp sal to 8000
//create CB Object
CriteriaBuilder cb = ses.getCriteriaBuilder();
//create CU object
CriteriaUpdate cu = cb.createCriteriaUpdate(Employee.class);
//create root
Root<Employee> root = cu.from(Employee.class);//select * from Emp;
//clause
cu.set(root.get("sal"), 8000.0).where(cb.equal(root.get("dept"), "QA"));
//create Query Obj
Query query = ses.createQuery(cu);
//execute
ses.beginTransaction();
int count = query.executeUpdate();
//print the number of records updated
System.out.println("Number of records Updated "+count);
//commit tx
ses.getTransaction().commit();
//delete the emp whose name start with r
//create CB Object
CriteriaBuilder cb = ses.getCriteriaBuilder();
//create cd obj
CriteriaDelete cd = cb.createCriteriaDelete(Employee.class);
//create root
Root<Employee> root = cd.from(Employee.class);//select * from Emp;
//clause condition
cd.where(cb.like(root.get("name"), "r%"));
//create Query Object
Query query = ses.createQuery(cd);
//execute
ses.beginTransaction();
int count = query.executeUpdate();
System.out.println("Number of emp deleted "+count);
//commit the tx
ses.getTransaction().commit();
}
}
}