This tutorial shows you how build a simple Java application with CockroachDB using a PostgreSQL-compatible driver or ORM.
We have tested the Java JDBC driver and the Hibernate ORM enough to claim beta-level support, so those are featured here. If you encounter problems, please open an issue with details to help us make progress toward full support.
For a more realistic use of Hibernate with CockroachDB, see our examples-orms
repository.
Before you begin
- Install CockroachDB.
- Start up a secure or insecure local cluster.
- Choose the instructions that correspond to whether your cluster is secure or insecure:
The examples on this page assume you are using a Java version <= 9. They do not work with Java 10.
Step 1. Install the Gradle build tool
This tutorial uses the Gradle build tool to get all dependencies for your application, including Hibernate.
To install Gradle on Mac, run the following command:
$ brew install gradle
To install Gradle on a Debian-based Linux distribution like Ubuntu:
$ apt-get install gradle
To install Gradle on a Red Hat-based Linux distribution like Fedora:
$ dnf install gradle
For other ways to install Gradle, see its official documentation.
Step 2. Create the maxroach
user and bank
database
Start the built-in SQL client:
$ cockroach sql --certs-dir=certs
In the SQL shell, issue the following statements to create the maxroach
user and bank
database:
> CREATE USER IF NOT EXISTS maxroach;
> CREATE DATABASE bank;
Give the maxroach
user the necessary permissions:
> GRANT ALL ON DATABASE bank TO maxroach;
Exit the SQL shell:
> \q
Step 3. Generate a certificate for the maxroach
user
Create a certificate and key for the maxroach
user by running the following command. The code samples will run as this user.
$ cockroach cert create-client maxroach --certs-dir=certs --ca-key=my-safe-directory/ca.key
Step 4. Convert the key file for use with Java
The private key generated for user maxroach
by CockroachDB is PEM encoded. To read the key in a Java application, you will need to convert it into PKCS#8 format, which is the standard key encoding format in Java.
To convert the key to PKCS#8 format, run the following OpenSSL command on the maxroach
user's key file in the directory where you stored your certificates (/tmp/certs
in this example):
$ openssl pkcs8 -topk8 -inform PEM -outform DER -in client.maxroach.key -out client.maxroach.pk8 -nocrypt
Step 5. Run the Java code
Download and extract hibernate-basic-sample.tgz, which contains a Java project that includes the following files:
File | Description |
---|---|
Sample.java |
Uses Hibernate to map Java object state to SQL operations. For more information, see Sample.java. |
hibernate.cfg.xml |
Specifies how to connect to the database and that the database schema will be deleted and recreated each time the app is run. For more information, see hibernate.cfg.xml. |
build.gradle |
Used to build and run your app. For more information, see build.gradle. |
In the hibernate-basic-sample
directory, build and run the application:
$ gradle run
Toward the end of the output, you should see:
1 1000
2 250
To verify that the table and rows were created successfully, start the built-in SQL client:
$ cockroach sql --certs-dir=certs --database=bank
To check the account balances, issue the following statement:
> SELECT id, balance FROM accounts;
+----+---------+
| id | balance |
+----+---------+
| 1 | 1000 |
| 2 | 250 |
+----+---------+
(2 rows)
Sample.java
The Java code shown below uses the Hibernate ORM to map Java object state to SQL operations. Specifically, this code:
Creates an
accounts
table in the database based on theAccount
class.Inserts rows into the table using
session.save(new Account())
.Defines the SQL query for selecting from the table so that balances can be printed using the
CriteriaQuery<Account> query
object.
package com.cockroachlabs;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.criteria.CriteriaQuery;
public class Sample {
// Create a SessionFactory based on our hibernate.cfg.xml configuration
// file, which defines how to connect to the database.
private static final SessionFactory sessionFactory =
new Configuration()
.configure("hibernate.cfg.xml")
.addAnnotatedClass(Account.class)
.buildSessionFactory();
// Account is our model, which corresponds to the "accounts" database table.
@Entity
@Table(name="accounts")
public static class Account {
@Id
@Column(name="id")
public long id;
@Column(name="balance")
public long balance;
// Convenience constructor.
public Account(int id, int balance) {
this.id = id;
this.balance = balance;
}
// Hibernate needs a default (no-arg) constructor to create model objects.
public Account() {}
}
public static void main(String[] args) throws Exception {
Session session = sessionFactory.openSession();
try {
// Insert two rows into the "accounts" table.
session.beginTransaction();
session.save(new Account(1, 1000));
session.save(new Account(2, 250));
session.getTransaction().commit();
// Print out the balances.
CriteriaQuery<Account> query = session.getCriteriaBuilder().createQuery(Account.class);
query.select(query.from(Account.class));
for (Account account : session.createQuery(query).getResultList()) {
System.out.printf("%d %d\n", account.id, account.balance);
}
} finally {
session.close();
sessionFactory.close();
}
}
}
hibernate.cfg.xml
The Hibernate config (in hibernate.cfg.xml
, shown below) specifies how to connect to the database. Note the connection URL that turns on SSL and specifies the location of the security certificates.
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- Database connection settings -->
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQL95Dialect</property>
<property name="hibernate.connection.url"><![CDATA[jdbc:postgresql://localhost:26257/bank?ssl=true&sslmode=require&sslrootcert=certs/ca.crt&sslkey=certs/client.maxroach.pk8&sslcert=certs/client.maxroach.crt]]></property>
<property name="hibernate.connection.username">maxroach</property>
<!-- Required so a table can be created from the 'Account' class in Sample.java -->
<property name="hibernate.hbm2ddl.auto">create</property>
<!-- Optional: Show SQL output for debugging -->
<property name="hibernate.show_sql">true</property>
<property name="hibernate.format_sql">true</property>
</session-factory>
</hibernate-configuration>
build.gradle
The Gradle build file specifies the dependencies (in this case the Postgres JDBC driver and Hibernate):
group 'com.cockroachlabs'
version '1.0'
apply plugin: 'java'
apply plugin: 'application'
mainClassName = 'com.cockroachlabs.Sample'
repositories {
mavenCentral()
}
dependencies {
compile 'org.hibernate:hibernate-core:5.2.4.Final'
compile 'org.postgresql:postgresql:42.2.2.jre7'
}
Step 2. Create the maxroach
user and bank
database
Start the built-in SQL client:
$ cockroach sql --insecure
In the SQL shell, issue the following statements to create the maxroach
user and bank
database:
> CREATE USER IF NOT EXISTS maxroach;
> CREATE DATABASE bank;
Give the maxroach
user the necessary permissions:
> GRANT ALL ON DATABASE bank TO maxroach;
Exit the SQL shell:
> \q
Step 3. Run the Java code
Download and extract hibernate-basic-sample.tgz, which contains a Java project that includes the following files:
File | Description |
---|---|
Sample.java |
Uses Hibernate to map Java object state to SQL operations. For more information, see Sample.java. |
hibernate.cfg.xml |
Specifies how to connect to the database and that the database schema will be deleted and recreated each time the app is run. For more information, see hibernate.cfg.xml. |
build.gradle |
Used to build and run your app. For more information, see build.gradle. |
In the hibernate-basic-sample
directory, build and run the application:
$ gradle run
Toward the end of the output, you should see:
1 1000
2 250
To verify that the table and rows were created successfully, start the built-in SQL client:
$ cockroach sql --insecure --database=bank
To check the account balances, issue the following statement:
> SELECT id, balance FROM accounts;
+----+---------+
| id | balance |
+----+---------+
| 1 | 1000 |
| 2 | 250 |
+----+---------+
(2 rows)
Sample.java
The Java code shown below uses the Hibernate ORM to map Java object state to SQL operations. Specifically, this code:
Creates an
accounts
table in the database based on theAccount
class.Inserts rows into the table using
session.save(new Account())
.Defines the SQL query for selecting from the table so that balances can be printed using the
CriteriaQuery<Account> query
object.
package com.cockroachlabs;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.criteria.CriteriaQuery;
public class Sample {
// Create a SessionFactory based on our hibernate.cfg.xml configuration
// file, which defines how to connect to the database.
private static final SessionFactory sessionFactory =
new Configuration()
.configure("hibernate.cfg.xml")
.addAnnotatedClass(Account.class)
.buildSessionFactory();
// Account is our model, which corresponds to the "accounts" database table.
@Entity
@Table(name="accounts")
public static class Account {
@Id
@Column(name="id")
public long id;
@Column(name="balance")
public long balance;
// Convenience constructor.
public Account(int id, int balance) {
this.id = id;
this.balance = balance;
}
// Hibernate needs a default (no-arg) constructor to create model objects.
public Account() {}
}
public static void main(String[] args) throws Exception {
Session session = sessionFactory.openSession();
try {
// Insert two rows into the "accounts" table.
session.beginTransaction();
session.save(new Account(1, 1000));
session.save(new Account(2, 250));
session.getTransaction().commit();
// Print out the balances.
CriteriaQuery<Account> query = session.getCriteriaBuilder().createQuery(Account.class);
query.select(query.from(Account.class));
for (Account account : session.createQuery(query).getResultList()) {
System.out.printf("%d %d\n", account.id, account.balance);
}
} finally {
session.close();
sessionFactory.close();
}
}
}
hibernate.cfg.xml
The Hibernate config (in hibernate.cfg.xml
, shown below) specifies how to connect to the database. Note the connection URL that turns on SSL and specifies the location of the security certificates.
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- Database connection settings -->
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="dialect">org.hibernate.dialect.PostgreSQL95Dialect</property>
<property name="connection.url">jdbc:postgresql://127.0.0.1:26257/bank?sslmode=disable</property>
<property name="connection.username">maxroach</property>
<!-- Required so a table can be created from the 'Account' class in Sample.java -->
<property name="hibernate.hbm2ddl.auto">create</property>
<!-- Optional: Show SQL output for debugging -->
<property name="hibernate.show_sql">true</property>
<property name="hibernate.format_sql">true</property>
</session-factory>
</hibernate-configuration>
build.gradle
The Gradle build file specifies the dependencies (in this case the Postgres JDBC driver and Hibernate):
group 'com.cockroachlabs'
version '1.0'
apply plugin: 'java'
apply plugin: 'application'
mainClassName = 'com.cockroachlabs.Sample'
repositories {
mavenCentral()
}
dependencies {
compile 'org.hibernate:hibernate-core:5.2.4.Final'
compile 'org.postgresql:postgresql:42.2.2.jre7'
}
What's next?
Read more about using the Hibernate ORM, or check out a more realistic implementation of Hibernate with CockroachDB in our examples-orms
repository.
You might also be interested in using a local cluster to explore the following CockroachDB benefits: