JDBC和连接池

一、JDBC写法：

连接之前需要下载jar包

maven下：

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->

<dependency>

  <groupId>mysql</groupId>

  <artifactId>mysql-connector-java</artifactId>

  <version>5.1.38</version>

</dependency>

     DB.java:

package com.tyler.db;

import java.sql.Connection;

import java.sql.DriverManager;

/**

* Created by tyler on 2016/11/8.

*/

public class DB {

    public static Connection connection;

    public static Connection getConnection(){

        try {

            Class.forName("org.gjt.mm.mysql.Driver");

            connection= DriverManager.getConnection("jdbc:mysql:// localhost/ database?useUnicode=true&characterEncoding=UTF-8"," root"," root");

        } catch (Exception e) {

            e.printStackTrace();

        }

        return connection;

    }

    public static void closeConnection(){

        if(connection!=null){

            try {

                connection.close();

            } catch (Exception e) {

                e.printStackTrace();

            }

        }

    }

}

二、DBCP

maven下：

<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->

<dependency>

    <groupId>commons-dbcp</groupId>

    <artifactId>commons-dbcp</artifactId>

    <version>1.4</version>

</dependency>

DBCP.java

package com.tyler.db;

import javax.sql.DataSource;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSourceFactory;

/**

* Created by tyler on 2016/11/15.

*/

public class DBCP {

    private static Connection conn;

    private static Properties pro;

    private static DataSource ds;

    static{

        pro = new Properties();

        InputStream is = DBCP.class.getClassLoader().getResourceAsStream("dbcp.ini");

        try {

            pro.load(is);

            ds = BasicDataSourceFactory.createDataSource(pro);

        } catch (Exception e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    public static Connection getConnection(){

        try {

            conn = ds.getConnection();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return conn;

    }

}

dbcp.ini

#连接设置

driverClassName=org.gjt.mm.mysql.Driver

url=jdbc:mysql:// localhost:3306/ database?useUnicode=true&characterEncoding=UTF-8

username=root

password=root

#<!-- 初始化连接 -->

initialSize=10

#最大连接数量

maxActive=50

#<!-- 最大空闲连接 -->

maxIdle=20

#<!-- 最小空闲连接 -->

minIdle=5

#<!-- 超时等待时间以毫秒为单位 60000毫秒/1000等于60秒 -->

maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]

#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。

connectionProperties=useUnicode=true;characterEncoding=utf-8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。

defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。

#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）

defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。

#可用值为下列之一：

三、C3P0

maven下：

<!-- https://mvnrepository.com/artifact/c3p0/c3p0 -->

<dependency>

  <groupId>c3p0</groupId>

  <artifactId>c3p0</artifactId>

  <version>0.9.1.2</version>

</dependency>

C3P0.java

package com.tyler.db;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;

import java.sql.SQLException;

/**

* Created by tyler on 2016/11/15.

*/

public class C3P0 {

    private static Connection connection = null;

    private static ComboPooledDataSource cpds = null;

    static {

        cpds= new ComboPooledDataSource("mysql");

        try {

            connection = cpds.getConnection();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    public static Connection getConnection(){

        try {

            connection = cpds.getConnection();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return connection;

    }

}

c3p0-config.xml

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

<c3p0-config>

    <default-config>

        <property name="automaticTestTable">con_test</property>

        <property name="checkoutTimeout">30000</property>

        <property name="idleConnectionTestPeriod">30</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>

    </default-config>

    <named-config name="mysql">

        <property name="driverClass">com.mysql.jdbc.Driver </property>

        <property name="jdbcUrl">jdbc:mysql:// localhost :3306/ databses ?useUnicode=true&amp;characterEncoding=utf-8

        </property>

        <property name="user">root</property>

        <property name="password">root</property>

        <property name="acquireIncrement">5</property>

        <property name="initialPoolSize">10</property>

        <property name="minPoolSize">5</property>

        <property name="maxPoolSize">30</property>

    </named-config>

</c3p0-config>

四、druid

maven下

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->

<dependency>

  <groupId>com.alibaba</groupId>

  <artifactId>druid</artifactId>

  <version>1.0.18</version>

</dependency>

Druid.java

package com.tyler.db;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.Properties;

/**

* Created by tyler on 2016/11/15.

*/

public class Druid {

    public static Connection conn;

    public static DataSource ds;

    static {

        Properties pro = new Properties();

        InputStream is=Druid.class.getClassLoader().getResourceAsStream("druid.ini");

        try {

            pro.load(is);

            ds = DruidDataSourceFactory.createDataSource(pro);

        } catch (Exception e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    public static Connection getConnection(){

        try {

            conn = ds.getConnection();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return conn;

    }

}

druid.ini

#mysql数据库

url=jdbc:mysql:// localhost:3306/ database?useUnicode=true&characterEncoding=UTF-8

username=root

password=root

#filters=stat

#最大连接数量

maxActive=20

#初始化连接数量

initialSize=10

#超时等待时间以毫秒为单位

maxWait=12000

#最小空闲连接

minIdle=5

#校验连接池中限制时间超过minEvictableIdleTimeMillis的连接对象

timeBetweenEvictionRunsMillis=6000

#连接在池中保持空闲而不被空闲连接回收器线程(如果有)回收的最小时间值，单位毫秒

#minEvictableIdleTimeMillis=

#SQL查询,用来验证从连接池取出的连接,在将连接返回给调用者之前

validationQuery=SELECT now();

#指明连接是否被空闲连接回收器(如果有)进行检验.

#如果检测失败,则连接将被从池中去除.

testWhileIdle=true

#指明是否在从池中取出连接前进行检验,如果检验失败,则从池中去除连接并尝试取出另一个.

testOnBorrow=false

#指明是否在归还到池中前进行检验

testOnReturn=false

#poolPreparedStatements=true

#maxPoolPreparedStatementPerConnectionSize=20