SpringBoot integrated JDBC,Druid,Mybatis,JPA,Redis summary

[11] SpringBoot integrated JDBC,Druid,Mybatis,JPA,Redis summary

1, Consolidated JDBC


  1. Add jdbc pom dependency
  2. Application Configuring database information in YML
  1. Pom XML add Jdbc dependency
 		<!-- JDBC -->

        <!-- MySQL-->
  1. Application Configuring database information in YML


  1. mysql8 needs to configure time zone information (serverTimezone=UTC)
  2. mysql8 driver use: com Mysql Cj Jdbc Driver

UTC: world standard time

# Classes involved: DataSourceAutoConfiguration, DataSourceConfiguration, DataSourceProperties
    username: root
    password: admin
    # Pay attention to the time zone. mysql8 requires
    url: jdbc:mysql:///mall?useUnicode=true&serverTimezone=UTC&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
  1. Inject DataSource to use JDBC
class SpringbootDataJdbc02ApplicationTests {

    DataSource dataSource;

    private JdbcTemplate jdbcTemplate;

    void contextLoads() throws SQLException {
        // View default data sources

        // Get connection
        Connection connection = dataSource.getConnection();



    void testJdbcTemplate(){
        String sql = "select * from user";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);

2, Consolidate Druid data sources


  1. Add pom dependency for druid
  2. Modify spring Datasource Type
  3. Add druid configuration information
  4. Writing druid configuration classes
  1. Add pom dependency
  <!-- Import data source: Druid-->
  1. Application Add druid configuration in YML
# Classes involved: DataSourceAutoConfiguration, DataSourceConfiguration, DataSourceProperties
    username: root
    password: admin
    # Pay attention to the time zone. mysql8 requires
    url: jdbc:mysql:///mall?useUnicode=true&serverTimezone=UTC&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    ###################druid allocation###########################
    type: com.alibaba.druid.pool.DruidDataSource
    # Number of initialized connection pools
    initialSize: 5
    # Minimum number of connection pools - < is no longer used, and the configuration has no effect
    minIdle: 2
    # Maximum number of connection pools
    maxActive: 20
    # Configure the timeout time for obtaining the connection, in milliseconds. By default, the fair lock is enabled, and the concurrency efficiency will be reduced
    maxWait: 60000
    # Configure how often to detect idle connections that need to be closed. The unit is milliseconds
    timeBetweenEvictionRunsMillis: 60000
    # Configure the minimum lifetime of a connection in the pool, in milliseconds
    minEvictableIdleTimeMillis: 300000
    # The sql used to check whether the connection is valid requires a query statement.
    # If validationQuery is null, testonmirror, testOnReturn, and testwhiteidle will not work
    validationQuery: SELECT 1 FROM DUAL
    # It is recommended to set the configuration to true, which will not affect performance and ensure security.
    # Check when applying for connection. If the idle time is greater than timeBetweenEvictionRunsMillis, run validationQuery to check whether the connection is valid.
    testWhileIdle: true
    # When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance
    testOnBorrow: false
    # When the connection is returned, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance
    testOnReturn: false
    # Open PSCache and specify the size of PSCache on each connection
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    # The extension plug-ins are configured by alias. Multiple English commas are separated. The common plug-ins are:
    # filter:stat for monitoring statistics
    # Filter for log: log4j
    # filter:wall for preventing sql injection
    filters: stat,wall,log4j
    # Open the mergeSql function through the connectProperties property; Slow SQL record
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # Merge monitoring data of multiple DruidDataSource
    useGlobalDataSourceStat: true
  1. druid configuration class


  1. Configure the data source, use @Bean to put it into the container, and then use @ConfigurationProperties to inject the information in the configuration file
  2. Register the Druid data monitoring Servlet and use the ServletRegistrationBean
    1. Reason: there is no web XML, this class is equivalent to web The role of XML
  3. Configure the Druid data monitoring Filter, similarly
public class DruidConfig {

     * Configure the data source and use the customized configuration
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druidDataSource(){
        return new DruidDataSource();
     * Register Druid data monitoring Servlet
    public ServletRegistrationBean statViewServlet(){

        ServletRegistrationBean<StatViewServlet> statViewServletBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");

        // Background account password configuration
        HashMap<String, String> params = new HashMap<>();

        // Account and password settings. The login key is fixed

        // Who is allowed to access? Multiple are separated by commas
        // Forbidden access list, multiple separated by commas
        // Can I reset the data source and disable the "Reset All" function on the HTML page
        params.put("resetEnable", "false");
        return statViewServletBean;

    public FilterRegistrationBean webStatFilter(){

        FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<>();
        bean.setFilter(new WebStatFilter());

        // All requests are monitored and processed
        // Which requests can be filtered
        bean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.css,/druid/*");
        return bean;

3, Integrate Mybatis

1) xml based integration


  1. Pom Add mybatis dependency to XML
  2. Application YML configuring mybatis integration information
  1. Pom Add mybatis dependency to XML

Spring initializr is used in idea. Check the module to quickly create


  1. Application YML configuration database and mybatis integration information
# Database configuration
    username: root
    password: admin
    url: jdbc:mysql:///eesy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver

# Integrate mybatis
  # alias
  type-aliases-package: top.roise.pojo 
  # Xxxmapper XML file location
  mapper-locations: classpath:mybatis/mapper/*.xml  

  1. Writing entity classes and Mapper


public class User {

    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;


@Mapper: mark this class as a mapper interface of mybatis, which can be automatically scanned into the spring context by spring boot

public interface UserMapper {

    List<User> findUsers();

    User findUserById(int id);

    void insertUser(User user);

    int updateUser(User user);

    int deleteUser(int id);


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
<mapper namespace="top.roise.mapper.UserMapper">
	<!-- Insert user-->
    <insert id="insertUser" parameterType="top.roise.pojo.User">
        insert into user values(#{id},#{username},#{birthday},#{sex},#{address})
    <!-- Update user-->
    <update id="updateUser" parameterType="user">
        update user set username=#{username} where id = #{id}
    <!-- delete user-->  
    <delete id="deleteUser" parameterType="int">
        delete from user where id = #{id}
	<!-- Query all users-->
    <select id="findUsers" resultType="top.roise.pojo.User" >
        select * from user
    <!-- according to id Find users-->
    <select id="findUserById" resultType="top.roise.pojo.User">
        select * from user where id = #{id};

2) Annotation based integration

Mainly use @select, @delete, @insert and @update

Example: mapper class

public interface EmployeeDao {

	// @Options(): require primary key increment
	@Insert("insert into employee(last_name,email,gender,department_id,birth) values(#{lastName},#{email},#{gender},#{department.id},#{birth})")
	@Options(useGeneratedKeys = true, keyProperty = "id")
	void save(Employee employee);

	@Select("select * from employee")
	List<Employee> getEmplyees();

	@Select("select * from employee where id = #{id}")
	@Results(id = "employeeMap",value = {
			@Result(id = true,column = "id",property = "id"),
			@Result(column = "last_name",property = "lastName"),
			@Result(column = "email",property = "email"),
			@Result(column = "gender",property = "gender"),
			@Result(column = "department_id",property = "department",one = @One(select = "roise.top.mapper.DepartmentDao.getDepartment",fetchType = FetchType.EAGER)),
			@Result(column = "birth",property = "birth",javaType = Date.class)
	Employee get(Integer id);

	@Delete("delete from employee where id = #{id}")
	void delete(Integer id);

Then you can use it directly

For the use of mybatis annotation, please refer to: mybatis annotation

Add: when adding elements, you need to automatically increase the primary key

@Options(useGeneratedKeys = true, keyProperty = "id")

4, Integrate Spring Data JPA

  1. Add the start dependency of Spring Data JPA
<!-- springBoot JPA Start dependence -->
  1. Add database driver dependency
<!-- MySQL Connection drive -->
  1. In application Properties to configure database and jpa related properties
# Database connection configuration information

#JPA configuration
  1. Create entity configuration entity
public class User {
    // Primary key
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    // user name
    private String username;
    // password
    private String password;
    // name
    private String name;
    //The setter and getter methods are omitted here
  1. Writing UserRepository
public interface UserRepository extends JpaRepository<User,Long>{
    public List<User> findAll();
  1. Writing test classes
public class JpaTest {

    private UserRepository userRepository;

    public void test(){
        List<User> users = userRepository.findAll();
  1. Console print information

Note: if it is jdk9, the execution error is as follows:

Cause: the jdk lacks the corresponding jar

Solution: manually import the corresponding maven coordinates as follows:

<!--jdk9 The following coordinates need to be imported-->

5, Integrate Redis


  1. Add the start dependency of redis
  2. Configure redis connection information
  3. Writing Redis configuration classes
  1. Add the start dependency of redis
<!-- Configuration use redis starter -->
  1. Configure redis connection information
    #redis server address
    #redis connection port number
    port: 6379
    #redis connection password
    password: 199611
    #redis connection timeout (MS)
    timeout: 18000
        # Connection pool minimum free connections
        min-idle: 1
        # Connection pool maximum free connections
        max-idle: 10
        # Connection pool maximum blocking wait time (use a negative value to indicate no limit)
        max-wait: -1
        # Maximum number of connections in the connection pool (use a negative value to indicate no limit)
        max-active: -1
  1. Writing Redis configuration classes
public class RedisConfig extends CachingConfigurerSupport {

     * Custom redisTemplate method
    public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory factory) {

        RedisTemplate<String, Object> template = new RedisTemplate<>();
        // Configure connection factory

        //Use Jackson2JsonRedisSerializer to serialize and deserialize the value value of redis (the JDK serialization method is used by default)
        Jackson2JsonRedisSerializer jacksonSeial = new Jackson2JsonRedisSerializer(Object.class);

        ObjectMapper om = new ObjectMapper();
        // Specify the fields to be serialized, field,get, set, and modifier range. ANY includes private and public
        om.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);
        // Specify the type of serialized input. The class must be non final modified. Final modified classes, such as string and integer, will run exceptions

        // jackson is used for value serialization
        // key is serialized by String
        template.setKeySerializer(new StringRedisSerializer());

        // The key of hash is serialized by String
        template.setHashKeySerializer(new StringRedisSerializer());
        // The value of hash is serialized by jackson

        return template;
  1. Inject RedisTemplate to test redis operation
@SpringBootTest(classes = SpringbootJpaApplication.class)
public class RedisTest {

    private UserRepository userRepository;

    private RedisTemplate<String, String> redisTemplate;

    public void test() throws JsonProcessingException {
        //Get the specified data from the redis cache
        String userListData = redisTemplate.boundValueOps("user.findAll").get();
        //If there is no data in redis
            //Query database to obtain data
            List<User> all = userRepository.findAll();
            //Convert to json format string
            ObjectMapper om = new ObjectMapper();
            userListData = om.writeValueAsString(all);
            //Store the data in redis, and get the data directly from redis in the next query, instead of querying the database
            System.out.println("===============Get data from database===============");
            System.out.println("===============from redis Get data from cache===============");


Tags: MySQL Spring Boot Mybatis

Posted by krabople on Wed, 01 Jun 2022 19:07:58 +0530