Spring - Jdbc的使用 和 Spring进行事务控制

1.Spring与JDBC

    我们知道使用JDBC开发特点是 固定代码+动态参数 ;

    场景描述：通过客户id查询客户信息;

    (1) 图解 Spring与JDBC操作

        

    (2) dao实现

        

public interface ClientDao {

	FClient findClientByid(int id) throws Exception;
	
	int insertClient(FClient client) throws Exception;
	
}

   接口实现类 ：注入 dataSource

public class ClientDaoImpl implements ClientDao{


	private DataSource dataSource;
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public FClient findClientByid(int id) throws Exception {
		
		FClient fClient = new FClient();
		
		Connection conn = dataSource.getConnection();
		String sql="select * from f_client where id=?";
		PreparedStatement pstm = conn.prepareStatement(sql);
		pstm.setInt(1,id);
		ResultSet rs = pstm.executeQuery();
		while (rs.next()) {
			fClient.setUsername(rs.getString("username"));
		}
		return fClient;
	}

	
	@Override
	public int insertClient(FClient client) throws Exception {
		// TODO Auto-generated method stub
		return 0;
	}
	
}

   （3）ApplicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans
	xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
		http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-2.5.xsd
        http://www.springframework.org/schema/aop 
		http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
    	http://www.springframework.org/schema/tx 
		http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">

   <context:property-placeholder location="classpath:db.properties"/>


   <bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
      <property name="driverClassName" value="${oracle.driver}"></property>
      <property name="url" value="${oracle.url}"></property>
      <property name="username" value="${oracle.name}"></property>
      <property name="password" value="${oracle.pass}"></property>
   </bean>
   
   <!-- 使用dao测试数据加载 -->
   <bean id="ClientDao" class="cn.labelnet.dao.impl.ClientDaoImpl">
       <property name="dataSource" ref="datasource"></property>
   </bean>
</beans>

   （4）测试

	private ApplicationContext applicationContext;
	
	@Before
	
	public void setUp() throws Exception {
		applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
	}

	@Test
	public void test() throws Exception {
		ClientDao dao=(ClientDao) applicationContext.getBean("ClientDao");
		FClient clientByid = dao.findClientByid(15);
		System.out.println(clientByid);
	}

2.Spring 与 JDBC 使用 SqlTemplate 操作

     （1）图解

    （2）dao实现

public interface ClientDao {

	FClient findClientByid(int id) throws Exception;
	
	int insertClient(FClient client) throws Exception;
	
}

    接口 ：

public class ClientDaoTwo extends JdbcDaoSupport implements ClientDao{
	

	@Override
	public FClient findClientByid(int id) throws Exception {
		
        String sql="select * from f_client where id="+id;
        
		List<FClient> query = this.getJdbcTemplate().query(sql,new ClientRowMapper());
		
		return query.get(0);
	}

	@Override
	public int insertClient(FClient client) throws SQLException{
		
		String sql="insert into yuan_test(id,uname) values("+client.getId()+",'"+client.getUsername()+"')";
		   
		System.out.println(sql);
		
		this.getJdbcTemplate().execute(sql);
     
		return 1;
	}
	

}

   帮助类 ：

     说明： 实现RowMapper接口，相当与在执行ResultSet的循环体中的内容;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import cn.labelnet.po.FClient;

public class ClientRowMapper implements RowMapper<FClient> {

	@Override
	public FClient mapRow(ResultSet rs, int arg1) throws SQLException {
		
		 FClient client=new FClient();
		 client.setId(rs.getInt("id"));
		 client.setUsername(rs.getString("username"));
		 client.setClient_certificate_no(rs.getString("client_certificate_no"));
		return client;
	}

}

   （3）ApplicationContext.xml 配置实现

             注意： 配置数据源 dataSource ,实现是 org.apache.commons.dbcp.BasicDataSource;

                          配置bean-jdbcTemplete，实现是 org.springframework.jdbc.core.JdbcTemplate;

                          配置bean-daoimpl接口实现类 ，继承 jdbcDaoSupport;

<?xml version="1.0" encoding="UTF-8"?>
<beans
	xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
		http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-2.5.xsd
        http://www.springframework.org/schema/aop 
		http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
    	http://www.springframework.org/schema/tx 
		http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">

   <context:property-placeholder location="classpath:db.properties"/>


   <bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
      <property name="driverClassName" value="${oracle.driver}"></property>
      <property name="url" value="${oracle.url}"></property>
      <property name="username" value="${oracle.name}"></property>
      <property name="password" value="${oracle.pass}"></property>
   </bean>
   

   <!-- 使用jdbcTemplete，加载数据 -->
   <bean id="jdbcTemplete" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="datasource"></property>
   </bean>
  
   <!-- 使用JdbcDaoSupport -->  
  <bean id="ClientDaoTwo" class="cn.labelnet.two.ClientDaoTwo">
         <property name="jdbcTemplate" ref="jdbcTemplete"></property>
   </bean>
</beans>

  （4）测试

	private ApplicationContext applicationContext;
	
	@Before
	
	public void setUp() throws Exception {
		applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
	}

	
	@Test
	public void testJdbcDaoSupport() throws Exception {
		ClientDao dao=(ClientDao) applicationContext.getBean("ClientDaoTwo");
		FClient clientByid = dao.findClientByid(15);
		System.out.println(clientByid);
	}

3. Spring声明式事务处理

   （1）Service 实现

         在这里我们继续使用上面的JdbcTemplete方式的dao层，这里实现Service层，来进行Spring进行事务管理;

          Spring声明式事务处理
  声明：针对开发汪，开发汪告诉spring容器，那些方法需要事务和那些不需要事务；
  事务：事务处理（开启，提交，回滚事务）
  目的：让spring管理事务，开发者不再关注事务；

       Service接口实现：

public interface ClientServiceTwo {

	String insertClient(FClient c) throws Exception;
	
}

      Service接口实现 ：

         1.在这里我们使用自定义异常来捕获Service层异常信息（相当与切面）;

         2.我们使用Spring来进行事务处理，而非我们自定义的事务处理;

public class ClientServiceTwoImpl  implements ClientServiceTwo{

	private ClientDao clientdao;
	public void setClientdao(ClientDao clientdao) {
		this.clientdao = clientdao;
	}

	@Override
	public String insertClient(FClient c) throws Exception {
		return clientdao.insertClient(c)>0?"成功":"失败";
	}
	
}

  （2）配置实现

            注意：

               1) 配置dao , 配置 service , 配置 Exception 切面 ;

               2) 配置transactionManager ，使用org.springframework.jdbc.datasource.DataSourceTransactionManager ;

               3) 配置AOP , 包括切入点

<?xml version="1.0" encoding="UTF-8"?>
<beans
	xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
		http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-2.5.xsd
        http://www.springframework.org/schema/aop 
		http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
    	http://www.springframework.org/schema/tx 
		http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">

   <context:property-placeholder location="classpath:db.properties"/>


   <bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
      <property name="driverClassName" value="${oracle.driver}"></property>
      <property name="url" value="${oracle.url}"></property>
      <property name="username" value="${oracle.name}"></property>
      <property name="password" value="${oracle.pass}"></property>
   </bean>
   
   <!-- 使用dao测试数据加载 -->
   <bean id="ClientDao" class="cn.labelnet.dao.impl.ClientDaoImpl">
       <property name="dataSource" ref="datasource"></property>
   </bean>
   

  
  
  <!-- 下面测试spring事务处理 -->
  
  <bean id="clientTwoDao" class="cn.labelnet.two.ClientDaoTwo">
      <property name="jdbcTemplate" ref="jdbcTemplete"></property>
  </bean>
  
  <bean id="clientTwoService" class="cn.labelnet.two.service.ClientServiceTwoImpl">
      <property name="clientdao" ref="clientTwoDao"></property>
  </bean>
  
  <!-- 切面：异常 -->
  <bean id="MyException" class="cn.labelnet.exception.MyException"></bean>
  
  
  
  <!-- 事务 -->
  
  <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
     <property name="dataSource" ref="datasource"></property>
  </bean>
  
  <tx:advice id="tx" transaction-manager="transactionManager">
        <tx:attributes>
			<!--
				name规定方法 isolation 默认值为DEFAULT propagation 传播机制 REQUIRED
			-->
			<tx:method name="insertClient*" read-only="false" />
		</tx:attributes>
  </tx:advice>
  
  <aop:config>
  
     <aop:pointcut expression="execution(* cn.labelnet.two.service.*.*(..))" id="perform"/>
  
     <!-- 配置事務提交 -->
     <aop:advisor advice-ref="tx" pointcut-ref="perform"/>

     <aop:aspect ref="MyException">
        <aop:after-throwing method="printException" throwing="msg" pointcut-ref="perform"/>
     </aop:aspect>
     
  </aop:config>
  
  

</beans>

   （3）测试

	@Test
	public void testJdbcService() throws Exception {
		ClientServiceTwo service=(ClientServiceTwo) applicationContext.getBean("clientTwoService");
		
		FClient fc = new FClient();
		fc.setId(205);
		fc.setUsername("测试用户名");
		
		String result = service.insertClient(fc);
		
		System.out.println(result);
	}

   （4）总结

               采用什么样的方法处理事务，目标方法采用什么样的事务处理策略；
1)搭建环境
2)在spring的配置文件中，导入dataSource
3)测试datasource
4)进行aop的配置：引入事务管理器
5)测试service层的类是否是代理对象

  

4.Demo免积分下载

   http://download.csdn.net/detail/lablenet/9386096