你的位置:首页 > Java教程

[Java教程]Hibernate(十二)Criteria查询


一、简述

Criteria是一种比hql更面向对象的查询方式。Criteria 可使用 Criterion 和 Projection 设置查询条件。可以设置 FetchMode(联合查询抓取的模式 ) ,设置排序方式,Criteria 还可以设置 FlushModel (冲刷 Session 的方式)和 LockMode (数据库锁模式)。

Criteria本身只是查询的容器。Criteria查询又称对象查询

Criteria查询采用面向对象的方式封装查询条件。由Hibernater自动生成SQL查询语句

二、不带查询条件

 建立数据表

create table login(    username  varchar2(32) primary key,    password  varchar2(32) not null ,    age     number(3));insert into loginselect '张三','123456',21 from dual unionselect 'Tom','123123',34 from dual unionselect 'Jack','12345678',34 from dual unionselect '李四','qwerty',23 from dual;      commit;

建立持久化类和配置文件

Hibernate.cfg.

<??><!DOCTYPE hibernate-configuration PUBLIC     "-//Hibernate/Hibernate Configuration DTD 3.0//EN"     "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"><hibernate-configuration>  <session-factory>    <property name="dialect">      org.hibernate.dialect.Oracle9Dialect    </property>    <property name="connection.url">      jdbc:oracle:thin:@localhost:1521:orcl    </property>    <property name="connection.username">root</property>    <property name="connection.password">root</property>    <property name="connection.driver_class">      oracle.jdbc.OracleDriver    </property>    <property name="show_sql">true</property>    <property name="format_sql">true</property>        <mapping resource="entity/Login.hbm. />  </session-factory></hibernate-configuration>

持久化类和配置文件 

package entity;public class Login implements java.io.Serializable {  // Fields  /**   *   */  private static final long serialVersionUID = 1L;  private String username;  private String password;  private int age;  // Constructors  /** default constructor */  public Login() {  }  /** minimal constructor */  public Login(String username, String password) {    this.username = username;    this.password = password;  }  /** full constructor */  public Login(String username, String password, int age) {    this.username = username;    this.password = password;    this.age = age;  }  // Property accessors  public String getUsername() {    return this.username;  }  public void setUsername(String username) {    this.username = username;  }  public String getPassword() {    return this.password;  }  public void setPassword(String password) {    this.password = password;  }  public int getAge() {    return this.age;  }  public void setAge(int age) {    this.age = age;  }}

配置文件Login.hbm.

<??><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping>  <class name="entity.Login" table="LOGIN" schema="ROOT">    <id name="username" type="java.lang.String">      <column name="USERNAME" length="32" />      <generator class="assigned" />    </id>    <property name="password" type="java.lang.String">      <column name="PASSWORD" length="32" not-null="true" />    </property>    <property name="age" type="java.lang.Integer">      <column name="AGE" precision="3" scale="0" />    </property>  </class></hibernate-mapping>

测试类:

package demo;import java.util.List;import org.hibernate.Criteria;import org.hibernate.HibernateException;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.cfg.Configuration;import entity.Login;public class Demo1 {  /**   * Criteria无查询条件查询所有   */  public static void main(String[] args) {    //声明一个集合用来接收结果    List<Login> result=null;    //声明SessionFactory    SessionFactory factory=null;    //声明Session    Session session=null;    //初始化以上对象    try{    factory=new Configuration().configure().buildSessionFactory();    session=factory.openSession();    //声明Criteria对象传入一个持久化类对象类型    Criteria criteria=session.createCriteria(Login.class);    //查询使用list方法    result=criteria.list();    }catch(HibernateException e){      e.printStackTrace();    }finally{      session.close();      factory.close();    }    //输出结果    for (Login login : result) {      System.out.println("用户名:"+login.getUsername()+"  密码:"+login.getPassword()+"  年龄:"+login.getAge());    }          }  }

结果:

Hibernate:   select    this_.USERNAME as USERNAME0_0_,    this_.PASSWORD as PASSWORD0_0_,    this_.AGE as AGE0_0_   from    ROOT.LOGIN this_用户名:Jack  密码:12345678  年龄:34用户名:Tom  密码:123123  年龄:34用户名:李四  密码:qwerty  年龄:23用户名:张三  密码:123456  年龄:21

三、添加查询条件

3.1、加入一个username的条件

  //声明Criteria对象传入一个持久化类对象类型    Criteria criteria=session.createCriteria(Login.class);    //添加查询条件 Restrictions.eq是等于的意思,2个参数,第一个为持久化类的属性,第2个为比较的参数值        criteria.add(Restrictions.eq("username", "Tom"));    //查询使用list方法    result=criteria.list();

执行的SQL

  select    this_.USERNAME as USERNAME0_0_,    this_.PASSWORD as PASSWORD0_0_,    this_.AGE as AGE0_0_   from    ROOT.LOGIN this_   where    this_.USERNAME=?

3.2、多个查询条件AND--between

//年龄在大于等于25    criteria.add(Restrictions.le("age", 25));    //年龄小于等于23    criteria.add(Restrictions.ge("age", 23));

或者

//年年龄大于23小于25之间的记录    criteria.add(Restrictions.between("age", 23, 25));

执行的SQL

 select    this_.USERNAME as USERNAME0_0_,    this_.PASSWORD as PASSWORD0_0_,    this_.AGE as AGE0_0_   from    ROOT.LOGIN this_   where    this_.AGE<=?     and this_.AGE>=?

select    this_.USERNAME as USERNAME0_0_,    this_.PASSWORD as PASSWORD0_0_,    this_.AGE as AGE0_0_   from    ROOT.LOGIN this_   where    this_.AGE between ? and ?

3.3、多个查询条件或者条件 or

criteria.add(Restrictions.or(Restrictions.eq("age", 23), Restrictions.like("username", "%李%")));    //或者以下写法直接使用SQL语句    criteria.add(Restrictions.sqlRestriction("age=20 or username like '%李%'"));

执行SQL

 select    this_.USERNAME as USERNAME0_0_,    this_.PASSWORD as PASSWORD0_0_,    this_.AGE as AGE0_0_   from    ROOT.LOGIN this_   where    (      this_.AGE=?       or this_.USERNAME like ?    )

直接使用SQL的执行语句

 select    this_.USERNAME as USERNAME0_0_,    this_.PASSWORD as PASSWORD0_0_,    this_.AGE as AGE0_0_   from    ROOT.LOGIN this_   where    age=20     or username like '%李%'

四、Restrictions常用方法

五、常用方法使用

package demo;import java.util.List;import org.hibernate.Criteria;import org.hibernate.HibernateException;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.cfg.Configuration;import org.hibernate.criterion.Restrictions;import entity.Login;public class Demo1 {  /**   * Criteria无查询条件查询所有   */  public static void main(String[] args) {    //声明一个集合用来接收结果    List<Login> result=null;    //声明SessionFactory    SessionFactory factory=null;    //声明Session    Session session=null;    //初始化以上对象    try{    factory=new Configuration().configure().buildSessionFactory();    session=factory.openSession();    //声明Criteria对象传入一个持久化类对象类型    Criteria criteria=session.createCriteria(Login.class);    //1.查询用户名为Tom的记录    //criteria.add(Restrictions.eq("username", "Tom"));    //2.查询年龄大于等于21同时小于等于25的记录    //criteria.add(Restrictions.ge("age", 21));    //criteria.add(Restrictions.le("age", 25));    //between写法    //criteria.add(Restrictions.between("age", 21, 25));    //3.或者查询年龄=21或者名字中有李的记录    //criteria.add(Restrictions.or(Restrictions.eq("age", 21), Restrictions.like("username", "%李%")));    //sql写法    //criteria.add(Restrictions.sqlRestriction("age=21 or username like '%李%'"));    //4.年龄在21,23,25的记录    //criteria.add(Restrictions.in("age", new Integer []{21,23,25}));    //5.and用法    criteria.add(Restrictions.and(Restrictions.ge("age", 23), Restrictions.like("username", "%T%")));        //查询使用list方法    result=criteria.list();    }catch(HibernateException e){      e.printStackTrace();    }finally{      session.close();      factory.close();    }    System.out.println("==========");    //输出结果    for (Login login : result) {      System.out.println("用户名:"+login.getUsername()+"  密码:"+login.getPassword()+"  年龄:"+login.getAge());    }          }  }

 六、对结果进行排序

使用Order关键字,进行排序

criteria.addOrder(Order.desc(属性名称));降序

criteria.addOrder(Order.asc(属性名称));升序

//按年龄降序排序criteria.addOrder(Order.desc("age"));//按姓名升序排序criteria.addOrder(Order.asc("username"));//查询使用list方法result=criteria.list();//输出结果    for (Login login : result) {      System.out.println("用户名:"+login.getUsername()+"  密码:"+login.getPassword()+"  年龄:"+login.getAge());    }

七、示例查询

package demo;import java.util.List;import org.hibernate.Criteria;import org.hibernate.HibernateException;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.cfg.Configuration;import org.hibernate.criterion.Example;import org.hibernate.criterion.Order;import org.hibernate.criterion.Restrictions;import entity.Login;public class Demo2 {  /**   * Criteria无查询条件查询所有   */  public static void main(String[] args) {    //声明一个集合用来接收结果    List<Login> result=null;    //声明SessionFactory    SessionFactory factory=null;    //声明Session    Session session=null;    //声明一个Login对象,并赋值可以是多个 但不能是主键    Login user=new Login();    user.setAge(21);        //初始化以上对象    try{    factory=new Configuration().configure().buildSessionFactory();    session=factory.openSession();    //声明Criteria对象传入一个持久化类对象类型    Criteria criteria=session.createCriteria(Login.class);    //加入查询条件    criteria.add(Example.create(user));        //查询使用list方法    result=criteria.list();    }catch(HibernateException e){      e.printStackTrace();    }finally{      session.close();      factory.close();    }    System.out.println("==========");    //输出结果    for (Login login : result) {      System.out.println("用户名:"+login.getUsername()+"  密码:"+login.getPassword()+"  年龄:"+login.getAge());    }          }  }

八、聚合函数

 

package demo;import java.util.Iterator;import java.util.List;import org.hibernate.Criteria;import org.hibernate.HibernateException;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.cfg.Configuration;import org.hibernate.criterion.ProjectionList;import org.hibernate.criterion.Projections;import entity.Login;public class Demo3 {  /**   * Criteria无查询条件查询所有   */  public static void main(String[] args) {    //声明一个集合用来接收结果    List<Login> result=null;    //声明SessionFactory    SessionFactory factory=null;    //声明Session    Session session=null;        //初始化以上对象    try{    factory=new Configuration().configure().buildSessionFactory();    session=factory.openSession();    //声明Criteria对象传入一个持久化类对象类型    Criteria criteria=session.createCriteria(Login.class);    //加入查询条件    //总记录数    //criteria.setProjection(Projections.rowCount());    //平均年龄    //criteria.setProjection(Projections.avg("age"));    //分组    criteria.setProjection(Projections.groupProperty("username"));    //查询使用list方法    result=criteria.list();    //System.out.println("平均年龄:"+result.iterator().next());    //System.out.println("总记录数:"+result.iterator().next());    Iterator iterator=result.iterator();    while(iterator.hasNext()){      System.out.println(iterator.next());    }    }catch(HibernateException e){      e.printStackTrace();    }finally{      session.close();      factory.close();    }      }  }

 分页

package demo;import java.util.List;import org.hibernate.Criteria;import org.hibernate.HibernateException;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.cfg.Configuration;import org.hibernate.criterion.Order;import org.hibernate.criterion.Restrictions;import entity.Login;public class Demo4 {  /**   * Criteria无查询条件查询所有   */  public static void main(String[] args) {    //声明一个集合用来接收结果    List<Login> result=null;    //声明SessionFactory    SessionFactory factory=null;    //声明Session    Session session=null;    //当前页数第几页    int pageIndex=1;    //最大显示记录数    int pageSize=2;    //初始化以上对象    try{    factory=new Configuration().configure().buildSessionFactory();    session=factory.openSession();    //声明Criteria对象传入一个持久化类对象类型    Criteria criteria=session.createCriteria(Login.class);    //起始记录数    criteria.setFirstResult((pageIndex-1)*pageSize);    //每页显示最大记录数    criteria.setMaxResults(pageSize);            //查询使用list方法    result=criteria.list();    }catch(HibernateException e){      e.printStackTrace();    }finally{      session.close();      factory.close();    }    System.out.println("==========");    //输出结果    for (Login login : result) {      System.out.println("用户名:"+login.getUsername()+"  密码:"+login.getPassword()+"  年龄:"+login.getAge());    }  }  }