mySQL中hql条件查询date_【HQL】属性查询、条件查询

单一属性：

//返回结果集属性列表，元素类型和实体类中的属性类型一致

List students = session.createQuery("select name from Student").list();for (Iterator iter=students.iterator(); iter.hasNext();) {

String name=(String)iter.next();

System.out.println(name);

}

多个属性：

//查询多个属性，返回对象数组集合//数组元素的类型与查询的属性类型一致//数组的长度与select中查询的属性个数一致

List students = session.createQuery("select id, name from Student").list();for (Iterator iter=students.iterator(); iter.hasNext();) {

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

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

}

多个属性查询,返回Student：

//可以使用hql返回Student对象//需要提供构造函数

List students = session.createQuery("select new Student(id, name) from Student").list();for (Iterator iter=students.iterator(); iter.hasNext();) {

Student student=(Student)iter.next();

System.out.println(student.getId()+ ", " +student.getName());

}

可以使用别名：

//可以使用别名

List students = session.createQuery("select s.id, s.name from Student s").list();for (Iterator iter=students.iterator(); iter.hasNext();) {

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

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

}

实体对象查询：

//返回Student对象的集合//可以忽select关键字

List students = session.createQuery("from Student").list();for (Iterator iter=students.iterator(); iter.hasNext();) {

Student student=(Student)iter.next();

System.out.println(student.getName());

}

//返回Student对象的集合//可以忽select关键字，可以加入别名

List students = session.createQuery("from Student s").list();for (Iterator iter=students.iterator(); iter.hasNext();) {

Student student=(Student)iter.next();

System.out.println(student.getName());

}

//返回Student对象的集合//如果使用select查询实体对象，必须使用别名

List students = session.createQuery("select s from Student s").list();for (Iterator iter=students.iterator(); iter.hasNext();) {

Student student=(Student)iter.next();

System.out.println(student.getName());

}

条件查询

//可以拼串

List students = session.createQuery("select s.id, s.name from Student s where s.name like '%0%'").list();for (Iterator iter=students.iterator(); iter.hasNext();) {

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

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

}

//可以使用？方式传递参数//索引从0开始，不同于jdbc从1开始//值能使用单引号引起来//List students = session.createQuery("select s.id, s.name from Student s where s.name like ?").list();//Query query = session.createQuery("select s.id, s.name from Student s where s.name like ?");//query.setParameter(0, "%0%");//List students = query.list();//方法链编程，建议采用此种方式

List students = session.createQuery("select s.id, s.name from Student s where s.name like ?")

.setParameter(0, "%0%")

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

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

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

}

//可以采用 :参数名 的方式传递参数

List students = session.createQuery("select s.id, s.name from Student s where s.name like :myname")

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

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

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

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

}

//采用 ？方式，查询学号为1,2,3,4,5的学生

List students = session.createQuery("select s.id, s.name from Student s where s.id in(?, ?, ?, ?, ?)")

.setParameter(0, 1)

.setParameter(1, 2)

.setParameter(2, 3)

.setParameter(3, 4)

.setParameter(4, 5)

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

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

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

}

//采用 :参数名 方式，查询学号为1,2,3,4,5的学生

List students = session.createQuery("select s.id, s.name from Student s where s.id in(:ids)")

.setParameterList("ids", 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]);

}

//查询2009-08的学生，可以调用mysql的日期格式化函数

List students = session.createQuery("select s.id, s.name from Student s where date_format(s.createTime, '%Y-%m')=?")

.setParameter(0, "2009-08")

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

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

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

}

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//查询2009-08-01 到2009-08-20的学生，可以调用mysql的日期格式化函数

List students = session.createQuery("select s.id, s.name from Student s where s.createTime between ? and ?")

.setParameter(0, sdf.parse("2009-08-01 00:00:00"))

.setParameter(1, sdf.parse("2009-08-20 23:59:59"))

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

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

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

}