CODEWITHSUNDEEP
javamysqljdbc
user2549814
user2549814

Reputation: 33

Too many connection on JAVA with MySQL

I make Java application that will show the list of car queue that will load product in factory. This time I use the loop to update display by read from (MySQL) Database every second and I faced the error of "too many connections".

Is there any other way efficient to do this. I goggled on trigger of Database but I still didn't understand it.

Upvotes: 1

Views: 831

Answers (3)

Aiden
Aiden

Reputation: 335

make sure u kill the connections as soon as they have served their purpose. this gets alittle tricky with moving resultsets around our classes, you might want to get one class in which u query for resultsets, assign them to some nice multidimension arraylist or any collection u find comfortable to use and kill the connection there and then and pass the data around as far as u can keep it alive. that way u dont have deal with many connections

i have basically built a whole application on just a maximum of one connection....though it was quite a simple one. cheers!

Upvotes: 0

chetan
chetan

Reputation: 2886

use a static connection object or use connection pooling.

http://www.mchange.com/projects/c3p0/

or

http://commons.apache.org/proper/commons-dbcp/

Here is a simple example using c3p0 api

package com.chetan.dbconnection;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;


public class ConnectionProvider {
private static String SERVER = "localhost";
private static String PORT = "3306";
private static String DATABASE = "yourdb";
private static String UID = "root";
private static String PWD = "password";
private static Connection conn;
private static DataSource ds;

public static Connection getConnection() {

    try {
        if (ds == null) {
            ds = setupDataSource();
        }
        if (conn == null || conn.isClosed()) {
            conn = ds.getConnection();
        }

        return conn;
    } catch (SQLException e) {

        e.printStackTrace();
    }
    return null;

}

private static DataSource setupDataSource() {
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    try {

        String server = prop.getProperty("SERVER", SERVER);
        String port = prop.getProperty("PORT", PORT);
        String db = prop.getProperty("DATABASE", DATABASE);
        String uid = prop.getProperty("UID", UID);
        String pwd = prop.getProperty("PWD", PWD);

        log.info("Getting new database connection");
        String driver = "com.mysql.jdbc.Driver";
        String dbUrl = "jdbc:mysql://" + server + ":" + port + "/" + db
                + "?autoReconnect=true";

        try {
            cpds.setDriverClass(driver);
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        cpds.setJdbcUrl(dbUrl);
        cpds.setUser(uid);
        cpds.setPassword(pwd);
        cpds.setMinPoolSize(1);
        cpds.setInitialPoolSize(1);
        cpds.setAcquireIncrement(1);
        cpds.setMaxPoolSize(20);
        //cpds.setUnreturnedConnectionTimeout(100);
    } catch (IOException e1) {
        e1.printStackTrace();
    }

    return cpds;

}
}

Upvotes: 3

AllTooSir
AllTooSir

Reputation: 49372

You need to use some sort of Connection pooling . Also , do properly close all the connections which you have opened in the finally block.

Upvotes: 3

Related Questions