Overview

The following example demonstrates how tick data can be saved to MySQL database.

Creating a database table

Use the following CREATE TABLE statement in order to create a database table for tick data.

CREATE TABLE ticks(
instrument char(7), 
tick_time vаrchar(255), 
ask vаrchar(255), 
bid vаrchar(255),
askVol vаrchar(255), 
bidVol vаrchar(255)
);

Method onStart

The onStart method is used to set up connection properties. Replace username and password with the actual username and password to your MySQL database.

public void onStart(IContext context) throws JFException {
    this.context = context;
    console = context.getConsole();
    try {
        java.util.Properties properties = new java.util.Properties();
        properties.put("driverClassName", "com.mysql.jdbc.Driver");
        properties.put("url", "jdbc:mysql://localhost/test");
        properties.put("username", "username");
        properties.put("password", "password");
        properties.put("poolPreparedStatements", "true");

        dataSource = BasicDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
        console.getErr().println(e);
    }
}

Method onTick

The onTick method is used to create and then execute the appropriate INSЕRT INTO statement.

public void onTick(Instrument instrument, ITick tick) throws JFException {
    try {
        Connection connection = dataSource.getConnection();
    try {
        PreparedStatement statement = connection.prepareStatement("insеrt into ticks(instrument, tick_time, ask, bid, askVol, bidVol) values (?, ?, ?, ?, ?, ?)");
        try {
            statement.setString(1, instrument.toString());
        statement.setTimestamp(2, new Timestamp(tick.getTime()), gmtCalendar);
        statement.setDouble(3, tick.getAsk());
        statement.setDouble(4, tick.getBid());
        statement.setDouble(5, tick.getAskVolume());
        statement.setDouble(6, tick.getBidVolume());
        statement.execute();
        } finally {
        statement.close();
        }
    } finally {
        connection.close();
    }
    } catch (SQLException e) {
        console.getErr().println(e);
    }
}

Method onStop

Close all connections associated with the data source:

public void onStop() throws JFException {
    try {
        ((BasicDataSource) dataSource).close();
    } catch (SQLException e) {
        console.getErr().println(e);
    }
}

Download

TestMySQLAccess.java

External links

Apache Commons Pool
Apache Commons DBCP
MySQL Connector/J