如何使用Hibernate进行投影分组 [英] How to group by projections with Hibernate
问题描述
我需要找到一个特定组中的学生列表,以及他们的位置中的电话号码。
我的主要问题是我无法检索每个学生的电话号码作为集合。例如,如果我有student1,student2。电话1111为位置1中的学生1和电话2222和电话3333为位置1中的学生2以及位置2中的学生2为电话444。
可以说我有
Student1 Alex group1 1111 Location1 Street1
Student3 Jack group1 93939 Location2 Street4
Student7 Joe group2 22223 Location4 Street8
Student2 John group1 2222 3333 Location1 Street1
Student2 John group1 4444 Location1 Street2
Student12 Mark group1 4423 Location9 Street9
带数据的输出示例
用户要求所有在group1和location1的学生
Student1 Alex Street1电话1111距离30
Student2 John Street1电话22222,3333 distance30
Student2 John Street2电话4444距离40
换句话说,我希望有一份学生名单,以及他们的位置和所选位置的电话。
Hibernate返回以下错误信息对我当前的代码
org.springframework.orm.hib ernate4.HibernateSystemException:
在为属性
调用setter时发生IllegalArgumentException [com.example.Address.phones(expected type = java.util.List)];目标
= [com.example.results.AllStudents@6deeac0],属性值=
[11111111] setter of com.example.results.AllStudents.phones;嵌套的
异常是在调用setter
for property [com.example.results.AllStudents.phones(expected type =
java.util.List)]时发生IllegalArgumentException; target = [com.example.results.AllStudents@6deeac0],
property value = [11111111]
@Entity
public class Student实现了java.io.Serializable {
private static final long serialVersionUID = -23949494858373847L;
@Id
@GeneratedValue
字符串ID;
字符串名称;
@ManyToMany(fetch = FetchType.LAZY,cascade = CascadeType.ALL)
@JoinTable(name =student_groups,joinColumns = {@JoinColumn(name =id,nullable = false,updatable = false)},inverseJoinColumns = {@JoinColumn(name =groupId,nullable = false,updatable = false)})
Set< Group> groups = new HashSet< Group>(0);
..
地址 p>
@Entity
public class Address implements java.io.Serializable {
private static final long serialVersionUID = -274634747474623637L;
@Id
@GeneratedValue
字符串addId;
@Id
@ManyToOne
@JoinColumn(name =id,nullable = false)
学生学生;
@ManyToOne
@JoinColumn(name =locId,nullable = false)
位置位置;
双纬度;
双经度;
字符串地址;
@OneToMany(mappedBy =phoneOwner,fetch = FetchType.EAGER)
Set< Phone> phones = new HashSet< Phone>();
字符串公式=(6371 * acos(cos(弧度(
+ lat
+))* cos(弧度(this_.latitude)) * cos(弧度(this_.longitude) - 弧度(
+ lan +))++sin(弧度(+ lat
+))* sin(弧度(this_.latitude) ))))作为距离;
Session session = sessionFactory.getCurrentSession();
ProjectionList pl = Projections
.projectionList()
.add(Projections.property(std.id)。as(id))
.add(Projections。 property(std.name)。as(name))
.add(Projections.property(addr.address)。as(
address))
。添加(Projections.property(location.name)。as(location))
.add(Projections.property(location.city)。as(city))
。添加(Projections.property(location.latitude)。as(latitude))
.add(Projections.property(location.longitude)。as(longitude))
。 add(Projections.sqlProjection(formula,
new String [] {distance},
new Type [] {new DoubleType()}));
List< AllStudents> students =(List .createCriteria(Address.class,addr)
.createAlias(addr.student,std)
.createAlias(std。 (group,group)
.createAlias(addr.location,location)
.setProjection(pl)
.setFetchMode(group,FetchMode.JOIN)
.add(Restrictions.ilike(group.name,groupName))
.add(Restrictions.eq(location.id,locId))
.setResultTransformer(
new AliasToBeanNestedResultTransformer(AllStudents.class))
.list();
结果班
public class AllStudents {
List< String>手机;
...
}
AliasToBeanNestedResultTransformer
public class AliasToBeanNestedResultTransformer extends
AliasedTupleSubsetResultTransformer {
private static final long serialVersionUID = -8047276133980128266L;
private static final int TUPE_INDEX = 0;
private static final int ALISES_INDEX = 1;
private static final int FIELDNAME_INDEX = 2;
private static final PropertyAccessor accessor = PropertyAccessorFactory
.getPropertyAccessor(property);
private final Class<?> resultClass;
private Object [] entityTuples;
private String [] entityAliases;
私人地图< String,Class<>> fieldToClass = new HashMap< String,Class<>>();
私人地图< String,List<>> subEntities = new HashMap< String,List<>>();
私人列表< String> nestedAliases = new ArrayList< String>();
私人地图< String,Class<>> listFields = new HashMap< String,Class<>>();
$ b $ public boolean isTransformedValueATupleElement(String [] aliases,
int tupleLength){
return false;
}
public AliasToBeanNestedResultTransformer(Class<?> resultClass){
this.resultClass = resultClass;
public Object transformTuple(Object [] tuple,String []别名){
handleSubEntities(tuple,aliases);
cleanParams(元组,别名);
ResultTransformer rootTransformer = new AliasToBeanResultTransformer(
resultClass);
Object root = rootTransformer.transformTuple(entityTuples,
entityAliases);
loadSubEntities(root);
cleanMaps();
返回root;
}
private void handleSubEntities(Object [] tuple,String []别名)
抛出HibernateException {
String fieldName =;
String aliasName =;
尝试{
for(int i = 0; i< aliases.length; i ++){
String alias = aliases [i];
if(alias.contains(。)){
String [] sp = alias.split(\\\。);
StringBuilder aliasBuilder = new StringBuilder();
for(int j = 0; j if(j == 0){
fieldName = sp [j];
} else {
aliasBuilder.append(sp [j]);
aliasBuilder.append(。);
}
}
aliasName = aliasBuilder.substring(0,
aliasBuilder.length() - 1);
nestedAliases.add(别名);
manageEntities(fieldName,aliasName,tuple [i]);
}
}
} catch(NoSuchFieldException e){
throw new HibernateException(无法实例化resultclass:
+ resultClass.getName()+for field名称:+ fieldName
+和别名:+ aliasName);
}
}
private Class<?> findClass(String fieldName)抛出NoSuchFieldException,
SecurityException {$ b $ if(fieldToClass.containsKey(fieldName)){
return fieldToClass.get(fieldName);
} else {
Class<?> subclass = resultClass.getDeclaredField(fieldName)
.getType();
if(subclass.equals(List.class)|| subclass.equals(Set.class)){
if(subclass.equals(List.class)){
listFields.put(fieldName,LinkedList.class);
} else {
listFields.put(fieldName,HashSet.class);
}
Field field = resultClass.getDeclaredField(fieldName);
ParameterizedType genericType =(ParameterizedType)字段
.getGenericType();
subclass =(Class<>)genericType.getActualTypeArguments()[0];
}
fieldToClass.put(fieldName,subclass);
返回子类;
$ b @SuppressWarnings(unchecked)
private void manageEntities(String fieldName,String aliasName,
Object tupleValue)throws NoSuchFieldException,SecurityException {
Class<?> subclass = findClass(fieldName);
if(!subEntities.containsKey(fieldName)){
List< Object> list = new ArrayList< Object>();
list.add(new ArrayList< Object>());
list.add(new ArrayList< String>());
list.add(FIELDNAME_INDEX,子类);
subEntities.put(fieldName,list); $(b
((List< Object>)subEntities.get(fieldName).get(TUPE_INDEX))
.add(tupleValue);
((List< String>)subEntities.get(fieldName).get(ALISES_INDEX))
.add(aliasName);
$ b $ private void cleanParams(Object [] tuple,String [] aliases){
entityTuples = new Object [aliases.length - nestedAliases.size()];
entityAliases = new String [aliases.length - nestedAliases.size()];
for(int j = 0,i = 0; j if(!nestedAliases.contains(aliases [j])){
entityTuples [i] = tuple [j];
entityAliases [i] =别名[j];
++ i;
$ @SuppressWarnings({unchecked,rawtypes})
private void loadSubEntities(Object root)throws HibernateException {
尝试{
for(String fieldName:subEntities.keySet()){
Class<?> subclass =(Class<> subEntities.get(fieldName).get(
FIELDNAME_INDEX);
ResultTransformer subclassTransformer = new AliasToBeanNestedResultTransformer(
subclass);
Object subObject = subclassTransformer.transformTuple(
((List< Object>)subEntities.get(fieldName).get(0))
.toArray(),
((List< Object>)subEntities.get(fieldName).get(1))
.toArray(new String [0]));
Setter setter = accessor.getSetter(resultClass,fieldName);
if(listFields.containsKey(fieldName)){
Class<?> collectionClass = listFields.get(fieldName);
Collection subObjectList =(Collection)collectionClass
.newInstance();
subObjectList.add(subObject);
setter.set(root,subObjectList,null);
} else {
setter.set(root,subObject,null);
$ b $ catch(Exception e){
throw new HibernateException(e);
private void cleanMaps(){
fieldToClass = new HashMap< String,Class<>>();
subEntities = new HashMap< String,List<>>();
nestedAliases = new ArrayList< String>();
listFields = new HashMap< String,Class<>>();
}
}
AliasToBeanNestedResultTransformer 支持嵌套的DTO,但它不支持Collections of DTOs。
您可以将 AllStudent
DTS更改为:
公共班级AllStudents {
学生学生;
弦乐手机;
位置位置;
公共AllStudents(学生学生,弦乐手机,位置位置){
this.student = student;
this.phone = phone;
this.location = location;
}
public Student getStudent(){
return student;
}
public String getPhone(){
return phone;
}
public Location getLocation(){
return location;
}
}
,您需要添加 StudentDTO
来保存聚合结果:
public class StudentDTO {
private final Student学生;
私人字符串位置;
私人列表< String> phones = new ArrayList<>();
公共StudentDTO(学生学生){
this.student =学生;
}
public Student getStudent(){
return student;
}
public String getLocation(){
return location;
}
public void setLocation(String location){
this.location = location;
}
public List< String> getPhones(){
返回手机;
$ b现在当你运行你的查询时, AllStudents: 列表< AllStudents> allStudents = ...
你可以简单地这样组合:
LinkedHashMap< Long,StudentDTO> studentMap = new LinkedHashMap<>(); (AllStudents all:allStudents)
{
StudentDTO studentDTO = studentMap.get(all.getStudent()。getId());
if(studentDTO == null){
studentDTO = new StudentDTO(all.getStudent());
studentMap.put(all.getStudent()。getId(),studentDTO); (all.getPhone()!= null){
studentDTO.getPhones()。add(all.getPhone());
}
if
}
studentDTO.setLocation(all.getLocation());
}
列表< StudentDTO> studentDTOs = new ArrayList<>(studentMap.values());
I need to find a list of students that are in a specific group and located in a specific address along with their phone numbers in their location.
My main issue is I can not retrieve phone numbers of each student as a collection. For example if I have student1,student2. Phone 1111 for student1 in location1 and phone 2222 and phone 3333 for student2 in location 1 and phone 444 for student2 in location2.
Lets say I have
Student1 Alex group1 1111 Location1 Street1
Student3 Jack group1 93939 Location2 Street4
Student7 Joe group2 22223 Location4 Street8
Student2 John group1 2222 3333 Location1 Street1
Student2 John group1 4444 Location1 Street2
Student12 Mark group1 4423 Location9 Street9
Sample Output with data
User asks for all students in group1 and location1
Student1 Alex Street1 phone 1111 distance30
Student2 John Street1 phones 22222,3333 distance30
Student2 John Street2 phone 4444 distance40
In other words, I would like to have a list of students along with their locations and phones of the selected location.
Hibernate returns following error message against my current code
org.springframework.orm.hibernate4.HibernateSystemException:
IllegalArgumentException occurred while calling setter for property
[com.example.Address.phones (expected type = java.util.List)]; target
= [com.example.results.AllStudents@6deeac0], property value =
[11111111] setter of com.example.results.AllStudents.phones; nested
exception is IllegalArgumentException occurred while calling setter
for property [com.example.results.AllStudents.phones (expected type =
java.util.List)]; target = [com.example.results.AllStudents@6deeac0],
property value = [11111111]
Student
@Entity
public class Student implements java.io.Serializable {
private static final long serialVersionUID = -23949494858373847L;
@Id
@GeneratedValue
String id;
String name;
@ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinTable(name = "student_groups", joinColumns = { @JoinColumn(name = "id", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "groupId", nullable = false, updatable = false) })
Set<Group> groups = new HashSet<Group>(0);
..
}
Address
@Entity
public class Address implements java.io.Serializable {
private static final long serialVersionUID = -274634747474623637L;
@Id
@GeneratedValue
String addId;
@Id
@ManyToOne
@JoinColumn(name = "id", nullable = false)
Student student;
@ManyToOne
@JoinColumn(name = "locId", nullable = false)
Location location;
double latitude;
double longitude;
String address;
@OneToMany(mappedBy = "phoneOwner", fetch = FetchType.EAGER)
Set<Phone> phones = new HashSet<Phone>();
String formula = "( 6371 * acos ( cos ( radians("
+ lat
+ ") ) * cos( radians( this_.latitude ) ) * cos( radians( this_.longitude ) - radians("
+ lan + ") ) +" + "sin ( radians(" + lat
+ ") ) * sin( radians( this_.latitude ) ) ) ) as distance";
Session session = sessionFactory.getCurrentSession();
ProjectionList pl = Projections
.projectionList()
.add(Projections.property("std.id").as("id"))
.add(Projections.property("std.name").as("name"))
.add(Projections.property("addr.address").as(
"address"))
.add(Projections.property("location.name").as("location"))
.add(Projections.property("location.city").as("city"))
.add(Projections.property("location.latitude").as("latitude"))
.add(Projections.property("location.longitude").as("longitude"))
.add(Projections.sqlProjection(formula,
new String[] { "distance" },
new Type[] { new DoubleType() }));
List<AllStudents> students = (List<AllStudents) session
.createCriteria(Address.class, "addr")
.createAlias("addr.student", "std")
.createAlias("std.groups", "group")
.createAlias("addr.location", "location")
.setProjection(pl)
.setFetchMode("group", FetchMode.JOIN)
.add(Restrictions.ilike("group.name", groupName))
.add(Restrictions.eq("location.id", locId))
.setResultTransformer(
new AliasToBeanNestedResultTransformer(AllStudents.class))
.list();
Results class
public class AllStudents {
List<String> phones;
...
}
AliasToBeanNestedResultTransformer
public class AliasToBeanNestedResultTransformer extends
AliasedTupleSubsetResultTransformer {
private static final long serialVersionUID = -8047276133980128266L;
private static final int TUPE_INDEX = 0;
private static final int ALISES_INDEX = 1;
private static final int FIELDNAME_INDEX = 2;
private static final PropertyAccessor accessor = PropertyAccessorFactory
.getPropertyAccessor("property");
private final Class<?> resultClass;
private Object[] entityTuples;
private String[] entityAliases;
private Map<String, Class<?>> fieldToClass = new HashMap<String, Class<?>>();
private Map<String, List<?>> subEntities = new HashMap<String, List<?>>();
private List<String> nestedAliases = new ArrayList<String>();
private Map<String, Class<?>> listFields = new HashMap<String, Class<?>>();
public boolean isTransformedValueATupleElement(String[] aliases,
int tupleLength) {
return false;
}
public AliasToBeanNestedResultTransformer(Class<?> resultClass) {
this.resultClass = resultClass;
}
public Object transformTuple(Object[] tuple, String[] aliases) {
handleSubEntities(tuple, aliases);
cleanParams(tuple, aliases);
ResultTransformer rootTransformer = new AliasToBeanResultTransformer(
resultClass);
Object root = rootTransformer.transformTuple(entityTuples,
entityAliases);
loadSubEntities(root);
cleanMaps();
return root;
}
private void handleSubEntities(Object[] tuple, String[] aliases)
throws HibernateException {
String fieldName = "";
String aliasName = "";
try {
for (int i = 0; i < aliases.length; i++) {
String alias = aliases[i];
if (alias.contains(".")) {
String[] sp = alias.split("\\.");
StringBuilder aliasBuilder = new StringBuilder();
for (int j = 0; j < sp.length; j++) {
if (j == 0) {
fieldName = sp[j];
} else {
aliasBuilder.append(sp[j]);
aliasBuilder.append(".");
}
}
aliasName = aliasBuilder.substring(0,
aliasBuilder.length() - 1);
nestedAliases.add(alias);
manageEntities(fieldName, aliasName, tuple[i]);
}
}
} catch (NoSuchFieldException e) {
throw new HibernateException("Could not instantiate resultclass: "
+ resultClass.getName() + " for field name: " + fieldName
+ " and alias name:" + aliasName);
}
}
private Class<?> findClass(String fieldName) throws NoSuchFieldException,
SecurityException {
if (fieldToClass.containsKey(fieldName)) {
return fieldToClass.get(fieldName);
} else {
Class<?> subclass = resultClass.getDeclaredField(fieldName)
.getType();
if (subclass.equals(List.class) || subclass.equals(Set.class)) {
if (subclass.equals(List.class)) {
listFields.put(fieldName, LinkedList.class);
} else {
listFields.put(fieldName, HashSet.class);
}
Field field = resultClass.getDeclaredField(fieldName);
ParameterizedType genericType = (ParameterizedType) field
.getGenericType();
subclass = (Class<?>) genericType.getActualTypeArguments()[0];
}
fieldToClass.put(fieldName, subclass);
return subclass;
}
}
@SuppressWarnings("unchecked")
private void manageEntities(String fieldName, String aliasName,
Object tupleValue) throws NoSuchFieldException, SecurityException {
Class<?> subclass = findClass(fieldName);
if (!subEntities.containsKey(fieldName)) {
List<Object> list = new ArrayList<Object>();
list.add(new ArrayList<Object>());
list.add(new ArrayList<String>());
list.add(FIELDNAME_INDEX, subclass);
subEntities.put(fieldName, list);
}
((List<Object>) subEntities.get(fieldName).get(TUPE_INDEX))
.add(tupleValue);
((List<String>) subEntities.get(fieldName).get(ALISES_INDEX))
.add(aliasName);
}
private void cleanParams(Object[] tuple, String[] aliases) {
entityTuples = new Object[aliases.length - nestedAliases.size()];
entityAliases = new String[aliases.length - nestedAliases.size()];
for (int j = 0, i = 0; j < aliases.length; j++) {
if (!nestedAliases.contains(aliases[j])) {
entityTuples[i] = tuple[j];
entityAliases[i] = aliases[j];
++i;
}
}
}
@SuppressWarnings({ "unchecked", "rawtypes" })
private void loadSubEntities(Object root) throws HibernateException {
try {
for (String fieldName : subEntities.keySet()) {
Class<?> subclass = (Class<?>) subEntities.get(fieldName).get(
FIELDNAME_INDEX);
ResultTransformer subclassTransformer = new AliasToBeanNestedResultTransformer(
subclass);
Object subObject = subclassTransformer.transformTuple(
((List<Object>) subEntities.get(fieldName).get(0))
.toArray(),
((List<Object>) subEntities.get(fieldName).get(1))
.toArray(new String[0]));
Setter setter = accessor.getSetter(resultClass, fieldName);
if (listFields.containsKey(fieldName)) {
Class<?> collectionClass = listFields.get(fieldName);
Collection subObjectList = (Collection) collectionClass
.newInstance();
subObjectList.add(subObject);
setter.set(root, subObjectList, null);
} else {
setter.set(root, subObject, null);
}
}
} catch (Exception e) {
throw new HibernateException(e);
}
}
private void cleanMaps() {
fieldToClass = new HashMap<String, Class<?>>();
subEntities = new HashMap<String, List<?>>();
nestedAliases = new ArrayList<String>();
listFields = new HashMap<String, Class<?>>();
}
}
解决方案 The AliasToBeanNestedResultTransformer supports nested DTOs but it doesn not support Collections of DTOs.
You can change the AllStudent
DTS to:
public class AllStudents {
Student student;
String phone;
Location location;
public AllStudents(Student student, String phone, Location location) {
this.student = student;
this.phone = phone;
this.location = location;
}
public Student getStudent() {
return student;
}
public String getPhone() {
return phone;
}
public Location getLocation() {
return location;
}
}
and you need to add a StudentDTO
to hold the aggregation result:
public class StudentDTO {
private final Student student;
private String location;
private List<String> phones = new ArrayList<>();
public StudentDTO(Student student) {
this.student = student;
}
public Student getStudent() {
return student;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}
public List<String> getPhones() {
return phones;
}
}
Now when you run your query you get a List of AllStudents:
List<AllStudents> allStudents = ...
which you simply group like this:
LinkedHashMap<Long, StudentDTO> studentMap = new LinkedHashMap<>();
for(AllStudents all : allStudents) {
StudentDTO studentDTO = studentMap.get(all.getStudent().getId());
if(studentDTO == null) {
studentDTO = new StudentDTO(all.getStudent());
studentMap.put(all.getStudent().getId(), studentDTO);
}
if(all.getPhone() != null) {
studentDTO.getPhones().add(all.getPhone());
}
studentDTO.setLocation(all.getLocation());
}
List<StudentDTO> studentDTOs = new ArrayList<>(studentMap.values());
这篇关于如何使用Hibernate进行投影分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!