1. JdbcTemplate的使用及Dao的两种编写方式

1.1 注解式

	<context:component-scan base-package="com.java"></context:component-scan><bean id="accountDao" class="com.java.dao.impl.AccountDaoImpl2"></bean><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property></bean><bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"><property name="driverClassName" value="com.mysql.jdbc.Driver"></property><property name="url" value="jdbc:mysql://localhost:3306/eesy"></property><property name="username" value="root"></property><property name="password" value="root"></property></bean>

@Repository
public class AccountDaoImpl2 implements IAccountDao {@Autowiredprivate JdbcTemplate jdbcTemplate;@Overridepublic Account findAccountById(Integer accountId) {List<Account> accounts = jdbcTemplate.query("select * from account where id = ? ",new BeanPropertyRowMapper<Account>(Account.class),accountId);return accounts.isEmpty()? null : accounts.get(0);}

1.2 继承JdbcDaoSupport

	<bean id="accountDao" class="com.java.dao.impl.AccountDaoImpl"><property name="dataSource" ref="dataSource"></property></bean><bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"><property name="driverClassName" value="com.mysql.jdbc.Driver"></property><property name="url" value="jdbc:mysql://localhost:3306/eesy"></property><property name="username" value="root"></property><property name="password" value="root"></property></bean>

public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {@Overridepublic Account findAccountById(Integer accountId) {List<Account> accounts = getJdbcTemplate().query("select * from account where id = ? ",new BeanPropertyRowMapper<Account>(Account.class),accountId);return accounts.isEmpty()? null : accounts.get(0);}

JdbcDaoSupport源码的简单实现（spring框架是已经写好了的不需要自己实现）

public class JdbcDaoSupport {private JdbcTemplate jdbcTemplate;public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}public JdbcTemplate getJdbcTemplate() {return jdbcTemplate;}public void setDataSource(DataSource dataSource) {if (jdbcTemplate == null){jdbcTemplate = creatJdbcTemplate(dataSource);}}private JdbcTemplate creatJdbcTemplate(DataSource dataSource){return new JdbcTemplate(dataSource);}
}

2. AOP事务控制

2.1 基于XML的AOP实现事务控制

<!--配置Dao对象--><bean id="accountDao" class="com.itheima.dao.impl.AccountDaoImpl"><!-- 注入QueryRunner --><property name="runner" ref="runner"></property><property name="connectionUtils" ref="connectionUtils"></property></bean><!--配置QueryRunner--><bean id="runner" class="org.apache.commons.dbutils.QueryRunner" scope="prototype"></bean><!-- 配置数据源 --><bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"><!--连接数据库的必备信息--><property name="driverClass" value="com.mysql.jdbc.Driver"></property><property name="jdbcUrl" value="jdbc:mysql://localhost:3306/eesy"></property><property name="user" value="root"></property><property name="password" value="root"></property></bean><bean id="connectionUtils" class="com.itheima.utils.ConnectionUtils"><property name="dataSource" ref="dataSource"></property></bean><bean id="txManager" class="com.itheima.utils.TranscationManager"><property name="connectionUtils" ref="connectionUtils"></property></bean><aop:config><aop:pointcut id="pt1" expression="execution(* com.itheima.service.impl.*.*(..))"></aop:pointcut><aop:aspect id="txAdvice" ref="txManager"><aop:before method="beginTransaction" pointcut-ref="pt1"></aop:before><aop:after-returning method="commit" pointcut-ref="pt1"></aop:after-returning><aop:after-throwing method="rollback" pointcut-ref="pt1"></aop:after-throwing><aop:after method="release" pointcut-ref="pt1"></aop:after></aop:aspect></aop:config>

Dao层

public class AccountDaoImpl implements IAccountDao {private QueryRunner runner;private ConnectionUtils connectionUtils;public void setRunner(QueryRunner runner) {this.runner = runner;}public void setConnectionUtils(ConnectionUtils connectionUtils) {this.connectionUtils = connectionUtils;}@Overridepublic Account findAccountById(Integer accountId) {try{return runner.query(connectionUtils.getThreadConnection(),"select * from account where id = ? ",new BeanHandler<Account>(Account.class),accountId);}catch (Exception e) {throw new RuntimeException(e);}}

Service层

public class AccountServiceImpl implements IAccountService{private IAccountDao accountDao;public void setAccountDao(IAccountDao accountDao) {this.accountDao = accountDao;}@Overridepublic void transfer(String sourceName, String targetName, Float money) {System.out.println("transfer....");//2.1根据名称查询转出账户Account source = accountDao.findAccountByName(sourceName);//2.2根据名称查询转入账户Account target = accountDao.findAccountByName(targetName);//2.3转出账户减钱source.setMoney(source.getMoney()-money);int i=1/0;//2.4转入账户加钱target.setMoney(target.getMoney()+money);//2.5更新转出账户accountDao.updateAccount(source);//2.6更新转入账户accountDao.updateAccount(target);}

连接与线程的绑定和解绑

public class ConnectionUtils {private ThreadLocal<Connection> tl = new ThreadLocal<>();private DataSource dataSource;public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;}public Connection getThreadConnection(){try {Connection conn = tl.get();if (conn == null){conn = dataSource.getConnection();tl.set(conn);}return conn;}catch (Exception e){throw new RuntimeException();}}public void removeConnection(){tl.remove();}
}

事务控制类

public class TranscationManager {private ConnectionUtils connectionUtils;public void setConnectionUtils(ConnectionUtils connectionUtils) {this.connectionUtils = connectionUtils;}/*** 开启事务*/public  void beginTransaction(){try {connectionUtils.getThreadConnection().setAutoCommit(false);}catch (Exception e){e.printStackTrace();}}/*** 提交事务*/public  void commit(){try {connectionUtils.getThreadConnection().commit();}catch (Exception e){e.printStackTrace();}}/*** 回滚事务*/public  void rollback(){try {connectionUtils.getThreadConnection().rollback();}catch (Exception e){e.printStackTrace();}}/*** 释放连接*/public  void release(){try {connectionUtils.getThreadConnection().close();//还回连接池中connectionUtils.removeConnection();}catch (Exception e){e.printStackTrace();}}
}

2.2 基于注解的AOP实现事务控制

用注解配置时，通知的执行顺序会出问题，spring（五）有提到，所以一般用环绕通知实现注解AOP

	<!--扫描包--><context:component-scan base-package="com.itheima"></context:component-scan>	<!--配置QueryRunner--><bean id="runner" class="org.apache.commons.dbutils.QueryRunner" scope="prototype"></bean><!-- 配置数据源 --><bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"><!--连接数据库的必备信息--><property name="driverClass" value="com.mysql.jdbc.Driver"></property><property name="jdbcUrl" value="jdbc:mysql://localhost:3306/eesy"></property><property name="user" value="root"></property><property name="password" value="root"></property></bean><!--开启spring注解AOP的支持--><aop:aspectj-autoproxy></aop:aspectj-autoproxy>

@Component("txManager")
@Aspect
public class TranscationManager {@Autowiredprivate ConnectionUtils connectionUtils;@Pointcut("execution(* com.itheima.service.impl.*.*(..))")private void pt1(){}/*** 开启事务*/
//    @Before("pt1()")public  void beginTransaction(){try {connectionUtils.getThreadConnection().setAutoCommit(false);}catch (Exception e){e.printStackTrace();}}/*** 提交事务*/
//    @AfterReturning("pt1()")public  void commit(){try {connectionUtils.getThreadConnection().commit();}catch (Exception e){e.printStackTrace();}}/*** 回滚事务*/
//    @AfterThrowing("pt1()")public  void rollback(){try {connectionUtils.getThreadConnection().rollback();}catch (Exception e){e.printStackTrace();}}/*** 释放连接*/
//    @After("pt1()")public  void release(){try {connectionUtils.getThreadConnection().close();//还回连接池中connectionUtils.removeConnection();}catch (Exception e){e.printStackTrace();}}@Around("pt1()")public Object arroundAdvice(ProceedingJoinPoint proceedingJoinPoint){Object rtValue = null;try {Object[] args = proceedingJoinPoint.getArgs();this.beginTransaction();rtValue = proceedingJoinPoint.proceed(args);this.commit();return rtValue;} catch (Throwable throwable) {this.rollback();throw new RuntimeException(throwable);}finally {this.release();}}