How to bind table name in JDBI

Question

I try to use " SELECT COUNT(*) FROM :TableName; ", then in java I use .bind("Tablename","MyTable") .

But the result is always inside single quotes, which is " SELECT COUNT(*) FROM 'MyTable'; ", is there a way to link (maybe I'm using the wrong word) something like TableName in a .sql file?

Answer 1

bind not for identifiers, but values. Table is a database object, and its name is the identifier for its reference.

Therefore, you must explicitly build the sql query string to dynamically include the table name.

An example :

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

Then, if you want to filter the score or any other results based on the value of a field or expression, you can link it.

An example :

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

You can see that the values ​​for columns or expressions are related, but not identifiers.

The function you are looking for is @Define - here is an example of its use:

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

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

Remember to add a dependency for UseStringTemplate3StatementLocator annotation

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