HQL (Hibernate Query Language) Tutorial with Examples

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">

<artifactId>HQLQueries</artifactId>

<version>0.0.1-SNAPSHOT</version>

<name>HQLQueries</name>

<url>http://maven.apache.org</url>

<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>

<groupId>junit</groupId>

<artifactId>junit</artifactId>

</dependency>

<groupId>org.hibernate</groupId>

<artifactId>hibernate-core</artifactId>

<version>5.6.8.Final</version>

</dependency>

<groupId>org.projectlombok</groupId>

<artifactId>lombok</artifactId>

<scope>provided</scope>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

</dependencies>

</project>

HibernateUtil

package com.test.util;

import java.util.Properties;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.cfg.Configuration;

import org.hibernate.cfg.Environment;

import com.test.entity.Employee;

public class HibernateUtil {

static SessionFactory factory = null;

static {

Configuration cfg = new Configuration();

Properties prop = new Properties();

prop.put(Environment.URL, "jdbc:mysql://localhost:3306/new");

prop.put(Environment.USER, "root");

prop.put(Environment.PASS, "root");

prop.put(Environment.SHOW_SQL, true);

prop.put(Environment.FORMAT_SQL, true);

prop.put(Environment.HBM2DDL_AUTO, "update");

cfg.setProperties(prop);

cfg.addAnnotatedClass(Employee.class);

factory = cfg.buildSessionFactory();

}

public static SessionFactory getSessionFactory() {

return factory;

}

public static Session getSession() {

return factory.openSession();

}

Employee

package com.test.entity;

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 Double sal;

private String dept;

public Employee(String name, Double sal, String dept) {

super();

this.name = name;

this.sal = sal;

this.dept = dept;

}

App

package com.test;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import com.test.entity.Employee;

import com.test.util.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 emp 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();

//save

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 java.util.List;

import javax.persistence.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import com.test.entity.Employee;

import com.test.util.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) {

//select * from emptab

//hql->from Employee

//create the query Object

Query query1 = ses.createQuery("from Employee");

//execute the query

List<Employee> list1 = query1.getResultList();

//print the list

list1.forEach(System.out::println);

//positional parameters

//select * from emptab where name in("sam","jhon")

//from Employee where name in(?1,?2)

//create the query Object

Query query2 = ses.createQuery("from Employee where name in(?50,?51)");

//Query query1 = ses.createQuery("from Employee where name in (?0,?1)");

//Query query1 = ses.createQuery("from Employee where name in (?0,?2)");//not allowed gap

//Query query1 = ses.createQuery("from Employee where name in (?50,?51)");//allowed

//Query query1 = ses.createQuery("from Employee where name in (?-1,?0)");//negative not allowed

//Query query1 = ses.createQuery("from Employee where name in (?11,?10)");//allowed

//set the param values

query2.setParameter(50, "sam");

query2.setParameter(51, "jhon");

//execute the query

List<Employee> list2 = query2.getResultList();

//print the list

list2.forEach(System.out::println);

//named parameters

//select * from emptab where id>=5 and id<=10

//from Employee where id>=:min and id<=:max

//create the query Object

Query query3 = ses.createQuery("from Employee where id>=:min and id<=:max");

//set the named param values

query3.setParameter("min", 5);

query3.setParameter("max", 10);

//execute the query

List<Employee> list3 = query3.getResultList();

//print the re

list3.forEach(System.out::println);

//positional and named param in the same query

//select * from emptab where id>=5 and id<=10

//from Employee where id>=:min and id<=:max

//create the query Object

Query query4 = ses.createQuery("from Employee where id>=?0 and id<=:max");

//set the named param values

query4.setParameter(0, 5);

query4.setParameter("max", 10);

//execute the query

List<Employee> list4 = query4.getResultList();

//print the re

list4.forEach(System.out::println);

//retriving the multiple specific col values

//select id,name,sal from emp where id>=:lowId and id<=:highId

//create the qyery object

Query query5 = ses.createQuery("select id,name,sal from Employee where id>=:min and id<=:max");

//set the param values

query5.setParameter("min",4);

query5.setParameter("max",7);

//execute the query

List<Object[]> list5 = query5.getResultList();

//print the res

list5.forEach(ob->{

for(Object val : ob) {

System.out.print(val+" ");

}

System.out.println();

});

//retriving the single col values

//select name from emp

//create the query

Query query6 = ses.createQuery("select name from Employee order by name desc");

//execute the query

List<Object> list = query6.getResultList();

//print the res

list.forEach(System.out::println);

//retriveing the single record

//select * from emp where id=5

//create the query

Query query7 = ses.createQuery("from Employee where id=:id");

//set the param values

query7.setParameter("id", 7);

//execute the query

Employee emp = (Employee)query7.getSingleResult();

//print the res

System.out.println("Emp with id 7 : "+emp);

//single record multiple columns

//select id,name from emp where id=6

//create the query

Query query8 = ses.createQuery("select id,name from Employee where id=:id");

//set the param values

query8.setParameter("id", 4);

//execute the query

Object[] obj = (Object[])query8.getSingleResult();

//print the res

System.out.println("id "+obj[0]+" name "+obj[1]);

//single row single value

//select name from emp where id=8

//create the query

Query query9 = ses.createQuery("select name from Employee where id=:id");

//set the param values

query9.setParameter("id", 2);

//execute the query

String name = (String)query9.getSingleResult();

//print the res

System.out.println("Name of emp with id 2 "+name);

//hql query having the aggregate functions

//select count(*) from emp

//create the query

Query query10 = ses.createQuery("select count(*) from Employee");

//execute the query

long count = (Long)query10.getSingleResult();

//print the res

System.out.println("No of records "+count);

//hql query having the multiple aggregate functions

//min , max, sum , avg sal

//create the query

Query query11 = ses.createQuery("select max(sal),min(sal),avg(sal),sum(sal) from Employee");

//execute the query

Object[] ar = (Object[])query11.getSingleResult();

//print the res

System.out.println("Max sal "+ar[0]);

System.out.println("Min sal "+ar[1]);

System.out.println("avg sal "+ar[2]);

System.out.println("sum sal "+ar[3]);

//hql sub query

//select * from emp where sal=(select max(sal) from emp);

//create the query

Query query12 = ses.createQuery("from Employee where sal=(select max(sal) from Employee)");

//execute the query

List<Employee> list12 = query12.getResultList();

//print the res

list12.forEach(System.out::println);

}

NonSelect

package com.test;

import javax.persistence.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import com.test.util.HibernateUtil;

public class NonSelect {

public static void main(String[] args) {

// get the SessionFactory obj

SessionFactory factory = HibernateUtil.getSessionFactory();

// get Session Obj

Session ses = HibernateUtil.getSession();

try (factory; ses) {

//begin the tx

ses.beginTransaction();

//update

//update emp set sal=sal+1000 where dept='dev'

//create the query

Query query = ses.createQuery("update Employee set sal=sal+:incre where dept=:dept");

//set the named param values

query.setParameter("incre", 1000.0);

query.setParameter("dept", "dev");

//execute the query

long count = query.executeUpdate();

//commit the tx

ses.getTransaction().commit();

//print

System.out.println("No of records updated "+count);*/

//delete from emptab where id>=2 and id<=4

//begin the tx

ses.beginTransaction();

//create the query

Query query = ses.createQuery("delete from Employee where id>=:min and id<=:max");

//set the parma values

query.setParameter("min", 2);

query.setParameter("max", 4);

//execute the query

int count = query.executeUpdate();

//commit the tx

ses.getTransaction().commit();

//print the res

System.out.println("No of emp deleted "+count);

}

Servlet Project Book Shop Application in eclipse

Servlet Project Book Shop Application in eclipse For explanation watch video::: Note :: In this Project you must configure web server (for example tomcat) with eclipse ide Download Bootstrap from :: https://getbootstrap.com/docs/4.3/getting-started/download/ Download mysql jar file from :: https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.22 adding MySQL Connector/J jar file in eclipse ide for jdbc video :: video link : https://youtu.be/4Fyd-k3eG_I Directory Structure:: web.xml <?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" id="WebApp_ID" version="4.0"> <welcome-file-list> <welcome-file>home.html</welcome-file> </welcome-file-list> <display-...

Idiot Programmer

Search This Blog