1. Introduction
1.介绍
In this tutorial, we’re going to take a look at Sql2o, a small and fast library for relational database access in idiomatic Java.
在本教程中，我们将看看Sql2o，这是一个小型且快速的库，用于在习惯的Java中访问关系数据库。
It is worth to mention that even though Sql2o works by mapping query results to POJOs (plain old Java objects), it’s not a complete ORM solution such as Hibernate.
值得一提的是，尽管Sql2o通过将查询结果映射到POJOs（普通的老式Java对象）来工作，它不是一个完整的ORM解决方案，如Hibernate。
2. Sql2o Setup
2.Sql2o设置
Sql2o is a single jar file that we can easily add to our project’s dependencies:
Sql2o是一个单一的jar文件，我们可以很容易地将其添加到我们项目的依赖项中：。
<dependency>
<groupId>org.sql2o</groupId>
<artifactId>sql2o</artifactId>
<version>1.6.0</version>
</dependency>We’ll also use HSQL, the embedded database, in our examples; in order to follow along, we can include it as well:
在我们的例子中，我们还将使用HSQL，即嵌入式数据库；为了跟随，我们也可以包括它。
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>2.4.0</version>
<scope>test</scope>
</dependency>Maven Central hosts the latest version of sql2o and HSQLDB.
Maven Central托管了最新版本的sql2o和HSQLDB。
3. Connecting to the Database
3.连接到数据库
To establish a connection, we start from an instance of the Sql2o class:
为了建立一个连接，我们从一个Sql2o类的实例开始：。
Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");Here, we’re specifying the connection URL, username, and password as constructor parameters.
在这里，我们指定连接的URL、用户名和密码作为构造函数参数。
The Sql2o object is thread-safe and we can share it across the application.
Sql2o 对象是线程安全的，我们可以在整个应用程序中共享它。
3.1. Using a DataSource
3.1.使用一个数据源
In most applications, we’ll want to use a DataSource instead of a raw DriverManager connection, perhaps to leverage a connection pool, or to specify additional connection parameters. Worry not, Sql2o has got us covered:
在大多数应用程序中，我们希望使用一个数据源而不是一个原始的DriverManager连接，也许是为了利用一个连接池，或者指定额外的连接参数。不用担心，Sql2o已经为我们准备好了。
Sql2o sql2o = new Sql2o(datasource);3.2. Working With Connections
3.2.使用连接
Merely instantiating a Sql2o object does not establish any connection to the database.
仅仅实例化一个 Sql2o对象并不能建立与数据库的任何连接。
Instead, we use the open method to get a Connection object (note that it’s not a JDBC Connection). Since Connection is AutoCloseable, we can wrap it in a try-with-resources block:
相反，我们使用open方法来获得一个Connection对象（注意，这不是一个JDBCConnection）。由于Connection是AutoCloseable，我们可以将其封装在try-with-resources块中。
try (Connection connection = sql2o.open()) {
// use the connection
}4. Insert and Update Statements
4.插入和更新语句
Now let’s create a database and put some data in it. Throughout the tutorial, we’ll use a simple table called project:
现在，让我们创建一个数据库并在其中放入一些数据。在整个教程中，我们将使用一个名为project:的简单表。
connection.createQuery(
"create table project "
+ "(id integer identity, name varchar(50), url varchar(100))").executeUpdate();executeUpdate returns the Connection object so that we can chain multiple calls. Then, if we want to know the number of affected rows, we use getResult:
executeUpdate返回Connection对象，这样我们就可以进行多次连锁调用。然后，如果我们想知道受影响的行的数量，我们使用getResult:。
assertEquals(0, connection.getResult());We’ll apply the pattern that we’ve just seen – createQuery and executeUpdate – for all DDL, INSERT and UPDATE statements.
我们将应用我们刚刚看到的模式–createQuery 和executeUpdate– 用于所有的DDL、INSERT和UPDATE语句。
4.1. Getting Generated Key Values
4.1.获取生成的密钥值
In some cases, though, we might want to get generated key values back. Those are the values of key columns that are automatically computed (like when using auto-increment on certain databases).
不过在某些情况下，我们可能想要取回生成的键值。这些是自动计算的键列的值（比如在某些数据库上使用自动递增时）。
We do that in two steps. First, with an additional parameter to createQuery:
我们分两步来做这件事。首先，给createQuery增加一个参数：。
Query query = connection.createQuery(
"insert into project (name, url) "
+ "values ('tutorials', 'github.com/eugenp/tutorials')", true);Then, invoking getKey on the connection:
然后，在连接上调用getKey。
assertEquals(0, query.executeUpdate().getKey());If the keys are more than one, we use getKeys instead, which returns an array:
如果键值超过一个，我们使用getKeys代替，它返回一个数组。
assertEquals(1, query.executeUpdate().getKeys()[0]);5. Extracting Data From the Database
5.从数据库中提取数据
Let’s now get to the core of the matter: SELECT queries and the mapping of result sets to Java objects.
现在让我们来讨论一下问题的核心。SELECT 查询和结果集与Java对象的映射。
First, we have to define a POJO class with getters and setters to represent our projects table:
首先，我们必须定义一个带有getters和setters的POJO类来表示我们的项目表。
public class Project {
long id;
private String name;
private String url;
//Standard getters and setters
}Then, as before, we’ll write our query:
然后，像以前一样，我们将编写我们的查询。
Query query = connection.createQuery("select * from project order by id");However, this time we’ll use a new method, executeAndFetch:
然而，这一次我们将使用一个新的方法，executeAndFetch:。
List<Project> list = query.executeAndFetch(Project.class);As we can see, the method takes the class of the results as a parameter, to which Sql2o will map the rows of the raw result set coming from the database.
我们可以看到，该方法将结果的类别作为一个参数，Sql2o将把来自数据库的原始结果集的行映射到该参数上。
5.1. Column Mapping
5.1.列的映射
Sql2o maps columns to JavaBean properties by name, case-insensitive.
Sql2o通过名称将列映射到JavaBean属性，不区分大小写。
However, naming conventions differ between Java and relational databases. Suppose that we add a creation date property to our projects:
然而，Java和关系型数据库之间的命名惯例是不同的。假设我们在项目中添加一个创建日期属性。
public class Project {
long id;
private String name;
private String url;
private Date creationDate;
//Standard getters and setters
}In the database schema, most probably we’ll call the same property creation_date.
在数据库模式中，很可能我们会调用同一个属性creation_date。
Of course, we can alias it in our queries:
当然，我们可以在我们的查询中对其进行别名。
Query query = connection.createQuery(
"select name, url, creation_date as creationDate from project");However, it’s tedious and we lose the possibility to use select *.
然而，这很乏味，而且我们失去了使用select *.的可能性。
Another option is to instruct Sql2o to map creation_date to creationDate. That is, we can tell the query about the mapping:
另一个选择是指示Sql2o将creation_date映射到creationDate.，也就是说，我们可以告诉查询关于映射的信息。
connection.createQuery("select * from project")
.addColumnMapping("creation_date", "creationDate");This is nice if we use creationDate sparingly, in a handful of queries; however, when used extensively in a larger project, it becomes tedious and error-prone to tell the same fact over and over.
如果我们在少量的查询中谨慎地使用creationDate，这很好；但是，当在一个较大的项目中广泛使用时，一遍又一遍地讲述同一个事实就会变得乏味和容易出错。
Fortunately, we can also specify mappings globally:
幸运的是，我们也可以全局地指定映射：。
Map<String, String> mappings = new HashMap<>();
mappings.put("CREATION_DATE", "creationDate");
sql2o.setDefaultColumnMappings(mappings);Of course, this will cause every instance of creation_date to be mapped to creationDate, so that’s another reason for striving to keep names consistent across the definitions of our data.
当然，这将导致creation_date的每个实例都被映射为creationDate，所以这也是我们努力在数据定义中保持名称一致的另一个原因。
5.2. Scalar Results
5.2.标量结果
Sometimes, we want to extract a single scalar result from a query. For example, when we need to count the number of records.
有时，我们想从一个查询中提取一个单一的标量结果。例如，当我们需要计算记录的数量时。
In those cases, defining a class and iterating over a list that we know to contain a single element is overkill. Thus, Sql2o includes the executeScalar method:
在这些情况下，定义一个类并在一个我们知道包含单个元素的列表上进行迭代是多余的。因此，Sql2o包括executeScalar方法：
Query query = connection.createQuery(
"select count(*) from project");
assertEquals(2, query.executeScalar(Integer.class));Here, we’re specifying the return type to be Integer. However, that’s optional and we can let the underlying JDBC driver decide.
这里，我们指定返回类型为Integer。然而，这是可选的，我们可以让底层的JDBC驱动程序决定。
5.3. Complex Results
5.3.复杂的结果
Sometimes instead, complex queries (such as for reporting) may not easily map onto a Java object. We might also decide that we don’t want to code a Java class to use only in a single query.
相反，有时复杂的查询（如用于报告）可能不容易映射到一个Java对象。我们也可能决定，我们不想编码一个Java类，只在单个查询中使用。
Thus, Sql2o also allows a lower-level, dynamic mapping to tabular data structures. We get access to that using the executeAndFetchTable method:
因此，Sql2o也允许对表格数据结构进行较低层次的动态映射。我们通过executeAndFetchTable方法来获得这种映射。
Query query = connection.createQuery(
"select * from project order by id");
Table table = query.executeAndFetchTable();Then, we can extract a list of maps:
然后，我们可以提取一个地图的列表。
List<Map<String, Object>> list = table.asList();
assertEquals("tutorials", list.get(0).get("name"));Alternatively, we can map the data onto a list of Row objects, that are mappings from column names to values, akin to ResultSets:
另外，我们可以将数据映射到Row对象的列表中，这是从列名到值的映射，类似于ResultSets。
List<Row> rows = table.rows();
assertEquals("tutorials", rows.get(0).getString("name"));6. Binding Query Parameters
6.绑定查询参数
Many SQL queries have a fixed structure with a few parameterized portions. We might naively write those partially dynamic queries with string concatenation.
许多SQL查询有一个固定的结构，其中有一些参数化的部分。我们可能会天真地用字符串连接法来编写这些部分动态查询。
However, Sql2o allows parameterized queries, so that:
然而，Sql2o允许参数化查询，因此。
- We avoid SQL injection attacks
- We allow the database to cache often-used queries and gain in performance
- Finally, we are spared from the need to encode complex types such as dates and times
So, we can use named parameters with Sql2o to achieve all of the above. We introduce parameters with a colon and we bind them with the addParameter method:
因此，我们可以使用Sql2o的命名参数来实现上述所有的目的。我们用冒号引入参数，并用addParameter方法将其绑定。
Query query = connection.createQuery(
"insert into project (name, url) values (:name, :url)")
.addParameter("name", "REST with Spring")
.addParameter("url", "github.com/eugenp/REST-With-Spring");
assertEquals(1, query.executeUpdate().getResult());6.1. Binding From a POJO
6.1.从POJO中绑定
Sql2o offers an alternative way of binding parameters: that is, by using POJOs as the source. This technique is particularly suitable when a query has many parameters and they all refer to the same entity. So, let’s introduce the bind method:
Sql2o提供了另一种绑定参数的方法：即通过使用POJOs作为源。当一个查询有很多参数并且它们都指向同一个实体时，这种技术特别适合。所以，让我们来介绍一下bind方法：
Project project = new Project();
project.setName("REST with Spring");
project.setUrl("github.com/eugenp/REST-With-Spring");
connection.createQuery(
"insert into project (name, url) values (:name, :url)")
.bind(project)
.executeUpdate();
assertEquals(1, connection.getResult());7. Transactions and Batch Queries
7.事务和批量查询
With a transaction, we can issue multiple SQL statements as a single operation that is atomic. That is, either it succeeds or it fails in bulk, with no intermediate results. In fact, transactions are one of the key features of relational databases.
通过事务，我们可以将多个SQL语句作为一个单一的操作发布，该操作是原子性的。也就是说，要么成功，要么批量失败，没有中间结果。事实上，事务是关系型数据库的关键特征之一。
In order to open a transaction, we use the beginTransaction method instead of the open method that we’ve used so far:
为了打开一个交易，我们使用beginTransaction方法，而不是我们到目前为止使用的open方法。
try (Connection connection = sql2o.beginTransaction()) {
// here, the transaction is active
}When execution leaves the block, Sql2o automatically rolls back the transaction if it’s still active.
当执行离开区块时，Sql2o会自动回滚交易，如果它仍然处于活动状态。
7.1. Manual Commit and Rollback
7.1.手动提交和回滚
However, we can explicitly commit or rollback the transaction with the appropriate methods:
然而，我们可以用适当的方法显式地提交或回滚事务：。
try (Connection connection = sql2o.beginTransaction()) {
boolean transactionSuccessful = false;
// perform some operations
if(transactionSuccessful) {
connection.commit();
} else {
connection.rollback();
}
}Note that both commit and rollback end the transaction. Subsequent statements will run without a transaction, thus they won’t be automatically rolled back at the end of the block.
注意，commit 和rollback 都会结束事务。后续的语句将在没有事务的情况下运行，因此它们不会在区块结束时被自动回滚。
However, we can commit or rollback the transaction without ending it:
然而，我们可以提交或回滚事务而不结束它。
try (Connection connection = sql2o.beginTransaction()) {
List list = connection.createQuery("select * from project")
.executeAndFetchTable()
.asList();
assertEquals(0, list.size());
// insert or update some data
connection.rollback(false);
// perform some other insert or update queries
}
// implicit rollback
try (Connection connection = sql2o.beginTransaction()) {
List list = connection.createQuery("select * from project")
.executeAndFetchTable()
.asList();
assertEquals(0, list.size());
}7.2. Batch Operations
7.2.批量操作
When we need to issue the same statement many times with different parameters, running them in a batch provides a great performance benefit.
当我们需要以不同的参数多次发布相同的语句时，以批处理的方式运行它们会带来很大的性能优势。
Fortunately, by combining two of the techniques that we’ve described so far – parameterized queries and transactions – it’s easy enough to run them in batch:
幸运的是，通过结合我们到目前为止所描述的两种技术–参数化查询和事务–就可以很容易地批量运行它们。
- First, we create the query only once
- Then, we bind the parameters and call addToBatch for each instance of the query
- Finally, we call executeBatch:
try (Connection connection = sql2o.beginTransaction()) {
Query query = connection.createQuery(
"insert into project (name, url) " +
"values (:name, :url)");
for (int i = 0; i < 1000; i++) {
query.addParameter("name", "tutorials" + i);
query.addParameter("url", "https://github.com/eugenp/tutorials" + i);
query.addToBatch();
}
query.executeBatch();
connection.commit();
}
try (Connection connection = sql2o.beginTransaction()) {
assertEquals(
1000L,
connection.createQuery("select count(*) from project").executeScalar());
}7.3. Lazy Fetch
7.3.懒人取物
Conversely, when a single query returns a great number of results, converting them all and storing them in a list is heavy on memory.
相反，当一个单一的查询返回大量的结果时，将它们全部转换并存储在一个列表中，对内存的影响很大。
So, Sql2o supports a lazy mode, where rows are returned and mapped one at a time:
因此，Sql2o支持一种懒惰模式，即每次返回和映射的行。
Query query = connection.createQuery("select * from project");
try (ResultSetIterable<Project> projects = query.executeAndFetchLazy(Project.class)) {
for(Project p : projects) {
// do something with the project
}
}Note that ResultSetIterable is AutoCloseable and is meant to be used with try-with-resources to close the underlying ResultSet when finished.
请注意，ResultSetIterable是AutoCloseable，并且要与try-with-resources一起使用，以便在完成后关闭底层的ResultSet。
8. Conclusions
8.结论
In this tutorial, we’ve presented an overview of the Sql2o library and its most common usage patterns. Further information can be found in the Sql20 wiki on GitHub.
在本教程中，我们已经介绍了Sql2o库的概况及其最常见的使用模式。进一步的信息可以在GitHub上的Sql20 wiki中找到。。
Also, the implementation of all these examples and code snippets can be found in the GitHub project, which is a Maven project, so it should be easy to import and run as-is.
另外，所有这些例子和代码片段的实现都可以在GitHub项目中找到，它是一个Maven项目，所以应该很容易导入并按原样运行。