一、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欢迎来访