Open connection on first create only, sqlDriver

Question

I have a performance issue with sqlConnexion with android. Problem is, I open the connexion for each request i make on the server. I would prefer to open it once, when the user first log in.

here is some of the code:

    public void onClick(View v) {
    // Perform action on click
    new Thread(new Runnable() {
        public void run() {
            Statement statement;
            try {
             *//This would be better to instantiate connexion at first...*
                connexion =   DriverManager.getConnection(url,     "login",    
                        "pass");//Not true login of course...
                statement = connexion.createStatement();
                ResultSet resultat = statement
                        .executeQuery("SELECT name  FROM users;");

                while (resultat.next()) {
                    resultId = resultat.getString("name");
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }).start();

If I place the DriverManager in a method, I get an error from server. In another class, I get NullPointerException.

I know working with json parser may be a better aproach, but I'm starting to learn, and Java/Android are more than enought too give me headeache for now...

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

Move the connection code to a Singleton or simply store the connection in a static attribute in a separate class. For more details, see Store application global data

edited May 23 '17 at 12:28

Community

1
1

answered Jun 05 '13 at 17:53

Piovezan

3,215
1
28
45

score 0 · Answer 2 · answered Jun 05 '13 at 17:58

Do Following: When you declare connexion assign it to null, and in try catch check whether connexion is null or not. If you get null, get new connection otherwise not

connexion = null; 

//In try block
if( connexion == null)
     connexion = DriverManager.getConnection(url, "login", "pass");

score 0 · Accepted Answer · answered Jun 05 '13 at 18:45

Don't try to reuse the connection!!! For each logical interaction with the database get a connection, statement, or whatever and make sure you close them all before the end of the function.

HOWEVER, to improve performance, use a connection "pool" such as Commons BasicDataSource. Using this will be invisible to you. You use connections and statements as normal but the BasicDataSource will pool the connections (and PreparedStatements) to improve your performance.

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

GaelDev · Answer 4 · 2013-06-06T10:21:36.657

Thank you all for your help !

I managed to solve this issue. What i did, if that can help someone i post here how :

First, make an other Classe, for me Driver() in which i make the DataSource :

public class Driver implements DataSource {

    public static Connection connection = null;
    public static final String url = "jdbc:mysql://url/dataBaseName";
    protected String user = "userName";
    protected String passwd = "password";
        //Andoid related, haven't tested the log so, i don't know if this work.
    private String state = Environment.getExternalStorageState();

    public Driver(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        PrintWriter logWriter = null;

        if (Environment.MEDIA_MOUNTED.equals(state)) {
            // We can read and write the media
            try {
                logWriter = new PrintWriter(state + "/com.me.appName/Logfile.log");
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return logWriter;
    }

    @Override
    public int getLoginTimeout() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public void setLogWriter(PrintWriter arg0) throws SQLException {
        // TODO Auto-generated method stub
        if (Environment.MEDIA_MOUNTED.equals(state)) {
            // We can read and write the media
            try {
                DriverManager.setLogWriter(new PrintWriter(
                        state + "/com.me.appName/Logfile.log"));
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public boolean isWrapperFor(Class<?> arg0) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> arg0) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Connection getConnection() throws SQLException {
        if (connection != null) {
            System.out.println("Cant create  Connection");
        } else {
            connection = DriverManager.getConnection(
                    url, user, passwd);
        }
        return connection;
    }

    @Override
    public Connection getConnection(String userName, String password)
            throws SQLException {
        // TODO Auto-generated method stub
        if (connection != null) {
            System.out.println("Cant craete a Connection");
        } else {
            connection = DriverManager.getConnection(
                    url, userName,
                    password);
        }
        return connection;
    }

}

Finaly in my main Classe :

in onCreate() i instantiate the driver then i can make all needed request without delay :)

public void onClick(View v) {
                // Perform action on click
                new Thread(new Runnable() {
                    public void run() {
                        try {
                            Statement statement;
                            //The .getConnection is where i handle the DriverManager.
                            connexion = driver.getConnection();
                            statement = connexion.createStatement();

                            ResultSet resultat = statement
                        .executeQuery("SELECT name  FROM users;");

                        while (resultat.next()) {
                            resultId = resultat.getString("name");
                        }
                        driver.setLoginTimeout(5);
                    } catch (SQLException e1) {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                    }

                }
            }).start();

That's really useful.

thank you all again, sory that i can't vote up your answer, and with hope of helping you one day !

Open connection on first create only, sqlDriver

4 Answers4