spring oracle mysql,使用Springboot在Oracle，MySQL数据库中创建用户-Spring Data JPA

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 - Help in architecturing Springboot-Spring Data JPA application configured with multiple data sources to run queries of the format : create user identified by password

解决方案

I'll be making some assumptions here:

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.

Method 1: using JPA

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.

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

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.

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.

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.

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 params = new HashMap<>();

params.put("USERNAME", username);

params.put("PASSWORD", password);

npjt.execute('create user :USERNAME identified by :PASSWORD', params);

}

List> listUsers() {

DataSource ds = (new JndiTemplate()).lookup(database);

NamedParameterJdbcTemplate npjt = new NamedParameterJdbcTemplate();

return npjt.queryForList("select * from all_users", new HashMap<>());

}

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.

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.

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.