I have a pojo similar to this:
StudentResultPojo{
studentId;
studentName;
studentEmail;
List<CourseResultPojo> coursePojoList;
}
with CourseResultPojo looking like this:
CourseResultPojo{
name;
teacher;
time;
teacherPhone;
teacherEmail;
}
I have Hibernate entities (Database : Postgres) for Student, Course, Teacher, Phone, Email etc.
Here is a snippet of code I have:
String query = "SELECT
distinct student.id as \"studentId\",
student.name as \"studentName\",
student.email as \"studentEmail\"
FROM
sample.sample_Student student";
Query q = entityManager.unwrap(Session.class).
createSQLQuery(query).
setResultTransformer(Transformers.aliasToBean(StudentResultPojo.class));
return q.list();
I would like to keep the query in NativeSQL as opposed to JPQL. Edit: Because the query can change anytime. I would like to store the query in the db and edit it as needed. Testing will be easier if it's in Native query.
I know JPQL has a similar functionality where we can convert results from a JPA query to pojo through it's constructor like select new (Pojo.class.getName()....) but that has the same problem I am facing.
With what I have, I will have to loop through the StudentResultPojo list that my service returns and I will have to fill the coursePojoList for every StudentResultPojo by retrieving the data from the database or in the Java code by navigating through their Hibernate relationships which takes some time.
Entities look like:
Student{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", unique = true, nullable = false)
private Long id;
private String name;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "student")
private Set<Course> courses = new HashSet<>(0);
}
Course{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", unique = true, nullable = false)
private Long id;
private String name;
private String time;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "student_id", referencedColumnName = "pk")
@ForeignKey(name = "student_course")
private Student student;
@OneToOne(fetch = FetchType.LAZY, mappedBy = "course")
private Set<Teacher> teacher;
}
......
Question: If I have a query that returns results in 2 rows (Not JSON, just the results returned when a query is run)
1, James, [email protected], Physics, Albert, 9.00, 7887897899, [email protected]
1, James, [email protected], English, Jackie, 10.00, 7887097899, [email protected]
Is there a way I can transform the results into a single StudentResultPojo with the the list of CourseResultPojos?
Meaning my StudentResultPojo will have the attribute values as
1, James, [email protected] for id, name and email respectively
and the list will have two CourseResultPojo objects with
1st Object values as Physics, Albert, 9.00, 7887897899, [email protected] for attributes name, teacher, time,teacherPhone,teacherEmail
2nd Object values as English, Jackie, 10.00, 7887097899, [email protected] for attributes name,teacher,time,teacherPhone,teacherEmail
Thank you.
I guess I'll try to help with what you have: a
StudentResultPojo
andCourseResultPojo
What you can do to achieve that JSON Stris to create a wrapper class for the 2 objects (i.e.
StudentCoursePojo
):If you want a
List
ofStudentCoursePojo
as a JSON string, guess what? You can also create a wrapper to it:You can then use either
Jackson
orGSON
to serialize yourStudentCoursePojo
orStudentCourseList
into JSON.some useful tutorials:
http://www.mkyong.com/java/jackson-2-convert-java-object-to-from-json/
http://www.mkyong.com/java/how-do-convert-java-object-to-from-json-format-gson-api/
hope this helps.
EDIT:
Since your
StudentResultPojo
already contains theCourseResultPojo
, you can simply build this object and then create a wrapper for aList
ofStudentResultPojo
. Then serialize to JSON using the above options.