Java MySQL连接池不起作用 [英] Java MySQL connetion pool is not working

问题描述

我已经用Java编写了一个函数，该函数运行MySQL查询并返回结果.我在这里使用此方法实现了连接池: http ://www.kodejava.org/how-do-i-create-a-database-connection-pool/.该功能正在运行，但连接时间仍与未合并约190 ms时相同.有人可以告诉我我在做什么错吗?

I've written a function in Java that runs a MySQL query and returns results. I've implemented connection pooling using this method here: http://www.kodejava.org/how-do-i-create-a-database-connection-pool/. The function is working, but connection time is still the same as it was without pooling ~190 ms. Can somebody tell me what am I doing wrong?

这是我的代码:

public static ArrayList<Map<String,Object>> query(String q) throws Exception {

    long start, end;

    GenericObjectPool connectionPool = null;

    String DRIVER = "com.mysql.jdbc.Driver"; 
    String URL = "jdbc:mysql://localhost/dbname";
    String USER = "root";
    String PASS = "";

    Class.forName(DRIVER).newInstance();

    connectionPool = new GenericObjectPool();
    connectionPool.setMaxActive(10);

    ConnectionFactory cf = new DriverManagerConnectionFactory(URL, USER, PASS);

    PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, connectionPool, null, null, false, true);

    DataSource ds = new PoolingDataSource(connectionPool);

    //create statement
    Statement Stm = null;

    try {

        Connection Con = null;
        PreparedStatement stmt = null;

        start = System.currentTimeMillis();

        Con = ds.getConnection();

        end = System.currentTimeMillis();
        System.out.println("DB Connection: " + Long.toString(end - start) + " ms");

        //fetch out rows
        ArrayList<Map<String, Object>> Rows = new ArrayList<Map<String,Object>>();

        Stm = Con.createStatement();

        //query
        ResultSet Result = null;

        boolean Returning_Rows = Stm.execute(q);

        if (Returning_Rows) {
            Result = Stm.getResultSet();
        } else {
            return new ArrayList<Map<String,Object>>();
        }

        //get metadata
        ResultSetMetaData Meta = null;
        Meta = Result.getMetaData();

        //get column names
        int Col_Count = Meta.getColumnCount();
        ArrayList<String> Cols = new ArrayList<String>();
        for (int Index=1; Index<=Col_Count; Index++) {
            Cols.add(Meta.getColumnName(Index));
        }

        while (Result.next()) {
            HashMap<String,Object> Row = new HashMap<String,Object>();
            for (String Col_Name:Cols) {
                Object Val = Result.getObject(Col_Name);
                Row.put(Col_Name,Val);
            }
            Rows.add(Row);
        }

        //close statement
        Stm.close();


        //pass back rows
        return Rows;

    } catch (Exception Ex) {

        System.out.print(Ex.getMessage());
        return new ArrayList<Map<String,Object>>();

    } finally {

        if (Stm != null) {
            Stm.close();
        }
        if (Stm != null) {
            Stm.close();
        }

        System.out.println("Max connections: " + connectionPool.getMaxActive());
        System.out.println("Active connections: " + connectionPool.getNumActive());
        System.out.println("Idle connections: " + connectionPool.getNumIdle());

    }

}

这是每次的控制台输出:

This is console output every time:

DB Connection: 186 ms
Max connections: 10 
Active connections: 1 
Idle connections: 0 

更新:我应该注意，使用此功能的Java应用程序的工作方式如下:执行，仅运行一个查询并关闭.我发现PHP是否可以像这样工作，并且默认情况下它使用的是连接池，所以Java应该吗?如果我错了，请纠正我.

UPDATE: I should note, that Java application that uses this works like this: executes, only runs one query and closes. I figured if PHP works like this and it's using connection pooling by default, so should Java? Correct me if I'm wrong.

推荐答案

您创建了一个连接池，但未在其中添加任何内容.连接池在创建时为空，因此保证您对它的第一个请求都将创建一个新连接，并且速度与手动获取连接一样慢.

You create a connection pool, but you don't put anything into it. You connection pool is empty when it is created, so your first request to it is guaranteed to to create a new connection and will be just as slow as getting a connection manually.

尝试将您的代码放入循环中，在该循环中您反复从池中获得连接.尝试一次，五次，十次和十五次.请注意结果如何变化.

Try putting your code into a loop, where you repeatedly get a connection from the pool. Try it once, five times, ten times and fifteen times. Note how the results change.

某些连接池支持自动创建并保持可供使用的 minimum 个数量以及 maximum 个数量.初始化池后，它将预取连接，因此前几个调用不会延迟.

Some connection pools support automatically creating and holding a minimum number of connections ready for use as well as a maximum. When the pool is initialised it will pre-fetch connections so the first few calls aren't delayed.

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