title: Java: How to efficiently read from database?
body: <p>Im trying to read out a column from an sql database in java, I want the result to be returned in an array. Here is the function:</p>
  
  <pre><code>public static double[] open(Connection conn,String symbol,int startdate,int enddate)                 throws SQLException {
      int id = database.get_stockid(conn, symbol);
      Statement stat = conn.createStatement();
      ResultSet rs = stat.executeQuery("select price_open from stock_data where stock_id="+id+" and date&gt;="+startdate+" and date&lt;="+enddate+";");
      ArrayList&lt;Double&gt; data = new ArrayList&lt;Double&gt;();
      while(rs.next()) {
          data.add(rs.getDouble("open"));
      }
      double[] data1 = new double[data.size()];
      for(int a = 0; a &lt; data1.length; ++a) {
          data1[a]=data.get(a);
      }
      return data1;
  }
  </code></pre>
  
  <p>This is quite slow. It Takes 1.5 secs with my sqlite database. Is this the standard way to read out a column or am I doing something wrong? This is the bottleneck in my application, so I need it to be as fast as possible.</p>
  
  <hr>
  
  <p>edited:
  thanks. I just found out that the ArrayList is not causing the problem. The bottleneck must lie in the sql part: 
  If I load data for just 10 days it takes as long as loading data for 10 years. SO I have to improve on my sql but how??</p>
  
  <p>Here the improved code:</p>
  
  <pre><code>public static double[] open(Connection conn,String symbol,int startdate,int enddate) throws SQLException {
      int id = database.get_stockid(conn, symbol);
  
      PreparedStatement stat = conn.prepareStatement("select price_open from stock_data where (stock_id="+id +") and (date between "+startdate+" and "+enddate+");");
      ResultSet rs = stat.executeQuery();
      ArrayList&lt;Double&gt; data = new ArrayList&lt;Double&gt;();
      while(rs.next()) {
          data.add(rs.getDouble(1));
      }
      double[] data1 = new double[data.size()];
      for(int a = 0; a &lt; data1.length; ++a) {
          data1[a]=data.get(a);
      }
      return data1;
  }
  </code></pre>