Opening and closing Connection within JavaFX application

Question

I want to write little application operating on MySQL database. However after reading this two topics below I got confused what is the proper way to work with Connection to database:

is it safe to keep database connections open for long time

Closing Database Connections in Java

One say I should keep Connection for a long time and Statements short, and second that I should close everything as soon as possible.

Which is the better/proper way?

Example 1:

    private void query(){
        final String query = "SELECT * FROM database;";
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setServerName("localhost");
        dataSource.setDatabaseName("database");
        dataSource.setUser("root");
        dataSource.setPassword("password");

        try( Connection connection = dataSource.getConnection() ){
            try( PreparedStatement preparedStatement = connection.prepareStatement(query) ){
                try( ResultSet resultSet = preparedStatement.executeQuery() ){
                    //--- working with resultset
                }
            }
        }catch(Exception exception){
            //---- handling exception
        };
    }

or is it okay to open connection which will last until application is closed:

Example 2:

public class Main extends Application {

    public static Connection connection; //I will use this everywhere

    @Override
    public void start(Stage primaryStage) {
        //============ opening connection and setting on close request
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setServerName("localhost");
        dataSource.setDatabaseName("database");
        dataSource.setUser("root");
        dataSource.setPassword("password");
        try {
            connection = dataSource.getConnection();
            System.out.println("connected to " + dataSource.getDatabaseName());
        } catch (SQLException e) {
            //---- exception
        }

        primaryStage.setOnCloseRequest(e->{
            try {
                connection.close();
                System.out.println("connection closed");
            } catch (Exception exc) { 
                System.err.println("couldn't close connection");
            }
        });


        try {
            BorderPane root = (BorderPane)FXMLLoader.load(getClass().getResource(CONSTANTS.ROOT_MAIN_WINDOW.string));
            Scene scene = new Scene(root);
            scene.getStylesheets().add(getClass().getResource("/view/application.css").toExternalForm());
            primaryStage.setScene(scene);
            primaryStage.show();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        launch(args);
    }
}

or maybe you know better way?

score 2 · Answer 1 · answered Apr 07 '15 at 01:15

2

There's no contradiction between the two questions you cite. The first says "It's fine to keep a connection open for a long time." The second says "You must close the connection when you are finished with it". So you can keep it open as long as you want, as long as you close it once you are done. What you must not do is repeatedly open new connections and not close them.

More specifically, opening a connection is a time consuming operation. So you actually want to avoid doing that too often; keeping the connection open is a way to achieve that.

In a server-side application, having a single connection will create a bottleneck as you try to service requests from multiple users. In this case a pool of connections should be utilized (the application server will provide this functionality). In a client-side application such as a JavaFX application, in typical use, the connection is only used in response to the action of a single user, so reusing a single connection is a reasonable approach.

answered Apr 07 '15 at 01:15

James_D

201,275
16
291
322

I found out that only the first time connecting to server takes long time. When I do it again it's much faster. I'm thinking now that it will be best and safer to use code in the first example. – Tomasz Mularczyk Apr 07 '15 at 16:11
It sounds like the data source implementation is caching the open connection for you. So when you retrieve it the first time, it opens it; when you "close" it (implicitly via the try-with-resources construct), it keeps it open but marks it as available again. Then subsequent calls to `getConnection()` take less time. It may also be implementing a pool for you: I am not familiar with `MysqlDataSource`. – James_D Apr 07 '15 at 17:11
_If you open a connection when you need it and dispose of it when you've finished, that will not actually close the connection, it'll just return it to the connection pool to be used again._ Found here: [link](http://programmers.stackexchange.com/questions/142065/creating-database-connections-do-it-once-or-for-each-query) You think it's the same with java? – Tomasz Mularczyk Apr 07 '15 at 19:45
1

"With Java" is too general. It is definitely not true if you retrieve the connection from the `DriverManager` and call `close` on it. It definitely is true if you retrieve it from a `DataSource` that implements connection pooling (that's effectively what "connection pooling" means. I don't know how `MysqlDataSource` is implemented (and could find good documentation on a quick search), but it seems reasonable to assume that's what it's doing. – James_D Apr 07 '15 at 19:48

Opening and closing Connection within JavaFX application

1 Answers1