preface

This article is an introduction to SpringBoot using JdbcTemplate operation database, to provide a small Demo for your reference.

There are many ways to manipulate a database, but this article describes using SpringBoot in conjunction with the JdbcTemplate.

New project

Create a new project. Add Jdbc dependency to POM file, complete POM as follows:

<? xml version="1.0" encoding="UTF-8"? > <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> < modelVersion > 4.0.0 < / modelVersion > < groupId > com. Dalaoyang < / groupId > < artifactId > springboot_jdbc < / artifactId > < version > 0.0.1 - the SNAPSHOT < / version > < packaging > jar < / packaging > < name > springboot_jdbc < / name > <description>springboot_jdbc</description> <parent> <groupId>org.springframework.boot</groupId> The < artifactId > spring - the boot - starter - parent < / artifactId > < version > 1.5.9. RELEASE < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> < project. Reporting. OutputEncoding > utf-8 < / project. Reporting. OutputEncoding > < Java version > 1.8 < / Java version > </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId>  <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency>  <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>
Copy the code

The configuration file is as follows:

# # the port number
server.port=8888

Database configuration
# database address
spring.datasource.url=jdbc:mysql://localhost:3306/test? characterEncoding=utf8&useSSL=false
Database user name
spring.datasource.username=root
Database password
spring.datasource.password=123456
## Database driver
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
Copy the code

Create a new entity class User, which implements RowMapper and overrides the mapRow method.

package com.dalaoyang.entity;


import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author dalaoyang
 * @project springboot_learn
 * @package com.dalaoyang.entity
 * @email [email protected]
 * @date 2018/7/25
 */
public class User implements RowMapper<User> {
    private int id;
    private String user_name;
    private String pass_word;

    public User(int id, String user_name, String pass_word) {
        this.id = id;
        this.user_name = user_name;
        this.pass_word = pass_word;
    }

    public User() {
    }

    public User(String user_name, String pass_word) {
        this.user_name = user_name;
        this.pass_word = pass_word;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getPass_word() {
        return pass_word;
    }

    public void setPass_word(String pass_word) {
        this.pass_word = pass_word;
    }

    @Override
    public User mapRow(ResultSet resultSet, int i) throws SQLException {
        User user = new User();
        user.setId(resultSet.getInt("id"));
        user.setUser_name(resultSet.getString("user_name"));
        user.setPass_word(resultSet.getString("pass_word"));
        returnuser; }}Copy the code

Common CURD operations generally use the following three methods:

1. Execute method: directly execute SQL statements

2. The update method is used to add and modify the deletion operation

3. The query method is used to query methods

As usual, this article tests with Controller and injects the JdbcTemplate. The complete code is as follows, and the test method will be introduced below:

package com.dalaoyang.controller; import com.dalaoyang.entity.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import java.util.ArrayList; import java.util.List; import java.util.Map; /** * @author dalaoyang * @project springboot_learn * @package com.dalaoyang.controller * @email [email protected] *  @date 2018/7/25 */ @RestController public class UserController { @Autowired private JdbcTemplate jdbcTemplate; //http://localhost:8888/createTable @GetMapping("createTable")
    public String createTable(){
        String sql = "CREATE TABLE `user` (\n" +
                " `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
                " `user_name` varchar(255) DEFAULT NULL,\n" +
                " `pass_word` varchar(255) DEFAULT NULL,\n" +
                " PRIMARY KEY (`id`)\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; \n" +
                "\n";
        jdbcTemplate.execute(sql);
        return "Create table User successfully";
    }

    //http://localhost:8888/saveUserSql
    @GetMapping("saveUserSql")
    public String saveUserSql(){
        String sql = "INSERT INTO USER (USER_NAME,PASS_WORD) VALUES ('dalaoyang','123')";
        int rows= jdbcTemplate.update(sql);
        return "Successful execution, impact"+rows+"Line"; } //http://localhost:8888/saveUser? userName=lisi&passWord=111 @GetMapping("saveUser")
    public String saveUser(String userName,String passWord){
        int rows= jdbcTemplate.update("INSERT INTO USER (USER_NAME,PASS_WORD) VALUES (? ,?) ",userName,passWord);
        return "Successful execution, impact"+rows+"Line"; } //http://localhost:8888/updateUserPassword? id=1&passWord=111 @GetMapping("updateUserPassword")
    public String updateUserPassword(int id,String passWord){
        int rows= jdbcTemplate.update("UPDATE USER SET PASS_WORD = ? WHERE ID = ?",passWord,id);
        return "Successful execution, impact"+rows+"Line"; } //http://localhost:8888/deleteUserById? id=1 @GetMapping("deleteUserById")
    public String deleteUserById(int id){
        int rows= jdbcTemplate.update("DELETE FROM USER WHERE ID = ?",id);
        return "Successful execution, impact"+rows+"Line";
    }


    //http://localhost:8888/batchSaveUserSql
    @GetMapping("batchSaveUserSql")
    public String batchSaveUserSql(){
        String sql =
                "INSERT INTO USER (USER_NAME,PASS_WORD) VALUES (? ,?) " ;
        List<Object[]> paramList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            String[] arr = new String[2];
            arr[0] = "zhangsan"+i;
            arr[1] = "password"+i;
            paramList.add(arr);
        }
        jdbcTemplate.batchUpdate(sql,paramList);
        return "Executed successfully"; } //http://localhost:8888/getUserByUserName? userName=zhangsan0 @GetMapping("getUserByUserName")
    public List getUserByUserName(String userName){
        String sql = "SELECT * FROM USER WHERE USER_NAME = ?"; // There are many ways to write this. //List<User> List = jdbctemplate.query (SQL,new Object[]{userName}, new BeanRowMapper (user.class)); List<User> list= jdbcTemplate.query(sql,new User(),new Object[]{userName});returnlist; } //http://localhost:8888/getMapById? id=1 @GetMapping("getMapById")
    public Map getMapById(Integer id){
        String sql = "SELECT * FROM USER WHERE ID = ?";
        Map map= jdbcTemplate.queryForMap(sql,id);
        returnmap; } //http://localhost:8888/getUserById? id=1 @GetMapping("getUserById")
    public User getUserById(Integer id){
        String sql = "SELECT * FROM USER WHERE ID = ?";
        User user= jdbcTemplate.queryForObject(sql,new User(),new Object[]{id});
        returnuser; }}Copy the code

Test Method Introduction

1. The createTable method

Create the User table using the execute method

2. SaveUserSql method

Using the UPDATE method, pass in the parameter SQL statement and perform the insert directly

3. SaveUser method

The UPDATE method is used to pass in the SQL statement and the corresponding field values for the insert operation

4. UpdateUserPassword method

Use the UPDATE method to pass in the SQL statement and corresponding field values to modify the data

5. DeleteUserById method

Use the UPDATE method to pass in the SQL statement and corresponding field values to delete the data

6. BatchSaveUserSql method

Using the batchUpdate method, pass in the SQL and parameter collection for batch updates

7. GetUserByUserName method

Use the Query method, pass in the SQL, entity object, query parameters, and here we use the mapRow method overwritten by the entity class

8. GetMapById method

Using the queryForMap method, pass in the SQL and parameters and return the Map

9. GetUserById method

Use queryForObject method, pass in SQL, entity object, query parameter, return User entity class, here also used entity class rewrite mapRow method

The specific method of use and there are many, please refer to the document: docs. Spring. IO/spring/docs…

Pay attention to

Do not worry about the error shown below

This error is caused by the SQL parameter question mark with too many quotation marks, which is also a mistake I made when writing the demo.

Source code download: elder Yang code cloud

Personal website: www.dalaoyang.cn

Follow the author’s official account