JDBC事务处理示例

1.首先对JDBC数据库进行初始化：
DBConfig.java

import java.sql.*;

public class DBConfig {
	public DBConfig(){

	}

	private static String driver = "com.mysql.jdbc.Driver";

	static {
		try {
			Class.forName(driver);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection() throws SQLException {

		Connection conn = null;
		try {
			// mysql url地址
			String url = "jdbc:mysql://localhost:3306/jdbctest";
			conn = DriverManager.getConnection(url, "root", "admin");

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		return conn;
	}
       //测试连接是否为空
	public static void main(String[] args) {
		try {
			Connection conn = null;
			if((conn=DBConfig.getConnection())!= null){
				System.out.println("Connection is not null.");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}

2.JAVABean 获得数据库的结构

public class TableBean {
	public TableBean() {

	}

	private String id;  //学号
	private String name;//姓名
	private String level;//等级

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getLevel() {
		return level;
	}

	public void setLevel(String level) {
		this.level = level;
	}

3.事务处理TableDao.java

import java.io.CharArrayReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.giantstone.base.dao.AbstractDAO;
import com.giantstone.common.util.ExceptionUtil;
import com.giantstone.table.bean.TableBean;

public class TableDAO extends AbstractDAO {

	public TableDAO(boolean inTransaction, Connection conn) {
		super(inTransaction, conn);
	}

	private String sourcedb;

	public String getSourcedb() {
		return sourcedb;
	}

	public void setSourcedb(String sourcedb) {
		this.sourcedb = sourcedb;
	}

	public void insert(TableBean bean) throws Exception {
		Connection conn = this.getConnection();

		if (conn == null) {
			throw new RuntimeException("Connection is NULL!");
		}

		// 预编译SQL语句对象
		PreparedStatement statement = null;

		if (bean.getId() == null || bean.getId().length() > 5) {
			throw new IllegalArgumentException("id is null or id too long"
					+ bean.getId());
		}

		if (bean.getName() == null || bean.getName().length() > 10) {
			throw new IllegalArgumentException("name is null or name too long"
					+ bean.getName());
		}

		try {
			conn.setAutoCommit(false);
			String sql = "insert into student (id,name,level) values (?,?,?)";
			statement = conn.prepareStatement(sql);
			debug = true;

			statement.setString(1, bean.getId());
			statement.setString(2, bean.getName());
			statement.setString(3, bean.getLevel());

			long startTime = 0, endTime = 0;
			if (debug) {
				startTime = System.currentTimeMillis();
			}

			// 操作结果行数
			int retFlag = statement.executeUpdate();
			if (debug) {
				endTime = System.currentTimeMillis();
				debug("student.insert() spend " + (endTime - startTime)
						+ "ms. retFlag = " + retFlag + " SQL:" + sql
						+ "; parameters : ID = \"" + bean.getId()
						+ "\" , Name = \"" + bean.getName() + "\" , Level = \""
						+ bean.getLevel());
			}
			if (retFlag == 0) {
				throw new RuntimeException("insert failed.");
			}

			if (!isInTransaction()) {
				conn.commit();
			}
		} catch (SQLException e) {
			e.printStackTrace(System.err);
			// 如果事务处理失败,事务回滚
			if (!isInTransaction()) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace(System.err);
				}
			}
		} finally {
			if (!isInTransaction()) {
				conn.setAutoCommit(true);
			}
			if (null != statement) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace(System.err);
				}
			}
		}

	}

	public void setLevel(char level) {
		Connection conn = this.getConnection();
		PreparedStatement statement = null;
		if (null == conn) {
			throw new RuntimeException("Connection is NULL");
		}

		if (new Character(level).compareTo('A') != 0
				|| new Character(level).compareTo('B') != 0
				|| new Character(level).compareTo('C') != 0) {
			throw new IllegalArgumentException("level is not allowed input "
					+ level);
		}

		try {
			conn.setAutoCommit(false);
			String sql = "update student set level = ? ";
			statement = conn.prepareStatement(sql);
			char[] chars = new char[1];
			chars[0] = level;

			// char类型放入数据库中
			statement.setCharacterStream(1, new CharArrayReader(chars), 1);

			long startTime = 0, endTime = 0;
			if (debug) {
				startTime = System.currentTimeMillis();
			}
			int retFlag = statement.executeUpdate();
			if (debug) {
				endTime = System.currentTimeMillis();
				debug("student.insert() spend " + (endTime - startTime)
						+ "ms. retFlag = " + retFlag + " SQL:" + sql);
			}
			if (retFlag == 0) {
				throw new RuntimeException("update failed.");
			}

			if (!isInTransaction()) {
				conn.commit();
			}

		} catch (SQLException e) {
			e.printStackTrace(System.err);
			if (!isInTransaction()) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace(System.err);
				}
				ExceptionUtil.throwActualException(e);
			}
		} finally {
			if (null != statement) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}

	public List findLevel() {
		Connection conn = this.getConnection();
		if (null == conn) {
			throw new RuntimeException("Connection is NULL!");
		}

		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List list = new ArrayList();
		TableBean returnBean = null;
		try {
			String sql = "select * from student where (Level IS NULL OR Level = '')";
			statement = conn.prepareStatement(sql);

			long startTime = 0, endTime = 0;
			if (debug) {
				startTime = System.currentTimeMillis();
			}

			// 提交查询
			resultSet = statement.executeQuery();
			if (debug) {
				endTime = System.currentTimeMillis();
				debug("setLevel() spend " + (endTime - startTime) + "ms.SQL:"
						+ sql);
			}

			while (resultSet.next()) {
				returnBean = new TableBean();
				returnBean.setId(resultSet.getString("id"));
				returnBean.setName(resultSet.getString("name"));
				returnBean.setLevel(resultSet.getString("level"));
				list.add(returnBean);
			}

		} catch (SQLException e) {
			e.printStackTrace(System.err);
			if (!isInTransaction()) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace(System.err);
				}
				ExceptionUtil.throwActualException(e);
			}
		} finally {
			if (null != statement) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	public void setLevel(String level,String id){
		Connection conn = this.getConnection();
		if(level.length() > 1){
			throw new RuntimeException("Level length too long.");
		}
		if(null == conn){
			throw new RuntimeException("Connection is NULL");
		}
		PreparedStatement statement = null;

		try{
			conn.setAutoCommit(false);
			String sql = "update student set level=? where id=?";
			statement = conn.prepareStatement(sql);

			char[] chars = new char[1];
			chars = level.toCharArray();

			// char类型放入数据库中
			statement.setCharacterStream(1, new CharArrayReader(chars), 1);

			statement.setString(2,	id);

			long startTime = 0,endTime = 0;
			if(debug){
				startTime = System.currentTimeMillis();
			}
			int retFlag =statement.executeUpdate();
			if(debug){
				endTime = System.currentTimeMillis();
				debug("setLevel() spend "+(endTime - startTime)+"ms. retFlag" +retFlag+" SQL:"+sql );
			}
			if(retFlag == 0){
				throw new RuntimeException("update failed.");
			}

			if(!isInTransaction()){
				conn.commit();
			}
		}catch(SQLException e){
			e.printStackTrace(System.err);
			if(!isInTransaction()){
				try{
					conn.rollback();
				}catch(SQLException e1){
					e1.printStackTrace(System.err);
				}
			}
			ExceptionUtil.throwActualException(e);
		}finally{

			if (null != statement) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace(System.err);
				}
			}

		}

	}
}

4.多线程的事务处理

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import com.giantstone.config.dbconfig.DBConfig;
import com.giantstone.connect.dao.TableDAO;
import com.giantstone.table.bean.TableBean;

public class ThreadHandler extends Thread {
	private char ch;

	public ThreadHandler() {

	}

	public void run() {
		Connection conn = null;

		try {
			conn = DBConfig.getConnection();
			TableDAO dao = new TableDAO(false, conn);
			List list = dao.findLevel();
			Iterator it = list.iterator();

			while(it.hasNext()){
				TableBean bean = (TableBean)it.next();
				String id = bean.getId();
				dao.setLevel("A", id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
}

5.测试类

import com.giantstone.common.config.ConfigManager;
import com.giantstone.config.dbconfig.*;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.log4j.PropertyConfigurator;
import com.giantstone.connect.dao.*;
import com.giantstone.table.bean.TableBean;

public class JDBCTest extends Thread{
	public static final String LOG_PROPS_FILE = "log4j.properties";
	public JDBCTest(){
			ConfigManager.setRootPath("./config");
				PropertyConfigurator.configure(ConfigManager
						.loadProperties(LOG_PROPS_FILE));

		}

	public void run(){
		Connection conn = null;
		TableBean bean = null;
		try{
			conn = DBConfig.getConnection();
			TableDAO dao = new TableDAO(false ,conn);
			dao.setSourcedb("student");

			bean = new TableBean();
			bean.setId("00008");
			bean.setName("liugong");
			bean.setLevel("A");

			dao.insert(bean);

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(null != conn){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	public static void main(String[] args){
       //开启多个线程，对数据库进行冲突操作，查看事务处理是否正常
		for(int i  = 0 ; i < 20 ; i ++){
			new JDBCTest().start();
			new ThreadHandler().start();
		}
	}
}