HQL is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL works with persistent objects and their properties. It is a superset of the JPQL, the Java Persistence Query Language; a JPQL query is a valid HQL query, but not all HQL queries are valid JPQL queries. HQL is a language with its own syntax and grammar. It is written as strings, like ?from Product p?. your HQL queries are translated by Hibernate into conventional SQL queries; Hibernate also provides an API that allows you to directly issue SQL queries as well.

CREATE TABLE `Employee` (
 `emp_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `emp_name` varchar(20) NOT NULL,
 `emp_salary` double(10,0) NOT NULL DEFAULT '0',
 PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `Address` (
 `emp_id` int(11) unsigned NOT NULL,
 `address_line1` varchar(50) NOT NULL DEFAULT '',
 `zipcode` varchar(10) DEFAULT NULL,
 `city` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`emp_id`),
 CONSTRAINT `emp_fk_1` FOREIGN KEY (`emp_id`) REFERENCES `Employee` (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `Employee` (`emp_id`, `emp_name`, `emp_salary`)
VALUES
 (1, 'Pankaj', 100),
 (2, 'David', 200),
 (3, 'Lisa', 300),
 (4, 'Jack', 400);
 
 
INSERT INTO `Address` (`emp_id`, `address_line1`, `zipcode`, `city`)
VALUES
 (1, 'Albany Dr', '95129', 'San Jose'),
 (2, 'Arques Ave', '95051', 'Santa Clara'),
 (3, 'BTM 1st Stage', '560100', 'Bangalore'),
 (4, 'City Centre', '100100', 'New Delhi');
 
commit;

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.journaldev.hibernate</groupId>
 <artifactId>HQLExample</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <dependencies>
 <dependency>
 <groupId>org.hibernate</groupId>
 <artifactId>hibernate-core</artifactId>
 <version>4.3.5.Final</version>
 </dependency>
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.0.5</version>
 </dependency>
 </dependencies>
</project>

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
 "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
 "http://hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
 <session-factory>
 <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
 <property name="hibernate.connection.password">pankaj123</property>
 <property name="hibernate.connection.url">jdbc:mysql://localhost/TestDB</property>
 <property name="hibernate.connection.username">pankaj</property>
 <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
 
 <property name="hibernate.current_session_context_class">thread</property>
 <property name="hibernate.show_sql">true</property>
 
 <mapping class="com.journaldev.hibernate.model.Employee"/>
 <mapping class="com.journaldev.hibernate.model.Address"/>
 </session-factory>
</hibernate-configuration>

HibernateUtil.java

import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
 
public class HibernateUtil {
 
 private static SessionFactory sessionFactory;
 
 private static SessionFactory buildSessionFactory() {
 try {
 // Create the SessionFactory from hibernate.cfg.xml
 Configuration configuration = new Configuration();
 configuration.configure("hibernate.cfg.xml");
 System.out.println("Hibernate Configuration loaded");
 
 ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
 System.out.println("Hibernate serviceRegistry created");
 
 SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);
 
 return sessionFactory;
 }
 catch (Throwable ex) {
 System.err.println("Initial SessionFactory creation failed." + ex);
 ex.printStackTrace();
 throw new ExceptionInInitializerError(ex);
 }
 }
 
 public static SessionFactory getSessionFactory() {
 if(sessionFactory == null) sessionFactory = buildSessionFactory();
 return sessionFactory;
 }
}

(追記) (追記ここまで)

Employee.java

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToOne;
import javax.persistence.Table;
 
import org.hibernate.annotations.Cascade;
 
@Entity
@Table(name = "EMPLOYEE")
public class Employee {
 
 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "emp_id")
 private long id;
 
 @Column(name = "emp_name")
 private String name;
 
 @Column(name = "emp_salary")
 private double salary;
 
 @OneToOne(mappedBy = "employee")
 @Cascade(value = org.hibernate.annotations.CascadeType.ALL)
 private Address address;
 
 public long getId() {
 return id;
 }
 
 public void setId(long id) {
 this.id = id;
 }
 
 public Address getAddress() {
 return address;
 }
 
 public void setAddress(Address address) {
 this.address = address;
 }
 
 public String getName() {
 return name;
 }
 
 public void setName(String name) {
 this.name = name;
 }
 
 public double getSalary() {
 return salary;
 }
 
 public void setSalary(double salary) {
 this.salary = salary;
 }
 
}

Address.java

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToOne;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;
 
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Parameter;
 
@Entity
@Table(name = "ADDRESS")
public class Address {
 
 @Id
 @Column(name = "emp_id", unique = true, nullable = false)
 @GeneratedValue(generator = "gen")
 @GenericGenerator(name = "gen", strategy = "foreign", 
 parameters = { @Parameter(name = "property", value = "employee") })
 private long id;
 
 @Column(name = "address_line1")
 private String addressLine1;
 
 @Column(name = "zipcode")
 private String zipcode;
 
 @Column(name = "city")
 private String city;
 
 @OneToOne
 @PrimaryKeyJoinColumn
 private Employee employee;
 
 public long getId() {
 return id;
 }
 
 public void setId(long id) {
 this.id = id;
 }
 
 public String getAddressLine1() {
 return addressLine1;
 }
 
 public void setAddressLine1(String addressLine1) {
 this.addressLine1 = addressLine1;
 }
 
 public String getZipcode() {
 return zipcode;
 }
 
 public void setZipcode(String zipcode) {
 this.zipcode = zipcode;
 }
 
 public String getCity() {
 return city;
 }
 
 public void setCity(String city) {
 this.city = city;
 }
 
 public Employee getEmployee() {
 return employee;
 }
 
 public void setEmployee(Employee employee) {
 this.employee = employee;
 }
 
}

HQLExamples.java

import java.util.Arrays;
import java.util.List;
 
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
 
import com.journaldev.hibernate.model.Employee;
import com.journaldev.hibernate.util.HibernateUtil;
 
public class HQLExamples {
 
 @SuppressWarnings("unchecked")
 public static void main(String[] args) {
 
 //Prep work
 SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
 Session session = sessionFactory.getCurrentSession();
 
 //Get All Employees
 Transaction tx = session.beginTransaction();
 Query query = session.createQuery("from Employee");
 List<Employee> empList = query.list();
 for(Employee emp : empList){
 System.out.println("List of Employees::"+emp.getId()+","+emp.getAddress().getCity());
 }
 
 //Get Employee with id
 query = session.createQuery("from Employee where id= :id");
 query.setLong("id", 3);
 Employee emp = (Employee) query.uniqueResult();
 System.out.println("Employee Name="+emp.getName()+", City="+emp.getAddress().getCity());
 
 //pagination example
 query = session.createQuery("from Employee");
 query.setFirstResult(0); //starts with 0
 query.setFetchSize(2);
 empList = query.list();
 for(Employee emp4 : empList){
 System.out.println("Paginated Employees::"+emp4.getId()+","+emp4.getAddress().getCity());
 }
 
 //Update Employee
 query = session.createQuery("update Employee set name= :name where id= :id");
 query.setParameter("name", "Pankaj Kumar");
 query.setLong("id", 1);
 int result = query.executeUpdate();
 System.out.println("Employee Update Status="+result);
 
 //Delete Employee, we need to take care of foreign key constraints too
 query = session.createQuery("delete from Address where id= :id");
 query.setLong("id", 4);
 result = query.executeUpdate();
 System.out.println("Address Delete Status="+result);
 
 query = session.createQuery("delete from Employee where id= :id");
 query.setLong("id", 4);
 result = query.executeUpdate();
 System.out.println("Employee Delete Status="+result);
 
 //Aggregate function examples
 query = session.createQuery("select sum(salary) from Employee");
 double sumSalary = (Double) query.uniqueResult();
 System.out.println("Sum of all Salaries= "+sumSalary);
 
 //join examples
 query = session.createQuery("select e.name, a.city from Employee e "
 + "INNER JOIN e.address a");
 List<Object[]> list = query.list();
 for(Object[] arr : list){
 System.out.println(Arrays.toString(arr));
 }
 
 //group by and like example
 query = session.createQuery("select e.name, sum(e.salary), count(e)"
 + " from Employee e where e.name like '%i%' group by e.name");
 List<Object[]> groupList = query.list();
 for(Object[] arr : groupList){
 System.out.println(Arrays.toString(arr));
 }
 
 //order by example
 query = session.createQuery("from Employee e order by e.id desc");
 empList = query.list();
 for(Employee emp3 : empList){
 System.out.println("ID Desc Order Employee::"+emp3.getId()+","+emp3.getAddress().getCity());
 }
 
 //rolling back to save the test data
 tx.rollback();
 
 //closing hibernate resources
 sessionFactory.close();
 }
 
}

After doing this kind of activity build and run the project. For doing this we need to go into the project node and right click on the project and select to the run project.

Previous Home Next

(追記) (追記ここまで)

R4R Topics