Springboot 整合jdbcTemple框架实现增删改查及事务处理——SpringBoot学习

╰半夏微凉° 2022-04-24 01:42 252阅读 0赞

### 一、POM 文件引入 JAR ###

在 pom.xml 中引入 `jdbcTemplate` 以及测试包

<dependency>
    	<groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    
    
    <dependency>
    	<groupId>mysql</groupId>
    	<artifactId>mysql-connector-java</artifactId>
    </dependency>
    
    
    <dependency>
    	<groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-test</artifactId>
    	<scope>test</scope>
    	<exclusions>
    		<exclusion>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-test</artifactId>
    		</exclusion>
    	</exclusions>
    </dependency>
    
    
    <dependency>
    	<groupId>org.springframework</groupId>
    	<artifactId>spring-test</artifactId>
    	<version>5.0.5.RELEASE</version>
    	<scope>test</scope>
    </dependency>

### 二、application.properties 新增配置 ###

在 `application.properties` 中添加数据源的配置

spring.datasource.url=jdbc:mysql://localhost:3306/mysql
    spring.datasource.username=root
    spring.datasource.password=123456
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver

使用 `application.yml` 的配置为

### datasource Configuration
    spring:      
      datasource:
        url: jdbc:mysql://localhost:3306/mysql
        driver-class-name: com.mysql.jdbc.Driver
        username: root
        password: 123456

### 三、添加 Service ###

增加 Service 接口 `src/main/java/com/service/JdbcService.java` 并实现 `src/main/java/com/service/impl/JdbcServiceImpl.java`。  
JdbcService 接口代码

package com.service;
    
    import java.util.List;
    
    import com.bean.User;
    
    /**
    * @Description 整合JDBCTemplate Service
    * @author 欧阳
    * @since 2019年4月11日 下午2:17:37
    * @version V1.0
    */
    
    public interface JdbcService {
    	
    	/**
    	 * 添加用户
    	 * @param user
    	 */
    	public void insertUser(User user);
    	
    	/**
    	 * 更新用户
    	 * @param user
    	 */
    	public void updateUser(User user);
    	
    	/**
    	 * 查询用户
    	 * @param user
    	 * @return
    	 */
    	public List<User> selectUser(User user);
    	
    	/**
    	 * 删除用户
    	 * @param id
    	 */
    	public void deleteUser(Integer id);
    }

JdbcServiceImpl 实现类

package com.service.impl;
    
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.PreparedStatementSetter;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    import com.bean.User;
    import com.service.JdbcService;
    
    /**
    * @Description 整合JDBCTemplate ServiceImpl
    * @author 欧阳
    * @since 2019年4月11日 下午2:20:33
    * @version V1.0
    */
    @Service
    public class JdbcServiceImpl implements JdbcService {
    
    	@Autowired
    	private JdbcTemplate jdbcTemplate;
    
    	@Override
    	@Transactional
    	public void insertUser(User user) {
    		String sql = "insert into t_user(id, name) values(?,?)";
    		jdbcTemplate.update(sql, user.getId(), user.getName());
    //		int num = 1/0;  //放开测试事务。预期结果为添加失败，库中无数据。测试结果与预期一致
    	}
    
    	@Override
    	@Transactional
    	public void updateUser(User user) {
    		String sql = "update t_user set name = ? where id = ?";
    		
    		PreparedStatementSetter pss = new PreparedStatementSetter() {
    			@Override
    			public void setValues(PreparedStatement arg0) throws SQLException {
    				arg0.setString(1, user.getName());
    				arg0.setString(2, user.getId());
    			}
    		};
    		
    		jdbcTemplate.update(sql, pss);
    	}
    
    	@Override
    	public List<User> selectUser(User user) {
    		String sql = "select id,name from t_user where 1=1 ";
    		
    		RowMapper<User> rowMapper = new RowMapper<User>() {
    			
    			@Override
    			public User mapRow(ResultSet arg0, int arg1) throws SQLException {
    				User user = new User();
    				if(arg0.wasNull()) {
    					return user;
    				}
    				user.setId(arg0.getString(1));
    				user.setName(arg0.getString(2));
    				return user;
    			}
    		};
    		Object[] args = null;
    		if(user != null) {
    			if(user.getId() != null) {
    				sql += "and id = ?";
    				args = new Object[1];
    				args[0] = user.getId();
    			}
    		}
    		return jdbcTemplate.query(sql, args, rowMapper);
    	}
    
    	@Override
    	@Transactional
    	public void deleteUser(Integer id) {
    		String sql = "delete from t_user where id = ?";
    		
    		jdbcTemplate.update(sql, String.valueOf(id));
    	}
    
    }

注意：通过使用 `@Transactional` 注解添加事务，在 `insertUser` 方法中的插入数据代码后增加异常代码 `int num = 1/0;` 测试事务是否起作用，实际测试结果表明测试结果与预期一致。

### 四、测试类 ###

添加测试类测试 `JdbcServiceImpl`

package com.service.impl;
    
    import java.util.List;
    
    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 com.App;
    import com.bean.User;
    import com.service.JdbcService;
    
    /**
    * @Description 测试整合  JdbcTemplate 结果
    * @author 欧阳
    * @since 2019年4月11日 下午2:42:53
    * @version V1.0
    */
    
    @RunWith(SpringRunner.class)
    @SpringBootTest(classes={App.class})
    public class JdbcServiceImplTest {
    
    	@Autowired
    	private JdbcService jdbcService;
    	
    	@Test
    	public void testInsertUser() {
    		jdbcService.insertUser(new User("11", "张三"));
    		jdbcService.insertUser(new User("12", "lisi"));
    		System.out.println("Yes");
    	}
    
    	@Test
    	public void testUpdateUser() {
    		User user = new User("11", "zhangsan");
    		jdbcService.updateUser(user);
    		System.out.println("Yes");
    	}
    
    	@Test
    	public void testSelectUser() {
    		List<User> users = jdbcService.selectUser(new User("11", "zhangsan"));
    		for(User user : users) {
    			System.out.println(user);
    		}
    		System.out.println("Yes");
    	}
    
    	@Test
    	public void testDeleteUser() {
    		jdbcService.deleteUser(12);
    		System.out.println("Yes");
    	}
    
    }

### 五、测试结果 ###

测试结果全部通过，增删改查功能正常，springboot 整合 jdbcTemple 完成。  
![测试结果全部通过][20190411155642515.PNG]

[20190411155642515.PNG]: /images/20220221/736ce1d8ae86426dbbf9c5e261a2fdec.png