17.12 事务处理(血干JAVA系类)

17.12.1 MySQL对事务的支持

17.12.2 执行JDBC的事务处理

【例17.28］没有使用事务处理

package file;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;

public class demo 
{
	public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
	public static final String DBURL = "jdbc:mysql://localhost:3306/java_mysql";
	public static final String DBUSER = "root";
	public static final String DBPASS = "dyh20011022";
	
	public static void main(String[] args) throws Exception
	{		
		Class.forName(DBDRIVER);//驱动
		//注意sql语句:select
		String sql = "insert into user(name,password,age,sex,birthday) values('赵胡金','password_fair',30,'男','2020-10-25')";
		
		Connection conn =DriverManager.getConnection(DBURL, DBUSER, DBPASS);
		PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); 
		pstmt.addBatch("insert into user(name,password,age,sex,birthday) values('赵胡金1','password_fair',30,'男','2020-10-25')");
		pstmt.addBatch("insert into user(name,password,age,sex,birthday) values('赵胡金2','password_fair',30,'男','2020-10-25')");
		//cuo wu
		pstmt.addBatch("insert into user(name,password,age,sex,birthday) values(''赵胡金3','password_fair',30,'男','2020-10-25')");
		
		pstmt.addBatch("insert into user(name,password,age,sex,birthday) values('赵胡金4','password_fair',30,'男','2020-10-25')");
		pstmt.addBatch("insert into user(name,password,age,sex,birthday) values('赵胡金5','password_fair',30,'男','2020-10-25')");
		int temp[] = pstmt.executeBatch();
		System.out.println("执行了"+temp.length+"条语句。");
		
		//关闭
		pstmt.close();
		conn.close();
	}
}

没有3

【例17.29】事务基本操作

conn.setAutoCommit(false);
int temp[] = pstmt.executeBatch();
conn.commit();//提交
conn.rollback();

package file;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;

public class demo 
{
	public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
	public static final String DBURL = "jdbc:mysql://localhost:3306/java_mysql";
	public static final String DBUSER = "root";
	public static final String DBPASS = "dyh20011022";
	
	public static void main(String[] args) throws Exception
	{		
		Class.forName(DBDRIVER);//驱动
		//注意sql语句:select
		String sql = "insert into user(name,password,age,sex,birthday) values('赵胡金','password_fair',30,'男','2020-10-25')";
		
		Connection conn =DriverManager.getConnection(DBURL, DBUSER, DBPASS);
		PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); 
		conn.setAutoCommit(false);
		
		
		pstmt.addBatch("insert into user(name,password,age,sex,birthday) values('赵1','password_fair',30,'男','2020-10-25')");
		pstmt.addBatch("insert into user(name,password,age,sex,birthday) values('赵2','password_fair',30,'男','2020-10-25')");
		//cuo wu
		pstmt.addBatch("insert into user(name,password,age,sex,birthday) values(''赵3','password_fair',30,'男','2020-10-25')");
		
		pstmt.addBatch("insert into user(name,password,age,sex,birthday) values('赵4','password_fair',30,'男','2020-10-25')");
		pstmt.addBatch("insert into user(name,password,age,sex,birthday) values('赵5','password_fair',30,'男','2020-10-25')");
		
		try 
		{
			int temp[] = pstmt.executeBatch();
			System.out.println("执行了"+temp.length+"条语句。");
			conn.commit();//提交
		}
		catch (Exception e) 
		{
			try 
			{
				conn.rollback();
			}
			catch (Exception e2) 
			{
				e2.printStackTrace();
			}
		}
		
		//关闭
		pstmt.close();
		conn.close();
	}
}

无变化，回滚了

面的程序中加入了事务的处理操作，这样当更新出错后，数据库会进行回滚，则所有的更新操作全部失效，如图17-49所示。
当然，也可以加入若干的Savepoint作为事务的保存点

【例 17.30］加入 Savepoint

conn.setAutoCommit(false);
pstmt.executeUpdate("insert into user(name,password,age,sex,birthday) values('赵1','password_fair',30,'男','2020-10-25')");
Savepoint sp = conn.setSavepoint();

conn.rollback(sp);//回滚
conn.commit();//提交

package file;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Savepoint;
import java.text.SimpleDateFormat;

public class demo 
{
	public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
	public static final String DBURL = "jdbc:mysql://localhost:3306/java_mysql";
	public static final String DBUSER = "root";
	public static final String DBPASS = "dyh20011022";
	
	public static void main(String[] args) throws Exception
	{		
		Class.forName(DBDRIVER);//驱动
		//注意sql语句:select
		String sql = "insert into user(name,password,age,sex,birthday) values('赵胡金','password_fair',30,'男','2020-10-25')";
		
		Connection conn =DriverManager.getConnection(DBURL, DBUSER, DBPASS);
		PreparedStatement pstmt = conn.prepareStatement(sql); 
		conn.setAutoCommit(false);
		
		
		pstmt.executeUpdate("insert into user(name,password,age,sex,birthday) values('赵1','password_fair',30,'男','2020-10-25')");
		pstmt.executeUpdate("insert into user(name,password,age,sex,birthday) values('赵2','password_fair',30,'男','2020-10-25')");
		pstmt.executeUpdate("insert into user(name,password,age,sex,birthday) values('赵3','password_fair',30,'男','2020-10-25')");

		Savepoint sp = conn.setSavepoint();

		pstmt.executeUpdate("insert into user(name,password,age,sex,birthday) values('赵4','password_fair',30,'男','2020-10-25')");
		pstmt.executeUpdate("insert into user(name,password,age,sex,birthday) values('赵5','password_fair',30,'男','2020-10-25')");
		
		try 
		{
			conn.rollback(sp);//回滚
			conn.commit();//提交
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		//关闭
		pstmt.close();
		conn.close();
	}
}