Michel Zanini opened SPR-9236 and commented

If you have table or column names that are reserved SQL words, like "varchar", "values", "integer", etc... the generated insert SQL must have escaping.

For example, now SimpleJdbcInsert is generating this SQL:

INSERT INTO crud (text, integer, char, datetime, decimal, varchar) VALUES(?, ?, ?, ?, ?, ?)

but it should be generating this:

INSERT INTO `crud` (`text`, `integer`, `char`, `datetime`, `decimal`, `varchar`) VALUES(?, ?, ?, ?, ?, ?)

As a workaround I am able to make it work adding the escape char "`" to the column names like this:

simpleJdbcInsert.withTableName("`" + tableName + "`");
simpleJdbcInsert.usingColumns("`" + column1 + "`", "`" + column2 + "`", "`" + column3 + "`");

but I believe SimpleJdbcInsert should support this out of the box. For example, it could have a boolean property like this:

simpleJdbcInsert.withTableName(tableName);
simpleJdbcInsert.usingColumns(column1, column2, column3);
simpleJdbcInsert.usingEscaping(true);

and then SimpleJdbcInsert would do the escaping for me.

Affects: 3.1.1

4 votes, 6 watchers

Comment From: spring-projects-issues

Michel Zanini commented

Actually, escaping the columns like shown here does NOT work. It is inserting null values for all columns.

Comment From: spring-projects-issues

Chris Beams commented

Added @thomas.risberg as watcher. Thomas, could you comment here?

Comment From: spring-projects-issues

Thomas Risberg commented

That escaping would only work for MySQL. Would be better to add support for quoted identifiers and in that case we would have to make sure we match the case used for the column names.

We could add usingQuotedIdentifiers(true)

and generate something like this:

INSERT INTO "CRUD" ("text", "integer", "char", "datetime", "decimal", "varchar") VALUES(?, ?, ?, ?, ?, ?)

We would need to get the quote character to use from the database metadata --http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getIdentifierQuoteString%28%29

Seems like a worthwhile improvement though.

-Thomas

Comment From: spring-projects-issues

Michel Zanini commented

I agree. Looking forward to have this.

Comment From: spring-projects-issues

Lephix Long commented

Why this issue last so long?

Comment From: spring-projects-issues

reda abdi commented

any workaround?

Comment From: spring-projects-issues

TotoTata commented

Really ? Since 2012 and no corrections ? There are 1000+ MySQL keywords so making column name without it is nearly impossible, so using SimpleJdbcInsert is also impossible. It's not that hard to had ' in created SQL query. I can't understand how a bug like this can still be here.

I'm using this for workaround:

String INSERT_SQL = "insert into CUSTOMER (NAME) values (?)";
KeyHolder key = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

      @Override
      public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        final PreparedStatement ps = connection.prepareStatement(INSERT_SQL, 
            Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, name);
        return ps;
      }
    }, key);
    return key.getKey().longValue();

Comment From: vhrankina

We need the same functionality for PostgreSQL because we use a mix of uppercase and lowercase letters for table names and we need to escape them with double quotes. When I set table name with double quotes like so 

new SimpleJdbcInsert(dataSource)
                .withTableName("\"Table_Name\"");

I see a log message that metadata for table not found.

Comment From: sadafzohra19

It worked for me. What I did is,

String[] columns = {"name", "desc",  "code", "`order`"}

SimpleJdbcInsert insert = new SimpleJdbcInsert(getJdbcTemplate());
insert.withTableName(tableName);
insert.usingColumns(columns);
insert.usingGeneratedKeyColumns("id");

Number id = insert.executeAndReturnKey(parameterSource);

Note that in order to pass parameter for column "order", you need to pass it like:

params.put("`order`", 3);

Comment From: sbrannen

  • superseded by #24013