CODEWITHSUNDEEP
mysqldatabase-connectionjavafx-8
Tomasz Mularczyk
Tomasz Mularczyk

Reputation: 36179

Opening and closing Connection within JavaFX application

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?

Upvotes: 1

Views: 2347

Answers (1)

James_D
James_D

Reputation: 209358

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.

Upvotes: 2

Related Questions