Develop JDBC

JDBC Application Development

Elastic MDS content is also reachable over standard JDBC: you connect with the MetaFluent JDBC driver, run a SELECT, and read rows through a ResultSet - the API you already know. It is the same content the JMS Application Development guide subscribes to; JMS streams it to you, JDBC lets you query it with SQL.

Audience: Java developers (the JDBC model is standard; only the driver and connection are MetaFluent-specific). No prior MetaFluent knowledge assumed.

One difference from an ordinary database is worth stating up front: a MetaFluent result set is live. The rows track the market - re-read the ResultSet and you see current values, for as long as you keep it open.

---

The SDK

The driver and the example applications ship in the MetaFluent JDBC SDK (jdbc-sdk). The default clone gives the latest release:

git clone --depth 1 git@github.com:MetaFluent/jdbc-sdk.git

The SDK contains:

• lib/metafluent.jdbc.jar - the JDBC driver (com.metafluent.jdbc.rtc.DriverImpl). Add this one jar to your classpath to build against Elastic MDS.
• examples/src/... - the source for the example applications, including JDBCTestApp.
• bin/JDBCTestApp.jar - the example prebuilt, so you can run it without building.

The driver registers itself, so DriverManager finds it automatically. (If your environment needs an explicit driver, name it: -Djdbc.drivers=com.metafluent.jdbc.rtc.DriverImpl.)

---

A first query

JDBCTestApp (in the SDK) is a complete, console-based query client. Its structure is the standard JDBC pattern: get a Connection, create a Statement, execute a SELECT, and read the ResultSet.

import java.sql.*;
import java.util.Properties;

public class FirstQuery {
    public static void main(String[] args) throws Exception {
        Properties props = new Properties();
        props.setProperty("com.metafluent.jdbc.application-name", "FirstQuery");
        props.setProperty("com.metafluent.jdbc.application-version", "1.0.0");
        props.setProperty("com.metafluent.jdbc.user", "guest");

        // The MetaFluent JDBC provider connects through the session server (port 8900).
        String url = "jdbc-mfrtc-session-json://mf-session:8900";

        try (Connection conn = DriverManager.getConnection(url, props);
             Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                                   ResultSet.CONCUR_UPDATABLE)) {

            ResultSet rs = stmt.executeQuery(
                "SELECT RIC, DSPLY_NAME, BID, ASK, TRDPRC_1 " +
                "FROM Quotes.RDF WHERE RIC IN ('SAB.L', 'BG.L', 'REX.L', 'WG.L')");

            // A MetaFluent result set is live. Re-scroll it to see current values.
            while (true) {
                rs.beforeFirst();
                while (rs.next()) {
                    System.out.printf("%-8s %-24s %10.4f %10.4f %10.4f%n",
                        rs.getString("RIC"), rs.getString("DSPLY_NAME"),
                        rs.getDouble("BID"), rs.getDouble("ASK"), rs.getDouble("TRDPRC_1"));
                }
                Thread.sleep(1000);   // redisplay once a second
            }
        }
    }
}

mf-session is the conventional name of the session server, mapped to your deployment the same way the Quick Start maps mf-api-gateway (on the single-host Quick Start, localhost:8900 works).

---

Live results

Notice the Statement is created TYPE_SCROLL_SENSITIVE. That is what makes the difference: the result set stays open and its rows reflect the live content, so re-reading it (beforeFirst() then next(), or absolute(...)) shows the current market rather than a one-time snapshot. A query is a standing view over the content, not a point-in-time fetch. Close the ResultSet (or the Connection) when you no longer need the data.

---

The SQL you can use

Elastic MDS accepts a subset of SQL-92 - a read-only query surface (SELECT and CREATE VIEW ... SELECT; there is no INSERT/UPDATE/DELETE of content). What is supported:

• Projections: columns, *, table.*, column aliases with AS, arithmetic (+ - * /), aggregates (SUM, AVG, COUNT, MIN, MAX), and library functions such as MFMath.AVG(BID, ASK) AS MID.
• WHERE: = <> < <= > >=, LIKE / NOT LIKE, IN / NOT IN, combined with AND / OR.
• Joins: a single equi-join between primary keys (a.key = b.key).
• GROUP BY, ORDER BY.
• CREATE VIEW ... SELECT (see below).

Some standard SQL is not supported, and a few clauses are accepted by the parser but do nothing - so avoid relying on them: HAVING, DISTINCT, BETWEEN, IS [NOT] NULL, CASE, CAST, subqueries, outer joins, joins of three or more tables, and UNION / INTERSECT / EXCEPT. The exact, exhaustive grammar will be covered in the SQL reference.

The row key is available as the column _KEY (often aliased, e.g. SELECT _KEY AS RIC, ...). Table names are schema-qualified (Quotes.RDF) - the same content model described in Dynamic Data Conventions.

---

Derived content with views

A SELECT can compute new columns - the MFMath.AVG(BID, ASK) AS MID above is a derived mid-price the source never sent. CREATE VIEW turns such a query into a named, reusable table that others can subscribe to or query:

CREATE VIEW Quotes.MyBook AS
  SELECT RIC, DSPLY_NAME, BID, ASK, MFMath.AVG(BID, ASK) AS MID
  FROM Quotes.RDF WHERE RIC IN ('SAB.L', 'BG.L', 'REX.L', 'WG.L')

The view is live like any other content: its rows update as the underlying market moves. Defining derived content once, centrally, is often better than every application computing it for itself.

---

Where to go next

• JMS Application Development - subscribe to the same content and stream it.
• Dynamic Data Conventions - the field and content model behind the tables you query.
• Architecture: Basics - the model behind the API.
• Glossary - definitions of the terms used here.