Hibernate笔记：HQL查询总结（二）——条件查询
条件查询
1.拼字符串
where条件后面，可以用字符串连接组成条件
| String hql = " select s.id, s.name from Student s where s.name like '%1%'"; Query query = session.createQuery(hql); List list = query.list(); for (Iterator iter=students.iterator(); iter.hasNext();) { Object[]obj=(Object[])iter.next(); System.out.println(obj[0]+","+obj[1]); } |
2.可以采用 ？来传递参数（索引从0开始）
（1）分开设值
//条件查询，参数索引值从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()); } |
（2）方法链
List students = session.createQuery("select s.id, s.name from Student s where s.name like ?") .setParameter(0,"%1%") .list();for(Iterator iter=students.iterator(); iter.hasNext();) { Object[]obj=(Object[])iter.next(); System.out.println(obj[0]+","+obj[1]); } |
3.使用 ：参数名称的方式传递参数值
//条件查询，自定义索引名(参数名):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); //第3种方式 //query = query.setParameter("username", "name1").setParameter //("password", "password1"); List<Users> list = query.list(); for(Users users : list){ System.out.println(users.getId()); } |
4.支持谓词in，需要使用setParameterList进行参数传递
List students = session.createQuery("select s.id, s.name from Student s where s.id in(:myids)") .setParameterList("myids",newObject[]{1,2,3,4,5}) .list(); for(Iteratoriter=students.iterator();iter.hasNext();){ Object[]obj=(Object[])iter.next(); System.out.println(obj[0]+","+obj[1]); } |
5.在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(Iteratoriter=students.iterator();iter.hasNext();){ Object[]obj=(Object[])iter.next(); System.out.println(obj[0]+","+obj[1]); } |
6.查询时间段的数据
http://blog.sina.com.cn/s/blog_59c701350100nkuk.html
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-0100:00:00")) .setParameter(1,sdf.parse("2010-12-3123:59:59")) .list(); for(Iteratoriter=students.iterator();iter.hasNext();){ Object[]obj=(Object[])iter.next(); System.out.println(obj[0]+","+obj[1]); } |