JdbcTemplate integrated with MySQL

Here I will use a car brand as an example for the purpose of learning.

Of course, my skills are limited, and there may be some irregularities in code writing and SQL loopholes.

Just take a look at it, don’t comment if you don’t like it

The project structure is as follows

The pom is 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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.1.4</version>
<relativePath/>
</parent>
<groupId>com.jdbc</groupId>
<artifactId>demo_JdbcTemplate</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo_JdbcTemplate</name>
<description>demo_JdbcTemplate</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>

</project>

CarBrand class:

package com.jdbc.entity;

import lombok.Data;

@Data
public class CarBrand {
    privateInteger id;
    private String country;
    private String name;
}

CarBrandDao class:

package com.jdbc.dao;

import com.jdbc.entity.CarBrand;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class CarBrandDao {
    JdbcTemplate template;
    @Autowired
    public CarBrandDao(JdbcTemplate template){
        this.template=template;
    }
    public List<CarBrand> getAll(){
        String sql="select * from CarBrand";
        return template.query(sql,(rs,rowNum)->{
            CarBrand entity=new CarBrand();
            entity.setId(rs.getInt("id"));
            entity.setCountry(rs.getString("country"));
            entity.setName(rs.getString("name"));
            return entity;
        });
    }
    public CarBrand getById(Integer id){
        String sql="select * from CarBrand where id=?";
        return template.queryForObject(sql
                ,new Object[]{id}
                ,new BeanPropertyRowMapper<>(CarBrand.class));
    }
    public List<CarBrand> getByCountry(String country){
        for(int i=0;i<country.length();i + + ){//Prevent sql injection vulnerability, if ' or # is found, return null
            if(country.charAt(i)=='#'||country.charAt(i)=='''){
                return null;
            }
        }
        String sql= "select * from CarBrand where country='" + country + "'";
        return template.query(sql,(rs,rowNum)->{
            CarBrand entity=new CarBrand();
            entity.setId(rs.getInt("id"));
            entity.setCountry(rs.getString("country"));
            entity.setName(rs.getString("name"));
            return entity;
        });
    }
    public int add(CarBrand entity){
        String sql="insert into CarBrand(country,name) values(?,?)";
        return template.update(sql,entity.getCountry(),entity.getName());
    }
    public int updata(CarBrand entity){
        String sql="update CarBrand set name=? where id=?";
        return template.update(sql,entity.getName(),entity.getId());
    }
    public int deleteById(Integer id){
        String sql="delete from CarBrand where id=?";
        return template.update(sql,id);
    }
    public int deleteByCountry(String country){
        String sql="delete from CarBrand where country=?";
        return template.update(sql,country);
    }
}

CarBrandController class:

package com.jdbc.controller;

import com.jdbc.dao.CarBrandDao;
import com.jdbc.entity.CarBrand;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/CarBrand")
public class CarBrandController {
    CarBrandDao dao;
    @Autowired
    public CarBrandController(CarBrandDao dao){
        this.dao=dao;
    }
    @RequestMapping("/list")
    public List<CarBrand> gatAll(){
        return dao.getAll();
   }
   @RequestMapping("/getById/{userId}")
    public CarBrand findById(@PathVariable("userId")Integer id){
        return dao.getById(id);
   }
    @RequestMapping("/getByCountry/{country}")
    public List<CarBrand> findByCountry(@PathVariable("country")String country){
        return dao.getByCountry(country);
    }
   @RequestMapping("/insert")
    public String insert(){
        CarBrand entity=new CarBrand();
        entity.setCountry("Test Country");
        entity.setName("Test");
        return dao.add(entity)>0?"Success":"Fail";
   }
   @RequestMapping("/edit")
   public String edit(){
        CarBrand entity=new CarBrand();
        entity.setId(8);
        entity.setCountry("Update test country");
        entity.setName("Update Test");
        return dao.updata(entity)>0?"Success":"Fail";
   }
    @RequestMapping("/deleteById/{id}")
    public String deleteById(@PathVariable("id")Integer id){
        return dao.deleteById(id)>0?"Success":"Fail";
    }
    @RequestMapping("/deleteByCountry/{country}")
    public String deleteByCountry(@PathVariable("country")String ountry){
        return dao.deleteByCountry(ountry)>0?"Success":"Fail";
    }
}

Let’s use some temporary data here.

Create a database

schema.sql is as follows:

drop table if exists CarBrand;
create table CarBrand(
    id int(12) auto_increment primary key comment 'primary key',
    country varchar(100) comment 'country',
    name varchar(100) comment 'name'
) engine=InnoD

data.sql is as follows:

insert into CarBrand(country,name) values ('country1','brand1');
insert into CarBrand(country,name) values ('Country 1', 'Brand 2');
insert into CarBrand(country,name) values ('Country 1', 'Brand 3');
insert into CarBrand(country,name) values ('Country 2', 'Brand 4');
insert into CarBrand(country,name) values ('Country 2', 'Brand 5');
insert into CarBrand(country,name) values ('Country 2', 'Brand 6');
insert into CarBrand(country,name) values ('Country 3', 'Brand 7');
insert into CarBrand(country,name) values ('country 3','brand 8');

application.yml is as follows:

server:
  port: 8089

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/DataApplication?useUnicode=true &characterEncoding=UTF-8
    username: root
    password: root
    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

Here are the results of the test

Inquire

New

Revise

delete

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Cloud native entry-level skills treeHomepageOverview 16,659 people are learning the system