Java hutool搭配c3p0数据库实例

1、pom

<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.5</version>
        </dependency>

<dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.4</version>
        </dependency>

2、配置文件

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/dbname?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
spring.datasource.username=root
spring.datasource.password=xxx
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

3、c3p0配置

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.beans.PropertyVetoException;

@Configuration
public class C3P0Config {
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.url}")
    private String url;

    /**
     * 生成c3p0连接池的bean
     *
     * @return
     * @throws PropertyVetoException
     */
    @Bean
    public ComboPooledDataSource createDataSource() throws PropertyVetoException {
        //创建c3p0连接池对象
        ComboPooledDataSource dataSource = new ComboPooledDataSource();

        /*基本配置*/
        dataSource.setDriverClass(driverClassName); //数据库驱动类
        dataSource.setUser(username); //数据库用户名
        dataSource.setPassword(password); //数据库用户密码
        dataSource.setJdbcUrl(url);//数据库连接URL

        /*配置c3p0连接池的私有属性*/
        dataSource.setMaxPoolSize(30); //连接池最大连接数
        dataSource.setMinPoolSize(10); //连接池最小连接数
        dataSource.setAutoCommitOnClose(false); //关闭连接后不自动提交
        dataSource.setCheckoutTimeout(10000); //连接超时时间
        dataSource.setAcquireRetryAttempts(2); //连接失败允许重试次数

        //返回连接池对象
        return dataSource;
    }
}

4、hutool数据库demo

private void testC3p0() throws Exception {
        Connection connection = comboPooledDataSource.getConnection();
//        Statement statement = connection.createStatement();
//        ResultSet resultSet = statement.executeQuery("select * from t_user");
//        while (resultSet.next()) {
//            log.info("{} {} {} {}", resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4));
//        }

        SqlConnRunner sqlConnRunner = SqlConnRunner.create(comboPooledDataSource);

        /*
        构造where条件
Entity where = Entity.create(TABLE_NAME).set(key1, value1).set(key2, value2)
相当于：
WHERE key1 = value1 and key2 = value2
.set()可多次使用
value默认以=号精准匹配，如需范围性匹配，可使用<、>、like、in等进行构造，例子：Entity.create(TABLE_NAME).set(key,"< value")
         */


//        List<Entity> inserts = new ArrayList<>();
//        for (int i = 0; i < 10; i++) {
//            Entity item = new Entity("t_user").set("name", "u" + (i + 1)).set("passwd", GuidUtils.getUUID32());
//            inserts.add(item);
//        }
//        int[] res = sqlConnRunner.insert(connection, inserts);
//        for (int item : res) {
//            log.info("{}", item);
//        }

        Map<String, Object> map = new HashedMap();
        map.put("name", "%u%");
        List<Entity> list = Db.use(comboPooledDataSource).query("select * from t_user where name like @name", map);

//        Entity entity = new Entity("t_user"); //.set("name", "like %b%");
//        List<Entity> list = sqlConnRunner.find(connection, entity);
        for (Entity item : list) {
            log.info("{}", item);
        }

    }

5、其他参考

1. 构造where条件
Entity where = Entity.create(TABLE_NAME).set(key1, value1).set(key2, value2)
相当于：
WHERE key1 = value1 and key2 = value2
.set()可多次使用
value默认以=号精准匹配，如需范围性匹配，可使用<、>、like、in等进行构造，例子：Entity.create(TABLE_NAME).set(key,"< value")

2. 基础使用
where为构造where条件，参照应用第1点，构造where条件
Db.use(Group)如果配置多源，则用group定义使用哪个数据库，Group选填，不填则使用默认配置
Db.use().insert(where)插入数据
Db.use().insertForGeneratedKey(where)插入数据并返回自增主键
Db.use().del(where)删除数据，where条件不能为空，防止全表删除，如需相关操作，需调用execute方法
Db.use().update(where1,where2)更新数据，where1为更新的数据，where2为更新条件
Db.use().findAll(TABLE_NAME)查询全字段数据
Db.use().findAll(where)按条件进行查询，含模糊查询
Db.use().findLike(TABLE_NAME, 条件,值, LikeType.Contains)模糊查询
Db.use().page(where, page)分页查询

3. 执行自定义sql语句
Db.use().query(sql)查询
Db.use().execute(sql)增删改

支持?占位符，例如：
Db.use().execute("insert into user values (?, ?, ?)", value1, value2, value3)
相当于
Db.use().execute("insert into user values (value1, value2,value3)")
支持使用:name、?name、@name命名占位符
Map<String, Object> paramMap = MapUtil.builder(name1,(Object) value1).put(name2, value2).put(name3, value3).build()
Db.use().query("select * from table where key=@name1 and key2= @name2 and key3= @name3", paramMap)
相当于
Db.use().query("select * from table where key=value1 and key2=value2 and key3=value3")