版本:springboot.2.1.6.RELEASE
1、添加依赖:
pom.xml:
<!-- jdbc-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 数据库连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<!-- mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>5.1.27</version>
</dependency>
完整依赖pom.xml:
<?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.1.6.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>cn.linst</groupId>
<artifactId>jdbctemplate</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>jdbctemplate</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>5.1.27</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2、创建一个User bean
User:
package cn.linst.jdbctemplate.bean;
public class User {
private Integer id;
private String username;
private String address;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", address='" + address + '\'' +
'}';
}
// getter, setter
}
3、创建一个service
UserService:
package cn.linst.jdbctemplate.service;
import cn.linst.jdbctemplate.bean.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@Service
public class UserService {
@Autowired
JdbcTemplate jdbcTemplate;
public Integer addUser(User user) {
return jdbcTemplate.update("insert into user (username,address) values (?,?);", user.getUsername(), user.getAddress());
}
public List<User> getAllUsers() {
return jdbcTemplate.query("select * from user", new RowMapper<User>() {
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user = new User();
int id = resultSet.getInt("id");
String username = resultSet.getString("username");
String address = resultSet.getString("address");
user.setUsername(username);
user.setId(id);
user.setAddress(address);
return user;
}
});
}
public Integer updateUsernameById(User user) {
return jdbcTemplate.update("update user set username = ? where id=?;", user.getUsername(), user.getId());
}
public Integer deleteUserById(Integer id) {
return jdbcTemplate.update("delete from user where id=?", id);
}
public List<User> getAllUsers2() {
return jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
}
}
4、application.properties
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql:///jdbctemplate?useUnicode=true&characterEncoding=UTF-8
5、创建一个test类
package cn.linst.jdbctemplate;
import cn.linst.jdbctemplate.bean.User;
import cn.linst.jdbctemplate.service.UserService;
import org.junit.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 java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class JdbctemplateApplicationTests {
@Autowired
UserService userService;
// 添加
@Test
public void contextLoads() {
User user = new User();
user.setUsername("wangwu");
user.setAddress("xxxxxx");
userService.addUser(user);
}
// 修改
@Test
public void test1() {
User user = new User();
user.setId(1);
user.setUsername("zhangsan");
userService.updateUsernameById(user);
}
// 删除
@Test
public void test2() {
userService.deleteUserById(1);
}
// 查询
@Test
public void test3() {
List<User> allUsers = userService.getAllUsers();
System.out.println(allUsers);
}
//查询
@Test
public void test4() {
List<User> allUsers = userService.getAllUsers2();
System.out.println(allUsers);
}
}
6、数据表结构
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
7、源码分析
在org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration
类,部分源码如下:
@Configuration
// 可以看到,当当前类路径下存在DataSource和JdbcTemplate时,该类就会被自动配置。
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(JdbcProperties.class)
public class JdbcTemplateAutoConfiguration {
@Configuration
static class JdbcTemplateConfiguration {
private final DataSource dataSource;
private final JdbcProperties properties;
JdbcTemplateConfiguration(DataSource dataSource, JdbcProperties properties) {
this.dataSource = dataSource;
this.properties = properties;
}
// jdbcTemplate方法则表示,如果没有提供一个JdbcOperations的实例的话,系统就自动配置一个默认的JdbcTemplate Bean。
// JdbcTemplate是JdbcOperations接口的一个实现。
@Bean
@Primary
@ConditionalOnMissingBean(JdbcOperations.class)
public JdbcTemplate jdbcTemplate() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
JdbcProperties.Template template = this.properties.getTemplate();
jdbcTemplate.setFetchSize(template.getFetchSize());
jdbcTemplate.setMaxRows(template.getMaxRows());
if (template.getQueryTimeout() != null) {
jdbcTemplate
.setQueryTimeout((int) template.getQueryTimeout().getSeconds());
}
return jdbcTemplate;
}
}
@Configuration
@Import(JdbcTemplateConfiguration.class)
static class NamedParameterJdbcTemplateConfiguration {
@Bean
@Primary
@ConditionalOnSingleCandidate(JdbcTemplate.class)
@ConditionalOnMissingBean(NamedParameterJdbcOperations.class)
public NamedParameterJdbcTemplate namedParameterJdbcTemplate(
JdbcTemplate jdbcTemplate) {
return new NamedParameterJdbcTemplate(jdbcTemplate);
}
}
}