Java之数据库池Mysql8.0+JDK8+C3P0+Spring

Java Mysql Spring C3P0
MySQL
发布日期:   2018-10-06

数据库连接池(JDK8+MySQL8)

一、手写实现简单的数据库连接池(jdbc)

注意

在xml配置文件中，url中的&符号需要转义成 “ & amp;”(中间没有空格)

info.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://192.168.144.139:3306/web09?&useSSL=false
username=root
password=mysql

MyDataSource.java

package com.an.test.jdbc.util;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;

import javax.sql.DataSource;

public class MyDataSource implements DataSource {
	private static LinkedList<Connection> pool = new LinkedList<Connection>();
	// 创建五个连接 并添加到池中
	static {
		for (int i = 0; i < 5; ++i) {
			try {
				Connection conn = JdbcUtils_V3.getConnection();
				pool.add(conn);
			} catch (SQLException e) {

				e.printStackTrace();
			}

		}
	}

	@Override
	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setLogWriter(PrintWriter out) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setLoginTimeout(int seconds) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public int getLoginTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public Connection getConnection() throws SQLException {
		Connection reConn=null;
		if (pool.size() == 0) {
			for (int i = 0; i < 5; ++i) {
				try {
					Connection conn = JdbcUtils_V3.getConnection();
					pool.add(conn);
				} catch (SQLException e) {

					e.printStackTrace();
				}

			}
		}
		reConn = pool.remove();
		return reConn;
	}
	public void backSource(Connection conn) {
		if(conn!=null) {
			pool.add(conn);
		}
	}
	@Override
	public Connection getConnection(String username, String password) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}

JdbcUtils_V3.java

package com.an.test.jdbc.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcUtils_V3 {

	private static String driver = null;
	private static String dbUrl = null;
	private static String user = null;
	private static String password = null;

	static {

		try {
			// 通过类加载器获取资源
			InputStream is = JdbcUtils_V3.class.getClassLoader().getResourceAsStream("info.properties");
			// 使用properties 处理流
			Properties properties = new Properties();

			properties.load(is);

			driver = properties.getProperty("driver");
			dbUrl = properties.getProperty("url");
			user = properties.getProperty("username");
			password = properties.getProperty("password");

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static Connection getConnection() throws SQLException {

		Connection connection = null;

		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(dbUrl, user, password);
			System.out.println("获取驱动并且获取链接成功");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


		return connection;
	}

	public static void release(Connection connection, PreparedStatement preparedStatement, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
	}
}

二、使用C3P0数据库连接池

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--默认配置 直接getConnection()默认使用这个 可以指定为其他的-->
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/library?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;serverTimezone=GMT</property>
        <property name="user">root</property>
        <property name="password">mysql123</property>
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost/web09?useSSL=false</property>
        <property name="user">root</property>
        <property name="password">mysql</property>
    </named-config>


</c3p0-config>

C3P0Utils.java

package com.an.test.jdbc.util;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
	private static DataSource dataSource = null;
	static {
		dataSource = new ComboPooledDataSource();
	}

	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
			
		}
	}
	public static DataSource getDataSource() {
		return dataSource;
	}
}

三、Spring中配置

spring-jdbc.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"
       xmlns:context="http://www.springframework.org/schema/context"
       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="db.properties"/>
    <!--配置c3p0连接池-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.pass}"/>
        <property name="driverClass" value="${jdbc.driverClass}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="initialPoolSize" value="${jdbc.initPoolSize}"/>
        <property name="maxPoolSize" value="${jdbc.maxPoolSize}"/>
     </bean>

    <!--配置Spring jdbc  -->
    <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!--配置具有名字的jdbctemplate-->
    <bean name="nameParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource"/>
    </bean>



</beans>

db.properties

jdbc.user=root
jdbc.pass=mysql123
jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/library?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Hongkong
jdbc.initPoolSize = 5
jdbc.maxPoolSize = 10

然后通过jdbcTemplates就可以操作数据库了

文章作者: Bxan
文章链接: http://anbingxu666.com/2018/10/06/Java%E4%B9%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E6%B1%A0Mysql8-0-JDK8-C3P0-Spring/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Bxan !
Java Mysql Spring C3P0
赏
  目录