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

查看:55
本文介绍了使用 Dropwizard &JDBI 查询具有多个模式的数据库?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用 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.

我知道如何创建数据源、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 方法.但是我找到了另一种方法(hack)来解决这个问题.JDBI 提供了语句定位器,我们可以在这里使用它来解决这个问题.

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.

假设我们在查询参数中发送模式名称,我们可以使用 jersey 请求过滤器来获取模式名称.

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;
    }
}

假设我们要访问所有模式中的users"表,像这样编写查询.

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 &amp;amp;JDBI 查询具有多个模式的数据库?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆