c3p0 shell 连接mysql数据库_c3p0连接MySQL数据库

前言

这几天在看连接数据库的东西,之前一直用dbutils,都忘了有数据库连接池这个东西.然后就查了一下,看到好多大神都写过关于和方面的文章.其中有好多.虽然好多都是只写了连接数据库的xml文件的配置,但是dbutils也不是放那看的啊!!!于是,我就决定把他俩在MySQL8的条件下结合一下.不多说

开搞

1搞到jar包

2.在eclipse中创建项目并导入jar包

3.在src文件夹下创建一个c3p0-config.xml文件

4.在c3p0-config.xml文件写如下配置

com.mysql.cj.jdbc.Driver

jdbc:mysql://localhost:3306/数据库名?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf-8&autoReconnect=true

用户名

密码

5

15

20

5

com.mysql.jdbc.Driver

jdbc:mysql://localhost:3306/数据库名?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf-8&autoReconnect=true

用户名

密码

5

15

20

5

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 query = qr.query(sql, new BeanListHandler(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 query = qr.query(sql, new ColumnListHandler("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 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());// 查询所有就要封装进BeanListHandler

// 执行SQL

System.out.println(query);

// 处理结果

} catch (Exception e) {

e.printStackTrace();

}

}

@Test

public void findMapListHanlder() {

try {

// 编写SQL

String sql = "select * from student";

// 设置参数

List> query = qr.query(sql, new MapListHandler());// 查询所有就要封装进BeanListHandler

// 执行SQL

for (Map 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 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.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 query = qr.query(conn, sql, new BeanListHandler(Student.class));// 查询所有就要封装进BeanListHandler

// 执行SQL

for (Student student : query) {

System.out.println(student);

}

// 处理结果

} catch (Exception e) {

e.printStackTrace();

}

}

}