hql查询
- package Usertest;
- import java.util.List;
- import org.hibernate.HibernateException;
- import org.hibernate.Session;
- import org.hibernate.SessionFactory;
- import org.hibernate.Transaction;
- import org.hibernate.query.Query;
- import org.junit.Test;
- import text.User;
- public class Hql {
- @Test
- public void Query1(){//搜索User中的所有属性输出姓名
- // TODO Auto-generated method stub
- SessionFactory sf = null;
- Session session = null;
- Transaction ts = null;
- try {
- sf = HibernateUtil.getSessionFactory();
- session = sf.getCurrentSession();
- ts = session.beginTransaction();
- User user=new User();
- Query query = session.createQuery("from User");
- List users = query.list();
- for(int i=0;i<users.size();i++)
- {
- user =(User)users.get(i);
- System.out.println(i+"--"+user.getUsername());
- }
- ts.commit();
- } catch (HibernateException e) {
- // TODO Auto-generated catch block
- if(ts != null)
- {
- ts.rollback();
- }
- e.printStackTrace();
- }
- }
- @Test
- //搜索多个属性返回数组
- public void Queryattributes(){
- // TODO Auto-generated method stub
- SessionFactory sf = null;
- Session session = null;
- Transaction ts = null;
- try {
- sf = HibernateUtil.getSessionFactory();
- session = sf.getCurrentSession();
- ts = session.beginTransaction();
- User user=new User();
- Query query = session.createQuery("select u.username, u.password from User as u");
- List users = query.list();
- for(int i=0;i<users.size();i++)
- {
- Object obj[] =(Object[])users.get(i);
- System.out.println(obj[0]+"的密码为:"+obj[1]);
- }
- ts.commit();
- } catch (HibernateException e) {
- // TODO Auto-generated catch block
- if(ts != null)
- {
- ts.rollback();
- }
- e.printStackTrace();
- }
- }
- @Test
- //指定新建一个对象传回,注意,此处需要在实体类中创建含参构造方法和无参构造方法,无参构造方法是为了其他语句使用,因为系统默认是调用无参的构造方法
- public void Querynewattributes(){
- // TODO Auto-generated method stub
- SessionFactory sf = null;
- Session session = null;
- Transaction ts = null;
- try {
- sf = HibernateUtil.getSessionFactory();
- session = sf.getCurrentSession();
- ts = session.beginTransaction();
- Query query = session.createQuery("select new User(u.username,u.password) from User as u");
- List users = query.list();
- for(int i=0;i<users.size();i++)
- {
- User user =(User)users.get(i);
- System.out.println(user.getUsername()+"的密码为:"+user.getPassword());
- }
- ts.commit();
- } catch (HibernateException e) {
- // TODO Auto-generated catch block
- if(ts != null)
- {
- ts.rollback();
- }
- e.printStackTrace();
- }
- }
- @Test
- public void countattributes(){//返回共有多少条记录,平均年龄以及最大年龄
- // TODO Auto-generated method stub
- SessionFactory sf = null;
- Session session = null;
- Transaction ts = null;
- try {
- sf = HibernateUtil.getSessionFactory();
- session = sf.getCurrentSession();
- ts = session.beginTransaction();
- Query query = session.createQuery("select count(*) from User");
- Object count = (Object)query.uniqueResult();
- System.out.println("共有"+count+"条记录");
- query = session.createQuery("select avg(u.age) from User u");
- Number average =(Number)query.uniqueResult();
- System.out.println("平均年龄为:"+average);
- query = session.createQuery("select max(u.age) from User u");
- Number max =(Number)query.uniqueResult();
- System.out.println("最大年龄为:"+max);
- ts.commit();
- } catch (HibernateException e) {
- // TODO Auto-generated catch block
- if(ts != null)
- {
- ts.rollback();
- }
- e.printStackTrace();
- }
- }
- @Test
- //提取第5-9个对象
- public void query(){
- // TODO Auto-generated method stub
- SessionFactory sf = null;
- Session session = null;
- Transaction ts = null;
- try {
- sf = HibernateUtil.getSessionFactory();
- session = sf.getCurrentSession();
- ts = session.beginTransaction();
- Query query=session.createQuery("from User");
- query.setFirstResult(4);
- query.setMaxResults(5);
- List result=query.list();
- for(int i=0;i<result.size();i++)
- {
- User user =(User)result.get(i);
- System.out.println( user.getId()+" "+user.getUsername()+"--"+user.getPassword());
- }
- ts.commit();
- } catch (HibernateException e) {
- // TODO Auto-generated catch block
- if(ts != null)
- {
- ts.rollback();
- }
- e.printStackTrace();
- }
- }
- @Test
- //嵌套子查询
- public void doublequery(){
- // TODO Auto-generated method stub
- SessionFactory sf = null;
- Session session = null;
- Transaction ts = null;
- try {
- sf = HibernateUtil.getSessionFactory();
- session = sf.getCurrentSession();
- ts = session.beginTransaction();
- Query query = session.createQuery("from User u where u.age>(select avg(age) from User)");
- List users = query.list();
- for(int i=0;i<users.size();i++)
- {
- User user =(User)users.get(i);
- System.out.println(user.getId()+"--姓名:"+user.getUsername()+"--年龄"+user.getAge());
- }
- ts.commit();
- } catch (HibernateException e) {
- // TODO Auto-generated catch block
- if(ts != null)
- {
- ts.rollback();
- }
- e.printStackTrace();
- }
- }
- }
注意,创建新对象需要在实体类中创建含参构造方法和无参构造方法,无参构造方法是为了其他语句使用,因为系统默认是调用无参的构造方法
- public User(){}
- public User(int id, String username, String password, String gender, int age) {
- this.id = id;
- this.username = username;
- this.password = password;
- this.gender = gender;
- this.age = age;
- }
搜索user中的所有属性输出姓名
搜索多个属性返回数组
统计总的记录数以及年龄的最大值和平均值输出结果
嵌套子查询
提取第5-9个对象结果