方便的JDBC

jdbc和jdbc驱动

In this library I tried to find a minimal abstraction for the standard Java JDBC framework. Overwhelmed by the complexity of the existing OR-mapping frameworks, I was searching for a library, which doesn’t try to hide SQL from the developer. But still gives you enough support by handling the tedious parts of JDBC. With a background in the Scala/Play development, I’d enjoyed using the Scala Anorm library. Unfortunately I couldn’t find a similar library for Java. So, I decided to implement one myself.

在这个库中，我试图为标准Java JDBC框架找到一个最小的抽象。 由于现有OR映射框架的复杂性不堪重负，我一直在寻找一个库，该库不会尝试向开发人员隐藏SQL。 但是通过处理JDBC的繁琐部分仍然可以为您提供足够的支持。 在Scala / Play开发背景下，我很喜欢使用Scala Anorm 库 。 不幸的是，我找不到类似的Java库。 因此，我决定自己实施。

This blog gives you an overview over the usage of the FacileJDBC library, mainly by example. A detailed description of the API is available as Javadoc and the library itself is available on Maven Central. The source code can be downloaded from GitHub.

该博客主要通过示例为您概述了FacileJDBC库的用法。 API的详细说明可以通过Javadoc获得 ，而库本身可以在Maven Central中获得 。 可以从GitHub下载源代码。

总览 (Overview)

FacileJDBC给您 (FacileJDBC gives you)

• A lightweight wrapper around the JDBC API.
  JDBC API的轻量级包装。

• The possibility to fill query parameters by name instead of its position: Available via the Param interface.

  通过名称而不是位置填充查询参数的可能性:通过Param界面可用。

• Functions for creating (parsing) entity objects from query ResultSets: Available via the RowParser interface

  用于从查询ResultSet创建(解析) 实体对象的函数:通过RowParser接口可用

• Functions for splitting (deconstructing) entity objects to DB columns: Available via the Dctor interface.

  将实体对象拆分(解构)为DB列的功能:通过Dctor接口可用。

• A Query object to putting all things together.

  一个将所有东西放在一起的Query对象。

• Lightweight transaction handling support.
  轻量级事务处理支持。

FacileJDBC不是 (FacileJDBC is not)

• An OR-Mapper
  或映射
• A type safe query language
  一种类型安全的查询语言
• An SQL query builder
  SQL查询构建器

FacileJDBC没有 (FacileJDBC has no)

• DB-vendor specific code, uses 100% pure JDBC.
  DB供应商特定的代码，使用100％纯JDBC。
• Query generation capabilities. The user is responsible for creating the proper SQL string.
  查询生成功能。 用户负责创建正确SQL字符串。
• Generated classes or dynamically generated proxies.
  生成的类或动态生成的代理。
• No connection pooling.
  没有连接池。

Other Java DB libraries

其他Java DB库

• jOOQ: Excellent library for accessing databases in a type safe way. Because of the different scope, it is more than a thin wrapper around the JDBC API.

  jOOQ :出色的库，用于以类型安全的方式访问数据库。 由于范围不同，因此它不仅仅是JDBC API的瘦包装。

• Jdbi: Similar scope, but with a different approach.

  Jdbi :范围类似，但方法不同。

例子 (Examples)

The following example show how to use FacileJDBC for different use cases.

下面的示例演示如何在不同的用例中使用FacileJDBC 。

执行查询 (Executing queries)

SQL queries are defined via the Query class. Since the Query class is immutable, it is safe to use it in a multi-threaded environment or define it as static class member. The Query class is the main entry point of the FacileJDBC library. For executing a query, all what it needs is a JDBC Connection.

SQL查询是通过Query类定义的。 由于Query类是不可变的，因此可以在多线程环境中使用它或将其定义为静态类成员是安全的。 Query类是FacileJDBC库的主要入口点。 为了执行查询，它所需要的只是一个JDBC Connection 。

The execute method returns a boolean value as specified in the PreparedStatement.execute() method. The Javadoc documents the used methods of the PreparedStatement for every execute method.

execute方法返回在PreparedStatement.execute()方法中指定的boolean值。 Javadoc为每个execute方法记录PreparedStatement的已使用方法。

选择对象 (Selecting objects)

Usually, your selected rows will be stored in DTO objects. For the simple examples the following Person DTO will be used.

通常，您选择的行将存储在DTO对象中。 对于简单的示例，将使用以下Person DTO。

The following query will select all persons which matches a given name pattern.

以下查询将选择与给定名称模式匹配的所有人员。

Executing the select query is shown in the following code snippet. It also shows how query parameter are set and how the result rows are parsed.

以下代码段显示了如何执行选择查询。 它还显示了如何设置查询参数以及如何解析结果行。

The Query.on method is used for filling the query parameters. The variables uses the usual syntax for SQL bind variables. With the Param.value factory method it is possible to fill the defined variables. The Query.as method executes the query and returns the parsed result rows. For converting the query result to a DTO, a RowParser is needed. With the RowParser.list() method you will tell the query that you expect 0 to n result rows. If only one result is expected, you need to use the RowParser.single() or RowParser.singleOp() method.

Query.on方法用于填充查询参数。 变量使用SQL绑定变量的常用语法。 使用Param.value工厂方法可以填充定义的变量。 Query.as方法执行查询并返回解析的结果行。 要将查询结果转换为DTO ，需要RowParser 。 使用RowParser.list()方法，您将告诉查询期望0到n个结果行。 如果仅预期一个结果，则需要使用RowParser.single()或RowParser.singleOp()方法。

The following code snippet shows the RowParser implementation for our Person DTO.

以下代码段显示了Person DTO的RowParser实现。

Since the RowParser is a functional interface it can be written as shown. The first function parameter represents the actual selected row and second parameter the JDBC Connection used for executing the query. In most cases the conn will not be used, but it is quite helpful for fetching dependent DTOs in a sub-query.

由于RowParser是一个功能接口，因此可以如图所示编写。 第一个函数参数表示实际选择的行，第二个参数表示JDBC Connection用于执行查询的参数。 在大多数情况下，不会使用conn ，但是对于在子查询中获取依赖的DTO很有帮助。

插入物件 (Inserting objects)

For inserting one new Person into the DB an insert query have to be defined.

为了将一个新的Person插入数据库，必须定义一个插入查询。

When all bind variable has been set, it can be inserted by calling the execute method.

设置所有绑定变量后，可以通过调用execute方法将其插入。

Setting the bind variables this way is quite tedious, if you already have a filled Person DTO. Inserting the Person DTO directly you need to define the variable-field mapping. This is done via the Dctor (de-constructor) interface. The Dctor can be seen as the inverse function of the RowParser.

如果您已经有一个已填充的Person DTO，则以这种方式设置绑定变量将非常繁琐。 直接插入Person DTO，您需要定义变量字段映射。 这是通过Dctor ( Dctor函数)接口完成的。 的Dctor可以被看作是的逆函数RowParser 。

Once a de-constructor is defined for your DTO, you can easily insert single Person objects.

为DTO定义解构函数后，您可以轻松插入单个Person对象。

If you are interested in the automatically generated primary key of the insertion, you have to use the executeInsert method. This method returns an Optional in the case no primary key could be generated.

如果您对自动生成的插入主键感兴趣，则必须使用executeInsert方法。 如果无法生成主键，则此方法返回Optional 。

or

要么

if you are need to control the parsing of the generated primary key.

如果需要控制所生成主键的解析。

批量插入 (Batch insertion)

If you have a collection of Person s, you can insert it in one batch.

如果您具有Person的集合，则可以将其批量插入。

For simple insertions, you can also do some kind of ad-hoc batch insertions.

对于简单的插入，您还可以进行某种临时的批量插入。

选择/插入对象图 (Selecting/inserting object graphs)

The previous examples shows the basic usage of the library. It is possible to use this for all needed select and insert queries, as you will do it with plain JDBC. If you need to select or insert small object graphs, this becomes fast tedious as well. Let’s extend our initial example an convert the link of the Person into an object

前面的示例显示了该库的基本用法。 可以将它用于所有需要的选择和插入查询，就像使用普通JDBC一样。 如果您需要选择或插入小对象图，这也会变得很繁琐。 让我们扩展最初的示例，将Person的链接转换为对象

and with a Link class, which will look like the following.

并带有一个Link类，如下所示。

It is now possible to create one RowParser<Person> and one Dctor<Person> which automatically takes care about the linked Link object. The new parser will look like the following code snippet.

现在可以创建一个RowParser<Person>和一个Dctor<Person> ，它们将自动处理链接的Link对象。 新的解析器将类似于以下代码片段。

With the shown de-constructor.

使用所示的解构函数。

The needed helper methods are responsible for selecting/inserting the Link object.

所需的帮助程序方法负责选择/插入Link对象。

It is still necessary to implement the sub-inserts and sub-selects, but this can be reused in other queries, where inserting and selecting of Link s is needed. Note that this is not an automatic OR-mapping mechanism. The user is still in charge for the concrete implementation.

仍然有必要实现子插入和子选择，但这可以在需要插入和选择Link的其他查询中重用。 请注意，这不是自动的OR映射机制。 用户仍然负责具体的实现。

Note

注意

Although the described feature is quite expressive and may solve some selection/insertion task in an elegant way, does not mean you have to use it. Just treat it as additional possibility.

尽管所描述的功能具有很强的表现力，并且可以以优雅的方式解决某些选择/插入任务，但这并不意味着您必须使用它。 只是将其视为其他可能性。

交易处理 (Transaction handling)

FacileJDBC also contains two interfaces for simple transaction handling. The Transaction interface defines methods for executing one or more queries in a transactional context.

FacileJDBC还包含两个用于简单事务处理的接口。 Transaction接口定义用于在事务上下文中执行一个或多个查询的方法。

If you are not interested in the return value of the SQL execution, you can use the accept method instead. In the case of an error, the connection is rolled back. If everything works fine, the connection is committed.

如果您对SQL执行的返回值不感兴趣，则可以改用accept方法。 发生错误时，连接将回滚。 如果一切正常，则连接已提交。

The second interface is the Transactional interface, which represents the transactional capability, typically exposed by a database. In this sense, it can be seen as a minimal database interface, just by exposing a Connection factory method, Transactional::connection. Since Transactional is a functional interface, it can easily created by defining the Connection factory method.

第二个接口是“ Transactional接口，它表示通常由数据库公开的事务功能。 从这个意义上讲，仅通过公开Connection工厂方法Transactional::connection ，就可以将其视为最小的数据库接口。 由于Transactional是功能性接口，因此可以通过定义Connection factory方法轻松创建。

The example above shows how to create a Transactional instance for a HSQLDB in-memory database, perfectly usable for testing purposes. Then it can be used for performing some SQL inserts.

上面的示例显示了如何为HSQLDB内存数据库创建Transactional实例，该实例可完美地用于测试目的。 然后可以将其用于执行一些SQL插入。

For production code you usually have a DataSource, which represents the connection to the DB. It's equally easy to create a Transactional object from a given DataSource instance.

对于生产代码，通常有一个DataSource ，它表示与数据库的连接。 从给定的DataSource实例创建Transactional对象同样容易。

翻译自: https://medium.com/swlh/facilejdbc-e6e84c8fedab

jdbc和jdbc驱动