Table Name Troubles With Hibernate Named Query

Question

Hi all,

I've got the following named query in Hibernate:

<sql-query name="CreateLogTable">
  CREATE TABLE IF NOT EXISTS :tableName (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
    `url` VARCHAR (750) NOT NULL, 
    `query` VARCHAR (500), 
    `ipaddress` VARCHAR (39), 
    `datetime` DATETIME NOT NULL, 
    `hits` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL, 
    `path_id` VARCHAR (8), 
    PRIMARY KEY(`id`),
    UNIQUE(`id`),
    INDEX(`id`,`query`,`datetime`,`path_id`)
  ) TYPE = MyISAM
</sql-query>

I am then trying to execute this line using the following line (constants point to correct items):

getSession().getNamedQuery(CREATE_SQL).setString(CREATE_SQL_TABLE_NAME_PARAM, "wibble").executeUpdate();

Now when I execute the code, I get an SQL error regarding the table name which is coming out surrounded by quotes:

CREATE TABLE IF NOT EXISTS 'wibble' (

If I take the generated query and try it without the quotes around the table name, the query works fine. It's just those quotes causing my issue.

So my question is: What do I need to use instead of setString to make sure that my parameter isn't surrounded by quotes? I've tried setParameter with no luck and I couldn't spot any better alternative from the API.

Thanks for any input, Lee

Answer 1

What database type are you using? Some databases are case sensitive (Actually most are except Microsoft's). So the table named 'wibble' is different from 'WiBbLe' and still different from 'WIBBLE'.

When you create a table named wibble with no quotes, most databases such as Oracle create this as the table named in all caps 'WIBBLE'. When you query it you use it as you do here:

SELECT * FROM wibble

The database would interpret this as 'WIBBLE' and there is no problem.

The persistence provider seems to always put this in quotes. To make the quoted name the same as it was created as, you should capitalize it. So try this:

getSession().getNamedQuery(CREATE_SQL).setString(CREATE_SQL_TABLE_NAME_PARAM, "WIBBLE").executeUpdate();

That should create the statement with 'WIBBLE' which should be the same as wibble with no quotes.

Answer 2

I don't think you can use named parameters for database identifiers like table names. Only value expressions. If it were allowed, it would make you susceptible to SQL injection attacks. As an alternative, you could construct the query in Java.