SpringJDBC

Spring+DB项目框架

JDBC

1.JDBC的jar包

<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>5.3.15</version>
	<!--默认作用域 compile-->
</dependency>

2.测试与数据库的连接

@Configuration
@ComponentScan
public class AppConfig {
    public static void main(String[] args) throws SQLException {
        ApplicationContext ac = new AnnotationConfigApplicationContext(AppConfig.class);
       
        DataSource ds = (DataSource) ac.getBean("dataSource");
        Connection con = ds.getConnection();
        System.out.println(con);
    }
    
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
        dataSource.setUrl("jdbc:oracle:thin:@//localhost:1521/orcl");
        dataSource.setUsername("scott");
        dataSource.setPassword("a");
        return dataSource;
    }
}

3.注入JdbcTemplate

public JdbcTemplate jdbcTemplate;
@Autowired
public void init(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
}

4.增删改查操作

• 增

@Override
public void addOpRecord(OpRecord opRecord) {
    this.jdbcTemplate.update("insert into oprecord values(seq_oprecord.nextval,?,?,sysdate)", opRecord.getAccountid(), opRecord.getOpmoney());
}
----------------------------------------------
@Override
public Integer addAccount(double money) {
    String sql = "insert into account values (seq_account.nextval, ?)";

    KeyHolder keyHolder = new GeneratedKeyHolder();

    jdbcTemplate.update(con -> {
        PreparedStatement pstmt = con.prepareStatement(sql, new String[]{"accountid"});
        pstmt.setObject(1, String.valueOf(money));
        return pstmt;
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

• 删

this.jdbcTemplate.update( "delete from t_actor where id = ?",actorId);

• 改

@Override
public int updateAccount(Account account) {
    int result = jdbcTemplate.update("update account set balance=balance+? where accountid=?", account.getBalance(), account.getAccountid());
    return result;
}

• 查

1.查寻出一条结果，并将结果保存到表对应类中
	@Override
	public Account findAccount(Integer accountid) {
	    Account account = this.jdbcTemplate.queryForObject("select * from account where accountid=" + accountid, (rs, rowNum) -> {
	        Account newAccount = new Account();
	        newAccount.setAccountid(rs.getInt(1));
	        newAccount.setBalance(rs.getDouble(2));
	        return newAccount;
	    });
	    return (Account) account;
	}
------------------------------------------------
2.查寻出多条结果，并将结果保存到表对应类中
	public List<OpRecord> findOpRecord(Integer accountid) {
	   List<OpRecord> list = this.jdbcTemplate.query("select * from oprecord where accountid=" + accountid, (rs, rowNum) -> {
	       //将查询返回的结果设置到OpRecord类中
	       OpRecord opRecord = new OpRecord();
	       opRecord.setId(rs.getInt(1));
	       opRecord.setAccountid(rs.getInt(2));
	       opRecord.setOpmoney(rs.getDouble(3));
	       opRecord.setOptime(rs.getString(4));
	       return opRecord;
	   });
	   return list;
	}

增加事务管理

• setAutoCommit(false)

1.导jar包

• 声明式事务管理器：AOP(spring同样支持编程式事务管理)

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
</dependency>

2.IOC到Spring

• 有数据源DataSource才有事务管理器DataSourceTransactionManager

@Bean  //不同DataSource有不同事务管理器
public DataSourceTransactionManager jdbcTransactionManager(DataSource dataSource) {
   DataSourceTransactionManager jdbcTransactionManager = new DataSourceTransactionManager(dataSource);
   return jdbcTransactionManager;
}

3.在需要事务管理的类加事务管理注解

@Transactional(transactionManager = "jdbcTransactionManager")//事务管理器

1.类和类中某方法上都可以有，类上对所有方法有效，方法上优先readOnly=true
2.@Transactional什么都不写的话会使用默认配置，看源码就懂了
3.@Transactional(isolation = Isolation.xxx)
	isolation =底层是怎么做的？？？修改数据库的绘画隔离级别
4.参数：传播机制+隔离级别+超时时间+允许回滚的异常(注意子类 异常的侵入性问题，到处都是try-catch)

4.在配置类加注解

@EnableTransactionManagement//事务管理器

数据库事务管理

mybatis plus