DBUtils的简单使用

①引入所需的jar包

②引入C3P0的配置文件

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>
    <default-config>
        <property name="automaticTestTable">con_test</property>
        <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>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost/bank?user=root&amp;password=root</property>
    </default-config>
</c3p0-config>

③创建辅助类

package com.yxfyg.util;

import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCUtil {
    
    private static ComboPooledDataSource dataSource = null;
    
    static {
        dataSource = new ComboPooledDataSource();
    }
    
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    public static ComboPooledDataSource getDataSource() {
        return dataSource;
    }
    
    public static void release(ResultSet rs,Statement st,Connection conn) {
        closeRs(rs);
        closeSt(st);
        closeConn(conn);
    }
    
    public static void release(Statement st,Connection conn) {
        closeSt(st);
        closeConn(conn);
    }
    
    private static void closeRs(ResultSet rs) {
        try {
            if(rs != null) {
                rs.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            rs = null;
        }
    }
    
    private static void closeSt(Statement st) {
        try {
            if(st != null) {
                st.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            st = null;
        }
    }
    
    private static void closeConn(Connection conn) {
        try {
            if(conn != null) {
                conn.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            conn = null;
        }
    }
}

package com.yxfyg.bean;

public class Account {
    
    private int id;
    
    private String name;
    
    private int money;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getMoney() {
        return money;
    }

    public void setMoney(int money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account [id=" + id + ", name=" + name + ", money=" + money + "]";
    }
        
}

④开始使用DBUtils

package com.yxfyg.test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.yxfyg.bean.Account;

public class MainTest {
    
    @Test
    public void test() {
        
        //1.创建数据库连接池
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        //2.以数据库连接池作为参数，创建QueryRunner对象
        QueryRunner queryRunner = new QueryRunner(dataSource);
        
        try {
            //增、删、改都使用update方法，查使用query方法
            //插入一条数据
            queryRunner.update("insert into account values(null,?,?)","Lucy",2000);
            
            //修改一条数据
            queryRunner.update("update account set money = money - ? where name = ?",300,"Lucy");
            
            //删除一条数据
            queryRunner.update("delete from account where name = ?","Lucy");
            
            //查询一条数据(使用匿名内部类，自己实现方法)
            Account account = queryRunner.query("select * from account where name = ?", new ResultSetHandler<Account>() {

                @Override
                public Account handle(ResultSet rs) throws SQLException {
                    Account account = new Account();
                    while(rs.next()) {
                        account.setId(rs.getInt("id"));
                        account.setName(rs.getString("name"));
                        account.setMoney(rs.getInt("money"));
                    }
                    return account;
                }
            },"Lucy");
            
            System.out.println(account.toString());
            
            //查询一条数据（使用接口ResultSetHandler的实现类BeanHandler）
            account = queryRunner.query("select * from account where name = ?", new BeanHandler<Account>(Account.class) ,"Lucy");
            System.out.println(account.toString());
            
            //查询多条数据（使用接口ResultSetHandler的实现类BeanListHandler）
            List<Account> list = queryRunner.query("select * from account", new BeanListHandler<Account>(Account.class));
            for (Account a : list) {
                System.out.println(a.toString());
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }    
    }
    
}