Hibernate5.2值QBC查询
一.简介
Hibenate的QBC查询个人认为是Hibernate的很大一个亮点，提供个丰富的查询API，在使用上可能显得稍稍有些繁琐。但是QBC据笔者多年的从业经验上来讲，几乎没有什么程序员使用，可能是源于其API有些多。那么本篇文章笔者还是按照之前的博文一样，提供大量的示例，而且笔者承诺这些示例在后期一定会不断的增加，也希望各位读者多多提一些宝贵意见，或者碰到什么问题，可以给本人留言，本人会虚心的接收或者给出本人觉得合适的一些解决方法。
本文所采用的数据库表应该是所有学过Oracle的读者都会经历的两张表(即scott用户的emp和dept表)。
二.数据库脚本
create table EMP
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
primary key (EMPNO)
); create table DEPT
(
deptno NUMBER(2) not null,
dname VARCHAR2(14),
loc VARCHAR2(13),
primary key (DEPTNO)
);
数据库中现有的数据如下：
EMP
DEPT
三.基础测试类代码
3.1 获取Session工具类
public class OracleSessionUtils {
//获取SessionFactory
public static SessionFactory getSessionFactory(){
StandardServiceRegistry registry = null;
SessionFactory sessionFactory = null;
try{
registry = new StandardServiceRegistryBuilder().configure("hibernate-oracle.xml").build();
//不指定文件名默认是找hibernate.cfg.xml文件
//registry = new StandardServiceRegistryBuilder().configure().build();
sessionFactory = new MetadataSources(registry).buildMetadata().buildSessionFactory();
}catch(Exception ex){
ex.printStackTrace();
StandardServiceRegistryBuilder.destroy(registry);
}
return sessionFactory;
}
//打开并返回一个Session
public static Session openSession(){
return getSessionFactory().openSession();
}
//关闭Session
public static void closeSession(Session session){
if(null != session){
session.close();
}
}
}
3.2单元测试基础代码
public class HibernateQBCTest {
private Session session;
@Before
public void getSession(){
session = OracleSessionUtils.openSession();
}
@After
public void closeSession(){
OracleSessionUtils.closeSession(session);
}
}
四.POJO类的创建
Emplyee.java
@Entity
@Table(name="emp")
public class Employee { @Id
@Column(name="empno")
@GenericGenerator(name="assignedGenerator", strategy="assigned")
@GeneratedValue(generator="assignedGenerator")
private int id; @Column(name="ename")
private String ename; @Column(name="job")
private String job; @Column(name="hiredate")
private Date hiredate; @Column(name="sal")
private Double salary; @Column(name="comm")
private Double comm; @ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="deptno")
private Department dept; //getter and setter
}
Department.java
@Entity
@Table(name="dept")
public class Department {
@Id
@Column(name="deptno")
@GenericGenerator(name="assignedGenerator", strategy="assigned")
@GeneratedValue(generator="assignedGenerator")
private int id; @Column(name="dname")
private String departmentName; @Column(name="loc")
private String location; @OneToMany(mappedBy="dept", fetch=FetchType.LAZY)
private Set<Employee> empSet; //setter and getter
}
五.QBC查询用例
A.查询所有的员工
//查询所有
@Test
public void list(){
List<Employee> empList = session.createCriteria(Employee.class).list();
for(Employee e : empList){
System.out.println(e.getEname());
}
}
B.分页查询员工
//分页查询
@Test
public void pageList(){
List<Employee> empList = session.createCriteria(Employee.class).setFirstResult(0).setMaxResults(4).list();
for(Employee e : empList){
System.out.println(e.getEname());
}
}
C.Like查询，忽略大小写
//like查询,忽略大小写
@Test
public void likeQuery(){
/*
List<Employee> empList = session.createCriteria(Employee.class).add(Restrictions.like("ename", "%a%").ignoreCase()).list();
*/
List<Employee> empList = session.createCriteria(Employee.class).add(Restrictions.like("ename", "a", MatchMode.ANYWHERE).ignoreCase()).list();
for(Employee e : empList){
System.out.println(e.getEname());
}
}
D.between查询
//日期或者Id的between查询
@Test
public void betweenQuery(){
/*
List<Employee> empList = session.createCriteria(Employee.class).add(Restrictions.between("id", 7000, 8000)).list();
for(Employee e : empList){
System.out.println(e.getEname());
}
*/
String beginDateStr = "1980-07-26 00:00:00";
String endDateStr = "2016-07-28 23:59:59";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date beginDate = null;
Date endDate = null;
try {
beginDate = sdf.parse(beginDateStr);
endDate = sdf.parse(endDateStr);
} catch (ParseException e) {
e.printStackTrace();
}
List<Employee> empList = session.createCriteria(Employee.class).add(Restrictions.between("hiredate", beginDate, endDate)).list();
for(Employee e : empList){
System.out.println(e.getEname());
}
}
E.in查询
//in查询
@Test
public void inQuery(){
List<Employee> empList = session.createCriteria(Employee.class).add(Restrictions.in("id", new Integer[]{7369, 7499})).list();
for(Employee e : empList){
System.out.println(e.getEname());
}
}
F.>或>=
//>= 或者 >
@Test
public void gtOrGeQuery(){
//List<Employee> empList = session.createCriteria(Employee.class).add(Restrictions.gt("id", 7500)).list(); // 大于(>)
List<Employee> empList = session.createCriteria(Employee.class).add(Restrictions.ge("id", 7521)).list(); // 大于等于(>=)
for(Employee e : empList){
System.out.println(e.getEname());
}
}
G.<或<=
//<或者<=
@Test
public void ltOrLeQuery(){
//List<Employee> empList = session.createCriteria(Employee.class).add(Restrictions.lt("id", 7500)).list(); // 小于(<)
List<Employee> empList = session.createCriteria(Employee.class).add(Restrictions.le("id", 7521)).list(); // 小于等于(<=)
for(Employee e : empList){
System.out.println(e.getEname());
}
}
H.=查询
//=
@Test
public void eqQuery(){
List<Employee> empList = session.createCriteria(Employee.class).add(Restrictions.eq("id", 7521)).list(); // 等于(=)
for(Employee e : empList){
System.out.println(e.getEname());
}
}
I.多条件查询(and)
//多条件查询and
@Test
public void multiAndQuery(){
String beginDateStr = "1980-07-26 00:00:00";
String endDateStr = "2016-07-28 23:59:59";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date beginDate = null;
Date endDate = null;
try {
beginDate = sdf.parse(beginDateStr);
endDate = sdf.parse(endDateStr);
} catch (ParseException e) {
e.printStackTrace();
}
List<Employee> empList = session.createCriteria(Employee.class)
.add(Restrictions.ge("id", 7521))
.add(Restrictions.between("hiredate", beginDate, endDate))
.add(Restrictions.like("ename", "a", MatchMode.ANYWHERE).ignoreCase())
.list();
for(Employee e : empList){
System.out.println(e.getEname());
}
}
G.多条件查询(or)
//多条件或者查询
@Test
public void multiOrQuery(){
List<Employee> empList = session.createCriteria(Employee.class)
.add(Restrictions.or(Restrictions.eq("id", 7521), Restrictions.like("ename", "A%").ignoreCase()))
.list();
for(Employee e : empList){
System.out.println(e.getEname());
}
}
K.排序
//排序
@Test
public void orderQuery(){
List<Employee> empList = session.createCriteria(Employee.class).addOrder(Order.desc("salary")).list();
for(Employee e : empList){
System.out.println(e.getEname());
}
}
L.统计
//统计20号部门有多少人
@Test
public void countQuery(){
List<Integer> list = session.createCriteria(Employee.class).setProjection(Projections.rowCount()).add(Restrictions.eq("dept.id", 20)).list();
System.out.println(list.get(0));
}
M.统计
//统计各个部门的人数、工资的平均值、工资的最大值、工资的最小值、工资的总和
@Test
public void count(){
List<Object[]> list = session.createCriteria(Employee.class)
.setProjection(Projections.projectionList()
.add(Projections.property("dept.id"))
.add(Projections.rowCount())
.add(Projections.avg("salary"))
.add(Projections.max("salary"))
.add(Projections.min("salary"))
.add(Projections.sum("salary"))
.add(Projections.groupProperty("dept.id"))
).list(); for(Object[] obj : list){
for(Object o : obj){
System.out.print(o + " ");
}
System.out.println();
}
}
N.投影(单列)
//只查询单独的一列
@Test
public void singleColumn(){
List<String> list = session.createCriteria(Employee.class)
.setProjection(Projections.property("ename")).list(); for(String str : list){
System.out.println(str);
}
}
O.投影(多列)
//只查询工号和姓名
@Test
public void nameAndNoQuery(){
List<Object[]> list = session.createCriteria(Employee.class)
.setProjection(
Projections.projectionList()
.add(Projections.property("ename"))
.add(Projections.property("id"))
).list(); for(Object[] obj : list){
for(Object o : obj){
System.out.print(o + " ");
}
System.out.println();
}
}
P.样例查询
//样例查询
@Test
public void exampleQuery(){
Employee e = new Employee();
e.setJob("CLERK"); Example example = Example.create(e).ignoreCase();
List<Employee> list = session.createCriteria(Employee.class).add(example).list();
for(Employee em : list){
System.out.println(em.getEname());
}
}
Q.非空查询
//非空查询
@Test
public void notNullQuery(){
List<Employee> list = session.createCriteria(Employee.class).add(Restrictions.isNotNull("comm")).list();
for(Employee e : list){
System.out.println(e.getEname());
}
}