jdbc回顾2

复写Connection接口

package cn.itdbutil.test;

import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.LinkedList;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

//1.实现同一个接口Connection
public class MyConnection implements Connection {
	
	//3.定义一个变量
	private Connection conn;
	private LinkedList<Connection> pool;
	
	// 2.编写一个构造方法(参数使用了面相对象的多态特性)
	public MyConnection(Connection conn,LinkedList<Connection> pool) {
		this.conn=conn;
		this.pool=pool;
	}
	
	//4.书写需要增强的方法
	@Override
	public void close() throws SQLException {
		pool.add(conn);
	}
	
	/**
	 * 此方法必须覆盖！否则会出现空指针异常！！！
	 */
	@Override
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return conn.prepareStatement(sql);
	}
	}
	

jdbc工具类

package cn.itdbutil.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.LinkedList;
import java.util.ResourceBundle;

public class jdbcUtils {
	//創建容器，用於存放鏈接
	private static LinkedList<Connection> pool = new LinkedList<Connection>(); 
	
	private static String driver;//驱动
	private static String url ;//路径
	private static String user ;//用户名
	private static String password ;//密码
	
	static {
		//jdk提供的工具加载类
		ResourceBundle rb = ResourceBundle.getBundle("db");
		driver = rb.getString("jdbc.driver");
		url = rb.getString("jdbc.url");
		user = rb.getString("jdbc.user");
		password = rb.getString("jdbc.password");
	}
	//初始化连接池中的连接
	static {
		try {
			//注册驱动
			Class.forName(driver);
			//获取连接
			for(int i = 0; i < 3; i++) {
				Connection conn = DriverManager.getConnection(url, user, password);
				MyConnection myConn = new MyConnection(conn, pool);
				pool.add(myConn);
			}
		} catch(Exception e) {
			throw new RuntimeException();
		}
	}
	//创建获取连接方法
	public static Connection getConnection() {
		try {
			//判断池中是否还有连接
			if(!pool.isEmpty()) {
				Connection conn = pool.removeFirst();
				return conn;
			}
			//如果没有等待1秒钟再取
			Thread.sleep(100);
			return getConnection();
		}catch(Exception e) {
			throw new RuntimeException();
		}	
	}
	
	//归还连接
	public static void release(Connection conn) {
		try {
			if(conn != null) {
				//conn.close();
				pool.add(conn);
				System.out.println(pool.size());
			}
		}catch(Exception e) {
			throw new RuntimeException();
		}
	}
	
}

C3P0連接池

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///user</property>
		<property name="user">root</property>
		<property name="password">root</property>
		<property name="initialPoolSize">5</property>
		<property name="maxPoolSize">20</property>
	</default-config>

	<named-config name="itheima">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///user</property>
		<property name="user">root</property>
		<property name="password">root</property>
	</named-config>
</c3p0-config>

package cn.itdbutil.test;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource("itheima");

	public static DataSource getDataSource() {
		return dataSource;
	}

	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}

``

測試代碼

package cn.itdbutil.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import cn.itdbc.test.JdbcUtils;

public class test {
	@Test
	public void testAdd() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = C3P0Utils.getConnection();
			String sql = "select * from product where pid=? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, 2);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				System.out.println("查询成功");
			}
		} catch(Exception e) {
			throw new RuntimeException(e);
		}finally {
			JdbcUtils.release(conn, pstmt, null, rs);
		}
	}
	
	@Test
	public void testAdd1() throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn  = jdbcUtils.getConnection();
			String sql = "select * from product where pid = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, 3);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				System.out.println("查询成功");
				System.out.println(rs.getString(2));
			}
		} catch(Exception e) {
			throw new RuntimeException(e);
		}finally {
			conn.close();
			//jdbcUtils.release(conn);
		}
	}

}