Shulou(Shulou.com)06/02 Report--

This article focuses on "how to use JDBC in Spring Boot". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn how to use JDBC in Spring Boot.

Read the catalogue

What is JDBC?

2. How to use JDBC in Spring Boot

III. Principle of automatic configuration

IV. JdbcTemplate

5. Configure Swagger for testing

VI. Testing

7. Error reporting and solutions:

Download Github code:

Https://github.com/Jackson0714/study-spring-boot

What is JDBC?

JDBC API belongs to Java APIJDBC for the following functions: connecting to a database, executing SQL statements

Second, how to use JDBC2.1 in Spring Boot to introduce JDBC API dependency and MySQL Driver dependency when creating Spring Boot Project, as well as Spring Web dependency (used in testing)

The introduced JDBC and mysql dependencies can be found in POM:

JDBC dependencies:

Org.springframework.boot

Spring-boot-starter-jdbc

MySql driver dependencies:

Mysql

Mysql-connector-java

Runtime

2.2 configure database connections

New profile: src/main/resources/application.yml

Spring:

Datasource:

Username: root

Password: root

Url: jdbc:mysql://localhost:3306/study-spring-boot?serverTimezone=UTC&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8

DriverClassName: com.mysql.cj.jdbc.Driver

Note: com.mysq.jdbc.Driver is obsolete and needs to use com.mysql.cj.jdbc.Driver

2.3 View the data source and database connection package com.jackson0714.springboot used

Import org.junit.jupiter.api.Test

Import org.springframework.beans.factory.annotation.Autowired

Import org.springframework.boot.test.context.SpringBootTest

Import javax.sql.DataSource

Import java.sql.Connection

Import java.sql.SQLException

@ SpringBootTest

Class Springboot05DataJdbcApplicationTests {

@ Autowired

DataSource dataSource; / / automatically configure the data source, using yml configuration

@ Test

Void contextLoads () throws SQLException {

System.out.println (data Source: + dataSource.getClass ())

Connection connection = dataSource.getConnection ()

System.out.println ("Database connection:" + connection)

Connection.close ()

}

}

Default data source: class com.zaxxer.hikari.HikariDataSource

Database connection: HikariProxyConnection@1335157064 wrapping com.mysql.cj.jdbc.ConnectionImpl@7ff8a9dc

III. Principle of automatic configuration

Automatic configuration file path: org.springframework.boot.autoconfigure.jdbc

DataSourceConfiguration is used to automatically import data sources (based on various judgments)

/ * *

* Tomcat Pool DataSource configuration.

, /

@ Configuration (proxyBeanMethods = false)

@ ConditionalOnClass (org.apache.tomcat.jdbc.pool.DataSource.class)

@ ConditionalOnMissingBean (DataSource.class)

@ ConditionalOnProperty (name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource"

MatchIfMissing = true)

Static class Tomcat {

@ Bean

@ ConfigurationProperties (prefix = "spring.datasource.tomcat") 3.1 automatically select a data source

If the org.apache.tomcat.jdbc.pool.DataSource data source is imported and the configured spring.datasource.type is configured with org.apache.tomcat.jdbc.pool.DataSource, or if type is not configured, the tomcat data source is also used

3.2 HikariDataSource data sources are similarly judged. 3.3.The default use of tomcat data sources 3.4 supports the following data sources org.apache.tomcat.jdbc.pool, HikariDataSource, org.apache.commons.dbcp23.5 support custom data sources

Create a data source using DataSourceBuilder, create a data source that responds to type using reflection, and bind relevant properties

/ * *

* Generic DataSource configuration.

, /

@ Configuration (proxyBeanMethods = false)

@ ConditionalOnMissingBean (DataSource.class)

@ ConditionalOnProperty (name = "spring.datasource.type")

Static class Generic {

@ Bean

DataSource dataSource (DataSourceProperties properties) {

/ / create a data source using DataSourceBuilder, create a data source that responds to type using reflection, and bind relevant properties

Return properties.initializeDataSourceBuilder () .build ()

}

} 3.6 DataSourceInitializerInvoker run script / * *

* Bean to handle {@ link DataSource} initialization by running {@ literal schema-*.sql} on

* {@ link InitializingBean#afterPropertiesSet ()} and {@ literal data-*.sql} SQL scripts on

* a {@ link DataSourceSchemaCreatedEvent}.

*

* @ author Stephane Nicoll

* @ see DataSourceAutoConfiguration

, /

Class DataSourceInitializerInvoker implements ApplicationListener, InitializingBean {createSchema () create table (file name rule schema-*.sql)

InitSchema () executes the data script (file name rule data-*.sql)

GetScripts () to get the script to be executed

Private List getScripts (String propertyName, List resources, String fallback) {

If (resources! = null) {

Return getResources (propertyName, resources, true)

}

String platform = this.properties.getPlatform ()

List fallbackResources = new ArrayList ()

FallbackResources.add ("classpath*:" + fallback + "-" + platform + ".sql")

FallbackResources.add ("classpath*:" + fallback + ".sql")

Return getResources (propertyName, fallbackResources, false)

}

1) fallback = "schema", platform= "all", which automatically executes schema-all.sql or schema.sql files in the root directory

2) fallback = "data", platform= "all", which automatically executes data-all.sql or data.sql files in the root directory

The isEnabled () method determines whether automatic script execution is turned on

There are three modes: NEVER,EMBEDDED (default), Always

Question: use EMBEDDED mode to return false, switch off, do not execute the script, this is why?

Using Always mode, start the spring boot script to execute repeatedly every time (create a table script to determine whether there is a table first, delete it and then rebuild it)

Private boolean isEnabled () {

DataSourceInitializationMode mode = this.properties.getInitializationMode ()

If (mode = = DataSourceInitializationMode.NEVER) {

Return false

}

If (mode = = DataSourceInitializationMode.EMBEDDED & &! isEmbedded ()) {

Return false

}

Return true

} 3.7 specify the script schema to be executed through the configuration file:

-classpath:department.sql

Created department table

IV. JdbcTemplate

The JdbcTemplateAutoConfiguration.java file is automatically injected into JdbcTemplate. (JdbcTemplate is used to manipulate the database)

@ Configuration (proxyBeanMethods = false)

@ ConditionalOnClass ({DataSource.class, JdbcTemplate.class})

@ ConditionalOnSingleCandidate (DataSource.class)

@ AutoConfigureAfter (DataSourceAutoConfiguration.class)

@ EnableConfigurationProperties (JdbcProperties.class)

@ Import ({JdbcTemplateConfiguration.class, NamedParameterJdbcTemplateConfiguration.class})

Public class JdbcTemplateAutoConfiguration {

Configure Swagger to test 5.1 pom.xml files to add swagger dependencies

Io.springfox

Springfox-swagger2

2.9.2

Io.springfox

Springfox-swagger-ui

2.9.2

5.2 add SwaggerConfig.java file package com.jackson0714.springboot.config

Import org.springframework.context.annotation.Bean

Import org.springframework.context.annotation.Configuration

Import springfox.documentation.builders.ApiInfoBuilder

Import springfox.documentation.builders.PathSelectors

Import springfox.documentation.builders.RequestHandlerSelectors

Import springfox.documentation.service.ApiInfo

Import springfox.documentation.spi.DocumentationType

Import springfox.documentation.spring.web.plugins.Docket

Import springfox.documentation.swagger2.annotations.EnableSwagger2

@ Configuration

@ EnableSwagger2

Public class SwaggerConfig {

@ Bean

Public Docket createRestApi () {

Return new Docket (DocumentationType.SWAGGER_2) .apiInfo (apiInfo ())

.select ()

.apis (RequestHandlerSelectors.any ())

Build (PathSelectors.any ())

}

Private ApiInfo apiInfo () {

Return new ApiInfoBuilder ()

.title ("play with the Spring Boot interface document")

.description ("This is a restful api document of Spring Boot.")

.version ("1.0")

.build ()

}

Access to Swagger documents

Http://localhost:8081/swagger-ui.html

Back to the top.

6. Test 6.1 new department @ ApiOperation (value = "1. New department")

@ ApiImplicitParams ({

@ ApiImplicitParam (name = "name", value = "department name")

})

@ PostMapping ("/ create")

Public int createDepartment (@ RequestParam String name) {

String sql = String.format ("insert into department (departmentName) value ('% s')", name)

Int result = jdbcTemplate.update (sql)

Return result

}

Table record

6.2 query all departments @ ApiOperation (value = "2. Query all departments")

@ GetMapping ("/ getAllDepartment")

Public List getAllDepartment () {

List list = jdbcTemplate.queryForList ("select * from department")

Return list

}

6.3 query a department @ ApiOperation according to id (value = "3. Query a department according to id")

@ ApiImplicitParams ({

@ ApiImplicitParam (name = "id", value = "department id to be queried")

})

@ GetMapping ("/ {id}")

Public Map getDepartmentById (@ PathVariable Long id) {

String sql = "select * from department where id =" + id

List list = jdbcTemplate.queryForList (sql)

Return list.get (0)

}

6.4 update department name @ ApiOperation according to id (value = "update department name according to id")

@ ApiImplicitParams ({

@ ApiImplicitParam (name = "id", value = "department id to be updated")

@ ApiImplicitParam (name = "name", value = "Department name to be updated")

})

@ PostMapping ("/ update")

Public int updateDepartmentById (@ RequestParam Long id, @ RequestParam String name) {

String sql = String.format ("update department set departmentName ='% s' where id =% d", name, id)

Int result = jdbcTemplate.update (sql)

Return result

}

6.5 delete department @ ApiOperation according to id (value = "delete department according to id")

@ ApiImplicitParams ({

@ ApiImplicitParam (name = "id", value = "department id to be deleted")

})

@ PostMapping ("/ delete")

Public int deleteDepartment (@ RequestParam Long id) {

String sql = String.format ("delete from department where id =% d", id)

Int result = jdbcTemplate.update (sql)

Return result

}

VII. Error reporting and solution: 7.1 problem 1

Java.sql.SQLException:null, message from server: "Host 'Siri' is not allowed to connect to this MySQL server"

Solution:

Execute the command:

Use mysql

Select host from user

Update user set host ='% 'where user =' root'

Execution result:

Query OK, 1 row affected

As shown in the following figure:

7.2 question 2

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'please thank you for your is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.

Solution:

When configuring spring.datasource.url, add the parameter: serverTimezone=UTC

At this point, I believe you have a deeper understanding of "how to use JDBC in Spring Boot". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.