Create a Java Program to Print a Grade Report and Assign a new Course to a Student
Learning objectives
In this chapter of the tutorial you will learn ...
- to use SQL inside of a Java program to query the database
- to iterate result sets
- to retrieve the conected vertices when qurying an edge class
- to navigate from one vertex to a connected vertex
- to use the SQL extension traverse to traverse a graph recursively
- to create a new edge using tinkerpop blueprints API
If you prefer you can watch a screencast video:
Structure of the Application
As a simple programming task we first will develop a Java program which prints a grade report for a selected student. The program consists of the following parts:
- User input: name of a student
- Retrieve all students with this name from the database and print their data
- User input: student number of the student
- Retrieve all attends edges of the selected student and the connected course vertices
- Print the grades of each attends edge together with the subject of the corresponding course
- Print all course names and numbers
- User input: course number
- Select desired course and retrieve all courses which are required for this course following the required edges
- Check whether the studend has successfully attended all required courses: Retrieve all attends edges of the selected student. Delete these courses from the list of required courses.
- If the check succeeds create a new attends edge from the selected student to the selected course
Develop the Program to Print a Grade Report
In the section Unit Tests of this tutorial you created a Java project in Eclipse. Open this project. To implement the grade report create a new package in Eclipse: applications. Then create a new JAVA class GradeReport in this package.
Since this is a very short program a main method with a linear structure is sufficient. First we establish the connection to the database.
public static void main(String[] args) {
// Connect to database
OrientGraphFactory factory = new OrientGraphFactory("remote:localhost/CourseParticipation", "admin", "admin"); // The OrientDB server must be running
OrientGraph db = factory.getTx();
The user provides a student's name that is read using a BufferedReader.
// User input: student name
String lastName;
System.out.println ("Type the last name of the student: ");
BufferedReader bfr = new BufferedReader (new InputStreamReader(System.in));
try {
lastName = bfr.readLine();
} catch (IOException e) {
e.printStackTrace();
return;
}
The next step is to query the database: retrieve all students with the provided name. We use a simple SQL-query to do this.
select * from Student where Name.LastName = ?
This is a prepared query where ? is substituted at execution time by the current parameter of the execute method. Thus the query can be reused with other parameter values later. The result of db.command(<SQL-query>).execute(<parameter>) is an Iterable which is used in a for-loop to print the data of all students with the provided name.
// Search for all students with the provided LastName
OSQLSynchQuery <Vertex> studQuery = new OSQLSynchQuery <Vertex> ("select * from Student where Name.LastName = ?");
Iterable <Vertex> studs = db.command(studQuery).execute(lastName);
if (studs.iterator().hasNext()) {
for (Vertex stud : studs) {
Vertex name = stud.getProperty("Name");
System.out.println ((String) name.getProperty("FirstName") + " " + (String) name.getProperty("LastName") + ", DOB: " + stud.getProperty("DOB") + ", Stud-Nr: " + stud.getProperty("StudentNr"));
}
} else {
System.out.println("No student found with this name!");
return;
}
To select exactly one student even if more students exist with the same name, the user is asked to provide the desired student number.
//User input: Student Number
String studNr;
System.out.println ("Type the student number: ");
try {
studNr = bfr.readLine();
} catch (IOException e) {
e.printStackTrace();
return;
}
In a similar query as above but with the student number as parameter instead of the name the student data are retrieved and printed. Since the student number is unique for students we need not iterate the result set.
// Retrieve the student and all his courses
studQuery = new OSQLSynchQuery <Vertex> ("select * from Student where StudentNr = ?");
studs = db.command(studQuery).execute(studNr);
Vertex stud = studs.iterator().next();
if (stud == null) {
System.out.println ("No student with this student number!");
return;
} else {
Vertex name = stud.getProperty("Name");
System.out.print ("Grade report for ");
System.out.println ((String) name.getProperty("FirstName") + " " + (String) name.getProperty("LastName") + ", DOB: " + stud.getProperty("DOB") + ", Stud-Nr: " + stud.getProperty("StudentNr"));;
}
The next part of the program is the grade report. The student's grades are stored in the attends edges. Therefore we retrieve all attends edges that start at the selected student.
Each edge connects two vertices: out specifies the source vertex where the edge comes out and in specifies the target vertex where the edge goes into. out must be our selected student. Instead of doing some String-operations and insert the rid (record id) of the student into the where condition we again use a prepared query and get the condition where out = ?.
Instead of a join operation to connect the attends edges with the corresponding course which is necessary in relational databases we can retrieve the course easily by the in property of the attends edge.
OSQLSynchQuery <Vertex> courseQuery = new OSQLSynchQuery <Vertex> ("select in, Semester, Attempt, Grade from attends where out = ? order by Semester");
Iterable <Vertex> result = db.command(courseQuery).execute(stud.getId());
for (Vertex item : result) {
Vertex course = item.getProperty("in");
System.out.println (course.getProperty("CourseNr") + " " + course.getProperty("Subject") + " " + item.getProperty("Semester") + " " + item.getProperty("Attempt") + " " + item.getProperty("Grade"));
}
Extend the Program to Assign a New Course to the Selected Student
After retrieving and printing the information about all courses the selected student has attended so far we want to assign a new course to the student. The user has to select a course. A list of all courses is printed and the user is asked for the course number. This course is retrieved from the database.
// Search for all courses with the provided courseName
OSQLSynchQuery <Vertex> courseQuery = new OSQLSynchQuery <Vertex> ("select * from Course where Subject LIKE ?");
Iterable <Vertex> courses = db.command(courseQuery).execute("%" + courseName + "%");
if (courses.iterator().hasNext()) {
for (Vertex course : courses) {
System.out.println ((String) course.getProperty("Subject") + " " + course.getProperty("CourseNr"));;
}
} else {
System.out.println("No course found with this name!");
return;
}
//User input: Course Number
String courseNr;
System.out.println ("Type the course number: ");
try {
courseNr = bfr.readLine();
} catch (IOException e) {
e.printStackTrace();
return;
}
// Select course with the provided courseNr
Vertex chosenCourse;
OSQLSynchQuery <Vertex> courseQueryNr = new OSQLSynchQuery <Vertex> ("select * from Course where CourseNr = ?");
Iterable <Vertex> coursesNr = db.command(courseQueryNr).execute(courseNr);
if (coursesNr.iterator().hasNext()) {
chosenCourse = coursesNr.iterator().next();
} else {
System.out.println("No course found with this course number!");
return;
}
We cannot just create a new attends edge between the selected student and the selected course. First we have to check whether the student has already successfully attended all required courses. Required courses are not only directly connected to the selected course via a required edge but can also be connected recursively by a chain of other courses and required edges. OrientDB allows to retrieve a graph recursively using the traverse command as a SQL extension.
This is the appropriate traverse command here:
traverse outE('requires'), requires.in from <Course Id>
The from part defines the starting point, a vertex, an edge or a list of vertices or edges, where the traversal begins. Behind the traverse keyword the connected objects are listed which are used to follow a path in the graph. Let's look at the example above: The traversal begins at a certain course id, let's assume at #13:25 representing "Artificial Intelligence". outE("requires") selects all requires edges starting at #13:25, in this case only one edge #15:5. With requires.in all vertices are selected where the required edges of the last step end. In our case it is the course "Software Engineering" with the Id #13:22. Now the traversal recursively continues with this course Id and retrieves the requires edge #15:3 and the course vertex #13:20 which is "Programming". (The mentioned IDs are only examples and may vary if you implement and run the application on your computer.)
Since we need only the courses, not the requires edges, we surround the traverse command with a select that extracts only courses. As you already know the result is an Iterable. This is transformed into an ArrayList.
// Find all courses which are required for the selected course
OSQLSynchQuery dependentCourseQuery = new OSQLSynchQuery <Vertex> ("select * from (traverse outE('requires'), requires.in from " + chosenCourse.getId() + ") where @class = 'Course'");
Iterable <Vertex> requiredCourses = db.command(dependentCourseQuery).execute();
ArrayList <String> requiredCoursesList = new ArrayList <String> (); // transform Iterable into List
for (Vertex course : requiredCourses) requiredCoursesList.add(course.getId().toString());
The list of required courses must be compared to the list of courses the selected student has already attended. These already attended courses could be retrieved using SQL. But this time the tutorial shows another possibility: navigational access using the Tinkerpop Blueprints API. stud.getEdges(Direction.OUT, "attends") retrieves all attends edges that start at the selected Student vertex stud. If a is one of these attends edges a.getVertex(Direction.IN) retrieves the corresponding Course vertex.
Each of the retrieved courses is deleted from the list of required courses. Of course this is done only for courses where the student has achieved a success grade ("A", "B" or "C"). Also the new course is deleted from this list.
requiredCoursesList.remove(chosenCourse.getId().toString());
for (Edge a : stud.getEdges(Direction.OUT, "attends")) {
String grade = a.getProperty("Grade");
if (grade != null)
if (grade.equals("A") || grade.equals("B") || grade.equals("C")) requiredCoursesList.remove(a.getVertex(Direction.IN).getId().toString());
}
If the list of required courses is empty afterwards the check succeeded and the selected student can be assigned to the new course by Edge a = db.addEdge(null, stud, chosenCourse, "attends");. The user is asked to provide information about the grade, the semester and the attempt and the properties are set accordingly, e.g. a.setProperty("Grade", grade);. Finally the transaction is committed.
// Assign student stud to course
if (requiredCoursesList.isEmpty()) {
//User input: Course Number
String grade;
String attempt;
String semester;
try {
System.out.println ("Type the grade: ");
grade = bfr.readLine();
System.out.println("Type the attempt: ");
attempt = bfr.readLine();
System.out.println ("Type the semester: ");
semester = bfr.readLine();
} catch (IOException e) {
e.printStackTrace();
return;
}
Edge a = db.addEdge(null, stud, chosenCourse, "attends");
a.setProperty("Grade", grade);
a.setProperty("Attempt", Integer.valueOf(attempt).intValue());
a.setProperty("Semester", Integer.valueOf(semester).intValue());
System.out.println("Check o.k., student assigned to course.");
db.commit();
} else {
System.out.println ("Check failed, student not assigned to course. Missing courses for student: ");
for (String cid : requiredCoursesList ) {
System.out.println ((String) db.getVertex(cid).getProperty("Subject"));
}
}
At last the connection to the database is closed.
db.shutdown();
factory.close();
}
}