Spring JdbcTemplate - how to prepend every query for achieving multitenancy?

Question

Setup

I have an app using Spring 4.3, JdbcTemplate, Hibernate 5 and MySQL 8. I have implemented multitenancy in hibernate per schema where i switch schemas using using hibernates multitenancy mechanism - MultiTenantConnectionProvider and doing there basically:

connection.createStatement().execute("USE " + databaseNamePrefix + tenantIdentifier); 

and this works.

Now the reporting part of my app uses JdbcTemplate for querying the DB. And now i want to similarily before every query executed by JdbcTemplate issue this USE tenantIdentifier statement.

Question

How can i prepend some SQL or a statement to every query executed by JdbcTemplate?

What i have tried

I looked into JdbcTemplate and only thing i found is setting a NativeJdbcExtractor. I have tried the code below but it's not even loggin that he is going through this methods.

@Bean
@DependsOn("dataSource")
public JdbcTemplate jdbcTemplate() {
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource());
  jdbcTemplate.setNativeJdbcExtractor(new SimpleNativeJdbcExtractor(){
     @Override
     public Connection getNativeConnection(Connection con) throws SQLException {
        LOGGER.info("getNativeConnection");
        System.out.println("aaa");
        return super.getNativeConnection(con);
     }

     @Override
     public Connection getNativeConnectionFromStatement(Statement stmt) throws SQLException {
        System.out.println("aaa");
        LOGGER.info("getNativeConnectionFromStatement");
        return super.getNativeConnectionFromStatement(stmt);
     }

  });
  return jdbcTemplate;
}

Added feature request to Spring: https://jira.spring.io/browse/SPR-17342

EDIT: i looked at Spring 5 and they removed the JdbcExtractor thing so this is definetly the wrong path.

Answer 1

There won't be an easy way to do this with JdbcTemplate as some methods are very generic e.g. execute(ConnectionCallback<T> action) methods allows direct access to java.sql.Connection object.

It would be easier to have a separate DataSource bean for every tenant and resolve this with qualified auto-wiring in Spring.

Having per-tenant java.sql.Connection will allow to execute USE tenantIdentifier statement when new database connection is opened (some pool libraries support this out the box). As MySQL USE statement docs this can be done once per session:

The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued.

Answer 2

Do not create jdbc template bean. Instead, you can use entity manager factory to create new instance of jdbc template, every time you need to execute a query. This approach worked for me.

public class JdbcQueryTemplate {

    public JdbcTemplate getJdbcTemplate(EntityManagerFactory emf) {
        EntityManagerFactoryInfo info = (EntityManagerFactoryInfo) emf;
        return new JdbcTemplate(info.getDataSource());
    }

    public NamedParameterJdbcTemplate getNamedJdbcTemplate(EntityManagerFactory emf) {
        EntityManagerFactoryInfo info = (EntityManagerFactoryInfo) emf;
        return new NamedParameterJdbcTemplate(info.getDataSource());
    }
}

And then use the class for querying.

public class Test{

  @Autowired
  private EntityManagerFactory entityManagerFactory;

  public List<Entity> executeQuery() {
      return new JdbcQueryTemplate().getNamedJdbcTemplate(entityManagerFactory)
              .query("query", new BeanPropertyRowMapper<>(Entity.class));
  }
}