模拟的数据库连接池与DBUtils工具包

Oracle的3个主要用户
普通用户:scott 密码:tiger
普通管理员:system 密码:manager
超级管理员:sys密码:change_on

1. 插入和读取 clob文本数据 Mysql ORACLE的字符数据，最大可达到4G
2. 插入和读取 Blob 二进制数据，
MySql的Blob 存储16KB数据,
与 Oracle 的Blob长度不一样 可存储 二进制数据，最大可达到4G 
3.真正存储二进制数据 要用事务，游标
先插入一个empty_blob()函数，再查询，再插入或读取
4.2种批处理addBatch() || addBatch(sql)，for循环;
#假如数据太大了虚拟机会内存溢出，办法：如1000条时候分段批量执行executeBatch();
5.获取数据库自动生成的主键
PreparedStatemment.getGeneratedKeys();  //返回st对象封装的sql的主键。MySQL中可以
如果是其他数据库,在执行conn.preparedStatement(sql,Statement.RETURN_GENERATED_KEYS)加上一个常量值,ORACLE也可以 
6.存储过程
CallableStatement c = conn.preparedCall("{call demeSp(?,?)}");
获取返回的时候要告诉数据库返回值的类型
c.registerOutParameter(2,Types.VARCHAR2);
7.事务
@Connection 对象处于自动提交模式下,意味着它在执行每个语句后都会自动提交更改，所以要conn.setAutoCommit(false);完了再commit();
有异常在catch()中手动(虽然自动)conn.rollback();
@SavaPoint回滚点
@事务的特性(ACID)
原子性(Atomicity):要么成功、失败
一致性(Consistency):前后数据一致、
隔离性(Isolation): 并发事务，负责隔离操作数据、
持久性(Durability):只要commit，必须要保存下来



未隔离可能发生的:
脏读:一个事物提交(读到)了别人未提交的数据 例:买卖
不可重复读:一个事务读到了提交的数据 例:2人读数据报表
虚读(幻读):一个事物内读到了别人插入的数据 例: 幻语句



当前的数据库连接级别:
set transaction isolation level read uncommitted; 设置最低级别
set transaction isolation level read committed; 设置读级别 (用于查账)
set transaction isolation level repeatable committed; 可重复提交 ,避免不了幻读
set transaction isolation level Serializable;    //单线程级别，别的用户访问不了(会死锁)
start starnsaction;  开启事务




级别设置serializable，才会产生共享锁。
不管在什么级别，只要做update都会加排他锁。
共享锁      解决办法--- 乐观锁:加入版本号解决更新丢失,update 后加入version字段。程序是并发的时候，回滚会造成性能下降。
排它锁      --- 悲观锁：select..for update;令一个账号就需要等待
考虑:系统用更新多，还是查询多，查询多用乐观锁，更新多用悲观锁
锁的是索引列的话，是锁住行，其他用户可以操作其他行。（行级锁）否则是锁住整个表。


[安装配置时]设置innodb引擎，才支持事务和行级锁 (考虑事务多)
如果是myisam，只有表级锁    (查询系统用这个)




Oracle不同，Oracle是针对数据加锁
间隙锁:where id>2 for update
============================================================16
实际开发中，要增强一个开发有3中方法:
1.继承一个类，覆盖实现要增强的方法。
2.包装decorate模式,实现接口，增强方法
3使用动态代理技术构建
包装设计模式
* 1.写一个类实现与被增强类相同的接口
* 2.在类中定义一个变量，记住被增强对象
* 3.在类中定义一个构造方法，接收被增强对象
* 4.重写想增强的方法
* 5.对于不想增强的方法，则在方法体内直接调用被增强对象的方法
数据库连接池:
有3种：
@1.DBCP开发库: commons-dbcp.jar,commons-pool.jddbc
1.得到一个new BasicDataSourceFactory();
2.DataSource ds = factory.createDataSource(properties);构建一个池
Connection conn = ds.getConnection();
@2.C3P0 开发库c3p0.jar ,mchange-commons-0.2
1.ComboPooledDataSource ds = new ();
/*2.Dds.setUrl//设置驱动,连接名称密码，初始化池大小。其他值会默认
ds.getConnection();*/
2.另外可以用配置文件c3p0-config.xml,
//指定配置文件中配置的数据库，不指定为缺省配置
ComboPooledDataSource ds = new ("mysql");
@3.tomcat中连接池
原理:DAO通过jdbUtils从JIDI容器中获取DataSource:app/datasource
配置文件也可以在META-INF目录下写context.xml 
注意:tomcat中会自动生成这份xml，
步骤
1.把jar包放如tomcat的lib包中
2.在tomacat的文档中找到jndi的配置文件
3.在项目META-INF下加入context.xml配置文件
<Context>
<Resource name="jdbc/EmployeeDB"
            auth="Container"
            type="javax.sql.DataSource"
            username="root"
            password="root"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/yuwang"
            initialSize="10"
            maxActive="8"
            maxIdle="4"/>
</Context>
4.以下这样就能得到Connection对象
private static DataSource ds = null;
static {
try {
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
ds = (DataSource) envCtx.lookup("jdbc/EmployeeDB");
} catch (Exception e) {
throw new RuntimeException(e);
}

}

模拟的数据库连接池

import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

public class JdbcPool implements DataSource {

	private static LinkedList<Connection> list = new LinkedList<Connection>();
	static{
		try {
			Class.forName("com.mysql.jdbc.Driver");
			for(int i=0;i<20;i++){
				Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day16", "root", "root");
				System.out.println(conn);
				list.add(conn);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/*
	 1.写一个子类，覆盖想增强的方法()
	 2.用包装设计模式
	 3.用动态代理
	 */
	
	/*
	class MyConnection extends mysqlConnection{
		public void close(){
			
		}
	}
	
	/*
	 * 1.写一个类实现与被增强类相同的接口
	 * 2.在类中定义一个变量，记住被增强对象
	 * 3.在类中定义一个构造方法，接收被增强对象
	 * 4.重写想增强的方法
	 * 5.对于不想增强的方法，则在方法体内直接调用被增强对象的方法
	 */
	/*
	class MyConnection implements Connection{
		private Connection conn;
		public MyConnection(Connection conn){
			this.conn = conn;
		}
		public void close() throws SQLException {
			list.add(conn);
		}
		public void clearWarnings() throws SQLException {
			this.conn.clearWarnings();
			
		}
		public void commit() throws SQLException {
			
			this.conn.commit();
		}
		public Statement createStatement() throws SQLException {
			
			return this.createStatement();
		}
		public Statement createStatement(int resultSetType,
				int resultSetConcurrency) throws SQLException {
			// TODO Auto-generated method stub
			return this.createStatement(resultSetType, resultSetConcurrency);
		}
		public Statement createStatement(int resultSetType,
				int resultSetConcurrency, int resultSetHoldability)
				throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public boolean getAutoCommit() throws SQLException {
			// TODO Auto-generated method stub
			return false;
		}
		public String getCatalog() throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public int getHoldability() throws SQLException {
			// TODO Auto-generated method stub
			return 0;
		}
		public DatabaseMetaData getMetaData() throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public int getTransactionIsolation() throws SQLException {
			// TODO Auto-generated method stub
			return 0;
		}
		public Map<String, Class<?>> getTypeMap() throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public SQLWarning getWarnings() throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public boolean isClosed() throws SQLException {
			// TODO Auto-generated method stub
			return false;
		}
		public boolean isReadOnly() throws SQLException {
			// TODO Auto-generated method stub
			return false;
		}
		public String nativeSQL(String sql) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public CallableStatement prepareCall(String sql) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public CallableStatement prepareCall(String sql, int resultSetType,
				int resultSetConcurrency) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public CallableStatement prepareCall(String sql, int resultSetType,
				int resultSetConcurrency, int resultSetHoldability)
				throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public PreparedStatement prepareStatement(String sql)
				throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public PreparedStatement prepareStatement(String sql,
				int autoGeneratedKeys) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public PreparedStatement prepareStatement(String sql,
				int[] columnIndexes) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public PreparedStatement prepareStatement(String sql,
				String[] columnNames) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public PreparedStatement prepareStatement(String sql,
				int resultSetType, int resultSetConcurrency)
				throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public PreparedStatement prepareStatement(String sql,
				int resultSetType, int resultSetConcurrency,
				int resultSetHoldability) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public void releaseSavepoint(Savepoint savepoint) throws SQLException {
			// TODO Auto-generated method stub
			
		}
		public void rollback() throws SQLException {
			// TODO Auto-generated method stub
			
		}
		public void rollback(Savepoint savepoint) throws SQLException {
			// TODO Auto-generated method stub
			
		}
		public void setAutoCommit(boolean autoCommit) throws SQLException {
			// TODO Auto-generated method stub
			
		}
		public void setCatalog(String catalog) throws SQLException {
			// TODO Auto-generated method stub
			
		}
		public void setHoldability(int holdability) throws SQLException {
			// TODO Auto-generated method stub
			
		}
		public void setReadOnly(boolean readOnly) throws SQLException {
			// TODO Auto-generated method stub
			
		}
		public Savepoint setSavepoint() throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public Savepoint setSavepoint(String name) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		public void setTransactionIsolation(int level) throws SQLException {
			// TODO Auto-generated method stub
			
		}
		public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
			// TODO Auto-generated method stub
			
		}
		
		
	}
	
	*/
	
	public Connection getConnection() throws SQLException {
		
		if(list.size()>0){
			final Connection conn = list.removeFirst();  //mysqlConnection  close
			System.out.println("从池中获取：" + conn + "当前池的大小是：" + list.size());
			//MyConnection myconn = new MyConnection(conn);
			return (Connection) Proxy.newProxyInstance(JdbcPool.class.getClassLoader(), conn.getClass().getInterfaces(), new InvocationHandler(){

				public Object invoke(Object proxy, Method method, Object[] args)
						throws Throwable {
					
					String methodname = method.getName();  //得到dao调用的是哪个方法
					if(methodname.equals("close")){
						list.add(conn);
						System.out.println(conn + "被还到池中了,池的大小是：" + list.size());
						return null;
					}else{
						return method.invoke(conn, args);
					}
				}
				
			});    //dao----connection.createstatement  commit close()
		}else{
			throw new RuntimeException("数据库忙，请等会再来！！");
		}
	}

	public Connection getConnection(String username, String password)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	public int getLoginTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	public void setLogWriter(PrintWriter arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	public void setLoginTimeout(int arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	
	
}

2.

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils {
	
	private static Properties prop = new Properties();
	static{
		try{
			InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
			prop.load(in);
			Class.forName(prop.getProperty("driver"));
		}catch (Exception e) {
			throw new ExceptionInInitializerError(e);
		}
	}
	
	public static Connection getConnection() throws SQLException{
		return DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
	}
	
	public static void release(Connection conn,Statement st,ResultSet rs){
		
		if(rs!=null){
			try{
			rs.close();
			}catch (Exception e) {}
		}
		
		if(st!=null){
			try{
			st.close();
			}catch (Exception e) {}
		}
		
		if(conn!=null){
			try{
			conn.close();
			}catch (Exception e) {}
		}
	}
	
	//搞定所有增删改
	public static void update(String sql,Object params[]) throws SQLException{
		Connection conn = null;
		PreparedStatement st = null;
		try{
			conn = getConnection();
			st = conn.prepareStatement(sql);
			for(int i=0;i<params.length;i++){
				st.setObject(i+1, params[i]);
			}
			st.executeUpdate();
		}finally{
			release(conn, st, null);
		}
	}
	//策略模式
	public static Object query(String sql,Object params[],ResultSetHandler rsh) throws SQLException{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			st = conn.prepareStatement(sql);
			for(int i=0;i<params.length;i++){
				st.setObject(i+1, params[i]);
			}
			rs = st.executeQuery();
			
			return rsh.handler(rs);
			
		}finally{
			release(conn, st, null);
		}
	}
	
}

DBUtils的工具操作:

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

public class Demo1 {
	
	/*
	create database wangs;
	use wangs;
	
	create table account
	(
		id int primary key auto_increment,
		name varchar(20),
		money double
	);
	 
	 
	*/
	

	@Test
	public void insert() throws SQLException {
		
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "insert into account(name,money) values(?,?)";
		Object params[] = {"aaa",10000};
		qr.update(sql, params);
		
	}

	@Test
	public void update() throws Exception {

		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "update account set money=? where id=?";
		Object params[] = {2000,1};
		qr.update(sql, params);
		
	}

	@Test
	public void delete() throws Exception {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "delete from account where id=?";
		qr.update(sql, 1);
	}

	@Test
	public void find() throws Exception {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql= "select * from account where id=?";
		Account a = (Account) qr.query(sql,2, new BeanHandler(Account.class));
		System.out.println(a);
	}

	@Test
	public void getAll() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql= "select * from account";
		List list = (List) qr.query(sql, new BeanListHandler(Account.class));
		System.out.println(list);
	}
	
	@Test
	public void batch() throws Exception{
		
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "insert into account(name,money) values(?,?)";
		Object params[][] = new Object[10][];
		for(int i=0;i<10;i++){
			params[i] = new Object[]{"aa"+i,1000+i};
		}
		qr.batch(sql, params);
	}
}

2.常见处理器

 

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;


public class Demo2 {
	//测试常见的处理器
	
	@Test
	public void testArrayHandler() throws Exception{
		
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from account";
		Object results[] = (Object[]) qr.query(sql, new ArrayHandler());
		System.out.println(Arrays.asList(results));  //toString()
	}
	
	
	@Test
	public void testArrayListHandler() throws Exception{
		
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from account";
		List<Object[]> list =  (List<Object[]>) qr.query(sql, new ArrayListHandler());
		System.out.println(Arrays.asList(list));  //toString()
	}
	
	@Test
	public void testColumnListHandler() throws Exception{
		
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from account";
		List list = (List) qr.query(sql, new MyColumnListHandler("id"));
		System.out.println(list);  //toString()
	}
	
	@Test
	public void testKeyedHandler() throws Exception{
		
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from account";
		Map<Integer,Map<String,Object>> map = (Map) qr.query(sql, new KeyedHandler("id"));
		Set<Entry<Integer, Map<String, Object>>> set = map.entrySet();
		for(Map.Entry<Integer, Map<String, Object>> entry : set){
			System.out.println("key=" + entry.getKey());
			Map<String, Object> innermap = entry.getValue();
			for(Map.Entry<String, Object> innerentry : innermap.entrySet()){
				System.out.print(innerentry.getKey() +"="+ innerentry.getValue() + "       ");
			}
			
			System.out.println();
		}
	}
	
	@Test
	public void testMapHandler() throws Exception{
		
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		
	}
	
	@Test
	public void testMapListHandler() throws Exception{
		
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		
	}
	
	
	//select count(*) from account;
	@Test
	public void testScalarHandler() throws Exception{
		
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select count(*) from account";
		long count = (Long)qr.query(sql, new ScalarHandler(1));
		System.out.println(count);
		
	}
	
}

class MyColumnListHandler implements ResultSetHandler{

	private String columnName;
	public MyColumnListHandler(String columnName){
		this.columnName = columnName;
	}
	
	public Object handle(ResultSet rs) throws SQLException {  //回调方法
		List list = new ArrayList();
		while(rs.next()){
			list.add(rs.getObject(columnName));
		}
		return list;
	}
	
}