Java Cheatsheet
Materialize is PostgreSQL-compatible, which means that Java applications can use any existing PostgreSQL client to interact with Materialize as if it were a PostgreSQL database. In this guide, we’ll use the PostgreSQL JDBC Driver to connect to Materialize and issue PostgreSQL commands.
Connect
You connect to Materialize the same way you connect to PostgreSQL with the JDBC driver.
Local Instance
You can connect to a local Materialize instance just as you would connect to a PostgreSQL instance:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class App {
private final String url = "jdbc:postgresql://localhost:6875/materialize";
private final String user = "materialize";
private final String password = "materialize";
/**
* Connect to Materialize
*
* @return a Connection object
*/
public Connection connect() {
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("ssl","false");
Connection conn = null;
try {
conn = DriverManager.getConnection(url, props);
System.out.println("Connected to Materialize successfully!");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}
public static void main(String[] args) {
App app = new App();
app.connect();
}
}
To establish the connection to Materialize, just as with PostgreSQL, you can call the getConnection() method on the DriverManager class.
Materialize Cloud Instance
Download your instance’s certificate files from the Materialize Cloud Connect dialog and specify the path to each file in the connection parameters. Replace MY_INSTANCE_ID in the connection string property with your Materialize Cloud instance ID.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class App {
private final String url = "jdbc:postgresql://MY_INSTANCE_ID:6875/materialize";
private final String user = "materialize";
private final String password = "materialize";
/**
* Connect to Materialize Cloud
*
* @return a Connection object
*/
public Connection connect() {
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("ssl", "true");
props.setProperty("sslmode", "require");
props.setProperty("sslcert", "materialize.crt");
props.setProperty("sslkey", "materialize.der.key");
props.setProperty("sslrootcert", "ca.crt");
Connection conn = null;
try {
conn = DriverManager.getConnection(url, props);
System.out.println("Connected to Materialize Cloud successfully!");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}
public static void main(String[] args) {
App app = new App();
app.connect();
}
}
Stream
To take full advantage of incrementally updated materialized views from a Java application, instead of querying Materialize for the state of a view at a point in time, use a TAIL statement to request a stream of updates as the view changes.
To read a stream of updates from an existing materialized view, open a long-lived transaction with BEGIN and use TAIL with FETCH to repeatedly fetch all changes to the view since the last query.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.sql.Statement;
public class App {
private final String url = "jdbc:postgresql://localhost:6875/materialize";
private final String user = "materialize";
private final String password = "materialize";
/**
* Connect to Materialize
*
* @return a Connection object
*/
public Connection connect() throws SQLException {
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("ssl","false");
return DriverManager.getConnection(url, props);
}
public void tail() {
try (Connection conn = connect()) {
Statement stmt = conn.createStatement();
stmt.execute("BEGIN");
stmt.execute("DECLARE c CURSOR FOR TAIL my_view");
while (true) {
ResultSet rs = stmt.executeQuery("FETCH ALL c");
if(rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3));
}
}
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
public static void main(String[] args) {
App app = new App();
app.tail();
}
}
The TAIL Output format of res.rows is an array of view update objects. When a row of a tailed view is updated, two objects will show up in the rows array:
...
1648567756801 1 value_3
1648567761801 1 value_4
1648567785802 -1 value_4
...
The first column is the timestamp of the update. The second column is the mz_diff value. A mz_diff value of -1 indicates Materialize is deleting one row with the included values. An update is just a deletion (mz_diff: '-1') and an insertion (mz_diff: '1') with the same mz_timestamp.
Query
Querying Materialize is identical to querying a traditional PostgreSQL database: Java executes the query, and Materialize returns the state of the view, source, or table at that point in time.
Because Materialize maintains materialized views in memory, response times are much faster than traditional database queries, and polling (repeatedly querying) a view doesn’t impact performance.
Query a view my_view with a select statement:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.sql.Statement;
public class App {
private final String url = "jdbc:postgresql://localhost:6875/materialize";
private final String user = "materialize";
private final String password = "materialize";
/**
* Connect to Materialize
*
* @return a Connection object
*/
public Connection connect() throws SQLException {
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("ssl","false");
return DriverManager.getConnection(url, props);
}
public void query() {
String SQL = "SELECT * FROM my_view";
try (Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL)) {
while (rs.next()) {
System.out.println(rs.getString("my_column"));
}
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
public static void main(String[] args) {
App app = new App();
app.query();
}
}
For more details, see the JDBC documentation.
Insert data into tables
Most data in Materialize will stream in via a SOURCE, but a TABLE in Materialize can be helpful for supplementary data. For example, use a table to join slower-moving reference or lookup data with a stream.
Basic Example: Insert a row of data into a table named countries in Materialize.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
public class App {
private final String url = "jdbc:postgresql://localhost:6875/materialize";
private final String user = "materialize";
private final String password = "materialize";
/**
* Connect to Materialize
*
* @return a Connection object
*/
public Connection connect() throws SQLException {
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("ssl","false");
return DriverManager.getConnection(url, props);
}
public void insert() {
try (Connection conn = connect()) {
String code = "GH";
String name = "Ghana";
PreparedStatement st = conn.prepareStatement("INSERT INTO countries(code, name) VALUES(?, ?)");
st.setString(1, code);
st.setString(2, name);
int rowsDeleted = st.executeUpdate();
System.out.println(rowsDeleted + " rows inserted.");
st.close();
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
public static void main(String[] args) {
App app = new App();
app.insert();
}
}
Manage sources, views, and indexes
Typically, you create sources, views, and indexes when deploying Materialize, although it is possible to use a Java app to execute common DDL statements.
Create a source from Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
public class App {
private final String url = "jdbc:postgresql://localhost:6875/materialize";
private final String user = "materialize";
private final String password = "materialize";
/**
* Connect to Materialize
*
* @return a Connection object
*/
public Connection connect() throws SQLException {
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("ssl","false");
return DriverManager.getConnection(url, props);
}
public void source() {
String SQL = "CREATE SOURCE market_orders_raw_2 FROM PUBNUB "
+ "SUBSCRIBE KEY 'sub-c-4377ab04-f100-11e3-bffd-02ee2ddab7fe' "
+ "CHANNEL 'pubnub-market-orders'";
try (Connection conn = connect()) {
Statement st = conn.createStatement();
st.execute(SQL);
System.out.println("Source created.");
st.close();
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
public static void main(String[] args) {
App app = new App();
app.source();
}
}
For more information, see CREATE SOURCE.
Create a view from Java
public void view() {
String SQL = "CREATE VIEW market_orders_2 AS "
+ "SELECT "
+ " val->>'symbol' AS symbol, "
+ " (val->'bid_price')::float AS bid_price "
+ "FROM (SELECT text::jsonb AS val FROM market_orders_raw_2)";
try (Connection conn = connect()) {
Statement st = conn.createStatement();
st.execute(SQL);
System.out.println("View created.");
st.close();
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
For more information, see CREATE VIEW.
Java ORMs
Materialize doesn’t currently support the full catalog of PostgreSQL system metadata API endpoints, including the system calls that object relational mapping systems (ORMs) like Hibernate use to introspect databases and do extra work behind the scenes. This means that some ORM system attempts to interact with Materialize will currently fail. Once full pg_catalog support is implemented, the features that depend on pg_catalog may work properly.