Hibernate HQL查询语句Query

Department类

@Entity
@Table(name="t_department")
public class Department extends BaseEntity {

    String name;

    @OneToMany(mappedBy="department")
    private Set<Employee> employees;

    public Set<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(Set<Employee> employees) {
        this.employees = employees;
    }

    public Department() {
        super();
        // TODO Auto-generated constructor stub
    }

    public Department(String name) {
        this.name = name;
    }

    public Department(int id, String name) {
        super(id);
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Department [name=" + name + "]";
    }

}

Employee 类

@Entity
@Table(name="t_employee")
public class Employee extends BaseEntity {

    private String name;
    private int age;

    @ManyToOne(cascade=CascadeType.PERSIST)
    @JoinColumn(name="depart_id")
    private Department department;


    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Employee() {
        super();
        // TODO Auto-generated constructor stub
    }

    public Employee(String name, int age) {
        super();
        this.name = name;
        this.age = age;
    }

    /*public Employee(int id, String name, int age) {
        super(id);
        this.name = name;
        this.age = age;
    }*/

    @Override
    public String toString() {
        return "Employee [id="+id+",name=" + name + ", age=" + age + "]";
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

}

测试类

public class HQLDemo01 {

    public static void main(String[] args) {
        //add();

        query();

    }


    /**
     * 演示HQL的使用
     */
    private static void query() {
        Session session=HibernateUtils.getSession();

        //1.查询单个对象
        /*String hql="from Employee where id=:id";
        Query query=session.createQuery(hql);
        query.setInteger("id",1);*/
        //如果明确知道查询的结果就一个对象，可以不使用集合操作。
        /*List<Employee> employees=query.list();
        System.out.println(employees);*/

        //如果只查询一个对象，可以使用query.uniqueResult()方法，此方法必须保证返回的对象只有一个，
        //如果有多个则报异常：org.hibernate.NonUniqueResultException:
        /*Employee employee=(Employee) query.uniqueResult();
        System.out.println(employee);*/


        /*//2.查询多个对象
        String hql="from Employee";
        Query query=session.createQuery(hql);
        List<Employee> employees=query.list();
        System.out.println(employees);*/

        /*
         * 3.分页查询
         * 
         * 已知变量：
         *  当前页号pageNo=1;
         *  每页显示的记录数pageSize=3;
         *  起始索引=(pageNo-1)*pageSize
         *  结束索引=pageNo*pageSize 
         */
        /*String hql="from Employee";
        Query query=session.createQuery(hql);

        int pageNo=2;
        int pageSize=4;
        int startIndex=(pageNo-1)*pageSize;
        //设置起始索引
        query.setFirstResult(startIndex);
        //设置每页显示的记录数
        query.setMaxResults(pageSize);
        List<Employee> employees=query.list();
        System.out.println(employees);*/

        //4.对查询的数据进行排序 order by 字段名 asc|desc 
        //String hql="from Employee order by id desc";
        //String hql="from Employee order by id asc";
        /*String hql="from Employee order by id";//如果不指定，则默认是升序排序，即asc可以省略
        Query query=session.createQuery(hql);
        List<Employee> employees=query.list();
        System.out.println(employees);*/

        //5.对查询的数据进行分组
        /*String hql="select e.age,count(*) from Employee e group by age having count(*)>2";//如果不指定，则默认是升序排序，即asc可以省略
        Query query=session.createQuery(hql);
        List<Object[]> employees=query.list();
        for(Object[] objs :employees){
            System.out.println(Arrays.toString(objs));
        }*/

        //6.聚合函数：count(*),sum(字段)，avg(字段名),max(字段),min(字段)
        /*String hql="select count(*) from Employee";
        Query query=session.createQuery(hql);
        long count=(long) query.uniqueResult();
        System.out.println("count="+count);*/

        /*String hql="select sum(age) from Employee";
        Query query=session.createQuery(hql);
        long sum=(long) query.uniqueResult();
        System.out.println("sum="+sum);*/

        /*String hql="select avg(age) from Employee";
        Query query=session.createQuery(hql);
        double avg=(double) query.uniqueResult();
        System.out.println("avg="+avg);*/

        /*String hql="select max(age) from Employee";
        Query query=session.createQuery(hql);
        int max=(int) query.uniqueResult();
        System.out.println("max="+max);*/

        /*String hql="select min(age) from Employee";
        Query query=session.createQuery(hql);
        int min=(int) query.uniqueResult();
        System.out.println("min="+min);*/

        /*String hql="select avg(age) from Employee";
        Query query=session.createQuery(hql);
        Object obj=(Object) query.uniqueResult();
        System.out.println("obj="+obj);*/

        /*//7.查询单个属性
        String hql="select name from Employee";
        Query query=session.createQuery(hql);
        List<Object> list=query.list();
        System.out.println("list="+list);*/

        //8.查询多个属性 
        //A:使用对象数组接收查询到的数据
        /*String hql="select id,name,age from Employee";
        Query query=session.createQuery(hql);
        List<Object[]> list=query.list();
        for(Object[] objs:list){
            System.out.println("objs="+Arrays.toString(objs));
        }
        */

        //B:使用List集合接收多个属性的值
        /*String hql="select new List(id,name,age) from Employee";
        Query query=session.createQuery(hql);
        List<List<Object>> list=query.list();
        for(List<Object> objs:list){
            System.out.println(objs);
        }*/

        //C:使用Map接收多个属性的值,此时建议一定要给查询的列起别名，否则键就是从0开始依次递增的数字作为key
        /*String hql="select new Map(id as id,name as name,age as age) from Employee";
        Query query=session.createQuery(hql);
        List<Map<String,Object>> list=query.list();
        for(Map<String,Object> objs:list){
            System.out.println(objs);
        }*/

        //D:使用对象接收
        //String hql="select new Employee(id,name,age) from Employee";
        //String hql="select e from Employee e";
        /*String hql="select new Employee(name,age) from Employee";
        Query query=session.createQuery(hql);
        List<Employee> list=query.list();
        for(Employee e:list){
            System.out.println(e);
        }*/

        //9.关联查询
        //String hql="select e.name,d.name from Employee e inner join Department d";//错误写法
        /*String hql="select e.name,d.name from Employee e inner join e.department d";//正确写法
        Query query=session.createQuery(hql);
        List<Object[]> list=query.list();
        for(Object[] objs:list){
            System.out.println(Arrays.toString(objs));
        }*/

        //String hql="select new Map(e.name as empName,d.name as deptName) from Employee e inner join e.department d";//正确写法
        //String hql="select new Map(e.name as empName,d.name as deptName) from Employee e left join e.department d";//正确写法
        /*String hql="select new Map(e.name as empName,d.name as deptName) from Employee e right join e.department d";//正确写法
        //String hql="select new Map(e.name as empName,d.name as deptName) from Employee e full join e.department d";//mysql5.x不支持全连接
        Query query=session.createQuery(hql);
        List<Map<String,Object>> list=query.list();
        for(Map<String,Object> map:list){
            System.out.println(map);
        }*/



        System.out.println("=======================");


        //使用hql做增删改操作必须显示启动事务和提交事务，否则操作失败
        //A:做插入操作,需要注意：目前hql不支持insert into 表(字段列表) values  (值列表)
        /*String hql="insert into Employee values(null,?,?)";
        Query query=session.createQuery(hql);
        query.setString(0,"小梦");
        query.setInteger(1,18);*/

        //类上sql:insert into t_employee(name,age,depart_id) select name,age,depart_id from  t_employee;
        //B:但hql支持insert into .... select 形式的插入

        /*Transaction transaction=session.beginTransaction();
        String hql="insert into Employee(name,age) select name,age from Employee";
        Query query=session.createQuery(hql);
        //返回受影响的行数
        int count=query.executeUpdate();
        System.out.println("count="+count);
        transaction.commit();
        */

        //C:修改操作
        /*Transaction transaction=session.beginTransaction();
        String hql="update Employee set name=:name,age=:age where id=:id";
        Query query=session.createQuery(hql);
        query.setString("name","小龙女");
        query.setInteger("age", 24);
        query.setInteger("id", 55);
        //返回受影响的行数
        int count=query.executeUpdate();
        System.out.println("count="+count);
        transaction.commit();*/

        //D:删除操作
        Transaction transaction=session.beginTransaction();
        String hql="delete from  Employee where id in (52,53,54,55)";
        Query query=session.createQuery(hql);
        //返回受影响的行数
        int count=query.executeUpdate();
        System.out.println("count="+count);
        transaction.commit();




    }


    private static void add() {
        Session session=HibernateUtils.getSession();

        try{
            Transaction transaction=session.beginTransaction();


            Employee e1=new Employee("小丽", 20);
            Department d1=new Department("教学部");
            e1.setDepartment(d1);
            session.persist(e1);

            Employee e2=new Employee("小龙", 20);
            e2.setDepartment(d1);
            session.persist(e2);

            Employee e3=new Employee("赵丽颖", 18);
            Department d2=new Department("影视部");
            e3.setDepartment(d2);
            session.persist(e3);

            Employee e4=new Employee("宇文玥", 18);
            e4.setDepartment(d2);
            session.persist(e4);


            Employee e5=new Employee("宇文坏", 18);
            session.persist(e5);

            Employee e6=new Employee(null, 18);
            session.persist(e6);


            Department d3=new Department("市场部");
            session.persist(d3);

            Department d4=new Department(null);
            session.persist(d4);


            transaction.commit();
        }finally{
            HibernateUtils.close(session);
        }
    }

}

以下只有查询语句而无实体类

    /*//1.查询所有
        String hql="from Student";
        Query query=session.createQuery(hql);
        List<Student> students=query.list();
        System.out.println(students);*/

        //2.根据where条件查询
        /*String hql="from Student where name like ? and age>?";
        Query query=session.createQuery(hql);
        String condition="晓";
        query.setString(0, "%"+condition+"%");
        query.setInteger(1, 20);
        List<Student> students=query.list();
        System.out.println(students);*/

        //3.查询所有实体
        //String hql="from com.hsj.domain.BaseEntity";
        /*String hql="select id from com.hsj.domain.BaseEntity";
        Query query=session.createQuery(hql);
        List<Object> ids=query.list();
        System.out.println(ids);*/

        /*String hql="select id,name from com.hsj.domain.BaseEntity";
        Query query=session.createQuery(hql);
        List<Object[]> list=query.list();
        for(Object[] objs:list){
            System.out.println(Arrays.toString(objs));
        }*/


        //当前数据库中所有注册过的实体对象对应的数据
        String hql="from java.lang.Object";
        Query query=session.createQuery(hql);
        List<Object> list=query.list();
        for(Object obj:list){
            System.out.println(obj);
        }

    //使用as 和where 关键字
        //String hql="select s from Student as s  where s.name like '%晓%'";

        //String hql="select s from Student as s  where s.name ='童薇学生'";
        //String hql="select s from Student as s  where s.name !='童薇学生'";
        //String hql="select s from Student as s  where s.name <>'童薇学生'";
        //String hql="select s from Student as s  where s.age >22";
        //String hql="select s from Student as s  where s.age <22";

        //between min and max:包括min和max的
        //String hql="select s from Student as s  where s.age between 22 and 25";
        //String hql="select s from Student as s  where s.age  not between 22 and 25";
        //String hql="select s from Student as s  where s.age in (20,22)";
        //String hql="select s from Student as s  where s.age not in (20,22)";

        //String hql="select s from Student as s  where s.email = null";//hql特有的写法,sql中这样写错误
        //String hql="select s from Student as s  where s.email is null";//正确的写法

        //String hql="select s from Student as s  where s.email != null";//hql特有的写法,sql中这样写错误
        //String hql="select s from Student as s  where s.email <> null";//hql特有的写法,sql中这样写错误

        //is not null是判断不为null的，但没有not is null的写法。
        //String hql="select s from Student as s  where s.email not is  null";//错误的写法
        String hql="select s from Student as s  where s.email is not null";//正确的写法
        Query query=session.createQuery(hql);
        List<Student> list=query.list();
        for(Student stu:list){
            System.out.println(stu);
        }

/*String hql="select new Student(name,sex,age,email)  from Student";
        Query query=session.createQuery(hql);
        List<Student> students= query.list();
        for(Student student:students){
            System.out.println(student);
        }*/

        /*//String hql="select upper(email)  from Student";
        //String hql="select lower(upper(email))  from Student";
        //String hql="select lower(upper(email))  from Student";
        String hql="select distinct email  from Student";
        Query query=session.createQuery(hql);
        List<Object> list= query.list();
        for(Object obj:list){
            System.out.println(obj);
        }*/


        /*String hql="select new List(name,age,sex)  from Student";
        Query query=session.createQuery(hql);
        List<List<Object>> list= query.list();
        for(List<Object> obj:list){
            System.out.println(obj);
        }*/

        /*//使用Map时必须给查询的列起别名，如果不起别名则使用索引值从0开始作为键，但如果其别名必须是as关键字
        String hql="select new Map(name as 姓名,age as 年龄,sex  as 性别)  from Student";
        Query query=session.createQuery(hql);
        List<Map<String,Object>> list= query.list();
        for(Map<String,Object> map:list){
            System.out.println(map);
        }*/

/*//做增删改操作必须启动事务并提交，否则无效
Transaction transaction=session.beginTransaction();
//hql语句不支持insert …. value…形式的语句，但支持insert into …. select …..
//String hql=”insert into Student(id,name,sex,age,email) values (null,’玲玲’,’女’,20,’hsj@63.com’,1)”;

    //insert into 表名(字段列表) select 字段列表 from 表名  where 条件
    //String hql="insert into  Student(name,sex,age,email) select name,sex,age,email from Student";
    Query query=session.createQuery(hql);
    //返回本次操作受影响的行数
    int count=query.executeUpdate();
    System.out.println("count="+count);
    transaction.commit();*/