TickPointDao.java

/*******************************************************************************
 * Copyright (c) 2013 Luigi Sgro. All rights reserved. This
 * program and the accompanying materials are made available under the terms of
 * the Eclipse Public License v1.0 which accompanies this distribution, and is
 * available at http://www.eclipse.org/legal/epl-v10.html
 *
 * Contributors:
 *     Luigi Sgro - initial API and implementation
 ******************************************************************************/
package com.quantcomponents.series.jdbc.derby;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.quantcomponents.core.model.DataType;
import com.quantcomponents.marketdata.ITickPoint;
import com.quantcomponents.marketdata.TickPoint;
import com.quantcomponents.series.jdbc.ITickPointDao;

public class TickPointDao implements ITickPointDao {
	public static final String TABLE_NAME = "TICK";
	public static final String SELECT_FIELDS = "DATE_TIME, DATA_TYPE, PRICE, SIZE";
	public static final String INSERT_FIELDS = "SDB_ID, " + SELECT_FIELDS;
	private final Connection connection;
	
	public TickPointDao(Connection connection) {
		this.connection = connection;
	}

	@Override
	public void initDb() throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("SELECT COUNT(*) FROM SYS.SYSTABLES WHERE TABLENAME = '" + TABLE_NAME + "'");
		ResultSet rs = stmt.executeQuery();
		rs.next();
		int numberOfTables = rs.getInt(1);
		if (numberOfTables == 0) {
			stmt = connection.prepareStatement("CREATE TABLE " + TABLE_NAME +
					" (ID BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + 
					"SDB_ID VARCHAR(200)," +
					"DATE_TIME TIMESTAMP," +
					"DATA_TYPE VARCHAR(50)," +
					"PRICE DECIMAL(30,10)," +
					"SIZE INT)");
			stmt.execute();
			connection.commit();
		}
		rs.close();
	}
	
	@Override
	public void save(String stockDatabaseId, ITickPoint item) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("INSERT INTO " + TABLE_NAME +
				" (" + INSERT_FIELDS + ") VALUES (?, ?, ?, ?, ?)");
		stmt.setString(1, stockDatabaseId);
		stmt.setTimestamp(2, new Timestamp(item.getIndex().getTime()));
		stmt.setString(3, item.getDataType().name());
		stmt.setDouble(4, item.getValue());
		stmt.setInt(5, item.getSize());
		stmt.execute();
	}

	@Override
	public List<ITickPoint> find(String stockDatabaseId) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("SELECT " + SELECT_FIELDS + " FROM " + TABLE_NAME + " WHERE SDB_ID = ? ORDER BY DATE_TIME");
		stmt.setString(1, stockDatabaseId);
		List<ITickPoint> result = new LinkedList<ITickPoint>();
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			Date date = new Date(rs.getTimestamp(1).getTime());
			DataType dataType = DataType.valueOf(rs.getString(2));
			Double price = rs.getDouble(3);
			Integer size = rs.getInt(4);
			TickPoint point = new TickPoint(date, dataType, price, size);
			result.add(point);
		}
		rs.close();
		return result;
	}

	@Override
	public void deleteAll(String stockDatabaseId) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("DELETE FROM " + TABLE_NAME + " WHERE SDB_ID = ?");
		stmt.setString(1, stockDatabaseId);
		stmt.execute();
	}
	
	@Override
	public void flush() throws SQLException {
		connection.commit();
	}
}