------------吾亦無他,唯手熟爾,謙卑若愚,好學若饑-------------
本篇部落格将會解釋Hibernate中的連接配接查詢(各種join)
一,目錄
1.内連結
1.1顯式内連接配接(inner join)
1.1迫切顯式内連接配接(inner join fetch)
2.外連接配接
2.1左外連接配接(left join)
2.2迫切左外連接配接(left join fetch)
2.3以及為什麼我們在hibernate中盡量避免使用右外連接配接,具體的解釋
二,準備工作
1.資料庫腳本的引用,我把測試需要的資料庫的腳本給兄弟們傳一下
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUWYxYmZhR2MiNjY3QGMjFGZlNTOzUWZjJGO2YzM1MWMfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.gif)
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptId` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`location` varchar(255) DEFAULT NULL,
PRIMARY KEY (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `dept` */
insert into `dept`(`deptId`,`name`,`location`) values (1,'xx部','1樓'),(2,'研發部','2樓'),(3,'銷售部','3樓');
/*Table structure for table `emp` */
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empId` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`sal` double DEFAULT NULL,
`job` varchar(255) DEFAULT NULL,
`deptNo` int(11) DEFAULT NULL,
PRIMARY KEY (`empId`),
KEY `FK110A81763AD08` (`deptNo`),
CONSTRAINT `FK110A81763AD08` FOREIGN KEY (`deptNo`) REFERENCES `dept` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `emp` */
insert into `emp`(`empId`,`name`,`sal`,`job`,`deptNo`) values (1,'a',10000,'财務猿1',1),(2,'Ab',5000,'财務猿2',1),(3,'bAa',6000,'财務猿3',1),(4,'aaA',100000,'程式猿1',2),(5,'aB',50000,'程式猿2',2),(6,'AC',60000,'程式猿3',2),(7,'AD',5000000,'Boss',NULL);
連接配接查詢需要使用到的sql腳本
2.實體類的建立:值得注意的是toString()需要做手腳,否者就無限遞歸了
2.1Dept(部門的實體類)
package cn.dawn.day05.entity;
import java.util.HashSet;
import java.util.Set;
/**
* Created by Dawn on 2018/6/4.
*/
public class Dept {
private Integer deptId;
private String name;
private String location;
/*一個部門對應多個員工*/
private Set<Emp> emps=new HashSet<Emp>();
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}
public Set<Emp> getEmps() {
return emps;
}
public void setEmps(Set<Emp> emps) {
this.emps = emps;
}
@Override
public String toString() {
return "Dept{" +
"deptId=" + deptId +
", name='" + name + '\'' +
", location='" + location + '\'' +
'}';
}
}
2.2Emp(員工實體類)
package cn.dawn.day05.entity;
/**
* Created by Dawn on 2018/6/4.
*/
public class Emp {
private Integer empId;
private String name;
private String job;
private Double sal;
//多個員工屬于一個部門
private Dept dept;
public Integer getEmpId() {
return empId;
}
public void setEmpId(Integer empId) {
this.empId = empId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Emp{" +
"empId=" + empId +
", name='" + name + '\'' +
", job='" + job + '\'' +
", sal=" + sal +
", dept=" + dept +
'}';
}
}
3.hbm.xml小配置的配置
3.1Dept.hbm.xml的配置
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="cn.dawn.day05.entity">
<!--如果上面指定package的話,class的name就不必寫全類名-->
<!--lazy:是否懶加載(延遲加載) 預設值是true,延遲加載-->
<!--<class name="Teacher">-->
<!--直接加載-->
<class name="Dept" lazy="false">
<!--主鍵-->
<id name="deptId" column="deptId">
<!--主鍵生成策咯 assigned程式員自己建立-->
<!--identity是mysql裡的自增,一會做增加操作不必再給主鍵指派-->
<!--increment是先查最大的主鍵列,在下一條給主鍵加一-->
<!--sequence是oracle的主鍵生成策咯,他一會需要指定序列名字<param name="sequence">序列名</param>-->
<generator class="assigned"></generator>
</id>
<property name="name"/>
<property name="location"/>
<!--table指的是中間表,承載關聯關系的表-->
<set name="emps" cascade="all" inverse="true">
<key>
<!--本類表的id-->
<column name="deptNo"/>
</key>
<!--另外與他有多對多關聯的實體類-->
<one-to-many class="Emp"/>
</set>
</class>
</hibernate-mapping>
3.2Emp.hbm.xml小配置的配置
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="cn.dawn.day05.entity">
<!--如果上面指定package的話,class的name就不必寫全類名-->
<!--lazy:是否懶加載(延遲加載) 預設值是true,延遲加載-->
<!--<class name="Teacher">-->
<!--直接加載-->
<class name="Emp" lazy="false">
<!--主鍵-->
<id name="empId" column="empId">
<!--主鍵生成策咯 assigned程式員自己建立-->
<!--identity是mysql裡的自增,一會做增加操作不必再給主鍵指派-->
<!--increment是先查最大的主鍵列,在下一條給主鍵加一-->
<!--sequence是oracle的主鍵生成策咯,他一會需要指定序列名字<param name="sequence">序列名</param>-->
<generator class="assigned"></generator>
</id>
<property name="name"/>
<property name="sal"/>
<property name="job"/>
<!--table指的是中間表,承載關聯關系的表-->
<!--另外與他有多對多關聯的實體類-->
<many-to-one name="dept" column="deptNo" class="Dept"/>
</class>
</hibernate-mapping>
4.hibernate.cfg.xml這個Hibernate核心配置的配置
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!--jdbc連接配接四要素-->
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql:///y2167</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password"></property>
<!--在控制台上展示sql-->
<property name="show_sql">true</property>
<!--格式化sql-->
<!--<property name="format_sql">true</property>-->
<!--====================================================================-->
<!--ddl操作生成策咯,每次運作都在原有表上修改,沒有的話就建立表-->
<!--
<property name="hbm2ddl.auto"></property>
key - hbm2ddl.auto:自動生成表結構政策
value - update(使用最多):當資料庫不存在表時,hibernate啟動後會自動生成表結構。
當資料庫表存在時,如果一樣,則隻會寫入資料,不會改變表結構。
當資料庫表存在時,如果不一樣,則會修改表結構,原有的表結構不會改變。
create(很少):無論表結構是否存在,hibernate啟動後都會重新生成表結構。(造成之前的資料丢失)
create-drop(極少):無論表結構是否存在,hibernate啟動都會重新生成表結構。并且hibernate關閉後,表結構會被删除。來無影去無蹤。
validate(很少):不會建立表結構,不會修改表結構。校驗與資料庫中的表結構是否一樣,如果不一樣則報異常。
-->
<property name="hbm2ddl.auto">update</property>
<!--使用getCurrentSession()需要配置此标簽-->
<property name="current_session_context_class">thread</property>
<!--與小配置檔案映射-->
<mapping resource="cn/dawn/day05/dao/Dept.hbm.xml"></mapping>
<mapping resource="cn/dawn/day05/dao/Emp.hbm.xml"></mapping>
</session-factory>
</hibernate-configuration>
三,顯式内連接配接(inner join)
@Test
/*内連接配接*/
/*傳回的是Object[]數組,隻有6條資料*/
public void t1innerjoin(){
String hql="from Emp e inner join e.dept";
List<Object[]> list = session.createQuery(hql).list();
for(Object[] o:list){
System.out.println(o[0]);//Emp對象
System.out.println(o[1]);//Dept對象
}
/*
Hibernate: alter table Emp add constraint FK42q0wt6tw3e1tcygsbj6pexh foreign key (deptNo) references Dept (deptId)
Hibernate: select emp0_.empId as empId1_1_0_, dept1_.deptId as deptId1_0_1_, emp0_.name as name2_1_0_, emp0_.sal as sal3_1_0_, emp0_.job as job4_1_0_, emp0_.deptNo as deptNo5_1_0_, dept1_.name as name2_0_1_, dept1_.location as location3_0_1_ from Emp emp0_ inner join Dept dept1_ on emp0_.deptNo=dept1_.deptId
Emp{empId=1, name='a', job='财務猿1', sal=10000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Dept{deptId=1, name='xx部', location='1樓'}
Emp{empId=2, name='Ab', job='财務猿2', sal=5000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Dept{deptId=1, name='xx部', location='1樓'}
Emp{empId=3, name='bAa', job='财務猿3', sal=6000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Dept{deptId=1, name='xx部', location='1樓'}
Emp{empId=4, name='aaA', job='程式猿1', sal=100000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Dept{deptId=2, name='研發部', location='2樓'}
Emp{empId=5, name='aB', job='程式猿2', sal=50000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Dept{deptId=2, name='研發部', location='2樓'}
Emp{empId=6, name='AC', job='程式猿3', sal=60000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Dept{deptId=2, name='研發部', location='2樓'}
* */
}
四,迫切顯式内連接配接(inner join fetch)
@Test
/*迫切内連接配接*/
/*傳回的是Object,隻有6條資料*/
public void t2innerjoinFetch(){
String hql="from Emp e inner join fetch e.dept";
List<Emp> list = session.createQuery(hql).list();
for(Emp e:list){
System.out.println(e);//Emp對象
}
/*
Hibernate: select emp0_.empId as empId1_1_0_, dept1_.deptId as deptId1_0_1_, emp0_.name as name2_1_0_, emp0_.sal as sal3_1_0_, emp0_.job as job4_1_0_, emp0_.deptNo as deptNo5_1_0_, dept1_.name as name2_0_1_, dept1_.location as location3_0_1_ from Emp emp0_ inner join Dept dept1_ on emp0_.deptNo=dept1_.deptId
Emp{empId=1, name='a', job='财務猿1', sal=10000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Emp{empId=2, name='Ab', job='财務猿2', sal=5000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Emp{empId=3, name='bAa', job='财務猿3', sal=6000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Emp{empId=4, name='aaA', job='程式猿1', sal=100000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Emp{empId=5, name='aB', job='程式猿2', sal=50000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Emp{empId=6, name='AC', job='程式猿3', sal=60000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
* */
}
五,左外連接配接(left join)
@Test
/*左外連接配接*/
/*傳回的是Object[]數組,有7條資料,多出來一條,以Emp表為準*/
public void t3leftjoin(){
String hql="from Emp e left join e.dept";
List<Object[]> list = session.createQuery(hql).list();
for(Object[] o:list){
System.out.println(o[0]);//Emp對象
System.out.println(o[1]);//Dept對象
}
/*
Hibernate: select emp0_.empId as empId1_1_0_, dept1_.deptId as deptId1_0_1_, emp0_.name as name2_1_0_, emp0_.sal as sal3_1_0_, emp0_.job as job4_1_0_, emp0_.deptNo as deptNo5_1_0_, dept1_.name as name2_0_1_, dept1_.location as location3_0_1_ from Emp emp0_ left outer join Dept dept1_ on emp0_.deptNo=dept1_.deptId
Emp{empId=1, name='a', job='财務猿1', sal=10000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Dept{deptId=1, name='xx部', location='1樓'}
Emp{empId=2, name='Ab', job='财務猿2', sal=5000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Dept{deptId=1, name='xx部', location='1樓'}
Emp{empId=3, name='bAa', job='财務猿3', sal=6000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Dept{deptId=1, name='xx部', location='1樓'}
Emp{empId=4, name='aaA', job='程式猿1', sal=100000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Dept{deptId=2, name='研發部', location='2樓'}
Emp{empId=5, name='aB', job='程式猿2', sal=50000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Dept{deptId=2, name='研發部', location='2樓'}
Emp{empId=6, name='AC', job='程式猿3', sal=60000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Dept{deptId=2, name='研發部', location='2樓'}
Emp{empId=7, name='AD', job='Boss', sal=5000000.0, dept=null}
null
* */
}
六,迫切左外連接配接(left join fetch)
@Test
/*迫切左外連接配接*/
/*傳回的是Object,有7條資料*/
public void t4leftjoinFetch(){
String hql="from Emp e left join fetch e.dept";
List<Emp> list = session.createQuery(hql).list();
for(Emp e:list){
System.out.println(e);//Emp對象
}
/*
Hibernate: select emp0_.empId as empId1_1_0_, dept1_.deptId as deptId1_0_1_, emp0_.name as name2_1_0_, emp0_.sal as sal3_1_0_, emp0_.job as job4_1_0_, emp0_.deptNo as deptNo5_1_0_, dept1_.name as name2_0_1_, dept1_.location as location3_0_1_ from Emp emp0_ left outer join Dept dept1_ on emp0_.deptNo=dept1_.deptId
Emp{empId=1, name='a', job='财務猿1', sal=10000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Emp{empId=2, name='Ab', job='财務猿2', sal=5000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Emp{empId=3, name='bAa', job='财務猿3', sal=6000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Emp{empId=4, name='aaA', job='程式猿1', sal=100000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Emp{empId=5, name='aB', job='程式猿2', sal=50000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Emp{empId=6, name='AC', job='程式猿3', sal=60000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Emp{empId=7, name='AD', job='Boss', sal=5000000.0, dept=null}
* */
}
七,以及為什麼我們在hibernate中盡量避免使用右外連接配接,具體的解釋
以右表為準,傳回的左表對象,如果右表有一個null值做關聯,那麼左表的對象就是null,如果把這個對象直接通路其屬性,就會引發空指針異常
是以,我們一般不會使用右外連接配接
@Test
/*迫切右外連接配接*/
/*傳回的是Object,*/
/*hibernate中一般不使用他,不是不能用,而是特别容易出現空指針的異常*/
/*注意觀察最後一行查出來的資料,如果我用通路他的屬性,這就很難受了*/
/*public void t5rightjoinFetch(){
String hql="from Emp e right join fetch e.dept";
List<Emp> list = session.createQuery(hql).list();
for(Emp e:list){
System.out.println(e);//Emp對象
}
*//*
Hibernate: select emp0_.empId as empId1_1_0_, dept1_.deptId as deptId1_0_1_, emp0_.name as name2_1_0_, emp0_.sal as sal3_1_0_, emp0_.job as job4_1_0_, emp0_.deptNo as deptNo5_1_0_, dept1_.name as name2_0_1_, dept1_.location as location3_0_1_ from Emp emp0_ right outer join Dept dept1_ on emp0_.deptNo=dept1_.deptId
Emp{empId=1, name='a', job='财務猿1', sal=10000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Emp{empId=2, name='Ab', job='财務猿2', sal=5000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Emp{empId=3, name='bAa', job='财務猿3', sal=6000.0, dept=Dept{deptId=1, name='xx部', location='1樓'}}
Emp{empId=4, name='aaA', job='程式猿1', sal=100000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Emp{empId=5, name='aB', job='程式猿2', sal=50000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
Emp{empId=6, name='AC', job='程式猿3', sal=60000.0, dept=Dept{deptId=2, name='研發部', location='2樓'}}
null
* *//*
}*/