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?

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