CODEWITHSUNDEEP
javaswingjdbc
Wahuu
Wahuu

Reputation: 1

Closing Database Connections in Java Swing

I'm little confused about closing connection by jdbc.

package Login;
public class LoginFrame {
    private JFrame loginFrame;
    Connection conn = null;
    /**
     * Launch the application.
     */
    public static void main(String[] args) {
        EventQueue.invokeLater(new Runnable() {
            public void run() {
                try {
                    LoginFrame window = new LoginFrame();
                    window.loginFrame.setVisible(true);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
    }

    /**
     * Create the application.
     */
    public LoginFrame() {
        initialize();
        conn = DBConnect.connectDB();
    }

    /**
     * Initialize the contents of the frame.
     */
    private void initialize() {
        loginFrame = new JFrame();
        loginFrame.setResizable(false);
        loginFrame.setTitle("XXX");
        loginFrame.setBounds(100, 100, 350, 300);
        loginFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        loginFrame.getContentPane().setLayout(null);

        panel = new JPanel();
        panel.setBorder(new TitledBorder(UIManager.getBorder("TitledBorder.border"), "Login", TitledBorder.LEADING, TitledBorder.TOP, null, SystemColor.inactiveCaptionText));
        panel.setBounds(96, 140, 139, 99);
        loginFrame.getContentPane().add(panel);
        panel.setLayout(null);

        loginField = new JTextField();
        loginField.setBounds(47, 16, 86, 20);
        panel.add(loginField);
        loginField.setColumns(10);

        passwordField = new JPasswordField();
        passwordField.setBounds(47, 37, 86, 20);
        panel.add(passwordField);

        JButton loginButton = new JButton("Login");
        loginButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {

                String sql = "select * from employees where login=? and password=?";
                try{
                    PreparedStatement pst = conn.prepareStatement(sql);
                    pst.setString(1, loginField.getText());
                    pst.setString(2, passwordField.getText());
                    ResultSet rs = pst.executeQuery();
                    int countUsr = 0;
                    while(rs.next()){
                        countUsr++;
                    }
                    if(countUsr == 1){
                        loginFrame.dispose();
                        AdminFrame adminFrame = new AdminFrame();
                        adminFrame.setVisible(true);
                    }else  if(countUsr > 1){
                        JOptionPane.showMessageDialog(null, "ERR");
                    }else{
                        JOptionPane.showMessageDialog(null, "ERR");
                        passwordField.setText("");
                    }
                rs.close();
                pst.close();
                }catch(Exception e){
                    JOptionPane.showMessageDialog(null, "ERR: "+e.getMessage());
                }
            }
        });
        loginButton.setBounds(25, 65, 89, 23);
        panel.add(loginButton);
    }
}

I'm not sure which metod is better to use to close connection:

@Override
protected void finalize() throws Throwable {
    conn.close();
    super.finalize();
}

or 

finally {
    conn.close();
}

after a try catch block in button ActionListener.

In some examples people say finally block is better but what when I have many methods(4example Action Listeners) and in every of them I do some operations on DB. Should I open and close connection in all methods or just use finalize method?

Upvotes: 0

Views: 2989

Answers (6)

jferris92
jferris92

Reputation: 11

I would use a try/catch/finally for all of the methods/transactions. And you should check if the connection, result set and prepared statements are not null before closing them. Something like this for the finally block of a method:

finally {
    try {
    if (rset != null) {
        rset.close();
    }
    if (st != null) {
        st.close();
    }
    } catch (Exception e) {
    // do something
    }
}

When you are finished using the database, I would close the connection with a method:

public void close() {
if (!isOpen) return;
try {
    if (conn != null) {
    conn.close();
    }
} catch (Exception e) {
    // do something
}
isOpen = false;
conn = null;
}

Hope it helps :-)

Upvotes: 0

Luiggi Mendoza
Luiggi Mendoza

Reputation: 85789

When working with databases, there are some concepts to take into account:

  • Do not keep a connection open too much time. To accomplish this, you should open and close it in the shortest possible scope. This will also help you from using the same connection to perform multiple actions in the same transaction, specially in multi thread environments.
  • Reuse the physical connections only. This means, you open the connection once, send it to SLEEP state and reuse it in future times. To accomplish this, do not reinvent the wheel, instead use a database connection pool which automatically handles this for you.

Steps to do this:

  • At the beginning of your application, create the database connection pool.
  • In every method when you need to perform some action against the database, obtain the connection from the database connection pool, perform your actions against database, then close the connection.
  • At the end of the application, close the database connection pool.

Here's an example to do this using HikariCP:

Define the data source that will use the database connection pool:

public final class DataSourceFactory {

    private static final Logger LOG = LoggerFactory.getLogger(DataSourceFactory.class);

    private static DataSource mySQLDataSource;

    private DataSourceFactory() { }

    private static DataSource getDataSource(String configurationProperties) {
        Properties conf = new Properties();
        try {
            conf.load(DataSourceFactory.class.getClassLoader().getResourceAsStream(configurationProperties));
        } catch (IOException e) {
            LOG.error("Can't locate database configuration", e);
        }
        HikariConfig config = new HikariConfig(conf);
        HikariDataSource dataSource = new HikariDataSource(config);
        return dataSource;
    }

    public static DataSource getMySQLDataSource() {
        LOG.debug("Retrieving data source for MySQL");
        if (mySQLDataSource == null) {
            synchronized(DataSourceFactory.class) {
                if (mySQLDataSource == null) {
                    LOG.debug("Creating data source for MySQL");
                    mySQLDataSource = getDataSource("mysql-connection.properties");
                }
            }
        }
        return mySQLDataSource;
    }
}

Use the connection in the shortest possible scope. 

public class LoginFrame {
    private JFrame loginFrame;
    //remove the Connection from here, this is not the shortest possible scope
    //Connection conn = null;
    /**
     * Launch the application.
     */
    public static void main(String[] args) {
        EventQueue.invokeLater(new Runnable() {
            public void run() {
                try {
                    LoginFrame window = new LoginFrame();
                    window.loginFrame.setVisible(true);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
    }

    /**
     * Create the application.
     */
    public LoginFrame() {
        initialize();
        conn = DBConnect.connectDB();
    }

    /**
     * Initialize the contents of the frame.
     */
    private void initialize() {
        //...
        loginButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                //This is the shortest possible scope for the connection
                //Declare it here and use it
                Connection conn = DataSourceFactory.getMySQLDataSource().getConnection();
                String sql = "select * from employees where login=? and password=?";
                try{
                    PreparedStatement pst = conn.prepareStatement(sql);
                    pst.setString(1, loginField.getText());
                    pst.setString(2, passwordField.getText());
                    ResultSet rs = pst.executeQuery();
                    int countUsr = 0;
                    while(rs.next()){
                        countUsr++;
                    }
                    if(countUsr == 1){
                        loginFrame.dispose();
                        AdminFrame adminFrame = new AdminFrame();
                        adminFrame.setVisible(true);
                    }else  if(countUsr > 1){
                        JOptionPane.showMessageDialog(null, "ERR");
                    }else{
                        JOptionPane.showMessageDialog(null, "ERR");
                        passwordField.setText("");
                    }
                } catch(Exception e) {
                    //ALWAYS log the exception, don't just show a message
                    e.printStackTrace();
                    JOptionPane.showMessageDialog(null, "ERR: "+e.getMessage());
                } finally {
                    try {
                        rs.close();
                        pst.close();
                        con.close();
                    } catch (SQLException silent) {
                        //do nothing
                    }
                }
            }
        });
        loginButton.setBounds(25, 65, 89, 23);
        panel.add(loginButton);
    }
}

If you're working with Java 7 or superior, then use try-with-resources (which is syntactic sugar, after all):

loginButton.addActionListener(new ActionListener() {
    public void actionPerformed(ActionEvent arg0) {
        //This is the shortest possible scope for the connection
        //Declare it here and use it
        Connection conn = ;
        String sql = "select * from employees where login=? and password=?";
        try(Connection conn = DataSourceFactory.getMySQLDataSource().getConnection();
            PreparedStatement pst = conn.prepareStatement(sql);) {
            pst.setString(1, loginField.getText());
            pst.setString(2, passwordField.getText());
            try (ResultSet rs = pst.executeQuery();) {
                int countUsr = 0;
                while(rs.next()){
                    countUsr++;
                }
                if(countUsr == 1){
                    loginFrame.dispose();
                    AdminFrame adminFrame = new AdminFrame();
                    adminFrame.setVisible(true);
                } else if(countUsr > 1){
                    JOptionPane.showMessageDialog(null, "ERR");
                } else {
                    JOptionPane.showMessageDialog(null, "ERR");
                    passwordField.setText("");
                }
            }
        } catch(Exception e) {
            //ALWAYS log the exception, don't just show a message
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "ERR: "+e.getMessage());
        }
    }
});

Upvotes: 5

Drew Kennedy
Drew Kennedy

Reputation: 4168

The nice thing about Java is all of PreparedStatement, Connection, and ResultSet use AutoCloseable. If you want to create a method that will close all of these instances in one shot:

public static void close(AutoCloseable... closeables) {
    for (AutoCloseable c : closeables) {
        try {
            if (c != null) {
                c.close();
            }
        } catch (Exception ex) {
            //do something, Logger or or your own message
        }
    }

}

You can then call this method and toss in any instances you have created that use AutoCloseable with no fixed length of parameters.

It is best to use close() calls in a finally block because if an Exception is thrown, the finally block will execute anyway. Otherwise you may run into issues with multiple connections being left open.

Upvotes: 1

Simon Sadetsky
Simon Sadetsky

Reputation: 544

The standard way is to close it in the finally block to save DB resources and avoid leakage. The best results can be obtained using connection pooling with idle timeout: http://www.mchange.com/projects/c3p0/index.html.

Upvotes: 0

Alexander Kohler
Alexander Kohler

Reputation: 1967

If you are using Java 7 and above, I would recommend using try with resources.

The try-with-resources statement ensures that each resource is closed at the end of the statement. Any object that implements java.lang.AutoCloseable, which includes all objects which implement java.io.Closeable, can be used as a resource.

In your case: 

        try (PreparedStatement pst = conn.prepareStatement(sql))//use try with resources
            {
            pst.setString(1, loginField.getText());
            pst.setString(2, passwordField.getText());
            ResultSet rs = pst.executeQuery();
            int countUsr = 0;
            while(rs.next()){
                countUsr++;
            }
            if(countUsr == 1){
                loginFrame.dispose();
                AdminFrame adminFrame = new AdminFrame();
                adminFrame.setVisible(true);
            }else  if(countUsr > 1){
                JOptionPane.showMessageDialog(null, "ERR");
            }else{
                JOptionPane.showMessageDialog(null, "ERR");
                passwordField.setText("");
            }
        //removed rst closing, no need to close if your PreparedStatement is being closed.
        //No need to explicitly close our PreparedStatement since we are using try with resources
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, "ERR: "+e.getMessage());
        }
    }

You should also note that you don't need to close your ResultSet if you are closing your PreparedStatement. (See this answer)

Upvotes: 2

Adam111p
Adam111p

Reputation: 3727

The finally is more appropriate is always called. Remember any check that conn is not null

Upvotes: 0

Related Questions