TIPS: 如遇兼容性问题,可提交github issue以获取帮助:https://github.com/cockroachdb/cockroach/issues/new

NOTE: Java版本不高于9,CockroachDB目前不能在Java 10上运行。

本小节将展示如何使用JDBC驱动或Hibernate框架进行编程交互。用户需要事先安装、配置CockroachDB。

使用JDBC驱动

Step 1: 安装JDBC驱动

用户可以参考官方文档进行安装。

Step 2: 启动节点

以非安全模式启动节点:

cockroach start --insecure --store=hello-1 --host=localhost

Step 3: 创建用户

执行cockroach user命令创建maxroach用户:

cockroach user set maxroach --insecure

Step 4: 创建数据库并授权

root用户启动内置的SQL客户端,创建bank数据库并授权maxroach用户。

cockroach sql --insecure -e 'CREATE DATABASE bank'
cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 5: 编写执行JAVA代码

NOTE: 若访问安全模式的集群,需要在代码中指定相应的安全证书。值得注意的是,CockroachDBcockroach cert命令生成的证书为PRM编码,需要转换为PKCS#8格式,用户可以使用OpenSSL的命令进行转换,例如: bash openssl pkcs8 -topk8 -inform PEM -outform DER -in client.maxroach.key -out client.maxroach.pk8 -nocrypt

基本操作

JAVA代码:

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

/*
  Download the Postgres JDBC driver jar from https://jdbc.postgresql.org.

  Then, compile and run this example like so:

  $ export CLASSPATH=.:/path/to/postgresql.jar
  $ javac BasicSample.java && java BasicSample
*/

public class BasicSample {
    public static void main(String[] args)
        throws ClassNotFoundException, SQLException {

        // Load the Postgres JDBC driver.
        Class.forName("org.postgresql.Driver");

        // Connect to the "bank" database.
        Properties props = new Properties();
        props.setProperty("user", "maxroach");
        //非安全模式
        props.setProperty("sslmode", "disable");
        //安全模式
        //props.setProperty("sslmode", "require");
        //props.setProperty("sslrootcert", "certs/ca.crt");
        //props.setProperty("sslkey", "certs/client.maxroach.pk8");
        //props.setProperty("sslcert", "certs/client.maxroach.crt");

        Connection db = DriverManager
            .getConnection("jdbc:postgresql://127.0.0.1:26257/bank", props);

        try {
            // Create the "accounts" table.
            db.createStatement()
                .execute("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)");

            // Insert two rows into the "accounts" table.
            db.createStatement()
                .execute("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)");

            // Print out the balances.
            System.out.println("Initial balances:");
            ResultSet res = db.createStatement()
                .executeQuery("SELECT id, balance FROM accounts");
            while (res.next()) {
                System.out.printf("\taccount %s: %s\n",
                                  res.getInt("id"),
                                  res.getInt("balance"));
            }
        } finally {
            // Close the database connection.
            db.close();
        }
    }
}

创建事务(带重试逻辑)

JAVA代码:

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

/*
  Download the Postgres JDBC driver jar from https://jdbc.postgresql.org.

  Then, compile and run this example like so:

  $ export CLASSPATH=.:/path/to/postgresql.jar
  $ javac TxnSample.java && java TxnSample
*/

// Ambiguous whether the transaction committed or not.
class AmbiguousCommitException extends SQLException{
    public AmbiguousCommitException(Throwable cause) {
        super(cause);
    }
}

class InsufficientBalanceException extends Exception {}

class AccountNotFoundException extends Exception {
    public int account;
    public AccountNotFoundException(int account) {
        this.account = account;
    }
}

// A simple interface that provides a retryable lambda expression.
interface RetryableTransaction {
    public void run(Connection conn)
        throws SQLException, InsufficientBalanceException,
               AccountNotFoundException, AmbiguousCommitException;
}

public class TxnSample {
    public static RetryableTransaction transferFunds(int from, int to, int amount) {
        return new RetryableTransaction() {
            public void run(Connection conn)
                throws SQLException, InsufficientBalanceException,
                       AccountNotFoundException, AmbiguousCommitException {

                // Check the current balance.
                ResultSet res = conn.createStatement()
                    .executeQuery("SELECT balance FROM accounts WHERE id = "
                                  + from);
                if(!res.next()) {
                    throw new AccountNotFoundException(from);
                }

                int balance = res.getInt("balance");
                if(balance < from) {
                    throw new InsufficientBalanceException();
                }

                // Perform the transfer.
                conn.createStatement()
                    .executeUpdate("UPDATE accounts SET balance = balance - "
                                   + amount + " where id = " + from);
                conn.createStatement()
                    .executeUpdate("UPDATE accounts SET balance = balance + "
                                   + amount + " where id = " + to);
            }
        };
    }

    public static void retryTransaction(Connection conn, RetryableTransaction tx)
        throws SQLException, InsufficientBalanceException,
               AccountNotFoundException, AmbiguousCommitException {

        Savepoint sp = conn.setSavepoint("cockroach_restart");
        while(true) {
            boolean releaseAttempted = false;
            try {
                tx.run(conn);
                releaseAttempted = true;
                conn.releaseSavepoint(sp);
            }
            catch(SQLException e) {
                String sqlState = e.getSQLState();

                // Check if the error code indicates a SERIALIZATION_FAILURE.
                if(sqlState.equals("40001")) {
                    // Signal the database that we will attempt a retry.
                    conn.rollback(sp);
                    continue;
                } else if(releaseAttempted) {
                    throw new AmbiguousCommitException(e);
                } else {
                    throw e;
                }
            }
            break;
        }
        conn.commit();
    }

    public static void main(String[] args)
        throws ClassNotFoundException, SQLException {

        // Load the Postgres JDBC driver.
        Class.forName("org.postgresql.Driver");

        // Connect to the 'bank' database.
        Properties props = new Properties();
        props.setProperty("user", "maxroach");
        //非安全模式
        props.setProperty("sslmode", "disable");
        //安全模式
        //props.setProperty("sslmode", "require");
        //props.setProperty("sslrootcert", "certs/ca.crt");
        //props.setProperty("sslkey", "certs/client.maxroach.pk8");
        //props.setProperty("sslcert", "certs/client.maxroach.crt");

        Connection db = DriverManager
            .getConnection("jdbc:postgresql://127.0.0.1:26257/bank", props);


        try {
                // We need to turn off autocommit mode to allow for
                // multi-statement transactions.
                db.setAutoCommit(false);

                // Perform the transfer. This assumes the 'accounts'
                // table has already been created in the database.
                RetryableTransaction transfer = transferFunds(1, 2, 100);
                retryTransaction(db, transfer);

                // Check balances after transfer.
                db.setAutoCommit(true);
                ResultSet res = db.createStatement()
                    .executeQuery("SELECT id, balance FROM accounts");
                while (res.next()) {
                    System.out.printf("\taccount %s: %s\n", res.getInt("id"),
                                      res.getInt("balance"));
                }

            } catch(InsufficientBalanceException e) {
                System.out.println("Insufficient balance");
            } catch(AccountNotFoundException e) {
                System.out.println("No users in the table with id " + e.account);
            } catch(AmbiguousCommitException e) {
                System.out.println("Ambiguous result encountered: " + e);
            } catch(SQLException e) {
                System.out.println("SQLException encountered:" + e);
            } finally {
                // Close the database connection.
                db.close();
            }
    }
}

编译执行:

javac -classpath .:/path/to/postgresql.jar TxnSample.java
java -classpath .:/path/to/postgresql.jar TxnSample

执行查询:

cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
|  1 |     900 |
|  2 |     350 |
+----+---------+
(2 rows)

NOTE: 在默认的隔离级别SERIALIZABLE下,因读写冲突导致事务执行失败,需要用户主动地重新提交事务。用户也实现通用的retry函数在事务内部重新执行事务。

使用Hibernate框架

example示例:https://github.com/cockroachdb/examples-orms

Step 1: 安装Gradle构造工具

Mac

brew install gradle

Ubuntu

apt-get install gradle

Red Hat

dnf install gradle

其他系统

参考官方文档

Step 2: 启动节点

以非安全模式启动:

cockroach start --insecure --store=hello-1 --host=localhost

Step 3: 创建用户

执行cockroach user命令创建maxroach用户:

cockroach user set maxroach --insecure

Step 4: 创建数据库并授权

root用户启动内置的SQL客户端,创建bank数据库并授权maxroach用户。

cockroach sql --insecure -e 'CREATE DATABASE bank'
cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 5: 编写执行JAVA代码

集成配置文件:hibernate-basic-sample.tgz

文件名 简介
hibernate.cfg.xml 连接数据库的配置
Sample.java 映射文件,映射数据库里表格结构
build.gradle 自动构造文件

hibernate-basic-sample目录下,执行:

gradle run

输出:

1 1000
2 250

执行查询:

cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     250 |
+----+---------+
(2 rows)

以下为压缩包内文件内容:

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();
        }
    }
}
<?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.PostgreSQL94Dialect</property>
        <!--安全模式-->
        <property name="connection.url">jdbc:postgresql://127.0.0.1:26257/bank?sslmode=disable</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="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>
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'
}