Java quick start

1. Create a directory containing a text file called Main.java, with this content:

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

   Copy

2. From the command line in the current directory, run the application:

   java Main.java

   Copy

   If the command is successful, Hello, World! line output is printed to your console.

3. Import the PostgreSQL JDBC driver. If you are using a dependency manager, include the PostgreSQL JDBC Driver as a dependency.

4. Download the JAR artifact of the JDBC Driver and save it with the Main.java file.

5. Import the JDBC Driver into the Java application and display a list of available drivers for the check:

   package com.timescale.java;
   
   import java.sql.DriverManager;
   
   public class Main {
   
       public static void main(String... args) {
           DriverManager.drivers().forEach(System.out::println);
       }
   }

   Copy

6. Run all the examples:

   java -cp *.jar Main.java

   Copy

If the command is successful, a string similar to org.postgresql.Driver@7f77e91b is printed to your console. This means that you are ready to connect to TimescaleDB from Java.

1. Locate your TimescaleDB credentials and use them to compose a connection string for JDBC.

   You'll need:

   • password
   • username
   • host URL
   • port
   • database name

2. Compose your connection string variable, using this format:

   var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";

   Copy

   For more information about creating connection strings, see the JDBC documentation.

   

   Warning

   This method of composing a connection string is for test or development purposes only. For production, use environment variables for sensitive details like your password, hostname, and port number.

   package com.timescale.java;
   
   import java.sql.DriverManager;
   import java.sql.SQLException;
   
   public class Main {
   
       public static void main(String... args) throws SQLException {
           var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
           var conn = DriverManager.getConnection(connUrl);
           System.out.println(conn.getClientInfo());
       }
   }

   Copy

3. Run the code:

   java -cp *.jar Main.java

   Copy

   If the command is successful, a string similar to {ApplicationName=PostgreSQL JDBC Driver} is printed to your console.

1. Compose a string which contains the SQL statement to create a relational table. This example creates a table called sensors, with columns id, type and location:

   CREATE TABLE sensors (
       id SERIAL PRIMARY KEY,
       type TEXT NOT NULL,
       location TEXT NOT NULL
   );

   Copy

2. Create a statement, execute the query you created in the previous step, and check that the table was created successfully:

   package com.timescale.java;
   
   import java.sql.DriverManager;
   import java.sql.SQLException;
   
   public class Main {
   
       public static void main(String... args) throws SQLException {
           var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
           var conn = DriverManager.getConnection(connUrl);
   
           var createSensorTableQuery = """
                   CREATE TABLE sensors (
                       id SERIAL PRIMARY KEY,
                       type TEXT NOT NULL,
                       location TEXT NOT NULL
                   )
                   """;
           try (var stmt = conn.createStatement()) {
               stmt.execute(createSensorTableQuery);
           }
   
           var showAllTablesQuery = "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public'";
           try (var stmt = conn.createStatement();
                var rs = stmt.executeQuery(showAllTablesQuery)) {
               System.out.println("Tables in the current database: ");
               while (rs.next()) {
                   System.out.println(rs.getString("tablename"));
               }
           }
       }
   }

   Copy

1. Create a CREATE TABLE SQL statement for your hypertable. Notice how the hypertable has the compulsory time column:

   CREATE TABLE sensor_data (
       time TIMESTAMPTZ NOT NULL,
       sensor_id INTEGER REFERENCES sensors (id),
       value DOUBLE PRECISION
   );

   Copy

2. Create a statement, execute the query you created in the previous step:

   SELECT create_hypertable('sensor_data', by_range('time'));

   Copy
   

   Note

   The by_range and by_hash dimension builder is an addition to TimescaleDB 2.13.

3. Execute the two statements you created, and commit your changes to the database:

   package com.timescale.java;
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   import java.util.List;
   
   public class Main {
   
       public static void main(String... args) {
           final var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
           try (var conn = DriverManager.getConnection(connUrl)) {
               createSchema(conn);
               insertData(conn);
           } catch (SQLException ex) {
               System.err.println(ex.getMessage());
           }
       }
   
       private static void createSchema(final Connection conn) throws SQLException {
           try (var stmt = conn.createStatement()) {
               stmt.execute("""
                       CREATE TABLE sensors (
                           id SERIAL PRIMARY KEY,
                           type TEXT NOT NULL,
                           location TEXT NOT NULL
                       )
                       """);
           }
   
           try (var stmt = conn.createStatement()) {
               stmt.execute("""
                       CREATE TABLE sensor_data (
                           time TIMESTAMPTZ NOT NULL,
                           sensor_id INTEGER REFERENCES sensors (id),
                           value DOUBLE PRECISION
                       )
                       """);
           }
   
           try (var stmt = conn.createStatement()) {
               stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))");
           }
       }
   }

   Copy

1. Open a connection to the database, use prepared statements to formulate the INSERT SQL statement, then execute the statement:

   final List<Sensor> sensors = List.of(
           new Sensor("temperature", "bedroom"),
           new Sensor("temperature", "living room"),
           new Sensor("temperature", "outside"),
           new Sensor("humidity", "kitchen"),
           new Sensor("humidity", "outside"));
   for (final var sensor : sensors) {
       try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
           stmt.setString(1, sensor.type());
           stmt.setString(2, sensor.location());
           stmt.executeUpdate();
       }
   }

   Copy

1. Define the SQL query you'd like to run on the database. This example combines time-series and relational data. It returns the average values for every 15 minute interval for sensors with specific type and location.

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

   Copy

2. Execute the query with the prepared statement and read out the result set for all a-type sensors located on the floor:

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

   Copy

   If the command is successful, you'll see output like this:

   2021-05-12 23:30:00.0: 0,508649
   2021-05-12 23:15:00.0: 0,477852
   2021-05-12 23:00:00.0: 0,462298
   2021-05-12 22:45:00.0: 0,457006
   2021-05-12 22:30:00.0: 0,568744
   ...

   Copy