I have introduced some examples of web layers, including building a RESTful API and rendering a web view using the Thymeleaf template engine, but these contents are not enough to build a dynamic application. Usually, whether we make apps or web applications, we need content, and the 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 it into a data form displayed for users.
This article introduces examples of configuring data sources under Spring Boot and writing data access through JdbcTemplate.
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, you need to introduce jdbc support, and the following configuration is introduced in build.gradle:
compile "org.springframework.boot:spring-boot-starter-jdbc:$spring_boot_version"
Connect to data sources
Taking MySQL database as an example, first introduce the MySQL connection dependency package and add it 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 parameterless constructor, and sets all classes by default to open class plugin 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 JNDI data source
When you deploy an application on an application server, you can use the following configuration method 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
Use JdbcTemplate to operate the database
Spring's JdbcTemplate is automatically configured, and you can use @Autowired to inject it into your own bean.
For example: We are creating a User table, including attributes id, name, and age. Let’s write data access objects and unit test cases.
Define an abstract interface that contains insertion, deletion, 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 high according to name* @param name */ fun deleteByName(name: String) /** * Delete all users*/ fun deleteAllUsers()}Implement data access operations defined in UserService through JdbcTemplate
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 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)@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 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 users${userService.allUsers}") // Delete two users userService.deleteByName("$username a") userService.deleteByName("$username b") log.info("Total user${userService.allUsers}") }}The JdbcTemplate introduced above is just a few of the most basic operations. For more information about using other data access operations, please refer to: JdbcTemplate API
Through the simple example above, we can see that the configuration of accessing databases under Spring Boot still adheres to the original intention of the framework: simple. We only need to add database dependencies to pom.xml and then configure connection information in application.yml. We do not need to create a JdbcTemplate bean like Spring application, and we can directly inject and use it in our own object.
Summarize
The above is the method of using JdbcTemplate to connect to MySQL database by Spring Boot and Kotlin, which I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!