Java Database Connectivity (JDBC)

I save stock data in a mySql table @ frame 1 minute, so that 1 row is 1 minute data. Data saved are as follow

http://s18.postimg.org/4uj8e0mfd/Z514.png

Since market session ranges from 0900 up to 1730, I have 511 rows for each day.

I need to compress (aggregate) these intra-day data into 1 daily data (one row only), so that

daily open -> first intra-day open value (0900 open value)

daily high -> highest value for all 511 intra-day value rows

daily low -> lowest value for all 511 intra-day value rows

daily close -> latest intra-day traded value (1730 close value)

daily volume -> sum of all 511 intra-day volume value rows

I need to perform this compression as fast as possibile since I would plot hundreds of daily data.

Any help really appreciated: here is my full code

import importexportdata.DataItem;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class AggregateByDay {   

String dbTabName  = "";   
String sTimeFrom = "";
String sTimeTo = "";   
short iTotBar = 0;
boolean volumeFieldIsOnTable;
LinkedList<DataItem> data_aggregated = null;
Dividends dividendsHdl = null;

Connection con = null;   
ResultSet rs  = null; 
Statement st  = null;
PreparedStatement selectReview=null;   
Statement stmtPrepareInitRow=null;
ResultSet rsSelectPrepareInitRow  = null;

public AggregateByDay(String dbTabName, String timeFrom, String timeTo, short iTotBar, boolean volumeFieldIsOnTable, Dividends dividendsHdl){   
  this.dbTabName  = dbTabName;
  this.sTimeFrom  = timeFrom;
  this.sTimeTo  = timeTo;   
  this.iTotBar  = iTotBar;   
  this.volumeFieldIsOnTable  = volumeFieldIsOnTable;
  this.dividendsHdl  = dividendsHdl;
}

private void connectToDb(){
  try {
  Class.forName("com.mysql.jdbc.Driver");
   //String connectionUrl = "jdbc:mysql://localhost:3306/borsaitalia?" +
   //   "user="+_dBUserName+"&password="+_dBPassword;

  String connectionUrl = DbConnect.getConnectionUrl();

  con = DriverManager.getConnection(connectionUrl);
   } //end try Class.forName("com.mysql.jdbc.Driver");   
  catch (SQLException ex) {
  System.out.println("Error "+ ex.getMessage ()+" "+ex.toString());
   } catch (ClassNotFoundException cE) {
  System.out.println("Class Not Found Exception: "+ cE.toString());
   }   
}

private String getLastFirstDayLower(String _date, String _DbTabName){
  String lastDate = "";

  try {   
  st = con.createStatement();
   if ( "".equals(_date)) {
   //Get the most recent date
  rs = st.executeQuery("SELECT date FROM "+_DbTabName+" ORDER BY date DESC, time DESC LIMIT 1;");
   }
   else{
  rs = st.executeQuery("SELECT date FROM "+_DbTabName+" WHERE date < "+_date+" ORDER BY date DESC, time DESC LIMIT 1;");
   }
   while (rs.next()){
  lastDate  = rs.getString("date");   
   }

   if (st != null) {
  st.close();
  st = null;
   }
   } //end try Class.forName("com.mysql.jdbc.Driver");   
  catch (SQLException ex) {
  System.out.println("Error "+ ex.getMessage ()+" "+ex.toString());
   } 
   return lastDate;   
}

private String getLastFirstDayLowerNext(String _date, String _DbTabName){
  String lastDate = "";

  try {   
   if (rsSelectPrepareInitRow.next()){
  lastDate  = rsSelectPrepareInitRow.getString("date");   
   }
   //System.out.println("lastDate "+lastDate);
   } //end try Class.forName("com.mysql.jdbc.Driver");   
  catch (SQLException ex) {
  System.out.println("Error "+ ex.getMessage ()+" "+ex.toString());
   } 
   return lastDate;   
}

private LinkedList<DataItem> getDbOneMinuteDataForDay(String _date, String _sTimeFrom, String _sTimeTo){

  String sDate = "", sTime = "";
   double dOpen = 0d,  dHigh = 0d, dLow = 0d, dClose = 0d;
  int iVolume = 0;
  float dividendsValue = 0;

  LinkedList<DataItem> init_one_minute_data = new LinkedList<>();
  try { 
  selectReview.setString(1, _date);
  selectReview.setString(2, _sTimeFrom);
  selectReview.setString(3, _sTimeTo);
  rs = selectReview.executeQuery();

   while (rs.next()){

  sDate  = rs.getString("date");
  sTime  = rs.getString("time");
  dOpen  = rs.getDouble("open");
  dHigh  = rs.getDouble("high");
  dLow  = rs.getDouble("low");
  dClose  = rs.getDouble("close");

   if (volumeFieldIsOnTable)
  iVolume = rs.getInt("volume"); 
   else
  iVolume = 0;

  DataItem dataItem = new DataItem();

  java.util.Date date = null;
  try {
  date = new SimpleDateFormat("yyyyMMdd HHmm").parse(sDate+" "+sTime);
   } catch (ParseException ex) {
  Logger.getLogger(AggregateByDay.class.getName()).log(Level.SEVERE, null, ex);
   }
  dataItem.setDate(date);

   if (dividendsHdl != null) {
  dividendsValue = dividendsHdl.getTotalDividendsFromDate(sDate);
   }
  dataItem.setOpen(dOpen-dividendsValue);
  dataItem.setHigh(dHigh-dividendsValue);
  dataItem.setLow(dLow-dividendsValue);
  dataItem.setClose(dClose-dividendsValue);
  dataItem.setVolume(iVolume);

  init_one_minute_data.add(dataItem);   
   }
   } //end try Class.forName("com.mysql.jdbc.Driver");   
  catch (SQLException ex) {
  System.out.println("Error "+ ex.getMessage ()+" "+ex.toString());
   }/* catch (ClassNotFoundException cE) {
  System.out.println("Class Not Found Exception: "+ cE.toString());
   }*/

   return init_one_minute_data;
}

public void getAggregateValue(ArrayList<DataItem> externalDataList) {

  String sDate;
  short count_min;
  Calendar calendarCurrWeek = null;
  int i, n;
  boolean  end;
  LinkedList<DataItem>  one_minute_data = null;
  LinkedList<DataItem>  data_one_day_aggregated = new LinkedList<>();
  java.util.Date dDateTimeRgp = null;
  int iVolumeRaggruppato = 0;
   double  dOpenRgp = 0d,  dHighRgp = 0d, dLowRgp = 0d, dCloseRgp = 0d;

  data_aggregated = new LinkedList<>();

  connectToDb();
  sDate = getLastFirstDayLower("", dbTabName);
   if ( "".equals(sDate) ) {
   return;
   }

   end = false;

  try {
   //selectPrepareInitRow = con.prepareStatement("SELECT date FROM "+DbTabName+" WHERE date < ? ORDER BY date DESC, time DESC LIMIT 1;");   

  stmtPrepareInitRow = con.createStatement();
  rsSelectPrepareInitRow = stmtPrepareInitRow.executeQuery("SELECT DISTINCT date FROM "+dbTabName+" WHERE date < "+sDate+" ORDER BY date DESC, time DESC;");
   } catch (SQLException ex) {
  Logger.getLogger(AggregateByDay.class.getName()).log(Level.SEVERE, null, ex);
   }

   if (volumeFieldIsOnTable) {
  try {

  selectReview = con.prepareStatement("SELECT open, high, low, close, volume, date, time FROM "+dbTabName+" WHERE date = ? AND time >= ? AND time <= ? ORDER BY time ASC;");
   } catch (SQLException ex) {
  Logger.getLogger(AggregateByDay.class.getName()).log(Level.SEVERE, null, ex
This post has been answered by unknown-7404 on Jan 8 2014
Jump to Answer