数据库连接池
一、C3P0数据库连接池
1、导包
或者
- c3p0-0.9.2.1.jar
- mysql-connector-java-5.1.20.jar
- mchange-commons-java-0.2.3.4.jar
或者在idea, 建好项目中pom.xml中导入
2.数据库的配置文件
建一个文件夹resources,在文件夹上右击选择Mark Directory as中的Resources root
在resources文件夹新建c3p0-config.xml的配置文件
(1)默认数据库连接池配置(MySQL)
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/java49?useUnicode=true&characterEncoding=UTF-8</property>
<property name="user">root</property>
<property name="password">root</property>
<!--初始化获取三个连接,取值在maxPoolSize与minPoolSize-->
<property name="initialPoolSize">5</property>
<!--最大空闲时间,指定的时间内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
<property name="maxIdleTime">60</property>
<!--连接池中保留的最大连接数。 -->
<property name="maxPoolSize">10</property>
<!--连接池中保留的最大连接数。 -->
<property name="minPoolSize">2</property>
<!--最大链接数-->
<property name="maxStatements">10</property>
</default-config>
</c3p0-config>
(2) 自定义数据库连接池配置,通过named-config标签的name属性来使用,ComboPooledDataSource必须有参数(MySQL)
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<!--mysql连接,自定义数据库连接池配置,通过named-config标签的name属性来使用 -->
<named-config name="mysql">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///java49</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- 连接池参数 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">8</property>
<property name="checkoutTimeout">1000</property>
</named-config>
<c3p0-config>
(3)Orcle数据库连接池配置
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<!--mysql连接,自定义数据库连接池配置,通过named-config标签的name属性来使用 -->
<named-config name="mysql">
<property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
<property name="jdbcUrl">jdbc:oracle:thin:@localhost:1521:orcl</property>
<property name="user">zhi</property>
<property name="password">zhi</property>
<!-- 连接池参数 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">8</property>
<property name="checkoutTimeout">1000</property>
</named-config>
<c3p0-config>
3、数据连接池工具类
public class MysqlUtils {
//默认读取配置文件c3p0-config.xml,后面无参读取默认的,有参读取自定义数据库连接池
public static ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql");
//从数据库连接池中取一个连接;
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void main(String[] args) {
System.out.println(getConnection());
}
}
4、写简单增删改查代码;
public class CRUD {
public static QueryRunner runner = new QueryRunner(JdbcUtils.dataSource);
/**
* 查询记录条数
* @return
*/
public static int getAccount(){
int cnt = 0;
try {
long count = (long)runner.query("select count(*) from user",new ScalarHandler());
cnt = (int)count;
} catch (SQLException e) {
e.printStackTrace();
}
return cnt;
}
/**
* 查询所有
* @return
*/
public static List<User> getAll(){
try {
return runner.query("select username,birthday,sex,address from user",new BeanListHandler<>(User.class));
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 查询一条
* @return
*/
public static User getOneUser(int id){
try {
return runner.query("select id,username,birthday,sex,address from user where id=?",new BeanHandler<>(User.class),id);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 增加用户
* @param user
*/
public static void addUser(User user){
try {
runner.update("insert into user(username,birthday,sex,address) values(?,?,?,?)",
user.getUsername(),user.getBirthday(),user.getSex(),user.getAddress());
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 删除一个用户
* @param id
*/
public static void delUser(int id){
try {
runner.update("delete from user where id = ?",id);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 修改用户
* @param user
*/
public static void getupdateUser(User user){
try {
runner.update("update user set username=?,birthday=?,sex=?,address=? where id=?",
user.getUsername(),user.getBirthday(),user.getSex(),user.getAddress(),user.getId());
} catch (SQLException e) {
e.printStackTrace();
}
}
}
6、测试类
@Test
public void testAlluser(){
CRUD crud = new CRUD();
System.out.println(getAccount());
List<User> users = crud.getAll();
for (User user: users) {
System.out.println(user);
}
}
@Test
public void testOneUser(){
CRUD crud = new CRUD();
System.out.println(crud.getOneUser(74));
}
@Test
public void testaddUser(){
CRUD crud = new CRUD();
User user = new User();
user.setUsername("张三");
user.setBirthday("2021-05-06");
user.setSex("女");
user.setAddress("湖南");
crud.addUser(user);
}
@Test
public void testdelUser(){
CRUD crud = new CRUD();
crud.delUser(73);
}
@Test
public void testupUser(){
CRUD crud = new CRUD();
User user = new User();
user.setId(74);
user.setUsername("王五");
user.setBirthday("2021-05-06");
user.setSex("女");
user.setAddress("杭州");
crud.getupdateUser(user);
}
5、一次性插入100条数据
public class Demo1 {
QueryRunner qr = new QueryRunner(MysqlUtils.dataSource); // mysql
public int add(){
int a = 0;
for (int i = 0; i < 100; i++) {
try {
a +=qr.update("insert into t_users(t_uid,t_uname, t_upwd, t_uage,t_usex) values(?,?,?,?,?)", i,"aaa","123",18,"nan");
} catch (SQLException e) {
e.printStackTrace();
}
}
return a;
}
}
二、Druid数据库连接池
1、导入druid-1.0.9.jar包。
2、建一个文件夹resources,在文件夹上右击选择Mark Directory as中的Resources root
在resources文件夹新建druid.properties的配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysql_test_data?useUnicode=true&characterEncoding=utf-8
username=root
password=root
#初始化申请的连接数量
initialSize=5
#最大连接数
maxActive=10
#超时连接等待时间
maxWait=3000
3、数据连接池工具类
/**
* druid 连接池工具类
*/
public class JdbcUtils {
private static DataSource dataSource;
static {
try {
//加载配置文件
Properties pro = new Properties();
InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("resources/druid.properties");
pro.load(is);
try {
dataSource = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
//获取连接对象
public static Connection getConnection() throws SQLException {
Connection connection = dataSource.getConnection();
return connection;
}
//关闭资源
public static void close(ResultSet set, Statement statement, Connection connection) {
if (set != null) {
try {
set.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();//归还连接
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//获取连接池对象
public static DataSource getDataSource() {
return dataSource;
}
}
4、通过工厂类获取数据库连接对象
public class DruidDemo {
public static void main(String[] args) {
DataSource dataSource = null;
List<Account> accounts = new ArrayList<>();
try {
//加载配置文件
Properties pro = new Properties();
InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("resources/druid.properties");
pro.load(is);
try {
//通过工厂类来获取数据库连接池对象
dataSource = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
5、测试方法
public static void main(String[] args) {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
List<Account> accounts = new ArrayList<>();
try {
accounts = qr.query("select id, name,money from account", new BeanListHandler<>(Account.class));
} catch (SQLException e) {
e.printStackTrace();
}
for (Account account: accounts) {
System.out.println(account);
}
}