Web services - a different way to interact with a database
In all of our database projects up to this point we have written applications that interact directly with a MySQL database. For projects that we want to release to the public using a direct connection to a database is both impractical and unsafe. The biggest problem is security - a user who has the necessary technical skills can reverse-engineer our application to learn the user name and password that the application uses to connect to the database server. With that knowledge they could break into our database to steal data or carry out other harmful activities.
Given these security concerns, the best practice for providing access to data in a database is to construct our application with a secure middle layer that clients can interact with. Clients will interact with this middle layer (common referred to as middleware), and that middle layer of software in turn will interact with a database. Since the middleware runs on a server and is not released to the general public, malicious users can not reverse-engineer the middleware to get direct access to the database. Furthermore, in almost all instances the database software that the middleware communicates with sits behind a firewall and is not reachable over the open internet.
The Spring project is a long running Java project that supplies components needed to construct Java middleware application. The Spring project started out as a project to construct back ends for web applications, but has since branched out to enable construction of a wider range of server types. The Spring Boot project is a subproject of Spring that aims to make it quick and easy to construct small, simple middleware servers. Spring Boot applications communicate with clients by using HTTP, which is that language that web browsers use to communicate with web servers.
In this course we will be using Spring Boot to construct middleware applications that can serve data from a MySQL database to web and mobile applications.
The Quiz example
The first example we are going to work with is an application that is delivers online quizzes. The database for this quiz application consists of four tables.
The students table stores a list of names of students who will be taking the quizzes.
| Column Name | Data Type | Description |
|---|---|---|
| id | INT | Primary key |
| name | VARCHAR(45) | Student's name |
The quizzes table stores information about individual quizzes.
| Column Name | Data Type | Description |
|---|---|---|
| id | INT | Primary key |
| title | VARCHAR(45) | Name of the quiz |
The questions table stores the questions for the quizzes.
| Column Name | Data Type | Description |
|---|---|---|
| id | INT | Primary key |
| subject | VARCHAR(45) | The subject of the quiz |
| question | VARCHAR(256) | The question |
| choices | VARCHAR(128) | Comma separated list of multiple choice options |
| answer | VARCHAR(1) | Which option is the correct answer |
| quiz | INT | The quiz this question belongs to |
The responses table stores the answers that students have given to the quiz questions.
| Column Name | Data Type | Description |
|---|---|---|
| id | INT | Primary key |
| student | INT | Who answered this question |
| question | INT | The id number of the question the student answered |
| response | VARCHAR(1) | Which option the student selected |
Java Objects
The Spring framework is an object-oriented framework for building web applications. When we pull data from a database table in a Spring application, we will transform that data into a Java object. The first step in building our server application is to define a simple Java class for each of the tables in the database. For example, to pull data from the table of questions in the quiz data base we will set up a Question class.
public class Question {
private int id;
private String subject;
private String question;
private String choices;
private int quiz;
public Question() {}
public int getId() { return id; }
public String getSubject() { return subject; }
public String getQuestion() { return question; }
public String getChoices() { return choices; }
public int getQuiz() { return quiz; }
public void setId(int id) { this.id = id; }
public void setSubject(String subject) { this.subject = subject; }
public void setQuestion(String question) { this.question = question; }
public void setChoices(String choices) { this.choices = choices; }
public void setQuiz(int quiz) { this.quiz = quiz; }
}
These types of simple Java classes are commonly called "POJOs", which stands for plain old Java objects. The most important requirement for a POJO is that it should have get and set methods for each of its member variables. When setting up a POJO to match a database table, it is also helpful to give the member variables of the POJO the same names as the corresponding columns in the database table.
Since the purpose of this server application is to serve questions up to students, we are careful here to not include the answer field in the Question object. That way when we serve up questions from this server application we don't accidentally include the answers in the questions we send out.
To facilitate pulling these objects out of the database we will also need a second set of classes, called row mappers. This classes contain a method that can read data from a row in a ResultSet and turn that data into an object of the appropriate type. For example, here is the RowMapper class that reads rows from the questions table and makes Question objects.
public class QuestionRowMapper implements RowMapper<Question> {
@Override
public Question mapRow(ResultSet row, int rowNum) throws SQLException {
Question q = new Question();
q.setId(row.getInt("id"));
q.setSubject(row.getString("subject"));
q.setQuestion(row.getString("question"));
q.setChoices(row.getString("choices"));
q.setQuiz(row.getInt("quiz"));
return q;
}
}
Building Repositories
The final step needed to work with the database in the Spring application is to set up DAO classes for each table in the database. Spring calls DAO classes "repositories." We will need to set up a repository class for each table in the database.
Here is the repository class for questions
@Repository
public class QuestionDAO {
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Question> findByQuiz(int quiz) {
String sql = "SELECT id, subject, question, choices, quiz FROM questions WHERE quiz=?";
RowMapper<Question> rowMapper = new QuestionRowMapper();
return jdbcTemplate.query(sql, rowMapper, quiz);
}
}
Two things are required to set up a repository class. We have to annotate the class with the @Repository annotation. This identifies the class as a repository to the Spring framework, making it possible for the framework to set up one of these repository objects automatically whenever the application needs it. Secondly, we need to implement an appropriate set of methods for all of the actions we will need to perform: these include find methods, a save method, and a remove method.
The repository class contains a member variable of type JdbcTemplate. This is a class that will handle all of our database interactions. We attach the @Autowired annotation to this member variable to tell Spring that it is responsible for initializing this member variable for us automatically. Spring uses a mechanism call dependency injection to initialize the member variable. We need to apply the annotation to tell Spring boot to do this for us.
The Spring JdbcTemplate class contains a number of useful methods that implement various common database interactions. This class uses one of these JdbcTemplate methods, query. To use this method we start by setting up a String that contains some SQL code we want to run. These SQL strings are essentially the strings you would set up in preparation for using a prepared statement in JDBC. Since these strings typically have placeholders, we also pass data to the JdbcTemplate methods that will we will use to fill in those placeholders. In addition, we will also have to pass row mapper objects to the query methods. The query method is designed to return a list of objects that the row mapper has made for us.
In addition to the query method the JdbcTemplate class also offers queryForObject and queryForList methods: we will see examples of these methods in use in a later example. You can also use the JdbcTemplate class to update and insert objects in a database table. We will see examples of this later.
REST
The server we are going to construct will be built using the REST architecture. REST is a very popular architecture for servers that serve up objects on the Internet. Here are the basic elements of the REST architecture.
- Clients and servers communicate using the HTTP protocol, which is the language that web browsers use to communicate with web servers.
- HTTP is an example of a request/response protocol. In a typical interaction between a client and a server the client sends a request to the server and the server replies with a response.
- An HTTP request consists of a header and a body. The header contains a URL that specifies what resource the client want to access and an HTTP verb which specifies what the client wants to do with it. The body can contain text that the client wants to send to the server.
- An HTTP also consists of a header and a body. The header contains a numerical result code that indicates whether or not the request was successful. The body contains text that the server has sent in response to the request.
- In the REST architecture, clients and servers can send objects to each other by encoding those objects in a text format and putting that text in the body of a request or a response. The two most popular encoding systems used with REST are XML and JSON.
The first step in constructing a REST server is to set up a list of URLs and verbs that the server will respond to. Each URL will take the following form:
http://localhost:8085<path>
where <path> is the portion of the URL that will differ from request to request. (This URL assumes that both the server and the client are running on the same computer, so the server address is simply localhost. If the server were running on a different computer somewhere on the Internet we would have to replace localhost with that computer's address.)
For this example we are going to support just one URL, which runs a search for a list of questions from a particular quiz.
http://localhost:8085/questions?quiz=xxxx
with the xxxx parts replaced with the quiz id number. In this case, the <path> to the resource is /questions and the ?quiz=xxxx portion of the URL is an example of a query parameter that we use for the search.
Spring RestControllers
A Spring server uses a set of special classes called REST controllers to respond to REST requests.
Here is the code for the sole REST controller we will need.
@RestController
@RequestMapping("/questions")
@CrossOrigin(origins="*")
public class QuestionController {
private QuestionDAO questionDAO;
public QuestionController(QuestionDAO dao) {
this.questionDAO = dao;
}
@GetMapping(params={"quiz"})
public List<Question> questionsByQuiz(@RequestParam int quiz) {
return dao.findByQuiz(quiz);
}
}
Spring uses annotations extensively to configure classes, and REST controllers use a number of these annotations.
The @RestController annotation identifies this class as a REST controller.
The @RequestMapping annotation specifies the start of the <path> that this controller will respond to.
The @CrossOrigin annotation is a security rule telling the server to respond to requests from all sources.
The @GetMapping annotation on a method specifies the method is designed to respond to a GET request, which is the simplest type of request. The annotation also indicates that the URL will contain request parameters.
The @RequestParam annotation in front of a method parameter tells Spring to place the value of a request parameter into the method parameter when calling the method.
Since all controllers will have to deal with data, a typical controller will contain one or more member variables that are repositories. In the methods that handle requests, the controller will use that repository to store and retrieve objects. Note that we don't have to worry about setting up these repositories: all we have to do is to provide a constructor for the controller class that initializes the member variable. The Spring framework will set up the necessary repository for us and call the constructor automatically when the application starts up.
Configuring the server
The final step needed to get our application ready to run is to provide some configuration information. This includes telling Spring how to configure the server and how to connect to the MySQL database. This configuration information goes into a properties file. You will find the main properties file for the application, application.properties, in the project view under Other Sources.
Here is the content of that properties file.
server.port=8085 spring.datasource.url=jdbc:mysql://localhost:3306/quiz spring.datasource.username=student spring.datasource.password=Cmsc250! spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.tomcat.max-wait=20000 spring.datasource.tomcat.max-active=50 spring.datasource.tomcat.max-idle=20 spring.datasource.tomcat.min-idle=15
These properties tell Spring to have the application listen for connections on port 8085, and give the necessary connection information for the MySQL database.
Interacting with the server
To demonstrate how to interact with the server application we just built you should download the project from the link at the top of these notes. In the project folder you will find a database folder. Use the import feature in the MySQL workbench to import the example daycare database into MySQL, and then run the project in NetBeans to launch the server.
Once the server is up and running you can interact with it in one of two ways. The simplest method is to use a browser. Browsers can only be used to send GET requests to the server, but do make an easy way to check whether or not the server is working correctly.
Open a browser and paste in the URL
http://localhost:8085/questions?quiz=1
If everything is working correctly, the server should send back a response with response body
[{"id":1,"subject":"Reading values","question":"What statement does SQL use to retrieve data from a table?","choices":"fetch,select,read,get","quiz":1},
{"id":2,"subject":"Making a new row","question":"What statement does SQL use to add a new row to a table?","choices":"create,add,insert,make","quiz":1},
{"id":3,"subject":"Removing a row","question":"What statement does SQL use to remove one or more rows from a table?","choices":"remove,delete,kill,wipe","quiz":1}]
which encodes a list of questions for that quiz.
A much more sophisticated tool that you can use to interact with a REST server is the Postman application. You can download Postman from https://www.getpostman.com/apps.
The Postman application allows you to construct requests using all available HTTP verbs, and provides a very nice graphical interface for constructing your requests and viewing responses.
Extensive documenation on how to use Postman is available at https://learning.getpostman.com/.