Connecting to CrateDB with Java and JDBC

rafaelasantana · September 8, 2021, 11:33am

In this tutorial, we will

Install CrateDB
Install Maven and create a simple Maven project
Connect to CrateDB using the PostgreSQL-JDBC Driver
Test the CrateDB connection and make queries
Make an alternative connection to CrateDB using the CrateDB-JDBC Driver

Installing CrateDB

In this tutorial, we use the Ad-hoc method to install CrateDB, following this step by step tutorial. We download CrateDB (version 4.6.1) and unpack it. In the terminal, we navigate to the unpacked CrateDB root folder with the command

cd /crate-4.6.1

and run a single-node instance from CrateDB with

./bin/crate

You should now be able to access the CrateDB Admin UI from your browser, going to the local host at http://localhost:4200

Other installing options (e.g. Docker) are detailed in CrateDB Installation Tutorial.

Accessing CrateDB from the terminal using Crash

First, we will access CrateDB from the terminal using the crash CLI client to create a first table and fill it with some data. If you don’t have crash installed on your machine, follow the CrateDB: Crash CLI tutorial for a step-by-step guide. We will install crash using pip3 on macOS:

pip3 install crash

then run crash with the command

crash

We create the table testdrive with the CREATE TABLE command

cr> CREATE TABLE testdrive (id INT PRIMARY KEY, data TEXT);                      
CREATE OK, 1 row affected  (1.742 sec)

and add a few values to it with the INSERT command

cr> INSERT INTO testdrive VALUES (0, 'zero'), (1, 'one'), (2, 'two');            
INSERT OK, 3 rows affected  (0.099 sec)

To display our newly created table we can run a simple SELECT

cr> SELECT * FROM testdrive;                                                     
+----+------+
| id | data |
+----+------+
|  1 | one  |
|  2 | two  |
|  0 | zero |
+----+------+
SELECT 3 rows in set (0.001 sec)
cr>

What we have done so far

cr> CREATE TABLE testdrive (id INT PRIMARY KEY, data TEXT);                      
CREATE OK, 1 row affected  (1.742 sec)
cr> INSERT INTO testdrive VALUES (0, 'zero'), (1, 'one'), (2, 'two');            
INSERT OK, 3 rows affected  (0.099 sec)
cr> SELECT * FROM testdrive;                                                     
+----+------+
| id | data |
+----+------+
|  1 | one  |
|  2 | two  |
|  0 | zero |
+----+------+
SELECT 3 rows in set (0.001 sec)
cr>

Installing Maven and Java in VS Code

In this tutorial, we are using Visual Studio Code, but you can also use Maven using the terminal or a different IDE like Eclipse or IntelliJ. Maven is a build automation tool, usually used for Java projects. It offers a variety of archetypes (template projects). To make our lives easier we add the Maven Extension to VS Code. Go to the Extensions Marketplace on the left side of VS Code, search for Maven for Java and click on the Install button

If you haven’t used Java with VS Code before you might find this tutorial useful

Creating a new Maven project

Once Maven is integrated into VS Code, we are able to create a new Maven project. On the start page of VS Code, go to the Explorer section and click on Create Java Project

When asked about the project type, select Maven, and a list of Maven Archetypes will pop up.

For this tutorial, we chose the quickstart Archetype, however, the following steps should work for different archetypes as well.

The next steps are:

Choose a version: we suggest taking the newest version of the archetype
Input group ID of your project: for instance, crate.io
Input artifact ID of your project: for instance, cratedb-jdbc-tutorial

After setting these configurations for your project, select your preferred destination folder.

Maven will create the project and open the terminal with the following questions:

Press Enter to accept

Then, this window will pop up, saying the Maven project was successfully created. Click open to go to the Maven project

Setting Crate-JDBC as a dependency:

After opening our Maven project (in this tutorial, built with the quickstart archetype), we find a structure like this:

src: The src directory contains all of the source code for building the project.
target: The target directory is used to house all output of the build.
pom.xml: A Project Object Model or POM is the fundamental unit of work in Maven. It is an XML file that contains information about the project and configuration details used by Maven to build the project.

The first thing we do is add the PostgreSQL-JDBC driver to the POM file.

Navigate to the repositories section of your pom.xml to add the link to the Maven Central Repository, where the PostgreSQL-JDBC Driver is stored:

if there’s still no section in your pom.xml, simply add it anywhere inside the section.

...
<repositories>
...
 <repository>
      <id>maven-central</id>
      <name>Maven Central</name>
      <layout>default</layout>
      <url>https://repo1.maven.org/maven2<;/url>
      <snapshots>
          <enabled>false</enabled>
      </snapshots>
  </repository>
  ...
</repositories>

Then, still in the pom.xml file, head to the section to add the PostgreSQL-JDBC dependency:

...
<dependencies>
...   
   <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.2.23</version>
   </dependency>
</dependencies>

The complete pom.xml file should now look something like this:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>crate.io</groupId>
  <artifactId>cratedb-jdbc-tutorial</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>cratedb-jdbc-tutorial</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <repositories>
    <repository>
        <id>maven-central</id>
        <name>Maven Central</name>
        <layout>default</layout>
        <url>https://repo1.maven.org/maven2</url>
        <snapshots>
            <enabled>false</enabled>
        </snapshots>
    </repository>
  </repositories>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.2.23</version>
    </dependency>
    
  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

Connecting to CrateDB

In your Maven project, navigate to the class where you want the connection to CrateDB to be established. For instance, we will use the main class App.java

As described in the CrateDB Documentation, the connection to CrateDB is made through an URL, using some SQL and Util classes.

Firstly, import the following packages:

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

Connection URL

We need the correct URL for the connection, which we save in the variable connectionUrl

String connectionUrl = "jdbc:postgresql://localhost:5432/";

Here you can read more about the URL elements.

Properties

Then, we need a Properties object, in which we will save the CrateDB connection credentials

Properties connectionProps = new Properties();

Now we set the connection properties, such as user, password, etc. In this tutorial, we will log in with the basic crate user, which has no password. You can use the desired credentials to access your tables by changing these parameters:

ssl is set to false to disable the encryption, as we are using a local cluster.

connectionProps.put("user", "crate");
connectionProps.put("password", "");
connectionProps.put("ssl", false);

Establishing the connection

The connection to CrateDB is created with the help of the DriverManager class. We will use the connectionUrl and connectionProps objects by adding the following line to your main class:

Connection sqlConnection = 
        DriverManager.getConnection(connectionUrl, connectionProps));

So now our App class looks like this:

package crate.io;
import java.sql.*;
import java.util.*;

public class App {

    public static void main( String[] args ) {
   
        String connectionUrl = "jdbc:postgresql://localhost:5432/";
        Properties connectionProps = new Properties();
        connectionProps.put("user", "crate");
        connectionProps.put("password", "");
        connectionProps.put("ssl", false);
        
        Connection sqlConnection = DriverManager.getConnection(connectionUrl, connectionProps);
    }
}

Testing the connection

We have everything we need to establish a connection to CrateDB. In the following code snippet, we will create an easy test to check if the connection is opened. Our App class should now look like this:

public class App {

    public static void main( String[] args ) throws Exception {
    
        Properties connectionProps = new Properties();
        String connectionUrl = "jdbc:postgresql://localhost:5432/";
        connectionProps.put("user", "crate");
        connectionProps.put("password", "");
        connectionProps.put("ssl", false);
        Connection sqlConnection = DriverManager.getConnection(connectionUrl, connectionProps);
        
        // Running connection test
        System.out.println("Testing the connection to CrateDB:");
        try {
            if(!sqlConnection.isClosed()){
                System.out.println("Connection is open!");
            }
        } 
        catch (SQLException e) {
            System.out.println("Something went wrong: " + e.getMessage());
        }
    }
}

Running the App class will create the following output in the terminal, showing the connection was successful:

Testing the connection to CrateDB:
Connection is open!

Querying in CrateDB

Now we have successfully connected to CrateDB, it’s time we make some queries. Let’s take as an example the testdrive table we created previously:

// create a statement for our connection
Statement statement = sqlConnection.createStatement();

// save the query result in the resultSet object
ResultSet resultSet = statement.executeQuery("SELECT * FROM testdrive;");

// print rows from resultSet with "id" (column 1) and "data" (column 2)
while (resultSet.next()) {
            System.out.println("id: " + resultSet.getString(1) 
            + "   data: " + resultSet.getString(2));
}

The class now looks like this

public class App {

    public static void main( String[] args ) throws Exception {
    
        Properties connectionProps = new Properties();
        String connectionUrl = "jdbc:postgresql://localhost:5432/";
        connectionProps.put("user", "crate");
        connectionProps.put("password", "");
        connectionProps.put("ssl", false);
        Connection sqlConnection = DriverManager.getConnection(connectionUrl, connectionProps);
        
        // Running connection test
        System.out.println("Testing the connection to CrateDB:");
        try {
            if(!sqlConnection.isClosed()){
                System.out.println("Connection is open!");
            }
            // create a statement for our connection
            Statement statement = sqlConnection.createStatement();

            // save the query result in the resultSet object
            ResultSet resultSet = statement.executeQuery("SELECT * FROM testdrive;");

            // print rows from resultSet with "id" (column 1) and "data" (column 2)
            while (resultSet.next()) {
                System.out.println("id: " + resultSet.getString(1) 
                + "   data: " + resultSet.getString(2));
            }
        } 
        catch (SQLException e) {
            System.out.println("Something went wrong: " + e.getMessage());
        }
    }
}

Running this class will output the values we added to the testdrive table:

Testing the connection to CrateDB:
Connection is open!
id: 1   data: one
id: 2   data: two
id: 0   data: zero

And now we can create different query statements, for example:

// creating a new statement
Statement newStatement = sqlConnection.createStatement();

// creating new table
newStatement.executeUpdate("CREATE TABLE newtable (name TEXT, age INT)");

// inserting values into the newtable
newStatement.executeUpdate("INSERT INTO newtable VALUES 
                            ('anna', 20), ('barbara', 30), ('carlos', 40)");

// selecting all rows from the table and saving the result in resultSet
resultSet = statement.executeQuery("SELECT * FROM newtable");

// printing rows 
while (resultSet.next()) {
        System.out.println("name: " + resultSet.getString(1) 
        + "   age: " + resultSet.getString(2));
}

which will print the following values:

name: anna   age: 20
name: carlos   age: 40
name: barbara   age: 30

Building the Maven project

To build the Maven project, go to the Maven folder in your IDE, right-click on the Maven project, and select install.

To clean the build artifacts, select clean.

Alternative connection using the Crate-JDBC Driver

In addition to the PostgreSQL-JDBC Driver, we can also use the CrateDB-JDBC Driver to connect to CrateDB.

The steps are mostly the same as described for the PostgreSQL-JDBC Driver, with a few replacements:

in the POM file: add the CrateDB dependency like the following (we are using version 2.6.0):

...
<dependencies>
  ...
  <dependency>
      <groupId>io.crate</groupId>
      <artifactId>crate-jdbc</artifactId>
      <version>2.6.0</version>
  </dependency>
</depedencies>
...

In the Connection URL: assign the connectionUrl variable with the following Crate-JDBC URL

String connectionUrl = "jdbc:crate://localhost:5432/";

And then follow the previous steps to run the program and build the Maven project.

Topic		Replies	Views
Connecting to CrateDB with Excel ODBC Driver on MacOS Tutorials integration , getting-started	0	656	February 13, 2023
How can I connect to CrateDB using JDBC? 3rd Party Tools	4	2350	August 20, 2021
Connecting to CrateDB from Go Tutorials integration	0	1500	April 19, 2021
Connecting to CrateDB with Node.js Tutorials integration	0	1454	July 25, 2021
Using CrateDB with Tableau Tutorials integration , data-visualization	0	1931	August 19, 2022