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