第六课 数据库事务_DAO

1 事务模拟（银行转账）

• 哪些操作会导致数据的自动提交
  • DDL操作一旦执行，都会自动提交，关闭自动提交对其无效
  • DML操作默认情况下自动提交，可以关闭自动提交后不再自动提交
  • 默认关闭连接时，会自动提交

/*
 * 事务模拟：转账模拟
 */
public static void test01() {
	Connection conn = null;
	try {
		conn = JDBCTools.getConn();
		// 关闭自动提交
		conn.setAutoCommit(false);
		String sql_AA = "update user_table set balance = balance - 100 where `user` = ?";
		CommonUpdate(conn, sql_AA, "AA");
		
		// 模拟异常出现时候的状态，此时应该执行回滚操作
		double exception = 10 / 0;
		
		String sql_BB = "update user_table set balance = balance + 100 where `user` = ?";
		CommonUpdate(conn, sql_BB, "BB");
		// 统一提交
		conn.commit();
		System.out.println("转账成功");
	} catch (Exception e) {
		try {
			// 发生异常时，执行回滚操作
			conn.rollback();
			System.out.println("转账失败");
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		e.printStackTrace();
	} finally {
		JDBCTools.close(conn);
	}
}

/*
 * 通用增删改操作：连接从外部传进来，保证连接贯穿整个事务
 */
public static void CommonUpdate(Connection conn,String SQL, Object... obj) {
	PreparedStatement ps = null;
	try {
		ps = conn.prepareStatement(SQL);
		for (int i = 0; i < obj.length; i++) {
			ps.setObject(i + 1, obj[i]);
		}
		ps.execute();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		JDBCTools.close(ps);
	}
}

2 JDBC中设置数据库隔离级别

// 隔离级别：读未提交_脏读、不可重复读、幻读
conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
// 隔离级别：读已提交_不可重复读、幻读
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
// 隔离级别：可重复读_幻读
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
// 隔离级别：串行化，解决所有问题
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

3 补充操作

• 创建mysql数据库用户

create user tom identified by 'abc123';

• 授予权限

#授予通过网络方式登录的tom用户，对所有库所有表的全部权限，密码设为abc123.
grant all privileges on *.* to tom@'%'  identified by 'abc123'; 

#给tom用户使用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
grant select,insert,delete,update on atguigudb.* to tom@localhost identified by 'abc123'; 

4 DAO及相关实现类

• DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息。有时也称作：BaseDAO

• 作用：为了实现功能的模块化，更有利于代码的维护和升级。

• BaseDao

package JDBC.DAO;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

/**
 * 主要是其他功能的一个辅助 包括：通用的增删改查
 * 不会直接实例化这个类，因此用abstract修饰
 * 
 * @author Yorick
 *
 */
public abstract class BaseDao<T> {
	private Class<T> clazz;
	// 对clazz 实例化，此时，不需要传入对应类的.class
	{
		Type genericSuperclass = this.getClass().getGenericSuperclass();
		ParameterizedType pt = (ParameterizedType) genericSuperclass;
		Type[] actualTypeArguments = pt.getActualTypeArguments();
		clazz = (Class<T>) actualTypeArguments[0];
	}
	/*
	 * 实现增删改
	 */
	public void update(Connection conn, String sql, Object... objects) {
		try {
			int len = objects.length;
			PreparedStatement ps = conn.prepareStatement(sql);
			for (int i = 0; i < len; i++) {
				ps.setObject(i + 1, objects[i]);
			}
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/*
	 * 实现查
	 */
	public List<T> select(Connection conn, String sql, Object... objects) {
		try {
			int len = objects.length;
			PreparedStatement ps = conn.prepareStatement(sql);
			for (int i = 0; i < len; i++) {
				ps.setObject(i + 1, objects[i]);
			}
			ResultSet result = ps.executeQuery();
			T t = null;
			List<T> list = new ArrayList<T>();
			ResultSetMetaData rsd = result.getMetaData();
			while (result.next()) {
				t = this.clazz.newInstance();
				int columnCount = rsd.getColumnCount();
				for (int i = 0; i < columnCount; i++) {
					String columnName = rsd.getColumnLabel(i + 1);
					Object columnValue = result.getObject(i + 1);
					Field declaredField = clazz.getDeclaredField(columnName);
					declaredField.setAccessible(true);
					declaredField.set(t, columnValue);
				}
				list.add(t);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/*
	 * 其他分组统计查询
	 */
	public <E> E getValue(Connection conn, String sql) {
		E e = null;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet result = ps.executeQuery();
			if (result.next()) {
				e = (E) result.getObject(1);
			}
			return e;
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return null;
	}
}

• CustomersDao

package JDBC.DAO;

import java.sql.Connection;
import java.util.Date;
import java.util.List;

import JDBC.CommonSelect.Customers;

public interface CustomersDao {
	void insert(Connection conn, Customers cust);

	void deleteById(Connection conn, int id);

	void update(Connection conn, Customers cust);

	Customers getCustomersById(Connection conn, int id);

	List<Customers> getAll(Connection conn);

	Long getCount(Connection conn);

	Date getMaxBirth(Connection conn);
}

• CustomersDaoImpl

package JDBC.DAO;

import java.sql.Connection;
import java.util.Date;
import java.util.List;

import JDBC.CommonSelect.Customers;

public class CustomersDaoImpl extends BaseDao<Customers> implements CustomersDao {

	@Override
	public void insert(Connection conn, Customers cust) {
		String sql = "insert into customers (name,email,birth) values (?,?,?)";
		update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth());
	}

	@Override
	public void deleteById(Connection conn, int id) {
		String sql = "delete from customers where id = ?";
		update(conn, sql, id);
	}

	@Override
	public void update(Connection conn, Customers cust) {
		String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";
		update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth(), cust.getId());
	}

	@Override
	public Customers getCustomersById(Connection conn, int id) {
		String sql = "select name,email,birth from customers where id = ?";
		List<Customers> customerList = select(conn, sql, id);
		Customers customer = customerList.get(0);
		return customer;
	}

	@Override
	public List<Customers> getAll(Connection conn) {
		String sql = "select id,name,email,birth from customers";
		List<Customers> customerList = select(conn, sql);
		return customerList;
	}

	@Override
	public Long getCount(Connection conn) {
		String sql = "select count(*) from customers";
		Long value = getValue(conn, sql);
		return value;
	}

	@Override
	public Date getMaxBirth(Connection conn) {
		String sql = "select max(birth) from customers";
		return getValue(conn, sql);
	}
}
Override
	public Long getCount(Connection conn) {
		String sql = "select count(*) from customers";
		Long value = getValue(conn, sql);
		return value;
	}

	@Override
	public Date getMaxBirth(Connection conn) {
		String sql = "select max(birth) from customers";
		return getValue(conn, sql);
	}
}