1. JDBC

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

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

spring:
  datasource:
    username: root
    password: zhangjiahui
    url: jdbc:mysql://192.168.199.172:3306/jdbc
    driver-class-name: com.mysql.cj.jdbc.Driver

Effect:

By default, com.zaxxer.hikari.HikariDataSource is used as the data source

Data source related configurations are in DataSourceProperties

Auto configuration principle:

org.springframework.boot.autoconfigure.jdbc

1. Refer to DataSourceConfiguration, create data source according to configuration, and use HikariDataSource by default; you can use spring.datasource.type to specify custom data source type

2. Spring boot supports the following data sources by default

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

3. Custom data source

   @ConditionalOnMissingBean({DataSource.class})
   @ConditionalOnProperty(
       name = {"spring.datasource.type"}
   )
   static class Generic {
       Generic() {
       }
   
       @Bean
       public DataSource dataSource(DataSourceProperties properties) {
           //Use DataSourceBuilder to create data source, use reflection to create response type data source, and bind related attributes
           return properties.initializeDataSourceBuilder().build();
       }
   }
   

4. DataSourceInitializerInvoker

DataSourceAutoConfiguration

@Configuration
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ DataSourcePoolMetadataProvidersConfiguration.class,
      DataSourceInitializationConfiguration.class })
public class DataSourceAutoConfiguration {

DataSourceInitializationConfiguration

@Configuration
@Import({ DataSourceInitializerInvoker.class,
      DataSourceInitializationConfiguration.Registrar.class })
class DataSourceInitializationConfiguration {

DataSourceInitializerInvoker

/**
 * 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<DataSourceSchemaCreatedEvent>, InitializingBean {
    
    @Override
	public void onApplicationEvent(DataSourceSchemaCreatedEvent event) {
		// NOTE the event can happen more than once and
		// the event datasource is not used here
		DataSourceInitializer initializer = getDataSourceInitializer();
		if (!this.initialized && initializer != null) {
			initializer.initSchema();
			this.initialized = true;
		}
	}

1. DataSourceInitializerInvoker will automatically create tables and initialize data for us. We only need to place the script in the specified directory with a specific naming method:

   It is placed in the classpath path by default, and the naming rules are as follows:

   1. Table creation script: schema-*.sql
   2. Initialization data script: data-*.sql

Custom path:

spring:
  datasource:
    schema:
      - classpath:db/department.sql
      - classpath:db/init_department.sql

Spring boot2. X important setting item: spring.datasource.initialization-mode initialization mode (springboot2.0). There are three values. Always is to always perform initialization, embedded is only to initialize memory database (default value), such as h2, and never is not to perform initialization.

Note: mysql database is case sensitive

1. Automatic injection of JdbcTemplate

@Configuration
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(JdbcProperties.class)
public class JdbcTemplateAutoConfiguration {

2. Integrate basic Druid data sources

Druid data source configuration:

1. Import data source

   <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>druid</artifactId>
       <version>1.1.12</version>
   </dependency>
   

2. Data source property binding

   @Configuration
   public class DruidConfig {
   
       @ConfigurationProperties(prefix = "spring.datasource")
       @Bean
       public DataSource druid() {
           return new DruidDataSource();
       }
   }
   

3. Attribute configuration

   spring:
     datasource:
       initialSize: 5
       minIdle: 5
       maxActive: 20
       maxWait: 60000
       timeBetweenEvictionRunMillis: 60000
       minEvictableIdleTimeMillis: 300000
       validationQuery: SELECT 1 FROM DUAL
       testWhileIdle: true
       testOnBorrow: false
       testOnReturn: false
       poolPreparedStatements: true
       #Configure the filters for monitoring statistics interception. After removing the filters, the sql of monitoring interface cannot be counted, and "wall" is used for firewall
       filters: stat,wall,log4j2
       maxPoolPreparedStatementPerConnectionSize: 20
       useGlobalDataSourceStat: true
       connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
   

4. Configure Servlet and Filter

@Configuration
public class DruidConfig {

    @Bean
    public ServletRegistrationBean statViewServlet() {

        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        Map<String, String> initParams = new HashMap<>();
        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", "admin");
        initParams.put("allow", "");
        initParams.put("deny", "");

        bean.setInitParameters(initParams);

        return bean;
    }

    @Bean
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();

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

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

        return bean;
    }
}

3. Integrate Mybatis

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

Dependency:

Steps:

1. Introduce dependency and configuration attributes (see the previous section)

2. Create database table

3. Create JavaBean: Department/Employee

4. Add, delete, modify and check

1. annotated edition

1. Mapper

//Specify that this is a mapper that operates the database
@Mapper
public interface DepartmentMapper {

    @Select("SELECT * FROM department WHERE id=#{id}")
    public Department getDeptById(Integer id);

    @Delete("DELETE FROM department WHERE id=#{id}")
    public int deleteDeptById(Integer id);

    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("INSERT INTO department(departmentName) VALUES(#{departmentName})")
    public int insertDept(Department dept);

    @Update("UPDATE department SET departmentName=${departmentName} WHERE id=${id}")
    public int updateDept(Department dept);
}

1. Controller

@RestController
public class DeptController {

    @Autowired
    DepartmentMapper departmentMapper;

    @GetMapping("/dept/{id}")
    public Department getDept(@PathVariable("id") Integer id) {

        return departmentMapper.getDeptById(id);
    }

    @GetMapping("/dept")
    public Department insertDept(Department department) {
        departmentMapper.insertDept(department);
        return department;
    }
}

1. Customize Mybatis configuration method

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

    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return new ConfigurationCustomizer() {
            @Override
            public void customize(Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

2. Profile version

1. Mapper

//@Mapper or @ MapperScan assemble interface scan into the container
public interface EmployeeMapper {

    public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);
}

1. Controller

@Controller
public class EmpController {

    @Autowired
    EmployeeMapper employeeMapper;

    @ResponseBody
    @GetMapping("/emp/{id}")
    public Employee getEmp(@PathVariable(value = "id") Integer id) {
        Employee employee = employeeMapper.getEmpById(id);

        return employee;
    }
}

1. Mybatis main configuration file mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

1. mapper configuration file EmployeeMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qiang.springboot.mapper.EmployeeMapper">
    <!--
    public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);-->
    <select id="getEmpById" resultType="com.qiang.springboot.bean.Employee">
        SELECT * FROM employee WHERE id=#{id}
    </select>

    <insert id="insertEmp">
        INSERT INTO employee(last_name, email, gender, department_id, birth) VALUES(#{lastName}, #{email}, #{gender}, #{departmentId}, CURRENT_DATE);
    </insert>
</mapper>

1. Main configuration file and mapper file path specification

#Related configurations of mybatis start with mybatis
mybatis:
  #Specify the primary profile path
  config-location: classpath:mybatis/mybatis-config.xml
  #Specifies the mapper profile path (array, which can be matched with wildcards)
  mapper-locations: classpath:mybatis/mapper/*.xml

5. Two methods of specifying Mapper

1. Use @ Mapper annotation

   //Directly add the @ mapper annotation to the interface class, and specify that this is a mapper that operates the database
   @Mapper
   public interface DepartmentMapper {
   
       @Select("SELECT * FROM department WHERE id=#{id}")
       public Department getDeptById(Integer id);
   
       @Delete("DELETE FROM department WHERE id=#{id}")
       public int deleteDeptById(Integer id);
   
       @Options(useGeneratedKeys = true, keyProperty = "id")
       @Insert("INSERT INTO department(departmentName) VALUES(#{departmentName})")
       public int insertDept(Department dept);
   
       @Update("UPDATE department SET departmentName=${departmentName} WHERE id=${id}")
       public int updateDept(Department dept);
   }
   

2. Use @ mapperscan (value = "mapper package") annotation

   //Add the annotation @ mapperscan (value = "mapper package") to the SpringBoot main program
   //All classes under the mapper package will be identified as mapper
   @MapperScan(value = "com.qiang.springboot.mapper")
   @SpringBootApplication
   public class SpringBoot06DataMybatisApplication {
   
       public static void main(String[] args) {
           SpringApplication.run(SpringBoot06DataMybatisApplication.class, args);
       }
   
   }
   

4. integrate JPA

1. Introduction to spring data

2. Integrate Spring Data JPA

JPA is based on ORM (Object Relational Mapping) idea

1. Write an entity class (bean) and data table for mapping, and configure the relationship

@Entity //Tell JPA that this is an entity class (a class mapped to a data table)
@Table(name = "tbl_user") //Specifies which data table to correspond to. If this annotation is omitted, the lowercase class name is used as the mapping table name by default
public class User {

    /**
     * @Id : Tell JPA this is a primary key field
     * @GeneratedValue : Set up automatically
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) //Set up automatically
    private Integer id;

    /**
     * @Column : The attribute corresponding to a column in the data table. The default attribute name is the column name
     */
    @Column
    private String lastName;

    /**
     * @Column : You can use name to specify the column name and length to specify the column length
     */
    @Column(name = "user_email", length = 50)
    private String email;
    
    //getter & setter
    //...
}

1. Write a dao interface to operate the data table corresponding to the entity class (Repository)

//Repository must be an interface
//Inherit JpaRepository to complete the operation of database
public interface UserRepository extends JpaRepository<User, Integer> {
}

1. Basic configuration

spring:
  datasource:
    url: jdbc:mysql://192.168.199.172:3306/jpa
    username: root
    password: zhangjiahui
    driver-class-name: com.mysql.cj.jdbc.Driver

  jpa:
    hibernate:
      #Update or create data table structure
      ddl-auto: update
    #Display SQL in the console
    show-sql: true

Changes of findOne() after JPA 2.x