Integrate JDBC

Integrate JDBC

Use Spring Boot to simply operate the database, you can use JDBC, and Spring Boot encapsulates JDBC, which eliminates the need for tedious operations such as establishing connections and creating Statements. You only need to use the JdbcTemplate provided by Sping Boot.

Introduce dependencies

To use JDBC to operate the database, you first need to introduce the JDBC starter dependency

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

Then introduce the corresponding database-driven dependency, here is MySql as an example

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

When the MySql database driver dependency was introduced, the version number was not specified. You can see that the introduced driver version is 8.0.23

Insert picture description here

This is because Spring Boot's automatic version arbitration mechanism specifies the version number of the driver. You can see that the version number of the MySql database has been specified in the parent dependency of Spring Boot.

Insert picture description here

If you need to modify the version number, you can specify the specific version number when introducing the MySql database driver dependency, or use the following configuration

<properties>
    <mysql.version>5.1.49</mysql.version>
</properties>

Configure data source

First, you need to configure the url of the data source, user name and password, and the driver used

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
    username: root
    password: 12345
    driver-class-name: com.mysql.cj.jdbc.Driver

We can spring.jdbcconfigure the JDBC prefixed configuration, for example, configured to configure the connection timeout for 10 seconds

spring:
  jdbc:
    template:
      query-timeout: 10

Write sql statement

Automatically inject JdbcTemplateclasses, and then use JdbcTemplateclass methods to manipulate the database

Query method usage queryForListmethod, the return value is a list of map collections, each map encapsulates the key-value pairs of each column of a row of data

Add, delete and modify the operation updatemethod, the return value is the number of rows modified, or use the executemethod with no return value

Create the following data table for testing

create table user
(
	id int auto_increment,
	age int null,
	name varchar(10) null,
	address varchar(10) null,
	constraint user_pk primary key (id)
);

Write test cases in the test class

@SpringBootTest
class SpringDemoApplicationTests {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Test
    void contextLoads() {
        int row = jdbcTemplate.update("insert into user (name,age,address) values ('cong',22,'HZ'),('zhang',20,'BJ'),('liu',18,'SZ')");
        System.out.println(row);
        List<Map<String, Object>> users = jdbcTemplate.queryForList("select * from user");
        users.forEach(System.out::println);
    }

}

The result is as follows, the first row shows that 3 rows of data have been inserted, followed by each row of the traversal query result

Insert picture description here

You can also use queryForListthe overload method when querying data , specify the type of return value to query a column

@SpringBootTest
class SpringDemoApplicationTests {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Test
    void contextLoads() {
        List<String> users = jdbcTemplate.queryForList("select name from user",String.class);
        users.forEach(System.out::println);
    }

}
Insert picture description here

When there is only one return value, such as using an aggregate function, you can use the queryForObjectmethod

Add parameters

Pass the parameter into the sql statement, you can use ?placeholders in the sql statement , and then pass the parameter into the overload method of adding, deleting, modifying, and checking

@SpringBootTest
class SpringDemoApplicationTests {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Test
    void contextLoads() {
        Object[] id = {1};
        List<Map<String, Object>> users = jdbcTemplate.queryForList("select * from user where id=?", id);
        users.forEach(System.out::println);
    }

}

This will query the user with id 1

Insert picture description here

Batch operation

Add, delete and modify the data in the database in batches, you can use the batchUpdatemethod

@SpringBootTest
class SpringDemoApplicationTests {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Test
    void contextLoads() {
        jdbcTemplate.batchUpdate("insert into user (name,age,address) values ('Tom',22,'NY')",
                "insert into user (name,age,address) values ('Jim',22,'LA')",
                "insert into user (name,age,address) values ('Jack',22,'SAN')");
    }

}