# Hibernate - Angabe des Schemas



## y0dA (14. Nov 2010)

Hi!

Versuche gerade dass ich mittels Hibernate auf meine Postgres DB zugreife - leider klappt es noch nicht.

1) Habe ich entdeckt dass ich folgenden Eintrag in meiner persistence.xml besitze welcher mir ja jedesmal die Tabellen neu anlegt. Was muss ich machen damit einfach die definierten Tabellen benutzt werden die ich schon per pgAdmin3 erstellt habe? Einfach den folgenden Eintrag aus der persistence.xml löschen bzw. auf validate setzen?

```
<property name="hibernate.hbm2ddl.auto" value="create" /
```

2) Werden zur Zeit die Tabellen unter dem Schema "public" angelegt, jedoch möchte ich ein anderes Schema benutzen (in welchen bereits meine per pgAdmin3 angelegten Tabellen "schlummern") - wie kann ich ein Schema bei Hibernate angeben?
Bei openjpa ging es bspw. so:

```
<property name="openjpa.jdbc.Schema" value="SCHEMA" />
```


----------



## gman (14. Nov 2010)

Zu 1.): Du solltest die Zeile löschen können. Hatte JPA2.0 bisher nur mit Eclipselink, aber ich denke 
Hibernate funktioniert ähnlich.

Zu 2.): Sorry, k.A.


----------



## y0dA (14. Nov 2010)

gman hat gesagt.:


> Zu 1.): Du solltest die Zeile löschen können. Hatte JPA2.0 bisher nur mit Eclipselink, aber ich denke
> Hibernate funktioniert ähnlich.


Hatte ich mir schon gedacht, danke dir.

Zu 2)
Es kann doch nicht sein dass ich mit Hibernate bei jeder Entity bei der Table Annotation den Schemanamen dazuschreiben muss oder?

**EDIT**
Haben nun wie folgt die persistence.xml um "hibernate.default_schema" erweitert:

```
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
	version="2.0">

	<persistence-unit name="mybudget" transaction-type="RESOURCE_LOCAL">
		<properties>
			<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
			<property name="hibernate.show_sql" value="false" />
			<property name="hibernate.format_sql" value="false" />
			<property name="hibernate.use_sql_comments" value="false" />
			<property name="hibernate.connection.autocommit" value="false" />
			<property name="hibernate.cache.use_query_cache" value="false" />
			<property name="hibernate.cache.use_second_level_cache"
				value="false" />
			<!-- <property name="hibernate.hbm2ddl.auto" value="create-drop" /> -->  
			<property name="hibernate.default_schema" value="mybudgetschema"/>
		</properties>

	</persistence-unit>
</persistence>
```
Nur um folgende Exception zu bekommen:

```
javax.faces.el.EvaluationException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:102)
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
	at javax.faces.component.UICommand.broadcast(UICommand.java:315)
	at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:774)
	at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1266)
	at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:82)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
	at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:310)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
	at java.lang.Thread.run(Thread.java:662)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:76)
	at at.mpf.mybudget.dao.facade.impl.PersonFacade.loadAll(PersonFacade.java:30)
	at at.mpf.mybudget.service.impl.PersonService.findAllPersons(PersonService.java:32)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy24.findAllPersons(Unknown Source)
	at at.mpf.mybudget.gui.page.PersonBean.showCustomer(PersonBean.java:29)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:329)
	at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:342)
	at org.jboss.el.parser.AstPropertySuffix.invoke(AstPropertySuffix.java:58)
	at org.jboss.el.parser.AstValue.invoke(AstValue.java:96)
	at org.jboss.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:98)
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
	... 24 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2231)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
	at org.hibernate.loader.Loader.list(Loader.java:2120)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67)
	... 49 more
Caused by: org.postgresql.util.PSQLException: FEHLER: Relation »mybudgetschema.person« existiert nicht
  Position: 101
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
	at org.hibernate.loader.Loader.doQuery(Loader.java:697)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
	at org.hibernate.loader.Loader.doList(Loader.java:2228)
	... 57 more
14.11.2010 18:34:34 com.sun.faces.lifecycle.InvokeApplicationPhase execute
WARNUNG: #{personBean.showCustomer}: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
javax.faces.FacesException: #{personBean.showCustomer}: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:118)
	at javax.faces.component.UICommand.broadcast(UICommand.java:315)
	at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:774)
	at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1266)
	at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:82)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
	at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:310)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
	at java.lang.Thread.run(Thread.java:662)
Caused by: javax.faces.el.EvaluationException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:102)
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
	... 23 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:76)
	at at.mpf.mybudget.dao.facade.impl.PersonFacade.loadAll(PersonFacade.java:30)
	at at.mpf.mybudget.service.impl.PersonService.findAllPersons(PersonService.java:32)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy24.findAllPersons(Unknown Source)
	at at.mpf.mybudget.gui.page.PersonBean.showCustomer(PersonBean.java:29)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:329)
	at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:342)
	at org.jboss.el.parser.AstPropertySuffix.invoke(AstPropertySuffix.java:58)
	at org.jboss.el.parser.AstValue.invoke(AstValue.java:96)
	at org.jboss.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:98)
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
	... 24 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2231)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
	at org.hibernate.loader.Loader.list(Loader.java:2120)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67)
	... 49 more
Caused by: org.postgresql.util.PSQLException: FEHLER: Relation »mybudgetschema.person« existiert nicht
  Position: 101
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
	at org.hibernate.loader.Loader.doQuery(Loader.java:697)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
	at org.hibernate.loader.Loader.doList(Loader.java:2228)
	... 57 more
```

Irritierende Exception da ich bspw. im pgAdmin3 folgende Statement absetzen kann und die Tabelle gefunden wird:

```
SELECT "ID", "FIRSTNAME", "LASTNAME"
  FROM mybudgetschema."PERSON";
```

Warum klappt

```
<property name="hibernate.default_schema" value="mybudgetschema"/>
```
 nicht? Bzw. warum wird das Schema nicht erkannt obwohl es tatsächlich existiert?


----------



## mvitz (14. Nov 2010)

Wenn das hier ( can I specify Oracle database schema in hibernate.cfg.xml ? (Object Relational Mapping forum at JavaRanch) ) stimmt, dann muss man das Schema über die JDBC URL setzen.


----------



## y0dA (15. Nov 2010)

mvitz hat gesagt.:


> Wenn das hier ( can I specify Oracle database schema in hibernate.cfg.xml ? (Object Relational Mapping forum at JavaRanch) ) stimmt, dann muss man das Schema über die JDBC URL setzen.



Und wie soll das aussehen?

Bin ich schon wieder die erste Person mit einem eigenen Schema im Zusammenhang mit Hibernate und Postgres?


----------



## mvitz (15. Nov 2010)

jdbcostgresql://localhost:5432/andreak?schema=myschema

Re: Schema in connection URL

(Ich selber nutze kein Postgres und hab das jetzt auch nur gegoogled.)


----------



## nocturn (27. Jan 2015)

Also bei mir muss gefrickelt werden.


CREATE TABLE "default"."language" (
	"language_id" INT, PRIMARY KEY ("language_id"));

Mit 

@Entity
@Table(name= "language")
@AccessType("property")
@SuppressWarnings({"unused", "serial"})
public class Language{
...
}

Ergab:
  select this_.language_id as language1_3_0_  from default.language_translation
und den Fehler:
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.postgresql.util.PSQLException: FEHLER: Syntaxfehler bei `default'

Also wird Benötigt
c.setProperty(Environment.DEFAULT_SCHEMA, "\"default\"");

Dann klappts. Kurios!


----------

