I am given a postgre database in which I have a table that has a column called:
addr:city
Since I cant use colons in java classvariables I tried to represent it this way:
@Column(name = "addr:city")
private String addrCity;
Sadly this didn't work, I get the following exception (I am sorry for the german language in the exception text, I don't know why it is this way - translated it means a Syntax error at the : (colon)):
ERROR: FEHLER: Syntaxfehler bei »:« Position: 52 Exception in thread "main" org.hibernate.exception.SQLGrammarException: FEHLER: Syntaxfehler bei »:« Position: 52 at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) at $Proxy12.executeQuery(Unknown Source) at org.hibernate.loader.Loader.getResultSet(Loader.java:1978) at org.hibernate.loader.Loader.doQuery(Loader.java:829) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) at org.hibernate.loader.Loader.doList(Loader.java:2463) at org.hibernate.loader.Loader.doList(Loader.java:2449) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279) at org.hibernate.loader.Loader.list(Loader.java:2274) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) at database.BerlinPolygonDAO.getBerlinLocationByID(BerlinPolygonDAO.java:52) at databaseAccess.Tester.main(Tester.java:33) Caused by: org.postgresql.util.PSQLException: FEHLER: Syntaxfehler bei »:«
Position: 52 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) ... 16 more
I read in some other posts that I should escape it the way below, but it did not work for me (exception that simply says that there is a syntax error with the "")
@Column(name = "addr\\:city")
private String addrCity;
It also didn't help to use two colons:
@Column(name = "addr::city")
private String addrCity;
Then I get an exception, that the column "addr" (so without the colon and the rest) doesn't exist.
Can anyone tell me what I need to do for reading out this column?
I found this related question that deals with the exact same column I'm dealing with now and the problem with the colon, but not in hibernate. I'm thinking if this might help somehow.
I solved my problem.
If there is a colon in a columname in postgresql and you try to read it out with hibernate you need to declare it like this:
@Column(name = "\"addr:city\"")
private String addrCity;