大家帮我看下,太纠结了。数据库是oracle 10g 设置了回滚事务,就是不回滚,每次都插入了数据。 <!-- spring2.0的配置方式 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>
<!-- 事务通知 -->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<!-- <tx:method name="add*" propagation="REQUIRED" /> -->
<tx:method name="mod*" propagation="REQUIRED" />
<tx:method name="*" read-only="true"
rollback-for="Exception,RuntimeException,SQLException"
propagation="REQUIRED" />
</tx:attributes>
</tx:advice>
<!-- Spring AOP config -->
<aop:config proxy-target-class="true">
<!-- 切入点 -->
<aop:pointcut id="servicesPointcut"
expression="execution(* com.zyujie.service..*Impl.*(..))" />
<!-- <aop:pointcut id="daoPointcut"
expression="execution(* com.zyujie.dao..*.*(..))" /> -->
<!-- 运行拦截 -->
<aop:advisor advice-ref="txAdvice"
pointcut-ref="servicesPointcut" />
<!-- <aop:advisor advice-ref="txAdvice" pointcut-ref="daoPointcut" /> -->
</aop:config>
/*
* 添加用户,为了测试是否开启事务。DAO层的方法
*/
public int addUser(SysUser user) {
logger.debug("开始执行添加用户的操作......");
Connection con = ConnectionFactory.getConnection();
String sql = "INSERT INTO USER_INFO VALUES (?,?,?,?,?,?,?,?,sysdate)";
int result = 0;
PreparedStatement ps = null;
try {
// con.setAutoCommit(false);
ps = con.prepareStatement(sql);
for (int i = 0; i < 1000; i++) {
String temps = i + "user";
if (i == 600) {
temps = null;
}
ps.setString(1, temps);
ps.setString(2, "test");
ps.setString(3, "99");
ps.setString(4, "测试地市");
ps.setString(5, "888");
ps.setString(6, "测试部门");
ps.setString(7, "888888");
ps.setString(8, "2");
// ps.setString(9, "");
result += ps.executeUpdate(); //有人说是这里本身就已经提交了,但是还是问问大家。
}
// con.commit();
} catch (Exception e) {
logger.debug("添加用户事务回滚了......");
// throw new RuntimeException("error"); // 抛出异常,测试回滚
throw new RuntimeException(); // 抛出异常,测试回滚
} finally {
try {
if (ps != null) {
ps.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
// e.printStackTrace();
// throw new RuntimeException(); // 抛出异常,测试回滚
}
}
return result;
}
/*
* 添加用户,为了测试是否开启事务。Service层的接口实现类,方法
*/
public int addUser(SysUser user) {
return sysUserDAO.addUser(user);
}
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>
<!-- 事务通知 -->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<!-- <tx:method name="add*" propagation="REQUIRED" /> -->
<tx:method name="mod*" propagation="REQUIRED" />
<tx:method name="*" read-only="true"
rollback-for="Exception,RuntimeException,SQLException"
propagation="REQUIRED" />
</tx:attributes>
</tx:advice>
<!-- Spring AOP config -->
<aop:config proxy-target-class="true">
<!-- 切入点 -->
<aop:pointcut id="servicesPointcut"
expression="execution(* com.zyujie.service..*Impl.*(..))" />
<!-- <aop:pointcut id="daoPointcut"
expression="execution(* com.zyujie.dao..*.*(..))" /> -->
<!-- 运行拦截 -->
<aop:advisor advice-ref="txAdvice"
pointcut-ref="servicesPointcut" />
<!-- <aop:advisor advice-ref="txAdvice" pointcut-ref="daoPointcut" /> -->
</aop:config>
/*
* 添加用户,为了测试是否开启事务。DAO层的方法
*/
public int addUser(SysUser user) {
logger.debug("开始执行添加用户的操作......");
Connection con = ConnectionFactory.getConnection();
String sql = "INSERT INTO USER_INFO VALUES (?,?,?,?,?,?,?,?,sysdate)";
int result = 0;
PreparedStatement ps = null;
try {
// con.setAutoCommit(false);
ps = con.prepareStatement(sql);
for (int i = 0; i < 1000; i++) {
String temps = i + "user";
if (i == 600) {
temps = null;
}
ps.setString(1, temps);
ps.setString(2, "test");
ps.setString(3, "99");
ps.setString(4, "测试地市");
ps.setString(5, "888");
ps.setString(6, "测试部门");
ps.setString(7, "888888");
ps.setString(8, "2");
// ps.setString(9, "");
result += ps.executeUpdate(); //有人说是这里本身就已经提交了,但是还是问问大家。
}
// con.commit();
} catch (Exception e) {
logger.debug("添加用户事务回滚了......");
// throw new RuntimeException("error"); // 抛出异常,测试回滚
throw new RuntimeException(); // 抛出异常,测试回滚
} finally {
try {
if (ps != null) {
ps.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
// e.printStackTrace();
// throw new RuntimeException(); // 抛出异常,测试回滚
}
}
return result;
}
/*
* 添加用户,为了测试是否开启事务。Service层的接口实现类,方法
*/
public int addUser(SysUser user) {
return sysUserDAO.addUser(user);
}
这里是不是写错了,, <tx:method name="add*" propagation="REQUIRED" /> 试试..
rollback-for="Exception,RuntimeException,SQLException"
propagation="REQUIRED" />
throw new RuntimeException(); // 抛出异常,测试回滚
执行没有呢?你在
result += ps.executeUpdate(); //有人说是这里本身就已经提交了,但是还是问问大家。
后加上
throw new RuntimeException(); // 抛出异常,测试回滚
测试下;
我测试过,如果写在result += ps.executeUpdate();之后的话,会有一条数据插入,其它不会插入。
throw new RuntimeException(); 会执行的。
我的ConnectionFactory 是从spring的配置文件,dataSource中获得连接。 /*
* 获取系统的数据源
*/
public static DataSource getDataSource() {
DataSource dataSource = null;
try {
dataSource = (DataSource) ContextHelper.getContext().getBean(
"dataSource");
} catch (Exception e) {
logger.error("获取数据源出错,请检查Spring数据源配置!");
}
return dataSource;
} /*
* 获取数据库连接
*/
public static synchronized Connection getConnection() {
Connection conn = null;
try {
conn = getDataSource().getConnection();
logger.info("连接获取成功!");
} catch (SQLException e) {
logger.error("通过数据源获取数据库连接发生异常!");
e.printStackTrace();
}
return conn;
}而我的dataSource是配置在了spring中 <!-- 配置数据源,读取jdbc.properties配置文件 -->
<bean id="dataSource"
class="org.logicalcobwebs.proxool.ProxoolDataSource">
<property name="driver" value="${jdbc.driver}" />
<property name="driverUrl" value="${jdbc.url}" />
<property name="user" value="${jdbc.user}" />
<property name="password" value="${jdbc.password}" />
<property name="alias" value="${jdbc.alias}" />
<property name="simultaneousBuildThrottle"
value="${jdbc.simultaneousBuildThrottle}" />
<property name="maximumActiveTime"
value="${jdbc.maximumActiveTime}" />
<property name="houseKeepingTestSql"
value="${jdbc.houseKeepingTestSql}" />
<property name="maximumConnectionCount"
value="${jdbc.maximumConnectionCount}" />
<property name="minimumConnectionCount"
value="${jdbc.minimumConnectionCount}" />
<property name="delegateProperties"
value="characterEncoding=${jdbc.characterEncoding}" />
</bean>
<!-- spring2.0的配置方式 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>很郁闷哇,如果写上con.setAutoCommit(false);就是启动了事务的,数据库不会插入,如果不写,spring根本就不回滚,不明真像
* File: ContextHelper.java
* User: zhouyujie
* Date: 2011-12-11 14:20:46
* struts2和spring2练习工程的数据库连接工厂类
*/
package com.zyujie.db;import java.sql.Connection;
import java.sql.SQLException;import javax.sql.DataSource;import org.apache.log4j.Logger;public class ConnectionFactory { // 定义log4j日志对象
private static final Logger logger = Logger.getLogger("ConnectionFactory"); /*
* 获取系统的数据源
*/
public static DataSource getDataSource() {
DataSource dataSource = null;
try {
dataSource = (DataSource) ContextHelper.getContext().getBean(
"dataSource");
} catch (Exception e) {
logger.error("获取数据源出错,请检查Spring数据源配置!");
}
return dataSource;
} /*
* 获取数据库连接
*/
public static synchronized Connection getConnection() {
Connection conn = null;
try {
conn = getDataSource().getConnection();
logger.info("连接获取成功!");
} catch (SQLException e) {
logger.error("通过数据源获取数据库连接发生异常!");
e.printStackTrace();
}
return conn;
}
}
而应该用ThreadLocal的方式,下面给出详细例子:package org.sky.util;import java.sql.*;public class ConnectionManager {
private static ThreadLocal tl = new ThreadLocal();
private static Connection conn = null; public static void BeginTrans(boolean beginTrans) throws Exception {
if (tl.get() == null || ((Connection) tl.get()).isClosed()) {
conn = SimpleDataConnection.getInstance().ds.getConnection();
if (beginTrans) {
conn.setAutoCommit(false);
}
tl.set(conn);
}
}
public static Connection getConnection() throws Exception {
return (Connection)tl.get();
}
public static void close() throws SQLException {
try {
((Connection) tl.get()).setAutoCommit(true);
} catch (Exception e) {
}
((Connection) tl.get()).close();
tl.set(null);
} public static void commit() throws SQLException {
try {
((Connection) tl.get()).commit();
} catch (Exception e) {
}
try {
((Connection) tl.get()).setAutoCommit(true);
} catch (Exception e) {
}
} public static void rollback() throws SQLException {
try {
((Connection) tl.get()).rollback();
} catch (Exception e) {
}
try {
((Connection) tl.get()).setAutoCommit(true);
} catch (Exception e) {
}
}}
package org.sky.struts.service;
import org.apache.log4j.Logger;
import org.sky.struts.myclass.ClassForm;
import org.sky.struts.student.StudentForm;
import org.sky.util.*;
public class StudentClassTransaction {
private Logger log = Logger.getLogger(StudentClassTransaction.class);
public int addStudentsClass(ClassForm clsFrm, StudentForm stdFrm){
try{
ConnectionManager.BeginTrans(true);
StudentService stdService = new StudentService();
int answer = stdService.addStdClass(stdFrm, clsFrm);
if(answer==1){
ConnectionManager.commit();
return 1;
}else{
ConnectionManager.rollback();
return -1;
} }catch(Exception e){
e.printStackTrace();
log.error("addStudentsClass transaction error",e);
try{
ConnectionManager.rollback();
}catch(Exception dex){}
return -1;
}finally{
try{
ConnectionManager.close();
}catch(Exception e){}
}
}
}
package org.sky.struts.service;import org.apache.log4j.Logger;
import org.sky.struts.student.*;
import org.sky.struts.myclass.*;public class StudentService {
private Logger log = Logger.getLogger(StudentService.class); public int addStdClass(StudentForm stdFrm, ClassForm clsFrm) {
StudentDAO stdDao = new StudentDAO();
ClassDAO classDao = new ClassDAO();
try {
stdDao.addStudent(stdFrm.getStudentId(), stdFrm
.getStudentName(), stdFrm.getGender(), stdFrm.getDelete());
classDao.addClass(clsFrm.getMyClassId(), clsFrm
.getMyClassName());
return 1;
} catch (Exception e) {
e.printStackTrace();
log.error("addStdClass service error:" + e.getMessage(), e);
return -1;
}
}
}
package org.sky.struts.student;
import org.apache.log4j.Logger;
import org.sky.struts.sql.StudentSql;
import org.sky.util.ConnectionManager;
import org.sky.util.SimpleDataConnection;import java.util.*;
import java.util.*;
import java.sql.*;
import org.sky.struts.exception.*;
public class StudentDAO {
private Logger log = Logger.getLogger(StudentDAO.class);
public void addStudent(String studentId,String studentName,String gender,String deleteFlag)throws StudentException{
int answer=0;
PreparedStatement pstmt = null;
try{
Connection conn = ConnectionManager.getConnection();
pstmt = conn.prepareStatement(StudentSql.ADD_STUDENT);
pstmt.setString(1, studentId);
pstmt.setString(2, studentName);
pstmt.setString(3, gender);
pstmt.setString(4, deleteFlag);
pstmt.executeUpdate();
}catch(Exception e){
throw new StudentException("addStudent error with classid["+studentId+"]"+e.getMessage());
}finally{
try{
if(pstmt!=null){
pstmt.close();
pstmt = null;
}
}catch(Exception e){}
}
}
public ArrayList getAllStd(){
Connection conn = getConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
ArrayList stdList = new ArrayList();
StringBuffer sql = new StringBuffer();
sql.append(" select studentid,studentname,gender,deleteflag");
sql.append(" from student");
try{
if(conn==null){
return null;
}
pstmt = conn.prepareStatement(sql.toString());
rs = pstmt.executeQuery();
while(rs.next()){
Student std = new Student();
std.setStudentId(rs.getString("studentid"));
std.setStudentName(rs.getString("studentname"));
std.setGender(rs.getString("gender"));
std.setDelete(rs.getString("deleteflag"));
stdList.add(std);
}
return stdList;
}catch(Exception e){
e.printStackTrace();
log.error("getDeletedStudents error:"+e,e);
return null;
}finally{
try{
if(rs!=null){
rs.close();
rs = null;
}
}catch(Exception e){}
try{
if(pstmt!=null){
pstmt.close();
pstmt = null;
}
}catch(Exception e){}
try{
if(conn!=null){
conn.close();
conn = null;
}
}catch(Exception e){}
}
}
private Connection getConnection(){
Connection conn = null;
try{
conn = SimpleDataConnection.getInstance().ds.getConnection();
if(conn!=null){
return conn;
}else{
return null;
}
}catch(Exception e){
e.printStackTrace();
log.error("Get Connection error:"+e.getMessage(),e);
return null;
}
}
}
package org.sky.struts.student;import org.apache.struts.actions.DispatchAction;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.log4j.Logger;
import java.util.*;import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.sky.util.TestConnection;public class StudentAction extends DispatchAction {
private Logger log = Logger.getLogger(StudentAction.class); public ActionForward home(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
StudentService stdService = new StudentService();
ArrayList stdList = new ArrayList();
StudentForm stdForm = new StudentForm();
try {
//stdList = stdService.getStudents();
stdList = stdService.getStudentsThruReflect();
if (stdList == null) {
ActionForward af = mapping.findForward("error");
return af;
}
log.info("stdList size====" + stdList.size());
displayStd(stdList);
stdForm.setStudentList(stdList);
request.setAttribute("StudentForm", stdForm);
ActionForward af = mapping.findForward("success");
return af;
} catch (Exception e) {
e.printStackTrace();
ActionForward af = mapping.findForward("error");
return af;
}
} private void displayStd(ArrayList stdList) {
try {
if (stdList.size() < 1) {
System.out.println("there is no any students");
} else {
Iterator it = stdList.iterator();
while (it.hasNext()) {
StudentForm std = (StudentForm) it.next();
System.out.println("studentid:" + std.getStudentId()
+ " studentname:" + std.getStudentName()
+ " gender:" + std.getGender()
+ " deleteflag:" + std.getDelete());
}
}
} catch (Exception e) {
e.printStackTrace();
log.error("Display student error:" + e.getMessage(), e);
}
}
}
private static ClassPathXmlApplicationContext appctx; /*
* 静态块,只加载一次
*/
static {
appctx = new ClassPathXmlApplicationContext("applicationContext.xml");
} /*
* 返回spring容器环境配置文件
*/
public static ClassPathXmlApplicationContext getContext() {
return appctx;
}也就是说,我用ClassPathXmlApplicationContext ,然后(DataSource)ContextHelper.getContext().getBean("dataSource");的方法获取的dataSource是脱离spring事务环境的哇??
帮我纠正一下写法哇。谢谢了哈。正确的代码如:
@SuppressWarnings("unchecked")
public List<PriceCenter> getPriceCodeList() throws Exception {
// TODO Auto-generated method stub
return this.getJdbcTemplate().query(sqls.get("getPriceCodeList"), RowMapperUtil.getPriceCenterRowMapper());
} public void setSqls(Map<String, String> sqls) {
this.sqls = sqls;
}}
注意:extends SimpleJdbcDaoSupport implements IPriceCenterDao该DAO如果是extend SimpleJdbcDaoSupport的话,需要用spring为其注入一个datasource,如下:
<?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:batch="http://www.springframework.org/schema/batch" xmlns:p="http://www.springframework.org/schema/p" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
- <bean id="priceCenterDao" class="com.ccc.batch.job.dao.impl.PriceCenterDaoImp">
<property name="dataSource" ref="dataSourceLocal" />
- <property name="sqls">
- <map>
- <entry key="getPriceCodeList">
<value>SELECT PRICE_CENTER_CODE, PRICE_TABLE_NAME FROM T_PRICE_CENTER WHERE VALID_FLAG = 'Y'</value>
</entry>
</map>
</property>
</bean>
</beans>
然后,把这个dao套到service里,你的service再throw出来任何exception,就能达到你的效果了。结论:自己用datasource造出的jdbc connection的事务,必须自己手动控制,用不了SPRING的AOP机制。如果要用SPRING的AOP机制,就要用SimpleJdbcDaoSupport,HIBERNATE也是同理
如果,楼主一定要用JDBC CONNECTION,并且支持Service层抛错,自动回滚,应该用jdbctemplate,这样,你的connectionfactory类都不需要用了,下面给出例子:public class PriceCenterDaoImp extends SimpleJdbcDaoSupport implements IPriceCenterDao {private Map<String, String> sqls;@SuppressWarnings("unchecked")
public List<PriceCenter> getPriceCodeList() throws Exception {
// TODO Auto-generated method stub
return this.getJdbcTemplate().query(sqls.get("getPriceCodeList"), RowMapperUtil.getPriceCenterRowMapper());
}public void setSqls(Map<String, String> sqls) {
this.sqls = sqls;
}}
注意:extends SimpleJdbcDaoSupport implements IPriceCenterDao该DAO如果是extend SimpleJdbcDaoSupport的话,需要用spring为其注入一个datasource,如下:
<?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:batch="http://www.springframework.org/schema/batch" xmlns:p="http://www.springframework.org/schema/p" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
- <bean id="priceCenterDao" class="com.ccc.batch.job.dao.impl.PriceCenterDaoImp">
<property name="dataSource" ref="dataSourceLocal" />
- <property name="sqls">
- <map>
- <entry key="getPriceCodeList">
<value>SELECT PRICE_CENTER_CODE, PRICE_TABLE_NAME FROM T_PRICE_CENTER WHERE VALID_FLAG = 'Y'</value>
</entry>
</map>
</property>
</bean>
</beans>
然后,把这个dao套到service里,你的service再throw出来任何exception,就能达到你的效果了。结论:自己用datasource造出的jdbc connection的事务,必须自己手动控制,用不了SPRING的AOP机制。如果要用SPRING的AOP机制,就要用SimpleJdbcDaoSupport,HIBERNATE也是同理
<bean id="fxRateDarDao" class="x.batch.job.fxRate.FxRateDarImpl">
<property name="dataSource" ref="dataSourceDAR" />
- <property name="sqls">
- <map>
- <entry key="delSql">
<value>delete from TDAFRGN_XCHG_RT where datediff(d,EFF_DT,?)=0</value>
</entry>
- <entry key="insSql">
<value>INSERT INTO TDAFRGN_XCHG_RT( BASE_CUR_CD, TGT_CUR_CD,EFF_DT,XCHG_RT,MKT_ID) values(:baseCurrencyCode,:targetCurrencyCode,:effectiveDate, :currencyConversionRate,:etId)</value>
</entry>
</map>
</property>
</bean>
package x.batch.job.fxRate;import java.util.Date;
import java.util.List;
import java.util.Map;import org.apache.log4j.Logger;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;public class FxRateDarImpl extends SimpleJdbcDaoSupport implements FxRateDarDAO { private Logger log = Logger.getLogger(this.getClass()); private Map sqls; public void setSqls(Map sqls) {
this.sqls = sqls;
} public void deleteFxRate(Date date) {
try { this.getSimpleJdbcTemplate().update((String) sqls.get("delSql"), date);
} catch (Exception e) {
log.error("delete fxRate error:" + e.getMessage(), e);
return;
} } public void insertFxRate(List fxRateList) {
try {
for (Object fxRate : fxRateList) {
this.getSimpleJdbcTemplate().update((String) sqls.get("insSql"),
new BeanPropertySqlParameterSource(fxRate));
}
} catch (Exception e) {
log.error("insert fxRate error:" + e.getMessage(), e);
return;
}
}
}
我说直白一点吧
把你的
public static Connection getConnection() {
Connection conn = null;
try {
conn = getDataSource().getConnection();
logger.info("连接获取成功!");
} catch (SQLException e) {
logger.error("通过数据源获取数据库连接发生异常!");
e.printStackTrace();
}
return conn;
}改成public static Connection getConnection() {
try {
return org.springframework.jdbc.datasource.DataSourceUtils.getConnection(getDataSource());
} catch(Exception e) {
logger.error("通过数据源获取数据库连接发生异常!");
e.printStackTrace();}就可以了,要想搞明白的话就去看看org.springframework.jdbc.datasource.DataSourceUtils.getConnection方法怎么做的
说了这么多都不去看看org.springframework.jdbc.datasource.DataSourceUtils.getConnection方法怎么做的。
这个方法保证在整个事务范围内获取的是同一个connection对象,而且这个对象是在spring启动事务的时候打开的,并且也绑定在ThreadLocal里面