day030-03jdbc_dbcp_c3p0
dbcp properties文件读取创建数据源
dbcp需要导入包 dbcp.jar pool.jar c3p0需要导入包c3p0.jar mchange----.jar
dbcpconfig.properties文件代码
#连接设置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username=kkkk
password=123456
#初始化连接
initialSize=10
#最大连接数
maxActive=50
#最大空闲连接
minIdle=20
#最小空闲连接
maxIdle=5
#超时毫秒
maxWait=60000
#jdbc驱动链接属性格式 属性名=property;
connectionProperties=useUnicode-true;characterEncoding=UTF8;serverTimezone=UTC
#指定创建的连接自动提交
defaultAutoCommit=true
#设置驱动状态只读read-only
defaultReadOnly=
#指定事务级别 读未提交的 READ_UNCOMMIT 读已提交的
defaultTransactionIsolation=
c3p0-config.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="initialPoolSize">30</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="oracle">
<property name="driverClass">
oracle.jdbc.driver.OracleDriver
</property>
<property name="jdbcUrl">
jdbc:oracle:thin:@00.00.00.00:1521/ORCL
</property>
<property name="user">lll</property>
<property name="password">llllll</property>
</named-config>
<named-config name="sqlserver">
<property name="driverClass">
net.sourceforge.jtds.jdbc.Driver
</property>
<property name="jdbcUrl">
jdbc:jtds:sqlserver://00.00.000.0000:1534;DatabaseName=kkkk
</property>
<property name="user">kkk</property>
<property name="password">123456</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>
<named-config name="mysqlLocal"><!--mariadb 和mysql-->
<property name="driverClass">
com.mysql.cj.jdbc.Driver
</property>
<property name="jdbcUrl"><!--jdbc:mysql://localhost:3306;DatabaseName=test-->
jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
</property>
<property name="user">hhhhj</property>
<property name="password">21122224</property>
</named-config>
</c3p0-config>
运行程序demo
package com.self.jdbc;
import com.self.utils.C3p0Config;
import com.self.utils.DbcpConfig;
import com.self.utils.JdbcUtils;
import java.sql.*;
/**
* @ Author ：wwwzhqwww
* @ Date ：Created in 18:40 2021/2/17
* @ Description：
* @ Modified By：
* @Version: $version$
*/
public class JdbcDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// Connection coon = JdbcUtils.getConnection();//jdbc调用连接
// Connection coon = DbcpConfig.getConnection();//dbcp调用连接
Connection coon = C3p0Config.getConnection();//c3p0调用连接
String sql = "select * from student where id = ?";
PreparedStatement preparedStatement = coon.prepareStatement(sql);
preparedStatement.setInt(1,1);
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()){
System.out.println(rs.getString("name"));
}
JdbcUtils.relase(coon,preparedStatement,rs);
//加载驱动
// Class.forName("com.mysql.jdbc.Driver");//已经弃用Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'
// Class.forName("com.mysql.cj.jdbc.Driver");
//用户信息和url
//useUnicode=trueunicode编码支持中文&characterEncoding=utf8编码格式&useSSL=true使用安全连接&serverTimezone=UTC时区设置mysql8.0以上增加新特性
// String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
// String username = "kkkk";
// String pwd = "123456
";
//连接成功
// Connection connection = DriverManager.getConnection(url,username,pwd);
//执行sql对象
// Statement statement = connection.createStatement();
// statement.execute();//执行任何sql
// statement.executeQuery();//返回数据列表
// statement.executeUpdate();//返回更新成功行数
//对象执行sql
// String sql = "select * from student";
// ResultSet resultSet = statement.executeQuery(sql);
// while (resultSet.next()){
// System.out.println("id:"+resultSet.getObject("id"));
// System.out.println("name:"+resultSet.getObject("name"));
// }
//释放连接
// resultSet.close();
// statement.close();
// connection.close();
}
}
JdbcUtils.getConnection()
package com.self.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @ Author ：wwwzhqwww
* @ Date ：Created in 19:34 2021/2/17
* @ Description：
* @ Modified By：
* @Version: $version$
*/
public class JdbcUtils {
private static String driver ;
private static String url ;
private static String userNmae ;
private static String pwd ;
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("com/self/db.properties");
Properties properties = new Properties();
properties.load(is);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
userNmae = properties.getProperty("userName");
pwd = properties.getProperty("pwd");
Class.forName(driver);
}catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,userNmae,pwd);
}
public static void relase(Connection con, Statement sta, ResultSet res) throws SQLException {
res.close();
sta.close();
con.close();
System.out.println("lianjieyiguanbi");
}
}
DbcpConfig.getConnection()
package com.self.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @ Author ：wwwzhqwww
* @ Date ：Created in 11:12 2021/2/18
* @ Description：
* @ Modified By：
* @Version: $version$
*/
public class DbcpConfig {
private static DataSource dataSource;
static {
try{
//DBCP连接数据源 加载properties文件 jdbc.jar包 pool.jar包
InputStream in = DbcpConfig.class.getClassLoader().getResourceAsStream("com/self/dbcpconfig.properties");
Properties pp = new Properties();
pp.load(in);
//创建数据源
dataSource = BasicDataSourceFactory.createDataSource(pp);//工厂模式
//c3p0连接数据源 加载c3p0 xml文件 c3p0.jar
// dataSource = new ComboPooledDataSource();
// dataSource.setconn
}catch (Exception e){
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void relase(Connection coon, Statement sta, ResultSet res) throws SQLException {
res.close();
sta.close();
coon.close();
}
}
C3p0Config.getConnection();//c3p0调用连接
package com.self.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @ Author ：wwwzhqwww
* @ Date ：Created in 12:19 2021/2/18
* @ Description：
* @ Modified By：
* @Version: $version$
*/
public class C3p0Config {
private static DataSource dataSource;
static {//c3p0-config.xml文件必须放在src根目录下，放在子目录下找不到
dataSource = new ComboPooledDataSource("mysqlLocal");//xml文件会自动加载，comnopooledDataSource会找到c3p0配置文件获取数据
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
}
