hql 字符串where语句_Hibernate HQL示例四:条件查询

在

条件查询

* 可以拼字符串的形式传递参数

* 可以用过？来传递参数(注意索引值是从0开始的，跟jdbc不同，jdbc是从1开始的)

* 可以通过 ：参数名 来传递参数(即命名参数方式)

* 如果对应的参数值是多个，可以调用setParamterList()传方法开递

* 在HQL中可以使用数据库的函数，如date_format()

示例代码如下:

package com.bjsxt.hibernate;

import java.text.SimpleDateFormat;

import java.util.Iterator;

import java.util.List;

import org.hibernate.Query;

import org.hibernate.Session;

import junit.framework.TestCase;

public class SimpleConditionQueryTest extends TestCase {

public void testQuery1() {

Session session = null;

try {

session = HibernateUtils.getSession();

//条件查询，拼字符串

List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE '%1%'")

.list();

for (Iterator iter = students.iterator();iter.hasNext();) {

Object[] obj = (Object[])iter.next();

System.out.println(obj[0] + ", " + obj[1]);

}

}catch(Exception e) {

e.printStackTrace();

}finally {

HibernateUtils.closeSession(session);

}

}

public void testQuery2() {

Session session = null;

try {

session = HibernateUtils.getSession();

//条件查询，使用 ？ 的方式传递参数

Query query = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE ?");

//传递参数

//参数的索引是从0开始的

//传递的字符串，无需用''单引号括起来

query.setParameter(0, "%1%");

List students = query.list();

for (Iterator iter = students.iterator();iter.hasNext();) {

Object[] obj = (Object[])iter.next();

System.out.println(obj[0] + ", " + obj[1]);

}

}catch(Exception e) {

e.printStackTrace();

}finally {

HibernateUtils.closeSession(session);

}

}

public void testQuery3() {

Session session = null;

try {

session = HibernateUtils.getSession();

//条件查询，使用 ：参数名称 的方式传递参数

Query query = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE :myname");

//传递参数

query.setParameter("myname", "%1%");

List students = query.list();

for (Iterator iter = students.iterator();iter.hasNext();) {

Object[] obj = (Object[])iter.next();

System.out.println(obj[0] + ", " + obj[1]);

}

}catch(Exception e) {

e.printStackTrace();

}finally {

HibernateUtils.closeSession(session);

}

}

public void testQuery4() {

Session session = null;

try {

session = HibernateUtils.getSession();

//条件查询,因为setParameter方法返回Query接口，所以可以用省略的方式来查询

List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE :myname and s.id = :myid")

.setParameter("myname", "%1%")

.setParameter("myid", 15)

.list();

for (Iterator iter = students.iterator();iter.hasNext();) {

Object[] obj = (Object[])iter.next();

System.out.println(obj[0] + ", " + obj[1]);

}

}catch(Exception e) {

e.printStackTrace();

}finally {

HibernateUtils.closeSession(session);

}

}

public void testQuery5() {

Session session = null;

try {

session = HibernateUtils.getSession();

//条件查询,支持in，需要用setParameterList()进行参数传递

List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.id in(:myids)")

.setParameterList("myids", new Object[]{1, 3, 5})

.list();

for (Iterator iter = students.iterator();iter.hasNext();) {

Object[] obj = (Object[])iter.next();

System.out.println(obj[0] + ", " + obj[1]);

}

}catch(Exception e) {

e.printStackTrace();

}finally {

HibernateUtils.closeSession(session);

}

}

public void testQuery6() {

Session session = null;

try {

session = HibernateUtils.getSession();

//条件查询,支持in，需要用setParameter进行参数传递

//List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE " +

//"date_format(s.createTime, '%Y-%m') =?")

//.setParameter(0, "2007-01")

//.list();

List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE " +

"substring(s.createTime, 1, 7) =?")

.setParameter(0, "2007-01")

.list();

for (Iterator iter = students.iterator();iter.hasNext();) {

Object[] obj = (Object[])iter.next();

System.out.println(obj[0] + ", " + obj[1]);

}

}catch(Exception e) {

e.printStackTrace();

}finally {

HibernateUtils.closeSession(session);

}

}

public void testQuery7() {

Session session = null;

try {

session = HibernateUtils.getSession();

//条件查询,支持in，需要用setParameter进行参数传递

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE " +

"s.createTime between ? and ? ")

.setParameter(0, format.parseObject("2007-01-01"))

.setParameter(1, format.parseObject("2007-03-01"))

.list();

for (Iterator iter = students.iterator();iter.hasNext();) {

Object[] obj = (Object[])iter.next();

System.out.println(obj[0] + ", " + obj[1]);

}

}catch(Exception e) {

e.printStackTrace();

}finally {

HibernateUtils.closeSession(session);

}

}

}

分享到：

2010-05-05 01:24

浏览 2340

评论