c3p0操作MySQL数据库

使用c3p0连接MySQL数据库并对MySQL数据库进行基本操作。

1. [文件] 数据库准备 ~ 226B 下载(64)

##创建数据库并指定编码
create database test character set utf8;
##切换数据库
use test;
##创建表格
create table user(
id int primary key auto_increment,
name varchar(50),
password varchar(50)
);

2. [文件] c3p0-config.xml ~ 1KB 下载(96)

<c3p0-config>
    <!-- 默认配置，如果没有指定则使用这个配置 -->
    <default-config>
        <property name="checkoutTimeout">30000</property>
        <property name="idleConnectionTestPeriod">30</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>
        <user-overrides user="test-user">
            <property name="maxPoolSize">10</property>
            <property name="minPoolSize">1</property>
            <property name="maxStatements">0</property>
        </user-overrides>
    </default-config>
    <!-- 命名的配置 -->
    <named-config name="test">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///test?useUnicode=true&amp;characterset=utf-8
        </property>
        <property name="user">root</property>
        <property name="password">1234</property>
        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">20</property>
        <property name="minPoolSize">10</property>
        <property name="maxPoolSize">40</property>
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config> 

3. [文件] C3P0Utils.java ~ 615B 下载(85)

import java.sql.Connection;
import java.sql.SQLException;
 
import javax.sql.DataSource;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
 
/**
 * c3p0连接MySQL数据库工具包
 * 
 * @author 冰雨凌風
 * 
 */
public class C3P0Utils {
    // 定义全局变量
    private static ComboPooledDataSource cpds;
    // 静态代码块
    static {
        cpds = new ComboPooledDataSource("test");
    }
 
    // 获得数据源
    public static DataSource getDataSource() {
        return cpds;
    }
 
    // 获得连接
    public static Connection getConnection() throws Exception {
        return cpds.getConnection();
    }
}

4. [文件] User.java ~ 583B 下载(54)

public class User {
    private String id;
    private String name;
    private String password;
 
    public String getId() {
        return id;
    }
 
    public void setId(String id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", password=" + password
                + "]";
    }
 
}

5. [文件] C3P0Test.java ~ 2KB 下载(64)

import java.sql.SQLException;
 
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;
 
/**
 * 利用dbutils对数据库进行CURD操作
 * 
 * @author 冰雨凌風
 * 
 */
public class C3P0Test {
    /**
     * 在数据库中插入数据
     */
    @Test
    public void add() {
        try {
            // 创建执行者
            QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
            // 准备sql语句
            String sql = "insert into user(name,password) value(?,?)";
            // 准备参数
            Object[] params = { "admin", "1234" };
            // 执行语句
            runner.update(sql, params);
            System.out.println("插入数据成功！");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
 
    /**
     * 查询数据
     */
    @Test
    public void query() {
        try {
            // 创建执行者
            QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
            // 准备sql语句
            String sql = "select * from user";
            // 执行语句
            User user = runner.query(sql, new BeanHandler<User>(User.class));
            System.out.println(user);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
 
    /**
     * 更新数据
     */
    @Test
    public void update() {
        try {
            // 创建执行者
            QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
            // 准备sql语句
            String sql = "update user set password=? where name=?";
            // 准备参数
            Object[] params = { "123456", "admin" };
            // 执行语句
            runner.update(sql, params);
            System.out.println("更新数据成功！");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
 
    /**
     * 删除数据
     */
    @Test
    public void delete() {
        try {
            // 创建执行者
            QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
            // 准备sql语句
            String sql = "delete from user";
            // 执行语句
            runner.update(sql);
            System.out.println("删除数据成功");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}