Java Strategy for CrateDB

Integrations
1

Hi there,

I’m new to CrateDB (although not to distributed datastores and clusters in general) and wonder what the recommended practice is for Java development. I see that there used to be a Spring Data adapter, but that was discontinued in favor of a planned JPA integration. Has that ever come to fruition or is there another modern framework supported? Or is the only way to use CrateDB with the low level JDBC API?

2

I should add that I did try to use the Crate JDBC driver with Spring Data Jpa and setting the PostgreSQL Hibernate dialect. However, the implementation seems to be incomplete, because on saving a simple entity I got:

java.sql.SQLFeatureNotSupportedException: Connection: prepareStatement(String sql, int autoGeneratedKeys) not supported

The entity looks like this:

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;
3

Hi @ulim

For Spring the first choice would be Spring Data JDBC.

A lot has been changed in the last few years on our way to make CrateDB more compatible with ansi SQL and the PostgreSQL dialect / wire protocol. That also lead to the deprecation of some adapters.

Just to be sure, you are using the latest version of CrateDB?

4

I have been using io.crate:crate-jdbc:2.6.0, it’s the only release available in mvnrepository.com. And these are my Spring settings:

spring.datasource.url=jdbc:crate://localhost:5432/
spring.datasource.username=crate
spring.datasource.password=
spring.datasource.driver-class-name=io.crate.client.jdbc.CrateDriver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

5

Hi,
I notice you make reference to GenerationType.IDENTITY. CrateDB is a distributed system and it currently does not have a concept of sequences like identity columns, but there is a function called gen-random-text-uuid which is a suitable substitute in some cases.
Please also note that we also support using org.postgresql.Driver.

1 Like
6

So which driver is recommended?

I now changed my column to java.util.UUID and generated it myself, but then I got io.crate.shade.org.postgresql.util.PSQLException: ERROR: Can’t map PGType with oid=2950 to Crate type

So I changed the column type to String and got io.crate.shade.org.postgresql.util.PSQLException: ERROR: Relation ‘owner’ unknown

Owner is my Entity class with the fields as detailed above.

This does not look like any meaningful subset of JPA is supported. These are just the simplest operations thinkable - saving an entity class with two string fields.

7

Dear Ulrich,

thank you for writing in.

Could you also try to run your exercise using the vanilla PostgreSQL JDBC Driver and report back about the outcome?

With kind regards,
Andreas.

8

Sure, I just tried it, but the result was the same.
Kind regards,
Ulrich

9

Hi,
CrateDB does not currently have a dedicated UUID column type.
Please upvote Add a uuid type · Issue #11032 · crate/crate (github.com) if this is something you would find useful.
In the meanwhile, you can store UUIDs in TEXT columns, and as mentioned in that GitHub issue this doesn’t necessarily come with a performance hit.
I hope that helps. Thanks again for trying CrateDB and giving us your feedback.

10

I tried TEXT, but got the error message io.crate.shade.org.postgresql.util.PSQLException: ERROR: Relation ‘owner’ unknown

11

I’m sorry, I posted the wrong error message. This is the one that appears:
org.postgresql.util.PSQLException: ERROR: Relation ‘information_schema.sequences’ unknown

12

Hi again, apologies for the delay coming back to you.

So I changed the column type to String and got
io.crate.shade.org.postgresql.util.PSQLException: ERROR: Relation ‘owner’ unknown
Owner is my Entity class with the fields as detailed above.

I think at this point you were really close to have this working.
You probably implemented something similar to EclipseLink/Examples/JPA/CustomSequencing - Eclipsepedia
It is possible the table is in the doc schema but it is now being looked up in the public schema or a variation of this.
Try taking a look at http://localhost:4200/#!/tables to see what tables you have on each schema and then try setting the schema with spring.jpa.properties.hibernate.default_schema

org.postgresql.util.PSQLException: ERROR: Relation ‘information_schema.sequences’ unknown

In this other case it seems you are trying to use a sequence generator, the driver then translates this to the PostgreSQL implementation, but CrateDB does not have this functionality.

You may also want to consider a GenerationType.TABLE strategy.

13

Thank you very much for your comments. I did get rid of all sequences, so I have no idea why this error message appears.

Anyway, my main question actually was what the recommended strategy for Java development for CrateDB is and I thought it would not be Spring Data/Hibernate/JPA, but some other method or framework. But if I understood you correctly, then that is actually the way to go and I may have stumbled upon a bug. I suppose I would have to create a small sample application and report it as such.

Kind regards,
Ulrich

1 Like
14

In general we try to concentrate our efforts on improving compatibility with standard PostgreSQL rather than building custom CrateDB integrations with frameworks.

If you have a minimal example to reproduce the issue and can file it as an issue in GitHub that would be much appreciated, maybe just check the schema name thing first just in case it may be a configuration issue.

A colleague also pointed out that we have reference sample application using Spring Boot and Spring Data JDBC: crate-sample-apps/spring-jdbc at master · crate/crate-sample-apps (github.com)
Perhaps there are some snippets of code in there you may find useful.

15

Thanks for pointing it out, but that is Spring JDBC, not as in my case Spring Data JPA. These are two completely different frameworks and, in fact, JPA is a standard and Spring JDBC is not. Please note that Spring JDBC has not much to do with JDBC proper (which is, of course, also a standard), only that it builds another layer of abstraction on top of it.

1 Like
16

Dear Ulrich,

first of all, we would like to wish you a happy new year.

Talking about Java, both of those patches level up on the Java client layers beyond JDBC. One is about using CrateDB with Apache Flink by @matriv, the other one about Panache/Quarkus, probably with a bit of Hibernate inside by @mackerl.

Maybe one of them is already useful to you. We will let you know about any further news on this topic.

With kind regards,
Andreas.