一、JdbcTemplate环境搭建
Spring JDBC Template 是Spring 操作数据库的模块, 基于 JDBC API
在数据库中创建表porsche
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for porsche -- ---------------------------- DROP TABLE IF EXISTS `porsche`; CREATE TABLE `porsche` ( `porsche_id` int(11) NOT NULL AUTO_INCREMENT, `name` char(100) DEFAULT NULL, `price` double DEFAULT NULL, PRIMARY KEY (`porsche_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gb2312; -- ---------------------------- -- Records of porsche -- ---------------------------- BEGIN; INSERT INTO `porsche` VALUES (1, 'Panamera', 970000); INSERT INTO `porsche` VALUES (2, 'Cayenne', 910000); INSERT INTO `porsche` VALUES (3, 'Macan', 550000); INSERT INTO `porsche` VALUES (4, 'Taycan', 880000); INSERT INTO `porsche` VALUES (5, 'Porsche 911', 1270000); INSERT INTO `porsche` VALUES (6, 'Porsche 718', 540000); COMMIT; SET FOREIGN_KEY_CHECKS = 1; 复制代码
创建一个maven项目spring-jdbc-template,导入spring，mysql，druid依赖
<properties> <spring-version>5.3.13</spring-version> </properties> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>${spring-version}</version> </dependency> <!--spring jdbc依赖--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.16</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.14</version> </dependency> </dependencies> 复制代码
resources目录下创建数据库信息配置文件datasource.properties
driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true username=root password=root initialSize=5 maxActive=20 复制代码
创建application.xml，配置数据库连接池及Spring JDBC Template
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:util="http://www.springframework.org/schema/util" xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.3.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd"> <!--引用外部配置文件--> <context:property-placeholder location="classpath:database.properties"></context:property-placeholder> <!--数据库连接池配置--> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="driverClassName" value="${driverClassName}"/> <property name="url" value="${url}" /> <property name="username" value="${username}"/> <property name="password" value="${password}"/> <property name="initialSize" value="${initialSize}"/> <property name="maxActive" value="${maxActive}"/> </bean> <!--配置JDBC Template,注入Spring容器中--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg name="dataSource" ref="dataSource"></constructor-arg> </bean> </beans> 复制代码
在test包下创建ApplicationTest，使用Spring Test测试IoC容器中数据源及JDBC Template
@RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration("classpath:application.xml") public class ApplicationTest { @Autowired private DruidDataSource dataSource; @Autowired private JdbcTemplate jdbcTemplate; @Test public void testDataSource() throws SQLException { System.out.println(dataSource); Connection connection = dataSource.getConnection(); System.out.println(connection); } @Test public void testJdbcTemplate(){ System.out.println(jdbcTemplate); } } 复制代码
执行测试类
容器中成功创建了dataSource数据源及jdbcTemplate
二、JdbcTemplate实现增删改查
新建一个测试类PorscheTest继承ApplicationTest
public class PorscheTest extends ApplicationTest { @Autowired private JdbcTemplate jdbcTemplate; } 复制代码
更新porsche_id=4的name为Taycan 2022
增加测试方法
@Test public void testUpdateName(){ String updateNameSql = "UPDATE porsche SET name=? WHERE porsche_id=?"; int count = jdbcTemplate.update(updateNameSql, "Taycay 2022", 4); System.out.println("Affected rows:" + count); } 复制代码
执行该方法，影响了一行，更新成功
