query running using JDBC

Question

I wrote a Java class for running MS Sql queries inside my program. This program make a new connection for each query that should be run. I know that this can increase my latency. Here is the calss code:

import java.sql.*;

public abstract class DatabaseManager {

    public static ResultSet executeQuery(String SQL, String dbName)
    {
        ResultSet rset = null ;
        try {
               Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
               String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
                  "databaseName="+dbName+";user=??;password=??;";
               Connection con = DriverManager.getConnection(connectionUrl);
               Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
               rset = st.executeQuery(SQL);
               //st.close();
        }
        catch (ClassNotFoundException e) {}
        catch (SQLException e) {}
        return rset;
     }

    public static void executeUpdate(String SQL, String dbName)
    {
        try {
               Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
               String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
                  "databaseName="+dbName+";user=??;password=??;";
               Connection con = DriverManager.getConnection(connectionUrl);
               Statement st = con.createStatement();
               st.executeUpdate(SQL);
               st.close();
               con.close();
        }
        catch (ClassNotFoundException e) {System.out.println(e);}
        catch (SQLException e) {System.out.println(e);}
     }
}

How can I change in the way that just one connection is created and all my queries route throught that connection? Regards.

Make the connection a singleton in your class? Really, you should look into pooling options (eg. C3PO) which also provides test-on-borrow and other neat things. — Anders R. Bystrup
– Anders R. Bystrup, Commented Dec 18, 2013 at 8:42
This may be helpful to you rdeshapriya.com/a-singleton-java-class-for-mysql-db-connection — SpringLearner
– SpringLearner, Commented Dec 18, 2013 at 8:46

Jorge_B · Accepted Answer · 2013-12-18 08:46:35Z

4

I beg to differ, I would not declare a database connection as a singleton. The usual approach is to cache a pool of managed database connections. The advantages here are:

More concurrent access to the database
Managed disposal of the database resources (connection pool shrinking if necessary)

Have a look at this

http://en.wikipedia.org/wiki/Connection_pool

The usual implementations are http://sourceforge.net/projects/c3p0/ http://commons.apache.org/proper/commons-dbcp/

If you deploy your applications in a commercial application server, such as Websphere or Weblogic, they come with out-of-the-box support for database connection pooling

answered Dec 18, 2013 at 8:46

Jorge_B

9,8622 gold badges19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ali Over a year ago

What is the best implementation for that?

Jorge_B Over a year ago

It depends on your environment. In my experience, I have used the database connection pooling which the application servers gave me, but when I have had software delivered by providers, they were quite happy with DBCP.

Ali Over a year ago

I found out that MS SQL Server support connection pooling from inside. Do you have any information how can i use that?

Jorge_B Over a year ago

I am very sorry, I know nothing about that particular product :( However, if it works for you, then it's OK :) Just consider how attached you finish to your database implementation with that solution, and if a solution like DBCP would decouple you from future database provider changes

Michael Kazarian · Accepted Answer · 2013-12-18 09:10:30Z

You must implement Singleton pattern. It similar to below:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {
    private static Connection connection = null;
    private static Class driver;
    public static void loadDriver() throws ClassNotFoundException{
        driver = Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
    }
    /**
     * Check for some connection
     * @return true, eсли установленно; false в противном случае
     */
    public static boolean isConnection(){
        if (connection != null) return true;
        return false;
    }
    /**
     * Return connection
     */
    public static synchronized Connection getConnection(String url,
            String user, String pass) throws SQLException{
        //Create connections if we have't work connection.
        if (connection == null || connection.isClosed()) {
            connection = DriverManager.getConnection(url, user, pass); //Next string show use without parameters
            //connecction = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;" +
              "databaseName="+dbName+";user=??;password=??;";);
            connection.setAutoCommit(false);
        }
        return connection;
    }
}

You can use it:

try{
      DBConnection.loadDriver();
      conn = DBConnection.getConnection(dburl, dbuser, dbpass);
//get Prepared statement and Result set. You cam create many anstances from one connections.
      PreparedStatement ps = null; ResultSet rs = null;
      ps =  conn.prepareStatement("Some query");
      rs = ps.executeQuery();
} catch (SQLException sqlex) {System.out.println("SQL problem");}
finally{ //You can close all connections
        rs.close();
        ps.close();
        //Close DB connections before terminate code.
        conn.close()
}

That is only a solution if there is no multithreading, and it is no problem if the connection times out and breaks. Otherwise: use a connection pool and open a connection when needed and close when you have completed a unit of work.

Ignacio Ocampo · Accepted Answer · 2013-12-18 08:41:52Z

0

You should implement singleton pattern to get a single instance of a class, that handle all your query request. Here is an example Connect to Database with JDBC, Singleton

answered Dec 18, 2013 at 8:41

Ignacio Ocampo

2,7231 gold badge22 silver badges31 bronze badges

1 Comment

Mathias G. Over a year ago

Another option is to autowire your JDBCConnection through Spring. In that case you also reuse your same connection...

Collectives™ on Stack Overflow

query running using JDBC

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related