Java - MySQL 不会关闭连接 [英] Java - MySQL doesn't close connections

问题描述

我在尝试运行一个程序时遇到连接过多"错误，该程序从使用 tomcat 库实现的连接池中请求连接，在大约 50 个连接后失败，这是错误:

I'm getting a "too many connections" error while trying to run a program that asks for connections from a connection pool implemented with tomcat library, it's failing after around 50 connections, here's the error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"

这是主类，它有一个执行连接的循环

here's the main class, it has a loop that executes connections

  public static void main(String[] args) throws Exception{
      SimplePoolExample ex = new SimplePoolExample();

      int cont = 100;

      while (cont > 0)
      {
        ex.test();
        System.out.println(cont);
        cont--;
      }

  }

这是执行简单查询的 test() 函数

here's the test() function that executes a simple query

  public void test()  throws Exception 
  {

        Connection con = getConnection();
          try (Statement st = con.createStatement()) {
              ResultSet rs = st.executeQuery("select * from actor");

            while (rs.next()) {
                  System.out.println(rs.getString("actor_id") +" "+ rs.getString("first_name")+" "+ rs.getString("last_name"));
              }

            rs.close();
            st.close();

      } finally {
        if (con!=null) try {
            con.close();
        }

        catch (SQLException ignore) {

            System.out.println("***SQL EXC" + ignore.getMessage());
        }
      }
  }

以及从连接池中请求连接的 getConnection() 类

and the getConnection() class that asks for a connection from a connection pool

  public Connection getConnection () throws SQLException   
  {
        DataSource datasource = new DataSource();
        datasource.setPoolProperties(p);

        Connection con = null;
        con = datasource.getConnection();

        return con;
  }

编辑:这里是连接池设置:

  public void setPoolProperties ()
  {      

      p.setUrl("jdbc:mysql://localhost:3306/sakila");
      p.setDriverClassName("com.mysql.jdbc.Driver");
      p.setUsername("user");
      p.setPassword("pwd");
      p.setJmxEnabled(true); // utilities to manage JVM
      p.setTestWhileIdle(false); // test idle connections
      p.setTestOnBorrow(true); // 
      p.setValidationQuery("SELECT 1"); // any test requires it
      p.setTestOnReturn(false); 
      p.setValidationInterval(30000); // cada cuanto hace test
      p.setTimeBetweenEvictionRunsMillis(30000); // how often check idle and abandoned conn
      p.setMaxActive(50);
      p.setInitialSize(10);
      p.setMaxWait(50);
      p.setRemoveAbandonedTimeout(60); // OJO: max query last
      p.setMinEvictableIdleTimeMillis(30000); // time to consider a conn idle
      p.setMaxIdle(10);
      p.setMinIdle(10);
      p.setLogAbandoned(true); // log stack traces .. overhead
      p.setRemoveAbandoned(true); //abandoned timeout ... 
      p.setJdbcInterceptors(
        "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
        "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
  }

推荐答案

每次调用 getConnection() 时，您都在创建一个全新的连接池.

You are creating a completely new connection pool every time getConnection() is called.

您应该改用 DataSource 的单个共享实例.

You should use a single shared instance of DataSource instead.

这篇关于Java - MySQL 不会关闭连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！