QBC & 本地SQL 查询

最新推荐文章于 2020-06-06 14:40:33 发布

原创最新推荐文章于 2020-06-06 14:40:33 发布 · 450 阅读

0 ·

CC 4.0 BY-SA版权

Hibernate 专栏收录该内容

50 篇文章

订阅专栏

本文通过具体示例介绍了Hibernate中使用Query By Criteria (QBC)进行复杂查询的方法，包括条件组合、统计查询及排序分页等，并展示了如何执行本地SQL。

package com.baidu.test;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.baidu.QBC_or_LocalSQL.Department;
import com.baidu.QBC_or_LocalSQL.Employee;

public class TestQBC_or_LocalSQL {

	private SessionFactory sessionFactory;
	private Session session;
	private Transaction transaction;
	
	
	@Before
	public void init(){
		Configuration configuration = new Configuration().configure();
		ServiceRegistry serviceRegistry = new ServiceRegistryBuilder()
										.applySettings(configuration.getProperties())
										.buildServiceRegistry();
		
		sessionFactory = configuration.buildSessionFactory(serviceRegistry);
		
		session = sessionFactory.openSession();
		transaction = session.beginTransaction();
	}
	@After
	public void destroy(){
		transaction.commit();
		session.close();
		sessionFactory.close();
		
	}
	
	//~~~~~~~~~~~~~~~~~~~~~~~QBC~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	@Test
	public void testQBC(){
		
		//1. 创建一个Criteria 对象
			Criteria criteria = session.createCriteria(Employee.class);
		
		//2.添加查询条件，在QBC 中查询条件使用Criterion 来表示
		// Criterion 可以通过 Restrictions 的静态方法得到
			criteria.add(Restrictions.eq("email", "SKUMAR"));
			criteria.add(Restrictions.gt("salary", 5000F));
			
		//3.
		Employee employee = (Employee) criteria.uniqueResult();
		System.out.println(employee);
	}
	

	@Test
	public void testQBC2(){
		
		Criteria criteria = session.createCriteria(Employee.class);
		
		/**
		 * 1. and : 使用Conjunction 表示。 Conjunction 本身就是一个Criterion 对象
		 *   		且其中还可以添加Critertion 对象
		 * 
		 */
		Conjunction conjunction = Restrictions.conjunction();
		conjunction.add(Restrictions.like("name", "a", MatchMode.ANYWHERE));
		Department dept = new Department();
		dept.setId(80);
		conjunction.add(Restrictions.eq("dept", dept));
		
		System.out.println(conjunction);
		
		/**
		 * 2. or : 使用Disjunction 表示。 Disjunction 本身就是一个Criterion 对象
		 *   		且其中还可以添加Criterion 对象
		 * 
		 */
		Disjunction disjunction = Restrictions.disjunction();
		disjunction.add(Restrictions.ge("salary", 6000F));
		disjunction.add(Restrictions.isNull("email"));
		System.out.println(disjunction);
		
		criteria.add(disjunction);
		criteria.add(conjunction);
		List<Employee> emps = criteria.list();
	}
	
	@Test
	public void testQBC3(){
		
		Criteria criteria = session.createCriteria(Employee.class);
		/**
		 * 统计查询： 使用Projection 表示。可以使用Projections 的静态方法得到
		 * 
		 */
		
		criteria.setProjection(Projections.max("salary"));
		
		System.out.println(criteria.uniqueResult());
	}
	
	
	@Test
	public void testQBC4(){
		
		Criteria criteria = session.createCriteria(Employee.class);
		/**
		 * 排序： 
		 * 
		 */
		//1.添加排序
		criteria.addOrder(Order.asc("salary"));
		criteria.addOrder(Order.desc("email"));
		
		//2. 添加分页方法
		int pageNo = 3;
		int pageSize = 5;
		
		criteria.setFirstResult((pageNo-1)*pageSize)
				.setMaxResults(pageSize)
				.list();
	}
	
	
//~~~~~~~~~~~~~~~~~~~~~~~本地SQL~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	@Test
	public void testNativeSQL(){
		String sql = "INSERT INTO bb_departments VALUES(?,?)";
		Query query = session.createSQLQuery(sql);
		
		query.setInteger(0, 280)
			 .setString(1, "Baidu")
			 .executeUpdate();
	}
}

QBC

testQBC() 运行的SQL

Hibernate: 
    select
        this_.ID as ID1_1_0_,
        this_.NAME as NAME2_1_0_,
        this_.SALARY as SALARY3_1_0_,
        this_.EMAIL as EMAIL4_1_0_,
        this_.DEPT_ID as DEPT_ID5_1_0_ 
    from
        BB_EMPLOYEES this_ 
    where
        this_.EMAIL=? 
        and this_.SALARY>?
Employee [id=173]

testQBC2() 运行的SQL

(name like %a% and dept=Department [id=80])
(salary>=6000.0 or email is null)
Hibernate: 
    select
        this_.ID as ID1_1_0_,
        this_.NAME as NAME2_1_0_,
        this_.SALARY as SALARY3_1_0_,
        this_.EMAIL as EMAIL4_1_0_,
        this_.DEPT_ID as DEPT_ID5_1_0_ 
    from
        BB_EMPLOYEES this_ 
    where
        (
            this_.SALARY>=? 
            or this_.EMAIL is null
        ) 
        and (
            this_.NAME like ? 
            and this_.DEPT_ID=?
        )

testQBC3()运行的SQL

Hibernate: 
    select
        max(this_.SALARY) as y0_ 
    from
        BB_EMPLOYEES this_
256000.0

testQBC4()运行的SQL

Hibernate: 
    select
        * 
    from
        ( select
            row_.*,
            rownum rownum_ 
        from
            ( select
                this_.ID as ID1_1_0_,
                this_.NAME as NAME2_1_0_,
                this_.SALARY as SALARY3_1_0_,
                this_.EMAIL as EMAIL4_1_0_,
                this_.DEPT_ID as DEPT_ID5_1_0_ 
            from
                BB_EMPLOYEES this_ 
            order by
                this_.SALARY asc,
                this_.EMAIL desc ) row_ 
        where
            rownum <= ?
        ) 
    where
        rownum_ > ?

本地SQL

testNativeSQL()

Hibernate: 
    INSERT 
    INTO
        bb_departments
        
    VALUES
        (?,?)

~~~~~~~~~~~~~~~~~~~~~~~~~~~分割线~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

有关Criterion 见

QBC & 本地SQL 查询

Hibernate 查询Criterion数据