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

The main content of this article is to explain "what is the process of Mybatis+Druid data access in SpringBoot". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what the Mybatis+Druid data access process is like in SpringBoot"!

1. Introduction

For the data access layer, whether it is SQL (relational database) or NOSQL (non-relational database), the bottom layer of SpringBoot uses SpringData to deal with it uniformly.

The bottom layer of SpringBoot uses SpringData to deal with all kinds of databases, and SpringData is also a well-known project in Spring as well as SpringBoot, SpringCloud and so on.

SpingData official website: https://spring.io/projects

Database-related initiators: you can refer to the official document: https://docs.spring.io/springboot/docs/2.1.7.RELEASE/reference/htmlsingle/#using-boot-starter

2 、 JDBC

Create a new project test: springboot-data; introduces the corresponding module! Basic module

After the project is completed, it is found that the following initiators have been imported automatically for us:

Org.springframework.boot spring-boot-starter-jdbc mysql mysql-connector-java runtime

Access to the database

Connect to the database first, and then use IDEA to connect directly.

In SpringBoot, we only need simple configuration to connect to the database.

We use the configuration file of yaml to operate!

Spring: datasource: username: root password: 123456 #? serverTimezone=UTC to solve time zone error url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8

After configuring these things, you can use them directly, because SpringBoot has been automatically configured for us by default; let's go to the test class to test it.

Package com.kk;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;@SpringBootTestclass Springboot04DataApplicationTests {@ Autowired DataSource dataSource; @ Test void contextLoads () throws SQLException {/ / View the default data source class com.zaxxer.hikari.HikariDataSource dbcp System.out.println (dataSource.getClass ()) / / obtain database connection Connection connection = dataSource.getConnection (); System.out.println (connection); connection.close ();}}

Bug that you may encounter at run time

Invalid character found in the request target exception in SpringBoot

Add to the startup class

@ Beanpublic ConfigurableServletWebServerFactory webServerFactory () {TomcatServletWebServerFactory factory = new TomcatServletWebServerFactory (); factory.addConnectorCustomizers (new TomcatConnectorCustomizer () {@ Override public void customize (Connector connector) {connector.setProperty ("relaxedQueryChars", "| {} []");}}); return factory;}

Output result:

You can see that the default data source that it configures for us is: class com.zaxxer.hikari.HikariDataSource, which we did not configure manually.

Do a global search and find that all the automatic configuration of the data source is under the DataSourceProperties file; the principle of automatic configuration and what attributes can be configured here?

Spring Boot 2.1.7 uses com.zaxxer.hikari.HikariDataSource data sources by default

Previous versions, such as Spring Boot 1.5, default to use org.apache.tomcat.jdbc.pool.DataSource as the data source

HikariDataSource is known as the fastest data source of Java WEB, which is better than traditional connection pooling such as C3P0, DBCP, Tomcat jdbc, etc.

With a database connection, it is obvious that the database can be manipulated by CRUD.

3. CRUD operation

1. With the data source (com.zaxxer.hikari.HikariDataSource), you can get the database connection (java.sql.Connection). With the connection, you can use the connection and native JDBC statements to operate the database.

2. Even if you do not use a third-party data library operation framework, such as MyBatis, Spring itself does a lightweight encapsulation of the native JDBC, namely org.springframework.jdbc.core.JdbcTemplate.

3. All CRUD methods for database operations are in JdbcTemplate.

4. Spring Boot not only provides the default data source, but also defaults to configure JdbcTemplate and put it in the container, so programmers only need to inject it themselves.

5. The principle of automatic configuration of JdbcTemplate is to rely on the org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration class under the org.springframework.boot.autoconfigure.jdbc package.

JdbcTemplate mainly provides the following types of methods:

Execute method: can be used to execute any SQL statement, generally used to execute DDL statements

Update method and batchUpdate method: update method is used to execute new, modified, deleted and other statements; batchUpdate method is used to execute batch-related statements

Query method and queryForxxx method: used to execute query-related statements

Call method: used to execute stored procedures and function-related statements.

Test:

Package com.kk.controller;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RestController;import java.util.List;import java.util.Map @ RestControllerpublic class JDBCController {/ / JdbcTemplate is the core class of the core package, which is used to simplify JDBC operations and avoid some common errors, such as forgetting to close the database connection / / Spring Boot provides the data source by default, and org.springframework.jdbc.core.JdbcTemplate / / JdbcTemplate provides the data source by default, so you no longer have to close the database connection @ Autowired JdbcTemplate jdbcTemplate by yourself. / / query all the information in the database / / without entity classes, how to get things in the database? 1 Map in Map / / List corresponds to 1 row of data in the database / / Map corresponds to the database field name, and value corresponds to the database field value @ GetMapping ("/ userList") public List userList () {String sql= "select * from user"; List list_maps = jdbcTemplate.queryForList (sql); return list_maps } @ GetMapping ("/ addUser") public String addUser () {String sql= "insert into mybatis.user (id,name,pwd) values"; jdbcTemplate.update (sql); return "update-ok";} @ GetMapping ("/ updateUser/ {id}") public String updateUser (@ PathVariable ("id") int id) {String sql= "update mybatis.user set name=?,pwd=? Where id= "+ id; / / package Object [] objects = new Object [2]; objects [0] =" xiaobai "; objects [1] =" 9999999 "; jdbcTemplate.update (sql,objects); return" updateUser-ok ";} @ GetMapping (" / deleteUser/ {id} ") public String deleteUser (@ PathVariable (" id ") int id) {String sql=" delete from mybatis.user where id=? " JdbcTemplate.update (sql,id); return "deleteUser-ok";}}

The test was successful!

Exploration of the principle:

The function of org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration data source configuration class: after judging by logic, add data source

SpringBoot supports the following data sources by default:

Com.zaxxer.hikari.HikariDataSource (above Spring Boot 2.0, this data source is used by default)

Org.apache.tomcat.jdbc.pool.DataSource

Org.apache.commons.dbcp2.BasicDataSource

You can use spring.datasource.type to specify a custom data source type with a value of the fully qualified name of the connection pool implementation to use. By default, it is automatically detected from the classpath.

@ Configuration @ ConditionalOnMissingBean ({DataSource.class}) @ ConditionalOnProperty (name = {"spring.datasource.type"}) static class Generic {Generic () {} @ Bean public DataSource dataSource (DataSourceProperties properties) {return properties.initializeDataSourceBuilder (). Build ();} 4, custom data source DruidDataSource

Introduction to DRUID

Druid is a database connection pool implementation on Alibaba's open source platform, which combines the advantages of C3P0, DBCP, PROXOOL and other DB pools, while adding log monitoring.

Druid can well monitor the execution of DB pooled connections and SQL, and is born for monitoring DB connection pooling.

Spring Boot 2.0 and above use Hikari data sources by default. It can be said that both Hikari and Driud are the best data sources on Java Web. Let's focus on how Spring Boot integrates Druid data sources and how to realize database monitoring.

* basic configuration parameters of com.alibaba.druid.pool.DruidDataSource are as follows: *

Configuration default value description name

The significance of configuring this property is that if there are multiple data sources, monitoring can be distinguished by name. If it is not configured, a name will be generated in the format "DataSource-" + System.identityHashCode (this). In addition, configuring this property will not work at least in version 1.0.5, so forcibly setting name will cause errors-click here. Url

The url that connects to the database varies from database to database. For example: mysql: jdbc:mysql://10.20.153.104:3306/druid2 oracle: jdbc:oracle:thin:@10.20.149.85:1521:ocnautousername

User name password to connect to the database

The password to connect to the database. If you don't want the password to be written directly in the configuration file, you can use ConfigFilter. For more information, see here: https://github.com/alibaba/druid/wiki/ uses ConfigFilterdriverClassName to automatically identify this option according to url. If druid is not configured, dbType will be automatically identified according to url, and then select the number of physical connections to be established during initialization of the corresponding driverClassNameinitialSize0. Initialization occurs when the call to the init method is displayed, or the maximum number of connection pools in the maxActive8 is no longer used when the first getConnection, maxIdle8 is no longer used, and the configuration has no effect on minIdle

Minimum number of connection pools maxWait

Gets the maximum wait time for a connection, in milliseconds. After maxWait is configured, fair locks are enabled by default, and concurrency efficiency decreases, and unfair locks can be used for true by configuring the useUnfairLock attribute if necessary. Whether poolPreparedStatementsfalse caches preparedStatement, that is, PSCache. PSCache greatly improves the performance of databases that support cursors, such as oracle. It is recommended to close under mysql. For maxOpenPreparedStatements-1 to enable PSCache, it must be configured greater than 0. When it is greater than 0, poolPreparedStatements automatically triggers the modification to true. In Druid, there is no problem that PSCache takes up too much memory under Oracle. You can configure this value higher, such as 100validationQuery.

The sql used to detect whether the connection is valid requires that it be a query statement. If validationQuery is null,testOnBorrow, testOnReturn, testWhileIdle will not have its effect. ValidationQueryTimeout

In seconds, the timeout to detect whether the connection is valid. When the underlying layer calls the void setQueryTimeout (int seconds) method testOnBorrowtrue of the jdbc Statement object to apply for a connection, validationQuery is performed to check whether the connection is valid. This configuration will degrade performance. When testOnReturnfalse returns the connection, validationQuery is performed to check whether the connection is valid. This configuration will reduce the performance. It is recommended that testWhileIdlefalse be configured as true, which does not affect performance and ensures security. When applying for a connection, it is tested that if the idle time is greater than timeBetweenEvictionRunsMillis, validationQuery is performed to check whether the connection is valid. TimeBetweenEvictionRunsMillis1 minute (1.0.14) has two meanings: 1) the Destroy thread detects the interval time between connections, and closes the physical connection if the connection idle time is greater than or equal to minEvictableIdleTimeMillis. 2) the judgment basis of testWhileIdle, see the description of testWhileIdle attribute numTestsPerEvictionRun in detail.

No longer in use, a DruidDataSource supports only one EvictionRunminEvictableIdleTimeMillis30 minute (1.0.14) the maximum time connectionInitSqls for which a connection remains idle without being expelled

The sqlexceptionSorter executed when the physical connection is initialized automatically recognizes according to dbType that when the database throws some unrecoverable exceptions, the connection filters is discarded

The attribute type is a string, and the extension is configured by aliases. Common plug-ins are: filter:log4j used to monitor filter:stat logs for monitoring statistics to defend filter:wallproxyFilters injected by sql

Type is List. If both filters and proxyFilters are configured, it is a composite relationship, not a replacement relationship.

Introduction of data sources

Com.alibaba druid 1.2.6

View project dependencies and import successfully!

Switch data sourc

It has been said earlier that Spring Boot 2.0 and above use com.zaxxer.hikari.HikariDataSource data sources by default, and you can specify data sources through spring.datasource.type.

Type: com.alibaba.druid.pool.DruidDataSource

Set data source connection initialization size, maximum number of connections, wait time, minimum number of connections, etc.

# Spring Boot does not inject these attribute values by default. You need to bind your own # druid data source proprietary configuration initialSize: 5minIdle: 5maxActive: 20maxWait: 60000timeBetweenEvictionRunsMillis: 60000minEvictableIdleTimeMillis: 300000validationQuery: SELECT 1 FROM DUALtestWhileIdle: truetestOnBorrow: falsetestOnReturn: falsepoolPreparedStatements: true# configure filters,stat: monitoring statistics, log4j: logging, wall: defense sql injection # if Times error java.lang.ClassNotFoundException: org.apache.log4j.Priority# is allowed, you can import log4j dependency Maven address: https://mvnrepository.com/artifact/log4j/log4jfilters: stat,wall,log4jmaxPoolPreparedStatementPerConnectionSize: 20useGlobalDataSourceStat: trueconnectionProperties: druid.stat.mergeSql=true Druid.stat.slowSqlMillis=500

Log4**** log dependency

Log4j log4j 1.2.17

test

Public class SpringbootDemoDataApplicationTests {/ / injection data source @ Autowired DataSource dataSource; @ Test public void contextLoads () throws SQLException {/ / take a look at the default data source System.out.println (dataSource.getClass ()); / / get a connection Connection connection = dataSource.getConnection (); System.out.println (connection); DruidDataSource druidDataSource = (DruidDataSource) dataSource System.out.println ("druidDataSource data source maximum connections:" + druidDataSource.getMaxActive ()); System.out.println ("druidDataSource data source initialization connections:" + druidDataSource.getInitialSize ()); / / close connection connection.close ();}}

The test was successful!

1. Configure Druid data source monitoring

Druid data source has a monitoring function and provides a web interface for users to view, similar to when installing a router, it also provides a default web page.

So the first step is to set up the background management page of Druid, such as login account, password and so on.

Package com.kk.config;import com.alibaba.druid.pool.DruidDataSource;import com.alibaba.druid.support.http.StatViewServlet;import com.alibaba.druid.support.http.WebStatFilter;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.boot.web.servlet.FilterRegistrationBean;import org.springframework.boot.web.servlet.ServletRegistrationBean;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import javax.servlet.Filter;import javax.sql.DataSource;import java.util.HashMap Import java.util.Map;@Configurationpublic class DruidConfig {@ ConfigurationProperties (prefix = "spring.datasource") / / spring.datasource @ Bean public DataSource druidDataSource in the associated application.yaml () {return new DruidDataSource ();} / / configure Druid to monitor and manage the Servlet at the backend / / there is no web.xml file in the built-in Servler container, so use Spring Boot's registration Servlet method @ Bean public ServletRegistrationBean statViewServlet () {ServletRegistrationBean bean = new ServletRegistrationBean (new StatViewServlet (), "/ druid/*"); Map initParams = new HashMap (); initParams.put ("loginUsername", "admin"); / / login account initParams.put of the backend management interface ("loginPassword", "123456") / / login password of the backend management interface / / who is allowed to access / / initParams.put ("allow", "localhost"): indicates that only the local machine can access / / initParams.put ("allow", ""): if it is empty or null, all access to initParams.put ("allow", "") is allowed. / / who is denied access to / / initParams.put by deny:Druid backend ("kuangshen", "192.168.1.20"); disable this ip access / / set initialization parameter bean.setInitParameters (initParams); return bean / / these parameters can be found in the parent class com.alibaba.druid.support.http.ResourceServlet of com.alibaba.druid.support.http.StatViewServlet} / / filter @ Bean public FilterRegistrationBean webStatFilter () {FilterRegistrationBean bean = new FilterRegistrationBean (); bean.setFilter (new WebStatFilter ()); / / which requests can be filtered? HashMap initParameters = new HashMap (); / / these things are not counted initParameters.put ("exclusions", "* .js,*.css,/druid/*"); bean.setInitParameters (initParameters); return bean;}}

Test interview! Http://localhost:8080/druid/login.html

2. Configure Druid web to monitor filter

The function of this filter is to count all the database information in the web application request, such as the sql statement issued, the time of sql execution, the number of requests, the requested url address, as well as seesion monitoring, the number of visits to the database table, and so on.

/ / filter@Beanpublic FilterRegistrationBean webStatFilter () {FilterRegistrationBean bean = new FilterRegistrationBean (); bean.setFilter (new WebStatFilter ()); / / which requests can be filtered? HashMap initParameters = new HashMap (); / / these things are not counted initParameters.put ("exclusions", "* .js,*.css,/druid/*"); bean.setInitParameters (initParameters); return bean;}

Execute sql

There will be a record after the execution of sql

5. SpringBoot integrates mybatis1. Import the dependency org.mybatis.spring.boot mybatis-spring-boot-starter 2. 2. 02 required for mybatis. Configure database connection information # database driver: spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver# data source name spring.datasource.name=defaultDataSource# database connection address spring.datasource.url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC# database user name & password: spring.datasource.username=rootspring.datasource.password=123456

Test whether the connection is successful!

Package com.kk;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.SQLException;@SpringBootTestclass Springboot05MybatisApplicationTests {@ Autowired DataSource dataSource; @ Test void contextLoads () throws SQLException {System.out.println (dataSource.getClass ()); System.out.println (dataSource.getConnection ());}}

The test was successful!

3, create the entity class package com.kk;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.SQLException;@SpringBootTestclass Springboot05MybatisApplicationTests {@ Autowired DataSource dataSource; @ Test void contextLoads () throws SQLException {System.out.println (dataSource.getClass ()); System.out.println (dataSource.getConnection ());}} 4. The annotation to configure the Mapper interface class package com.kk.mapper;import com.kk.pojo.User;import org.apache.ibatis.annotations.Mapper;import org.springframework.stereotype.Repository;import java.util.List;// indicates that this is a mybatis mapper class @ Mapper / / controller layer @ Repository / / springpublic interface UserMapper {List queryUserList (); User queryUserById (int id); int addUser (User user); int updateUser (User user); int deleteUser (int id);}

5. Write controller

Package com.kk.controller;import com.kk.mapper.UserMapper;import com.kk.pojo.User;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RestController;import java.util.List;@RestControllerpublic class UserController {@ Autowired private UserMapper userMapper; @ GetMapping ("/ queryUserList") public List queryUserList () {List userList = userMapper.queryUserList () For (User user: userList) {System.out.println (user);} return userList;} / / Select user @ GetMapping ("/ queryUserById") public String selectUserById () {User user = userMapper.queryUserById (1); System.out.println (user); return "ok" based on id } / / add a user @ GetMapping ("/ addUser") public String addUser () {userMapper.addUser (new User (1, "rhubarb", "9999999"); userMapper.addUser (new User (3, "rhubarb", "9999999")); userMapper.addUser (new User (4, "rhubarb", "9999999"); return "ok" } / / modify a user @ GetMapping ("/ updateUser") public String updateUser () {userMapper.updateUser (new User (5, "yellow hair", "999999")); return "ok";} / / delete the user @ GetMapping ("/ deleteUser") public String deleteUser () {userMapper.deleteUser (5); return "ok";}} according to id

6.SpringBoot integration

In the past, when MyBatis was not integrated with spring, the configuration of data sources, transactions, accounts to connect to the database, passwords, etc., were carried out in the myBatis core configuration file.

After the integration of myBatis and springboot, the configuration of data sources, transactions, accounts and passwords connected to the database will be managed by spring. So here we are completely ok even if we don't use the mybatis configuration file!

Now that you have provided the mapping configuration files for myBatis, you need to tell spring boot the location of these files

# integrate mybatis# settings aliases and settings to enable spring to identify # specify the core configuration file of myBatis and Mapper mapping file # Note: the path mybatis.type-aliases-package=com.kk.pojomybatis.mapper-locations=classpath:mybatis/mapper/*.xml of the corresponding entity class

Springboot officially does not provide an initiator for myBaits. It is a development package provided by myBatis to adapt to springboot. It can be seen from the name of the dependency package in the pom.xml file that it does not start with spring-boot.

Similarly, the two lines of configuration in the global configuration file above also start with mybatis rather than spring, which fully shows that these are officially provided by myBatis.

View all configuration items from org.mybatis.spring.boot.autoconfigure.MybatisProperties

@ ConfigurationProperties (prefix = "mybatis") public class MybatisProperties {public static final String MYBATIS_PREFIX = "mybatis"; private static final ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver (); private String configLocation; private String [] mapperLocations; private String typeAliasesPackage; private Class typeAliasesSuperType; private String typeHandlersPackage; private boolean checkConfigLocation = false; private ExecutorType executorType; private Class

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.