mysql dbcp maxactive_JDBC+C3P0+DBCP 基本使用

1.概述

这篇文章主要说了JDBC的基本使用,包括Statement,PreparedStatement,JDBC的连接,Mysql创建用户创建数据表,C3P0的连接与配置,DBCP的连接与配置.

2.mysql的处理

这里的JDBC使用Mysql作为DBMS,请先安装Mysql,未安装的请点击这里下载,安装教程在这里,作者使用的Mysql的8.0.17版本.

(1)新建用户

随便新建一个用户,比如这里作者新建的是aa,密码是aa123bb.

create user 'aa'@'localhost' identified by 'aa123bb'

(2)建立数据表

建立测试用的数据表与数据库.

create database db;

use db;

create table db

(

id int PRIMARY key,

name char(20)

);

(3)用户权限

对刚才新建的用户授权:

grant select,update,delete,insert on db.* to 'aa'@'localhost';

2.JDBC

(1)jar包

(2)连接

首先注册驱动,驱动需要一个url,用户名和密码,用户名和密码是上一步创建好的,url包含ip地址,端口和数据库的名字.

private static final boolean mysqlVersionGreaterThen8 = true;

private static final String driver = "com.mysql" + (mysqlVersionGreaterThen8 ? ".cj" : "") + ".jdbc.Driver";

private static final String ip = "127.0.0.1";

private static final String port = "3306";

private static String databaseName = "db";

private static String url;

private static String username = "aa";

private static String password = "k041400r";

private static Connection connection = null;

public static Connection getConnection() {

try {

url = "jdbc:mysql://" + ip + ":" + port + "/" + databaseName;

Class.forName(driver);

return connection = DriverManager.getConnection(url, username, password);

} catch (Exception e) {

e.printStackTrace();

}

return null;

}

这里要注意以下旧版本的mysql的驱动叫com.mysql.jdbc.Driver,新版本的叫com.mysql.cj.jdbc.Driver.还有就是url的格式:

jdbc:mysql://ip:port/database

(3)Statement

获取数据库连接后,使用createStatement方法创建Statement

对于select,使用Statement的executeQuery(sql),返回ResultSet

对于update,delete,insert,使用Statement的executeUpdate(sql)

其中sql是要执行的sql语句,一个String.

public void useStatement() {

try {

useStatementInsert();

useStatementSelect();

useStatementUpdate();

useStatementSelect();

useStatementDelete();

} catch (SQLException e) {

e.printStackTrace();

}

}

public void useStatementInsert() throws SQLException {

String sql = "insert into db(id,name) values(1,'23')";

Statement statement = connection.createStatement();

statement.executeUpdate(sql);

}

public void useStatementDelete() throws SQLException {

String sql = "delete from db";

Statement statement = connection.createStatement();

statement.executeUpdate(sql);

}

public void useStatementSelect() throws SQLException {

String sql = "select * from db";

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery(sql);

ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

int count = resultSetMetaData.getColumnCount();

while (resultSet.next()) {

for (int i = 1; i <= count; ++i) {

System.out.println(resultSet.getObject(i));

}

}

}

public void useStatementUpdate() throws SQLException {

Statement statement = connection.createStatement();

String sql = "update db set id = 3,name = '555' where id = 1";

statement.executeUpdate(sql);

}

这里对ResultSet使用的getMetaData,可以获取结果集的各种类型信息,包括字段的类型,个数,等等.

(4)PreparedStatement

PreparedStatement与Statement使用基本一样.调用的时候先使用Connection的prepareStatement(sql)创建,然后

对于select,使用executeQuery(),返回一个ResultSet

对于update,delete,insert使用executeUpdate().

public void usePrepareStatement() {

try {

usePrepareStatementInsert();

usePrepareStatementSelect();

usePrepareStatementUpdate();

usePrepareStatementSelect();

usePrepareStatementDelete();

} catch (SQLException e) {

e.printStackTrace();

}

}

public void usePrepareStatementInsert() throws SQLException {

String sql = "insert into db(id,name) values(1,'23')";

PreparedStatement preparedStatement = connection.prepareStatement(sql);

preparedStatement.executeUpdate();

}

public void usePrepareStatementDelete() throws SQLException {

String sql = "delete from db";

PreparedStatement preparedStatement = connection.prepareStatement(sql);

preparedStatement.executeUpdate();

}

public void usePrepareStatementSelect() throws SQLException {

String sql = "select * from db";

PreparedStatement preparedStatement = connection.prepareStatement(sql);

ResultSet resultSet = preparedStatement.executeQuery();

ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

int count = resultSetMetaData.getColumnCount();

while (resultSet.next()) {

for (int i = 1; i <= count; ++i)

System.out.println(resultSet.getObject(i));

}

}

public void usePrepareStatementUpdate() throws SQLException {

String sql = "update db set id = 3,name = '555' where id = 1";

PreparedStatement preparedStatement = connection.prepareStatement(sql);

preparedStatement.executeUpdate();

}

(5)事务

Connection有一个setAutoCommit()方法,把它设置成false即可关闭自动提交,所有语句准备好后,一次性使用commit()提交即可.

实现回滚可以配合SavePoint使用.

3.C3P0

(1)jar包

两个:

(2)配置文件

src下创建一个叫c3p0.properties的文件:

c3p0.driverClass=com.mysql.cj.jdbc.Driver

c3p0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/db

c3p0.user=aa

c3p0.password=aa123bb

这里按自己需要更改即可.

(3)工具类

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;

public class DbUtil

{

private static ComboPooledDataSource C3P0dataSource = new ComboPooledDataSource("c3p0.properties");

public static void releaseConnection(Connection connection)

{

try

{

if(connection != null)

connection.close();

}

catch (Exception e)

{

e.printStackTrace();

}

}

public static Connection getC3P0Connection()

{

try

{

return C3P0dataSource.getConnection();

}

catch (Exception e)

{

e.printStackTrace();

}

return null;

}

}

4.DBCP

(1)jar包

三个:

(2)配置文件

src下新建dbcp.properties:

driver=com.mysql.cj.jdbc.Driver

url=jdbc:mysql://127.0.0.1:3306/db

username=aa

password=k041400r

initialSize=10

maxActive=50

maxIdle=15

minIdle=10

maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=utf8

defaultAutoCommit=true

分别是驱动,url,用户名,密码,初始化连接数,最大连接数,最大空闲连接数,最小空闲连接数,最大等待实际,连接属性(这里设置了编码),自动提交.

(3)工具类

import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.io.InputStream;

import java.sql.Connection;

import java.util.Properties;

import javax.sql.DataSource;

public class DbUtil {

private static DataSource DBCPdataSource;

static {

try {

InputStream inputStream = DbUtil.class.getClassLoader().getResourceAsStream("dbcp.properties");

Properties properties = new Properties();

properties.load(inputStream);

DBCPdataSource = BasicDataSourceFactory.createDataSource(properties);

} catch (Exception e) {

e.printStackTrace();

}

}

public static Connection getDBCPConnection() {

try {

return DBCPdataSource.getConnection();

} catch (Exception e) {

e.printStackTrace();

}

return null;

}

public static void releaseConnection(Connection connection) {

try {

if (connection != null)

connection.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}

首先加载属性文件,再使用Properties的load方法将其加载到一个Properties对象中,最后交给BasicDataSourceFactory处理.

5.源码

包含了jar包,配置文件,sql文件与测试代码.