spring+jdbc+template+transaction实现
使用spring和jdbc模板事务实现
1、创建实体类:
Role
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.wbg.sjt.entity; public class Role { private int id; private String roleName; private String note; @Override public String toString() { return "Role{" + "id=" + id + ", roleName='" + roleName + '\'' + ", note='" + note + '\'' + '}'; } public Role() { } public Role(int id, String roleName, String note) { this.id = id; this.roleName = roleName; this.note = note; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getRoleName() { return roleName; } public void setRoleName(String roleName) { this.roleName = roleName; } public String getNote() { return note; } public void setNote(String note) { this.note = note; } }
2、创建配置JavaConfig
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.wbg.sjt.config; import com.mchange.v2.c3p0.ComboPooledDataSource; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.ComponentScan; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.ImportResource; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.annotation.EnableTransactionManagement; import org.springframework.transaction.annotation.TransactionManagementConfigurer; import org.springframework.transaction.interceptor.TransactionInterceptor; import org.springframework.transaction.support.TransactionTemplate; import javax.sql.DataSource; import java.beans.PropertyVetoException; @Configuration @ComponentScan("com.wbg.sjt.*") @EnableTransactionManagement public class JavaConfig { @Bean(name = "dataSource") public DataSource getDataSource() { ComboPooledDataSource dataSource = new ComboPooledDataSource(); try { dataSource.setDriverClass("org.mariadb.jdbc.Driver"); } catch (PropertyVetoException e) { e.printStackTrace(); } dataSource.setJdbcUrl("jdbc:mariadb://localhost:3306/wbg_logistics"); dataSource.setUser("root"); dataSource.setPassword("123456"); dataSource.setMaxPoolSize(30); return dataSource; } @Bean public JdbcTemplate jdbcTemplate() { JdbcTemplate jdbcTemplate = new JdbcTemplate(); jdbcTemplate.setDataSource(getDataSource()); return jdbcTemplate; } @Bean public PlatformTransactionManager platformTransactionManager() { DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(); transactionManager.setDataSource(getDataSource()); return transactionManager; } @Bean TransactionTemplate transactionTemplate(PlatformTransactionManager platformTransactionManager){ return new TransactionTemplate(platformTransactionManager); } }
3、创建dao
当出错的时候,事务滚动,数据库数据不变
代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.wbg.sjt.service; import com.wbg.sjt.entity.Role; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcOperations; import org.springframework.jdbc.core.RowMapper; import org.springframework.stereotype.Repository; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.TransactionStatus; import org.springframework.transaction.support.DefaultTransactionDefinition; import org.springframework.transaction.support.TransactionTemplate; import javax.sql.DataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; @Repository public class RoleDao { @Autowired DataSource dataSource; @Autowired private PlatformTransactionManager transactionManager = null; @Autowired private TransactionTemplate transactionTemplate; ; @Autowired private JdbcOperations jdbcOperations; public Role getRole(){ String sql = "select * from role where id = 1"; Role role = jdbcOperations.queryForObject( sql, /* //方式一 new RowMapper<Role>() { @Override public Role mapRow(ResultSet rs, int rowNum) throws SQLException { return new Role(rs.getInt(1),rs.getString(2),rs.getString(3)); } }*/ //方式二: (rs, rowNum) -> new Role(rs.getInt(1),rs.getString(2),rs.getString(3)) ); return role; } public void create() { transactionTemplate.execute(status -> { //让事务出错 String sql = "insert into role(role_name,note) values(?,?)"; String sql2 = "insert into role(role_namess,note) values(?,?)"; jdbcOperations.update(sql, "sql", "aa"); jdbcOperations.update(sql2, "sql", "aa"); return null; }); } //0代码实现 public List<Role> listAll() { List<Role> list = new ArrayList<Role>(); Connection connection = null; try { connection = dataSource.getConnection(); } catch (SQLException e) { e.printStackTrace(); } String sql = "select * from role"; PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); ResultSet resultSet = preparedStatement.executeQuery(); Role role = null; while (resultSet.next()) { role = new Role( resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3) ); list.add(role); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (connection != null) connection.close(); if(preparedStatement != null) preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } public List<Map<String, Object>> getToList() { List<Map<String, Object>> list = jdbcOperations.queryForList("select * from role"); return list; } public Map<String, Object> getToMap() { String sql = "select * from role where id = ?"; Map<String, Object> map = jdbcOperations.queryForMap(sql, 1); return map; } public int insert(Role role) { Connection connection = null; DefaultTransactionDefinition dtd = new DefaultTransactionDefinition(); dtd.setPropagationBehavior(DefaultTransactionDefinition.PROPAGATION_REQUIRED); TransactionStatus ts = transactionManager.getTransaction(dtd); String sql = "insert into role(role_name,note) values(?,?)"; PreparedStatement preparedStatement = null; try { connection = dataSource.getConnection(); preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, role.getRoleName()); preparedStatement.setString(2, role.getNote()); preparedStatement.executeUpdate(); transactionManager.commit(ts); } catch (SQLException e) { transactionManager.rollback(ts); System.out.println("原因:" + e.getMessage()); } return 0; } }
测试:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.wbg; import com.wbg.sjt.config.JavaConfig; import com.wbg.sjt.service.RoleDao; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.AnnotationConfigApplicationContext; import java.util.Map; public class Main { public static void main(String[] args) { ApplicationContext applicationContext = new AnnotationConfigApplicationContext(JavaConfig.class); RoleDao roleDao = applicationContext.getBean(RoleDao.class); System.out.println(roleDao.getRole()); for (Map<String, Object> map : roleDao.getToList()) { System.out.println(map); } roleDao.create(); System.out.println("----------------------"); for (Map<String, Object> map : roleDao.getToList()) { System.out.println(map); } } }