Special characters in Hibernate column names
I have a table with a column @id
I want to execute this query:
select DISTINCT "@id" from "osm_art_center"
in Java (Hibernate).
The problem is that Hibrernate failed to understand @id
and returns an error so that the id column does not exist.
EDIT
Code causing the error:
String columnName="@id";
String tableName="osm_art_center";
ResultSet polygonSearch;
polygonSearch = st.executeQuery("select DISTINCT "+columnName+" from "+tableName);
The error that was thrown:
SQL Error: org.postgresql.util.PSQLException: ERROR: column "id" does not exist
source to share
Try specifying a column name:
String sql ="select DISTINCT \"@id\" from osm_art_center;
ResultSet polygonSearch = st.executeQuery(sql);
The Java language does not strip or modify the "@" character in your string. It is possible, however, that the JDBC driver you are using behaves differently from your PostgreSQL command interpreter.
Either way, try to elude the special character.
source to share
What I am doing here is that Hibernate does not quote identifiers when passed to the database, so PostgreSQL interprets the unspecified @id
as a statement @
applied to the identifier id
. No space is required before the prefix operator; as you can write a+b
instead a + b
, so you can write @id
or @ id
to apply the operator @
to the column id
.
Demonstration:
test=> CREATE TABLE iddemo("@id" integer);
CREATE TABLE
test=> SELECT @id FROM iddemo;
ERROR: column "id" does not exist
LINE 1: SELECT @id FROM iddemo;
If you provide an ID, it will instead get the expected output:
test=> SELECT "@id" FROM iddemo;
@id
-----
(0 rows)
This is IMO a Hibernate bug. It must list all the identifiers that it passes to the database. Failure to do this means that the user has to manually specify the IDs if they want something in uppercase, containing spaces, using reserved words, etc. It looks like they fixed this, but didn't include a default backward compatibility fix; see Automatic Reserved Word Reservation for Hibernate Tables and Columns .
You can work around this error by inserting a quote in your definitions, although it may have side effects elsewhere.
String columnName="\"@id\"";
or fix it globally in your Hibernate project config by setting hibernate.globally_quoted_identifiers
to true
in your persistence.xml
(or Hibernate config file or whatever).
source to share