使用Dropwizard& JDBI用多个模式查询数据库？ [英] Using Dropwizard & JDBI to query database with multiple schemas?

问题描述

我正在使用DropWizard（使用JDBI）构建Java Rest API，我的要求是我需要使用相同的应用程序查询多个MySQL模式。它基本上是一个包含多个模式的AWS MySQL实例 - 每个客户端一个模式。

I'm building a Java Rest API with DropWizard (which uses JDBI) and my requirements are that I need to query multiple MySQL schemas with the same application. It'll basically be one AWS MySQL instance housing multiple schemas -- one schema per client.

我需要的是一种机制，它知道要查询哪个模式，具体取决于请求 - IE：请求所属的客户端。

What I need is a mechanism which knows which "schema" to query depending on the request -- IE: which client a request belongs to.

我知道如何创建DataSource，DAO等（使用本教程： https://dropwizard.github.io/dropwizard/manual/jdbi.html ）但不知道如何查询多个模式。

I know how to create a DataSource, DAO, etc (using this tutorial: https://dropwizard.github.io/dropwizard/manual/jdbi.html) but have no idea how to query multiple schemas.

任何想法？

推荐答案

理想方式要做到这一点，请从请求中捕获与架构相关的信息并将其保存在ThreadLocal中，并在请求连接时设置架构。
不幸的是，当我尝试这种方法时，我发现setSchema方法尚未在驱动程序中实现。但我找到了解决这个问题的另一种方法（黑客）。
JDBI提供了语句Locator，我们可以在这里解决这个问题。

Ideal way to do this is, capture the schema related information from request and save it in ThreadLocal and set the schema whenever the connection is requested. Unfortunately when I tried this approach, I found setSchema method is not yet implemented in drivers. But I found another way(hack) to solve this. JDBI provides statement Locator which we can use here to solve this problem.

假设我们在查询参数中发送模式名称，我们可以使用泽西请求过滤器获取模式名称。

Lets say we are sending schema name in query Parameter, we can use jersey request filter to get schema name.

public class Schema {
    public static ThreadLocal<String> name = new ThreadLocal<>();
}


public class SchemaNameFilter implements ContainerRequestFilter {

    @Override
    public ContainerRequest filter(ContainerRequest request) {
        if(request.getQueryParameters().containsKey("schema")) {
            Schema.name.set(request.getQueryParameters().get("schema").get(0));
        }
        return request;
    }
}

这将获得每个请求的模式名称。在您的应用程序引导程序中注册此文件管理器。

This will get the schema name on every request. Register this filer on your application bootstrap.

environment.jersey().property(ResourceConfig.PROPERTY_CONTAINER_REQUEST_FILTERS, asList(new SchemaNameFilter()));

现在我们需要编写第二部分，我们应该使用这个架构信息。包含此SchemaRewriter，

Now we need to write the second part, where we should use this schema information. Include this SchemaRewriter,

public class SchemaReWriter implements StatementLocator {
    @Override
    public String locate(String sql, StatementContext ctx) throws Exception {
        if (nonNull(Schema.name.get())) {
            sql = sql.replaceAll(":schema", Schema.name.get());
        }
        return sql;
    }
}

让我们说要访问用户表在所有模式中，写这样的查询。

Lets say we want to access the table "users" which is in all the schemas, write query like this.

@OverrideStatementLocatorWith(SchemaReWriter.class)
public interface UserDao {

  @SqlQuery("select * from :schema.users")
  public List<User> getAllUsers();

}

不要忘记用StatementRewriter注释Dao。就这样。您无需担心多个模式。

Don't forget to annotate Dao with StatementRewriter. That's all. You don't need to worry about multiple schemas.

这篇关于使用Dropwizard& JDBI用多个模式查询数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！