Interested friends can go to know about the other several articles have been published, your praise is the biggest support for me, thank you!

The article directories

SpringBoot takeoff road -HelloWorld

(two) SpringBoot takeoff road – analysis of the principle of entry

(three) SpringBoot takeoff road -YAML configuration summary (entry must know will)

(4) SpringBoot takeoff road – static resource processing

(five) SpringBoot takeoff road -Thymeleaf template engine

(6) SpringBoot takeoff road – integration jdbctemplate-druid-mybatis

(7) SpringBoot launch road – integration of SpringSecurity (Mybatis, JDBC, memory)

(eight) SpringBoot take-off path – integration Shiro detailed tutorial (MyBatis, Thymeleaf)

SpringBoot -Swagger 2 And 3

Description:

SpringBoot takeoff road series of articles involved in the source code, are synchronized to github, there is a need for small partners, go down at will
- Github.com/ideal-20/Sp…
Untalented and uneducated, will point to shallow knowledge, we should be a tool to see it, do not like to spray ha ~

The introduction

The Thymeleaf template engine has been introduced, and some small demos will start to involve data, so today we will talk about how to integrate common data related techniques in SpringBoot: JdbcTemplate, Druid, MyBatis

The content introduced in this article is not new and does not involve too much grammar. The key is integration. I will briefly mention the three kinds of introduction

And finally, before we start, there’s one more thing to mention

SpringBoot on the database related processing, all use Spring Data, it is a sub-project in the Spring family bucket, can support the operation of relational/non-relational database, can greatly simplify the operation of database access

For more information, you can go to the official website:

https://spring.io/projects/spring-data

(1) Integrate JdbcTemplate

Although it is called integrating JdbcTemplate, it is essentially integrating JDBC, but the JdbcTemplate simplifies native JDBC

(1) Introducing dependencies

We first create a Project or Module, then initialize a SpringBoot Project, in addition to the basic Web, and select the JDBC API from the SQL option on the left, which introduces a dependency on integrating JDBC

Take a look at POM, which introduces spring-boot-starter-JDBC as an initiator, and encapsulates some of its dependencies

Details can refer to the official website documents, I posted two versions, more versions, you can go to read

https://docs.spring.io/spring-boot/docs/2.3.0.RELEASE/reference/html/using-spring-boot.html#using-boot-structuring-your- code

https://docs.spring.io/spring-boot/docs/2.2.7.RELEASE/reference/html/using-spring-boot.html#using-boot-starter

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

The previous version of mysql-connector-Java should also be introduced. If not, don’t worry, we will introduce it later when we write database configuration

(2) Modify the configuration

Next, we need to configure some information about the database in the configuration file, such as username, password and so on. We use YML for this, and the same with properties

One more word about the URL option, my table name is springboot_mybatis_test, but if you do not set the time zone or some encoding, there may be some errors in the use of different versions.

When setting the time zone, as I set serverTimezone=UTC, it will be 8 hours earlier than China time, so China can choose Asia/Shanghai or Asia/Hongkong

spring:
  datasource:
    username: root
    password: root99
    # serverTimezone=Asia/Shanghai
    url: jdbc:mysql://localhost:3306/springboot_mybatis_test? serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
Copy the code

If mysql-connector-java is not added to the mysql-connector-java dependencies, the configuration will be displayed in red. If mysql-connector-java is not added to the connector-java dependencies, the configuration will be displayed in red. If mysql-connector-java is not added to the connector-java dependencies, the configuration will be displayed in red. The default version-free dependency is the 8.x version of mysql-connector-Java

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

Therefore, select com.mysql.cj.jdbc.driver during configuration

Com.mysql.jdbc.driver is out of date

Loading class com.mysql.jdbc.Driver'. This is deprecated. The new driver class is com.mysql.cj.jdbc.Driver’. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

You can also explicitly specify the mysql-connector-Java version if you want to use older versions

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.26</version>
</dependency>
Copy the code

(3) Use JdbcTemplate for CURD

After simple configuration, it is already possible to add, delete, change and check the native JDBC. The native JDBC is a bit cumbersome, Spring has given us a certain simplification, namely the JdbcTemplate, which we should have used for a long time

A: the query

As for the query, I show you two ways, one is if we have an entity corresponding to the content of the database, and one is if there is no entity corresponding to the query, and don’t forget to inject the JdbcTemplate

There are entities: New a BeanPropertyRowMapper in the query, which asked JdbcTemplate help us put the query result set. Each row of the ResultSet results use BeanPropertyRowMapper mapRow () method, To the Java class object we want
No entities: Use the queryForList method to return a collection of individual maps

@Controller
@RequestMapping("/jdbc")
public class JdbcTemplateController {
    @Autowired
    JdbcTemplate jdbcTemplate;
    
	// There are corresponding entities
    @RequestMapping("/userList")
    public void userList(a){
        String sql = "select * from user";
        ArrayList<User> users = (ArrayList<User>) jdbcTemplate.query(sql,
                new BeanPropertyRowMapper<User>(User.class));
        for(User user : users){ System.out.println(user); }}// There is no corresponding entity
    @RequestMapping("/userList")
    @ResponseBody
    public List<Map<String, Object>> userList(){
        String sql = "select * from user";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        returnmaps; }}Copy the code

This is the collection of objects that we found

User{uid=1, username='zhangsan', password='666', nickname='The Flying Penguin'}
User{uid=2, username='lisi', password='666', nickname='Sad Little Boy'}
Copy the code

This is the set of maps that we found

[{"uid":1."username":"zhangsan"."password":"666"."nickname":"The Flying Penguin"}, {"uid":2."username":"lisi"."password":"666"."nickname":"Sad little Boy"}]
Copy the code

B: insert

@RequestMapping("/addUser")
@ResponseBody
public String addUser(a){
    // Insert statement, pay attention to time problem
    String sql = "Insert into user (uid, username, password and nickname) values (NULL, 'wangwu', '888', 'Cathy nickname')";
    jdbcTemplate.update(sql);
    / / query
    return "User added successfully";
}
Copy the code

C: modify

// Modify user information
@RequestMapping("/updateUser/{uid}")
@ResponseBody
public String updateUser(@PathVariable("uid") int uid){
    // Insert statement
    String sql = "update user set username=? ,password=? ,nickname=? where uid="+ uid;
    / / data
    String[] s = new String[3];
    s[0] = "jack";
    s[1] = "666";
    s[2] = "Jack";
    jdbcTemplate.update(sql,s);
    / / query
    return "User information modified successfully";
}
Copy the code

D: delete

// Delete the user
@RequestMapping("/deleteUser/{uid}")
@ResponseBody
public String delUser(@PathVariable("uid") int uid){
    // Insert statement
    String sql = "delete from user where uid=?";
    jdbcTemplate.update(sql,uid);
    / / query
    return "User deleted successfully";
}
Copy the code

(2) Integrate Druid

(1) SpringBoot default connection pool

Druid is an open source database connection pool that can be used to create and close a database. The connection pool can be used to create and close a database

SpringBoot, by default, has a configuration data source, which we can look at briefly, just like in the JDBC example above, in the test class

@SpringBootTest
class Springboot08JdbcApplicationTests {

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads(a) throws SQLException {
        / / the data source
        System.out.println(dataSource.getClass());
        // Get the connectionConnection connection = dataSource.getConnection(); System.out.println(connection); connection.close(); }}Copy the code

Take a look at the print

class com.zaxxer.hikari.HikariDataSource
HikariProxyConnection@118492650 wrapping com.mysql.cj.jdbc.ConnectionImpl@712cfb63
Copy the code

As you can see, SpringBoot uses Hikari connection pooling by default. If you want to take a look at the source code, you can take a look at these two classes

DataSourceAutoConfiguration, DataSourceConfiguration

Hikari and Druid are excellent database connection pools. Druid has a lot more functionality than Hikari and Druid. It also has some features such as statistics or SQL blocking

(2) Introducing dependencies

To start, add Druid’s dependencies to your Pom. Check out the latest version at the following url

https://mvnrepository.com/artifact/com.alibaba/druid

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

(3) Modify the configuration

Then use the spring in the configuration. The datasource. Type to specify a custom data source types, the value with the connection pool to realize the fully qualified name, for example: com. Alibaba. Druid. Pool. DruidDataSource

Druid = yML; Druid = yML; Druid = yML

spring:
  datasource:
    username: root
    password: root99
    url: jdbc:mysql://localhost:3306/springboot_mybatis_test? serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    # Custom data source
    type: com.alibaba.druid.pool.DruidDataSource 

    #druid Data source proprietary configuration
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true

    Configure filters to monitor statistical intercepts
    #stat: monitor statistics, log4j: log, wall: defend against SQL injection
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500
Copy the code

If the run time error Java. Lang. ClassNotFoundException: org.. Apache log4j. Priority import log4j rely on

Address: https://mvnrepository.com/artifact/log4j/log4j

<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>
Copy the code

(4) Bind custom configuration parameters

Some privatized Settings such as the following initialization size or Max/min connection count will not take effect because Spring Boot does not inject these values by default, so we need to bind them ourselves

Create a package called config and create the DruidConfig class

We first have to do is to bind the Druid configuration of global configuration files to com. Alibaba. Druid. Pool. DruidDataSource, it can take effect (@ ConfigurationProperties)

The custom Druid data source is then added to the container so that SpringBoot does not need to create it itself

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(a) {
        return newDruidDataSource(); }}Copy the code

It’s a simple test to see if it works

@SpringBootTest
class Springboot08JdbcApplicationTests {

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads(a) throws SQLException {
        / / the data source
        System.out.println(dataSource.getClass());
        // Get the connection
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        DruidDataSource druidDataSource = (DruidDataSource) dataSource;
        System.out.println("Maximum number of connections to data source:" + druidDataSource.getMaxActive());
        System.out.println("Data source initialization number of connections:" + druidDataSource.getInitialSize());
		// Close the connectionconnection.close(); }}Copy the code

The run shows that the basic configuration is in effect

class com.alibaba.druid.pool.DruidDataSource com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@74e6094b druidDataSource Maximum number of connections to a data source: 20 druidDataSource Number of initial connections to a data source: 5Copy the code

(5) Configure data source monitoring

Another feature of Druid is data source monitoring, which provides a background management page

Continue adding to the new DruidConfig configuration class above

Spring Boot is used to register servlets, because the built-in Servlet container does not have a web. XML file, it is some fixed writing method
Parameters such as user names can be found in the ResourceServlet parent of StatViewServlet under Druid
You can also set access personnel to be associated with access prohibited personnel

@Bean
public ServletRegistrationBean statViewServlet(a) {
    ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
    
    Map<String, String> initParams = new HashMap<>();
    initParams.put("loginUsername"."admin"); // Account used to log in to the background management interface
    initParams.put("loginPassword"."admin888"); // Password for logging in to the background GUI

    // initparams. put("allow", "localhost") : indicates that only the localhost is accessible
    // initparams. put("allow", ""): indicates that the background allows all access
    initParams.put("allow"."");

    // Set the initialization parameters
    bean.setInitParameters(initParams);
    return bean;
}
Copy the code

Take a look:

We enter http://localhost:8080/druid/ in the address bar of any path to access the login page background, after login, can see some such as SQL monitoring etc. Function

(3) Integration of MyBatis

So this is basically a normal user table, so let’s just test it out, and just to make it look a little bit more comfortable, I’m not specifying Druid here, so if you want to, you can just click on it, ok

(1) Introducing dependencies

Introduce mybatis initiator and connect to MySQL driver

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

<! MySQL connection driver -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
Copy the code

(2) Configure the database

Mybatis = mybatis = mybatis = Mybatis = Mybatis = Mybatis = Mybatis = Mybatis = Mybatis = Mybatis Type-aliases-package configures the package where we put our entity, that is, the scan package

spring:
  datasource:
    username: root
    password: root99
    url: jdbc:mysql://localhost:3306/springboot_mybatis_test? serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
# type: com. Alibaba. Druid. Pool. DruidDataSource # custom data source
  
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: cn.ideal.pojo
Copy the code

(3) Create entities

public class User {
    private Long uid;
    private String username;
    private String password;
    privateString nickname; . get set toString }Copy the code

(4) Write Mapper and mapper.xml

UserMapper

@Mapper
public interface UserMapper {
    public List<User> queryAllUser(a);
}
Copy the code

UserMapper.xml

This file path SRC \main\resources\mapper\ usermapper.xml


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.ideal.mapper.UserMapper">
    <select id="queryAllUser" resultType="cn.ideal.domain.User">
        select * from user
    </select>
</mapper>
Copy the code

(5) test

@Controller
public class MybatisController {
    @Autowired
    private UserMapper userMapper;

    @RequestMapping("/queryAllUser")
    @ResponseBody
    public List<User> queryUser(a) {
        List<User> users = userMapper.queryAllUser();
        returnusers; }}Copy the code

Return result:

[{"uid":1."username":"zhangsan"."password":"666"."nickname":"The Flying Penguin"}, {"uid":2."username":"lisi"."password":"666"."nickname":"Sad little Boy"}, {"uid":3."username":"jack"."password":"666"."nickname":"Jack"}]
Copy the code

(4) Redis integration

After writing the integrated MyBatis, I will briefly mention Redis, because I can use the integrated MyBatis to determine whether the data comes from the database or the cache when testing later

(1) Introducing dependencies

<! Redis initiator -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
Copy the code

(2) Modify the configuration

You can use “properties” here

spring:
  redis:
    host: 127.0. 01.
    port: 6379
Copy the code

Port is the default Redis, at the same time you can open the Redis client, installed in the directory to find the good redis-server.exe

(3) Test Redis by RedisTemplate

So let’s go ahead and test it in the test class, and here’s what it looks like, the first time it looks up from the database, and then it writes to the cache, and the second time it looks up from the cache because it already has data in the cache

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

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private RedisTemplate<String, String> redisTemplate;

    @Test
    public void test(a) throws JsonProcessingException {
        // check from redis
        String userLists = redisTemplate.boundValueOps("user.queryAllUser").get();
        // If there is no content in redis
        if (null == userLists) {
            // Query database data
            List<User> all = userMapper.queryAllUser();
            / / json
            ObjectMapper om = new ObjectMapper();
            userLists = om.writeValueAsString(all);
            // Store the data in Redis, and get the data directly from Redis next time
            redisTemplate.boundValueOps("user.queryAllUser").set(userLists);
            System.out.println("This data is from the database!!");
        } else {
            System.out.println("This data is retrieved from redis cache!!"); } System.out.println(userLists); }}Copy the code

The execution result

For the first time,

This data is obtained from the database!! [{"uid":1."username":"zhangsan"."password":"666"."nickname":"The Flying Penguin"}, {"uid":2."username":"lisi"."password":"666"."nickname":"Sad little Boy"}, {"uid":3."username":"jack"."password":"666"."nickname":"Jack"}]
Copy the code

The second time

This data is retrieved from redis cache!! [{"uid":1."username":"zhangsan"."password":"666"."nickname":"The Flying Penguin"}, {"uid":2."username":"lisi"."password":"666"."nickname":"Sad little Boy"}, {"uid":3."username":"jack"."password":"666"."nickname":"Jack"}]
Copy the code

(5) Ending

If there is any deficiency in the article, you are welcome to leave a message to exchange, thank friends for their support!

If it helps you, follow me! If you prefer the way of reading articles on wechat, you can follow my official account

We don’t know each other here, but we are working hard for our dreams

A adhere to push original development of technical articles of the public number: ideal more than two days

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

(6) SpringBoot takeoff road – integration jdbctemplate-druid-mybatis -Redis

The introduction

(1) Integrate JdbcTemplate

(1) Introducing dependencies

(2) Modify the configuration

(3) Use JdbcTemplate for CURD

A: the query

B: insert

C: modify

D: delete

(2) Integrate Druid

(1) SpringBoot default connection pool

(2) Introducing dependencies

(3) Modify the configuration

(4) Bind custom configuration parameters

(5) Configure data source monitoring

(3) Integration of MyBatis

(1) Introducing dependencies

(2) Configure the database

(3) Create entities

(4) Write Mapper and mapper.xml

(5) test

(4) Redis integration

(1) Introducing dependencies

(2) Modify the configuration

(3) Test Redis by RedisTemplate

(5) Ending

(6) SpringBoot takeoff road – integration jdbctemplate-druid-mybatis -Redis

The introduction

(1) Integrate JdbcTemplate

(1) Introducing dependencies

(2) Modify the configuration

(3) Use JdbcTemplate for CURD

A: the query

B: insert

C: modify

D: delete

(2) Integrate Druid

(1) SpringBoot default connection pool

(2) Introducing dependencies

(3) Modify the configuration

(4) Bind custom configuration parameters

(5) Configure data source monitoring

(3) Integration of MyBatis

(1) Introducing dependencies

(2) Configure the database

(3) Create entities

(4) Write Mapper and mapper.xml

(5) test

(4) Redis integration

(1) Introducing dependencies

(2) Modify the configuration

(3) Test Redis by RedisTemplate

(5) Ending

Related Posts

Interviewer: Talk about the singleton, young man

Primary six learns Hbase for big data

Climb dangdang net comment