HQL(Hibernate Query Language)の例についてのチュートリアル

HQLまたはHibernateクエリ言語は、Hibernateフレームワークのオブジェクト指向クエリ言語です。HQLは、テーブル名の代わりにオブジェクトを使用する点を除いて、SQLに非常に似ています。これにより、オブジェクト指向プログラミングにより近くなります。

ハイバネートクエリ言語(HQL)

HQLと大文字と小文字の区別:HQLはjavaのクラスや変数名を除いて大文字と小文字を区別しないです。したがって、SeLeCTはsELEctと同じであり、SELECTとも同じですが、com.scdev.model.Employeeはcom.scdev.model.EMPLOYEEとは異なります。HQLで一般的にサポートされているいくつかの節は次のとおりです。

    1. HQLのFrom句:HQLのFrom句は、SQLのselect句と同じです。例えば、from Employeeは、select * from Employeeと同じ意味です。また、from Employee empやfrom Employee as empのようにエイリアスを作成することもできます。

 

    1. HQLのJoin:HQLは内部結合、左外部結合、右外部結合、フル結合をサポートしています。例えば、select e.name, a.city from Employee e INNER JOIN e.address aというクエリでは、Employeeクラスにaddressという変数が存在する必要があります。具体的なコードの例で説明します。

 

    1. 集計関数:HQLは、count(*)、count(distinct x)、min()、max()、avg()、sum()といった一般的に使用される集計関数をサポートしています。

 

    1. 式:HQLは算術演算子(+、-、*、/)、バイナリ比較演算子(=、>=、<=、<>, !=、like)、論理演算(and、or、not)などの式をサポートしています。

 

    1. HQLはまた、order by句やgroup by句もサポートしています。

 

    1. HQLはSQLクエリと同様に、サブクエリもサポートしています。

 

    HQLはDDL、DML、ストアプロシージャの実行もサポートしています。

プログラム内でHQLを使用した簡単な例を見てみましょう。

HQLのサンプルデータベース設定の例

例としてMySQLデータベースを使用しており、下記のスクリプトはEmployeeとAddressの2つのテーブルを作成します。これらは一対一の関連性を持っており、例を示すためにいくつかのデモデータを挿入しています。

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;

Eclipseまたはご使用のIDEでMavenプロジェクトを作成し、最終的なプロジェクトは以下の画像のようになります。

ハイバネートのMaven依存関係

最終的なpom.xmlには、HibernateとMySQLドライバーの依存関係が含まれています。

<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.scdev.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>

ネイティブな日本語に以下の内容を言い換えます(オプションは一つのみ):
ハイバネートの設定XML

私たちのHibernateの設定XMLファイルには、データベース接続に関連するプロパティやマッピングクラスが含まれています。私はHibernateマッピングのためにアノテーションを使用する予定です。hibernate.cfg.xmlのコードは以下の通りです。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
		"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
		"https://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">scdev123</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost/TestDB</property>
        <property name="hibernate.connection.username">scdev</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.scdev.hibernate.model.Employee"/>
        <mapping class="com.scdev.hibernate.model.Address"/>
    </session-factory>
</hibernate-configuration>

ヒベルネートのSessionFactoryのユーティリティクラス

私たちは、Hibernate SessionFactoryを設定するためのユーティリティクラスを持っています。

package com.scdev.hibernate.util;

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

アノテーションを使用したマッピングを持つモデルクラス

私たちのJPA アノテーションを使用したモデルクラスは以下のようになります。

package com.scdev.hibernate.model;

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

}
package com.scdev.hibernate.model;

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

}

HQLの例のテストクラス

JavaプログラムでHQLを使う方法を見てみましょう。

package com.scdev.hibernate.main;

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.scdev.hibernate.model.Employee;
import com.scdev.hibernate.util.HibernateUtil;

public class HQLExamples {

	@SuppressWarnings("unchecked")
	public static void main(String[] args) {
		
		//Prep work
		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		
		//HQL example - 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());
		}
		
		//HQL example - 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());
		
		//HQL 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());
		}
		
		//HQL 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);

		//HQL 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);
		
		//HQL Aggregate function examples
		query = session.createQuery("select sum(salary) from Employee");
		double sumSalary = (Double) query.uniqueResult();
		System.out.println("Sum of all Salaries= "+sumSalary);
		
		//HQL 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));
		}
		
		//HQL 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));
		}
		
		//HQL 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();
	}

}

HQLのSelect、Update、Delete操作を使用していることに注意してください。また、HQLのJoinとHQLの集計関数の使い方も示しています。上記のHQLの例プログラムを実行すると、以下の出力が得られます。

May 22, 2014 1:55:37 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {4.0.4.Final}
May 22, 2014 1:55:37 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {4.3.5.Final}
May 22, 2014 1:55:37 PM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
May 22, 2014 1:55:37 PM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: HHH000021: Bytecode provider name : javassist
May 22, 2014 1:55:37 PM org.hibernate.cfg.Configuration configure
INFO: HHH000043: Configuring from resource: hibernate.cfg.xml
May 22, 2014 1:55:37 PM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: HHH000040: Configuration resource: hibernate.cfg.xml
May 22, 2014 1:55:37 PM org.hibernate.cfg.Configuration doConfigure
INFO: HHH000041: Configured SessionFactory: null
Hibernate Configuration loaded
Hibernate serviceRegistry created
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH000402: Using Hibernate built-in connection pool (not for production use!)
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000401: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost/TestDB]
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000046: Connection properties: {user=scdev, password=****}
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000006: Autocommit mode: false
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
INFO: HHH000115: Hibernate connection pool size: 20 (min=1)
May 22, 2014 1:55:37 PM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.internal.LobCreatorBuilder useContextualLobCreation
INFO: HHH000423: Disabling contextual LOB creation as JDBC driver reported JDBC version [3] less than 4
May 22, 2014 1:55:38 PM org.hibernate.engine.transaction.internal.TransactionFactoryInitiator initiateService
INFO: HHH000399: Using default transaction strategy (direct JDBC transactions)
May 22, 2014 1:55:38 PM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory <init>
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
List of Employees::1,San Jose
List of Employees::2,Santa Clara
List of Employees::3,Bangalore
List of Employees::4,New Delhi
Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_ where employee0_.emp_id=?
Employee Name=Lisa, City=Bangalore
Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_
Paginated Employees::1,San Jose
Paginated Employees::2,Santa Clara
Paginated Employees::3,Bangalore
Paginated Employees::4,New Delhi
Hibernate: update EMPLOYEE set emp_name=? where emp_id=?
Employee Update Status=1
Hibernate: delete from ADDRESS where emp_id=?
Address Delete Status=1
Hibernate: delete from EMPLOYEE where emp_id=?
Employee Delete Status=1
Hibernate: select sum(employee0_.emp_salary) as col_0_0_ from EMPLOYEE employee0_
Sum of all Salaries= 600.0
Hibernate: select employee0_.emp_name as col_0_0_, address1_.city as col_1_0_ from EMPLOYEE employee0_ inner join ADDRESS address1_ on employee0_.emp_id=address1_.emp_id
[Pankaj Kumar, San Jose]
[David, Santa Clara]
[Lisa, Bangalore]
Hibernate: select employee0_.emp_name as col_0_0_, sum(employee0_.emp_salary) as col_1_0_, count(employee0_.emp_id) as col_2_0_ from EMPLOYEE employee0_ where employee0_.emp_name like '%i%' group by employee0_.emp_name
[David, 200.0, 1]
[Lisa, 300.0, 1]
Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_ order by employee0_.emp_id desc
ID Desc Order Employee::3,Bangalore
ID Desc Order Employee::2,Santa Clara
ID Desc Order Employee::1,San Jose
May 22, 2014 1:55:38 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop
INFO: HHH000030: Cleaning up connection pool [jdbc:mysql://localhost/TestDB]

削除操作が行われると、そのレコードデータ(給与の合計は600です)が表示されなくなることに注意してください。しかし、私はトランザクションをロールバックしているため、テーブル内のデータは変更されません。コードを変更してトランザクションをコミットすると、それがデータベーステーブルに反映されます。私はHQLクエリを多用するのは好きではありません。なぜなら、コード内でテーブルのマッピングに気を付ける必要があるからです。Employeeオブジェクトを削除するためにSessionを使用すると、2つのテーブルからレコードが削除されます。以下のリンクからサンプルHQLのプロジェクトをダウンロードして、さらに例を試してみることができます。

ヒベルネイトHQLプロジェクトをダウンロードしてください。

コメントを残す 0

Your email address will not be published. Required fields are marked *