title: Opening and closing Connection within JavaFX application
body: <p>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:</p>
  
  <p><a href="https://stackoverflow.com/questions/312702/is-it-safe-to-keep-database-connections-open-for-long-time">is it safe to keep database connections open for long time</a></p>
  
  <p><a href="https://stackoverflow.com/questions/2225221/closing-database-connections-in-java">Closing Database Connections in Java</a></p>
  
  <p>One say I should keep Connection for a long time and Statements short, and second that I should close everything as soon as possible.</p>
  
  <p>Which is the better/proper way?</p>
  
  <p><strong>Example 1:</strong></p>
  
  <pre><code>    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
          };
      }
  </code></pre>
  
  <p>or is it okay to open connection which will last until application is closed:</p>
  
  <p><strong>Example 2:</strong></p>
  
  <pre><code>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-&gt;{
              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);
      }
  }
  </code></pre>
  
  <p>or maybe you know better way?</p>