public class Classes { /*班级ID*/ private int id; /*班级名称*/ private String name; /*班级和学生的关系*/ private Set students; //省略setter和getter方法 }
Student.java:
public class Student { /*学生ID*/ private int id; /*学生姓名*/ private String name; /*学生和班级的关系*/ private Classes classes; //省略setter和getter方法 }
Classes.hbm.xml:
Student.hbm.xml:
1.查询单一属性:
/*返回结果集属性列表,元素类型和实体类中的属性类型一致*/ 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); }
/*可以使用别名*/ 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]); }
5.查询实体对象:
/*返回的是实体对象类型的集合*/ List students = session.createQuery("from Student").list(); /*遍历*/ for (Iterator iter=students.iterator(); iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); }
注:查询实体可以直接使用from 类名的形式。
/*使用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]); }
/*链式编程*/ 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]); }
注:可以通过占位符的形式进行传参,这种方式可以防止SQL注入。
9.自定义参数的形式:
/*链式编程*/ 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]); }
注:like :myname冒号后面是没有空格的,否则会出错。
10.查询条件为in的形式:
[java] view plain copy /*采用in的方式,形参只要一个即可*/ 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]); }
注:in后面的括号中只要有一个形参即可,我们设置参数值的时候,可以通过对象数组就行传值。
11.使用数据库个性化函数:
/*查询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]); }
12.使用原生态的SQL语句:
/*使用select * 必须使用原生态的SQL语句,另外它类似于hql查询多个属性,所以返回的是一个对象数组类型的集合*/ List students = session.createSQLQuery("select * from t_student").list(); /*遍历*/ for (Iterator iter = students.iterator(); iter.hasNext();) { Object[] obj = (Object[]) iter.next(); System.out.println(obj[0] + ", " + obj[1]); }
/*分页查询,setFirstResult(1)表示从第一条数据开始查询;setMaxResult(2)表示每页显示2条数据*/ List students = session.createQuery("from Student") .setFirstResult(1) .setMaxResults(2) .list(); /*遍历*/ for (Iterator iter=students.iterator(); iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); }
14.导航查询
/*导航查询,s.classes.name从学生导航到班级在导航到班级名称(这是从多的一端导航到少的一端,反过来也可以)*/ List students = session.createQuery("from Student s where s.classes.name like '%2%'") .list(); /*遍历*/ for (Iterator iter=students.iterator(); iter.hasNext();) { Student student = (Student)iter.next(); System.out.println(student.getName()); }
/*查询语句*/ String hql = "select c.name, count(s) from Classes c join c.students s group by c.name order by c.name"; List students = session.createQuery(hql).list(); /*遍历*/ for (int i=0; i