Spring Boot and Kotlin use JdbcTemplate to connect to the MySQL database

I've covered some examples of the Web layer, including building RESTful API and rendering Web views using the Thymeleaf template engine, but these are not enough to build a dynamic application. In general, both App and Web applications require content, and content is usually stored in various types of databases. After receiving an access request, the server needs to access the database to obtain and process the content into the form of data presented to the user.

This article shows examples of configuring data sources and writing data access through Spring Boot.

Data source configuration

When we access the database, we need to configure a data source first. The following are several different database configuration methods.

First, in order to connect to the database, jdbc support is introduced. The following configuration is introduced in ES20en.gradle:

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

Connect to data source

Take MySQL database as an example, first introduce the dependency package of MySQL connection, and add in ES29en.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 integration SpringBoot The default parameter-free constructor, which sets all classes by default open Class plug-in 
  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"
}

In src/main/resources/application yml configuration in the data source information

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

Connect to the JNDI data source

When you deploy your 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 the JNDI is not very understand, please refer to the https: / / baike baidu. com/item/JNDI / 3792442 & # 63; fr=aladdin

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

Use JdbcTemplate to manipulate the database

JdbcTemplate's JdbcTemplate is automatically configured and you can inject it directly into your own bean using @ES70en.

Example: After creating User table with attributes id,name, age, let's write data access objects and unit test cases.

Define the abstract interface UserService with insert, delete, query

interface UserService {
 /**
  *  Get total number of users 
  */
 val allUsers: Int?
 /**
  *  new 1 A user 
  * @param name
  * @param age
  */
 fun create(name: String, password: String?)
 /**
  *  According to the name delete 1 A user is high 
  * @param name
  */
 fun deleteByName(name: String)
 /**
  *  Delete all users 
  */
 fun deleteAllUsers()
}

The data access operations defined in UserService are implemented 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 a unit test case for UserService to verify the correctness of the database operations by creating, deleting, and query-ing.

/**
 * 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 A user 
  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(" A total of the user  ${userService.allUsers}")
  //  Delete two users 
  userService.deleteByName("$username a")
  userService.deleteByName("$username b")
  log.info(" A total of the user  ${userService.allUsers}")
 }
}

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

From the simple example above, you can see that the configuration of accessing the database under Spring Boot still retains the framework's original purpose: simplicity. We only need to add the database dependency in ES100en.xml and then configure the connection information in ES102en.yml. We do not need to create JdbcTemplate's Bean in Spring application, so we can inject the use directly in our own objects.

conclusion