C# DAL层代码,运行多条增删改,使用事务操作:
/// <summary>
/// 运行 多条增删改 (非查询语句)
/// </summary>
/// <param name="strSql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static int ExcuteNonQuerys(string[] strSqls, SqlParameter[][] paras2Arr)
{
int res = 0;
//创建连接通道
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
//创建 事务
SqlTransaction tran = conn.BeginTransaction();
//创建命令对象
SqlCommand cmd = new SqlCommand();
//为命令对象指定连接通道
cmd.Connection = conn;
//为命令对象指定事务
cmd.Transaction = tran;
try
{
//循环运行sql语句
for (int i = 0; i < strSqls.Length; i++)
{
//获得要运行的sql语句
string strSql = strSqls[i];
//为命令对象指定 此次运行的 sql语句
cmd.CommandText = strSql;
//加入參数
if (paras2Arr.Length > i)//假设 參数2维数组的长度大于当前循环的下标
{
cmd.Parameters.AddRange(paras2Arr[i]);//将 交错数组 的第一个元素(事实上也是一个数组,加入到參数集合中)
}
res += cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
tran.Commit();//提交事务
}
catch (Exception ex)
{
res = 0;
tran.Rollback();//回滚事务
throw ex;
}
}
return res;
}
JAVA DAO层中编写事务代码:
@Test
public void test(){
Connection conn = null;
PreparedStatement stmt = null;
Savepoint sp = null;
try{
conn = JdbcUtil.getConnection();
conn.setAutoCommit(false); //开启事务
stmt = conn.prepareStatement("update account set money=money-100 where name='aaa'");
stmt.executeUpdate();
stmt = conn.prepareStatement("update account set money=money+100 where name='bbb'");
stmt.executeUpdate();
sp = conn.setSavepoint();//设置回滚点
stmt = conn.prepareStatement("update account set money=money-100 where name='bbb'");
stmt.executeUpdate();
int i=1/0; //bbb给ccc转账时遇到异常
stmt = conn.prepareStatement("update account set money=money+100 where name='ccc'");
stmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
try {
conn.rollback(sp); //回滚事务
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
try {
conn.commit(); //事务提交
} catch (SQLException e) {
e.printStackTrace();
}
JdbcUtil.release(null, stmt, conn);
}
}
JAVA Service层使用事务操作代码:
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
//把得到连接及事务有关的方法写到此类中
public class TransactionUtil {
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
private static DataSource ds;
static{
try {
InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties props = new Properties();
props.load(in);
ds = BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
e.printStackTrace();
}
}
public static DataSource getDataSource(){
return ds;
}
public static Connection getConnection(){
try {
Connection conn = tl.get();
if(conn==null){
conn = ds.getConnection();
tl.set(conn);
}
return conn;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void startTransaction(){
try {
Connection conn = tl.get();
if(conn==null){
conn = getConnection();
// tl.set(conn);
}
conn.setAutoCommit(false);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void rollback(){
try {
Connection conn = tl.get();
if(conn==null){
conn = getConnection();
// tl.set(conn);
}
conn.rollback();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void commit(){
try {
Connection conn = tl.get();
if(conn==null){
conn = getConnection();
// tl.set(conn);
}
conn.commit();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void relase(){
try {
Connection conn = tl.get();
if(conn!=null){
conn.close();
tl.remove();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}