Spring integrates JDBC (dbcp, C3P0, Druid, JdbcTemplate)

1. Integrated database connection pool dbcp

1. Import jar package

<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
    <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>1.4</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.31</version>
    </dependency>

2. Add configuration file spring.xml

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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/aop
       http://www.springframework.org/schema/aop/spring-aop.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd">

 <!--Open annotations-->
    <context:annotation-config/>
    <!--Start component scanning-->
    <context:component-scan base-package="com.yy"/>

</beans>

3.Configure data source

<--DBCP connection pool configuration -->
    <!--<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/ball?serverTimezone=GMT+8"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123sa"></property>
        </bean>

4. Use the database connection pool to implement native jdbc to add users

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private BasicDataSource dataSource;
    @Override
    public void aa(User user) {
        Connection connection=null;
        try {
            connection = dataSource. getConnection();
            String sql="insert into user values(null,?,?)";
            PreparedStatement ps = connection. prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPwd());
            ps. executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            try {
                connection. close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

2. Integrated database connection pool C3P0

1. Import jar package

 <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.2</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.31</version>
    </dependency>

2. Add configuration file spring.xml

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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/aop
       http://www.springframework.org/schema/aop/spring-aop.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd">

 <!--Open annotations-->
    <context:annotation-config/>
    <!--Start component scanning-->
    <context:component-scan base-package="com.yy"/>

</beans>

3.Configure data source

<!--C3P0 connection pool configuration -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/ball?serverTimezone=GMT+8"></property>
        <property name="user" value="root"></property>
        <property name="password" value="123sa"></property>
    </bean>

4. Use the database connection pool to implement native jdbc to add users

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private ComboPooledDataSource dataSource;
    @Override
    public void aa(User user) {
        Connection connection=null;
        try {
            connection = dataSource. getConnection();
            String sql="insert into user values(null,?,?)";
            PreparedStatement ps = connection. prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPwd());
            ps. executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            try {
                connection. close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

3. Integrate database connection pool Druid

1. Import jar package

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.15</version>
    </dependency>


    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.31</version>
    </dependency>

2. Add configuration file spring.xml

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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/aop
       http://www.springframework.org/schema/aop/spring-aop.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd">

 <!--Open annotations-->
    <context:annotation-config/>
    <!--Start component scanning-->
    <context:component-scan base-package="com.yy"/>

</beans>

3.Configure data source

<!--Druid connection pool configuration -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/ball?serverTimezone=GMT+8"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123sa"></property>
    </bean>

4. Use the database connection pool to implement native jdbc to add users

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private DruidDataSource dataSource;
    @Override
    public void aa(User user) {
        Connection connection=null;
        try {
            connection = dataSource. getConnection();
            String sql="insert into user values(null,?,?)";
            PreparedStatement ps = connection. prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPwd());
            ps. executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            try {
                connection. close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

4. Integrate spring built-in data source (JdbcTemplate )

1. Import jar package

<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.3.24</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.31</version>
    </dependency>

2. Add configuration file spring.xml

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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/aop
       http://www.springframework.org/schema/aop/spring-aop.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd">

 <!--Open annotations-->
    <context:annotation-config/>
    <!--Start component scanning-->
    <context:component-scan base-package="com.yy"/>

</beans>

3.Configure data source

 <!--JdbcTemplate connection pool configuration -->
    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/ball?serverTimezone=GMT+8"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123sa"></property>
    </bean>

4. Use the built-in data source to implement native jdbc to add users

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private DriverManagerDataSource dataSource;
    @Override
    public void aa(User user) {
        Connection connection=null;
        try {
            connection = dataSource. getConnection();
            String sql="insert into user values(null,?,?)";
            PreparedStatement ps = connection. prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPwd());
            ps. executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            try {
                connection. close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

}