How can I connect to CrateDB using JDBC?

Integrations 3rd Party Tools
1

1- how can I connect to crateDB using jdbcjar?
2- do we have a jdbc connection string?

2 
        Class.forName("io.crate.client.jdbc.CrateDriver");
        System.out.println("staring to connect...");
        Properties props = new Properties();
        props.put("user", "crate");
        Connection conn = DriverManager.getConnection("crate://127.0.0.1:4200/", props);

this will hangs on DriverManager.getConnection
crate versino is 4.1.5
crate-jdbc version is 2.6.0

3

Use the correct port for the postgresql protocol 5432 like documented here: https://crate.io/docs/jdbc/en/latest/connect.html.

4

Hi @hackerwin7,

I hope you are well.

Yes, the reference is correct and you will need our driver.

Latest release of our JDBC driver.

Just to make sure it is clear, I am going to walk you through the most generic postgres wire protocol centric way, and will I assume that you understand your way around a terminal command line and have a JDK installed and setup in your environment.

  1. Take the following snippet and save it to a file PostgresProtocolWireSolo.java:
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.util.Locale;
import java.util.Properties;

public class PostgresProtocolWireSolo {

    public static final int SO_RCVBUF = 1024 * 1024; // 1 MB
    public static final int MAX_BATCH_SIZE = 20000;
    public static final int QUERY_EXECUTION_TIMEOUT_SECS = 60;


    public static void main(String[] args) throws Exception {

        // In CrateDB:
        //
        // create table t (c0 int primary key, c1 text);
        // insert into t values(0, 'zero'), (1, 'one'), (2, 'two');

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

        Properties connectionProps = new Properties();
        // https://jdbc.postgresql.org/documentation/head/connect.html
        connectionProps.put("user", "crate");
        connectionProps.put("password", "");
        connectionProps.put("ssl", false);
        connectionProps.put("recvBufferSize", SO_RCVBUF);
        connectionProps.put("defaultRowFetchSize", MAX_BATCH_SIZE);
        connectionProps.put("loginTimeout", 5); // seconds, fail fast-ish
        connectionProps.put("socketTimeout", QUERY_EXECUTION_TIMEOUT_SECS);
        connectionProps.put("tcpKeepAlive", true);


        try (Connection sqlConnection = DriverManager.getConnection(connectionUrl, connectionProps)) {
            sqlConnection.setAutoCommit(true);
            if (sqlConnection.isClosed()) {
                System.out.println("Connection is not valid");
                return;
            }
            try(Statement stmt = sqlConnection.createStatement()) {
                boolean checkResults = stmt.execute("select * from t");
                if (checkResults) {
                    ResultSet rs = stmt.getResultSet();
                    while(rs.next()) {
                        ResultSetMetaData metaData = rs.getMetaData();
                        int columnCount = metaData.getColumnCount();
                        for (int i = 1; i <= columnCount; i++) {
                            System.out.printf(
                                    Locale.ENGLISH,
                                    ">> col %d: %s: %s\n",
                                    i,
                                    metaData.getColumnName(i),
                                    rs.getObject(i));
                        }
                    }
                }
            }
        }
    }
}
  1. From a terminal window, at the same path where the file is, compile it:
javac PostgresProtocolWireSolo.java

Notice that I like to be up to date with my java:

javac --version
javac 14
java --version
java 14 2020-03-17
Java(TM) SE Runtime Environment (build 14+36-1461)
Java HotSpot(TM) 64-Bit Server VM (build 14+36-1461, mixed mode, sharing)
  1. Now you need the appropriate JDBC driver, in jar format. You can copy the url (in the code snippet below) and paste it in a browser to get the file, or you can run the curl command, the important thing is that the jar file ends up right next to the PostgresProtocolWireSolo.class file generated in step 2:
curl --output pgql42_2_12.jar https://repo1.maven.org/maven2/org/postgresql/postgresql/42.2.12/postgresql-42.2.12.jar
  1. Make sure CrateDB is up and running. Notice that in the source code, it expects to find a socket listener at the other end, on port 5432. Sometimes I have postgres running in my host, and when I launch CrateDB, the default port 5432 will be already taken, thus CrateDB will bind to 5433 instead. This information will be available in CrateDB’s start logs, something like:
[2020-05-31T10:41:57,403][INFO ][psql                     ] [Zwölferkogel] publish_address {127.0.0.1:5433}, bound_addresses {[::1]:5432}, {127.0.0.1:5432}
  1. With CrateDB up and running, use your favourite client to create a table and insert some data:
create table t (c0 int primary key, c1 text);
insert into t values(0, 'zero'), (1, 'one'), (2, 'two');
  1. Run the program:
java -cp pgql42_2_12.jar:. PostgresProtocolWireSolo
>> col 1: c0: 1
>> col 2: c1: one
>> col 1: c0: 2
>> col 2: c1: two
>> col 1: c0: 0
>> col 2: c1: zero

Once the steps above are understood and you have the show running, you should fall back to our driver jar, as described in the reference.

Kind regards,

1 Like
5

Hi again,

I took the code example provided by Miguel as a blueprint and just added it to cratedb-examples/spikes/jdbc-basic at main · crate/cratedb-examples · GitHub, which now provides a full and concise example program plus some guidelines outlining how to connect to CrateDB using both the vanilla PgJDBC Driver and the CrateDB JDBC Driver.

With kind regards,
Andreas.

1 Like