为 Spring Boot 项目添加数据库支持

In this part of the tutorial, you'll add and configure a database to your project using JDBC. In JVM applications, you use JDBC to interact with databases. For convenience, the Spring Framework provides the JdbcTemplate class that simplifies the use of JDBC and helps to avoid common errors.

Add database support

The common practice in Spring Framework based applications is to implement the database access logic within the so-called service layer – this is where business logic lives. In Spring, you should mark classes with the @Service annotation to imply that the class belongs to the service layer of the application. In this application, you will create the MessageService class for this purpose.

In the DemoApplication.kt file, create the MessageService class as follows:

import org.springframework.stereotype.Service
import org.springframework.jdbc.core.JdbcTemplate

@Service
class MessageService(val db: JdbcTemplate) {
    fun findMessages(): List<Message> = db.query("select * from messages") { response, _ ->
        Message(response.getString("id"), response.getString("text"))
    }

    fun save(message: Message) {
        db.update(
            "insert into messages values ( ?, ? )",
            message.id, message.text
        )
    }
}

Update the MessageController class

Update MessageController to use the new MessageService class:

import org.springframework.web.bind.annotation.RequestBody
import org.springframework.web.bind.annotation.PostMapping

@RestController
class MessageController(val service: MessageService) {
    @GetMapping("/")
    fun index(): List<Message> = service.findMessages()

    @PostMapping("/")
    fun post(@RequestBody message: Message) {
       service.save(message)
    }
}

Update the MessageService class

The id for Message class was declared as a nullable String:

data class Message(val id: String?, val text: String)

It would not be correct to store the null as an id value in the database though: you need to handle this situation gracefully.

Update your code to generate a new value when the id is null while storing the messages in the database:

import java.util.UUID

@Service
class MessageService(val db: JdbcTemplate) {
    fun findMessages(): List<Message> = db.query("select * from messages") { response, _ ->
        Message(response.getString("id"), response.getString("text"))
    }

    fun save(message: Message) {
        val id = message.id ?: UUID.randomUUID().toString()
        db.update(
            "insert into messages values ( ?, ? )",
            id, message.text
        )
    } 
}

The application code is ready to work with the database. It is now required to configure the data source.

Configure the database

Configure the database in the application:

1. Create schema.sql file in the src/main/resources directory. It will store the database object definitions:

   

2. Update the src/main/resources/schema.sql file with the following code:

   CREATE TABLE IF NOT EXISTS messages (
   id       VARCHAR(60)  PRIMARY KEY,
   text     VARCHAR      NOT NULL
   );
   

   It creates the messages table with two columns: id and text. The table structure matches the structure of the Message class.

3. Open the application.properties file located in the src/main/resources folder and add the following application properties:

   spring.datasource.driver-class-name=org.h2.Driver
   spring.datasource.url=jdbc:h2:file:./data/testdb
   spring.datasource.username=name
   spring.datasource.password=password
   spring.sql.init.schema-locations=classpath:schema.sql
   spring.sql.init.mode=always
   

   These settings enable the database for the Spring Boot application.
   See the full list of common application properties in the Spring documentation.

Add messages to database via HTTP request

You should use an HTTP client to work with previously created endpoints. In IntelliJ IDEA, use the embedded HTTP client:

1. Run the application. Once the application is up and running, you can execute POST requests to store messages in the database. Create the requests.http file in the src/main/resources folder and add the following HTTP requests:

   ```http request

   Post "Hello!"

   POST http://localhost:8080/ Content-Type: application/json

   { "text": "Hello!" }

   Post "Bonjour!"

   POST http://localhost:8080/ Content-Type: application/json

   { "text": "Bonjour!" }

   Post "Privet!"

   POST http://localhost:8080/ Content-Type: application/json

   { "text": "Privet!" }

   Get all the messages

   GET http://localhost:8080/ ```

2. Execute all POST requests. Use the green Run icon in the gutter next to the request declaration. These requests write the text messages to the database:

   

3. Execute the GET request and see the result in the Run tool window:

   

Alternative way to execute requests

You can also use any other HTTP client or the cURL command-line tool. For example, run the following commands in the terminal to get the same result:

curl -X POST --location "http://localhost:8080" -H "Content-Type: application/json" -d "{ \"text\": \"Hello!\" }"

curl -X POST --location "http://localhost:8080" -H "Content-Type: application/json" -d "{ \"text\": \"Bonjour!\" }"

curl -X POST --location "http://localhost:8080" -H "Content-Type: application/json" -d "{ \"text\": \"Privet!\" }"

curl -X GET --location "http://localhost:8080"

Retrieve messages by id

Extend the functionality of the application to retrieve the individual messages by id.

1. In the MessageService class, add the new function findMessageById(id: String) to retrieve the individual messages by id:

    import org.springframework.jdbc.core.query
   
    @Service
    class MessageService(val db: JdbcTemplate) {
   
        fun findMessages(): List<Message> = db.query("select * from messages") { response, _ ->
            Message(response.getString("id"), response.getString("text"))
        }
   
        fun findMessageById(id: String): List<Message> = db.query("select * from messages where id = ?", id) { response, _ ->
            Message(response.getString("id"), response.getString("text"))
        }
   
        fun save(message: Message) {
            val id = message.id ?: UUID.randomUUID().toString()
            db.update(
                "insert into messages values ( ?, ? )", 
                id, message.text
            )
        }
    }
   

   The .query() function that is used to fetch the message by its id is a Kotlin extension function provided by the Spring Framework. It requires an additional import import org.springframework.jdbc.core.query as demonstrated in the code above.

2. Add the new index(...) function with the id parameter to the MessageController class:

    import org.springframework.web.bind.annotation.*
   
    @RestController
    class MessageController(val service: MessageService) {
        @GetMapping("/")
        fun index(): List<Message> = service.findMessages()
   
        @GetMapping("/{id}")
        fun index(@PathVariable id: String): List<Message> =
            service.findMessageById(id)
   
        @PostMapping("/")
        fun post(@RequestBody message: Message) {
            service.save(message)
        }
    }
   

   The message id is retrieved from the context path by the Spring Framework as you annotated the new function by @GetMapping("/{id}"). By annotating the function argument with @PathVariable, you tell the framework to use the retrieved value as a function argument. The new function makes a call to MessageService to retrieve the individual message by its id.

   The query() function takes three arguments:

   • SQL query string that requires a parameter to run
   • `id`, which is a parameter of type String
   • RowMapper instance is implemented by a lambda expression

   The second parameter for the query() function is declared as a variable argument (vararg). In Kotlin, the position of the variable arguments parameter is not required to be the last in the parameters list.

Here is a complete code of the DemoApplication.kt:

package com.example.demo

import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication
import org.springframework.stereotype.Service
import org.springframework.jdbc.core.JdbcTemplate
import java.util.UUID
import org.springframework.jdbc.core.query
import org.springframework.web.bind.annotation.*


@SpringBootApplication
class DemoApplication

fun main(args: Array<String>) {
    runApplication<DemoApplication>(*args)
}

@RestController
class MessageController(val service: MessageService) {
    @GetMapping("/")
    fun index(): List<Message> = service.findMessages()

    @GetMapping("/{id}")
    fun index(@PathVariable id: String): List<Message> =
        service.findMessageById(id)

    @PostMapping("/")
    fun post(@RequestBody message: Message) {
        service.save(message)
    }
}

data class Message(val id: String?, val text: String)

@Service
class MessageService(val db: JdbcTemplate) {

    fun findMessages(): List<Message> = db.query("select * from messages") { response, _ ->
        Message(response.getString("id"), response.getString("text"))
    }

    fun findMessageById(id: String): List<Message> = db.query("select * from messages where id = ?", id) { response, _ ->
        Message(response.getString("id"), response.getString("text"))
    }

    fun save(message: Message) {
        val id = message.id ?: UUID.randomUUID().toString()
        db.update(
            "insert into messages values ( ?, ? )",
            id, message.text
        )
    }
}

Run the application

The Spring application is ready to run:

1. Run the application again.

2. Open the requests.http file and add the new GET request:

   ```http request

   Get the message by its id

   GET http://localhost:8080/id ```

3. Execute the GET request to retrieve all the messages from the database.

4. In the Run tool window copy one of the ids and add it to the request, like this:

   ```http request

   Get the message by its id

   GET http://localhost:8080/f16c1d2e-08dc-455c-abfe-68440229b84f ```

   Put your message id instead of the mentioned above.

5. Execute the GET request and see the result in the Run tool window:

   

Next step

The final step shows you how to use more popular connection to database using Spring Data.

Proceed to the next chapter