用postgresql和java处理树数据模型

As consultant / developer, one of the things I enjoy the most is building bridges between the current world and the future, between the current system and the future system, in this post I will tell of tale of a similar bridge building exercise in the SQL / Java / Docker land.

作为顾问/开发人员，我最喜欢的事情之一是在当前世界与未来之间，在当前系统与未来系统之间建立桥梁，在这篇文章中，我将讲述一个类似的在SQL中进行桥梁构建的故事。 / Java / Docker领域。

I will like to tease your appetite to read this article with a starting question and context setting .

我想挑逗您的胃口，以一个开始的问题和上下文设置来阅读本文。

Have you ever faced the need to store in SQL something that it is a tree like data structure ?

您是否曾经面对过需要在SQL中存储像树一样的数据结构的需求？

Examples of this requirement are recurring and I have seen it on a couple of different occasions like:

重复出现此要求的示例，我在几个不同的场合看到过它，例如：

• Product catalog
  产品目录
• Document management system
  文件管理系统
• Employee / company management system
  员工/公司管理系统

A few months ago I came across a very elegant solution to this problem and it is based on the PostgreSQL LTREE extension and an amazing set of articles by Pat Shaughnessy on this topic, so this is not a generic solution for any RBMS but it is leveraging PostgreSQL custom data types and if you are using PostgreSQL by reading this article you will be able replicate this solution.

几个月前，我遇到了一个非常优雅的解决方案，该解决方案基于PostgreSQL LTREE扩展以及Pat Shaughnessy撰写的有关该主题的一系列精彩文章，因此，这不是任何RBMS的通用解决方案，但它正在利用PostgreSQL自定义数据类型，如果您通过阅读本文使用PostgreSQL，则可以复制此解决方案。

Additionally Pat’s article is very good for you to understand why this solution is a good solution and I will not try to replicate this my post but I will build on top of his article and show you how to implement the solution in a modern way with :

另外，Pat的文章非常有助于您理解为什么该解决方案是一个好的解决方案，并且我不会尝试复制我的文章，但是我将以他的文章为基础，并向您展示如何以现代方式实现该解决方案：

• Schema migrations using Flyway

  使用Flyway进行架构迁移

• Test automation using Testcontainers

  使用测试容器进行自动化测试

• How to access the custom PostgresSQL data type from Java using Micronaut Data JDBC.

  如何使用Micronaut Data JDBC从Java访问自定义PostgresSQL数据类型。

• Provision the database and application container using Docker Compose and Terraform
  使用Docker Compose和Terraform设置数据库和应用程序容器

If you are like me you will probably be thinking in your head: “Show me the code” well the complete solution is available at my GitHub account at:

如果您像我一样，可能会想到：“向我展示代码”，完整的解决方案可以在我的GitHub帐户上找到：

The all example code is nicely brought together by the Micronaut Framework, the example schema I am using for the testing is the tree structure bellow:

Micronaut Framework将所有示例代码很好地组合在一起，我用于测试的示例架构是下面的树结构：

To create this the following SQL DDL statement are issued:

为此，发出以下SQL DDL语句：

The statement at line 1 enable the LTREE custom data type in the target schema thus making sure that it is available for use to use it line 5 as part of the table creation as you can see this table has a column called path of type LTREE, and there is more to say about the content of this column shortly.

第1行的语句在目标模式中启用了LTREE自定义数据类型，因此请确保在第5行中将其用作表创建的一部分，因为您可以看到该表具有称为LTREE类型的path的列，不久以后，还有更多关于该专栏内容的发言。

Additionally in line 8 we need to let PostgreSQL know that we need a GIST index for the path column.

此外，在第8行中，我们需要让PostgreSQL知道我们需要path列的GIST索引。

In the image above you can see from line 1 to 7 each of the tree nodes been created in SQL code and the important part is the path column where at each record we are defining is location in the tree with each node parent node preceded by a “.” .

在上图中，您可以从第1行到第7行看到每个树节点都是用SQL代码创建的，而重要的部分是path列，在该列中，我们定义的每个记录都是树中的位置，每个节点的父节点前面都有一个“。” 。

By using Micronaut Flyway I able to write schema creation and versioning so when the application boot it will connect to the database and figure out that if the required schema has not been create already it needs to issue the SQL statements over to the database.

通过使用Micronaut Flyway，我可以编写模式创建和版本控制，因此在应用程序启动时它将连接到数据库，并弄清楚如果尚未创建所需的模式，则需要将SQL语句发布到数据库。

In the example code you will find standard bread and butter Micronaut JDBC Data code data access code in the domain and repository packages.

在示例代码中，您将在域和存储库包中找到标准的Micronaut JDBC Data数据访问代码。

In the domain layer package I have created the classes Record, LTree and LTreeConverters.

在域层包中，我创建了Record ， LTree和LTreeConverters类。

The Record class is a direct mapping one to one of the records in the previously created SQL table ‘tree’ what is particularly interesting in the domain package is the LTree class.

Record类是一个直接映射到先前创建SQL表“ tree ”中的记录之一的方法，在域包中特别有趣的是LTree类。

In the image above see the contents of the LTree class and this is Micronaut’s way of supporting custom SQL data types in JDBC code thus allowing the mapping of the LTREE PostgreSQL custom data type into an equivalent Java type, that we do not have support out the box in standard JDBC.

在上图中，看到LTree类的内容，这是Micronaut在JDBC代码中支持自定义SQL数据类型的方式，因此允许将LTREE PostgreSQL自定义数据类型映射为等效的Java类型，而我们不支持标准JDBC中的框。

Micronaut uses the TypeDef annotation to know when creating reading a record from the database that it has to use a type converter for the path column custom SQL data type.

Micronaut使用TypeDef批注来了解从数据库中读取记录的创建过程，该注释必须为路径列自定义SQL数据类型使用类型转换器。

The image above it the content of the LTree type converter code allowing us to read and write to the LTREE column in the database it is on the Java side we are handling the contents as a String and Micronaut is able to use dependency injection to discover the needed type converters and the most beautiful part is that it is able to do all of this whiteout using Java Reflection as it is using annotations preprocessing at compile time, so that the generated code is efficient on memory usage.

上面的图像是LTree类型转换器代码的内容，它使我们可以读写数据库中的LTREE列，这是在Java方面，我们将内容作为String进行处理，并且Micronaut能够使用依赖项注入来发现所需的类型转换器，最漂亮的部分是它能够使用Java反射来完成所有此中断操作，因为它在编译时使用注释预处理，因此生成的代码可以有效地利用内存。

Next up is the repository package where use the domain layer object to perform database access code.

接下来是存储库软件包，其中使用域层对象执行数据库访问代码。

The image above you can see the contents of the RecordRepository class and although small it is packed with juice and there is a lot going on, let us unpack and understand what is going on where.

您可以在上面的图像中看到RecordRepository类的内容，尽管它很小，但里面装满了果汁，并且发生了很多事情，让我们打开包装，了解一下发生了什么。

In line 17 we are using the JdbcRepository annotation that tells Micronaut that this is a JDBC data access object and that we are talking using the PostgreSQL dialect.

在第17行中，我们使用JdbcRepository注释，该注释告诉Micronaut这是一个JDBC数据访问对象，并且我们正在使用PostgreSQL方言进行交谈。

In line 18 we are implementing the CrudRepository interface and this will hint to Micronaut to link for use the all basic CRUD database access pluming code. The reason this is an abstract class is because we are not the one creating instances of this class but the object of this class will be given to us by Micronaut at runtime.

在第18行中，我们正在实现CrudRepository接口，这将提示Micronaut链接以使用所有基本的CRUD数据库访问权限代码。 之所以是抽象类，是因为我们不是创建此类的实例的人，而是Micronaut在运行时将此类的对象提供给我们的。

The childrenOf method is where the magic of the LTREE type comes to live as you can see in line 28 we are making a SQL select query using the special “@>” operator that it means give all the node having me as parent.

如第28行所示，childrenOf方法是LTREE类型的魔力得以实现的地方，我们正在使用特殊的“ @> ”运算符进行SQL选择查询，这意味着将所有将我作为父节点的节点给定。

Additionally in line 38 it is very important the use of the Types.OTHER as this tell the JDBC driver that this parameter is a custom SQL data type.

此外，在第38行中，使用Types.OTHER非常重要，因为这告诉JDBC驱动程序此参数是自定义SQL数据类型。

Finally in line 32 we are reading the result of the query to the database and mapping it to a list of Record object instances and this is possible because we have defined the LTree and LTreeConverters in the domain layer.

最后，在第32行中，我们正在将查询结果读取到数据库，并将其映射到Record对象实例的列表，这是可能的，因为我们已经在域层中定义了LTree和LTreeConverters 。

There is more interesting code in the controller package where I create the tree representation as a JSON object to return as part of the API response.

控制器包中还有更多有趣的代码，其中我将树表示形式创建为JSON对象以作为API响应的一部分返回。

The image above shows how to run the code from the command line and as you can see the Flyway database migrations have been run and it found that the schema in the database is up to date.

上图显示了如何从命令行运行代码，并且可以看到Flyway数据库迁移已运行，并且发现数据库中的架构是最新的。

The image above this the result of executing the a simple test using VSCode REST Client plugin.

上面的图像是使用VSCode REST Client插件执行简单测试的结果。

翻译自: https://medium.com/@luistrigueiros/handling-tree-data-models-with-postgresql-and-java-3c440105dead