Spring Boot and Kotlin use JdbcTemplate to connect MySQL

Keywords: Spring Database MySQL JDBC

Some examples of the Web tier were introduced, including building RESTful API s and rendering Web views using Thymeleaf template engine, but these are not enough to build a dynamic application. Usually we do App or Web applications, we need content. Content is usually stored in various types of databases. After receiving the access request, the server needs to access the database to obtain and process the data presented to the user.

This paper presents an example of configuring data sources and writing data access through JdbcTemplate based on Spring Book.

Data source configuration

When we access the database, we need to configure a data source first. Here are several different database configurations.

First, in order to connect to the database, we need to introduce jdbc support. In build.gradle, we introduce the following configuration:

compile "org.springframework.boot:spring-boot-starter-jdbc:$spring_boot_version"

Connecting data sources

Take MySQL database as an example, first introduce the dependency package of MySQL connection and add it in build.gradle:

compile "mysql:mysql-connector-java:$mysql_version"

Complete build.gradle

group 'name.quanke.kotlin'
version '1.0-SNAPSHOT'

buildscript {
    ext.kotlin_version = '1.2.10'
    ext.spring_boot_version = '1.5.4.RELEASE'
    ext.springfox_swagger2_version = '2.7.0'
    ext.mysql_version = '5.1.21'
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath "org.jetbrains.kotlin:kotlin-gradle-plugin:$kotlin_version"
        classpath("org.springframework.boot:spring-boot-gradle-plugin:$spring_boot_version")

//        Kotlin integrates SpringBoot's default parametric constructor and sets all classes to open class plug-ins by default
        classpath("org.jetbrains.kotlin:kotlin-noarg:$kotlin_version")
        classpath("org.jetbrains.kotlin:kotlin-allopen:$kotlin_version")
    }
}

apply plugin: 'kotlin'
apply plugin: "kotlin-spring" // See https://kotlinlang.org/docs/reference/compiler-plugins.html#kotlin-spring-compiler-plugin
apply plugin: 'org.springframework.boot'

jar {
    baseName = 'chapter11-6-1-service'
    version = '0.1.0'
}
repositories {
    mavenCentral()
}


dependencies {
    compile "org.jetbrains.kotlin:kotlin-stdlib-jre8:$kotlin_version"
    compile "org.springframework.boot:spring-boot-starter-web:$spring_boot_version"
    compile "org.springframework.boot:spring-boot-starter-jdbc:$spring_boot_version"
    compile "mysql:mysql-connector-java:$mysql_version"
    testCompile "org.springframework.boot:spring-boot-starter-test:$spring_boot_version"
    testCompile "org.jetbrains.kotlin:kotlin-test-junit:$kotlin_version"

}

compileKotlin {
    kotlinOptions.jvmTarget = "1.8"
}
compileTestKotlin {
    kotlinOptions.jvmTarget = "1.8"
}

Configure data source information in src/main/resources/application.yml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver

Connecting JNDI data sources

When you deploy an application on an application server and want the data source to be managed by the application server, you can use the following configuration to introduce the JNDI data source.

If you don't know much about JNDI, please refer to https://baike.baidu.com/item/JNDI/3792442?fr=aladdin

spring.datasource.jndi-name=java:jboss/datasources/customers

Operating databases using JdbcTemplate

Spring's JdbcTemplate is automatically configured and can be injected directly into your own bean s using @Autowire.

Example: We are creating a User table with attribute id,name, age. Here we write data access objects and unit test cases.

Define the abstract interface UserService that contains insert, delete, and query


interface UserService {

    /**
     * Get the total number of users
     */
    val allUsers: Int?

    /**
     * Add a new user
     * @param name
     * @param age
     */
    fun create(name: String, password: String?)

    /**
     * Delete a user height according to name
     * @param name
     */
    fun deleteByName(name: String)

    /**
     * Delete all users
     */
    fun deleteAllUsers()

}

Implementing data access operations defined in UserService through JdbcTemplate


import org.springframework.beans.factory.annotation.Autowired
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.stereotype.Service
/**
 * Created by http://quanke.name on 2018/1/10.
 */
@Service
class UserServiceImpl : UserService {

    @Autowired
    private val jdbcTemplate: JdbcTemplate? = null

    override val allUsers: Int?
        get() = jdbcTemplate!!.queryForObject("select count(1) from USER", Int::class.java)

    override fun create(name: String, password: String?) {
        jdbcTemplate!!.update("insert into USER(USERNAME, PASSWORD) values(?, ?)", name, password)
    }

    override fun deleteByName(name: String) {
        jdbcTemplate!!.update("delete from USER where USERNAME = ?", name)
    }

    override fun deleteAllUsers() {
        jdbcTemplate!!.update("delete from USER")
    }
}

Create unit test cases for UserService to verify the correctness of database operations by creating, deleting and querying.


/**
 * Created by http://quanke.name on 2018/1/9.
 */
@RunWith(SpringRunner::class)
@SpringBootTest
class ApplicationTests {

    val log = LogFactory.getLog(ApplicationTests::class.java)!!

    @Autowired
    lateinit var userService: UserService

    @Test
    fun `jdbc test"`() {

        val username = "quanke"
        val password = "123456"
        // Insert 5 users
        userService.create("$username a", "$password 1")
        userService.create("$username b", "$password 2")
        userService.create("$username c", "$password 3")
        userService.create("$username d", "$password 4")
        userService.create("$username e", "$password 5")


        log.info("Total user ${userService.allUsers}")

        // Delete two users
        userService.deleteByName("$username a")
        userService.deleteByName("$username b")

        log.info("Total user ${userService.allUsers}")
    }

}

The JdbcTemplate described above is just a few of the most basic operations. For more data access operations, please refer to: JdbcTemplate API

Through the above simple example, we can see that the configuration of accessing database under Spring Book still adheres to the original intention of the framework: simple. We just need to add database dependencies to pom.xml and configure connection information in application.yml. We can inject JdbcTemplate beans directly into our own objects without creating JdbcTemplate beans in Spring applications.

More Spring Boot and kotlin related content

Welcome to your attention. Spring Boot and kotlin Actual Warfare

Reference resources

Posted by phpknight on Sat, 22 Dec 2018 21:57:06 -0800