HQL、QBC、原生SQL、JDBC的使用

一、核心语句

1.HQL语句的使用

 1 package com.zhidi.test.relation;
 2 
 3 import java.util.List;
 4 
 5 import org.hibernate.Query;
 6 import org.junit.Test;
 7 
 8 import com.zdsofe.entity.one2many.Dept;
 9 import com.zdsofe.entity.one2many.Emp;
10 import com.zhidi.test.base.BaseTest;
11 
12 public class TestOne2Many extends BaseTest{
13 
14     /**
15      * HQL语句的使用
16      */
17     
18     @Test
19     public void testHQL()
20     {
21         //HQL语句  from后面是实体类名
22         String hql="from Dept";
23         Query query=session.createQuery(hql);
24         List<Dept> list=query.list();
25     }
26     
27     @Test
28     public void testHQl1()
29     {
30         //HQL语句
31         String hql="select loc,dname from Dept";
32         Query query=session.createQuery(hql);
33         //此处泛型为Object数组
34         List<Object[]> list=query.list();
35     }
36     
37     @Test
38     public void testHQL2()
39     {
40         //HQL语句
41         String hql="select new Dept(dname,loc) from Dept";
42         Query query=session.createQuery(hql);
43         List<Dept> list=query.list();
44     }
45     
46     // count(),min(),max(),avg(),sum(),length(),lower(),upper(),trim(),distinct(去除重复数据)
47     @Test
48     public void testHQL3()
49     {    
50          //fetch放在join后将关联对象立即抓取
51          String hql="from Dept d join fetch d.emps";
52          Query query=session.createQuery(hql);
53          List<Dept> list=query.list();
54     }
55     
56     @Test
57     public void testBindParam()
58     {
59         //占位符
60         String hql="from Emp where sal>? and empno>?";
61         Query query=session.createQuery(hql);
62         query.setDouble(0, 1000);
63         query.setInteger(1, 7938);
64         List<Emp> list=query.list();
65     }
66     
67     @Test
68     public void testBindParam1()
69     {
70         //动态参数
71         String hql="from Emp where sal>:psal and empno>:pno";
72         Query query=session.createQuery(hql);
73         query.setDouble("psal", 1000);
74         query.setInteger("pno", 7939);
75         List<Emp> list=query.list();
76     }
77     
78     @Test
79     public void testNameQuery()
80     {
81         //命名hql查询
82         Query query=session.getNamedQuery("findEmpById");
83         query.setParameter("id", 7939);
84         List<Emp> list=query.list();
85     }
86     
87     @Test
88     public void testPage()
89     {
90         //分页
91         String hql="from Emp";
92         Query query=session.createQuery(hql);
93         //设置开始的索引
94         query.setFirstResult(0);
95         //设置一次取出的最大记录数
96         query.setMaxResults(6);
97         List<Emp> list=query.list();
98     }
99 }

View Code

2.QBC的使用

  1 package com.zhidi.test.relation;
  2 
  3 import java.util.List;
  4 
  5 import org.hibernate.Criteria;
  6 import org.hibernate.FetchMode;
  7 import org.hibernate.criterion.Example;
  8 import org.hibernate.criterion.MatchMode;
  9 import org.hibernate.criterion.Order;
 10 import org.hibernate.criterion.Projection;
 11 import org.hibernate.criterion.ProjectionList;
 12 import org.hibernate.criterion.Projections;
 13 import org.hibernate.criterion.Restrictions;
 14 import org.junit.Test;
 15 
 16 import com.zdsofe.entity.one2many.Dept;
 17 import com.zdsofe.entity.one2many.Emp;
 18 import com.zhidi.test.base.BaseTest;
 19 
 20 public class TestQBC extends BaseTest {
 21     
 22     @Test
 23     public void testQBC()
 24     {
 25         //创建criteria对象
 26         Criteria criteria=session.createCriteria(Emp.class);
 27         //执行查询
 28         List<Emp> list=criteria.list();
 29     }
 30     
 31     @Test
 32     public void testQBC1()
 33     {
 34         //创建criteria对象
 35        Criteria criteria=session.createCriteria(Emp.class);
 36        //Restrictions类提供创建各种查询条件的方法
 37        //criteria.add(Restrictions.between("sal", 1000d, 3000d));
 38        //criteria.add(Restrictions.like("ename", "王",MatchMode.ANYWHERE));
 39        //criteria.add(Restrictions.eq("sal", 1000d));
 40        criteria.addOrder(Order.asc("sal"));       
 41        List<Emp> list=criteria.list();
 42     }
 43 
 44     @Test
 45     //样例查询
 46     public void testQBC2()
 47     {
 48         Emp emp=new Emp();
 49         emp.setEname("王麻子");
 50         emp.setSal(1000d);
 51         //以Emp对象为蓝本创建样例对象
 52         Example example=Example.create(emp);
 53         //设置样例中排除为空的属性
 54         example.excludeZeroes();
 55         //创建Criteria对象
 56         Criteria criteria=session.createCriteria(Emp.class);
 57         //将样例对象设置为查询条件
 58         criteria.add(example);
 59         List<Emp> list=criteria.list();
 60     }
 61     
 62     @Test
 63     public void testQBC3()
 64     {
 65         //创建Criteria对象
 66         Criteria criteria=session.createCriteria(Emp.class);
 67         //指定使用连接默认立即获得关联属性
 68         criteria.setFetchMode("dept", FetchMode.JOIN);
 69         List<Emp> list=criteria.list();
 70     }
 71     
 72     @Test
 73     public void testQBC4()
 74     {
 75         //创建Criteria对象
 76         Criteria criteria=session.createCriteria(Dept.class);
 77         //为关联对象起别名
 78         criteria.createAlias("emps", "e");
 79         //将关联对象的属性作为查询条件
 80         criteria.add(Restrictions.in("e.empno", new Object[]{3938,3942}));
 81         List<Emp> list=criteria.list();
 82         
 83     }
 84     
 85     @Test
 86     public void testQBC5()
 87     {
 88         //创建Criteria对象
 89         Criteria criteria=session.createCriteria(Dept.class);
 90         //统计总记录数
 91         criteria.setProjection(Projections.rowCount());
 92         Long total=(Long)criteria.uniqueResult();
 93     }
 94     
 95     @Test
 96     public void testQBC6()
 97     {
 98         //创建Criteria对象
 99         Criteria criteria=session.createCriteria(Dept.class);
100         //创建投影集合
101         ProjectionList projectionList=Projections.projectionList();
102         //向投影集合中添加聚合函数投影
103         projectionList.add(Projections.count("dname"));
104         //向投影集合中添加分组属性
105         projectionList.add(Projections.groupProperty("loc"));
106         //为Criteria设置投影
107         criteria.setProjection(projectionList);
108         List<Object[]> list=criteria.list();
109                 
110     }
111     
112     
113 }

View Code

3.原生SQL的使用

 1 package com.zhidi.test.relation;
 2 
 3 import java.util.List;
 4 import java.util.Map;
 5 
 6 import org.hibernate.SQLQuery;
 7 import org.hibernate.transform.Transformers;
 8 import org.junit.Test;
 9 
10 import com.zdsofe.entity.one2many.Emp;
11 import com.zhidi.test.base.BaseTest;
12 
13 public class TestSQL  extends BaseTest{
14     
15     @Test
16     public void testSQL()
17     {
18       SQLQuery sqlQuery=session.createSQLQuery("select * from emp");
19       //查询每一行数据封装到数组中
20       List<Object[]> list=sqlQuery.list();
21     }
22 
23     @Test
24     public void testSQL1()
25     {
26       SQLQuery sqlQuery=session.createSQLQuery("select * from emp");
27       //将查询结果转化为对象
28       sqlQuery.addEntity(Emp.class);
29       List<Emp> list=sqlQuery.list();
30     }
31     
32     @Test
33     public void testSQL2()
34     {
35       SQLQuery sqlQuery=session.createSQLQuery("select * from emp");
36       //将查询结果转化为map集合
37       sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
38       List<Map<Object,Object>> list=sqlQuery.list();
39     }
40 }

View Code

4.JDBC的使用

 1 package com.zhidi.test.relation;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.SQLException;
 6 
 7 import org.hibernate.jdbc.ReturningWork;
 8 import org.hibernate.jdbc.Work;
 9 import org.junit.Test;
10 
11 import com.zdsofe.entity.one2many.Emp;
12 import com.zhidi.test.base.BaseTest;
13 
14 public class TestJDBC extends BaseTest {
15     
16     @Test
17     public void testJDBC()
18     {
19         //执行不需要返回结果的数据库操作
20         session.doWork(new Work() {            
21             @Override
22             public void execute(Connection conn) throws SQLException {
23                        //在这里执行数据库的操作
24                 PreparedStatement ps=conn.prepareStatement("select * from emp");
25                        
26             }
27         });
28     }
29     
30 
31     @Test
32     public void testJDBC1()
33     {
34         //执行需要返回的数据库操作
35         session.doReturningWork(new ReturningWork<Emp>() {
36 
37             @Override
38             public Emp execute(Connection conn) throws SQLException {
39                 PreparedStatement ps=conn.prepareStatement("select * from emp");
40 
41                 return null;
42             }
43         });
44     }
45 }

View Code

二、配置文件

1.test文件

 1 package com.zhidi.test.base;
 2 
 3 import org.hibernate.Session;
 4 import org.hibernate.SessionFactory;
 5 import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
 6 import org.hibernate.cfg.Configuration;
 7 import org.junit.After;
 8 import org.junit.AfterClass;
 9 import org.junit.Before;
10 import org.junit.BeforeClass;
11 
12 public class BaseTest {
13     protected static SessionFactory sessionFactory;
14     protected Session session;
15 
16     @BeforeClass
17     public static void beforeClass()
18     {
19         Configuration cfg=new Configuration().configure();
20         sessionFactory=cfg.buildSessionFactory(new StandardServiceRegistryBuilder().applySettings(cfg.getProperties()).build());        
21     }
22     @AfterClass
23     public static void afterClass()
24     {
25         if(sessionFactory!=null)
26         {
27             sessionFactory.close();
28         }
29     }
30     @Before
31     public void before()
32     {
33         session=sessionFactory.getCurrentSession();
34         session.beginTransaction();        
35     }
36     
37     @After
38     public void after()
39     {
40         if(session.getTransaction().isActive())
41         {
42             session.getTransaction().commit();
43         }
44     }
45 }

View Code

2.两个实体类

package com.zdsofe.entity.one2many;

import java.util.HashSet;
import java.util.Set;

public class Dept {

    private Integer deptno;
    private String dname;
    private String loc;
    //关联
    private Set<Emp> emps=new HashSet<Emp>();
    
    public Dept() {
        
    }

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

    public Set<Emp> getEmps() {
        return emps;
    }

    public void setEmps(Set<Emp> emps) {
        this.emps = emps;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return dname;
    }

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

    public String getLoc() {
        return loc;
    }

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

}

View Code

 1 package com.zdsofe.entity.one2many;
 2 
 3 import java.util.Date;
 4 
 5 public class Emp {
 6     private Integer empno;
 7     private String ename;
 8     private String job;
 9     private String mgr;
10     private Date hiredate;
11     private double sal;
12     
13     
14 
15     public Integer getEmpno() {
16         return empno;
17     }
18 
19     public void setEmpno(Integer empno) {
20         this.empno = empno;
21     }
22 
23     public String getEname() {
24         return ename;
25     }
26 
27     public void setEname(String ename) {
28         this.ename = ename;
29     }
30 
31     public String getJob() {
32         return job;
33     }
34 
35     public void setJob(String job) {
36         this.job = job;
37     }
38 
39     public String getMgr() {
40         return mgr;
41     }
42 
43     public void setMgr(String mgr) {
44         this.mgr = mgr;
45     }
46 
47     public Date getHiredate() {
48         return hiredate;
49     }
50 
51     public void setHiredate(Date hiredate) {
52         this.hiredate = hiredate;
53     }
54 
55     public double getSal() {
56         return sal;
57     }
58 
59     public void setSal(double sal) {
60         this.sal = sal;
61     }
62 
63     
64 }

View Code

3.配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.zdsofe.entity.one2many">
<class name="Dept" table="dept">
<id name="deptno" column="deptno">
<generator class="native"></generator>
</id>
<property name="dname"></property>
<property name="loc"></property>
<!--  关联,cascade设置级联-->
<set name="emps" cascade="save-update,delete-orphan">
<!--关联外键  -->
<key column="empno"/>
<one-to-many class="Emp"/>

</set>
</class>
</hibernate-mapping>

View Code

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.zdsofe.entity.one2many">
<class name="Emp" table="emp">
<id name="empno" column="empno">
<generator class="native"></generator>
</id>
<property name="ename"></property>
<property name="job"></property>
<property name="mgr"></property>
<property name="hiredate" type="date"></property>
<property name="sal"></property>

</class>
<!-- 命名查询 -->
<query name="findEmpById">
<![CDATA[from Emp where empno=:id]]>
</query>
</hibernate-mapping>

View Code

核心配置

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE hibernate-configuration PUBLIC
 3     "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
 4     "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
 5 <hibernate-configuration>
 6 <session-factory>
 7 <!-- 数据库连接文件 -->
 8 <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
 9 <property name="connection.url">jdbc:mysql://localhost:3306/db_hibernate</property>
10 <property name="connection.username">root</property>
11 <property name="connection.password">775297</property>
12 <!-- 数据库连接池 -->
13 <property name="connection.pool_size">1</property>
14 <!-- sql方言 -->
15 <property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>
16 <!-- session环境 -->
17 <property name="current_session_context_class">thread</property>
18 <!-- 打印sql语句 -->
19 <property name="show_sql">true</property>
20 <!-- 格式化SQL语句 -->
21 <property name="format_sql">true</property>
22 <!-- 加载配置文件 多对一-->
23 <!-- <mapping resource="com/zdsofe/entity/many2one/Dept.hbm.xml"/>
24 <mapping resource="com/zdsofe/entity/many2one/Emp.hbm.xml"/>  -->
25 <!-- 加载配置文件 一对多 -->
26 <mapping resource="com/zdsofe/entity/one2many/Dept.hbm.xml"/>
27 <mapping resource="com/zdsofe/entity/one2many/Emp.hbm.xml"/>  
28 <!-- 加载配置文件  一对多双向 -->
29 <!-- <mapping resource="com/zdsofe/entity/bothway/Dept.hbm.xml"/>
30 <mapping resource="com/zdsofe/entity/bothway/Emp.hbm.xml"/>  -->
31 <!-- 加载配置问价 多对多 -->
32 <!-- <mapping resource="com/zdsofe/entity/many2many/Student.hbm.xml"/>
33 <mapping resource="com/zdsofe/entity/many2many/Course.hbm.xml"/>  -->
34 </session-factory>
35 </hibernate-configuration>

View Code

布局和其他文件 (数据库在这就不描述了)