javamysqljersey
GET REQUEST does not return faculty data properly causes 500 Internal Server Error
I'm trying to return the faculty data with it's corresponding students, professors, subjects and grade, I get this Error 500:
I want postman to return this: to display the faculty that is searched by id and inside the faculty the student with the data of subjects, professor and grade of the student.
{
"id": 1,
"university_id": 1,
"name": "FSHMN",
"Enrolled Students:": [
{
{
"id": "5",
"username": "Student5",
"password": "d123",
"fullname": "Student",
"email": "Student@gmail.com",
"subjects": [
{
"id": 1,
"name": "Programim 1",
"Professor": [
{
"id": 1,
"first name": "Stephen",
"last name": "Hawking",
"Title": "Docen"
}
],
"Grade": [
{
"grade_id": 2,
"mark": 9,
"description": "Very Good"
}
]
},
{
"id": 2,
"name": "Programim 2",
"Professor": [
{
"id": 2,
"first name": "John",
"last name": "VonNeuman",
"Title": "Inordinar"
}
],
"Grade": [
{
"grade_id":1,
"mark": 10,
"description": "Very well Done"
}
]
},
{
"id": 3,
"name": "Calculus",
"Professor": [
{
"id": 3,
"first name": "Albert",
"last name": "Einstein",
"Title": "Ordinar"
}
],
"Grade": [
{
"grade_id": 4,
"mark": 7,
"description": "well"
}
]
},
{
"id": 4,
"name": "Discrete mathematics",
"Professor": [
{
"id": 4,
"first name": "John",
"last name": "Feynman",
"Title": "Rektor"
}
],
"Grade": [
{
"grade_id": 2,
"mark": 8,
"description": " Good"
}
]
}
]
}
]
}
Here are the methods that the GET Request is Calling:
public Faculty getFacultyStudent(int id) throws Exception { Connection connection = null;
Faculty faculty = new Faculty();
Student student = new Student();
HashMap<String,List<Student>> studentFacultyMap=new HashMap<>();
HashMap<String,Professor> professorHashMap=new HashMap<>();
HashMap<String, List<StudentMark>> studentMarksHashMap=new HashMap<>();
faculty.setStudentList(new ArrayList<>());
Map<String,Subject> subjectMap = new HashMap<>();
try {
connection = new MysqlDbConnectionService().getConnection();
String select = "SELECT f.fid, f.fname, f.university_id, s.user_id, s.username, s.password, s.fullname, s.email, "+
" s.email, subj.id , subj.name , p.professor_id, p.first_name, p.last_name, p.title, g.grade_id, g.mark, g.description" +
" FROM faculty f " +
" INNER JOIN student_faculty sf ON sf.faculty_id=f.fid " +
" INNER JOIN student s ON sf.student_id=s.user_id " +
" INNER JOIN faculty_subject fs ON f.fid = fs.faculty_id " +
" INNER JOIN subject subj ON fs.subject_id = subj.id " +
" INNER JOIN professor_subject ps ON ps.subject_id = subj.id " +
" INNER JOIN professor p ON ps.prof_id = p.professor_id " +
" INNER JOIN student_subject_marks sm ON sm.student_id = s.user_id and sm.subject_id = subj.id" +
" INNER JOIN grade g ON sm.grade_id = g.grade_id " +
"WHERE fid = ?";
PreparedStatement ps = connection.prepareStatement(select);
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
studentFacultyMap= facultyService.getFacultyStudentMap(id);
professorHashMap=facultyService.getProfessors(id);
studentMarksHashMap=facultyService.getStudentSubjectMarks(id);
while (rs.next()) {
if(faculty.getFid()==0) {
faculty.setFid(rs.getInt("fid"));
faculty.setUniversityid(rs.getInt("university_id"));
faculty.setFname(rs.getString("fname"));
}
String subjectID=rs.getString("id");
Subject subject =null;
if(!subjectMap.containsKey(subjectID)) {
subject = new Subject();
subject.setProfessorList(new ArrayList<>());
subject.setMarkList(new ArrayList<>());
subject.setId(rs.getInt("id"));
subject.setName(rs.getString("name"));
subjectMap.put(subjectID, subject);
subject.getProfessorList().addAll(professorHashMap.values());
subject.getMarkList().addAll((Collection<? extends StudentMark>); studentMarksHashMap.values());
}
else{
subject = subjectMap.get(subjectID);
}
student= (Student) studentFacultyMap.values();
student.getSubjectList().add(subject);
}
} catch (Exception e) {
System.out.println(e + " Retrieve not successful");
}
faculty.getStudentList().add(student);
return faculty;
}
This method calls 3 more methods that generate the student, professor and grade data: This method gets the student:
private HashMap<String,List<Student>> getFacultyStudentMap(int id){
Connection connection = null;
HashMap<String,List<Student>> studentMap=new HashMap<>();
try {
connection = new MysqlDbConnectionService().getConnection();
String select = "SELECT f.fid, f.fname, f.university_id, s.user_id, s.username, s.password, s.fullname, s.email, " +
" s.email, subj.id , subj.name , p.professor_id, p.first_name, p.last_name, p.title, g.grade_id, g.mark, g.description" +
" FROM faculty f " +
" INNER JOIN student_faculty sf ON sf.faculty_id=f.fid " +
" INNER JOIN student s ON sf.student_id=s.user_id " +
" INNER JOIN faculty_subject fs ON f.fid = fs.faculty_id " +
" INNER JOIN subject subj ON fs.subject_id = subj.id " +
" INNER JOIN professor_subject ps ON ps.subject_id = subj.id " +
" INNER JOIN professor p ON ps.prof_id = p.professor_id " +
" INNER JOIN student_subject_marks sm ON sm.student_id = s.user_id and sm.subject_id = subj.id" +
" INNER JOIN grade g ON sm.grade_id = g.grade_id " +
"WHERE fid = ?";
//
//
PreparedStatement ps = connection.prepareStatement(select);
ps.setInt(1, id);
//
ResultSet rs = ps.executeQuery();
String userID = rs.getString("user_id");
Student student = null;
while (rs.next()) {
if (!studentMap.containsKey(userID)) {
student = new Student();
student.setSubjectList(new ArrayList<>());
student.setId(rs.getString("user_id"));
student.setUsername(rs.getString("username"));
student.setPassword(rs.getString("password"));
student.setFullName(rs.getString("fullname"));
student.setEmail(rs.getString("email"));
studentMap.put(userID, (List<Student>) student);
}
else{
student = (Student) studentMap.get(userID);
}
}
}
catch(Exception e)
{
System.out.println("FacultyStudentMap: " + e);
}
return studentMap;
}
This method gets professor:
private HashMap<String,Professor> getProfessors(int id){
Connection connection = null;
HashMap<String,Professor> professorMap=new HashMap<>();
try{
connection = new MysqlDbConnectionService().getConnection();
String select = "SELECT f.fid, f.fname, f.university_id, s.user_id, s.username, s.password, s.fullname, s.email, "+
" s.email, subj.id , subj.name , p.professor_id, p.first_name, p.last_name, p.title, g.grade_id, g.mark, g.description" +
" FROM faculty f " +
" INNER JOIN student_faculty sf ON sf.faculty_id=f.fid " +
" INNER JOIN student s ON sf.student_id=s.user_id " +
" INNER JOIN faculty_subject fs ON f.fid = fs.faculty_id " +
" INNER JOIN subject subj ON fs.subject_id = subj.id " +
" INNER JOIN professor_subject ps ON ps.subject_id = subj.id " +
" INNER JOIN professor p ON ps.prof_id = p.professor_id " +
" INNER JOIN student_subject_marks sm ON sm.student_id = s.user_id and sm.subject_id = subj.id" +
" INNER JOIN grade g ON sm.grade_id = g.grade_id " +
"WHERE fid = ?";
PreparedStatement ps = connection.prepareStatement(select);
ps.setInt(1, id);
//
ResultSet rs = ps.executeQuery();
String professorID = rs.getString("professor_id");
Professor professor = null;
while(rs.next())
{
if (!professorMap.containsKey(professorID)) {
professor = new Professor();
professor.setProfessor_id(rs.getInt("professor_id"));
professor.setFirst_name(rs.getString("first_name"));
professor.setLast_name(rs.getString("last_name"));
professor.setTitle(rs.getString("title"));
professorMap.put(professorID, professor);
}
else{
professor = (Professor) professorMap.get(professorID);
}
}
}
catch(Exception e)
{
System.out.println("GetProfessor: " + e);
}
return professorMap;
}
This method Gets the grades:
private HashMap<String,List<StudentMark>> getStudentSubjectMarks(int id){
Connection connection = null;
HashMap<String,List<StudentMark>> studentMark=new HashMap<>();
try{
connection = new MysqlDbConnectionService().getConnection();
String select = "SELECT f.fid, f.fname, f.university_id, s.user_id, s.username, s.password, s.fullname, s.email, "+
" s.email, subj.id , subj.name , p.professor_id, p.first_name, p.last_name, p.title, g.grade_id, g.mark, g.description" +
" FROM faculty f " +
" INNER JOIN student_faculty sf ON sf.faculty_id=f.fid " +
" INNER JOIN student s ON sf.student_id=s.user_id " +
" INNER JOIN faculty_subject fs ON f.fid = fs.faculty_id " +
" INNER JOIN subject subj ON fs.subject_id = subj.id " +
" INNER JOIN professor_subject ps ON ps.subject_id = subj.id " +
" INNER JOIN professor p ON ps.prof_id = p.professor_id " +
" INNER JOIN student_subject_marks sm ON sm.student_id = s.user_id and sm.subject_id = subj.id" +
" INNER JOIN grade g ON sm.grade_id = g.grade_id " +
"WHERE fid = ?";
PreparedStatement ps = connection.prepareStatement(select);
ps.setInt(1, id);
//
ResultSet rs = ps.executeQuery();
String markID = rs.getString("grade_id");
StudentMark mark = null;
while(rs.next())
{
if (!studentMark.containsKey(markID)) {
mark = new StudentMark();
mark.setGrade_id(rs.getInt("grade_id"));
mark.setMark(rs.getInt("mark"));
mark.setDescription(rs.getString("description"));
studentMark.put(markID, (List<StudentMark>) mark);
}
else{
mark = (StudentMark) studentMark.get(markID);
}
}
}
catch(Exception e)
{
System.out.println("getStudentSubjectMark: " + e);
}
return studentMark;
}
Here is the Subject and Student Class:
Student Class:
package com.common.db.domain;
import com.google.gson.annotations.SerializedName;
import java.util.List;
public class Student {
@SerializedName("id")
private String id;
@SerializedName("username")
private String username;
@SerializedName("password")
private String password;
@SerializedName("fullname")
private String fullName;
@SerializedName("email")
private String email;
@SerializedName("subjects")
private List<Subject> subjectList;
public Student() {
}
public Student(String id, String username, String password, String fullName, String email) {
super();
this.id = id;
this.username = username;
this.password = password;
this.fullName = fullName;
this.email = email;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getFullName() {
return fullName;
}
public void setFullName(String fullName) {
this.fullName = fullName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public List<Subject> getSubjectList() {
return subjectList;
}
public void setSubjectList(List<Subject> subjectList) {
this.subjectList = subjectList;
}
}
Subject Class:
package com.common.db.domain;
import com.google.gson.annotations.SerializedName;
import java.util.List;
public class Subject {
@SerializedName("id")
private int id;
@SerializedName("name")
private String name;
@SerializedName("Professor")
private List<Professor> professorList;
@SerializedName("Grade")
private List<StudentMark> markList;
public Subject() {
this.id = id;
this.name=name;
}
public void setId(int id)
{
this.id=id;
}
public int getId()
{
return id;
}
public void setName(String name)
{
this.name=name;
}
public String getName()
{
return name;
}
public List<Professor> getProfessorList() {
return professorList;
}
public void setProfessorList(List<Professor> professorList) {
this.professorList = professorList;
}
public List<StudentMark> getMarkList() {
return markList;
}
public void setMarkList(List<StudentMark> markList) {
this.markList = markList;
}
}
I know I have not defined the methods properly and not interlinked the 3 methods with the main method properly , so what changes do I need to do in the code for those methods to return the faculty data properly.
Solution
Here is the solution of my problem:
I've made substantial changes to the methods:
This is the main method which the GET request is calling:
public List<Faculty> getFacultiesIncludingSubObjects() throws Exception {
Connection connection = null;
List<Faculty> faculties=getFaculty();
HashMap<String, List<Subject>> studentSubjectMap = new HashMap<>();
HashMap<String, List<StudentMark>> studentSubjectGradeMap = new HashMap<>();
HashMap<String, List<Student>> facultyStudentMap = new HashMap<>();
try {
connection = new MysqlDbConnectionService().getConnection();
studentSubjectMap=getStudentSubjectMap(connection);
studentSubjectGradeMap=getStudentSubjectGradeMap(connection);
facultyStudentMap=getFacultyStudentMap(connection);
}finally {
connection.close();
}
for(Faculty faculty:faculties){
faculty.setStudentList(facultyStudentMap.get(faculty.getFid()+""));
if(faculty.getStudentList()!=null){
for(Student student:faculty.getStudentList()){
student.setSubjectList(studentSubjectMap.get(student.getId()));
if(student.getSubjectList()!=null){
for(Subject sb:student.getSubjectList()){
sb.setMarkList(studentSubjectGradeMap.get(student.getId()+"_"+sb.getId()));
}
}
}
}
}
return faculties;
}
This line: List<Faculty> faculties=getFaculty();
Calls the getFaculty method:
public ArrayList<Faculty> getFaculty() throws Exception {
ArrayList<Faculty> data = new ArrayList<Faculty>();
Connection connection = null;
try {
connection = new MysqlDbConnectionService().getConnection();
String select = "select * from faculty";
PreparedStatement ps = connection.prepareStatement(select);
ResultSet rs = ps.executeQuery();
Faculty model = null;
while (rs.next()) {
model = new Faculty();
model.setFid(rs.getInt("fid"));
model.setUniversityid(rs.getInt("university_id"));
model.setFname(rs.getString("fname"));
data.add(model);
}
} catch (Exception e) {
System.out.println(e + "Retrieve not successful");
}
return data;
}
Also inside the main method 3 more methods are called:
Subject Method:
public HashMap<String, List<Subject>> getStudentSubjectMap(Connection connection) throws SQLException {
HashMap<String, List<Subject>> result=new HashMap<>();
PreparedStatement ps = connection.prepareStatement("select s.user_id as student_id,sb.*\n" +
"from student s\n" +
" inner join student_faculty sf on s.user_id = sf.student_id\n" +
" inner join faculty f on sf.faculty_id = f.fid\n" +
" inner join faculty_subject fs on f.fid = fs.faculty_id\n" +
" inner join subject sb on fs.subject_id = sb.id;");
ResultSet rs=ps.executeQuery();
while(rs.next()){
String studentId=rs.getString("student_id");
result.putIfAbsent(studentId,new ArrayList<>());
Subject subject=new Subject();
subject.setId(rs.getInt("sb.id"));
subject.setName(rs.getString("sb.name"));
result.get(studentId).add(subject);
}
return result;
}
Student Method:
public HashMap<String, List<Student>> getFacultyStudentMap(Connection connection) throws SQLException {
HashMap<String, List<Student>> result=new HashMap<>();
PreparedStatement ps = connection.prepareStatement("select sf.faculty_id, s.*\n" +
"from student s\n" +
" inner join student_faculty sf on sf.student_id = s.user_id;");
ResultSet rs=ps.executeQuery();
while(rs.next()){
String facultyId=rs.getString("faculty_id");
result.putIfAbsent(facultyId,new ArrayList<>());
Student student=new Student();
student.setId(rs.getString("user_id"));
student.setUsername(rs.getString("username"));
student.setPassword(rs.getString("password"));
student.setFullName(rs.getString("fullname"));
student.setEmail(rs.getString("email"));
result.get(facultyId).add(student);
}
return result;
}
Grade Method:
public HashMap<String, List<StudentMark>> getStudentSubjectGradeMap(Connection connection) throws SQLException {
HashMap<String, List<StudentMark>> result=new HashMap<>();
PreparedStatement ps = connection.prepareStatement("select concat_ws('_', s.user_id, sb.id) as student_subject, sb.name, g.*\n" +
"from student s\n" +
" inner join student_faculty sf on s.user_id = sf.student_id\n" +
" inner join faculty f on sf.faculty_id = f.fid\n" +
" inner join faculty_subject fs on f.fid = fs.faculty_id\n" +
" inner join subject sb on fs.subject_id = sb.id\n" +
" inner join student_subject_marks ssm on sb.id = ssm.subject_id and ssm.student_id = s.user_id\n" +
" inner join grade g on ssm.grade_id = g.grade_id;");
ResultSet rs=ps.executeQuery();
while(rs.next()){
String studentSubjectId=rs.getString("student_subject");
result.putIfAbsent(studentSubjectId,new ArrayList<>());
StudentMark mark=new StudentMark();
mark.setMark(rs.getInt("mark"));
mark.setDescription(rs.getString("description"));
result.get(studentSubjectId).add(mark);
}
return result;
}
And the Jersey method that calls the main method is:
@GET
public Response getFaculty() throws Exception {
return Response.ok(new Gson().toJson(facultyService.getFacultiesIncludingSubObjects())).build();
}
- Keep numbers which appear in both columns, in J lang
- Differentiation in J
- How to reshape an array with an arbitrary size in one dimension?
- Why is Insert (fold) right associative
- Write 4 : 'x&{.&.;: y' tacitly
- Alignment issue when printing formatted prime numbers in J language
- How can I define a verb in J that applies a different verb alternately to each atom in a list?
- How to get user input in the J programming language
- How to unbox a list of boxed lists of differing lengths in J?
- How can I fix 'noun result was required' error in J?
- In j, how can I define a verb locally in one scope and pass it to a defined adverb?
- Convert boxed array to normal array?
- Read column of CSV file as array
- Replace atom in array of strings
- What does the dyad `=` do to boxed strings?
- Index of minimum element using J
- How can I take the outer product of string vectors in J?
- Building an array of verbs in J
- Reading in multidigit command line parameter
- Amend with bond to new data shows unexpected behaviour
- How to turn a table or matrix into a (flat) list in J
- How to run dissect in J?
- How to define selection using index function in J
- How to exit the J console?
- Find 4-neighbors using J
- Writing custom verbs in J
- How do I negate a selector in J lang?
- How to use arbitrary selector in interchange in J lang?
- different result once square root is added inside tacit
- Sum of arrays with repeated indices