DataSource

// JDBCUtil.java
public class JdbcUtil {

    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;

    static{
        try {
            InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("dbcfg.properties");
            Properties props = new Properties();
            props.load(in);

            driverClass = props.getProperty("driverClass");
            url = props.getProperty("url");
            user = props.getProperty("user");
            password = props.getProperty("password");
            Class.forName(driverClass);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }

    }

    public static Connection getConnection() {
        try {
            Connection conn = DriverManager.getConnection(url,user,password);
            return conn;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void release(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

简易数据库连接池

public class SimpleConnectionPool {
    // 存放连接对象的池
    private static List<Connection> pool = Collections.synchronizedList(new ArrayList<Connection>());
    // 最开始初始化一些链接到池中
    static {
        for(int i=0;i<10;i++) {
            Connection conn = JdbcUtil.getConnection();
            pool.add(conn);
        }
    }
    public static Connection getConnection() {
        if(pool.size()>0) {
            return pool.remove(0);
        } else {
            throw new RuntimeException("服务器忙");
        }
    }
    public static void release(Connection conn) {
        pool.add(conn);
    }
}

实现DataSource接口的数据库连接池

public class MyDataSource implements DataSource {
    private static List<Connection> pool = Collections.synchronizedList(new ArrayList<Connection>());
    static {
        for(int i=0;i<10;i++) {
            Connection conn =JdbcUtil.getConnection();
            pool.add(conn);
        }
    }

    @Override
    public Connection getConnection() throws SQLException {
        // TODO Auto-generated method stub
        if(pool.size()>0) {
            return pool.remove(0);
        } else {
            throw new RuntimeException("服务器忙");
        }
    }

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

使用动态代理实现数据库连接池

public class MyDataSource implements DataSource {

    private static List<Connection> pool = Collections.synchronizedList(new ArrayList<Connection>());
    //最开始初始化一些链接到池中
    static{
        for(int i=0;i<10;i++){
            Connection conn = JdbcUtil.getConnection();
            pool.add(conn);
        }
    }


    public Connection getConnection() throws SQLException {
        if(pool.size()>0){
            final Connection conn =  pool.remove(0);//数据库驱动的
            Connection proxyConn = (Connection)Proxy.newProxyInstance(conn.getClass().getClassLoader(), 
                    conn.getClass().getInterfaces(), new InvocationHandler() {
                        public Object invoke(Object proxy, Method method, Object[] args)
                                throws Throwable {
                            if("close".equals(method.getName())){
                                //用户调用的是close方法：还回池中
                                return pool.add(conn);
                            }else{
                                //调用原来对象的对应方法
                                return method.invoke(conn, args);
                            }
                        }
                    });
            return proxyConn;
        }else{
            throw new RuntimeException("服务器忙");
        }
    }
}

C3P0

c3p0-config.xml

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///test</property>
        <property name="user">root</property>
        <property name="password">sorry</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">30</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </default-config> <!-- This app is massive! -->
    <named-config name="day16">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///day16</property>
        <property name="user">root</property>
        <property name="password">sorry</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </named-config>
</c3p0-config> 

C3P0Util

public class C3P0Util {
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

    public static DataSource getDataSource(){
        return dataSource;
    }
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException("获取数据库连接失败");
        }
    }
}

public static void main(String[] args) throws SQLException {
    Connection conn = C3P0Util.getConnection();
    System.out.println(conn.getClass().getName());
    conn.close();//还回池中
}

JNDI

Java Naming and Directory Interface
1. 把数据库jar包拷贝到lib目录下
2. 配置JNDI数据源，在META_INF目录下，建立context.xml配置文件

<Context>
    <!-- 
    name:放到JNDI容器中的名称
     -->
    <Resource name="jdbc/test" auth="Container" type="javax.sql.DataSource"
               maxActive="30" maxIdle="30" maxWait="10000"
               username="root" password="sorry" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/test"/>
</Context>

1. 获取数据源

<%
    Context initContext = new InitialContext();
    Context envContext  = (Context)initContext.lookup("java:/comp/env");//路径
    DataSource ds = (DataSource)envContext.lookup("jdbc/test");//名称
    Connection conn = ds.getConnection();
    out.print(conn); 
 %>

获取数据库元信息

public class MetaDataDemo {
    //数据库本身的元信息的获取
    @Test
    public void test1() throws SQLException{
        Connection conn = DBCPUtil.getConnection();
        DatabaseMetaData dmd = conn.getMetaData();
        System.out.println(dmd.getDatabaseProductName());

        conn.close();
    }
    //SQL语句参数的元信息
    @Test
    public void test2() throws SQLException{
        Connection conn = DBCPUtil.getConnection();
        PreparedStatement stmt = conn.prepareStatement("??");//SQL语句要求程序要要写对
        ParameterMetaData pmd = stmt.getParameterMetaData();
        int num = pmd.getParameterCount();//获取sql语句中的占位符的个数
        System.out.println(num);
        conn.close();
    }
    //结果集元信息
    @Test
    public void test3() throws SQLException{
        Connection conn = DBCPUtil.getConnection();
        PreparedStatement stmt = conn.prepareStatement("select * from test");
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int num = rsmd.getColumnCount();//列数
        for(int i=0;i<num;i++){
            String columnName = rsmd.getColumnName(i+1);
            int type = rsmd.getColumnType(i+1);
            System.out.println(columnName+":"+type);
        }
        conn.close();
    }
}