Here is our schema we will be using for this activity
CREATE TABLE activity.student
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(256) NOT NULL,
last_name VARCHAR(256) NOT NULL,
year INT NOT NULL,
gpa DECIMAL NOT NULL
);
CREATE TABLE activity.class
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(256) NOT NULL UNIQUE,
units INT NOT NULL
);
CREATE TABLE activity.student_class
(
student_id INT NOT NULL,
class_id INT NOT NULL,
PRIMARY KEY (student_id, class_id),
FOREIGN KEY (student_id) REFERENCES activity.student (id)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (class_id) REFERENCES activity.class (id)
ON UPDATE CASCADE ON DELETE CASCADE
);MySQL Provides us with a good amount of functions for creating JSON strings. This can be very usefull when mapping data to our Models.
Let us first take this query for example:
SELECT c.id, c.name, c.units
FROM class c
JOIN student_class sc ON c.id = sc.class_id
WHERE sc.student_id = :studentIdThis query would require us to iterate over the ResultSet to be able to creat a List of StudentClass. But we can save some time by using two MySQL functions:
JSON_ARRAYAGG()JSON_OBJECT()
Lets first rewrite the previous query using one of these two functions:
SELECT JSON_OBJECT('id', c.id, 'name', c.name, 'units', c.units) as jsonString
FROM class c
JOIN student_class sc ON c.id = sc.class_id
WHERE sc.student_id = :studentIdJSON_OBJECT takes a list of arguments in the form: key, value, key, value, ... (As many key value pairs). And it will return a JSON String. So for the previous query it calling rs.getString("jsonString") would return this:
{
"id": 1,
"name": "Intro to CS",
"units": 4
}Now we are getting our data in the form of a JSON, but we still need to iterate over all the rows and grab each json string. we can remove the need to do that by using the JSON_ARRAYAGG() function like this:
SELECT JSON_ARRAYAGG(JSON_OBJECT('id', c.id, 'name', c.name, 'units', c.units)) as jsonArrayString
FROM class c
JOIN student_class sc ON c.id = sc.class_id
WHERE sc.student_id = :studentIdNow when we run this query and do rs.getString("jsonArrayString") we will get a JSON Array String composed of all the rows as JSON Objects:
[
{
"id": 1,
"name": "Intro to CS",
"units": 4
},
{
"id": 2,
"name": "Data Structure",
"units": 6
}
]So now we get a single JSON Array String that we can use to map to our data. Normally we had Spring automatically map JSON to Model for us, but we can do it manually when dealing with JSON Directly by using the ObjectMapper class.
We get our ObjectMapper by asking spring for it in our @Autowired constructor like so:
@RestController
public class SQLController
{
private final ObjectMapper objectMapper;
@Autowired
public SQLController(ObjectMapper objectMapper)
{
this.objectMapper = objectMapper;
}
}ObjectMapper has a function: ObjectMapper::readValue(String jsonString, Class<?> modelClass)
This function takes two arguments:
- String jsonString - the JSON String to map
- Class<?> modelClass - the Model's class we want to map to.
ObjectMapper objectMapper; // We get this from our @Autowired constructor
String jsonArray = this.template(...); // We execture our query to get the JSON Array String
StudentClass[] studentClassArray =
objectMapper.readValue(jsonArrayString, StudentClass[].class);
// Notice we use Object[].class notation for Arrays
// If we just had a JSON Object rather than a array we would just use StudentClass.classThere are cases where we will want to retrieve a large collection of data from our database that may seem to required multiple queries. However, we can utilize subqueries to be able to do this in a single query (given that the query is not too costly)
Lets say we wanted to get a student's details as well as a list of their classes. This would normally require two queries:
SELECT id, first_name, last_name, year, gpa
FROM student s
WHERE s.id = :studentId;
SELECT JSON_ARRAYAGG(JSON_OBJECT('id', c.id, 'name', c.name, 'units', c.units)) AS jsonArrayString
FROM class c
JOIN student_class sc ON c.id = sc.class_id
WHERE sc.student_id = :studentIdBut if we were to rewrite the function with a subquery we could do this in one action:
SELECT id, first_name, last_name, year, gpa,
(SELECT JSON_ARRAYAGG(JSON_OBJECT('id', c.id, 'name', c.name, 'units', c.units))
FROM class c
JOIN student_class sc ON c.id = sc.class_id
WHERE sc.student_id = :studentId) AS jsonArrayString -- Notice that we name this column as classes
FROM student s
WHERE s.id = :studentId;Sometimes we need to do more with the subquery, such as label as DISTINCT and give a ORDER BY we can do this by having another subquery:
SELECT id, first_name, last_name, year, gpa,
(SELECT JSON_ARRAYAGG(JSON_OBJECT('id', c.id, 'name', c.name, 'units', c.units))
FROM (SELECT DISTINCT c.id, c.name, c.units
FROM class c
JOIN student_class sc ON c.id = sc.class_id
WHERE sc.student_id = :studentId
ORDER BY c.name) as c) AS jsonArrayString
FROM student s
WHERE s.id = :studentId;This query would retrieve 6 columns:
id- Integerfirst_name- Stringlast_name- Stringyear- Integergpa- Doubleclasses- String (A JSON Array String we can map)
Sometimes when searching our database for strings we want to search by sub-string rather than an exact match. We do this by using both the LIKE operator (rather than the = operator) and having the string parameter start and end with the % character, like so:
public Student studentByFirstName(String firstName)
{
String SQL =
"SELECT id, first_name, last_name, year, gpa " +
"FROM student s " +
"WHERE first_name LIKE :firstName; ";
// We add the '%' to the value rather than in the SQL Query
String wildcardSearch = '%' + firstName + '%';
MapSqlParameterSource source = new MapSqlParameterSource()
.addValue("firstName", wildcardSearch, Types.VARCHAR);
return this.template.queryForObject(SQL, source, this::mapToStudent);
}For some queries we will not be able to use a 'static' SQL String because the FROM and WHERE clause will depend on the users request.
Lets say a user wants to search for students depending on their firstName, we would use this query:
SELECT id, first_name, last_name, year, gpa
FROM student s
WHERE first_name LIKE :firstName;But then lets say the user wants to search for students depending on their first name and the classes they are in, meaning we would now need to JOIN the class table to our query like so:
SELECT DISTINCT id, first_name, last_name, year, gpa
FROM student s
JOIN student_class sc ON s.id = sc.student_id
JOIN class c ON sc.class_id = c.id
WHERE c.name LIKE :className AND s.first_name LIKE :lastName;Because of this we would need to dynmaicly create the 'WHERE' and 'FROM' clauses.
Here we prepair two queries:
This one for when the user is not searching by class:
SELECT id, first_name, last_name, year, gpa
FROM student sThis one for when the user is searching for class:
SELECT DISTINCT id, first_name, last_name, year, gpa
FROM student s
JOIN student_class sc ON s.id = sc.student_id
JOIN class c ON sc.class_id = c.idThen we would add the where clause depending on the search parameters the user gives us. We do this by using Java's StringBuilder:
public List<Student> search(SearchRequest request)
{
StringBuilder sql;
MapSqlParameterSource source = new MapSqlParameterSource();
boolean whereAdded = false;
// Here we create the inital StringBuilder depending on the query that requires it
// In this case the need to search by classname requires we have the JOIN class clause
if (request.getClassName() != null) {
sql = new StringBuilder(STUDENT_WITH_CLASS);
sql.append(" WHERE c.name LIKE :className ");
// This allows for WILDCARD Search
String wildcardSearch = '%' + request.getClassName() + '%';
source.addValue("className", wildcardSearch, Types.VARCHAR);
whereAdded = true;
} else {
sql = new StringBuilder(STUDENT_NO_CLASS);
}
...
}Notice that we also have a boolean whereAdded. We have this because we need to know if we need to start the where clause (with a WHERE) or if we need to add to the clause (with a AND) like so:
public List<Student> search(SearchRequest request)
{
... // From previous example
if (request.getFirstName() != null) {
if (whereAdded) {
sql.append(" AND ");
} else {
sql.append(" WHERE ");
whereAdded = true;
}
sql.append(" s.first_name LIKE :firstName ");
source.addValue("firstName", request.getFirstName(), Types.VARCHAR);
}
... // We repeat as neccessary for each value that we need to search by
// We then 'build' the sql using StringBuilder::toString()
List<Student> students = this.template.query(
sql.toString(),
source,
(rs, rowNum) ->
new Student()
.setId(rs.getLong("id"))
.setFirstName(rs.getString("first_name"))
.setLastName(rs.getString("first_name"))
.setYear(rs.getInt("year"))
.setGpa(rs.getDouble("gpa"))
);
return students;
}