1

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?

TimoStaudinger
  • 41,396
  • 16
  • 88
  • 94
Albert Rannetsperger
  • 794
  • 2
  • 9
  • 19

2 Answers2

5

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

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

}

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

Manikandan
  • 3,025
  • 2
  • 19
  • 28
0

Simplest solution for you would be using multiple:

  @JsonProperty("database")
    public DataSourceFactory getDataSourceFactory() {
        return database;
    }

Configuration so for example:

@JsonProperty("products")
@JsonProperty("clients")

And then configuration file:

products:
  # products schema configuration...
clients:
  # clients configuration
akakus
  • 191
  • 4
  • Thanks for your efforts but that doesn't solve my problem. I only have one AWS MySQL RDMS instance I need to connect to, but within that instance I will have multiple databases all with the same schema. My application needs a way of selecting which schema to query depending on the request. – Albert Rannetsperger May 28 '15 at 21:39
  • Ah sorry... I missed this... Do you know clients upfront, as you could create DataSources for them? If yes - my approach would be still valid (or some other like Spring JDBC). If not, then probably this is a bit more tricky - as I don't think you could rely on Dropwizard out of the box transaction handling, as it requires you to set DataSources on the start of application (as far as I am aware). So in this case I would say you need to implement a bit lower level logic (like connection pooling using c3p0 and some manual basic transaction management - @UnitOfWork won't work in this scenario). – akakus May 28 '15 at 23:10
  • If you have fixed amount of databases, then you can still use this approach, and pass a map of DAOs to your classes which you can choose according to the request. But if it's dynamically changing set of databases, then dropwizard's JDBI module probably won't be flexible enough. You can then use JDBI directly and write your own custom connection pool management OR you could change your database design to have only one but with a specific "identifier" columns in all tables. – Natan May 29 '15 at 15:58