CODEWITHSUNDEEP
javajdbc
aditsu quit because SE is EVIL
aditsu quit because SE is EVIL

Reputation: 4089

How to quote/escape identifiers such as column names with JDBC?

Different database servers use different ways to quote and escape identifiers.

E.g. "foo bar" vs `foo bar` vs [foo bar], or "10""" vs "10\"", or identifiers such as FooBar or array need to be quoted for some databases but not for others.

Is there any API method that performs the quoting/escaping correctly for a given database connection? Or any alternative solution?

Upvotes: 13

Views: 9381

Answers (3)

OrangeDog
OrangeDog

Reputation: 38777

Since Java 9, the Statement interface provides various methods for engine-specific quoting:

  • enquoteIdentifier for SQL identifiers (e.g. schema, table, column names)
  • enquoteLiteral for string literals (e.g. char, varchar, text literals)
  • enquoteNCharLiteral for National Character Set literals
Statement stmt = connection.createStatement();
String query = String.format(
        "SELECT id FROM %s WHERE name = %s",
        stmt.enquoteIdentifier("table", false),
        stmt.enquoteLiteral("it's"));
ResultSet resultSet = stmt.executeQuery(query);

However, whenever possible (i.e. for values in data queries), use prepared statements instead.

Statement stmtFormat = connection.createStatement();
String query = String.format(
        "SELECT id FROM %s WHERE name = ?", 
        stmtFormat.enquoteIdentifier("table", false);
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, "it's");
ResultSet resultSet = stmt.executeQuery();

Upvotes: 13

Yishai
Yishai

Reputation: 91871

I think the answer to your question is that if you are writing a database neutral application using JDBC, then you need to use database neutral names, and not things that require special escaping per database vendor.

There is nothing I know of in the JDBC which supports that. A ORM product will deal with such things.

Edit: If you are writing an ORM, then I would think need a seperate SQL generation class for each supported database, just to handle the various syntax involved, so you would have to write that. You can certainly look at the source code of the various open source ORM's out there and see how they handle it.

Upvotes: 0

user85421
user85421

Reputation: 29680

Have a look at 

DatabaseMetaData.getIdentifierQuoteString()

I never used it but it sounds good :-)

getExtraNameCharacters() could also be of some help

Upvotes: 14

Related Questions