本文旨在为读者呈现不同的方式查询方式:HQL方式的查询、SQL原生态SQL方式的查询、以及Criteria方式的查询,拓展查询的不同实现思路,开阔视野、并涵盖了部分的Hibernate增删改的基本操作。
简单介绍一下HQL和Criteria:
HQL
HQL(Hibernate Query Language)提供更加丰富灵活、更为强大的查询能力;HQL更接近SQL语句查询语法;
[select/delete/update…][from…][where…][group by…][having…][order by…]
Criteria
Criteria是一种比hql更面向对象的查询方式。Criteria 可使用 Criterion 和 Projection 设置查询条件。可以设置 FetchMode(联合查询抓取的模式 ) ,设置排序方式,Criteria 还可以设置 FlushModel (冲刷 Session 的方式)和 LockMode
Criterion 是 Criteria 的查询条件。
Criteria 提供了 add(Criterion criterion) 方法来添加查询条件。
Criterion 接口的主要实现包括: Example 、 Junction 和 SimpleExpression 。而Junction 的实际使用是它的两个子类 conjunction 和 disjunction ,分别是使用 AND 和 OR 操作符进行来联结查询条件集合。
Criterion 的实例可以通过 Restrictions 工具类来创建,Restrictions 提供了大量的
静态方法,如 eq (等于)、 ge (大于等于)、 between 等来方法的创建 Criterion 查询条件(SimpleExpression 实例)。除此之外, Restrictions 还提供了方法来创建 conjunction 和disjunction 实例,通过往该实例的 add(Criteria) 方法来增加查询条件形成一个查询条件集合。
Example 的创建有所不同, Example 本身提供了一个静态方法 create(Objectentity) ,即根据一个对象(实际使用中一般是映射到数据库的对象)来创建。然后可以设置一些过滤条件:
Example exampleUser =Example.create(u)
.ignoreCase() // 忽略大小写
.enableLike(MatchMode.ANYWHERE); // 对 String 类型的属性,无论在那里值在那里都匹配。相当于 %value%
Project 主要是让 Criteria 能够进行报表查询,并可以实现分组。 Project 主要有SimpleProjection 、ProjectionList 和 Property 三个实现。其中SimpleProjection 和ProjectionList 的实例化是由内建的Projections 来完成,如提供的 avg 、count 、max 、min 、sum 可以让开发者很容易对某个字段进行统计查询。
Property 是对某个字段进行查询条件的设置,如通过Porperty.forName(“color”). in(new String[]{“black”,”red”,”write”}); 则可以创建一个 Project 实例。通过criteria 的 add(Project) 方法加入到查询条件中去。
准备工作
jar包以及项目的结构
User.java
- package com.siti.domain;
- public class User {
- private Long userId;
- private String userName;
- private String password;
- public Long getUserId() {
- return userId;
- }
- public void setUserId(Long userId) {
- this.userId = userId;
- }
- public String getUserName() {
- return userName;
- }
- public void setUserName(String userName) {
- this.userName = userName;
- }
- public String getPassword() {
- return password;
- }
- public void setPassword(String password) {
- this.password = password;
- }
- @Override
- public String toString() {
- return "User [userId=" + userId + ", userName=" + userName
- + ", password=" + password + "]";
- }
- }
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE hibernate-mapping PUBLIC
- "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
- "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
- <hibernate-mapping package="com.siti.domain">
- <class name = "User" table="user">
- <id name = "userId" type = "java.lang.Long" column = "id">
- <generator class="native"></generator>
- </id>
- <property name="userName" type = "string" column = "userName" length = "20"></property>
- <property name="password" type = "string" column = "password" length = "20"></property>
- </class>
- </hibernate-mapping>
- <!DOCTYPE hibernate-configuration PUBLIC
- "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
- "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
- <!-- Generated by MyEclipse Hibernate Tools. -->
- <hibernate-configuration>
- <session-factory>
- <property name="hibernate.connection.driver_class">
- com.mysql.jdbc.Driver
- </property>
- <property name="hibernate.connection.url">
- jdbc:mysql://localhost:3306/hiber
- </property>
- <property name="hibernate.connection.username">root</property>
- <property name="hibernate.connection.password">root</property>
- <property name="dialect">
- org.hibernate.dialect.MySQLDialect
- </property>
- <property name="connection.pool_size">1</property>
- <property name="show_sql">true</property>
- <property name="hibernate.hbm2ddl.auto">update</property>
- <mapping resource="com/siti/domain/User.hbm.xml" />
- </session-factory>
- </hibernate-configuration>
对照代码
- package com.siti.test;
- import java.util.List;
- import org.hibernate.SQLQuery;
- import org.hibernate.Session;
- import org.hibernate.SessionFactory;
- import org.hibernate.cfg.Configuration;
- import org.hibernate.criterion.Criterion;
- import org.hibernate.criterion.Projection;
- import org.hibernate.criterion.Projections;
- import org.hibernate.criterion.Restrictions;
- import com.siti.domain.User;
- public class HiberTest {
- static SessionFactory sessionFactory = null;
- static {
- sessionFactory = new Configuration()
- .configure()
- .buildSessionFactory();
- }
- public static void main(String[] args) {
- testCriteriaSelectProjectUser();
- }
- /**
- * 7. Criteria 方式投影分组查询
- * Hibernate: select this_.userName as y0_, count(this_.password) as y1_ from user this_ group by this_.userName
- *
- * [[Ljava.lang.Object;@1f44ec7, [Ljava.lang.Object;@602b6b, [Ljava.lang.Object;@1c297a3, [Ljava.lang.Object;@717323, [Ljava.lang.Object;@3e1bc8]
- *
- * 从这几个例子我们不难发现,如果没有指定select语句(没有投影),那么将返回表中的所有字段,返回结果会被封装到Entity实体对象User中,一但提供select语句(投影)后,
- * 返回的结果类型,将不再封装到User对象,而是根据投影的实际类型返回,这就是投影对结果封装策略的影响。
- */
- public static void testCriteriaSelectProjectUser(){
- Session session = sessionFactory.openSession();
- List<Object> userList = session.createCriteria(User.class)//
- .setProjection(
- Projections.projectionList()
- .add(Projections.groupProperty("userName"))
- .add(Projections.count("password"))
- )
- .list();
- System.out.println(userList);
- session.close();
- }
- /**
- * 6. SQL 方式测试查询附加条件id>?
- * Hibernate: select * from user where id > 5 // 注意这里用的是id>5而不是userId
- *
- * [User [userId=11, userName=zhangsan, password=zs]]
- *
- */
- public static void testSQLSelectUser(){
- Session session = sessionFactory.openSession();
- List<User> userList = session.createSQLQuery("select * from user where id > 5")//
- .addEntity(User.class)//
- .list();
- System.out.println(userList);
- session.close();
- }
- /**
- * 6. HQL 方式测试查询附加条件id>?
- * Hibernate: select user0_.id as id0_, user0_.userName as userName0_, user0_.password as password0_ from user user0_ where user0_.id>5
- *
- * [User [userId=11, userName=zhangsan, password=zs]]
- *
- */
- public static void testHQLSelectUser(){
- Session session = sessionFactory.openSession();
- List<User> userList = session.createQuery("from User user where user.userId > 5")
- .list();
- System.out.println(userList);
- session.close();
- }
- /**
- * 6. Criteria 方式测试查询附加条件id>?
- * Hibernate: select this_.id as id0_0_, this_.userName as userName0_0_, this_.password as password0_0_ from user this_ where this_.id>?
- *
- * [User [userId=11, userName=zhangsan, password=zs]]
- *
- */
- public static void testCriteriaSelectUser(){
- Session session = sessionFactory.openSession();
- List<User> userList = session.createCriteria(User.class)//
- .add(Restrictions.gt("userId", 5L))//
- .list();
- System.out.println(userList);
- session.close();
- }
- /**
- * 5. SQL 方式测试查询like附加条件
- * Hibernate: select * from user where userName like 'zhan%'
- *
- * [User [userId=11, userName=zhangsan, password=zs]]
- *
- */
- public static void testSQLSelectPartUser(){
- Session session = sessionFactory.openSession();
- List<User> userList = session.createSQLQuery("select * from user where userName like 'zhan%'")
- .addEntity(User.class)
- .list();
- System.out.println(userList);
- session.close();
- }
- /**
- * 5. HQL 方式测试查询like附加条件
- * Hibernate: select user0_.id as id0_, user0_.userName as userName0_, user0_.password as password0_ from user user0_ where user0_.userName like 'zhan%'
- *
- * [User [userId=11, userName=zhangsan, password=zs]]
- *
- */
- public static void testHQLSelectPartUser(){
- Session session = sessionFactory.openSession();
- List<User> userList = session.createQuery("from User user where user.userName like 'zhan%'")
- .list();
- System.out.println(userList);
- session.close();
- }
- /**
- * 5. Criteria 方式测试查询like附加条件
- * Hibernate: select this_.id as id0_0_, this_.userName as userName0_0_, this_.password as password0_0_ from user this_ where this_.userName like ?
- *
- * [User [userId=11, userName=zhangsan, password=zs]]
- *
- */
- public static void testCriteriaSelectPartUser(){
- Session session = sessionFactory.openSession();
- List<User> userList = session.createCriteria(User.class)
- .add(Restrictions.like("userName", "zhan%"))
- .list();
- System.out.println(userList);
- session.close();
- }
- /**
- * 4. SQL 方式测试查询所有的user数据
- * Hibernate: select * from user
- *
- * [User [userId=1, userName=wy, password=1234],
- * User [userId=2, userName=yy, password=123],
- * User [userId=3, userName=sd, password=12],
- * User [userId=4, userName=test, password=sffs],
- * User [userId=11, userName=zhangsan, password=zs]]
- */
- public static void testSQLSelectAllUser(){
- Session session = sessionFactory.openSession();
- List<User> userList = session.createSQLQuery("select * from user").addEntity(User.class).list();
- System.out.println(userList);
- session.close();
- }
- /**
- * 4. HQL 方式测试查询所有的user数据
- * Hibernate: select user0_.id as id0_, user0_.userName as userName0_, user0_.password as password0_ from user user0_
- *
- * [User [userId=1, userName=wy, password=1234],
- * User [userId=2, userName=yy, password=123],
- * User [userId=3, userName=sd, password=12],
- * User [userId=4, userName=test, password=sffs],
- * User [userId=11, userName=zhangsan, password=zs]]
- */
- public static void testHQLSelectAllUser(){
- Session session = sessionFactory.openSession();
- List<User> userList = session.createQuery("from User").list();
- System.out.println(userList);
- session.close();
- }
- /**
- * 4. Criteria 方式测试查询所有的user数据
- * Hibernate: select this_.id as id0_0_, this_.userName as userName0_0_, this_.password as password0_0_ from user this_
- *
- * [User [userId=1, userName=wy, password=1234],
- * User [userId=2, userName=yy, password=123],
- * User [userId=3, userName=sd, password=12],
- * User [userId=4, userName=test, password=sffs],
- * User [userId=11, userName=zhangsan, password=zs]]
- */
- public static void testCriteriaSelectAllUser(){
- Session session = sessionFactory.openSession();
- List<User> userList = session.createCriteria(User.class).list();
- System.out.println(userList);
- session.close();
- }
- /**
- * 3. 删除用户
- * Hibernate: delete from user where id=?
- */
- public static void testDeleteUser(){
- User user = new User();
- user.setUserId(5L);
- user.setUserName("zhanan");
- user.setPassword("zb");
- Session session = sessionFactory.openSession();
- session.beginTransaction(); // 开启事务
- session.delete(user);
- session.getTransaction().commit();// 提交事务
- session.close();
- }
- /**
- * 2. 更新用户
- * Hibernate: update user set userName=?, password=? where id=?
- */
- public static void testUpdateUser(){
- User user = new User();
- user.setUserId(5L);
- user.setUserName("zhanan");
- user.setPassword("zb");
- Session session = sessionFactory.openSession();
- session.beginTransaction(); // 开启事务
- session.update(user);
- session.getTransaction().commit();// 提交事务
- session.close();
- }
- /**
- * 1. 添加用户
- * Hibernate: insert into user (userName, password) values (?, ?)
- */
- public static void testInsertUser(){
- User user = new User();
- user.setUserId(5L);
- user.setUserName("zhangsan");
- user.setPassword("zs");
- Session session = sessionFactory.openSession();
- session.beginTransaction(); // 开启事务
- session.save(user);
- session.getTransaction().commit();// 提交事务
- session.close();
- }
- }