packagecom.wlh.hibernate;
importjava.text.SimpleDateFormat;
importjava.util.Iterator;
importjava.util.List;
importjunit.framework.TestCase;
importorg.hibernate.Query;
importorg.hibernate.Session;
publicclassSimpleConditionQueryTestextendsTestCase{
publicvoidtestQuery1() {
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);
}
}
publicvoidtestQuery2() {
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);
}
}
publicvoidtestQuery3() {
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);
}
}
publicvoidtestQuery4() {
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);
}
}
publicvoidtestQuery5() {
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);
}
}
publicvoidtestQuery6() {
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);
}
}
publicvoidtestQuery7() {
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",newObject[]{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);
}
}
publicvoidtestQuery8() {
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);
}
}
publicvoidtestQuery9() {
Session session =null;
try{
session = HibernateUtils.getSession();
session.beginTransaction();
[b]//找出某个(时间段)入学的学生
查询2007-01-01到2007-01-20创建的学生[/b]
SimpleDateFormat sdf=newSimpleDateFormat("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);
}
}
}