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

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

Some examples of Web layers have been introduced earlier, including building RESTful APIs and rendering Web views using the Thymeleaf template engine. However, these contents are insufficient for building a dynamic application. Content is usually required for apps and Web applications, while content is usually stored in various types of databases, after receiving an access request, the server needs to access the database to obtain and display the data to the user.

This article describes how to configure the data source and write data access through JdbcTemplate under Spring Boot.

Data source configuration

When accessing the database, we need to configure a data source. The following describes several different database configuration methods.

First, introduce jdbc support to connect to the database. Introduce the following configuration in build. gradle:

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

Connect to the data source

Take the MySQL database as an example. first introduce the MySQL connection dependency package and add the following to 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 no-argument constructor. 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-pluginapply 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

Connect to the JNDI Data Source

If you want to allow the data source to be managed by the Application Server when deploying the application on the application server, you can use the following configuration method to introduce the JNDI data source.

If you are not familiar with JNDI, please refer to https://baike.baidu.com/item/JNDI/3792442? Fr = aladdin

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

Use JdbcTemplate to operate databases

Spring JdbcTemplate is automatically configured. You can directly use @ Autowired to inject it into your own bean.

For example, when creating a User table, including the property id, name, and age, we will compile the data access object and unit test case.

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

Interface UserService {/*** get the total number of users */val allUsers: Int? /*** Add a user * @ param name * @ param age */fun create (name: String, password: String ?) /*** Delete a user high by name * @ param name */fun deleteByName (name: String)/*** delete all users */fun deleteAllUsers ()}

Use JdbcTemplate to implement data access operations defined in UserService

import org.springframework.beans.factory.annotation.Autowiredimport org.springframework.jdbc.core.JdbcTemplateimport org.springframework.stereotype.Service/** * Created by http://quanke.name on 2018/1/10. */@Serviceclass 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 and verify the correctness of database operations by creating, deleting, and querying.

/*** Created by http://quanke.name on 2018/1/9. */@ RunWith (SpringRunner: class) @ SpringBootTestclass ApplicationTests {val log = LogFactory. getLog (ApplicationTests: class. java )!! @ Autowired lateinit var userService: UserService @ Test fun 'jdbc test "'() {val username =" quanke "val password =" 123456 "// Insert five user userservices. 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 users $ {userService. allUsers} ") // Delete two userservices. deleteByName ("$ username a") userService. deleteByName ("$ username B") log.info ("total users $ {userService. allUsers }")}}

The JdbcTemplate described above is only the most basic operations. For more information about how to use other data access operations, see JdbcTemplate API.

Through the simple example above, we can see that the configuration for accessing the database under Spring Boot still inherits the original intention of the framework: simple. We only need. add the database dependency to xml and then go to application. configure the connection information in yml. You do not need to create a JdbcTemplate Bean in the Spring application to inject and use it directly in your own object.

Summary

The above section describes how Spring Boot and Kotlin use JdbcTemplate to connect to the MySQL database. I hope to help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!