12、Hibernate内存查询与数据库查询

set和map集合保存到数据库是无序的，但我们查询的时候希望带顺序查询，不是默认顺序，这就涉及查询排序问题。

有两种查询排序：内存排序及数据库排序

1、数据库排序使用order-by=“name asc”

对于map举例：

import java.util.HashMap;import java.util.Map;public class Team{private String id;private String teamName;private Map students = new HashMap();public String getId(){return id;}public void setId(String id){this.id = id;}public String getTeamName(){return teamName;}public void setTeamName(String teamName){this.teamName = teamName;}public Map getStudents(){return students;}public void setStudents(Map students){this.students = students;}}

对应的hbm.xml映射文件：

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping><class name="com.cdtax.hibernate.Team" table="team"><id name="id" column="id" type="string"><generator class="uuid"></generator></id><property name="teamName" column="teamname" type="string"></property><map name="students" table="student" order-by="name asc"><!-- name是数据库字段 --><key column="team_id"></key><index column="name" type="java.lang.String"></index><!-- 指定Map中的key值 --><element column="description" type="java.lang.String"></element><!-- 指定Map中的value值 --></map> </class></hibernate-mapping>

一定要注意，这里的name是数据库字段的名字，而不是对象的成员变量名字。在进行sql查询时，会使用order by字句。

插入一些数据：

try{tx = session.beginTransaction();Team team = new Team();team.setTeamName("team 2");Map map = team.getStudents();map.put("lisi", "hello");map.put("zhangsan", "world");map.put("wangwu", "wangwu");map.put("eerr", "erere");session.save(team);tx.commit();}

插入数据库的数据student表：

team_iddescriptionname402881c0424612810142461282370001erereeerr402881c0424612810142461282370001hellolisi402881c0424612810142461282370001wangwuwangwu402881c0424612810142461282370001worldzhangsan
查询：

try{tx = session.beginTransaction();Team team = (Team)session.createQuery("from Team t where t.teamName='team 2'").uniqueResult();Map map = team.getStudents();Collection col = map.values();Iterator iter = col.iterator();while(iter.hasNext()){System.out.println(iter.next());}tx.commit();}

执行结果：

Hibernate: select team0_.id as id0_, team0_.teamname as teamname0_ from team team0_ where team0_.teamname='team 2'
Hibernate: select students0_.team_id as team1_0_, students0_.description as descript2_0_, students0_.name as name0_ from student students0_ where students0_.team_id=? order by students0_.name asc
erere
hello
wangwu
world

注意：这里打印的结果是字段description，我们将order-by改为name desc：

执行结果：

Hibernate: select team0_.id as id0_, team0_.teamname as teamname0_ from team team0_ where team0_.teamname='team 2'
Hibernate: select students0_.team_id as team1_0_, students0_.description as descript2_0_, students0_.name as name0_ from student students0_ where students0_.team_id=? order by students0_.name desc
world
wangwu
hello
erere

对于set举例：

import java.util.HashSet;import java.util.Set;public class Team{private String id;private String teamName;private Set students = new HashSet();public String getId(){return id;}public void setId(String id){this.id = id;}public String getTeamName(){return teamName;}public void setTeamName(String teamName){this.teamName = teamName;}public Set getStudents(){return students;}public void setStudents(Set students){this.students = students;}}

对应的映射文件、：

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping><class name="com.cdtax.hibernate.Team" table="team"><id name="id" column="id" type="string"><generator class="uuid"></generator></id><property name="teamName" column="teamname" type="string"></property><set name="students" table="student" order-by="name asc"><key column="team_id"></key><element column="name" type="string"></element></set> </class></hibernate-mapping>

插入测试数据：

try{tx = session.beginTransaction();Team team = new Team();team.setTeamName("team1");team.getStudents().add("zhangsan");team.getStudents().add("lisi");team.getStudents().add("wangwu");session.save(team);tx.commit();}

查询：

Hibernate: select team0_.id as id0_0_, team0_.teamname as teamname0_0_ from team team0_ where team0_.id=?
Hibernate: select students0_.team_id as team1_0_, students0_.name as name0_ from student students0_ where students0_.team_id=? order by students0_.name asc
lisi
wangwu
zhangsan

是有顺序的

如果将Team.hbm.xml的set修改一下，去掉order-by

<set name="students" table="student"><key column="team_id"></key><element column="name" type="string"></element></set> 

执行结果：

Hibernate: select team0_.id as id0_0_, team0_.teamname as teamname0_0_ from team team0_ where team0_.id=?
Hibernate: select students0_.team_id as team1_0_, students0_.name as name0_ from student students0_ where students0_.team_id=?
wangwu
lisi
zhangsan

2、内存排序使用sort属性

同上面的位置是一样的，就是order-by改为sort="" sort的属性值有三个：unsorted，不排序；natural，自然排序；第三个属性值是我们自己定义的排序规则类（Comparator接口）。

举例：将上例中Team.hbm.xml修改为：

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping><class name="com.cdtax.hibernate.Team" table="team"><id name="id" column="id" type="string"><generator class="uuid"></generator></id><property name="teamName" column="teamname" type="string"></property><set name="students" table="student" sort="unsorted"><key column="team_id"></key><element column="name" type="string"></element></set> </class></hibernate-mapping>

执行结果：

Hibernate: select team0_.id as id0_0_, team0_.teamname as teamname0_0_ from team team0_ where team0_.id=?
Hibernate: select students0_.team_id as team1_0_, students0_.name as name0_ from student students0_ where students0_.team_id=?
wangwu
lisi
zhangsan
改为

 sort="natural"

执行结果：

Hibernate: select team0_.id as id0_0_, team0_.teamname as teamname0_0_ from team team0_ where team0_.id=?
Hibernate: select students0_.team_id as team1_0_, students0_.name as name0_ from student students0_ where students0_.team_id=?
lisi
wangwu
zhangsan

可以看到按照字符的升序排列，而且sql查询语句也没有使用order by，这是查询出来后，在没有组装到对象前，由hibernate排序。

内存排序，我们自定义排序规则，方式是定义一个类，让其实现Comparator接口，并且实现该接口中的compare方法，在该方法中实现排序规则即可。然后将该自定义排序规则的类名作为sort的属性值即可。

测试：

import java.util.Set;public class Team{private String id;private String name;private Set<Student> students;public String getId(){return id;}public void setId(String id){this.id = id;}public String getName(){return name;}public void setName(String name){this.name = name;}public Set<Student> getStudents(){return students;}public void setStudents(Set<Student> students){this.students = students;}}

public class Student{private String id;private String name;private String cardId;private int age;public String getCardId(){return cardId;}public void setCardId(String cardId){this.cardId = cardId;}public int getAge(){return age;}public void setAge(int age){this.age = age;}private Team team;public Team getTeam(){return team;}public void setTeam(Team team){this.team = team;}public String getId(){return id;}public void setId(String id){this.id = id;}public String getName(){return name;}public void setName(String name){this.name = name;}}

对应的映射文件：

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping><class name="com.cdtax.hibernate.Team" table="team"><id name="id" column="id" type="string"><generator class="uuid"></generator></id><property name="name" column="name" type="string"></property><set name="students" lazy="false" cascade="all" inverse="true" fetch="select" sort="com.cdtax.hibernate.MyComparator"><key column="team_id"></key><one-to-many class="com.cdtax.hibernate.Student"/></set> </class></hibernate-mapping>

注意sort的值为我们自定义的比较器类

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping><class name="com.cdtax.hibernate.Student" table="student"><id name="id" column="id" type="string"><generator class="uuid"></generator></id><property name="name" column="name" type="string"></property><property name="cardId" column="cardid" type="string"></property><property name="age" column="age" type="integer"></property><many-to-one name="team" class="com.cdtax.hibernate.Team" column="team_id" fetch="select" cascade="all"></many-to-one></class></hibernate-mapping>

我们的比较器：

import java.util.Comparator;public class MyComparator implements Comparator<Student>{@Overridepublic int compare(Student o1, Student o2){if(o1 == o2){return 0;}int result = o1.getCardId().compareTo(o2.getCardId());if(result != 0){return result;}return o1.getName().compareTo(o2.getName());}}

测试程序：

try{tx = session.beginTransaction();Team team1 = (Team)session.get(Team.class,"402881c04246244c014246244d920001");Set<Student> set = team1.getStudents();for(Iterator<Student> iter = set.iterator();iter.hasNext();){System.out.println(iter.next().getName());}tx.commit();}

数据库数据：

idnameteam_idcardidage402881c04246244c014246244d920003lisi402881c04246244c014246244d920001aa7402881c04246244c014246244d920012zhang402881c04246244c014246244d920001bc9402881c04246244c014246244d920013wang402881c04246244c014246244d920001cs54402881c04246244c014246244d920015zliu402881c04246244c014246244d920001cs32402881c04246244c014246244d920016aaaaa402881c04246244c014246244d920001aa32
执行结果

Hibernate: select team0_.id as id1_0_, team0_.name as name1_0_ from team team0_ where team0_.id=?
Hibernate: select students0_.team_id as team5_1_, students0_.id as id1_, students0_.id as id0_0_, students0_.name as name0_0_, students0_.cardid as cardid0_0_, students0_.age as age0_0_, students0_.team_id as team5_0_0_ from student students0_ where students0_.team_id=?
aaaaa
lisi
zhang
wang
zliu

如果我们需要相反的顺序，修改比较器类：

import java.util.Comparator;public class MyComparator implements Comparator<Student>{@Overridepublic int compare(Student o1, Student o2){if(o1 == o2){return 0;}int result = o1.getCardId().compareTo(o2.getCardId());if(result != 0){return -result;}return -o1.getName().compareTo(o2.getName());}}

执行结果：

Hibernate: select team0_.id as id1_0_, team0_.name as name1_0_ from team team0_ where team0_.id=?
Hibernate: select students0_.team_id as team5_1_, students0_.id as id1_, students0_.id as id0_0_, students0_.name as name0_0_, students0_.cardid as cardid0_0_, students0_.age as age0_0_, students0_.team_id as team5_0_0_ from student students0_ where students0_.team_id=?
zliu
wang
zhang
lisi
aaaaa