HQL基础汇总

一、HQL简介

HQL全称是Hibernate Query Language。是一种面向对象(核心)的查询语言。SQL的操作对象是数据列、表等数据库数据,而HQL操作的是类、实例、属性。

 

二、HQL使用

1、实体查询form子句

如果from前面没有指定select内容,就是将整个User对象取出,封装到结果Query中。如果是得到单个结果,通过query.uniqueResult();方法得到;如果是多个结果,就会封装到List中,可以用query.list();来得到。

 

public List<User> getAllUsers(){

                //from子句

                Session session = new HibernateSessionFactory().getSession();

                String hql = "from com.demo.bean.User"//select * from user;

                Query query = session.createQuery(hql); 

                List<User> list = query.list()//list方法,将查询结果封装到List中,然后返回

                return list;

}

 

2、条件子句 where

例如:"form User u  where u.id = 1 " = "select * form user where id = 1"

 

3、按属性查询

(1)查询出一个String/或者其他类型的属性,会封装为List<Object>

        public List<String> getAllUsernames(){

                Session session = new HibernateSessionFactory().getSession();

                String hql = "select u.username from User as u where status = 0"//select * from user;

                Query query = session.createQuery(hql); 

                List<String> list = query.list()//返回出的List对象中,封装的对象的类型,看具体的情况而定的

                return list;

        }

 

(2)查询多个属性,会将属性封装为对象数组所组成的List<Object[]>

//一行记录(一组姓名和年龄)就是List中的一个元素,每个List的元素是一个Object[]来记录各种信息

                

        public List<Object[]> getAllUsernamesAndAge(){

                Session session = new HibernateSessionFactory().getSession();

                String hql = "select u.username,u.age from User as u where status = 0"

                Query query = session.createQuery(hql); 

                List<Object[]> list = query.list()//查询多个属性,将多个属性值封装为对象数组组成的List

                return list;

        }

        public static void main(String[] args) {

                UserDaoImpl ud = new UserDaoImpl();

                

                List<Object[]> list = ud.getAllUsernamesAndPassword();

                for(Object[] u : list){

                        System.out.println((String)u[0]);

                        System.out.println((Integer)u[1]);

                }

(3)查询多个属性,将得到的属性再次封装成对象,要求恰好有合适的构造函数,而且不要忘记给实体类加上空构造函数

 

        public List<User> getAllUsernamesAndAge(){

                Session session = new HibernateSessionFactory().getSession();

                //要求User恰好有u.username,u.age两个属性所构成的构造函数

                String hql = "select new User(u.username,u.age) from User as u where status = 0";

                Query query = session.createQuery(hql); 

                List<User> list = query.list()//查询多个属性,将多个属性值封装为对象数组组成的List

                return list;

        }

 

public class User {

        private Integer id//最好用Integer而不用int作为Identifier

        private String username;

        private String password;

        private int age;

        private int status;

        

        public User(){  //不要忘记给实体类加上空构造函数

                

        }

        public User(String username,int age){

                this.username=username;

                this.age=age;

        }

 

四、使用统计函数

String hql = "select count(*) from User where status = 0";

String hql = "select min(u.age) from User as u where status = 0";

String hql = "select count(*),min(u.age) from User as u where status = 0";

 

 

//查询出单个统计函数的值,结果存入query中,可以用uniqueResult()得到结果对象。

public void getSomeInfo1(){

                Session session = new HibernateSessionFactory().getSession();        

                String hql = "select count(*) from User as u where status = 0";

                Query query = session.createQuery(hql); 

                //List<Object[]> list = query.list();  //查询多个属性,将多个属性值封装为对象数组组成的List

                Object o  = query.uniqueResult();

                System.out.println((Integer)o);

        }

 

 

//同时查询多个统计函数的值,结果封装到List<Object[]>中,需要用query.list()方法得到结果list,然后取出第一个元素。第一个元素就是Object[],Object[0]和Object[1]分别是我们需要的信息

        public void getSomeInfo(){

                Session session = new HibernateSessionFactory().getSession();

                

                String hql = "select count(*),min(u.age) from User as u where status = 0";

                Query query = session.createQuery(hql); 

                

                List<Object[]> list = query.list();

                Object [] o = list.get(0);

                System.out.println(o[0]);

                System.out.println(o[1]);

        }        

 

Hql同样支持sql中的一些原生函数,比如upper(将结果全部大写),distinct(username),过滤掉重复字段 等

 

五、分组和排序

String hql = “select u.username form User as u order by u.age desc”  按年龄的降序排列取出姓名

String hql =”select count(user),user.age from User  group by age”; 

String hql =”select count(user),age from User group by age having count(user)>3”;

 

六、实体更新和删除

对数据库的修改操作,注意需要事务来实现

1、有条件删除

String hql = "delete from User where age<15";

 

        public void delSome(){

                Session session = new HibernateSessionFactory().getSession();

                String hql = "delete from User where age<15";

                Query query = session.createQuery(hql); 

                Transaction tr = session.beginTransaction();

                query.executeUpdate();

                tr.commit();

                session.close();

        }        

 

2、有条件的更新

        public void updateSome(){

                Session session = new HibernateSessionFactory().getSession();

                String hql = "update User set username='tom' where id=2";

                Query query = session.createQuery(hql); 

                Transaction tr = session.beginTransaction();

                query.executeUpdate();

                tr.commit();

                session.close();

        }

 

七、参数绑定

1、顺序占位符:用?来代表参数,用query.setXxx(0, ??); 给参数赋值,参数的序号从0开始

 

        public void updateSome(String username,int id){

                Session session = new HibernateSessionFactory().getSession();

                String hql = "update User set username=? where id=?";

                Query query = session.createQuery(hql);

                query.setString(0, username);  //参数的序号从0开始

                query.setInteger(1, id);

                Transaction tr = session.beginTransaction();

                query.executeUpdate();

                tr.commit();

                session.close();

        }

 

2、引用占位符:用“:占位字符串”实现

 

        public User getOneUser(String username,String password){

                String hql = "from User where username=:username and password =:password";

                Session session = new HibernateSessionFactory().getSession();

                Query query = session.createQuery(hql);

                query.setString("username", username);

                query.setString("password", password);

                User u = (User) query.uniqueResult();

                return u;

        }

 

八、派生列处理

查询一些统计信息还可以使用派生列的方式来实现:

例:经常需要得到User的人数

步骤1、将人数count属性放入User对象,但是user表中没有count列。

 

public class User {

        private Integer id//最好用Integer而不用int作为Identifier

        private String username;

        private String password;

        private int age;

        private int status;

        

        private int count//记录人数,这个属性在数据表中没有对应的列

        public int getCount() {

                return count;

        }

        public void setCount(int count) {

                this.count = count;

        }

 

 

步骤2、配置User.hbm.xml

加上<property name="count" formula="(select count(*) from user)"></property>,在此处设置一个子查询,将查询结果,注入count属性

 

<?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>

        <class name="com.demo.bean.User"  lazy="false" table="user">

                <id name="id" column="id">

                        <generator class="native"></generator>

                </id>

                <property name="username"></property>

                <property name="password"></property>

                <property name="age"></property>

                <property name="status"></property>

                <property name="count" formula="(select count(*) from user)"></property>

        </class>

</hibernate-mapping>

 

步骤3、获得人数的值

先获得User对象,通过User对象的getCount方法获得人数

        public static void main(String[] args) {

                UserDaoImpl ud = new UserDaoImpl();

                User u = ud.getOneUser();

                System.out.println(u.getCount());

        }

个人网站已经更新:www.stopping.top欢迎来访

展开阅读全文
©️2020 CSDN 皮肤主题: 创作都市 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值