How to dynamically bind a table name in JDBI

Question

I tried using

SELECT COUNT(*) FROM :TableName;

And in JDBI I used

.bind("Tablename", "MyTable")

The result is always inside single quotes:

SELECT COUNT(*) FROM 'MyTable';

Is there a proper way to parameterise something like TableName?

Fenugreek

Answer 1

bind is not intended for identifiers but values. Table is a database object and its name is an identifier to refer it.

Hence you have to explicitly construct the sql query string to include table name dynamically.

Example:

String tableName = "employee";
String sql = "SELECT COUNT(*) FROM " + tableName;

Then if you want to filter the count or any other results, based on a field value or on an expression, you can bind it.

Example:

sql = sql + " WHERE deptno = :deptNoToBind";
int deptNo = 20;
// ... use db handle to bind
handle.createQuery( sql )
      .bind( "deptNoToBind", deptNo );

You can see that values for columns or expressions are bound but not identifiers.

The feature you are looking for are @Define - here is an example of its usage:

import org.skife.jdbi.v2.sqlobject.customizers.Define;
...
@SqlUpdate("create table if not exists <table> (" +
        "startTime TimeStamp not null," +
        "stopTime TimeStamp not null," +
        "uuid varchar(255)" +
        ")")
public void createTable(@Define("table") String table);

Committal

@UnknownDownVoter: Specific reason for down voting !? – Committal

Haters gonna hate, brow. ¬¬ – Clash

if you use <*> syntax for <table> you need to annotate the interface with @UseStringTemplate3StatementLocator and for this annotation add this dependency to your pom <dependency> <groupId>org.antlr</groupId> <artifactId>stringtemplate</artifactId> <version>3.2</version> </dependency> – Austin

Answer 2

import org.skife.jdbi.v2.sqlobject.customizers.Define;
import org.skife.jdbi.v2.sqlobject.stringtemplate.UseStringTemplate3StatementLocator;

@UseStringTemplate3StatementLocator
public interface CreateTableDAO {

    @SqlUpdate("create table if not exists <table> (" +
        "startTime TimeStamp not null," +
        "stopTime TimeStamp not null," +
        "uuid varchar(255)" +
        ")")
    public void createTable(@Define("table") String table);

}

Don't forget to add the dependency for UseStringTemplate3StatementLocator annotation

<dependency>
    <groupId>org.antlr</groupId>
    <artifactId>stringtemplate</artifactId>
    <version>3.2</version>
</dependency>

Austin