# hibernate 2/21 觀念
###### tags: `hibernate`
### 114 Hibernate的關聯
看需求選擇單向or雙向
一個購物車唯一個物件 購物車內的東東唯數量
多方太多盡量不要用雙向
多對多 [中介表格](https://openhome.cc/Gossip/HibernateGossip/ManyToMany.html)
[關聯介紹](https://notes.andywu.tw/2018/%E8%B3%87%E6%96%99%E5%BA%AB-%E9%97%9C%E8%81%AF%E4%BB%8B%E7%B4%B9-%E4%B8%80%E5%B0%8D%E4%B8%80%E3%80%81%E4%B8%80%E5%B0%8D%E5%A4%9A%E3%80%81%E5%A4%9A%E5%B0%8D%E5%A4%9A/)
[關聯補充](https://hackmd.io/@OceanChiu/ryM5xipxI)
### 115 單向一對一
老師口訣:校長找到學校(物件導向的觀點 校長物件內有學校變數物件)
放入檔案
### 01 實作邏輯講解
校長找的到學校 學校沒有定義校長
老師口訣:一對一 一中有個一
* 校長有對應學校的主鍵(外鍵)
```clikc=
@OneToOne(cascade=CascadeType.PERSIST)//如果用這個
session.persist(p1);
session.persist(p2);
@OneToOne(cascade=CascadeType.ALL)
session.save(p1);
session.save(p2);
```
先存沒有外鍵的再存有外鍵的
---
sql f7
---
校長的外鍵要=學校的主鍵
#### sql
#### 01 Merge
#### 01 Delete
1. 校長表格有定義學校表格(學校表格會不能刪)
2. 有連動性不能刪除
3. 校長會找不道對應的表格
4. 先刪校長表格在刪學校表格
---
如果沒有編寫cascade= { CascadeType.REMOVE})，則只會刪除校長物件對應的紀錄。
#### 02 實作邏輯
一對一 兩邊其中一邊有外鍵就好 @JoinColumn
* **(重要)**
* mappedBy(本類別沒有外鍵資訊) = "school_p"(變數)
* PrincipalBi principal;(外鍵資訊)
---
#### 128.129 單向一對多
1. 一對多有單向雙向
2. 多對一也有單向雙向
* **FK**
* fk會出現在多方類別對應的表格
* @joinColumn 外鍵會出現在本註釋對應的表格裡面
#### 130 實作畫面單向一對多
唯一特例 單向一對多
* 單向的一對多 多方不能定義單向的物件參考
* 多方不能寫註釋
#### 雙向一對多
多方類別要定義一個一方的類別參考
一找到多 多找到一
一方不能寫任何註釋
* **外鍵**
* 外鍵永遠在多方的概念
* 一方有外鍵 雙方都能有關連
_p 讓他知道外鍵是誰
#### 單向多對一
* 口訣: 多對一，多方有個儲存ㄧ方物件參考實例變數，簡稱『多方有個一』
* 多能找到一 一不能找到多
---
sql
---
修改
* Console
先新增沒外鍵的 在新增外鍵的
```clike=
得到Session物件
Hibernate:
insert
into
Department_Table
(depName)
values
(?)
Hibernate:
insert
into
Employee_Table
(department_FK_126, employee_Id, empName)
values
(?, ?, ?)
Hibernate:
insert
into
Employee_Table
(department_FK_126, employee_Id, empName)
values
(?, ?, ?)
Hibernate:
insert
into
Department_Table
(depName)
values
(?)
Hibernate:
insert
into
Employee_Table
(department_FK_126, employee_Id, empName)
values
(?, ?, ?)
Hibernate:
insert
into
Employee_Table
(department_FK_126, employee_Id, empName)
values
(?, ?, ?)
Hibernate:
insert
into
Employee_Table
(department_FK_126, employee_Id, empName)
values
(?, ?, ?)
關閉SessionFactory
```
#### 多對多
* **joinColumn(s)**
* 對方可能是複合主鍵
* 
* 在Join Table中，儲存本類別之主鍵值的外鍵欄位名稱
* **inverseJoinColumn**
* 在Join Table中，儲存對應對照類別之主鍵值的外鍵欄位名稱
```clike=
Hibernate:
create table author_book (
FK_BOOK_ID_126_B int not null,
FK_AUTHOR_ID_126_A int not null,
primary key (FK_BOOK_ID_126_B, FK_AUTHOR_ID_126_A)//複合主鍵
)
Hibernate:
alter table author_book//中介表格
add constraint FK5f5qmsmijrwrtcjrodipp6gm7//這個東東 是Hibernate算出來的外鍵名稱
foreign key (FK_AUTHOR_ID_126_A)//對照作者表格
references Author_M2M_01_ANNO
```
---
#### sql
* 中介表格放兩邊的主鍵
#### 162 Hibernate Query Language
* 查詢出來的物件 通通都是永續物件
#### 163 Query介面
#### 164 執行HQL的步驟
* setFirstResult
1. (分頁)
2. 0開頭
3. 編號
* setMaxResult
1. (最多幾筆)
2. 數量
#### 165 Qurey介面常用的方法
R=永續類別
### 實作Query介面
修改
#### 167 HQL查詢傳回值的型態
查詢標的為類別(效能最好)
元素是字串型態的陣列
#### 168 HQL的FROM子句
上班寫別名比較好(效能較好)
#### 169 HQL的SELECT子句
### 實作
* 合體
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_01 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
//兩種寫法
String hql = "FROM Employee AS e";
// String hql = "FROM Employee e";
// Object obj = new Object();
Transaction tx = null;
try {
tx = session.beginTransaction();
List<Employee>emps = session.createQuery(hql).getResultList();
for (Employee e :emps) {
System.out.println(e.getId()+","+e.getName()+","
+e.getSalary()+","+e.getBirthday());
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
public class HibernateQueryExercise126_02 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql = "SELECT e.name FROM Employee e";
Transaction tx = null;
try {
tx = session.beginTransaction();
List<String> names = session.createQuery(hql).getResultList();
for (String name : names) {
System.out.println(name);
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
public class HibernateQueryExercise126_03 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql = "SELECT e.salary FROM Employee AS e";
Transaction tx = null;
try {
tx = session.beginTransaction();
List<Integer> salaries = session.createQuery(hql).getResultList();
for (Integer salary : salaries) {
System.out.println(salary);
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
查詢多個
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
public class HibernateQueryExercise126_03 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql = "SELECT e.name, e.salary, e.birthday FROM Employee AS e";
Transaction tx = null;
try {
tx = session.beginTransaction();
List< Object[] > list = session.createQuery(hql).getResultList();//list裡面每個元素都是字串物件
for (Object[] objs : list) {
System.out.println(objs[0]+","+objs[1]+","+objs[2]);
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
#### HQL的WHERE子句
* 實作
* 
```click=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_04 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql = "FROM Employee e WHERE e.salary >= 30000 and e.name like '黃%' ";
Transaction tx = null;
try {
tx = session.beginTransaction();
//用操作物件來操作表格內的物件
List< Employee > emps = session.createQuery(hql).getResultList();//list裡面每個元素都是字串物件
for (Employee emp : emps) {
System.out.println(emp.getId()+","+emp.getName()+","+emp.getBirthday());
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
#### 172 HQL的ORDER BY子句
實作
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_05 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql = "FROM Employee e WHERE e.salary >= 30000 ORDER BY e.birthday DESC, e.salary ";//DESC由大到小//ASC由小到大
Transaction tx = null;
try {
tx = session.beginTransaction();
//用操作物件來操作表格內的物件
List< Employee > emps = session.createQuery(hql).getResultList();//list裡面每個元素都是字串物件
for (Employee emp : emps) {
System.out.println(emp.getId()+","+emp.getName()+","+emp.getBirthday());
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
[聚合函數](https://ithelp.ithome.com.tw/articles/10218055)
[補充](https://rileylin91.github.io/2020/06/12/MSSQL-13-AggregateFunctions/)
n個數字變成n個數字
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
public class HibernateQueryExercise126_06 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql = "SELECT SUM(e.salary), MIN(e.salary), MAX(e.birthday),count(*) FROM Employee e";//聚合函數
Transaction tx = null;
try {
tx = session.beginTransaction();
//用操作物件來操作表格內的物件
List< Object[] > list = session.createQuery(hql).getResultList();//list裡面每個元素都是字串物件
for (Object[] objs : list) {
System.out.println(objs[0]+","+objs[1]+","+objs[2]+","+objs[3]);
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
多個聚合分類查詢
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
public class HibernateQueryExercise126_07 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
//SELECT 只能用 GROUP BY e.depId 去分類
String hql = "SELECT e.depId, SUM(e.salary), MIN(e.salary), MAX(e.birthday),count(*) "
+ "FROM Employee e GROUP BY e.depId";//GROUP BY拿有重複地去比較(合理的)
Transaction tx = null;
try {
tx = session.beginTransaction();
//用操作物件來操作表格內的物件
List< Object[] > list = session.createQuery(hql).getResultList();//list裡面每個元素都是字串物件
for (Object[] objs : list) {
System.out.println(objs[0]+","+objs[1]+","+objs[2]+","+objs[3]+","+objs[4]);
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
---
如果多了e.name
#### 使用參數
加參數
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_08 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
//SELECT 只能用 GROUP BY e.depId 去分類
String hql = "FROM Employee AS e WHERE e.id = :empId";//參數
Transaction tx = null;
try {
tx = session.beginTransaction();
//用操作物件來操作表格內的物件
List< Employee > emps = session.createQuery(hql).setParameter("empId", 5).getResultList();
for (Employee e : emps) {
System.out.println(e.getId()+","+e.getName()+","+e.getSalary()+","+e.getBirthday());
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
查詢不同參數變化
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_09 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
//SELECT 只能用 GROUP BY e.depId 去分類
String hql = "FROM Employee AS e WHERE e.salary >= :empSalary";//參數
Transaction tx = null;
try {
tx = session.beginTransaction();
//用操作物件來操作表格內的物件
List< Employee > emps = session.createQuery(hql).setParameter("empSalary", 3000).getResultList();
for (Employee e : emps) {
System.out.println(e.getId()+","+e.getName()+","+e.getSalary()+","+e.getBirthday());
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
#### HQL不支援?
#### update子句
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_10 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
//SELECT 只能用 GROUP BY e.depId 去分類
String hql = "UPDATE Employee AS e SET e.salary = e.salary + :sal "
+ " WHERE e.id = :empId";//先更新
String hql1 = "FROM Employee AS e WHERE e.id = :empId";//更新結果
Transaction tx = null;
try {
tx = session.beginTransaction();
//用操作物件來操作表格內的物件
session.createQuery(hql)
.setParameter("sal", 4000)
.setParameter("empId", 5)
.executeUpdate();
List<Employee> emps = session.createQuery(hql1)
.setParameter("empId", 5)
.getResultList();
for (Employee e : emps) {
System.out.println(e.getId()+","+e.getName()+","+e.getSalary()+","+e.getBirthday());
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
#### 實作getSingleResult()傳回值單一物件
```clike=
package ch06;
import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_11 {
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql1 = "FROM Employee AS e WHERE e.id = :empId";//更新結果
Transaction tx = null;
Employee emp = null;
try {
tx = session.beginTransaction();
try {
emp = (Employee) session.createQuery(hql1)
.setParameter("empId", 1)
.getSingleResult();//傳回值單一物件
} catch (NoResultException e) {
System.out.println("查詢此筆紀錄");
}catch (NonUniqueResultException e) {
System.out.println("查出多筆紀錄");
}
if(emp != null) {
System.out.println(emp.getId()+","+emp.getName()+","+emp.getSalary()+","+emp.getBirthday());
}else {
System.out.println("查詢發生異常");
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
```clike=
package ch06;
import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_12 {
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql1 = "FROM Employee AS e WHERE e.depId = :did";//更新結果
Transaction tx = null;
Employee emp = null;
try {
tx = session.beginTransaction();
try {
emp = (Employee) session.createQuery(hql1)
.setParameter("did", 1)
.getSingleResult();//傳回值單一物件
} catch (NoResultException e) {
System.out.println("查詢此筆紀錄");
}catch (NonUniqueResultException e) {
System.out.println("查出多筆紀錄");
}
if(emp != null) {
System.out.println(emp.getId()+","+emp.getName()+","+emp.getSalary()+","+emp.getBirthday());
}else {
System.out.println("查詢發生異常");
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
#### getResultList();0筆或多筆
```
clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_13 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql1 = "FROM Employee AS e WHERE e.depId = :did";//更新結果
Transaction tx = null;
List<Employee> emps = null;
try {
tx = session.beginTransaction();
emps = session.createQuery(hql1)
.setParameter("did", 1)
.getResultList();//0筆或多筆
if(emps.size() > 0) {
for(Employee emp : emps) {
System.out.println(emp.getId()+","+emp.getName()+","+emp.getSalary()+","+emp.getBirthday());
}
}else {
System.out.println("查無此部門代號之員工");
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
簡化try
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_14 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql1 = "FROM Employee AS e WHERE e.id = :empId";//更新結果
Transaction tx = null;
List<Employee> emps = null;
try {
tx = session.beginTransaction();
emps = session.createQuery(hql1)
.setParameter("did", 2)
.getResultList();//0筆或多筆
if(emps.size() > 0) {
System.out.println("查詢之員工如下");
Employee emp = emps.get(0);
System.out.println(emp.getId()+","+emp.getName()+","+emp.getSalary()+","+emp.getBirthday());
}else {
System.out.println("查無此部門代號之員工");
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
#### 177 DELETE
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_15 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
//SELECT 只能用 GROUP BY e.depId 去分類
String hql = "DELETE FROM Employee e WHERE e.name = :empName ";
String hql1 = "FROM Employee ";
Transaction tx = null;
try {
tx = session.beginTransaction();
//用操作物件來操作表格內的物件
session.createQuery(hql)
.setParameter("empName", "黃明和")
.executeUpdate();
List<Employee> emps = session.createQuery(hql1)
.getResultList();
for (Employee e : emps) {
System.out.println(e.getId()+","+e.getName()+","+e.getSalary()+","+e.getBirthday());
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
#### 178 INSERT INTO
#### 179 分頁
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_16 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String hql = "FROM Employee AS e";
Transaction tx = null;
try {
tx = session.beginTransaction();
List<Employee>emps = session.createQuery(hql)
.setFirstResult(4)
.setMaxResults(2)
.getResultList();
for (Employee e :emps) {
System.out.println(e.getId()+","+e.getName()+","
+e.getSalary()+","+e.getBirthday());
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
#### Native Query
```clike=
package ch06;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import ch06.model.Employee;
public class HibernateQueryExercise126_17 {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
Session session = HibernateUtils.getSessionFactory().getCurrentSession();
String sql = "SELECT e.depId, e.name, e.birthday, e.salary FROM EMPLOYEE_Ch06 AS e";
Transaction tx = null;
try {
tx = session.beginTransaction();
List<Object[]>emps = session.createNativeQuery(sql)
.setFirstResult(0)
.setMaxResults(2)
.getResultList();
for (Object[] oa : emps) {
System.out.println(oa[0]+","+oa[1]+","+oa[2]+","+oa[3]);
}
tx.commit();
} catch(Exception e) {
e.printStackTrace();
if (tx != null) {
tx.rollback();
}
}
}
}
```
Sign in with Wallet
Connect another wallet