条件查询(重要)
* 可以采用拼字符串的方式传递参数
* 可以采用 ?号的方式传递参数
* 可以采用 :参数名的方式传递参数
* 如果传递多个参数,使用setParameterList来传递
* 在hql中可以使用数据库的函数,如:date_format
* 参见:SimpleConditionQueryTest.java
[color=red]"like" 和 "=" 都可以通过 ": 参数名" 或者 "?" 接参数
如下: like :参数名 ,like ? , =:参数名 , =?[/color]
* 可以采用拼字符串的方式传递参数
* 可以采用 ?号的方式传递参数
* 可以采用 :参数名的方式传递参数
* 如果传递多个参数,使用setParameterList来传递
* 在hql中可以使用数据库的函数,如:date_format
* 参见:SimpleConditionQueryTest.java
[color=red]"like" 和 "=" 都可以通过 ": 参数名" 或者 "?" 接参数
如下: like :参数名 ,like ? , =:参数名 , =?[/color]
package com.wlh.hibernate;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.List;
import junit.framework.TestCase;
import org.hibernate.Query;
import org.hibernate.Session;
public class SimpleConditionQueryTest extends TestCase{
public void testQuery1() {
Session session = null;
try {
session = HibernateUtils.getSession();
session.beginTransaction();
//可以拼字符串
List students=session.createQuery("select id,name from Student where name like '%1%'").list();
for(Iterator iter=students.iterator();iter.hasNext();){
Object[] o=(Object[])iter.next();
System.out.println(o[0]+","+o[1]);
}
session.getTransaction().commit();
}catch(Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally {
HibernateUtils.closeSession(session);
}
}
public void testQuery2() {
Session session = null;
try {
session = HibernateUtils.getSession();
session.beginTransaction();
[b]//可以使用 ?号方式传递参数
//参数的索引从0开始
//传递的参数值不用单引号引起来[/b]
/*Query query=session.createQuery("select id,name from Student where name like ?");
query.setParameter(0, "%1%");
List students=query.list();*/
List students=session.createQuery("select id,name from Student where name like ? ").setParameter(0, "%1%").list();
for(Iterator iter=students.iterator();iter.hasNext();){
Object[] o=(Object[])iter.next();
System.out.println(o[0]+","+o[1]);
}
session.getTransaction().commit();
}catch(Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally {
HibernateUtils.closeSession(session);
}
}
public void testQuery3() {
Session session = null;
try {
session = HibernateUtils.getSession();
session.beginTransaction();
[b]//like :参数名[/b]
Query query=session.createQuery("select id,name from Student where name like :myname");
query.setParameter("myname", "%1%");
List students=query.list();
for(Iterator iter=students.iterator();iter.hasNext();){
Object[] o=(Object[])iter.next();
System.out.println(o[0]+","+o[1]);
}
session.getTransaction().commit();
}catch(Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally {
HibernateUtils.closeSession(session);
}
}
public void testQuery4() {
Session session = null;
try {
session = HibernateUtils.getSession();
session.beginTransaction();
[color=red]//=?[/color]
Query query=session.createQuery("select id,name from Student where id=?");
query.setParameter(0,10);
List students=query.list();
for(Iterator iter=students.iterator();iter.hasNext();){
Object[] o=(Object[])iter.next();
System.out.println(o[0]+","+o[1]);
}
session.getTransaction().commit();
}catch(Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally {
HibernateUtils.closeSession(session);
}
}
public void testQuery5() {
Session session = null;
try {
session = HibernateUtils.getSession();
session.beginTransaction();
[b][color=red]//=:参数名[/color][/b]
Query query=session.createQuery("select id,name from Student where id=:myid");
query.setParameter("myid",10);
List students=query.list();
for(Iterator iter=students.iterator();iter.hasNext();){
Object[] o=(Object[])iter.next();
System.out.println(o[0]+","+o[1]);
}
session.getTransaction().commit();
}catch(Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally {
HibernateUtils.closeSession(session);
}
}
public void testQuery6() {
Session session = null;
try {
session = HibernateUtils.getSession();
session.beginTransaction();
[b]// like :参数名
// = :参数名 [/b]
Query query=session.createQuery("select id,name from Student where name like :myname and id =:myid");
query.setParameter("myname", "%1%");
query.setParameter("myid", 11);
List students=query.list();
for(Iterator iter=students.iterator();iter.hasNext();){
Object[] o=(Object[])iter.next();
System.out.println(o[0]+","+o[1]);
}
session.getTransaction().commit();
}catch(Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally {
HibernateUtils.closeSession(session);
}
}
public void testQuery7() {
Session session = null;
try {
session = HibernateUtils.getSession();
session.beginTransaction();
[b][color=red]//支持in,需要使用setPramaterList进行参数传递
//传入的参数必须是对象数组[/color][/b]
Query query=session.createQuery("select id,name from Student where id in(:myids)");
query.setParameterList("myids", new Object[]{1,2,3,4,5});
List students=query.list();
for(Iterator iter=students.iterator();iter.hasNext();){
Object[] o=(Object[])iter.next();
System.out.println(o[0]+","+o[1]);
}
session.getTransaction().commit();
}catch(Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally {
HibernateUtils.closeSession(session);
}
}
public void testQuery8() {
Session session = null;
try {
session = HibernateUtils.getSession();
session.beginTransaction();
[b]//查询2007年2月创建的学生(模糊查询)[/b]
/*Query query=session.createQuery("select id,name from Student where date_format(createTime,'%Y-%m')=?");
query.setParameter(0, "2007-02");*/
Query query=session.createQuery("select id,name from Student where substring(createTime,1,7)=?");
query.setParameter(0, "2007-02");
List students=query.list();
for(Iterator iter=students.iterator();iter.hasNext();){
Object[] o=(Object[])iter.next();
System.out.println(o[0]+","+o[1]);
}
session.getTransaction().commit();
}catch(Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally {
HibernateUtils.closeSession(session);
}
}
public void testQuery9() {
Session session = null;
try {
session = HibernateUtils.getSession();
session.beginTransaction();
[b]//找出某个(时间段)入学的学生
查询2007-01-01到2007-01-20创建的学生[/b]
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Query query=session.createQuery("select id,name from Student where createTime between ? and ? order by id");
query.setParameter(0, sdf.parse( "2007-01-10 00:00:00"));
query.setParameter(1, sdf.parse( "2007-01-20 23:59:59"));
List students=query.list();
for(Iterator iter=students.iterator();iter.hasNext();){
Object[] o=(Object[])iter.next();
System.out.println(o[0]+","+o[1]);
}
session.getTransaction().commit();
}catch(Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally {
HibernateUtils.closeSession(session);
}
}
}