title: Java/javaFX: new connection to db for every query?
body: <p>So, I am creating an application in javaFX (for learning purposes only) which mainly consists of a tabpane with sub-fxmls for each tab. Each tab consist of a tableview with data collected from a database. To connect to the database I have a separate connection class that creates a new connection pool using c3p0.</p>
  
  <p>My first question is: Should I create (and close) a new connection for every query I make to the database? </p>
  
  <p>Example: upon entering a new tab I create a new connection, collect the data and then close the connection (with statement and resultset). </p>
  
  <p>If not, how would I do this in a proper and efficient way?</p>
  
  <p>My second question: Currently my application is doing what's described above, but I notice that if I leave the application open for a while, without interacting with it, whenever I start interacting with it again it freezes for a bit before it continues. The current table that is viewed at that time then shows "no data in table", until I switch to another tab and then the data is displayed properly again. Why does this happen?</p>
  
  <p>I am quite new to Java in general, and I couldn't find anything by searching that resembled my problem. My database is a postgres db.</p>
  
  <p><strong>EDIT 1:</strong>
  Regarding question 2:
  I have followed the mvc-pattern for this, not sure if that's a good idea, but that's another question.</p>
  
  <p>There are four classes that play a part in a table's creation, which are:</p>
  
  <p>Controller class:</p>
  
  <pre><code>public class StockController {
  
  //tableview from fxml-file
  @FXML
  private TableView&lt;List&lt;Object&gt;&gt; stockTable;
  
  //
  private BuildDataModel stockData = new BuildDataModel();
  
  //Called from main whenever currently active tab changes to this tab.
  public void CreateView(){
      String query = "SELECT component.name, stock.* FROM component NATURAL JOIN stock";
  
      stockData.BuildData(stockTable, query);
  
  }
  //Called from main whenever currently active tab changes to another tab.
  public void DestroyView(){
      if(!stockTable.getColumns().isEmpty()) {
          stockData.DeleteData();
      }
  }
  }
  </code></pre>
  
  <p>Model class:</p>
  
  <pre><code>public class BuildDataModel {
  
  private Connection conn;
  @FXML
  private TableView&lt;List&lt;Object&gt;&gt; tableview;
  
  public TableData data;
  
  private TableData getAllData(String query){
      List&lt;List&lt;Object&gt;&gt; data = new ArrayList&lt;&gt;();
      List&lt;String&gt; columnNames = new ArrayList&lt;&gt;();
      PreparedStatement st = null;
      ResultSet rs = null;
  
      try {
          conn = PostgresConnection.CreateConnection();
          st = conn.prepareStatement(query);
          rs = st.executeQuery();
  
          //We create columns
          int columnCount = rs.getMetaData().getColumnCount();
          for(int i = 1; i &lt;= columnCount; i++) {
              String columnName = rs.getMetaData().getColumnName(i);
              columnNames.add(columnName);
          }
          //We populate our tabledata object with the data
          while(rs.next()){
              List&lt;Object&gt; row = new ArrayList&lt;&gt;();
              for(int i = 1; i &lt;= columnCount; i++){
                  row.add(rs.getObject(i));
  
              }
              data.add(row);
          }
      }
      catch (Exception e){
          e.printStackTrace();
      }
      finally {
          DbUtils.closeQuietly(conn, st, rs);
  
      }
      return new TableData(columnNames, data);
  }
  //Called from controller class, calls getAllData with the specified query
  //and populates tableview based on this.
  public void BuildData(TableView&lt;List&lt;Object&gt;&gt; tableview, String query) {
      this.tableview = tableview;
      try {
          data = getAllData(query);
          for (int i = 0 ; i &lt; data.getNumColumns(); i++) {
              TableColumn&lt;List&lt;Object&gt;, Object&gt; column = new TableColumn&lt;&gt;(data.getColumnName(i));
              int columnIndex = i;
              column.setCellValueFactory(cellData -&gt;
                      new SimpleObjectProperty&lt;&gt;(cellData.getValue().get(columnIndex)));
              tableview.getColumns().add(column);
          }
  
          tableview.getItems().setAll(data.getData());
      }
      catch (Exception e) {
      }
  
  }
  
  public void DeleteData(){
      tableview.getColumns().clear();
  }}
  </code></pre>
  
  <p>Connection class:</p>
  
  <pre><code>public class PostgresConnection {
  
  //DB credentials, modify as needed
  private static final String HOST     = "localhost";
  private static final String DATABASE = "&lt;INSERT DATABASE HERE&gt;";
  private static final String USERNAME = "&lt;INSERT USERNAME HERE&gt;";
  private static final String PASSWORD = "&lt;INSERT PASSWORD HERE&gt;";
  
  //Default postgresql port, don't change unless you know what you're doing.
  private static final String PORT     = "5432";
  private static final String URL      = "jdbc:postgresql://" + HOST + ":" + PORT + "/" + DATABASE;
  private static final String DRIVER_NAME = "org.postgresql.Driver";
  
  private static Connection conn;
  
  //Disables C3P0-pool logging
  static {
      Properties p = new Properties(System.getProperties());
      p.put("com.mchange.v2.log.MLog", "com.mchange.v2.log.FallbackMLog");
      p.put("com.mchange.v2.log.FallbackMLog.DEFAULT_CUTOFF_LEVEL", "OFF");
      System.setProperties(p);
  }
  
  private static ComboPooledDataSource cpds = new ComboPooledDataSource();
  
  public static Connection CreateConnection() throws SQLException {
      try {
          cpds.setDriverClass(DRIVER_NAME);
      } catch (PropertyVetoException e) {
          e.printStackTrace();
      }
      cpds.setJdbcUrl(URL);
      cpds.setUser(USERNAME);
      cpds.setPassword(PASSWORD);
      cpds.setMinPoolSize(3);
      cpds.setAcquireIncrement(5);
      cpds.setMaxPoolSize(100);
      cpds.setMaxStatements(180);
      return cpds.getConnection();
  }}
  </code></pre>
  
  <p>and DAO:</p>
  
  <pre><code>public class TableData {
  
  private final List&lt;String&gt; columnNames;
  private final List&lt;List&lt;Object&gt;&gt; data;
  
  public TableData(List&lt;String&gt; columnNames, List&lt;List&lt;Object&gt;&gt; data) {
      this.columnNames = columnNames;
      this.data = data;
  }
  
  public int getNumColumns() {
      return columnNames.size();
  }
  
  public String getColumnName(int index) {
      return columnNames.get(index);
  }
  
  public int getNumRows() {
      return data.size();
  }
  
  public Object getData(int column, int row) {
      return data.get(row).get(column);
  }
  
  public List&lt;List&lt;Object&gt;&gt; getData() {
      return data;
  }
  public void removeData(){
      data.clear();
      columnNames.clear();
  }}
  </code></pre>