1. Introduction
1.介绍
This tutorial is an introductory guide to using CockroachDB with Java.
本教程是使用CockroachDB与Java的介绍性指南。
We’ll explain the key features, how to configure a local cluster and how to monitor it, along with a practical guide on how we can use Java to connect and interact with the server.
我们将解释关键功能、如何配置本地集群以及如何监控它，同时提供实用指南，说明我们如何使用Java来连接并与服务器互动。
Let’s start by first defining what it is.
让我们首先定义它是什么。
2. CockroachDB
2.CockroachDB[/strong
CockroachDB is a distributed SQL database built on top of a transactional and consistent key-value store.
CockroachDB是一个分布式的SQL数据库，建立在一个事务性的、一致的键值存储之上。
Written in Go and completely open source, its primary design goals are the support for ACID transactions, horizontal scalability, and survivability. With these design goals, it aims to tolerate everything from a single disk failure to an entire datacenter crash with minimal latency disruption and without manual intervention.
它的主要设计目标是支持ACID事务、水平可扩展性和生存能力。通过这些设计目标，它旨在以最小的延迟干扰和无需人工干预的方式，容忍从单个磁盘故障到整个数据中心崩溃的一切。
As result, CockroachDB can be considered a well-suited solution for applications that require reliable, available, and correct data regardless of scale. However, it’s not the first choice when very low latency reads and writes are critical.
因此，CockroachDB可以被认为是一个非常适合需要可靠、可用和正确数据的应用程序的解决方案，而不管其规模如何。然而，当极低延迟的读取和写入至关重要时，它不是首选。
2.1. Key Features
2.1.关键特征
Let’s continue exploring some of the key aspects of CockroachDB:
让我们继续探索CockroachDB的一些关键方面。
- SQL API and PostgreSQL compatibility – for structuring, manipulating and querying data
- ACID transactions – supporting distributed transactions and provides strong consistency
- Cloud-ready – designed to run in the cloud or on an on-premises solution providing easy migration between different cloud providers without any service interruption
- Scales horizontally – adding capacity is as easy as pointing a new node at the running cluster with minimal operator overhead
- Replication – replicates data for availability and guarantees consistency between replicas
- Automated repair – continue seamlessly as long as a majority of replicas remain available for short-term failures while, for longer-term failures, automatically rebalances replicas from the missing nodes, using the unaffected replicas as sources
3. Configuring CockroachDB
3.配置CockroachDB
After we’ve installed CockroachDB, we can start the first node of our local cluster:
在我们安装了CockroachDB之后，我们可以启动我们本地集群的第一个节点。
cockroach start --insecure --host=localhost;For demo purposes, we’re using the insecure attribute, making the communication unencrypted, without the need to specify the certificates location.
为了演示的目的，我们使用insecure属性，使通信不加密，不需要指定证书位置。
At this point, our local cluster is up and running. With only one single node, we can already connect to it and operate but to better take advantages of CockroachDB’s automatic replication, rebalancing, and fault tolerance, we’ll add two more nodes:
在这一点上，我们的本地集群已经启动并运行。只有一个单一的节点，我们已经可以连接到它并进行操作，但是为了更好地利用CockroachDB的自动复制、重新平衡和容错的优势，我们将再增加两个节点。
cockroach start --insecure --store=node2 \
--host=localhost --port=26258 --http-port=8081 \
--join=localhost:26257;
cockroach start --insecure --store=node3 \
--host=localhost --port=26259 --http-port=8082 \
--join=localhost:26257;For the two additional nodes, we used the join flag to connect the new nodes to the cluster, specifying the address and port of the first node, in our case localhost:26257. Each node on the local cluster requires unique store, port, and http-port values.
对于另外两个节点，我们使用join标志将新的节点连接到集群，指定第一个节点的地址和端口，在我们的例子中是localhost:26257。本地集群的每个节点都需要唯一的store、port和http-port值。
When configuring a distributed cluster of CockroachDB, each node will be on a different machine, and so specifying the port, store and the http-port can be avoided since the defaults values suffice. In addition, the actual IP of the first node should be used when joining the additional nodes to the cluster.
当配置CockroachDB的分布式集群时，每个节点都在不同的机器上，因此可以避免指定port、store和http-port，因为默认值已经足够了。此外，在加入其他节点到集群时，应该使用第一个节点的实际IP。
3.1. Configuring Database and User
3.1.配置数据库和用户
Once we have our cluster up and running, via the SQL console provided with CockroachDB, we need to create our database and a user.
一旦我们的集群启动并运行，通过CockroachDB提供的SQL控制台，我们需要创建我们的数据库和一个用户。
First of all, let’s start the SQL console:
首先，让我们启动SQL控制台。
cockroach sql --insecure;Now, let’s create our testdb database, create a user and add grants to the user in order to be able to perform CRUD operations:
现在，让我们创建我们的testdb数据库，创建一个用户并为该用户添加权限，以便能够执行CRUD操作。
CREATE DATABASE testdb;
CREATE USER user17 with password 'qwerty';
GRANT ALL ON DATABASE testdb TO user17;If we want to verify that the database was created correctly, we can list all the databases created in the current node:
如果我们想验证数据库是否被正确创建，我们可以列出当前节点中创建的所有数据库。
SHOW DATABASES;Finally, if we want to verify the automatic replication feature of CockroachDB, we can check on one of the two others nodes if the database was created correctly. To do so, we have to express the port flag when we are using the SQL console:
最后，如果我们想验证CockroachDB的自动复制功能，我们可以在另外两个节点中的一个检查数据库是否被正确创建。要做到这一点，我们必须在使用SQL控制台时表达port标志。
cockroach sql --insecure --port=26258;4. Monitoring CockroachDB
4.监控CockroachDB
Now that we have started our local cluster and created the database, we can monitor them using the CockroachDB Admin UI:
现在我们已经启动了本地集群并创建了数据库，我们可以使用CockroachDB管理界面监控它们。
This Admin UI, that comes in a bundle with CockroachDB, can be accessed at http://localhost:8080 as soon as the cluster is up and running. In particular, it provides details about cluster and database configuration, and helps us optimize cluster performance by monitoring metrics like:
这个与CockroachDB捆绑在一起的管理界面，可以在集群启动和运行后立即访问http://localhost:8080。特别是，它提供了关于集群和数据库配置的详细信息，并通过监控诸如等指标帮助我们优化集群性能。
- Cluster Health – essential metrics about the cluster’s health
- Runtime Metrics – metrics about node count, CPU time, and memory usage
- SQL Performance – metrics about SQL connections, queries and transactions
- Replication Details – metrics about how data is replicated across the cluster
- Node Details – details of live, dead, and decommissioned nodes
- Database Details – details about the system and user databases in the cluster
5. Project Setup
5.项目设置
Given our running local cluster of CockroachDB, in order to be able to connect to it, we have to add an additional dependency to our pom.xml:
鉴于我们正在运行的CockroachDB本地集群，为了能够连接到它，我们必须在我们的pom.xml中添加一个额外的依赖性：。
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.1.4</version>
</dependency>Or, for a Gradle project:
或者，对于一个Gradle项目。
compile 'org.postgresql:postgresql:42.1.4'6. Using CockroachDB
6.使用CockroachDB
Now that it’s clear what we’re working with and that everything is set up properly, let’s start using it.
现在我们很清楚我们在做什么，所有的东西都设置好了，让我们开始使用它。
Thanks to the PostgreSQL compatibility, it’s either possible to connect directly with JDBC or using an ORM, such as Hibernate (At the time of writing (Jan 2018) both drivers have been tested enough to claim the beta-level support according to the developers). In our case, we’ll use JDBC to interact with the database.
由于PostgreSQL的兼容性，要么直接用JDBC连接，要么使用ORM，如Hibernate（在撰写本文时（2018年1月），这两种驱动都经过了测试，根据开发人员的说法，足以声称beta级别支持）。在我们的案例中，我们将使用JDBC来与数据库进行交互。
For simplicity, we’ll follow with the basic CRUD operations as they are the best to start with.
为了简单起见，我们将遵循基本的CRUD操作，因为它们是最好的开始。
Let’s start by connecting to the database.
让我们从连接到数据库开始。
6.1. Connecting to CockroachDB
6.1.连接到CockroachDB
To open a connection with the database, we can use the getConnection() method of DriverManager class. This method requires a connection URL String parameter, a username, and a password:
为了打开与数据库的连接，我们可以使用DriverManager类的getConnection()方法。这个方法需要一个连接URLString参数、一个用户名和一个密码。
Connection con = DriverManager.getConnection(
"jdbc:postgresql://localhost:26257/testdb", "user17", "qwerty"
);6.2. Creating a Table
6.2.创建一个表
With a working connection, we can start creating the articles table that we are going to use for all the CRUD operations:
有了一个工作连接，我们就可以开始创建articles表，我们将使用该表进行所有的CRUD操作。
String TABLE_NAME = "articles";
StringBuilder sb = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
.append(TABLE_NAME)
.append("(id uuid PRIMARY KEY, ")
.append("title string,")
.append("author string)");
String query = sb.toString();
Statement stmt = connection.createStatement();
stmt.execute(query);If we want to verify that the table was properly created, we can use the SHOW TABLES command:
如果我们想验证该表是否被正确创建，我们可以使用SHOW TABLES命令。
PreparedStatement preparedStatement = con.prepareStatement("SHOW TABLES");
ResultSet resultSet = preparedStatement.executeQuery();
List tables = new ArrayList<>();
while (resultSet.next()) {
tables.add(resultSet.getString("Table"));
}
assertTrue(tables.stream().anyMatch(t -> t.equals(TABLE_NAME)));Let’s see how it’s possible to modify the just created table.
让我们看看如何修改刚刚创建的表。
6.3. Altering a Table
6.3.修改表格
If we missed some columns during the table creation or because we needed them later on, we can easily add them:
如果我们在创建表格时错过了一些列，或者因为我们后来需要它们，我们可以很容易地添加它们。
StringBuilder sb = new StringBuilder("ALTER TABLE ").append(TABLE_NAME)
.append(" ADD ")
.append(columnName)
.append(" ")
.append(columnType);
String query = sb.toString();
Statement stmt = connection.createStatement();
stmt.execute(query);Once we altered the table, we can verify if the new column was added using the SHOW COLUMNS FROM command:
一旦我们改变了表，我们可以使用SHOW COLUMNS FROM命令来验证新列是否被添加。
String query = "SHOW COLUMNS FROM " + TABLE_NAME;
PreparedStatement preparedStatement = con.prepareStatement(query);
ResultSet resultSet = preparedStatement.executeQuery();
List<String> columns = new ArrayList<>();
while (resultSet.next()) {
columns.add(resultSet.getString("Field"));
}
assertTrue(columns.stream().anyMatch(c -> c.equals(columnName)));6.4. Deleting a Table
6.4.删除一个表
When working with tables, sometimes we need to delete them and this can be easily achieved with few lines of code:
在处理表格时，有时我们需要删除它们，这可以通过几行代码轻松实现。
StringBuilder sb = new StringBuilder("DROP TABLE IF EXISTS ")
.append(TABLE_NAME);
String query = sb.toString();
Statement stmt = connection.createStatement();
stmt.execute(query);6.5. Inserting Data
6.5.插入数据
Once we have clear the operations that can be performed on a table, we can now start working with data. We can start defining the Article class:
一旦我们明确了可以对表进行的操作，我们现在就可以开始处理数据了。我们可以开始定义Article类。
public class Article {
private UUID id;
private String title;
private String author;
// standard constructor/getters/setters
}Now we can see how to add an Article to our articles table:
现在我们可以看看如何在我们的articles表中添加一个Article。
StringBuilder sb = new StringBuilder("INSERT INTO ").append(TABLE_NAME)
.append("(id, title, author) ")
.append("VALUES (?,?,?)");
String query = sb.toString();
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, article.getId().toString());
preparedStatement.setString(2, article.getTitle());
preparedStatement.setString(3, article.getAuthor());
preparedStatement.execute();6.6. Reading Data
6.6.读取数据
Once the data are stored in a table, we want to read those data and this can be easily achieved:
一旦数据被存储在表中，我们想读取这些数据，这可以很容易实现。
StringBuilder sb = new StringBuilder("SELECT * FROM ")
.append(TABLE_NAME);
String query = sb.toString();
PreparedStatement preparedStatement = connection.prepareStatement(query);
ResultSet rs = preparedStatement.executeQuery();However, if we don’t want to read all the data inside the articles table but just one Article, we can simply change how we build our PreparedStatement:
然而，如果我们不想读取article表内的所有数据，而只是读取一个Article，我们可以简单地改变我们建立PreparedStatement的方式。
StringBuilder sb = new StringBuilder("SELECT * FROM ").append(TABLE_NAME)
.append(" WHERE title = ?");
String query = sb.toString();
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, title);
ResultSet rs = preparedStatement.executeQuery();6.7. Deleting Data
6.7.删除数据
Last but not least, if we want to remove data from our table, we can delete a limited set of records using the standard DELETE FROM command:
最后但并非最不重要的是，如果我们想从表中删除数据，我们可以使用标准的DELETE FROM命令删除一组有限的记录。
StringBuilder sb = new StringBuilder("DELETE FROM ").append(TABLE_NAME)
.append(" WHERE title = ?");
String query = sb.toString();
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, title);
preparedStatement.execute();Or we can delete all the record, within the table, with the TRUNCATE function:
或者我们可以用TRUNCATE函数删除表中的所有记录。
StringBuilder sb = new StringBuilder("TRUNCATE TABLE ")
.append(TABLE_NAME);
String query = sb.toString();
Statement stmt = connection.createStatement();
stmt.execute(query);6.8. Handling Transactions
6.8.处理事务
Once connected to the database, by default, each individual SQL statement is treated as a transaction and is automatically committed right after its execution is completed.
一旦连接到数据库，默认情况下，每个单独的SQL语句被视为一个事务，并在执行完成后自动提交。
However, if we want to allow two or more SQL statements to be grouped into a single transaction, we have to control the transaction programmatically.
然而，如果我们想让两个或更多的SQL语句组合成一个事务，我们必须以编程方式控制事务。
First, we need to disable the auto-commit mode by setting the autoCommit property of Connection to false, then use the commit() and rollback() methods to control the transaction.
首先，我们需要将Connection的autoCommit属性设置为false来禁用自动提交模式，然后使用commit()和rollback()方法来控制事务。
Let’s see how we can achieve data consistency when doing multiple inserts:
让我们看看在做多次插入时如何实现数据的一致性。
try {
con.setAutoCommit(false);
UUID articleId = UUID.randomUUID();
Article article = new Article(
articleId, "Guide to CockroachDB in Java", "baeldung"
);
articleRepository.insertArticle(article);
article = new Article(
articleId, "A Guide to MongoDB with Java", "baeldung"
);
articleRepository.insertArticle(article); // Exception
con.commit();
} catch (Exception e) {
con.rollback();
} finally {
con.setAutoCommit(true);
}In this case, an exception was thrown, on the second insert, for the violation of the primary key constraint and therefore no articles were inserted in the articles table.
在这种情况下，在第二次插入时，由于违反了主键约束而抛出了一个异常，因此在articles表中没有插入文章。
7. Conclusion
7.结论
In this article, we explained what CockroachDB is, how to set up a simple local cluster, and how we can interact with it from Java.
在这篇文章中，我们解释了什么是CockroachDB，如何建立一个简单的本地集群，以及我们如何从Java中与它进行交互。
The complete source code for this article can be found, as always, over on Github.
这篇文章的完整源代码可以一如既往地在Github上找到，。