使用Springboot在Oracle，MySQL数据库中创建用户-Spring Data JPA [英] Create users in Oracle, MySQL databases using Springboot - Spring Data JPA

问题描述

我对Springboot和Spring Data JPA还是陌生的，正在研究一个用例，在该用例中，我需要在不同的数据库中创建用户. 该应用程序将从队列中接收2个输入-用户名和数据库名称. 使用此方法，我必须在给定的数据库中设置给定的用户. 我无法理解项目架构. 由于我需要运行的查询将采用以下格式-创建由密码标识的用户ABC； 在模型类，存储库等方面，项目的外观如何?由于我没有要针对其运行查询的实际表，因此我需要一个模型类，因为这样不会发生任何列映射.

I am very new to Springboot and Spring Data JPA and working on a use case where I am required to create users in different databases. The application will receive 2 inputs from a queue - username and database name. Using this I have to provision the given user in the given database. I am unable to understand the project architecture. Since the query I need to run will be of the format - create user ABC identified by password; How should the project look like in terms of model class, repositories etc? Since I do not have an actual table against which the query will be run, do I need a model class since there will be no column mappings happening as such.

TLDR-帮助构建配置有多个数据源的Springboot-Spring Data JPA应用程序，以运行以下格式的查询:创建由密码标识的用户

TLDR - Help in architecturing Springboot-Spring Data JPA application configured with multiple data sources to run queries of the format : create user identified by password

我一直在使用此GitHub存储库作为参考- https://github.com/jahe/spring-boot-multiple-datasources/blob/master/src/main/java/com/foobar

I have been using this GitHub repo for reference - https://github.com/jahe/spring-boot-multiple-datasources/blob/master/src/main/java/com/foobar

推荐答案

我将在此处进行一些假设:

I'll be making some assumptions here:

• 根据提供的语法，您选择的数据库为Oracle:create user ABC identified by password
• 您要创建并列出用户
• 您的数据库是众所周知的，并在JNDI中定义

• your database of choice is Oracle, based on provided syntax: create user ABC identified by password
• you want to create and list users
• your databases are well-known and defined in JNDI

不幸的是，我不能仅仅提供代码，因为设置代码会花费我一些时间，但是我可以为您提供要点.

I can't just provide code unfortunately as setting it up would take me some work, but I can give you the gist of it.

方法1:使用JPA

• 首先，创建一个User实体和一个相应的UserRepository.将实体绑定到all_users表.主键可能是USERNAME或USER_ID列...，但这并不重要，因为您不会在该表中进行任何插入.
• 要创建用户，请在您自己的UserRepository中添加专用方法，以在@NativeQuery批注中指定用户创建查询.它应该是开箱即用的.
• 要列出用户，您无需执行任何操作，因为此时您的实体已绑定到正确的表.只需在您的存储库中调用适当的(并且已经存在的)方法即可.

• first, create a User entity and a corresponding UserRepository. Bind the entity to the all_users table. The primary key will probably be either the USERNAME or the USER_ID column... but it doesn't really matter as you won't be doing any insert into that table.
• to create and a user, add a dedicated method to your own UserRepository specifying the user creation query within a @NativeQuery annotation. It should work out-of-the-box.
• to list users you shouldn't need to do anything, as your entity at this point is already bound to the correct table. Just call the appropriate (and already existing) method in your repository.

以上理论上涵盖了使用JPA在给定数据库中创建和列出用户的情况.

The above in theory covers the creation and listing of users in a given database using JPA.

如果此时数据库数量有限(因此，有限数量的知名JNDI数据源)，则可以按照所引用的GitHub示例所示进行操作，方法是为每个不同的DataSource，每个都有相关的(相同的)存储库放在单独的程序包中.

If you have a limited number of databases (and therefore a limited number of well-known JNDI datasources) at this point you can proceed as shown in the GitHub example you referenced, by providing different @Configuration classes for each different DataSource, each with the related (identical) repository living in a separate package.

您当然必须添加一些逻辑，以允许您适当地选择JpaRepository用于操作.

You will of course have to add some logic that will allow you to appropriately select the JpaRepository to use for the operations.

这将导致某些代码重复，并且仅在需求随时间推移变得非常简单的情况下才有效.也就是说:如果您所有的微服务"都需要做的就是创建/列出(甚至删除)用户，并且随着时间的推移，数据源的数量仍然很少，那么这是可行的，因为每个新的数据源都需要您添加新的类，重新编译并重新部署微服务.

This will lead to some code duplication and works well only if the needs remain very simple over time. That is: it works if all your "microservice" will ever have to do is this create/list (and maybe delete) of users and the number of datasources remains small over time, as each new datasource will require you to add new classes, recompile and redeploy the microservice.

或者，尝试使用此处提出的方法: https://www.endpoint.com/blog/2016/11/16/connect-multiple-jpa-repositories-using

Alternatively, try with the approach proposed here: https://www.endpoint.com/blog/2016/11/16/connect-multiple-jpa-repositories-using

但是我个人会把JPA完全丢出窗口，因为除了动态地配置任意DataSource对象并重新配置存储库以使其每次针对不同的DataSource进行工作之外，它都很容易，上述解决方案将迫使您不断进行修改如此简单的应用程序进行维护.

Personally however I would throw JPA out of the window completely as it's anything but easy to dynamically configure arbitrary DataSource objects and reconfigure the repositories to work each time against a different DataSource and the above solution will force you to constant maintenance over such a simple application.

我会坚持使用NamedParameterJdbcTemplate通过使用JndiTemplate对其进行初始化.示例:

What I would do would be sticking with NamedParameterJdbcTemplate initialising it by using JndiTemplate. Example:

void createUser(String username, String password, String database) {
    DataSource ds = (new JndiTemplate()).lookup(database);
    NamedParameterJdbcTemplate npjt = new NamedParameterJdbcTemplate();
    Map<String, Object> params = new HashMap<>();
    params.put("USERNAME", username);
    params.put("PASSWORD", password);
    npjt.execute('create user :USERNAME identified by :PASSWORD', params);
}

List<Map<String, Object>> listUsers() {
    DataSource ds = (new JndiTemplate()).lookup(database);
    NamedParameterJdbcTemplate npjt = new NamedParameterJdbcTemplate();
    return npjt.queryForList("select * from all_users", new HashMap<>());
}

假设您的容器已经定义了JNDI数据源，则上面的代码应涵盖用户的创建和用户列表.无需定义实体或存储库或其他任何东西.您甚至不必在spring @Configuration中定义数据源.上面的代码(您将必须测试)实际上就是您所需要的，因此您可以将其连接到@Controller中并完成它.

Provided that your container has the JNDI datasources already defined, the above code should cover both the creation of a user and the listing of users. No need to define entities or repositories or anything else. You don't even have to define your datasources in a spring @Configuration. The above code (which you will have to test) is really all you need so you could wire it in a @Controller and be done with it.

如果您不使用JNDI，也没有问题:您可以使用HikariCP定义数据源，并提供其他参数作为参数.

If you don't use JNDI it's no problem either: you can use HikariCP to define your datasources, providing the additional arguments as parameters.

无论您拥有多少个不同的数据源，该解决方案都将起作用，除非您真的需要使用其功能，否则无需重新部署.另外，它不需要开发人员知道JPA，也不需要在整个地方散布配置.

This solution will work no matter how many different datasources you have and won't need redeployment unless you really have to work on its features. Plus, it doesn't need the developer to know JPA and it doesn't need to spread the configuration all over the place.

这篇关于使用Springboot在Oracle，MySQL数据库中创建用户-Spring Data JPA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！