How to read out a column with a colon (:) in its name in hibernate and postgresql

3
votes

sorry for this maybe being a very easy question, I searched and didn't find anything about my specific case.

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? Thanks a lot for every thought and idea you're sharing with me.

EDIT: 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.

1
javahibernateannotationssyntax-errorcolon
Did you try with just one backslash? – cabbagery
One backslash gives me a compilererror with "Invalid escape sequence (valid ones are \b \t \n \f \r \" \' \\ )". Thanks for your comment. – Waylander
I'd build the name parameter as a String, and reference the String in the annotation. If that doesn't work, I'd try to reference the colon as a char, using single-quotes at first, and then referencing a char colon = ':'; field. (Obviously, I'm just guessing, but I have Hibernate stuff to look forward to in the next couple weeks, and I've never used it... :\ ) – cabbagery
Thanks for your comment! I tried this, but sadly it gives me a "org.postgresql.util.PSQLException: FEHLER: Syntaxfehler bei »\«" exception ("Error: Syntaxerror at »\«"), the same as when I try to use two backslashes. – Waylander
Hmm. Well, I cannot test with Hibernate, but other annotations do accept a colon in the string (@RequestMapping(value="test:test") works fine), and there's no indication from here that the @Column annotation behaves particularly differently, so I'd wager it's Hibernate's SQL translation (HSQL). I just checked your edit, though, and I wonder if you tried @Column(name="'addr:city'"), if it might work. (Also, I'll leave you be -- my guesses are probably unhelpful.) – cabbagery

1 Answers

1
votes

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;