Hibernate连表查询

未分类 发表评论

1.整体架构

 

 

2.所需要的jar包

 

 

3.hibernate.cfg.xml文件

 

<?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>

        <!– Database connection settings –>
        <property name=”connection.driver_class”>oracle.jdbc.OracleDriver</property>
        <property name=”connection.url”>jdbc:oracle:thin:@localhost:1521:orcl</property>
        <property name=”connection.username”>jack</property>
        <property name=”connection.password”>8888</property>

      <property name=”hibernate.dialect”>org.hibernate.dialect.Oracle10gDialect</property>
<property name=”show_sql”>true</property>
<property name=”format_sql”>true</property>

<mapping resource=”com/entity/Emp.hbm.xml”/>
<mapping resource=”com/entity/Dept.hbm.xml”/>
    </session-factory>

 

</hibernate-configuration>

 

4.实体类Emp和Dept

 

Emp实体类

package com.entity;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name=”emp”)
public class Emp {

private Integer empid;
private String ename;
private String job;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptid;

public Emp() {
super();
}

public Emp(String ename, Date hiredate, Double sal) {
super();
this.ename = ename;
this.hiredate = hiredate;
this.sal = sal;
}

public Emp(String ename, String job, Date hiredate, Double sal, Double comm, Integer deptid) {
super();
this.ename = ename;
this.job = job;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptid = deptid;
}

public Emp(Integer empid, String ename, String job, Date hiredate, Double sal, Double comm, Integer deptid) {
super();
this.empid = empid;
this.ename = ename;
this.job = job;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptid = deptid;
}

@Override
public String toString() {
return “Emp [empid=” + empid + “, ename=” + ename + “, job=” + job + “, hiredate=” + hiredate + “, sal=” + sal
+ “, comm=” + comm + “, deptid=” + deptid + “]”;
}

@Id
@GeneratedValue
public Integer getEmpid() {
return empid;
}

public void setEmpid(Integer empid) {
this.empid = empid;
}

@Column(name=”ename”)
public String getEname() {
return ename;
}

public void setEname(String ename) {
this.ename = ename;
}

@Column(name=”job”)
public String getJob() {
return job;
}

public void setJob(String job) {
this.job = job;
}

@Column(name=”hiredate”)
public Date getHiredate() {
return hiredate;
}

public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}

@Column(name=”sal”)
public Double getSal() {
return sal;
}

public void setSal(Double sal) {
this.sal = sal;
}

@Column(name=”comm”)
public Double getComm() {
return comm;
}

public void setComm(Double comm) {
this.comm = comm;
}

@Column(name=”deptid”)
public Integer getDeptid() {
return deptid;
}

public void setDeptid(Integer deptid) {
this.deptid = deptid;
}

}

Dept实体类

package com.entity;

import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name=”dept”)
public class Dept {
private Integer deptid;
private String loc;
private String dname;

public Dept() {
super();
}

public Dept(Integer deptid, String loc, String dname, List<Emp> emps) {
super();
this.deptid = deptid;
this.loc = loc;
this.dname = dname;
}

public Dept(Integer deptid, String loc, String dname) {
super();
this.deptid = deptid;
this.loc = loc;
this.dname = dname;
}

@Override
public String toString() {
return “Dept [deptid=” + deptid + “, loc=” + loc + “, dname=” + dname + “]”;
}

@Id
@GeneratedValue
public Integer getDeptid() {
return deptid;
}

public void setDeptid(Integer deptid) {
this.deptid = deptid;
}

@Column(name=”loc”)
public String getLoc() {
return loc;
}

public void setLoc(String loc) {
this.loc = loc;
}

@Column(name=”dname”)
public String getDname() {
return dname;
}

public void setDname(String dname) {
this.dname = dname;
}

 

}

Emp实体类对应的Emp.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”>
<!– package指定类所在的包名 –>
<hibernate-mapping package=”com.entity”>

<!– class针对类做的属性名与列名的映射关系 –>
    <class name=”Emp” table=”emp”>
    <!– id主键映射 –>
        <id name=”empid” column=”empid”>
        <!– generator 主键生成策略 –>
            <generator class=”sequence”>
            <!– 指定生成主键值的序列名 –>
            <param name=”sequence_name”>seq_emp</param>
            </generator>
        </id>
        
        <!– 非主键映射 –>
        <property name=”ename” column=”ename”/>
        <property name=”job” column=”job”/>
        <property name=”hiredate” column=”hiredate”/>
        <property name=”sal” column=”sal”/>
        <property name=”comm” column=”comm”/>
        <property name=”deptid” column=”deptid”/>
    </class>

 

</hibernate-mapping>

Dept实体类对应的Dept.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”>
<!– package指定类所在的包名 –>
<hibernate-mapping package=”com.entity”>

<!– class针对类做的属性名与列名的映射关系 –>
    <class name=”Dept” table=”dept”>
    <!– id主键映射 –>
        <id name=”deptid” column=”deptid”>
        <!– generator 主键生成策略 –>
            <generator class=”sequence”>
            <!– 指定生成主键值的序列名 –>
            <param name=”sequence_name”>seq_dept</param>
            </generator>
        </id>
        
        <!– 非主键映射 –>
        <property name=”loc” column=”loc”/>
        <property name=”dname” column=”dname”/>
    </class>

 

</hibernate-mapping>

Dept实体类对应的Dept.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”>
<!– package指定类所在的包名 –>
<hibernate-mapping package=”com.entity”>

<!– class针对类做的属性名与列名的映射关系 –>
    <class name=”Dept” table=”dept”>
    <!– id主键映射 –>
        <id name=”deptid” column=”deptid”>
        <!– generator 主键生成策略 –>
            <generator class=”sequence”>
            <!– 指定生成主键值的序列名 –>
            <param name=”sequence_name”>seq_dept</param>
            </generator>
        </id>
        
        <!– 非主键映射 –>
        <property name=”loc” column=”loc”/>
        <property name=”dname” column=”dname”/>
    </class>

 

</hibernate-mapping>

 

5.测试hql查询语句,实现查询结果

 

@Test
public void testUnion() {

String hql = “select t1.ename,t1.hiredate,t2.dname from Emp as t1,Dept as t2 where t1.deptid=t2.deptid”;

List<Object> emps = session.createQuery(hql).list();    
for (int i = 0; i < emps.size(); i++) {    
    Object[] obj = (Object[])emps.get(i);    
    System.out.println(obj[0]+”—“+obj[1]+”—“+obj[2]);   
}

}

6.Emp表与Dept表

–删除员工表
DROP TABLE emp;
DROP TABLE dept;

–删除序列
DROP SEQUENCE seq_emp;
DROP SEQUENCE seq_dept;

–创建序列
CREATE SEQUENCE seq_emp;
CREATE SEQUENCE seq_dept;

–创建部门表
CREATE TABLE dept(
       deptid NUMBER PRIMARY KEY NOT NULL,
       dname nvarchar2(20) NOT NULL,
       loc NVARCHAR2(20)
);
INSERT INTO dept VALUES(seq_dept.nextval,’开发部’,’北京’);
INSERT INTO dept VALUES(seq_dept.nextval,’市场部’,’上海’);
INSERT INTO dept VALUES(seq_dept.nextval,’人事部’,’广州’);
INSERT INTO dept VALUES(seq_dept.nextval,’财务部’,’深圳’);

–创建员工表
CREATE TABLE emp(

       empid NUMBER PRIMARY KEY NOT NULL,
       ename nvarchar2(20) NOT NULL,
       job NVARCHAR2(20) NOT NULL,
       hiredate DATE NOT NULL,
       sal NUMBER(10,2) NOT NULL,
       comm NUMBER(10,2),
       deptid NUMBER REFERENCES dept(deptid)  NOT NULL

);
–插入数据
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华1′,’程序员’,DATE’2018-02-09′,8888.88,1999.99,5);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华2′,’程序员’,DATE’2017-03-09′,7888.88,2999.99,5);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华3′,’人事专员’,DATE’2016-04-09′,6888.88,3999.99,2);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华4′,’人事专员’,DATE’2015-05-09′,5888.88,4999.99,2);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华5′,’会计师’,DATE’2014-06-09′,4888.88,5999.99,3);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华6′,’会计师’,DATE’2013-07-09′,5888.88,6999.99,3);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华7′,’销售专员’,DATE’2012-08-09′,6888.88,7999.99,4);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华8′,’销售专员’,DATE’2011-09-09′,7888.88,8999.99,4);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华9′,’人事专员’,DATE’2015-05-09′,5888.88,4999.99,2);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华10′,’会计师’,DATE’2014-06-09′,4888.88,5999.99,3);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华11′,’会计师’,DATE’2013-07-09′,5888.88,6999.99,3);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华12′,’销售专员’,DATE’2012-08-09′,6888.88,7999.99,4);
INSERT INTO emp VALUES(seq_emp.nextval,’刘德华13′,’销售专员’,DATE’2011-09-09′,7888.88,8999.99,4);

–提交
COMMIT;
SELECT * FROM emp;

SELECT * FROM dept;

7.查询结果

 

Hibernate: 
    select
        emp0_.ename as col_0_0_,
        emp0_.hiredate as col_1_0_,
        dept1_.dname as col_2_0_ 
    from
        emp emp0_ cross 
    join
        dept dept1_ 
    where

        emp0_.deptid=dept1_.deptid

 

刘德华14—2018-04-10 15:28:46.0—人事部
刘德华15—2018-04-10 15:29:31.0—人事部
刘德华1—2018-02-09 00:00:00.0—财务部
刘德华2—2017-03-09 00:00:00.0—财务部
刘德华3—2016-04-09 00:00:00.0—开发部
刘德华4—2015-05-09 00:00:00.0—开发部
刘德华5—2014-06-09 00:00:00.0—市场部
刘德华6—2013-07-09 00:00:00.0—市场部
刘德华7—2012-08-09 00:00:00.0—人事部
刘德华8—2011-09-09 00:00:00.0—人事部
刘德华9—2015-05-09 00:00:00.0—开发部
刘德华10—2014-06-09 00:00:00.0—市场部
刘德华15—2018-04-10 15:30:19.0—人事部
刘德华12—2012-08-09 00:00:00.0—人事部
jack—2018-04-10 12:52:57.0—开发部

发表评论

电子邮件地址不会被公开。 必填项已用*标注

昵称 *