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

Question

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?

Answer 1

Have a look at

DatabaseMetaData.getIdentifierQuoteString()

I never used it but it sounds good :-)

getExtraNameCharacters() could also be of some help

Answer 2

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