Spring配置数据源(c3p0,druid)

Spring配置数据源

1.数据源(连接池)的作用

• 数据源(连接池)是提高程序性能如出现的

• 事先实例化数据源，初始化部分连接资源

• 使用连接资源时从数据源中获取

• 使用完毕后将连接资源归还给数据源

常见的数据源(连接池)：DBCP、C3P0、BoneCP、Druid等

2.数据源开发步骤:

① 导入数据源的坐标和数据库驱动坐标

② 创建数据源对象

③ 设置数据源的基本连接数据

④ 使用数据源获取连接资源和归还连接资源

3.数据源的手动创建

导入Maven:

1.MySQL

<dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.0.31</version>
        </dependency>

2.junit

<dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

3.c3p0

<dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>

4.druid

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.16</version>
        </dependency>

导入Maven后,我们先来尝试尝试手动使用java代码进行连接池的创建

1.手动创建c3p0连接池:

@Test
    //测试手动创建c3p0
    public void Test01() throws PropertyVetoException, SQLException {
        //创建数据源
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //设置数据库连接参数
        comboPooledDataSource.setDriverClass("com.mysql.jdbc.Driver");
        comboPooledDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        comboPooledDataSource.setUser("sangyu");
        comboPooledDataSource.setPassword("sangyu");
        //获得连接对象
        Connection connection = comboPooledDataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }

测试可输出connection地址值

2.手动创建druid:

@Test
    //测试手动创建Druid
    public void Test02() throws SQLException {
        //创建数据源
        DruidDataSource druidDataSource = new DruidDataSource();
        //设置数据库连接参数
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("jdbc:mysql://localhost:3306/test");
        druidDataSource.setUsername("sangyu");
        druidDataSource.setPassword("sangyu");
        //获得连接对象
        DruidPooledConnection connection = druidDataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }

测试可输出connection地址值

我们可以对上述手动创建数据源连接池的代码进行解耦,提高复用性和安全性

解耦:

1.properties:

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=sangyu
jdbc.password=sangyu

2.c3p0:

读取jdbc.properties配置文件创建连接池

@Test
    //测试手动创建c3p0(解耦版)(加载配置文件版)
    public void Test03() throws PropertyVetoException, SQLException {
        ResourceBundle resourceBundle = ResourceBundle.getBundle("jdbc");
        String driver = resourceBundle.getString("jdbc.driver");
        String url = resourceBundle.getString("jdbc.url");
        String username = resourceBundle.getString("jdbc.username");
        String password = resourceBundle.getString("jdbc.password");
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(username);
        comboPooledDataSource.setPassword(password);
        Connection connection = comboPooledDataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }

4.使用Spring:

可以将DataSource的创建权交由Spring容器去完成

• DataSource有无参构造方法，而Spring默认就是通过无参构造方法实例化对象的

• DataSource要想使用需要通过set方法设置数据库连接信息，而Spring可以通过set方法进行字符串注入

applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test"/>
        <property name="user" value="sangyu"/>
        <property name="password" value="sangyu"/>
    </bean>

    <bean id="dataSource2" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test"/>
        <property name="username" value="sangyu"/>
        <property name="password" value="sangyu"/>
    </bean>

</beans>

@Test
    //使用spring测试c3p0
    public void Test04() throws SQLException {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        DataSource dataSource = (DataSource) applicationContext.getBean("dataSource");
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        //可输出地址值
        connection.close();
    }

    @Test
    //Spring测试druid
    public void Test05() throws SQLException {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        DataSource dataSource2 = (DataSource) applicationContext.getBean("dataSource2");
        Connection connection = dataSource2.getConnection();
        System.out.println(connection);
        //可输出地址值
        connection.close();
    }

4.抽取jdbc配置文件

applicationContext.xml加载jdbc.properties配置文件获得连接信息。

首先，需要引入context命名空间和约束路径：

• 命名空间:xmlns:context=“http://www.springframework.org/schema/context”

• 约束路径:http://www.springframework.org/schema/context

  http://www.springframework.org/schema/context/spring-context.xsd

  Spring容器加载properties文件

<context:property-placeholder location="xx.properties"/>
<property name="" value="${key}"/>

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <context:property-placeholder location="classpath:jdbc.properties"/>

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

</beans>

测试:

@Test
    //使用spring测试c3p0
    public void Test04() throws SQLException {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        DataSource dataSource = (DataSource) applicationContext.getBean("dataSource");
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        //可输出地址值
        connection.close();
    }