JDBC+C3P0+DBCP基本使用

1.概述

這篇文章主要說(shuō)了JDBC的基本使用,包括Statement,PreparedStatement,JDBC的連接,MySQL創(chuàng)建用戶創(chuàng)建數(shù)據(jù)表,C3P0的連接與配置,DBCP的連接與配置.

創(chuàng)新互聯(lián)公司專業(yè)為企業(yè)提供臨縣網(wǎng)站建設(shè)、臨縣做網(wǎng)站、臨縣網(wǎng)站設(shè)計(jì)、臨縣網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、臨縣企業(yè)網(wǎng)站模板建站服務(wù)，十載臨縣做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

2.mysql的處理

這里的JDBC使用Mysql作為DBMS,請(qǐng)先安裝Mysql,未安裝的請(qǐng)點(diǎn)擊這里下載,安裝教程在這里,作者使用的Mysql的8.0.17版本.

(1)新建用戶

隨便新建一個(gè)用戶,比如這里作者新建的是aa,密碼是aa123bb.

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

(2)建立數(shù)據(jù)表

建立測(cè)試用的數(shù)據(jù)表與數(shù)據(jù)庫(kù).

create database db;
use db;

create table db
(
    id int PRIMARY key,
    name char(20)
);

(3)用戶權(quán)限

對(duì)剛才新建的用戶授權(quán):

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

2.JDBC

(1)jar包

8.0.17版本在這里

各個(gè)版本的在這里下載

(2)連接

首先注冊(cè)驅(qū)動(dòng),驅(qū)動(dòng)需要一個(gè)url,用戶名和密碼,用戶名和密碼是上一步創(chuàng)建好的,url包含ip地址,端口和數(shù)據(jù)庫(kù)的名字.

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的驅(qū)動(dòng)叫com.mysql.jdbc.Driver,新版本的叫com.mysql.cj.jdbc.Driver.還有就是url的格式:

jdbc:mysql://ip:port/database

(3)Statement

獲取數(shù)據(jù)庫(kù)連接后,使用createStatement方法創(chuàng)建Statement

對(duì)于select,使用Statement的executeQuery(sql),返回ResultSet
對(duì)于update,delete,insert,使用Statement的executeUpdate(sql)

其中sql是要執(zhí)行的sql語(yǔ)句,一個(gè)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);
}

這里對(duì)ResultSet使用的getMetaData,可以獲取結(jié)果集的各種類型信息,包括字段的類型,個(gè)數(shù),等等.

(4)PreparedStatement

PreparedStatement與Statement使用基本一樣.調(diào)用的時(shí)候先使用Connection的prepareStatement(sql)創(chuàng)建,然后

對(duì)于select,使用executeQuery(),返回一個(gè)ResultSet
對(duì)于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)事務(wù)

Connection有一個(gè)setAutoCommit()方法,把它設(shè)置成false即可關(guān)閉自動(dòng)提交,所有語(yǔ)句準(zhǔn)備好后,一次性使用commit()提交即可.
實(shí)現(xiàn)回滾可以配合SavePoint使用.

3.C3P0

(1)jar包

兩個(gè):

c3p0
mchange-commons

(2)配置文件

src下創(chuàng)建一個(gè)叫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包

三個(gè):

commons-dbcp
commons-logging
commons-pool

(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

分別是驅(qū)動(dòng),url,用戶名,密碼,初始化連接數(shù),最大連接數(shù),最大空閑連接數(shù),最小空閑連接數(shù),最大等待實(shí)際,連接屬性(這里設(shè)置了編碼),自動(dòng)提交.

(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方法將其加載到一個(gè)Properties對(duì)象中,最后交給BasicDataSourceFactory處理.

5.源碼

包含了jar包,配置文件,sql文件與測(cè)試代碼.

github
碼云

網(wǎng)站題目：JDBC+C3P0+DBCP基本使用
鏈接分享：http://jinyejixie.com/article36/jjhgsg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)、手機(jī)網(wǎng)站建設(shè)、網(wǎng)站內(nèi)鏈、關(guān)鍵詞優(yōu)化、面包屑導(dǎo)航、品牌網(wǎng)站設(shè)計(jì)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

JDBC+C3P0+DBCP基本使用

1.概述

2.mysql的處理

(1)新建用戶

(2)建立數(shù)據(jù)表

(3)用戶權(quán)限

2.JDBC

(1)jar包

(2)連接

(3)Statement

(4)PreparedStatement

(5)事務(wù)

3.C3P0

(1)jar包

(2)配置文件

(3)工具類

4.DBCP

(1)jar包

(2)配置文件

(3)工具類

5.源碼