CODEWITHSUNDEEP
javajdbc
user1582625
user1582625

Reputation: 303

JAVA JDBC reusing connections

I have a Java program in which I am doing some JDBC for select queries. Will it be advisable to call testDataBase() each time which inturns calls DBConnection() each time or I should reuse one connection for all the queries. Thanks in advance.

private  void testDataBase(String query){
    Connection con = DBConnection();
    Statement st = null;
    ResultSet rs = null;

    try {
        st = con.createStatement();
        rs = st.executeQuery(query);
        boolean flag = true;
        while (rs.next()) {
            String resultString = "";
            for(int i = 1; i <=rs.getMetaData().getColumnCount();i++){
                resultString=resultString+" "+  rs.getString(i);
            }
            System.out.println(resultString);
        }
    } catch (SQLException e) {
        e.printStackTrace();

    } finally {
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}               



private  Connection DBConnection() {
    final String method_name =  "DBConnection";
    Connection conn = null;
    try{
      Class.forName(driver).newInstance();
      conn = java.sql.DriverManager.getConnection(url,userName,password);

    }catch (ClassNotFoundException e) {
        System.out.println(e.getMessage());
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    } catch (InstantiationException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    }

    return conn;
}

Upvotes: 19

Views: 16899

Answers (6)

javadev
javadev

Reputation: 1669

If you want only one instance of Connection, you can make use of the Singleton pattern, you can consider : 

public class Connector {

private static final String URL = "jdbc:mysql://localhost/";
private static final String LOGIN = "root";
private static final String PASSWORD = "azerty";
private static final String DBNAME = "videotheque";
private static Connector connector;
private static Connection connection;

private Connector() {
}

public synchronized static Connector getInstance() {
    if (connector == null) {
        connector = new Connector();
    }
    return connector;
}

public static Connection getConnection() {
    if (connection == null) {
        Connection c = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            c = DriverManager.getConnection(URL + DBNAME, LOGIN, PASSWORD);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return c;
    }
    return connection;
}

}

And then, you can call : Connector.getInstance().getConnection()

Upvotes: -1

duffymo
duffymo

Reputation: 308763

Connections are not thread safe, so sharing them across requests is not a good idea.

A better idea is to pool connections and keep their scope as narrow as possible: check the connection out of the pool, use it, close it in transaction scope.

Upvotes: 7

cloudy_weather
cloudy_weather

Reputation: 2977

I completely agree with @Amir Kost, in terms of performances, opening a DB connection in one of the slowest operation that you can do, and if you have restrictive real time constraints it could be a big issue. I do not know if you are using a framework or not, but a good practice is to publish a bean which wrap a pool of connection and every time that you need to interact directly with the db, you get the current open connection (which usually corresponds to a so called "session"). I suggest to you, (even if you are not using any framework) to reproduce this technicality.

Upvotes: 1

NickJ
NickJ

Reputation: 9559

Getting a database connection is quite an expensive operation, so it is advisable to re-use a connection if possible. Consider also using connection pooling, which will maintain a number of connections for you, so you can just grab one from the pool when needed. The method shown above might not need to change, it depends on the DBConnection() method you call.

Upvotes: 5

tbsalling
tbsalling

Reputation: 4545

Database connections are long-running and should be re-used, unless you have a very low query rate.

Upvotes: 5

Amir Kost
Amir Kost

Reputation: 2168

Opening a DB connection is an expensive operation in terms of perfofmance. You should use a ConnectionPool for sharing connections among different requests.

Upvotes: 13

Related Questions