Java tutorial
/* This file is part of jTotus. jTotus is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. jTotus is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with jTotus. If not, see <http://www.gnu.org/licenses/>. */ package org.jtotus.database; import java.math.BigDecimal; import java.sql.*; import java.util.*; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.commons.lang.ArrayUtils; import org.joda.time.DateTime; import org.joda.time.format.DateTimeFormat; import org.joda.time.format.DateTimeFormatter; import org.jtotus.common.DateIterator; /** * @author Evgeni Kappinen */ public class LocalJDBC implements InterfaceDataBase { private static final DateTimeFormatter formatter = DateTimeFormat.forPattern("dd-MM-yyyy"); private boolean debug = false; private DataFetcher fetcher = null; @Override public double[] fetchDataPeriod(String stockName, DateTime fromDate, DateTime toDate, String type) { throw new UnsupportedOperationException("Not supported yet."); } public static enum DataTypes { CLOSE, VOLUME }; private Connection getConnection() throws SQLException { //FIXME: login & password from config return DriverManager.getConnection( "jdbc:h2:~/.jtotus/local_database;LOG=0;CACHE_SIZE=65536;LOCK_MODE=0;UNDO_LOG=0", "sa", "sa"); } //TODO;create procedures private void createTable(Connection con, String stockTable) { PreparedStatement createTableStatement = null; try { String statement = "CREATE TABLE IF NOT EXISTS " + stockTable + " (" + "ID IDENTITY AUTO_INCREMENT," + "DATE DATE," + "TIME TIME," + "OPEN DECIMAL(18,4)," + "CLOSE DECIMAL(18,4)," + "HIGH DECIMAL(18,4)," + "LOW DECIMAL(18,4)," // + "AVRG DECIMAL(18,4)," // + "TRADES DECIMAL(18,4)," + "VOLUME INT," + "PRIMARY KEY(ID));"; createTableStatement = con.prepareStatement(statement); createTableStatement.execute(); } catch (SQLException ex) { Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } } public BigDecimal fetchClosingPrice(String stockName, DateTime date) { return this.fetchData(stockName, date, "CLOSE"); } public void setDebug(boolean debug) { this.debug = debug; } public BigDecimal fetchData(String tableName, DateTime date, String column) { BigDecimal retValue = null; PreparedStatement pstm = null; Connection connection = null; ResultSet results = null; try { connection = getConnection(); String statement = "SELECT " + column + " FROM " + this.normTableName(tableName) + " WHERE DATE=?"; this.createTable(connection, this.normTableName(tableName)); pstm = connection.prepareStatement(statement); java.sql.Date sqlDate = new java.sql.Date(date.getMillis()); pstm.setDate(1, sqlDate); if (debug) { System.out.printf("Fetching:'%s' from'%s' Time" + date.toDate() + " Stm:%s\n", column, tableName, statement); } results = pstm.executeQuery(); // System.out.printf("Results:%d :%d :%s (%d)\n",results.getType(), results.findColumn(column), results.getMetaData().getColumnLabel(1),java.sql.Types.DOUBLE); if (results.next()) { retValue = results.getBigDecimal(column); } } catch (SQLException ex) { System.err.printf("LocalJDBC Unable to find date for:'%s' from'%s' Time" + date.toDate() + "\n", column, tableName); // Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (results != null) { results.close(); results = null; } if (pstm != null) { pstm.close(); pstm = null; } if (connection != null) { connection.close(); connection = null; } } catch (SQLException ex) { Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } } return retValue; } public void setFetcher(DataFetcher fetcher) { this.fetcher = fetcher; } public DataFetcher getFetcher() { if (this.fetcher == null) { this.fetcher = new DataFetcher(); } return this.fetcher; } public double[] fetchPeriod(String tableName, DateTime startDate, DateTime endDate, String type) { BigDecimal retValue = null; PreparedStatement pstm = null; java.sql.Date retDate = null; ResultSet results = null; ArrayList<Double> closingPrices = new ArrayList<Double>(600); Connection connection = null; try { String query = "SELECT " + type + ", DATE FROM " + this.normTableName(tableName) + " WHERE DATE>=? AND DATE<=? ORDER BY DATE ASC"; // this.createTable(connection, this.normTableName(tableName)); connection = this.getConnection(); pstm = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); java.sql.Date startSqlDate = new java.sql.Date(startDate.getMillis()); pstm.setDate(1, startSqlDate); java.sql.Date endSqlDate = new java.sql.Date(endDate.getMillis()); pstm.setDate(2, endSqlDate); DateIterator dateIter = new DateIterator(startDate, endDate); results = pstm.executeQuery(); DateTime dateCheck; if (debug) { System.out.printf("start data %s end date: %s\n", startSqlDate.toString(), endSqlDate.toString()); } while (dateIter.hasNext()) { dateCheck = dateIter.nextInCalendar(); if (results.next()) { retValue = results.getBigDecimal(1); retDate = results.getDate(2); DateTime compCal = new DateTime(retDate.getTime()); if (compCal.getDayOfMonth() == dateCheck.getDayOfMonth() && compCal.getMonthOfYear() == dateCheck.getMonthOfYear() && compCal.getYear() == dateCheck.getYear()) { closingPrices.add(retValue.doubleValue()); continue; } else { results.previous(); } } BigDecimal failOverValue = getFetcher().fetchData(tableName, dateCheck, type); if (failOverValue != null) { closingPrices.add(failOverValue.doubleValue()); } } } catch (SQLException ex) { System.err.printf("LocalJDBC Unable to find date for:'%s' from'%s' Time" + startDate.toDate() + "\n", "Cosing Price", tableName); ex.printStackTrace(); SQLException xp = null; while ((xp = ex.getNextException()) != null) { xp.printStackTrace(); } } finally { try { if (results != null) results.close(); if (pstm != null) pstm.close(); if (connection != null) connection.close(); // System.out.printf("Max connect:%d in use:%d\n",mainPool.getMaxConnections(), mainPool.getActiveConnections()); // mainPool.dispose(); } catch (SQLException ex) { Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } } return ArrayUtils.toPrimitive(closingPrices.toArray(new Double[0])); } public BigDecimal fetchVolume(String stockName, DateTime date) { return this.fetchData(this.normTableName(stockName), date, "VOLUME"); } public void storeData(String stockName, DateTime date, BigDecimal value, String type) { PreparedStatement pstm = null; Connection connection = null; try { String table = this.normTableName(stockName); connection = this.getConnection(); //upsert this.createTable(connection, table); String query = "MERGE INTO " + table + " (ID,DATE," + type + ") VALUES((SELECT ID FROM " + table + " ID WHERE DATE=?), ?, ?)"; pstm = connection.prepareStatement(query); java.sql.Date sqlDate = new java.sql.Date(date.getMillis()); pstm.setDate(1, sqlDate); pstm.setDate(2, sqlDate); System.out.printf("Inserting :%f :%s time:%s\n", value.doubleValue(), stockName, date.toDate().toString()); pstm.setDouble(3, value.doubleValue()); pstm.execute(); } catch (SQLException ex) { Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (pstm != null) { pstm.close(); } if (connection != null) { connection.close(); } } catch (SQLException ex) { Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } } } public HashMap<String, Double> fetchPeriodAsMap(String tableName, DateTime startDate, DateTime endDate) { HashMap<String, Double> retMap = new HashMap<String, Double>(); BigDecimal retValue = null; PreparedStatement pstm = null; java.sql.Date retDate = null; ResultSet results = null; Connection connection = null; try { String query = "SELECT CLOSE, DATE FROM " + this.normTableName(tableName) + " WHERE DATE>=? AND DATE<=? ORDER BY DATE ASC"; // this.createTable(connection, this.normTableName(tableName)); connection = this.getConnection(); pstm = connection.prepareStatement(query); java.sql.Date startSqlDate = new java.sql.Date(startDate.getMillis()); pstm.setDate(1, startSqlDate); java.sql.Date endSqlDate = new java.sql.Date(endDate.getMillis()); pstm.setDate(2, endSqlDate); System.out.printf("fetchPeriod : %s : %s\n", startSqlDate, endSqlDate); DateIterator iter = new DateIterator(startDate, endDate); results = pstm.executeQuery(); DateTime dateCheck; while (results.next()) { retValue = results.getBigDecimal(1); retDate = results.getDate(2); if (retValue == null || retDate == null) { System.err.println("Database is corrupted!"); System.exit(-1); } if (iter.hasNext()) { dateCheck = iter.nextInCalendar(); DateTime compCal = new DateTime(retDate.getTime()); if (debug) { if (retValue != null) { System.out.printf("Fetched:\'%s\' from \'%s\' : value:%f date:%s\n", "Closing Price", tableName, retValue.doubleValue(), retDate.toString()); } else { System.out.printf("Fetched:\'%s\' from \'%s\' : value:%s date:%s\n", "Closing Price", tableName, "is null", retDate.toString()); } } if (compCal.getDayOfMonth() == dateCheck.getDayOfMonth() && compCal.getMonthOfYear() == dateCheck.getMonthOfYear() && compCal.getYear() == dateCheck.getYear()) { retMap.put(formatter.print(compCal), retValue.doubleValue()); continue; } while (((compCal.getDayOfMonth() != dateCheck.getDayOfMonth()) || (compCal.getMonthOfYear() != dateCheck.getMonthOfYear()) || (compCal.getYear() != dateCheck.getYear())) && dateCheck.isBefore(compCal)) { if (fetcher != null) { BigDecimal failOverValue = getFetcher().fetchData(tableName, dateCheck, "CLOSE"); if (failOverValue != null) { retMap.put(formatter.print(dateCheck), retValue.doubleValue()); } if (iter.hasNext()) { System.err.printf("Warning : Miss matching dates for: %s - %s\n", retDate.toString(), dateCheck.toString()); dateCheck = iter.nextInCalendar(); continue; } } else { System.err.printf("Fatal missing fetcher : Miss matching dates: %s - %s\n", retDate.toString(), dateCheck.toString()); return null; } } } } while (iter.hasNext()) { retValue = getFetcher().fetchData(tableName, iter.nextInCalendar(), "CLOSE"); if (retValue != null) { retMap.put(formatter.print(iter.getCurrentAsCalendar()), retValue.doubleValue()); } } } catch (SQLException ex) { System.err.printf("LocalJDBC Unable to find date for:'%s' from'%s' Time" + startDate.toDate() + "\n", "Cosing Price", tableName); // ex.printStackTrace(); // SQLException xp = null; // while((xp = ex.getNextException()) != null) { // xp.printStackTrace(); // } } finally { try { if (results != null) results.close(); if (pstm != null) pstm.close(); if (connection != null) connection.close(); // System.out.printf("Max connect:%d in use:%d\n",mainPool.getMaxConnections(), mainPool.getActiveConnections()); // mainPool.dispose(); } catch (SQLException ex) { Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } } return retMap; } public static String normTableName(String name) { return name.replace(" ", "").replace("-", ""); } public void storeClosingPrice(String stockName, DateTime date, BigDecimal value) { this.storeData(stockName, date, value, "CLOSE"); } public void storeVolume(String stockName, DateTime date, BigDecimal value) { this.storeData(stockName, date, value, "VOLUME"); } public long entryExists(Connection con, String stockName, DateTime date) { long retValue = 0; PreparedStatement pstm = null; try { String statement = "SELECT ID FROM " + this.normTableName(stockName) + " WHERE DATE=?"; pstm = con.prepareStatement(statement); java.sql.Date sqlDate = new java.sql.Date(date.getMillis()); pstm.setDate(1, sqlDate); ResultSet results = pstm.executeQuery(); if (results.next()) { retValue = results.getLong(1); } } catch (SQLException ex) { Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } finally { if (pstm != null) { try { pstm.close(); } catch (SQLException ex) { Logger.getLogger(LocalJDBC.class.getName()).log(Level.SEVERE, null, ex); } } } return retValue; } }