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

1, Consolidated JDBC

Steps:

1. Add jdbc pom dependency
2. Application Configuring database information in YML

1. Pom XML add Jdbc dependency

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

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

1. Application Configuring database information in YML

Note:

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
spring:
  datasource:
    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

@SpringBootTest
class SpringbootDataJdbc02ApplicationTests {

    @Autowired
    DataSource dataSource;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void contextLoads() throws SQLException {
        // View default data sources
        System.out.println(dataSource.getClass());

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

        System.out.println(connection);

        connection.close();
    }

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

2, Consolidate Druid data sources

Steps:

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-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>

1. Application Add druid configuration in YML

# Classes involved: DataSourceAutoConfiguration, DataSourceConfiguration, DataSourceProperties
spring:
  datasource:
    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

Function:

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

@Configuration
public class DruidConfig {

    /**
     * Configure the data source and use the customized configuration
     */
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }
    
    /**
     * Register Druid data monitoring Servlet
     */
    @Bean
    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
        params.put("loginUsername","admin");
        params.put("loginPassword","111");

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

    @Bean
    public FilterRegistrationBean webStatFilter(){

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

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

3, Integrate Mybatis

1) xml based integration

Steps:

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

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

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

1. Application YML configuration database and mybatis integration information

# Database configuration
spring:
  datasource:
    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
mybatis:
  # alias
  type-aliases-package: top.roise.pojo 
  # Xxxmapper XML file location
  mapper-locations: classpath:mybatis/mapper/*.xml  

1. Writing entity classes and Mapper

User

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {

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

UserMapper

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

@Mapper
@Repository
public interface UserMapper {

    List<User> findUsers();

    User findUserById(int id);

    void insertUser(User user);

    int updateUser(User user);

    int deleteUser(int id);
}

UserMapper.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="top.roise.mapper.UserMapper">
    
	<!-- Insert user-->
    <insert id="insertUser" parameterType="top.roise.pojo.User">
        insert into user values(#{id},#{username},#{birthday},#{sex},#{address})
    </insert>
    
    <!-- Update user-->
    <update id="updateUser" parameterType="user">
        update user set username=#{username} where id = #{id}
    </update>
        
    <!-- delete user-->  
    <delete id="deleteUser" parameterType="int">
        delete from user where id = #{id}
    </delete>
        
	<!-- Query all users-->
    <select id="findUsers" resultType="top.roise.pojo.User" >
        select * from user
    </select>
        
    <!-- according to id Find users-->
    <select id="findUserById" resultType="top.roise.pojo.User">
        select * from user where id = #{id};
    </select>
</mapper>

2) Annotation based integration

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

Example: mapper class

@Repository
@Mapper
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")
	@ResultMap("employeeMap")
	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 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

1. Add database driver dependency

<!-- MySQL Connection drive -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

1. In application Properties to configure database and jpa related properties

# Database connection configuration information
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root

#JPA configuration
spring.jpa.database=MySQL
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy

1. Create entity configuration entity

@Entity
public class User {
    // Primary key
    @Id
    @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

@RunWith(SpringRunner.class)
@SpringBootTest(classes=MySpringBootApplication.class)
public class JpaTest {

    @Autowired
    private UserRepository userRepository;

    @Test
    public void test(){
        List<User> users = userRepository.findAll();
        System.out.println(users);
    }
}

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-->
<dependency>
    <groupId>javax.xml.bind</groupId>
    <artifactId>jaxb-api</artifactId>
    <version>2.3.0</version>
</dependency>

5, Integrate Redis

Steps:

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 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>

1. Configure redis connection information

#Redis
redis:
    #redis server address
    host: 192.168.0.102
    #redis connection port number
    port: 6379
    #redis connection password
    password: 199611
    #redis connection timeout (MS)
    timeout: 18000
    lettuce:
      pool:
        # 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

@Configuration
public class RedisConfig extends CachingConfigurerSupport {

    /**
     * Custom redisTemplate method
     */
    @Bean
    @SuppressWarnings("all")
    public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory factory) {

        RedisTemplate<String, Object> template = new RedisTemplate<>();
        // Configure connection factory
        template.setConnectionFactory(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
        om.enableDefaultTyping(ObjectMapper.DefaultTyping.NON_FINAL);
        jacksonSeial.setObjectMapper(om);

        // jackson is used for value serialization
        template.setValueSerializer(jacksonSeial);
        // 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
        template.setHashValueSerializer(jacksonSeial);
        template.afterPropertiesSet();

        return template;
    }
}

1. Inject RedisTemplate to test redis operation

@RunWith(SpringRunner.class)
@SpringBootTest(classes = SpringbootJpaApplication.class)
public class RedisTest {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private RedisTemplate<String, String> redisTemplate;

    @Test
    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
        if(null==userListData){
            //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
            redisTemplate.boundValueOps("user.findAll").set(userListData);
            System.out.println("===============Get data from database===============");
        }else{
            System.out.println("===============from redis Get data from cache===============");
        }
        System.out.println(userListData);

    }
}