Data access of Spring Boot framework

The code repository address mentioned in the blog: Demo-Springboot

SpringBoot and Data access layer, do a lot of adaptation, whether SQL or NOSQL are supported, SpringBoot default using Spring Data access for unified processing, including the amount of automatic configuration, help us save a lot of configuration work.

(1) JDBC access

Import the relevant Maven dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
Copy the code

Configure database connections

spring:
  datasource:
    username: root
    password: 123456
    url: JDBC: mysql: / / 127.0.0.1:3306 / base_crud? useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
Copy the code

Write test classes to view database connections

@SpringBootTest
class Springboot05JdbcApplicationTests {

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads(a) throws SQLException {
        System.out.println("Data source is:"+dataSource.getClass());
        Connection connection = dataSource.getConnection();
        System.out.println("The obtained database connection is:"+connection); connection.close(); }}Copy the code

Test conclusions:

• Use the defaultcom.zaxxer.hikari.HikariDataSourceAs a data source;
• Data source-related configuration is inDataSourceProperties;

Springboot automatically configures JDBC:

(1) automatic configuration of package: org. Springframework. Boot. Autoconfigure. JDBC

(2) DataSourceConfiguration This configuration is used to configure data sources. By default, HikariDataSource is used as the data source. Configure the datasource using spring.datasource. Type;

Data sources supported by default in SpringBoot:

• org.apache.tomcat.jdbc.pool.DataSource
• com.zaxxer.hikari.HikariDataSource
• org.apache.commons.dbcp2.BasicDataSource

SpringBoot also supports custom data sources

@Configuration(proxyBeanMethods = false)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type")
static class Generic {

    @Bean
    DataSource dataSource(DataSourceProperties properties) {
        // Use the DataSourceBuilder to create the data source, use reflection to create the data source for the response type, and bind the related properties
        returnproperties.initializeDataSourceBuilder().build(); }}Copy the code

(4) in the automatic configuration class DataSourceAutoConfiguration imported DataSourceInitializationConfiguration class is used to initialize the DataSource data source configuration;

⑤ The automatic configuration class is also configured with the JDBCTemplate operation database;

2. Integrate Druid data sources

Druid is first and foremost a database connection pool, but it is more than just a database connection pool. It also includes ProxyDriver, a set of built-in JDBC component libraries, SQLParser, and more. Druid supports all jdbc-compliant databases, including Oracle, MySql, Derby, Postgresql, SQLServer, H2, and more.

Druid provides special optimizations for Oracle and MySql, such as PSCache memory usage optimization for Oracle and ping detection optimization for MySql. Druid offers significant advantages in monitoring, scalability, stability, and performance. Druid provides an extended FILter-chain API that allows you to write your own filters to intercept any JDBC method and do anything from performance monitoring to SQL auditing to username and password encryption to logging.

Druid storage address

To change a data source to a Druid data source:

Import Druid dependencies.

② Modify the configuration file to add connection configuration;

3. Write the configuration class and configure Druid.

① Import the Druid dependency

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>
Copy the code

2 Modify the configuration file and add configuration items

spring:
  datasource:
    username: root
    password: 123456
    url: JDBC: mysql: / / 139.224.83.90:3306 / base_crud? useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    Use DruidSource data source
    type: com.alibaba.druid.pool.DruidDataSource

    Data source connection configuration is relevant
    # initialize size, min, Max
    initialSize: 5
    minIdle: 5
    maxActive: 20
    Set the connection waiting timeout
    maxWait: 60000
    Set the detection interval to close idle connections in milliseconds
    timeBetweenEvictionRunsMillis: 60000
    Set the minimum lifetime of a connection in the connection pool in milliseconds
    minEvictableIdleTimeMillis: 300000
    SQL > check whether the connection is a valid SQL statement
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    # validationQuery checks whether a connection is valid when applying for a connection
    testOnBorrow: false
    ValidationQuery is executed when a connection is returned to check whether the connection is valid. Turning this on degrades performance and defaults to True
    testOnReturn: false
    # if cache preparedStatement, mysql5.5 + recommended
    poolPreparedStatements: true
    # Configure the filters for monitoring statistics interception. After removing the filters, the MONITORING interface SQL cannot be counted. 'wall' is used for the firewall
    filters: stat,wall
    # Specify the size of PSCahe on each connection
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500
Copy the code

Write the configuration class and configure Druid

@Configuration
public class DruidConfig {

    /** * Import data source *@return* /
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(a) {
        return new DruidDataSource();
    }

    /** * Configure Durid monitoring * 1. Configure a Servlet to manage monitoring background *@return* /
    @Bean
    public ServletRegistrationBean statViewServlet(a) {
        // Create a background Servlet and specify a path
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        Map<String, String> initParams = new HashMap<>();
        // Configure the Druid access user name and password
        initParams.put("loginUsername"."admin");
        initParams.put("loginPassword"."123456");
        // If this parameter is not configured, all access is allowed
        initParams.put("allow"."");
        // Configure the IP address to deny access. When configured together with allow, deny takes effect first
        initParams.put("deny"."139.244.83.90");

        bean.setInitParameters(initParams);
        return bean;
    }

    /** * 2. Configure a filter * for web monitoring@return* /
    @Bean
    public FilterRegistrationBean webStatFilter(a) {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions"."*.js,*.css,/druid/*");

        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/ *"));

        returnbean; }}Copy the code

For more information about Druid configuration items, see the DruidDataSource configuration property list

After startup SpringBoot program, enter http://localhost:9080/druid/ input configured user name and password, can monitor panel in the browser view the Druid.

Hikari druid C3P0 DBCP JDBC

(iii) Integration of Mybatis framework

For the introduction and use of MyBatis framework please refer to the previous blog: MyBatis framework, here only write MyBatis framework and SpringBoot integration.

Steps to integrate MyBatis framework:

① Import MyBatis to SpringBoot integration dependency;

② Configure database connection attributes and data source information;

④ Use MyBatis (notes/external files);

Import MyBatis to SpringBoot integration dependency

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>

<! Import Druid dependencies -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
Copy the code

The mybatis-spring-boot-starter dependency is formed

Mybatis -spring-boot-starter already has JDBC dependencies, so there is no need to import JDBC dependencies

② Configure database connection attributes and data source information;

The configuration information in this section is the same as that in integrating Druid data sources and is not displayed here

1. Use MyBatis for annotations

Using MyBatis framework, you can automatically configure queries and correspondence by writing SQL statements directly in annotations.

@Mapper
public interface IUserDao {

    /** * Query all users *@return* /
    @Select("select * from user")
    List<User> getUsers(a);

    /** * Query a single user *@param id
     * @return* /
    @Select("select * from user where id=#{id}")
    User getUserById(Integer id);

    /** * Delete a single user *@param id
     * @return* /
    @Delete("delete from user where id=#{id}")
    Integer deleteUserById(Integer id);

    /** * insert a user *@param user
     * @return* /
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into user (username, birthday, sex, address) values (#{username}, #{birthday}, #{sex}, #{address})")
    Integer saveUser(User user);


}
Copy the code

Annotations can be used directly because:

SpringBoot is automatically configured through the MybatisAutoConfiguration class, which is defined in the configuration properties class MybatisProperties.

Implement the ConfigurationCustomizer interface to customize the configuration of MyBatis

@org.springframework.context.annotation.Configuration
public class MybatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer(a) {
        return new ConfigurationCustomizer() {
            @Override
            public void customize(Configuration configuration) {
                // The configuration allows the camel name transformation, namely the camel name and underline name transformation
                configuration.setMapUnderscoreToCamelCase(true); }}; }}Copy the code

We can also use the @Mapperscan annotation in the main startup class to batch scan the Mapper, eliminating the @Mapper annotation in our classes at each Dao layer

@MapperScan(value = "cn.bruce.springboot.dao")
@SpringBootApplication
public class Springboot05MybatisApplication {

	public static void main(String[] args) { SpringApplication.run(Springboot05MybatisApplication.class, args); }}Copy the code

2. Configure mapper using Mybatis through external files

Basic steps:

(1) Write dao layer interface;

② Write mapper configuration file;

③ Write the configuration file to configure MyBatis;

4 Specify the configuration file path in the global configuration file.

① Write dao layer interfacesIAccountDao

public interface IAccountDao {
    /** * Get account information based on id *@param id
     * @return* /
    Account getAccountById(Integer id);

    /** * get all accounts *@return* /
    List<Account> getAccounts(a);

    /** * save account *@param account
     */
    void saveAccount(Account account);

    /** * Delete account * by id@param id
     */
    void deleteAccountById(Integer id);

    /** * Update account *@param account
     */
    void updateAccount(Account account);
}
Copy the code

② Write an external configuration fileIAccount.xml

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.bruce.springboot.dao.IAccountDao">

    <! -- Get account info by id -->
    <select id="getAccountById" resultType="cn.bruce.springboot.domain.Account">
        select * from account where ID = #{id}
    </select>

    <! Get all accounts -->
    <select id="getAccounts" parameterType="java.lang.Integer" resultType="cn.bruce.springboot.domain.Account">
        select * from account
    </select>

    <! -- Save account information -->
    <insert id="saveAccount" parameterType="cn.bruce.springboot.domain.Account">
        insert into account(id, uid, money) value (#{id}, #{uid}, #{money})
    </insert>

    <! Delete user by id -->
    <select id="deleteAccountById" parameterType="java.lang.Integer">
        delete from account where ID = #{id}
    </select>

    <! Update account information -->
    <update id="updateAccount" parameterType="cn.bruce.springboot.domain.Account">
        update account set ID = #{id}, UID = #{uid}, MONEY = #{money}
    </update>
</mapper>
Copy the code

③ Use external filesmybatis-config.xmlConfigure MyBatis

      
<! DOCTYPEconfiguration
        PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <! -- Enable hump and Underline conversion -->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>
Copy the code

④ In the global configuration fileapplication.ymlSpecify mapper and configuration file locations in

# Mybatis related configuration
mybatis:
  config-location: classpath:mybatis/mybatis-config.xml
  mapper-locations: classpath:mybatis/mapper/*.xml
Copy the code

Annotations and configuration files can be used together, using annotations for simple SQL queries and an external Mapper for complex SQL queries. You are advised to write all SQL queries into mapper for unified management.

(4) Integration of SpringData JPA

1. SpringData profile

SpringData is an official Spring technology for unified data access, providing access to a variety of data sources to simplify the work of developers, and there are many sub-projects in SpringData to achieve access to different data sources.

1.1 Introduction to SpringData on the Spring website

Spring Data’s mission is to provide a familiar and consistent, Spring-based programming model for data access while still retaining the special traits of the underlying data store.

It makes it easy to use data access technologies, relational and non-relational databases, map-reduce frameworks, and cloud-based data services. This is an umbrella project which contains many subprojects that are specific to a given database. The projects are developed by working together with many of the companies and developers that are behind these exciting technologies.

The mission of Spring Data is to provide a familiar, consistent, Spring-based programming model for Data access while still preserving the special features of the underlying Data store.

It makes it easier to use data access technologies, relational and non-relational databases, map-Reduce frameworks, and cloud-based data services. This is an umbrella project that contains a number of subprojects specific to a given database. These projects are developed through partnerships with many companies and developers that support these exciting technologies.

SpringData website

Subprojects included in SpringData:

• SpringData JDBC
• SpringData JPA
• SpringData MongoDB
• SpringData Redis
• SpringData Neo4j
• .

SpringData data access diagram

1.2 SpringData features:

SpringData provides us with a unified API for manipulating the data access layer; This is primarily done by the Spring Data Commons project. Spring Data Commons allows us to use both relational and non-relational Data access technologies based on a unified Spring standard that includes CRUD (Create, get, update, delete), query, sort, and paging operations.

SpringData provides a unified Repository interface for data access:

Repository

: RevisionRepository

> : Based on optimistic locking mechanism CrudRepository < T, ID extends the Serializable > : basic CRUD operations PagingAndSortingRepository < T, ID extends the Serializable > : Basic CRUD and paging
,>
,>

SpringData provides a template class for data accessxxxTemplate

MongoDBTemplate, RedisTemplate, etc. are used to manipulate data access

2. Integrate with SpringData JPA

2.1 Introduction to SpringData JPA

SpringData JPA is a JPA framework implemented based on the JSR317 specification. JPA (Java Persistence API) is a JDK 5.0 annotation or XML mapping that describes object-relational tables and persists run-time entity objects to a database.

An introduction to SpringData AJpa on the SpringData website

Spring Data JPA, part of the larger Spring Data family, makes it easy to easily implement JPA based repositories. This module deals with enhanced support for JPA based data access layers. It makes it easier to build Spring-powered applications that use data access technologies.

Implementing a data access layer of an application has been cumbersome for quite a while. Too much boilerplate code has to be written to execute simple queries as well as perform pagination, and auditing. Spring Data JPA aims to significantly improve the implementation of data access layers by reducing the effort to the amount that’s actually needed. As a developer you write your repository interfaces, including custom finder methods, and Spring will provide the implementation automatically.

Spring Data JPA is part of the larger Spring Data family and makes it easy to implement JPA-based repositories. This module handles enhanced support for the JPA-based data access layer. It makes it easier to build Spring-driven applications that use data access technology.

Implementing the data access layer of an application is already cumbersome. Too much boilerplate code had to be written to perform simple queries, paging, and auditing. The goal of Spring Data JPA is to significantly improve the implementation of the Data access layer by reducing the actual amount of work required. As a developer, you need to write the repository interface, including custom finder methods, which Spring automatically provides.

Spring Data JPA official documentation

Spring Data JPA vs. MyBatis

2.2 Use of SpringData JPA

SpringData JPA is an Object Relational Mapping (ORM) framework. It needs to work with entity classes (beans) and database tables to perform Relational Mapping and configure the Mapping relationship for common use.

Specific use steps:

① Write entity class (bean);

② Write Dao interface to operate entity class database table;

3. In the configuration file, configure SpringData JPA.

④ Write controller to access;

Write entity class (bean)

@Entity // Notify JPA that this class is an entity class (and a database mapped class)
@Table(name = "tbl_user")// Specify which database table to map to. If omitted, the default table name is an abbreviation of the class name. This table may not exist in the database; the framework will create it automatically
public class User {

    @Id // Indicates that this attribute is the primary key
    @GeneratedValue(strategy = GenerationType.IDENTITY) // Indicates that the ID is automatically increased
    private Integer id;

    @Column(name = "username", length = 50) // Specifies the column name of the database table for this property
    private String name;

    @Column // Omit the default column name as the attribute name
    private Integer sex;

    @Column
    private String email;

    @Column
    private Date birthday;

    public User(a) {}public Integer getId(a) {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName(a) {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getSex(a) {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public String getEmail(a) {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirthday(a) {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString(a) {
        return "User{" +
            "id=" + id +
            ", name='" + name + '\' ' +
            ", sex=" + sex +
            ", email='" + email + '\' ' +
            ", birthday=" + birthday +
            '} '; }}Copy the code

② Write Dao interface to operate entity class database table

public interface UserRepository extends JpaRepository<User.Integer> {}Copy the code

In the configuration file, configure the SpringData JPA

spring:
  datasource:
    username: root
    password: 123456
    url: JDBC: mysql: / / 139.224.83.90:3306 / base_crud? useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    Use DruidSource data source
    type: com.alibaba.druid.pool.DruidDataSource

    Data source connection configuration is relevant
    # initialize size, min, Max
    initialSize: 5
    minIdle: 5
    maxActive: 20
    Set the connection waiting timeout
    maxWait: 60000
    Set the detection interval to close idle connections in milliseconds
    timeBetweenEvictionRunsMillis: 60000
    Set the minimum lifetime of a connection in the connection pool in milliseconds
    minEvictableIdleTimeMillis: 300000
    SQL > check whether the connection is a valid SQL statement
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    # validationQuery checks whether a connection is valid when applying for a connection
    testOnBorrow: false
    ValidationQuery is executed when a connection is returned to check whether the connection is valid. Turning this on degrades performance and defaults to True
    testOnReturn: false
    # if cache preparedStatement, mysql5.5 + recommended
    poolPreparedStatements: true
    # Configure the filters for monitoring statistics interception. After removing the filters, the MONITORING interface SQL cannot be counted. 'wall' is used for the firewall
    filters: stat,wall
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500

  # jPA-related configuration
  jpa:
    hibernate:
      Update or create table structure
      ddl-auto: update
    The console displays SQL
    show-sql: true

server:
  port: 9090
  servlet:
    context-path: /jpa

Copy the code

④ Write controller to access

@RestController
public class UserController {

    @Autowired
    UserRepository userRepository;

    @GetMapping("/saveUser")
    public User saveUser(User user) {
        User saveUser = userRepository.save(user);
        return saveUser;
    }

    @GetMapping("/deleteUserById/{id}")
    public String deleteUserById(@PathVariable("id") Integer id) {
        userRepository.deleteById(id);
        return "Deleted successfully";
    }

    @GetMapping("/updateUser")
    public User updateUser(User user) {
        User updateUser = userRepository.save(user);
        return updateUser;
    }

    @GetMapping("/getUsers")
    public List<User> getUsers(a) {
        List<User> users = userRepository.findAll();
        return users;
    }

    @GetMapping("/getUserById/{id}")
    public User getUserById(@PathVariable("id") Integer id) {
        User user = userRepository.getOne(id);
        returnuser; }}Copy the code

The Content of SpringData JPA is only introduced here, and there will be more blog posts on SpringBoot integration. Further details on the use of SpringData JPA will be blogoed at ヾ(•ω• ‘)o