The background,

It’s been three weeks since the last growth record. Sin, sin.

Without further ado, as a standard add, delete, change and check engineer, then the use of mysql is undoubtedly a top priority.

JdbcTemplate is one of the most basic and low-level implementations of Accessing a database provided by Spring.

Today’s goal is to implement add, delete, change and query using JdbcTemplate.

Two, environment configuration

1. First, make sure you have mysql installed.

2. Create a test database

Select * from user where user = user;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Copy the code

Import the core package

The following will be added to pom.xml

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

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

Copy the code

Configure the properties file and fill in the database information:

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test? useSSL=false&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=123456
Copy the code

Create User entity class

Entity classes correspond to table structures in the database

package com.example.demo.controller;

public class User {

    private int id;
    private String name;
    private int age;

    public User() {
    }

    public User(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' + ", age=" + age + '}'; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; }}Copy the code

Five, add, delete, change and check interface implementation

New MysqlController. Java

package com.example.demo.controller;

import com.alibaba.fastjson.JSONObject;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


@RestController
public class MysqlController {

    @Resource
    private JdbcTemplate jdbcTemplate;


    @RequestMapping("/getAllData")
    public Object getAllData() {
        String sql = "select * from user"; List<User> Users = jdbcTemplate.query(sql, New BeanPropertyRowMapper<User>() {// This uses an anonymous class // native JDBC will store all records queried from the database in the ResultSet, There are currently two main RowMapper implementations, SingleColumnRowMapper and BeanPropertyRowMapper, which correspond to single-column and multi-column data respectively. @Override public User mapRow(ResultSet resultSet, int i) throws SQLException { User User = new User(); User.setId(resultSet.getInt("id"));
                User.setName(resultSet.getString("name"));
                User.setAge(resultSet.getInt("age"));
                returnUser; }});for (User s : Users) {
            System.out.println(s);
        }
        returnUsers; } // Update, insert, delete are all using jdbcTemplate update@requestMapping ("/updateNameById")
    public String updateNameById(@RequestParam(value = "name") String name, Integer id) {
        String sql = "update user set name=? where id=?";
        int flag = jdbcTemplate.update(sql, new Object[]{name, id});
        String returnStr;

        if (flag > 0) {
            returnStr = "Update successful";
        } else {
            returnStr = "Update failed";
        }
        System.out.println(flag);
        return returnStr;
    }

    @RequestMapping("/insertData")
    public String insertData(@RequestParam(value = "name") String name, Integer age) {
        String sql = "insert into user(name,age) values (? ,?) ";
        int flag = jdbcTemplate.update(sql, new Object[]{name, age});
        String returnStr;

        if (flag > 0) {
            returnStr = "Insert successful";
        } else {
            returnStr = "Insert failed";
        }
        System.out.println(flag);
        return returnStr;
    }
    
    @RequestMapping("/deleteData")
    public String deleteData(@RequestParam(value = "id") Integer id) {
        String sql = "delete from user where id=?";
        int flag = jdbcTemplate.update(sql, new Object[]{id});
        String returnStr;

        if (flag > 0) {
            returnStr = "Deleted successfully";
        } else {
            returnStr = "Delete failed";
        }
        System.out.println(flag);
        return returnStr; }}Copy the code

6. Achieving results

Initial situation - > [{http://localhost:8080/getAllData"id": 1,"name":"caohaoyu"."age": 3}, {"id": 3."name":"chy"."age": 20}] ______________________________________________________________________________ A new data, http://localhost:8080/insertData? name=chy2&age=21 [{"id": 1,"name":"caohaoyu"."age": 3}, {"id": 3."name":"chy"."age": 20}, {"id": 5,"name":"chy2"."age": 21}] ______________________________________________________________________________ modify id name for 5, http://localhost:8080/updateNameById? id=5&name=caohaoyu2 [{"id": 1,"name":"caohaoyu"."age": 3}, {"id": 3."name":"chy"."age": 20}, {"id": 5,"name":"caohaoyu2"."age": 21}] ______________________________________________________________________________ delete record with id 1, http://localhost:8080/deleteData? id=1 [{"id": 3."name":"chy"."age": 20}, {"id": 5,"name":"caohaoyu2"."age": 21}]Copy the code

Basic add delete change check complete!

Seven, the introduction of layers

1.Dao layer: Full name Data Access Object. Dao layer compares the bottom layer, is responsible for dealing with the database specifically to a table, an entity to add, delete, change and check.

2.Service layer: also called the Service layer or business layer, encapsulates the operations of the Dao layer and makes a method externally appear as realizing a function.

3.Controller layer: The business control layer is responsible for receiving data and requests and calling the Service layer to implement this business logic.

In the learning process, I learned about the content of the layer, but it was only a simple implementation, so I completed all functions in the Controller layer, and then CARRIED out in-depth practice of the concept of layer.

Eight, feelings

Except that the query operation is fundamentally different (binding the returned data to the entity class), the other thing is to write the SQL and pass in the parameters. After all, it is wrapped, similar to PHP.

Growth record portal:

Output Hello Word!

The realization of the curl