How to keep connection alive in java

Question

I am working on my first Java Project with MySQL. I have one function that gets called every time I get data back from my data source. This function should save a new line to my MySQL database. See the code here:

import java.sql.*;
import java.util.Properties;

/**
 *
 * @author jeffery
 */
public class SaveToMysql {
    // The JDBC Connector Class.
    private static final String dbClassName = "com.mysql.jdbc.Driver";

    private static final String CONNECTION = "jdbc:mysql://localhost/test";

    static public String test(int reqId, String date, double open, double high, double low,
                                        double close, int volume, int count, double WAP, boolean hasGaps){

        if (date.contains("finished")){
            return "finished";
        }


        // Class.forName(xxx) loads the jdbc classes and
        // creates a drivermanager class factory
        try{
            Class.forName(dbClassName);
        }catch ( ClassNotFoundException e ) {
            System.out.println(e);
        }

        // Properties for user and password. Here the user and password are both 'paulr'
        Properties p = new Properties();
        p.put("user","XXXXXXXX");
        p.put("password","XXXXXXXXXXXx");

        // Now try to connect
        Connection conn;
        try{
            conn = DriverManager.getConnection(CONNECTION,p);
        }catch(SQLException e){
            return e.toString();
        }

        PreparedStatement stmt;
        try{
            stmt = conn.prepareStatement("insert into dj_minute_data set symbol = (select ticker from dow_jones_constituents where id = ?), "
                    + "date = str_to_date(?,'%Y%m%d %H:%i:%s')" +
            ", open = ?" +
            ", high = ?" +
            ", low = ?" +
            ", close = ?" +
            ", volume = ?" +
            ", adj_close = ?");
            stmt.setInt(1, reqId);
            stmt.setString(2, date);
            stmt.setDouble(3, open);
            stmt.setDouble(4, high);
            stmt.setDouble(5, low);
            stmt.setDouble(6, close);
            stmt.setDouble(7, volume);
            stmt.setDouble(8, WAP);
        }catch (SQLException e){
            return e.toString();
        }

        try{
            stmt.executeUpdate();
        }catch (SQLException e){
            return e.toString();
        }

        return stmt.toString();
    }

}

As you all can see this function test is in its own class, called SaveToMysql. To call this function, I import the class into a different class, and use this syntax:

msg = SaveToMysql.test(reqId, date, open, high, low, close, volume, count, WAP, hasGaps);

The msg then get output to the screen. Showing either error message or success.

This function may be called many times rapidly in a short time period. I know I should not have to re-open my connection with the MySQL server every time the function gets called. How would I change this so that the 1 MySQL connection stays open for every call to the function.

Thanks!!

Answer 1

I know I should not have to re-open my connection with the MySQL server every time the function gets called.

No, it's fine to do so - as long as you have a connection pool under the hood to manage the real connections. There are various connection pool projects around, such as c3p0 and DBCP. That way you can make each of your calls isolated from the others:

• Fetch a connection from the pool
• Use it
• Release it back to the pool

See the documentation for whichever pool you choose for the details. Often connection pools allow you to just request a connection from JDBC as normal and then close it as normal, effectively making the whole thing transparent.

Also note that you should definitely start using prepared statements with parameters rather than including your values directly in your SQL statement. Your current code is a SQL injection attack waiting to happen.

Answer 2

JDBC URL: jdbc:mysql://:/? connectTimeout=0&socketTimeout=0&autoReconnect=true

Answer 3

If you want use 1 connection when call SaveToMysql.test method many times, you can add your connection in method parameter, or create a global connection variable out side SaveToMysql.test method. but, if you use 1 connection, please attention about transaction. I hope it will help you.

Answer 4

you need to manage one static class or method.

like

public class ConnectionClass
{
    public static Connection connection = null;

    public Connection getCurrentConnection()
    {
        if(connection != null)
        {
            return connection;
        }
        else
        {
            create new connection...
                    and return that new connection after giving it to global connection..
        }
    }
}

by this every time you will get current connection. and if there is some issue and connection is not available then you can create new connection. when you need connection object you just need to call getCurrentConnection method.

so you just need to following things in ur code.

Connection conn;
try{
    //conn = DriverManager.getConnection(CONNECTION,p);
      conn = getCurrentConnection();
}catch(SQLException e){
    return e.toString();
}