To use a JDBC driver to connect to a database directly in a Spring Boot application, we will need to add the following dependencies to the pom.xml file:

<dependencies>
  ...
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
  </dependency>
</dependencies>

With the spring-boot-starter-jdbc module, Spring Boot will spin up an instance of javax.sql.DataSource and make it available as a bean in the Spring container. It will also configure a database connection pool. The mysql-connector-java library is the JDBC driver for MySQL database and you can see why we didn't specify <version> for mysql-connector-java, which is normally required. Later in this chapter, we will discuss how Spring Boot works behind the scenes on this, as well as how it automatically configures the data source bean and connection pool.

The next thing we need to do is to configure the parameters that Spring needs to instantiate DataSource. Let's add the following properties to application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/app_messages?useSSL=false
spring.datasource.username=<username>
spring.datasource.password=<password>
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

As you can see, the url, username, password, and driver-class objects are the minimal settings that we need to provide. If you need to ensure that the connection uses UTF-8 encoding , you can change the URL to jdbc:mysql://localhost:3306/app_messages?useUnicode=true&amp;characterEncoding=UTF-8. You will need to replace <username> and <password> with those of your own. The prefix of the property's spring.datasource. key is required by the spring-boot-starter-jdbc starter.

Now, let's make changes to the MessageRepository class. We need to ask Spring to inject a DataSource instance to MessageRepository so that we can get a database connection from it, as follows:

public class MessageRepository {
  private DataSource dataSource;
  public MessageRepository(DataSource dataSource) {
    this.dataSource = dataSource;
  }
  ...
}

The next thing is to implement the saveMessage() method. Here is what it looks like:

1. public Message saveMessage(Message message) {
2.    Connection c = DataSourceUtils.getConnection(dataSource);
3.    try {
4.      String insertSql = "INSERT INTO message
        s (`id`, `text`, 
        `created_date`) VALUE (null, ?, ?)";
5.      PreparedStatement ps = c.prepareStatement(insertSql, 
        Statement.RETURN_GENERATED_KEYS);
6.      // Prepare the parameters for the SQL
7.      ps.setString(1, message.getText()); 
8.      ps.setTimestamp(2, new 
        Timestamp(message.getCreatedDate().getTime()));
9.      int rowsAffected = ps.executeUpdate();

In line 1, we change our saveMessage() method to return the newly saved message object. In line 2, we use DataSourceUtils, a helper class of Spring, to get a database connection. And then, in line 4, we prepare our insert SQL. Since we use AUTO_INCREMENT for the id column of the messages table, we use the null value for it so that the database will generate a value for the ID. In line 5, we create PreparedStatement by passing the SQL and specify that we need the database to return the generated message id. In lines 7 to 8, we set the other two parameters. In line 9, we use the executeUpdate() method of PreparedStatement to execute the SQL against the database, and the return value of this method is the number of rows affected by the execution:

10.     if (rowsAffected > 0) {
11.       // Getting the newly saved message id
12.       ResultSet result = ps.getGeneratedKeys();
13.       if (result.next()) {
14.         int id = result.getInt(1);
15.         return new Message(id, message.getText(), 
            message.getCreatedDate());
16.       } else {
17.         logger.error("Failed to retrieve id. No row in result 
            set");
18.         return null;
19.       }
20.     } else {
21.       // Insert did not succeed
22.       return null;
23.     }

In lines 10 to 20, we check whether any row is affected. Since we're doing an insert, without any error, there should be one row affected, which is the newly inserted row. In line 12, we call the getGeneratedKeys() method of PreparedStatement to get the id of our message object from a ResultSet object. And you will need to call its next() method to move the cursor forward so that you can read the value in the ResultSet. Then, we create a new Message object with all three fields. Lines 16 to 22 are for dealing with failure. If there is no row affected or ResultSet is empty, we will return a null value, indicating that the message wasn't saved successfully. By now, we have completed a large part of this method. One last thing is that we must close the connection that we opened in line 2, otherwise, the connection won't be returned to the connection pool and reused by others:

24.   } catch (SQLException ex) {
25.     logger.error("Failed to save message", ex);
26.     try {
27.       c.close();
28.     } catch (SQLException e) {
29.       logger.error("Failed to close connection", e);
30.     }
31.   } finally {
32.     DataSourceUtils.releaseConnection(c, dataSource);
33.   }
34.   return null;
35. }

In lines 24 to 33, it is all about cleaning up connections. And in line 34, if the execution reaches here, it means that we didn't save the message object successfully.

As you can see, the JDBC API is a very low-level API and using it requires a lot of boilerplate code. You should avoid using it unless you don't have a choice.

Before we move on to implementation with Spring JDBC, let's change our MessageService and MessageController to provide an HTTP API for saving messages.

Here are the changes to the save() method of MessageService:

public Message save(String text) {
  return repository.saveMessage(new Message(text));
}

We make it return the result of the saved message. And we need to change our Message class as follows:

1. public class Message {
2.    private Integer id;
3.    private String text;
4.    private Date createdDate;
5.    public Message(String text) {
6.      this.text = text;
7.      this.createdDate = new Date();
8.    }
9.    public Message(int id, String text, Date createdDate) {
10.     this.id = id;
11.     this.text = text;
12.     this.createdDate = createdDate;
13.   }
14.   public Integer getId() {return id;}
15.   public String getText() {return text;}
16.   public Date getCreatedDate() {return createdDate;}
17.   // equals() and hashCode() omitted
18. }

In lines 2 and 4, we add a new id field and createdDate. The reason the id field is declared an Integer type is that, for a new Message object, it doesn't have an id generated, and its value will be null. Lines 5 to 8 include a constructor that takes a single parameter text. This is for creating a new message. And we will generate createdDate for it. Lines 9 to 13 include another constructor that is used for reconstituting a message object from the database. And in lines 14 to 16, the class only offers getters so you cannot change the value of any field. For now, this is good enough for our app.

Now, let's add a handler in MessageController so that clients can send an HTTP POST request like the following to save messages:

POST /messages
Content-Type: application/json
Request Body: {"text":"Add message here"}

Here is what our handle looks like:

@PostMapping("")
@ResponseBody
public ResponseEntity<Message> saveMessage(@RequestBody MessageData data){
  Message saved = messageService.save(data.getText());
  if (saved == null) {
    return ResponseEntity.status(500).build();
  }
  return ResponseEntity.ok(saved);
}

We use the @PostMapping annotation to map it to the /messages path. The return value of the handler is a Spring ResponseEntity, which allows us to set the response status, body, and headers. And this handler takes one parameter of the MessageData type, which we will create shortly. It is annotated with @RequestBody. In this way, Spring will convert the JSON format String passed in the HTTP request body to an instance of MessageData, so that we don't need to dig into the HTTP request to get the request body ourselves. Inside the handler, we simply pass the text field to MessageService and return the newly saved message using the ResponseEntity.ok() method, or, when it fails, we return the 500 status, an internal server error, to the client, telling them that there is something wrong on the server side.

Here is what the MessageData class looks like:

public class MessageData {
  private String text;
  public String getText() {return this.text;}
  public void setText(String text) {this.text = text;}
}

As you can see, it is a Plain Old Java Object (POJO) with only one text field. This MessageData is the definition of our API's request body. If you use another name for the text field, for example, message, the request body that the client sends over will then be changed to {"message":"Add message here"} accordingly.

Now, if you restart the application, we are ready to test it out. You can use an API test tool to send an HTTP POST request, for example, Postman (https://www.getpostman.com/), a free application with a friendly UI:

As shown in Figure 3.5, in Postman, after you create a new request and select POST for the method and the URL of the API, remember to add the Content-Type header with the application/json value. In this way, Spring knows that the request body is a JSON format string:

As shown in Figure 3.6, in the Body tab, select Raw and JSON (application/json) and then put in the JSON string {"text":"Hello, Spring Boot!"}. After that, click the Send button. You should be able to see the result underneath without any difficulty, similar to the one in the screenshot.