JdbcTemplate integrated with MySQL in Spring Boot

How to use JdbcTemplate and MySQL for database operations in Spring Boot project

1. Create a simple Spring Boot project.

2. Configure project dependencies, including Spring Boot and MySQL connection driver.

Before using JbdcTemplate, you need to add the JDBC starter dependency

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

Then introduce the Mysql dependency package

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
</dependency>

3. Use JdbcTemplate to create a MySQL data table and insert relevant information.

Create a new package in the resources folder to store sql statements

It has been created here. The following schema.sql is used to create the data table, and data.sql is used to insert data into the data table.

4. Write the Spring Boot configuration file and configure the connection information between JdbcTemplate and MySQL.

First create a database in Mysql

In the project configuration file, configure the connection information of the MySQL database

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/haha?useUnicode=true &characterEncoding=UTF-8
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

  sql:
    init:
      schema-locations: classpath:db/schema.sql
      mode: always
      continue-on-error: true
      data-locations: classpath:db/data.sql

At this time, we tried to run it and found that the database had successfully created a data table named user with some data.

5. Create a DAO class in the project and use JdbcTemplate to complete the addition, deletion, modification and query operations of the database.

First create a user class and add the @Data annotation

@Data
public class User {
    private Integer id;
    private String name;
}

Create a JdbcTemplate object in the DAO class, and use the @Autowired annotation to complete automatic assembly before calling the JdbcTemplate object.

JdbcTemplate template;
@Autowired
public DAO(JdbcTemplate template){
    this.template=template;
}

Next, complete the operations of adding, deleting, checking and modifying the database.

// Get user information based on id
public User getById(Integer id){
    String sql="select * from user where id=?";
    return template.queryForObject(sql
            ,new Object[]{id}
            ,new BeanPropertyRowMapper<>(User.class));
}
//Insert user data
public int add(User entity){
    String sql="insert into user(name) values(?)";
    return template.update(sql,entity.getName());
}
//Modify user data
public int update(User entity){
    String sql="update user set name=? where id=?";
    return template.update(sql,entity.getName(),entity.getId());
}
//Delete user data
public int delete(Integer id){
    String sql="delete from user where id=?";
    return template.update(sql,id);
}

6. Create a Controller class in the project and provide corresponding interfaces to access database operations.

1. Use @RequestMapping mapping

2. Implement addition, deletion, check and modification operations

@RestController
@RequestMapping("/user")
public class Controller {
    DAO dao;
    @Autowired
    public Controller(DAO dao){
        this.dao=dao;
    }
    //User information list
    @RequestMapping("/list")
    public List<User> getAll(){
        return dao.getAll();
    }
    // Get user information based on id
    @RequestMapping("/getById/{userId}")
    public User findById(@PathVariable("userId")Integer id){
        return dao.getById(id);
    }
    //Insert user data
    @RequestMapping("/insert")
    public String insert(){
        User entity=new User();
        entity.setName("Test Username");
        return dao.add(entity)>0?"Added data successfully!":"Fail";
    }
    //Modify user data
    @RequestMapping("/edit")
    public String edit(){
        User entity=new User();
        entity.setId(9);
        entity.setName("Test username-update");
        return dao.update(entity)>0?"Data modified successfully!":"Fail";
    }
    //Delete user data
    @RequestMapping("/delete/{userId}")
    public String delete(@PathVariable("userId") Integer id){
        return dao.delete(id)>0?"Delete data successfully!":"The data cannot be found";
    }
}

Then you can try on the browser to see if additions, deletions, and data changes are completed.

1. Find user information based on ID