Quick Start: Java and TimescaleDB

Goal

This quick start guide is designed to get Java developers up and running with TimescaleDB as their database. In this tutorial, you’ll learn how to:

Pre-requisites

To complete this tutorial, you will need a cursory knowledge of the Structured Query Language (SQL). The tutorial will walk you through each SQL command, but it will be helpful if you’ve seen SQL before.

To start, install TimescaleDB. Once your installation is complete, you can proceed to ingesting or creating sample data and finishing the tutorial.

You will also need to install Java Development Kit (JDK) and PostgreSQL Java Database Connectivity (JDBC) Driver as well. All code is presented for Java 16 and above. If you are working with older JDK versions, use legacy coding techniques.

Connect Java to TimescaleDB

Step 1: Create a new Java application

For simplicity, we will use the application in a single file as an example. You can use any of your favorite build tools, including gradle and maven.

Create a separate directory and navigate to it. In it, create a text file with name and extension Main.java and the following content:

  1. package com.timescale.java;
  3. public class Main {
  5.     public static void main(String... args) {
  6.         System.out.println("Hello, World!");
  7.     }
  8. }

From the command line in the current directory, try running the application with this command:

  1. java Main.java

You should see the Hello, World! line output to your console. In case of an error, refer to the documentation and check if the JDK was installed correctly. You don’t have to create directory structure ./com/timescale/java similar to package path in source file. You should just create a single java file in empty folder and run java Main.java from it.

Step 2: Import Postgres JDBC driver

To work with the PostgreSQL, you need to import the appropriate JDBC Driver. If you are using a dependency manager, include PostgreSQL JDBC Driver as dependency. In this case, download jar artifact of JDBC Driver and place it next to the Main.java file.

Now you can import the JDBC Driver into the Java application and display a list of available drivers for the check:

  1. package com.timescale.java;
  3. import java.sql.DriverManager;
  5. public class Main {
  7.     public static void main(String... args) {
  8.         DriverManager.drivers().forEach(System.out::println);
  9.     }
  10. }

Use this command to run all the following examples:

  1. java -cp *.jar Main.java

You should end up with something like [[email protected]](https://docs.timescale.com/cdn-cgi/l/email-protection). This means that you are ready to connect to TimescaleDB from Java.

Step 3: Compose a database connection string

Locate your TimescaleDB credentials. You need these to compose a connection string for JDBC to use to connect to your TimescaleDB instance.

You’ll need these credentials:

  • host
  • port
  • database name
  • username
  • password

Next, compose your connection string variable using this format:

  1. var connUrl = "jdbc:postgresql://host:port/dbname?user=username&password=password";

Full documentation on the formation of the connection string can be found in the official documentation of the PostgreSQL JDBC Driver.

warning

The above method of composing a connection string is for test or development purposes only, for production purposes be sure to make sensitive details like your password, hostname, and port number environment variables.

Step 4: Connect to TimescaleDB instance using the PostgreSQL JDBC driver

Change the code to connect to the database server and verify that all settings are correct:

  1. package com.timescale.java;
  3. import java.sql.DriverManager;
  4. import java.sql.SQLException;
  6. public class Main {
  8.     public static void main(String... args) throws SQLException {
  9.         var connUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres";
  10.         var conn = DriverManager.getConnection(connUrl);
  11.         System.out.println(conn.getClientInfo());
  12.     }
  13. }

Run with the java -cp *.jar Main.java command and you should see this output: {ApplicationName=PostgreSQL JDBC Driver}.

Congratulations, you’ve successfully connected to TimescaleDB using Java.

Create a relational table

Step 1: Formulate your SQL statement

First, compose a string which contains the SQL state that you would use to create a relational table. In this example, we create a table called sensors, with columns id, type and location:

  1. CREATE TABLE sensors (
  2.     id SERIAL PRIMARY KEY,
  3.     type TEXT NOT NULL,
  4.     location TEXT NOT NULL
  5. );

Step 2: Execute the SQL statement and commit changes

Next, execute the CREATE TABLE query by creating a statement, executing the query from Step 1 and check that table was created with SELECT statement:

  1. package com.timescale.java;
  3. import java.sql.DriverManager;
  4. import java.sql.SQLException;
  6. public class Main {
  8.     public static void main(String... args) throws SQLException {
  9.         var connUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres";
  10.         var conn = DriverManager.getConnection(connUrl);
  12.         var createSensorTableQuery = """
  13.                 CREATE TABLE sensors (
  14.                     id SERIAL PRIMARY KEY,
  15.                     type TEXT NOT NULL,
  16.                     location TEXT NOT NULL
  17.                 )
  18.                 """;
  19.         try (var stmt = conn.createStatement()) {
  20.             stmt.execute(createSensorTableQuery);
  21.         }
  23.         var showAllTablesQuery = "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public'";
  24.         try (var stmt = conn.createStatement();
  25.              var rs = stmt.executeQuery(showAllTablesQuery)) {
  26.             System.out.println("Tables in the current database: ");
  27.             while (rs.next()) {
  28.                 System.out.println(rs.getString("tablename"));
  29.             }
  30.         }
  31.     }
  32. }

Congratulations, you’ve successfully created a relational table in TimescaleDB using Java.

Generate a hypertable

In TimescaleDB, the primary point of interaction with your data is a hypertable, the abstraction of a single continuous table across all space and time intervals, such that one can query it via standard SQL.

Virtually all user interactions with TimescaleDB are with hypertables. Creating tables and indexes, altering tables, inserting data, and selecting data, can (and should) all be executed on the hypertable.

A hypertable is defined by a standard schema with column names and types, with at least one column specifying a time value.

Step 1: Create sensors data table

First, we create CREATE TABLE SQL statement for our hypertable. Notice how the hypertable has the compulsory time column:

  1. CREATE TABLE sensor_data (
  2.     time TIMESTAMPTZ NOT NULL,
  3.     sensor_id INTEGER REFERENCES sensors (id),
  4.     value DOUBLE PRECISION
  5. );

Step 2: Create hypertable for sensors data

Next, you can formulate the SELECT statement to convert the table we created in Step 1 into a hypertable. Note that you must specify the table name to convert to a hypertable and its time column name as the two arguments, as mandated by the create_hypertable docs:

  1. SELECT create_hypertable('sensor_data', 'time');

Step 3: Execute previous steps from your Java code

Now you can bring it all together by executing the statement from step 1, then executing the statement from step 2 and committing your changes to the database:

  1. package com.timescale.java;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.SQLException;
  6. import java.util.List;
  8. public class Main {
  10.     public static void main(String... args) {
  11.         final var connUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres";
  12.         try (var conn = DriverManager.getConnection(connUrl)) {
  13.             createSchema(conn);
  14.             insertData(conn);
  15.         } catch (SQLException ex) {
  16.             System.err.println(ex.getMessage());
  17.         }
  18.     }
  20.     private static void createSchema(final Connection conn) throws SQLException {
  21.         try (var stmt = conn.createStatement()) {
  22.             stmt.execute("""
  23.                     CREATE TABLE sensors (
  24.                         id SERIAL PRIMARY KEY,
  25.                         type TEXT NOT NULL,
  26.                         location TEXT NOT NULL
  27.                     )
  28.                     """);
  29.         }
  31.         try (var stmt = conn.createStatement()) {
  32.             stmt.execute("""
  33.                     CREATE TABLE sensor_data (
  34.                         time TIMESTAMPTZ NOT NULL,
  35.                         sensor_id INTEGER REFERENCES sensors (id),
  36.                         value DOUBLE PRECISION
  37.                     )
  38.                     """);
  39.         }
  41.         try (var stmt = conn.createStatement()) {
  42.             stmt.execute("SELECT create_hypertable('sensor_data', 'time')");
  43.         }
  44.     }
  45. }

Congratulations, you’ve successfully created a hypertable in your TimescaleDB database using Java.

Insert a batch of rows into TimescaleDB

Here’s a typical pattern you could use to insert some data into a table. In the example below, insert the relational data from list of sensors, into the relational table named sensors.

First, open a connection to the database, then using prepared statements formulate the INSERT SQL statement and then execute that statement:

  1. final List<Sensor> sensors = List.of(
  2.         new Sensor("temperature", "bedroom"),
  3.         new Sensor("temperature", "living room"),
  4.         new Sensor("temperature", "outside"),
  5.         new Sensor("humidity", "kitchen"),
  6.         new Sensor("humidity", "outside"));
  7. for (final var sensor : sensors) {
  8.     try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
  9.         stmt.setString(1, sensor.type());
  10.         stmt.setString(2, sensor.location());
  11.         stmt.executeUpdate();
  12.     }
  13. }

You can insert a batch of rows into TimescaleDB in a couple of different ways. Let’s see what it looks like to insert a number of rows with batching mechanism. For simplicity’s sake, we’ll use PostgreSQL to generate some sample time-series data in order to insert into the sensor_data hypertable:

  1. final var sensorDataCount = 100;
  2. final var insertBatchSize = 10;
  3. try (var stmt = conn.prepareStatement("""
  4.         INSERT INTO sensor_data (time, sensor_id, value)
  5.         VALUES (
  6.             generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
  7.             floor(random() * 4 + 1)::INTEGER,
  8.             random()
  9.         )
  10.         """)) {
  11.     for (int i = 0; i < sensorDataCount; i++) {
  12.         stmt.addBatch();
  14.         if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
  15.             stmt.executeBatch();
  16.         }
  17.     }
  18. }

Below is a complete listing of the application, from creating tables to filling in the data:

  1. package com.timescale.java;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.SQLException;
  6. import java.util.List;
  8. public class Main {
  10.     public static void main(String... args) {
  11.         final var connUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres";
  12.         try (var conn = DriverManager.getConnection(connUrl)) {
  13.             createSchema(conn);
  14.             insertData(conn);
  15.         } catch (SQLException ex) {
  16.             System.err.println(ex.getMessage());
  17.         }
  18.     }
  20.     private static void createSchema(final Connection conn) throws SQLException {
  21.         try (var stmt = conn.createStatement()) {
  22.             stmt.execute("""
  23.                     CREATE TABLE sensors (
  24.                         id SERIAL PRIMARY KEY,
  25.                         type TEXT NOT NULL,
  26.                         location TEXT NOT NULL
  27.                     )
  28.                     """);
  29.         }
  31.         try (var stmt = conn.createStatement()) {
  32.             stmt.execute("""
  33.                     CREATE TABLE sensor_data (
  34.                         time TIMESTAMPTZ NOT NULL,
  35.                         sensor_id INTEGER REFERENCES sensors (id),
  36.                         value DOUBLE PRECISION
  37.                     )
  38.                     """);
  39.         }
  41.         try (var stmt = conn.createStatement()) {
  42.             stmt.execute("SELECT create_hypertable('sensor_data', 'time')");
  43.         }
  44.     }
  46.     private static void insertData(final Connection conn) throws SQLException {
  47.         final List<Sensor> sensors = List.of(
  48.                 new Sensor("temperature", "bedroom"),
  49.                 new Sensor("temperature", "living room"),
  50.                 new Sensor("temperature", "outside"),
  51.                 new Sensor("humidity", "kitchen"),
  52.                 new Sensor("humidity", "outside"));
  53.         for (final var sensor : sensors) {
  54.             try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
  55.                 stmt.setString(1, sensor.type());
  56.                 stmt.setString(2, sensor.location());
  57.                 stmt.executeUpdate();
  58.             }
  59.         }
  61.         final var sensorDataCount = 100;
  62.         final var insertBatchSize = 10;
  63.         try (var stmt = conn.prepareStatement("""
  64.                 INSERT INTO sensor_data (time, sensor_id, value)
  65.                 VALUES (
  66.                     generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
  67.                     floor(random() * 4 + 1)::INTEGER,
  68.                     random()
  69.                 )
  70.                 """)) {
  71.             for (int i = 0; i < sensorDataCount; i++) {
  72.                 stmt.addBatch();
  74.                 if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
  75.                     stmt.executeBatch();
  76.                 }
  77.             }
  78.         }
  79.     }
  81.     private record Sensor(String type, String location) {
  82.     }
  83. }

tip

If you are inserting data from a CSV file, we recommend the timescale-parallel-copy tool, which is a command line program for parallelizing PostgreSQL’s built-in COPY functionality for bulk inserting data into TimescaleDB.

Congratulations, you’ve successfully inserted data into TimescaleDB using Java.

Execute queries on TimescaleDB

Step 1: Define the SQL query

First, define the SQL query you’d like to run on the database. The example below contains a query which combines time-series and relational data. It returns the average values for every 15 minute interval for sensors with specific type and location.

  1. SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
  2. FROM sensor_data
  3. JOIN sensors ON sensors.id = sensor_data.sensor_id
  4. WHERE sensors.type = ? AND sensors.location = ?
  5. GROUP BY bucket
  6. ORDER BY bucket DESC;

Notice the use of placeholders for sensor type and location.

Step 2: Execute the query

Now you can execute the query with the prepared statement and read out the result set for all a-type sensors located on the floor:

  1. try (var stmt = conn.prepareStatement("""
  2.         SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
  3.         FROM sensor_data
  4.         JOIN sensors ON sensors.id = sensor_data.sensor_id
  5.         WHERE sensors.type = ? AND sensors.location = ?
  6.         GROUP BY bucket
  7.         ORDER BY bucket DESC
  8.         """)) {
  9.     stmt.setString(1, "temperature");
  10.     stmt.setString(2, "living room");
  12.     try (var rs = stmt.executeQuery()) {
  13.         while (rs.next()) {
  14.             System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2));
  15.         }
  16.     }
  17. }

After executing the statement, you should see something like this in the console:

  1. 2021-05-12 23:30:00.0: 0,508649
  2. 2021-05-12 23:15:00.0: 0,477852
  3. 2021-05-12 23:00:00.0: 0,462298
  4. 2021-05-12 22:45:00.0: 0,457006
  5. 2021-05-12 22:30:00.0: 0,568744
  6. ...

Complete Java snippet for working with TimescaleDB

  1. package com.timescale.java;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.SQLException;
  6. import java.util.List;
  8. public class Main {
  10.     public static void main(String... args) {
  11.         final var connUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres";
  12.         try (var conn = DriverManager.getConnection(connUrl)) {
  13.             createSchema(conn);
  14.             insertData(conn);
  15.             executeQueries(conn);
  16.         } catch (SQLException ex) {
  17.             System.err.println(ex.getMessage());
  18.         }
  19.     }
  21.     private static void createSchema(final Connection conn) throws SQLException {
  22.         try (var stmt = conn.createStatement()) {
  23.             stmt.execute("""
  24.                     CREATE TABLE sensors (
  25.                         id SERIAL PRIMARY KEY,
  26.                         type TEXT NOT NULL,
  27.                         location TEXT NOT NULL
  28.                     )
  29.                     """);
  30.         }
  32.         try (var stmt = conn.createStatement()) {
  33.             stmt.execute("""
  34.                     CREATE TABLE sensor_data (
  35.                         time TIMESTAMPTZ NOT NULL,
  36.                         sensor_id INTEGER REFERENCES sensors (id),
  37.                         value DOUBLE PRECISION
  38.                     )
  39.                     """);
  40.         }
  42.         try (var stmt = conn.createStatement()) {
  43.             stmt.execute("SELECT create_hypertable('sensor_data', 'time')");
  44.         }
  45.     }
  47.     private static void insertData(final Connection conn) throws SQLException {
  48.         final List<Sensor> sensors = List.of(
  49.                 new Sensor("temperature", "bedroom"),
  50.                 new Sensor("temperature", "living room"),
  51.                 new Sensor("temperature", "outside"),
  52.                 new Sensor("humidity", "kitchen"),
  53.                 new Sensor("humidity", "outside"));
  54.         for (final var sensor : sensors) {
  55.             try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
  56.                 stmt.setString(1, sensor.type());
  57.                 stmt.setString(2, sensor.location());
  58.                 stmt.executeUpdate();
  59.             }
  60.         }
  62.         final var sensorDataCount = 100;
  63.         final var insertBatchSize = 10;
  64.         try (var stmt = conn.prepareStatement("""
  65.                 INSERT INTO sensor_data (time, sensor_id, value)
  66.                 VALUES (
  67.                     generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
  68.                     floor(random() * 4 + 1)::INTEGER,
  69.                     random()
  70.                 )
  71.                 """)) {
  72.             for (int i = 0; i < sensorDataCount; i++) {
  73.                 stmt.addBatch();
  75.                 if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
  76.                     stmt.executeBatch();
  77.                 }
  78.             }
  79.         }
  80.     }
  82.     private static void executeQueries(final Connection conn) throws SQLException {
  83.         try (var stmt = conn.prepareStatement("""
  84.                 SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
  85.                 FROM sensor_data
  86.                 JOIN sensors ON sensors.id = sensor_data.sensor_id
  87.                 WHERE sensors.type = ? AND sensors.location = ?
  88.                 GROUP BY bucket
  89.                 ORDER BY bucket DESC
  90.                 """)) {
  91.             stmt.setString(1, "temperature");
  92.             stmt.setString(2, "living room");
  94.             try (var rs = stmt.executeQuery()) {
  95.                 while (rs.next()) {
  96.                     System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2));
  97.                 }
  98.             }
  99.         }
  100.     }
  102.     private record Sensor(String type, String location) {
  103.     }
  104. }

Congratulations 🎉, you’ve successfully executed a query on TimescaleDB using Java and PostgreSQL JDBC!

Next steps

Now that you’re able to connect, read, and write to a TimescaleDB instance from your Java application, be sure to check out these advanced tutorials: