今天我用传统的数据库和数据库连接池用访问量的大小进行速度的测试,刚开始还好着了,后来传统用传统方式超越10万以后就出现了这个异常.
出现的bug:
For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required.
For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271).
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at com.fan.dao.DBUtil.getConn1(DBUtil.java:30)
at com.fan.test.ConnectionTest.connTest1(ConnectionTest.java:20)
at com.fan.test.ConnectionTest.main(ConnectionTest.java:11)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.
我的代码展示:
package com.fan.dao;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.beanutils.ConvertUtils;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public abstract class DBUtil {
protected Connection conn=null;
protected PreparedStatement ps=null;
protected ResultSet rs=null;
private static DruidDataSource druidDataSource=null;
/**
* 连接数据库
* @return连接对象
* @throws ClassNotFoundException
* @throws SQLException
*/
protected Connection getConn1() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
return conn= DriverManager.getConnection("jdbc:mysql://localhost:3307/user_db?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai","root","FJG258775");
}
static {
//读取资源文件(db.properties)
//创建一个资源类实例对象
try {
Properties properties = new Properties();
//将资源文件db.properties加载内存中,生成一个输入流对象
InputStream inputStream = DBUtil.class.getClassLoader().getResourceAsStream("db.properties");
//将输入流对象添加到properties对象,此时properties对象会自动读取输入流中的数据
properties.load(inputStream);
//创建Druid数据源对象
druidDataSource = new DruidDataSource();
//将properties对象中属性自动添加到druid对象中
druidDataSource.configFromPropety(properties);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 连接池的
* @return
* @throws SQLException
*/
protected Connection getConn() throws SQLException {
//从Druid中获取连接对象
return conn=druidDataSource.getConnection();
}
protected void closeAll(){
try {
if (rs!=null){
rs.close();
}
if (ps!=null){
ps.close();
}
if (conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn=null;
ps=null;
rs=null;
}
}
protected int executeUpdate(String sql,Object...params){
//连接数据库
try {
this.getConn();
ps=conn.prepareStatement(sql);
if (params!=null&¶ms.length!=0){
for (int i=0;i<params.length;i++){
ps.setObject(i+1,params[i]);
}
}
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeAll();
}
return 0;
}
public <T>List<T> executeQuery(Class<T> cla,String sql,Object...params){
try {
this.getConn();
ps=conn.prepareStatement(sql);
if (params!=null&¶ms.length!=0){
for (int i=0;i< params.length;i++){
ps.setObject(i+1,params[i]);
}
}
rs=ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
List<T> list = new ArrayList<T>();
while(rs.next()){
T t = cla.getDeclaredConstructor().newInstance();
for (int i=0;i<columnCount;i++){
Object value = rs.getObject(i + 1);
String catalogName = metaData.getCatalogName(i + 1);
Field declaredField = cla.getDeclaredField(catalogName);
String methodName = "set" + catalogName.substring(0, 1).toUpperCase() + catalogName.substring(1);
Method declaredMethod = cla.getDeclaredMethod(methodName, declaredField.getType());
declaredMethod.invoke(t, ConvertUtils.convert(value,declaredField.getType()));
list.add(t);
}
return list;
}
} catch (SQLException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
this.closeAll();
}
return null;
}
}
package com.fan.test;
import com.fan.dao.DBUtil;
import java.sql.SQLException;
public class ConnectionTest extends DBUtil {
public static void main(String[] args) {
ConnectionTest connectionTest = new ConnectionTest();
long start = System.currentTimeMillis();
connectionTest.connTest1();
long end = System.currentTimeMillis();
System.out.println("共耗时"+(end-start)+"毫秒");
}
//非连接池
public void connTest1(){
for (int i=0;i<100000;i++){
try {
super.getConn1();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
super.closeAll();
}
}
}
/**
* 连接池
*/
public void connTest(){
for (int i=0;i<100000;i++){
try {
conn=super.getConn();
} catch (SQLException e) {
e.printStackTrace();
} finally {
super.closeAll();
}
}
}
}
后来我仔细看了一下那个报错的异常,
com.mysql.cj.exceptions.CJCommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.
发现是我用传统方式,对数据库的断开和连接超过了某个最大的量,我用传统方式测试的是10万次,所以就报错了,这是数据库的问题,当然了,也从另一方面说明数据库连接池Druid是多么强大!