[Hibernate] 7. Hibernate(하이버네이트) HQL

프로그래밍n학습자료(2018~)/Hibernate

[Hibernate] 7. Hibernate(하이버네이트) HQL

단세포소년 2018. 3. 8. 16:59

HQL 사용하기

HQL은 SQL과는 거의 비슷하다. 다른 점이라면 테이블명이나 컬럼명을 쓰는 것이 아니라 맵핑 된 클래스명과 멤버변수명을 쓰는 것이다.

그리고 테이블과 맵핑된 클래스명을 쓸때는 Alias 를 꼭 써야한다.

HQL을 쓰면 SQL로 맵핑되고 그 결과가 다시 맵핑 클래스로 맵핑되는 형태이다. 즉 HQL의 결과는 클래스로 맵핑된다.

예제 데이터

간단한 예제를 위해 foreigne key등 Constraint(제약조건)은 무시, 정규화 무시.

학생, 과목, 과목별점수 테이블로 이루어진 예제이다. 이 예제를 바탕으로 몇가지 HQL 예제를 확인할 것이다.

스키마 및 데이터(mysql):

DROP TABLE IF EXISTS `Course`;

CREATE TABLE `Course` (

`ID` int(11) NOT NULL,

`COURSE_NAME` varchar(45) DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `Course` WRITE;

INSERT INTO `Course` VALUES (1,'Science'),(2,'Math'),(3,'English'),(4,'Music');

UNLOCK TABLES;

DROP TABLE IF EXISTS `Score`;

CREATE TABLE `Score` (

`STUDENT_ID` int(11) NOT NULL,

`COURSE_ID` int(11) NOT NULL,

`Score` int(11) DEFAULT NULL,

PRIMARY KEY (`STUDENT_ID`,`COURSE_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `Score` WRITE;

INSERT INTO `Score` VALUES (1,1,70),(1,2,50),(1,3,64),(2,1,10),(2,2,32),(2,4,15),(3,3,99);

UNLOCK TABLES;

DROP TABLE IF EXISTS `Student`;

CREATE TABLE `Student` (

`ID` int(11) NOT NULL,

`NAME` varchar(45) DEFAULT NULL,

`BIRTHDAY` date DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `Student` WRITE;

INSERT INTO `Student` VALUES (1,'Mark','1997-02-02'),(2,'Jane','1990-10-10'),(3,'Jone','1960-11-11');

UNLOCK TABLES;

Student:

ID	NAME	BIRTHDAY
1	Mark	1997-02-02
2	Jane	1990-10-10
3	Jone	1960-11-11

Course:

ID	COURSE_NAME
1	Science
2	Math
3	English
4	Music

Score:

STUDENT_ID	COURSE_ID	Score
1	1	70
1	2	50
1	3	64
2	1	10
2	2	32
2	4	15
3	3	99

맵핑 클래스

Student 매핑

package org.onecellboy.db.hibernate.table;

import java.util.Date;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.Table;

@Entity

@Table(name="Student")

public class Student {

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)

@Column(name="ID",columnDefinition="INT")

private int id;

@Column(name="NAME",columnDefinition="VARCHAR(45)")

private String name;

@Column(name="BIRTHDAY",columnDefinition="DATE")

private Date birthday;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday = birthday;

}

Course 맵핑

package org.onecellboy.db.hibernate.table;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.Table;

@Entity

@Table(name="Course")

public class Course {

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)

@Column(name="ID",columnDefinition="INT")

private int id;

@Column(name="COURSE_NAME",columnDefinition="VARCHAR(45)")

private String name;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

Score 맵핑

package org.onecellboy.db.hibernate.table;

import java.io.Serializable;

import javax.persistence.AttributeOverride;

import javax.persistence.AttributeOverrides;

import javax.persistence.Column;

import javax.persistence.Embeddable;

import javax.persistence.EmbeddedId;

import javax.persistence.Entity;

import javax.persistence.Table;

import org.onecellboy.db.hibernate.table.Apartment_Embeddable.APT_PK;

@Entity

@Table(name="Score")

public class Score {

@Embeddable

public static class Score_PK implements Serializable{

private static final long serialVersionUID = 1L;

protected int student_id;

protected int course_id;

public Score_PK() {

// TODO Auto-generated constructor stub

}

public Score_PK(int student_id, int course_id)

{

this.student_id = student_id;

this.course_id = course_id;

}

public int getStudent_id() {

return student_id;

}

public void setStudent_id(int student_id) {

this.student_id = student_id;

}

public int getCourse_id() {

return course_id;

}

public void setCourse_id(int course_id) {

this.course_id = course_id;

}

@Override

public boolean equals(Object o) {

return ((o instanceof Score_PK) && student_id == ((Score_PK)o).getStudent_id() && course_id == ((Score_PK) o).getCourse_id());

}

@Override

public int hashCode() {

return (int)(student_id ^ course_id);

}

@EmbeddedId

@AttributeOverrides(value = {

@AttributeOverride(name="student_id", column=@Column(name="STUDENT_ID",columnDefinition="INT")),

@AttributeOverride(name="course_id", column=@Column(name="COURSE_ID",columnDefinition="INT"))

})

private Score_PK id;

@Column(name="SCORE",columnDefinition="INT")

private int score;

public Score_PK getId() {

return id;

}

public void setId(Score_PK id) {

this.id = id;

}

public int getScore() {

return score;

}

public void setScore(int score) {

this.score = score;

}

Score의 맵핑 클래스의 경우 복합키를 사용한다.

HQL SELECT 예제

HQL은 위에서 말한 것 처럼 실제 테이블명과 컬럼명이 아니라 맵핑 클래스의 클래스명과 멤버변수를 써야한다.

HQL은 맵핑정보를 보고 SQL로 자동 전환되고 그 결과도 맵핑 클래스로 자동 할당된다.

기본 Select

query = session.createQuery("FROM Student");

resultList = query.getResultList();

for(Student st : (List<Student>)resultList)

{

System.out.println("-----------------");

System.out.println("id="+st.getId());

System.out.println("name="+st.getName());

System.out.println("birthday="+st.getBirthday());

}

Hibernate: select student0_.ID as ID1_10_, student0_.BIRTHDAY as BIRTHDAY2_10_, student0_.NAME as NAME3_10_ from Student student0_

-----------------

id=1

name=Mark

birthday=1997-02-02 00:00:00.0

-----------------

id=2

name=Jane

birthday=1990-10-10 00:00:00.0

-----------------

id=3

name=Jone

birthday=1960-11-11 00:00:00.0

조건 Select

query = session.createQuery("FROM Student Where id=:id");

query.setParameter("id", 1);

resultList = query.getResultList();

for(Student st : (List<Student>)resultList)

{

System.out.println("-----------------");

System.out.println("id="+st.getId());

System.out.println("name="+st.getName());

System.out.println("birthday="+st.getBirthday());

}

setParameter()를 통해 인자를 설정할 수 있다. 이 방법이 안전하다. 위의 예제는 ':id' 가 인자이다.

query.setParameter("id", 1); 은 ':id' 대신 숫자 1을 넣는다. 만약 문자열이었다면 "문자열"이 설정되었을 것이다.

명시적으로 반환 테이블 지정

query = session.createQuery("SELECT sdt FROM Student sdt");

resultList = query.getResultList();

for(Student st : (List<Student>)resultList)

{

System.out.println("-----------------");

System.out.println("id="+st.getId());

System.out.println("name="+st.getName());

System.out.println("birthday="+st.getBirthday());

}

Hibernate: select student0_.ID as ID1_10_, student0_.BIRTHDAY as BIRTHDAY2_10_, student0_.NAME as NAME3_10_ from Student student0_

-----------------

id=1

name=Mark

birthday=1997-02-02 00:00:00.0

-----------------

id=2

name=Jane

birthday=1990-10-10 00:00:00.0

-----------------

id=3

name=Jone

birthday=1960-11-11 00:00:00.0

여러 테이블 Join과 그 결과 클래스로 맵핑하기

SELECT 에 보면 테이블의 alias 값을 넣었다. 이러면 해당 테이블 전체를 말하며 맵핑 클래스로 값이 할당되어 클래스 자체가 반환된다.

query = session.createQuery("SELECT sdt,sc,cr FROM Student sdt INNER JOIN Score sc ON sdt.id = sc.id.student_id INNER JOIN Course cr ON sc.id.course_id = cr.id ");

resultList = query.getResultList();

for(Object[] values : (List<Object[]>)resultList)

{

System.out.println("--------types---------");

for(Object o : values)

{

System.out.println("type = "+o.getClass().getTypeName());

}

for(Object[] values : (List<Object[]>)resultList)

{

Student student = (Student)values[0];

Score score = (Score)values[1];

Course course = (Course)values[2];

System.out.print("name="+student.getName());

System.out.print(" course="+course.getName());

System.out.print(" score="+score.getScore());

System.out.println();

}

Hibernate: select student0_.ID as ID1_10_0_, score1_.COURSE_ID as COURSE_I1_9_1_, score1_.STUDENT_ID as STUDENT_2_9_1_, course2_.ID as ID1_3_2_, student0_.BIRTHDAY as BIRTHDAY2_10_0_, student0_.NAME as NAME3_10_0_, score1_.SCORE as SCORE3_9_1_, course2_.COURSE_NAME as COURSE_N2_3_2_ from Student student0_ inner join Score score1_ on (student0_.ID=score1_.STUDENT_ID) inner join Course course2_ on (score1_.COURSE_ID=course2_.ID)

--------types---------

type = org.onecellboy.db.hibernate.table.Student

type = org.onecellboy.db.hibernate.table.Score