1、Spring 中的 JdbcTemplate
UserDao 中使用 JdbcTemplate
@Repository("userDao")
public class UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public int saveUser(User user) {
return jdbcTemplate.update("insert into user(name,age) values(?,?)", user.getName(), user.getAge());
}
public List<User> list() {
return jdbcTemplate.query("select * from user", new RowMapperImpl());
}
public int deleteUser(Integer userId) {
return jdbcTemplate.update("delete from user where id=?", userId);
}
public User getUserById(Integer id) {
List<User> query = jdbcTemplate.query("select * from user where id = ?", new RowMapperImpl(), id);
if (query == null || query.isEmpty()) {
return null;
}
if (query.size() > 1) {
throw new RuntimeException("查询出来的数据不唯一");
}
return query.get(0);
}
public class RowMapperImpl implements RowMapper<User> {
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user = new User();
user.setAge(resultSet.getInt("age"));
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setMoney(resultSet.getBigDecimal("money"));
return user;
}
}
public void updateUser(User user) {
jdbcTemplate.update("update user set age=?,name=?,money=? where id=?", user.getAge(), user.getName(), user.getMoney(), user.getId());
}
}
2、Spring 的事务控制
2.1、PlatformTransactionManager
此接口是 spring 的事务管理器,它里面提供了我们常用的操作事务的方法,
public interface PlatformTransactionManager extends TransactionManager {
// 获取事务状态信息
TransactionStatus getTransaction(@Nullable TransactionDefinition var1) throws TransactionException;
// 提交事务
void commit(TransactionStatus var1) throws TransactionException;
// 回滚事务
void rollback(TransactionStatus var1) throws TransactionException;
}
2.2、TransactionDefinition
public interface TransactionDefinition {
int PROPAGATION_REQUIRED = 0;
int PROPAGATION_SUPPORTS = 1;
int PROPAGATION_MANDATORY = 2;
int PROPAGATION_REQUIRES_NEW = 3;
int PROPAGATION_NOT_SUPPORTED = 4;
int PROPAGATION_NEVER = 5;
int PROPAGATION_NESTED = 6;
int ISOLATION_DEFAULT = -1;
int ISOLATION_READ_UNCOMMITTED = 1;
int ISOLATION_READ_COMMITTED = 2;
int ISOLATION_REPEATABLE_READ = 4;
int ISOLATION_SERIALIZABLE = 8;
int TIMEOUT_DEFAULT = -1;
default int getPropagationBehavior() {
return 0;
}
default int getIsolationLevel() {
return -1;
}
default int getTimeout() {
return -1;
}
default boolean isReadOnly() {
return false;
}
@Nullable
default String getName() {
return null;
}
static TransactionDefinition withDefaults() {
return StaticTransactionDefinition.INSTANCE;
}
}
2.2.1、事务中提供的方法
- String getName()获取事务对象的名称
- int getIsolationLevel() 获取事务的隔离级别
- int getPropagationBehavior() 获取事务的传播行为
- int getTimeout() 获取事务的超时时间
- boolean isReadOnly() 获取事务是否是只读
2.2.2、事务的隔离级别
- ISOLATION_DEFAULT 默认级别
- ISOLATION_READ_UNCOMMITTED 可以读取未提交数据
- ISOLATION_READ_COMMITTED 只能读取已提交数据,解决脏读问题(oracle默认级别)
- ISOLATION_REPEATABLE_READ 是否读取其他事务提交修改后的数据,解决不可重复读问题(MySQL默认级别)
- ISOLATION_SERIALIZABLE 是否读取其他事务提交添加后的数据,解决幻影读问题
2.2.3、事务的传播行为
- REQUIRED:如果当前没有事务,就新建一个事务,如果已经存在一个事务 中,加入到这个事务 中。一般的选 择(默认值)
- SUPPORTS:支持当前事务,如果当前没有事务,就以非事务方式执行(没有事务)
- MANDATORY:使用当前的事务,如果当前没有事务,就抛出异常
- REQUERS_NEW:新建事务,如果当前在事务中,把当前事务挂起
- NOT_SUPPORTED:以非事务方式执行操作,如果当前存在事务,就把当前事务挂起
- NEVER:以非事务方式运行,如果当前存在事务,抛出异常
- NESTED:如果当前存在事务,则在嵌套事务内执行。如果当前没有事务,则执行 REQUIRED 类似的操作。
2.3、使用Spring事务
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.wmding.spring</groupId>
<artifactId>com.wmding.spring</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>RELEASE</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
</dependencies>
</project>
@Configuration
@ComponentScan(value = "com.wmding.spring")
@Import(value = {JdbcConfig.class,TransactionConfig.class})
@EnableTransactionManagement
public class SpringConfig {
}
@PropertySource(value = "classpath:jdbc.properties")
public class JdbcConfig {
@Value("${jdbc.driver}")
private String driver;
@Value("${jdbc.url}")
private String url;
@Value("${jdbc.username}")
private String username;
@Value("${jdbc.password}")
private String password;
@Bean("jdbcTemplate")
public JdbcTemplate jdbcTemplate(DataSource dataSource){
return new JdbcTemplate(dataSource);
}
@Bean("dataSource")
public DataSource createDataSource(){
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
return dataSource;
}
}
public class TransactionConfig {
@Bean("transactionManager")
public PlatformTransactionManager dataSourceTransationManager(DataSource dateSource){
return new DataSourceTransactionManager(dateSource);
}
}
@Repository("userDao")
public class UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public int saveUser(User user) {
return jdbcTemplate.update("insert into user(name,age) values(?,?)", user.getName(), user.getAge());
}
public List<User> list() {
return jdbcTemplate.query("select * from user", new RowMapperImpl());
}
public int deleteUser(Integer userId) {
return jdbcTemplate.update("delete from user where id=?", userId);
}
public User getUserById(Integer id) {
List<User> query = jdbcTemplate.query("select * from user where id = ?", new RowMapperImpl(), id);
if (query == null || query.isEmpty()) {
return null;
}
if (query.size() > 1) {
throw new RuntimeException("查询出来的数据不唯一");
}
return query.get(0);
}
public class RowMapperImpl implements RowMapper<User> {
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user = new User();
user.setAge(resultSet.getInt("age"));
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setMoney(resultSet.getBigDecimal("money"));
return user;
}
}
public void updateUser(User user) {
jdbcTemplate.update("update user set age=?,name=?,money=? where id=?", user.getAge(), user.getName(), user.getMoney(), user.getId());
}
}
@Service("userService")
@Transactional(readOnly = true,propagation = Propagation.SUPPORTS)
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
public int saveUser(User user) {
return userDao.saveUser(user);
}
public List<User> list() {
return userDao.list();
}
public int deleteUser(Integer userId) {
return userDao.deleteUser(userId);
}
public User getUserById(Integer id) {
return userDao.getUserById(id);
}
public void updateUser(User user) {
userDao.updateUser(user);
}
@Transactional(readOnly = false,propagation = Propagation.REQUIRED)
public void transfer(Integer sourceId, Integer targetId, BigDecimal bigDecimal) {
User sourceUser = userDao.getUserById(sourceId);
User targetUser = userDao.getUserById(targetId);
sourceUser.setMoney(sourceUser.getMoney().subtract(bigDecimal));
userDao.updateUser(sourceUser);
targetUser.setMoney(targetUser.getMoney().add(bigDecimal));
userDao.updateUser(targetUser);
}
}
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = SpringConfig.class)
public class UserTest {
@Autowired
private UserService userService;
@Test
public void saveUser() {
User user = new User();
user.setAge(20);
user.setName("wmding");
user.setMoney(new BigDecimal(10000));
int i = userService.saveUser(user);
if (i > 0) {
System.out.println("saveUser success");
}
}
@Test
public void getUserById() {
User userById = userService.getUserById(1);
System.out.println(userById.toString());
}
@Test
public void transfer() {
userService.transfer(1, 2, new BigDecimal(100));
}
}