3

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.

Daniel Widdis
  • 8,424
  • 13
  • 41
  • 63
Waylander
  • 825
  • 2
  • 12
  • 34
  • Did you try with just one backslash? – cabbagery May 16 '13 at 21:34
  • One backslash gives me a compilererror with "Invalid escape sequence (valid ones are \b \t \n \f \r \" \' \\ )". Thanks for your comment. – Waylander May 16 '13 at 21:35
  • 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 May 16 '13 at 21:43
  • 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 May 16 '13 at 22:08
  • 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](http://docs.oracle.com/javaee/5/api/javax/persistence/Column.html) 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 May 16 '13 at 22:23
  • You were almost(!) right ;) Check my answer below and you see that you need to add extra escaped quotations at the right places. And actually your guesses were really inspiring me as well. Thanks for your time. – Waylander May 16 '13 at 22:45

1 Answers1

1

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;
Waylander
  • 825
  • 2
  • 12
  • 34