c3p0操作MySQL数据库

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

 

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

?
1
2
3
4
5
6
7
8
9
10
##创建数据库并指定编码
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)     

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<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)     

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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)     

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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)     

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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);
        }
    }
}

posted on 2016-04-15 17:22  cn三少<script></script>  阅读(2181)  评论(0)    收藏  举报

导航