前言
这几天在看连接数据库的东西,之前一直用dbutils,都忘了有数据库连接池这个东西.然后就查了一下,看到好多大神都写过关于和方面的文章.其中有好多.虽然好多都是只写了连接数据库的xml文件的配置,但是dbutils也不是放那看的啊!!!于是,我就决定把他俩在MySQL8的条件下结合一下.不多说
开搞
1搞到jar包
c3p0的jar包
mchange-commons-java(c3p0依赖jar包)
2.在eclipse中创建项目并导入jar包
3.在src文件夹下创建一个c3p0-config.xml文件
4.在c3p0-config.xml文件写如下配置
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/数据库名?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf-8&autoReconnect=true</property>
<property name="user">用户名</property>
<property name="password">密码</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">15</property>
<property name="maxPoolSize">20</property>
<property name="minPoolSize">5</property>
</default-config>
<!-- 命名的配置,可以通过方法调用实现 -->
<named-config name="lyf">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/数据库名?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf-8&autoReconnect=true
</property>
<property name="user">用户名</property>
<property name="password">密码</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">15</property>
<property name="maxPoolSize">20</property>
<property name="minPoolSize">5</property>
</named-config>
</c3p0-config>
5.两种连接方式
<!--默认方式-->
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class Conn {
private static ComboPooledDataSource cpc = new ComboPooledDataSource();
public static DataSource getDataSource() {
return cpc;
}
// 获取一个连接
/* public static Connection getConnection() throws SQLException {
return cpc.getConnection();
}*/
}
<!-- 命名的配置,可以通过方法调用实现 -->
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class Conn {
private static ComboPooledDataSource cpc = new ComboPooledDataSource("lyf");
public static DataSource getDataSource() {
return cpc;
}
// 获取一个连接
/* public static Connection getConnection() throws SQLException {
return cpc.getConnection();
}*/
}
6.使用,由于只是连接数据库的方式不一样,其余的东西好多还是一样的所以上代码比较直观
package org.vector.c3p0bycode;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import org.vector.domain.Student;
public class C3p0Test {
// 创建语句执行者
private QueryRunner qr = new QueryRunner(Conn.getDataSource());
@Test
public void findAll() {
try {
// 编写SQL
String sql = "select * from student";
// 设置参数
List<Student> query = qr.query(sql, new BeanListHandler<Student>(Student.class));// 查询所有就要封装进BeanListHandler
// 执行SQL
for (Student student : query) {
System.out.println(student);
}
// 处理结果
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void findColumnListHandler() {
try {
// 编写SQL
String sql = "select * from student";
// 设置参数
List<String> query = qr.query(sql, new ColumnListHandler<String>("sname"));// 查询所有就要封装进BeanListHandler
// 执行SQL
for (String string : query) {
System.out.println(string);
}
// 处理结果
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void findArrayListHandler() {
try {
String sql = "select * from student";
// 设置参数
List<Object[]> query = qr.query(sql, new ArrayListHandler());// 查询所有就要封装进BeanListHandler
// 执行SQL
for (Object[] string : query) {
System.out.println(string.toString());
}
// 处理结果
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void findArrayHandler() {
try {
// 编写SQL
String sql = "select * from student where sid = ?";
// 设置参数
Object[] query = qr.query(sql, new ArrayHandler(), 6);// 查询所有就要封装进BeanListHandler
// 执行SQL
for (Object string : query) {
System.out.println(string.toString());
}
// 处理结果
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void findCount() {
try {
// 编写SQL
String sql = "select count(*) from student";
// 设置参数
Long query = qr.query(sql, new ScalarHandler<Long>());// 查询所有就要封装进BeanListHandler
// 执行SQL
System.out.println(query);
// 处理结果
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void findMapListHanlder() {
try {
// 编写SQL
String sql = "select * from student";
// 设置参数
List<Map<String, Object>> query = qr.query(sql, new MapListHandler());// 查询所有就要封装进BeanListHandler
// 执行SQL
for (Map<String, Object> map : query) {
for (Object object : map.keySet()) {
System.out.println(map.get(object));
}
}
// 处理结果
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void findMapHanlder() {
try {
// 编写SQL
String sql = "select * from student where sid = ?";
// 设置参数
Map<String, Object> query = qr.query(sql, new MapHandler(), 6);// 查询所有就要封装进BeanListHandler
// 执行SQL
for (Object object : query.keySet()) {
System.out.println(query.get(object));
}
// 处理结果
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void findOne() {
try {
// 编写SQL
String sql = "select * from student where sid = ? ";
// 设置参数
Student query = qr.query(sql, new BeanHandler<Student>(Student.class), 32);// 查询所有就要封装进BeanListHandler
// 执行SQL
System.out.println(query);
// 处理结果
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void insert() {
try {
// 编写SQL
String sql = "insert into student (sname,sex,classes) values (?,?,?)";
// 设置参数
String sname = "sname";
String sex = "1";
int classes = 12346;
// 执行SQL
int update = qr.update(sql, sname, sex, classes);
// 处理结果
System.out.println(update);
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void delete() {
try {
// 编写SQL
String sql = "delete from student where sname = ?";
// 设置参数
String sname = "sname";
// 执行SQL
int update = qr.update(sql, sname);
// 处理结果
System.out.println(update);
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void update() {
try {
// 编写SQL
String sql = "update student set sname = ? ,sex = ? , age = ? ,snumber = ? where sid = ?";
// 设置参数
int sid = 32;
String sname = "三生三世";
String sex = "妖";
int age = 258;
int classes = 121;
String snumber = "15457884";
// 执行SQL
int update = qr.update(sql, sname, sex, age, snumber, sid);
// 处理结果
System.out.println(update);
} catch (Exception e) {
e.printStackTrace();
}
}
}
注:用java代码配置连接方式(个人不推荐,比较不好维护,也不直观)
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class ConnectionByCode {
private static ComboPooledDataSource cpds = new ComboPooledDataSource();
private static void configDataSource() {
try {
cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
cpds.setJdbcUrl(
"jdbc:mysql://localhost:3306/students?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf-8&autoReconnect=true");//这里注意一下,&没有这东西
cpds.setUser("root");
cpds.setPassword("123456");
cpds.setAcquireIncrement(10);
cpds.setInitialPoolSize(10);
cpds.setMinPoolSize(5);
cpds.setMaxPoolSize(20);
} catch (PropertyVetoException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection conn = null;
try {
configDataSource();
conn = cpds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
演示的代码
import java.sql.Connection;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import org.vector.domain.Student;
public class C3p0Test {
private Connection conn = ConnectionByCode.getConnection();
// 创建语句执行者
private QueryRunner qr = new QueryRunner();
@Test
public void findAll() {
try {
// 编写SQL
String sql = "select * from student";
// 设置参数
List<Student> query = qr.query(conn, sql, new BeanListHandler<Student>(Student.class));// 查询所有就要封装进BeanListHandler
// 执行SQL
for (Student student : query) {
System.out.println(student);
}
// 处理结果
} catch (Exception e) {
e.printStackTrace();
}
}
}
网友评论