dropwizard mysql,使用Dropwizard& JDBI用多个模式查询数据库？

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.

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

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.

Any ideas?

解决方案

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 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()));

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 getAllUsers();

}

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