package org.rain.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库事务,回滚到指定点
 */
public class SmallRainDBTranscation {

	public static void main(String[] args) {

		Connection smallrainConn = null;
		Statement smallrainStmt = null;
		Savepoint smallRainSavaPoint = null;
		PreparedStatement smallrainEditDB = null;
		Savepoint smallrainSavaPoint=null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			smallrainConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "smallrain123456789",
					"Xiaoyu123456789");
			smallrainConn.setAutoCommit(false);

			smallrainStmt = smallrainConn.createStatement();
			int smallrainInsertId = 50;
			for (; smallrainInsertId < 70; smallrainInsertId++) {

				smallrainEditDB = smallrainConn
						.prepareStatement("INSERT INTO r_table_stu(STU_ID,NAME, DATARAIN) VALUES(?,?,?) ");
				smallrainEditDB.setObject(1, smallrainInsertId);
				smallrainEditDB.setObject(2, "data中文" + smallrainInsertId);
				smallrainEditDB.setObject(3, "数据data中文" + smallrainInsertId);
				if(smallrainInsertId==56) {
					//事务提交前面的数据，回滚以后的sql
					smallrainSavaPoint = smallrainConn.setSavepoint("smallRianTranscationSavepoint");
				}
				smallrainEditDB.executeUpdate();
			}

			int smallrainInsertErrorId = 3;
			for (; smallrainInsertErrorId < 6; smallrainInsertErrorId++) {

				smallrainEditDB = smallrainConn
						.prepareStatement("INSERT INTO r_table_stu(STU_ID,NAME, DATARAIN) VALUES(?,?,?) ");
				smallrainEditDB.setObject(1, "数字类型为字符串报错");
				smallrainEditDB.setObject(2, "error中文" + smallrainInsertId);
				smallrainEditDB.setObject(3, "errordata中文" + smallrainInsertId);
				smallrainEditDB.executeUpdate();
			}

		} catch (Exception e) {
			try {
				if(null!=smallrainConn&&null!=smallrainSavaPoint) {
					smallrainConn.rollback(smallrainSavaPoint);
				}
				System.out.println("事务回滚到指定点");
			} catch (Exception ein) {

				e.printStackTrace();
			}
		} finally {
			if (null != smallrainEditDB) {
				try {
					smallrainEditDB.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (null != smallrainStmt) {
				try {
					smallrainStmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (null != smallrainConn) {
				try {

					smallrainConn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
	}

}