C3P0
package com.sql.jbdc.datasource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class C3P0_ {
//方式1. 相关参数在程序中指定user,url,password等
@Test
public void testc3p0() throws IOException, PropertyVetoException, SQLException {
//1.创建一个数据源对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//2.通过配置文件 mysql.properties 获取相关信息
Properties properties = new Properties();
properties.load(new FileInputStream("src\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
//给数据源 comboPooledDataSource 设置相关信息
//注意：连接管理是由 comboPoolDateSource 来管理
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
//设置初始化连接数
comboPooledDataSource.setInitialPoolSize(10);
//最大连接数
comboPooledDataSource.setMaxPoolSize(50);
//测试连接池效率
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();//核心方法 DataSource 接口实现
System.out.println("连接成功");
connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end-start);
}
//使用配置文件模板的方式
//该文件制定了连接数据库和连接池的相关参数
@Test
public void testc3p0_() throws SQLException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("lixingrui");
Connection connection = comboPooledDataSource.getConnection();
System.out.println("连接成功");
connection.close();
}
}
Druid
package com.sql.jbdc.datasource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;
public class Druid_ {
@Test
public void testDruid() throws Exception {
//1.加入Druid jar包
//2.加入配置文件 到src目录
//3.创建Properties对象，读取配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("src\druid.properties"));
//4.创建一个指定参数的数据库连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
System.out.println("连接成功");
connection.close();
}
}
基于Druid数据库连接池工具类封装
package com.sql.jbdc.datasource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
//基于druid数据库连接池工具类
public class JDBCUtilsByDruid {
private static DataSource ds;
//在静态代码块完成 ds初始化
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//编写getConnection方法
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
//关闭连接(这里是直接放回连接池)
public static void close(ResultSet resultSet, Statement statement,Connection connection){
try {
if(resultSet != null){
resultSet.close();
}
if(statement != null){
statement.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
Druid工具类的使用
package com.sql.jbdc.datasource;
import com.sql.jbdc.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtilsByDruid_Use {
@Test
public void testSelect(){
//1.得到连接
Connection connection = null;
//2.组织一个sql
String sql = "select * from actor";
PreparedStatement preparedStatement = null;
ResultSet set = null;
//3.创建PreparedStatement 对象
try {
connection = JDBCUtilsByDruid.getConnection();
preparedStatement = connection.prepareStatement(sql);
//执行得到结果集
set = preparedStatement.executeQuery();
//遍历该结果集
while(set.next()){
int id = set.getInt("id");
String name = set.getString("name");
String phone = set.getString("phone");
String sex = set.getString("sex");
String borndate = set.getString("borndate");
System.out.println(id + "t" + name + "t" + borndate + "t" + sex +"t" + phone);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtilsByDruid.close(set, preparedStatement, connection);
}
}
}
ApDBUtils 使用
package com.sql.jbdc.datasource;
import java.util.Date;
public class Actor {
private int id;
private String name;
private String sex;
private Date borndate;
private String phone;
public Actor(int id, String name, String sex, Date borndate, String phone) {
this.id = id;
this.name = name;
this.sex = sex;
this.borndate = borndate;
this.phone = phone;
}
public Actor(){
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBorndate() {
return borndate;
}
public void setBorndate(Date borndate) {
this.borndate = borndate;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Actor{" +
"id=" + id +
", name='" + name + ''' +
", sex='" + sex + ''' +
", borndate=" + borndate +
", phone='" + phone + ''' +
'}';
}
}
package com.sql.jbdc.datasource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class ApDBUtils_ {
//使用apache-DBUtils 工具类 +druid 完成对表的crud操作
@Test
public void testQuerMany() throws SQLException {//返回结果是多行的情况
//1.得到连接 druid
Connection connection = JDBCUtilsByDruid.getConnection();
//2.使用 DBUtils 类和接口，先引入 DBUtils 相关的jar
//3.创建一个QueryRunner
QueryRunner queryRunner = new QueryRunner();
//4.就可以执行相关方法,返回ArrayList 结果集
String sql = "select * from actor where id >= ?";
//(1)query 方法就是执行sql 语句,得到resultest ---封装到 -->ArrayList 集合中
//(2)返回集合
//(3)connection:连接
//(4)sql:执行的sql语句
//(5)new BeanListHandler<>(Actor.class):在将resultset --> Actor 对象 -->封装到ArrayList
// 底层使用反射机制 去获取Actor 类的属性，然后进行封装
//(6)1 就是给 sql语句中的? 赋值，可以有多个值，因为是可变参数Object... params
//(7)底层得到的resultset ,会在query 关闭, 关闭PreparedStatment
List list =
queryRunner.query(connection,sql,new BeanListHandler<>(Actor.class),1);
for(Actor actor : list){
System.out.println(actor);
}
JDBCUtilsByDruid.close(null,null,connection);
}
}
