springboot整合JdbcTemplate

1.新建一个springboot项目,导入相关依赖

<?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>2.3.3.RELEASE</version> <relativePath /> <!-- lookup parent from repository --> </parent> <groupId>com.shaoming</groupId> <artifactId>springboot-jdbcTemplate</artifactId> <version>0.0.1-SNAPSHOT</version> <name>springboot-jdbcTemplate</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <!-- 整合jdbcTemlate 1.jdbc 2.mysql 驱动 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</artifactId> </dependency> <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.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <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> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>

说明:

主要的两个依赖

​ 1.jdbc ​ 2.mysql 驱动

<dependency> ​ <groupId>org.springframework.boot</groupId> ​ <artifactId>spring-boot-starter-jdbc</artifactId> ​ </dependency> ​ <dependency> ​ <groupId>mysql</groupId> ​ <artifactId>mysql-connector-java</artifactId> ​ <scope>runtime</scope> ​ </dependency>

2.准别需要测试的数据库

-- 创建数据库 create database springboot_leanring charset utf8; -- 创建表 CREATE TABLE `User` ( `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL, `age` int NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

3.配置yml或者是properties

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springboot_leanring?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

说明:

url后面要添加参数,直接写会报错

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springboot_leanring+参数

参数示例里面有

jdbc:mysql://127.0.0.1:3306/springboot_leanring?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true

4.编写entity(属性与数据库列一一对应)

package com.shaoming.entity; import lombok.Data; import lombok.NoArgsConstructor; //使用lombok注解自动生成get/set/toString等方法 @Data @NoArgsConstructor public class User { private String name; private Integer age; }

5.编写service和service实现类

说明:

由于没有业务,方便测试,此demo没有dao层

service接口

package com.shaoming.service; import java.util.List; import com.shaoming.entity.User; public interface UserService { /** * 新增一个用户 * * @param name * @param age */ int create(String name, Integer age); /** * 根据name查询用户 * * @param name * @return */ List<User> getByName(String name); /** * 根据name删除用户 * * @param name */ int deleteByName(String name); /** * 获取用户总量 */ int getAllUsers(); /** * 删除所有用户 */ int deleteAllUsers(); /** * 查询所有用户信息 */ List<User> findAll(); }

service实现类

package com.shaoming.service.impl; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.shaoming.entity.User; import com.shaoming.service.UserService; import java.util.List; @Service public class UserServiceImpl implements UserService { private JdbcTemplate jdbcTemplate; /** 使用构造函数初始化jdbcTemplate */ UserServiceImpl(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override @Transactional public int create(String name, Integer age) { int row = jdbcTemplate.update("insert into USER(NAME, AGE) values(?, ?)", name, age); //测试事务是否生效,也就是@Transactional这个注解是否生效 // int i = 1/0; int a = -1; if(a < 0) { throw new RuntimeException("报错:运行时异常"); } return row; } @Override public List<User> getByName(String name) { List<User> users = jdbcTemplate.query("select NAME, AGE from USER where NAME = ?", (resultSet, i) -> { User user = new User(); user.setName(resultSet.getString("NAME")); user.setAge(resultSet.getInt("AGE")); return user; }, name); return users; } @Override public int deleteByName(String name) { return jdbcTemplate.update("delete from USER where NAME = ?", name); } @Override public int getAllUsers() { return jdbcTemplate.queryForObject("select count(1) from USER", Integer.class); } @Override public int deleteAllUsers() { return jdbcTemplate.update("delete from USER"); } @Override public List<User> findAll() { List<User> users = jdbcTemplate.query("select NAME, AGE from USER", (resultSet, i) -> { User user = new User(); user.setName(resultSet.getString("NAME")); user.setAge(resultSet.getInt("AGE")); return user; }); return users; } }

6.测试方法

package com.shaoming; import static org.junit.Assert.*; import java.util.List; import org.junit.Assert; import org.junit.Before; import org.junit.jupiter.api.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import com.shaoming.entity.User; import com.shaoming.service.UserService; @SpringBootTest @RunWith(SpringRunner.class) public class ApplicationTests { @Autowired private UserService userSerivce; @Before public void setUp() { // 准备，清空user表 userSerivce.deleteAllUsers(); } @Test public void test() throws Exception { // 插入5个用户 userSerivce.create("Tom", 10); userSerivce.create("Mike", 11); userSerivce.create("Didispace", 30); userSerivce.create("Oscar", 21); userSerivce.create("Linda", 17); // 查询名为Oscar的用户，判断年龄是否匹配 List<User> userList = userSerivce.getByName("Oscar"); Assert.assertEquals(21, userList.get(0).getAge().intValue()); // 查数据库，应该有5个用户 Assert.assertEquals(5, userSerivce.getAllUsers()); // 删除两个用户 userSerivce.deleteByName("Tom"); userSerivce.deleteByName("Mike"); // 查数据库，应该有5个用户 Assert.assertEquals(3, userSerivce.getAllUsers()); } /** * 测试查询条数 */ @Test public void testName1() throws Exception { Integer rows = userSerivce.getAllUsers(); System.out.println("影响行数: " + rows); } /** * 测试插入一条数据 */ @Test public void testinsert() throws Exception { int row = userSerivce.create("Linda", 17); System.out.println("影响行数: " + row); } /** * 测试查询所有 */ @Test public void testFindAll() { List<User> userList = userSerivce.findAll(); userList.forEach(System.out::println); } }