Hibernate的HQL总结

HQL建立于对象的概念上,与MYSQL 的SQL语句有点不太一样,但是,大部分还是一样!现收集网络资源以供以后方便查找!
以个普通Users表为例: 
建表语句: 
CREATE DATABASE `interceptor`


CHARACTER SET 'utf8';


USE `interceptor`;


DROP TABLE IF EXISTS `users`;






create table users(


id int auto_increment primary key,


name varchar(10) not null,


passwd varchar(10) not null


)


ENGINE=InnoDB DEFAULT CHARSET=utf8;


User.java: 
package com.domain;
public class Users implements java.io.Serializable {


private Integer id;


private String name;


private String passwd;


public Users() {


}


public Users(String name, String passwd) {


this.name = name;


this.passwd = passwd;


}


public Integer getId() {


return this.id;


}


public void setId(Integer id) {


this.id = id;


}


public String getName() {


return this.name;


}


public void setName(String name) {


this.name = name;


}


public String getPasswd() {


return this.passwd;


}


public void setPasswd(String passwd) {


this.passwd = passwd;


}


User.hbm.xml: 




<?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.domain.Users" table="users" catalog="interceptor">


<id name="id" type="java.lang.Integer">


<column name="id" />


<generator class="native" />


</id>


<property name="name" type="java.lang.String">


<column name="name" length="10" not-null="true" />


</property>


<property name="passwd" type="java.lang.String">


<column name="passwd" length="10" not-null="true" />


</property>


</class>


</hibernate-mapping>


1. 查询整个映射对象所有字段 

//直接from查询出来的是一个映射对象,即:查询整个映射对象所有字段

String hql = "from Users";

Query query = session.createQuery(hql);

List<Users> users = query.list();

for(Users user : users){

System.out.println(user.getName() + " : " + user.getPasswd() + " : " + user.getId());
}


输出结果为:

name1 : password1 : 1

name2 : password2 : 2

name3 : password3 : 3


2.查询单个字段 
String hql = " select name from Users";
Query query = session.createQuery(hql);
List<String> list = query.list();
for(String str : list){
System.out.println(str);
}


输出结果为:
name1

name2

name3

3.查询其中几个字段 

String hql = " select name,passwd from Users";
Query query = session.createQuery(hql);
//默认查询出来的list里存放的是一个Object数组
List<Object[]> list = query.list();
for(Object[] object : list){
String name = (String)object[0];
String passwd = (String)object[1];
System.out.println(name + " : " + passwd);

}


输出结果为:

name1 : password1

name2 : password2

name3 : password3


4.修改默认查询结果(query.list())不以Object[]数组形式返回,以List形式返回 
//查询其中几个字段,添加new list(),注意list里的l是小写的。也不需要导入包,这样通过query.list()出来的list里存放的不再是默认的Object数组了,而是List集合了

String hql = " select new list(name,passwd) from Users";

Query query = session.createQuery(hql);

//默认查询出来的list里存放的是一个Object数组,但是在这里list里存放的不再是默认的Object数组了,而是List集合了

List<List> list = query.list();

for(List user : list){


String name = (String)user.get(0);

String passwd = (String)user.get(1);

System.out.println(name + " : " + passwd);

}


输出结果为:

name1 : password1

name2 : password2

name3 : password3


5.修改默认查询结果(query.list())不以Object[]数组形式返回,以Map形式返回 
//查询其中几个字段,添加new map(),注意map里的m是小写的。也不需要导入包,这样通过query.list()出来的list里存放的不再是默认的Object数组了,而是map集合了

String hql = " select new map(name,passwd) from Users";

Query query = session.createQuery(hql);

//默认查询出来的list里存放的是一个Object数组,但是在这里list里存放的不再是默认的Object数组了,而是Map集合了

List<Map> list = query.list();

for(Map user : list){

//一条记录里所有的字段值都是map里的一个元素,key是字符串0,1,2,3....,value是字段值

//如果将hql改为:String hql = " select new map(name as username,passwd as password) from Users";,那么key将不是字符串0,1,2...了,而是"username","password"了

String name = (String)user.get("0");//get("0");是get(key),注意:0,1,2...是字符串,而不是整形

String passwd = (String)user.get("1");

System.out.println(name + " : " + passwd);


}

输出结果为:

name1 : password1

name2 : password2

name3 : password3


6.修改默认查询结果以Set形式返回,但是因为Set里是不允许有重复的元素,所以:username和password的值不能相同。只需将hql改为:String
hql = " select new set(name,passwd) from Users";

7.修改默认查询结果(query.list())不以Object[]数组形式返回,以自定义类型返回 
自定义类:
package com.domain;
public class MyUser {

private String username;

private String password;

//因为:String hql = " select new  com.domain.MyUser(name,passwd) from Users";所以必须要有接受2个参数的构造函数

public MyUser(String username,String password){

this.username = username;

this.password = password;

}


public String getUsername() {

return username;

}


public void setUsername(String username) {

this.username = username;

}


public String getPassword() {

return password;

}


public void setPassword(String password) {

this.password = password;

}

}


String hql = "from Users";中的Users类也是必须加包名的,但是因为再Users.hbm.xml里<hibernate-mapping auto-import="true"> auto-import默认值为true(所以auto-import属性也可以不写),自动导入了

String hql = " select new  com.domain.MyUser(name,passwd) from Users";

Query query = session.createQuery(hql);

//默认查询出来的list里存放的是一个Object数组,但是在这里list里存放的不再是默认的Object数组了,而是MyUser对象了

List<MyUser> myUsers = query.list();


for(MyUser myUser : myUsers){

String name = myUser.getUsername();

String passwd = myUser.getPassword();

System.out.println(name + " : " + passwd);

}


输出结果为:

name1 : password1

name2 : password2

name3 : password3

8:条件查询 
//条件查询,参数索引值从0开始,索引位置。通过setString,setParameter设置参数

String hql = "from Users where name=? and passwd=?";

Query query = session.createQuery(hql);

//第1种方式

// query.setString(0, "name1");


// query.setString(1, "password1");


//第2种方式


query.setParameter(0, "name1",Hibernate.STRING);


query.setParameter(1, "password1",Hibernate.STRING);


List<Users> list = query.list();


for(Users users : list){


System.out.println(users.getId());


}


//条件查询,自定义索引名(参数名):username,:password.通过setString,setParameter设置参数


String hql = "from Users where name=:username and passwd=:password";


Query query = session.createQuery(hql);


//第1种方式


// query.setString("username", "name1");


// query.setString("password", "password1");


//第2种方式,第3个参数确定类型


query.setParameter("username", "name1",Hibernate.STRING);


query.setParameter("password", "password1",Hibernate.STRING);


List<Users> list = query.list();


for(Users users : list){


out.println(users.getId());


}






//条件查询,通过setProperties设置参数


String hql = "from Users where name=:username and passwd=:password";


Query query = session.createQuery(hql);


//MyUser类的2个属性必须和:username和:password对应


MyUser myUser = new MyUser("name1","password1");


query.setProperties(myUser);


List<Users> list = query.list();


for(Users users : list){


System.out.println(users.getId());


}
支持谓词in,需要使用setParameterList进行参数传递
List students = session.createQuery("select s.id, s.name from Student s where s.id in(:myids)").setParameterList("myids", new Object[]{1, 2, 3, 4, 5}).list();
for (Iterator iter=students.iterator(); iter.hasNext();) { 
    Object[] obj = (Object[])iter.next();  
    System.out.println(obj[0] + "," + obj[1]);
}
在HQL中使用数据库的函数
List students = session.createQuery("select s.id, s.name from Student s where date_format(s.createTime, '%Y-%m')=?").setParameter(0, "2010-12").list();
for (Iterator iter=students.iterator(); iter.hasNext();) { 
    Object[] obj = (Object[])iter.next();  
    System.out.println(obj[0] + "," + obj[1]);
}
 
6.查询时间段的数据
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
List students = session.createQuery("select s.id, s.name from Student s where s.createTime between ? and ?").setParameter(0, sdf.parse("2010-12-01 00:00:00")).setParameter(1, sdf.parse("2010-12-31 23:59:59")).list();
for (Iterator iter=students.iterator(); iter.hasNext();) { 
    Object[] obj = (Object[])iter.next();  
    System.out.println(obj[0] + "," + obj[1]); 
}
原生sql测试
List students = session.createSQLQuery("select * from t_student").list();
多表连接
测试查询有配置多对多关系的对象
    @SuppressWarnings("unchecked")
    public void test1() 
        Session session = sessionFactory.openSession();
        session.beginTransaction();
        String hql = "select s from Student s join s.courses c where s.name like '%micmiu.com%' and c.name ='math'";
        String hql2 = "select s from Student s,Course c where c.id in elements (s.courses) and s.name like '%micmiu.com%' and c.name ='math'";
        Query query = session.createQuery(hql);
        List<Student> list = query.list();
        System.out.println("----------- size:" + list.size());
        Assert.assertEquals(1, list.size());
        for (Student s : list) {
            Assert.assertEquals("micmiu.com", s.getName());
            System.out.println(s);
        }
        session.getTransaction().commit();
        session.close();
    }
测试查询无配置多对多关系的对象
    @SuppressWarnings("unchecked")
    public void test2() {
        Session session = sessionFactory.openSession();
        session.beginTransaction();
        String hql = "select distinct c from Student s,Course c where c.id in elements (s.courses) and s.name like '%micmiu.com%' and c.name ='math'";
        Query query = session.createQuery(hql);
        List<Course> list = query.list();
        System.out.println("----------- size:" + list.size());
        Assert.assertEquals(1, list.size());
        for (Course c : list) {
            Assert.assertEquals("math", c.getName());
            System.out.println(c);
        }
        session.getTransaction().commit();
        session.close();
    }
}

order by 语句
 @Test  
    public void testHQL_3() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        Query query = s.createQuery("from Category c order by c.name desc");  
        List<Category> cs = query.list();  
        for(Category c : cs) {  
            System.out.println(c.getName());  
        }  
        s.getTransaction().commit();  
    }  
  
排序desc/asc
   @Test  
    public void testHQL_4() {  
        Session session = sf.getCurrentSession();  
        session.beginTransaction();  
        Query q = session.createQuery("select distinct c from Category c order by c.name desc");  
        List<Category> cs = (List<Category>)q.list();  
        for(Category c : cs) {  
            System.out.println(c.getName());  
        }  
        session.getTransaction().commit();  
    } 
实体类中有关系的查询(对象导航)
 @Test  
    public void testHQL_10() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        Query q = s.createQuery("from Topic t where t.category.id = 1");  
        List<Topic> ts = (List<Topic>)q.list();  
        for(Topic t : ts) {  
            System.out.println(t.getId() + " = " + t.getCategory().getName());  
        }  
        s.getTransaction().commit();  
    }  
      
    @Test  
    public void testHQL_11() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        Query q = s.createQuery("from Msg m where m.topic.category.id = 1");  
        List<Msg> ms = (List<Msg>)q.list();  
        for(Msg m : ms) {  
            System.out.println(m.getCont());  
        }  
        s.getTransaction().commit();  
    }  




NOT NULL 查询条件

@Test  
    public void testHQL_18() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        Query q = s.createQuery("from Msg m where m.cont is not null");  
        List<Msg> cs = (List<Msg>)q.list();  
        for(Msg c : cs) {  
            System.out.println(c.getId() + " - " + c.getCont());  
        }  
        s.getTransaction().commit();  
    }  
Query q = session.createQuery("from Msg m where m.cont is not null");
Query q = session.createQuery("from Topic t where t.msgs is empty")  


Having查询
 @Test  
    public void testHQL_25() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        Query q = s.createQuery("select t.title , count(*) from Topic t group by t.title having count(*) >= 1");  
        List<Object[]> objs = (List<Object[]>)q.list();  
        for(Object[] obj : objs) {  
            System.out.println(obj[0] + " " + obj[1]);  
        }  
        s.getTransaction().commit();  
    }  
嵌套查询
 @Test  
    public void testHQL_26() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        Query q = s.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)");  
        List<Topic> ts = q.list();  
        for(Topic t : ts) {  
            System.out.println(t.getId() + " " + t.getTitle());  
        }  
        s.getTransaction().commit();  
    }  
函数查询使用
 @Test  
    public void testHQL_26() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        Query q = s.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)");  
        List<Topic> ts = q.list();  
        for(Topic t : ts) {  
            System.out.println(t.getId() + " " + t.getTitle());  
        }  
        s.getTransaction().commit();  
    }  
      
    @Test  
    public void testHQL_27() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        Query q = s.createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id , 2) = 0)");  
        List<Topic> ts = (List<Topic>)q.list();  
        for(Topic t : ts) {  
            System.out.println(t.getId() + "  " + t.getTitle());  
        }  
        s.getTransaction().commit();  
    }  
      
    //exists 可以用in来实现,如果exists 执行效率高  
    @Test  
    public void testHQL_28() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        //Query q = s.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id = t.id)");  
        Query q = s.createQuery("from Topic t where t.id in (select t.id from Msg m where m.topic.id != t.id)");  
        List<Topic> ts = (List<Topic>)q.list();  
        for(Topic t : ts) {  
            System.out.println(t.getId() + " " + t.getTitle());  
        }  
        s.getTransaction().commit();  
    }  
      
    @Test  
    public void testHQL_29() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        Query q = s.createQuery("update Topic t set t.title = upper(t.title)");  
        q.executeUpdate();  
        q = s.createQuery("from Topic");  
        List<Topic> ts = (List<Topic>)q.list();  
        for(Topic t : ts) {  
            System.out.println(t.getId() + " " + t.getTitle());  
        }  
        s.createQuery("update Topic t set t.title = lower(t.title)" ).executeUpdate();  
        s.getTransaction().commit();  
    }  
      
    @Test  
    public void testHQL_30() {  
        Session s = sf.getCurrentSession();  
        s.beginTransaction();  
        SQLQuery sq = s.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);  
        List<Category> cs = (List<Category>)sq.list();  
        for(Category c : cs) {  
            System.out.println(c.getId() + " " + c.getName());  
        }  
        s.getTransaction().commit();  
    }  

other
Java代码  
Query q = session.createQuery("select lower(t.title),"  
                + "upper(t.title)," + "trim(t.title),"  
                + "concat(t.title, '***')," + "length(t.title)"  
                + " from Topic t ");  
  
Query q = session.createQuery("select abs(t.id)," + "sqrt(t.id),"  
                + "mod(t.id, 2)" + " from Topic t ");  
  
Query q = session  
                .createQuery("select current_date, current_time, current_timestamp, t.id from Topic t");  
  
Query q = session  
                .createQuery("from Topic t where t.createDate < :date");  
        q.setParameter("date", new Date());  
  
Query q = session  
                .createQuery("select t.title, count(*) from Topic t group by t.title having count(*) >= 1");  
  
Query q = session  
                .createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)");  
  
Query q = session  
                .createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id, 2)= 0) ");  
  
// 用in 可以实现exists的功能  
        // 但是exists执行效率高  
        // t.id not in (1)  
        Query q = session  
                .createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id=t.id)");  
        // Query q =  
        // session.createQuery("from Topic t where exists (select m.id from Msg m where m.topic.id=t.id)")  




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值