- 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
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:
-
sslis set tofalseto 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.