Spring 数据库连接池

      常用的数据库连接池：C3P0，DBCP，Druid。

POM.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.company</groupId>
    <artifactId>SpringIOC_01</artifactId>
    <version>1.0-SNAPSHOT</version>

<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.8.1</version>
            <configuration>
                <target>1.8</target>
                <source>1.8</source>
                <encoding>utf-8</encoding>
            </configuration>
        </plugin>
    </plugins>
</build>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.4</version>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>7.2.2.jre8</version>
        </dependency>
    </dependencies>
</project>

POJO目录下的Account.java

public class Account {
    private int id;
    private String name;
    private float 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 float getMoney() {
        return money;
    }

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

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

DBCP

public class DBCPTest {
    private String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private String url="jdbc:sqlserver://localhost:1433;DatabaseName=testdb";
    private String username="sa";
    private String password="123.abc";

    private QueryRunner queryRunner=null;

    @Before
    public void before(){
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        queryRunner = new QueryRunner(dataSource);
    }

    @Test
    public void queryAccountByIdTest() throws Exception{
        String sql="select * from account where id=?";
        Object[] param={2};
        Account account = queryRunner.query(sql, new BeanHandler<>(Account.class), param);
        System.out.println(account);
    }

    @Test
    public void queryAccountListTest() throws Exception{
        String sql="select * from account";
        List<Account> accountList = queryRunner.query(sql, new BeanListHandler<>(Account.class));
        accountList.forEach(System.out::println);
    }

    @Test
    public void insertAccountTest() throws Exception{
        String sql="insert into account(name,money) values(?,?)";
        Object[] objects={"smiles","3000"};
        int update = queryRunner.update(sql, objects);
        System.out.println("inserted rows:"+update);
    }

    @Test
    public void updateAccountTest() throws Exception{
        String sql="update account set name=? , money=? where id=?";
        Account account = new Account();
        account.setId(3);
        account.setName("face");
        account.setMoney(800f);
        Object[] objects={account.getName(),account.getMoney(),account.getId()};
        int update = queryRunner.update(sql, objects);
        System.out.println("updated rows:"+update);
    }
    @Test
    public void deleteAccountByIdTest() throws Exception{
        String sql="delete from account where id=?";
        Object[] objects={4};
        int update = queryRunner.update(sql, objects);
        System.out.println("deleted rows:"+update);
    }
}

C3P0

public class C3P0Test {
    private String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private String url="jdbc:sqlserver://localhost;DatabaseName=testdb";
    private String username="sa";
    private String password="123.abc";

    private QueryRunner queryRunner=null;

    @Before
    public void before() throws Exception{
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass(driver);
        dataSource.setJdbcUrl(url);
        dataSource.setUser(username);
        dataSource.setPassword(password);
        queryRunner = new QueryRunner(dataSource);
    }

    @Test
    public void queryUserByIdTest() throws Exception {
        String sql="select * from account where id=?";
        Object[] param={3};
        Account account = queryRunner.query(sql, new BeanHandler<>(Account.class), param);
        System.out.println(account);
    }

    @Test
    public void queryUserListTest() throws Exception {
        String sql="select * from account";
        List<Account> accountList = queryRunner.query(sql, new BeanListHandler<>(Account.class));
        accountList.forEach(System.out::println);
    }

    @Test
    public void insertAcountTest() throws Exception{
        String sql="insert into account(name,money) values(?,?)";
        Object[] objects=new Object[]{"jerry","6000"};
        int update = queryRunner.update(sql, objects);
        System.out.println("inserted rows:"+update);
    }

    @Test
    public void updateAccountTest() throws Exception{
        String sql="update account set name=? , money=? where id=?";
        Account account=new Account();
        account.setId(2);
        account.setName("jerry2");
        account.setMoney(8000f);
        Object[] objects={account.getName(),account.getMoney(),account.getId()};
        int update = queryRunner.update(sql, objects);
        System.out.println("updated rows:"+update);
    }

    @Test
    public void deleteUserByIdTest() throws Exception{
        String sql="delete from account where id=?";
        Object[] objects={1};
        int update = queryRunner.update(sql, objects);
        System.out.println("deleted rows:"+update);
    }
}