[大数据]2021.12.30C3P0-Mysql简单实现学习记录

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--默认配置-->
    <default-config>
        <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>
    </default-config>

    <!--配置连接池mysql-->
    <named-config name="linux01mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://192.168.111.131:3306/mybatisdb</property>
        <property name="user">root</property>
        <property name="password">root</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>
    </named-config>

    <!--配置连接池mysql-->
    <named-config name="linux06mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://192.168.111.131:3306/mybatisdb</property>
        <property name="user">root</property>
        <property name="password">root</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>
    </named-config>
</c3p0-config>

mydb.properties?

mysqldriver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.111.131:3306/mybatisdb
user=root
password=root
initNum=101
min=31
max=151
increase=32

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>nj.zb.cn.kgc</groupId>
  <artifactId>jdbcpooldemo</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>jdbcpooldemo</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.4</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.25</version>
    </dependency>
  </dependencies>

  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

?C3p0Utils

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class C3p0Utils {

    static ComboPooledDataSource source = new ComboPooledDataSource("linux01mysql");

    public static Connection getConnection() {

        Connection connection = null;

        try {
            connection = source.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;

    }

    public static void close(Connection connection, PreparedStatement pstmt, ResultSet rs){
        if(rs!=null){

            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }



    }

    public static void main(String[] args) {
        for (int i = 0; i < 5; i++) {
            Connection connection = C3p0Utils.getConnection();

            System.out.println(connection);
        }

    }
}

?Student

public class Student {

    private Integer id;
    private String name;

    public Student() {
    }

    public Student(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

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

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

StudentDao

import java.util.List;

public interface StudentDao {
    public void insertStudent(List<Student>students);
}

?StudentDaoImpl

import nj.zb.cn.kgc.mypool.MyPoolUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    @Override
    public void insertStudent(List<Student> students) {
//   insert into Student(id,name) values(1,'zs'),(2,'ls'),(3,'ww')
        String sql = "insert into Student(id,name) values";
        for (Student stu :
                students) {
            sql += "(" +stu.getId()+","+stu.getName()+ "),";
        }
//   insert into Student(id,name) values(1,'zs'),(2,'ls'),(3,'ww'),
        sql = sql.substring(0,sql.length()-1);

        Connection conn = C3p0Utils.getConnection();
//        Connection conn = MyPoolUtils.getConnection();

        try {
            PreparedStatement prest = conn.prepareStatement(sql);
            System.out.println(sql);
//            int i = prest.executeUpdate();
            C3p0Utils.close(conn,prest,null);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        List<Student> list = new ArrayList<>();
        Student zs = new Student(1, "zs");
        Student ls = new Student(2, "ls");
        list.add(zs);
        list.add(ls);

        StudentDao  studentDao = new StudentDaoImpl();
        studentDao.insertStudent(list);
    }
}