Error changing application from H2 to PostgreSQL

Question

Error changing application from H2 to PostgreSQL

Navigation

#1 by (1 votes)

1

I was developing a benchmarking application H2 just for testing and everything was working.

When I finished the implementation I changed the connection from JPA to PostgreSQL , but when I did this the system stopped persisting the person object, returning me the following error:

2015-05-31 10:57:05,395 DEBUG [org.hibernate.SQL] - <
    call next value for sq_pessoa_coseq>
Hibernate:
    call next value for sq_pessoa_coseq
2015-05-31 10:57:05,396 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - <SQL Error: 0, SQLState: 42601>
2015-05-31 10:57:05,396 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - <ERROR: syntax error at or near "call"
  Posição: 1>

This is the log of startup :

INFORMAÇÕES: Initializing Spring root WebApplicationContext
2015-05-31 10:56:35,390 INFO [org.springframework.web.context.ContextLoader] - <Root WebApplicationContext: initialization started>
2015-05-31 10:56:35,453 INFO [org.springframework.web.context.support.XmlWebApplicationContext] - <Refreshing Root WebApplicationContext: startup date [Sun May 31 10:56:35 BRT 2015]; root of context hierarchy>
2015-05-31 10:56:35,483 INFO [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] - <Loading XML bean definitions from ServletContext resource [/WEB-INF/spring/applicationContext.xml]>
2015-05-31 10:56:35,665 INFO [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] - <Loading XML bean definitions from file [/Users/tpoderoso/Projetos/GitHub/.metadata/.plugins/org.eclipse.wst.server.core/tmp0/wtpwebapps/agenda/WEB-INF/classes/META-INF/spring/applicationContext-db.xml]>
2015-05-31 10:56:35,831 INFO [org.springframework.beans.factory.config.PropertyPlaceholderConfigurer] - <Loading properties file from file [/Users/tpoderoso/Projetos/GitHub/.metadata/.plugins/org.eclipse.wst.server.core/tmp0/wtpwebapps/agenda/WEB-INF/classes/META-INF/spring/database.properties]>
2015-05-31 10:56:35,880 INFO [org.springframework.jdbc.datasource.DriverManagerDataSource] - <Loaded JDBC driver: org.postgresql.Driver>
2015-05-31 10:56:35,929 INFO [org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean] - <Building JPA container EntityManagerFactory for persistence unit 'persistencia'>
2015-05-31 10:56:35,946 INFO [org.hibernate.jpa.internal.util.LogHelper] - <HHH000204: Processing PersistenceUnitInfo [
        name: persistencia
        ...]>
2015-05-31 10:56:36,012 INFO [org.hibernate.Version] - <HHH000412: Hibernate Core {4.3.6.Final}>
2015-05-31 10:56:36,013 INFO [org.hibernate.cfg.Environment] - <HHH000206: hibernate.properties not found>
2015-05-31 10:56:36,014 INFO [org.hibernate.cfg.Environment] - <HHH000021: Bytecode provider name : javassist>
2015-05-31 10:56:36,197 INFO [org.hibernate.annotations.common.Version] - <HCANN000001: Hibernate Commons Annotations {4.0.5.Final}>
2015-05-31 10:56:36,415 INFO [org.hibernate.dialect.Dialect] - <HHH000400: Using dialect: org.hibernate.dialect.H2Dialect>
2015-05-31 10:56:36,429 INFO [org.hibernate.engine.jdbc.internal.LobCreatorBuilder] - <HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException>
2015-05-31 10:56:36,455 WARN [org.hibernate.cfg.AnnotationBinder] - <HHH000138: Mixing inheritance strategy in a entity hierarchy is not allowed, ignoring sub strategy in: br.ufscar.web.core.model.Juridica>
2015-05-31 10:56:36,455 WARN [org.hibernate.cfg.AnnotationBinder] - <HHH000138: Mixing inheritance strategy in a entity hierarchy is not allowed, ignoring sub strategy in: br.ufscar.web.core.model.Fisica>
2015-05-31 10:56:36,536 INFO [org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory] - <HHH000397: Using ASTQueryTranslatorFactory>
2015-05-31 10:56:36,758 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <HHH000228: Running hbm2ddl schema update>
2015-05-31 10:56:36,758 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <HHH000102: Fetching database metadata>
2015-05-31 10:56:36,767 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <HHH000396: Updating schema>
2015-05-31 10:56:36,772 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_endereco>
2015-05-31 10:56:36,775 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_pessoa>
2015-05-31 10:56:36,777 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_pessoa_fisica>
2015-05-31 10:56:36,779 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_pessoa_juridica>
2015-05-31 10:56:36,781 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_endereco>
2015-05-31 10:56:36,783 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_pessoa>
2015-05-31 10:56:36,785 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_pessoa_fisica>
2015-05-31 10:56:36,787 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_pessoa_juridica>
2015-05-31 10:56:36,789 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_endereco>
2015-05-31 10:56:36,791 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_pessoa>
2015-05-31 10:56:36,793 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_pessoa_fisica>
2015-05-31 10:56:36,795 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: tb_pessoa_juridica>
2015-05-31 10:56:36,797 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: sq_endereco_coseq>
2015-05-31 10:56:36,799 INFO [java.sql.DatabaseMetaData] - <HHH000262: Table not found: sq_pessoa_coseq>
2015-05-31 10:56:36,819 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <HHH000232: Schema update complete>
2015-05-31 10:56:37,523 INFO [org.springframework.web.context.ContextLoader] - <Root WebApplicationContext: initialization completed in 2133 ms>
mai 31, 2015 10:56:37 AM com.sun.faces.config.ConfigureListener contextInitialized
INFORMAÇÕES: Inicializando Mojarra 2.1.6 (SNAPSHOT 20111206) para o contexto '/agenda'
mai 31, 2015 10:56:37 AM com.sun.faces.spi.InjectionProviderFactory createInstance
INFORMAÇÕES: JSF1048: Anotações PostConstruct/PreDestroy presentes.  Os métodos ManagedBeans marcados com essas anotações informarão as anotações processadas.
mai 31, 2015 10:56:38 AM org.primefaces.webapp.PostConstructApplicationEventListener processEvent
INFORMAÇÕES: Running on PrimeFaces 5.0
mai 31, 2015 10:56:38 AM com.sun.faces.config.ConfigureListener$WebConfigResourceMonitor$Monitor <init>
INFORMAÇÕES: Monitoring jndi:/localhost/agenda/WEB-INF/faces-config.xml for modifications
mai 31, 2015 10:56:38 AM org.apache.coyote.AbstractProtocol start
INFORMAÇÕES: Starting ProtocolHandler ["http-bio-8080"]
mai 31, 2015 10:56:38 AM org.apache.coyote.AbstractProtocol start
INFORMAÇÕES: Starting ProtocolHandler ["ajp-bio-8009"]
mai 31, 2015 10:56:38 AM org.apache.catalina.startup.Catalina start
INFORMAÇÕES: Server startup in 4226 ms

Could anyone tell me why this is occurring?

EDIT : including configuration files

persistence.xml :

<?xml version="1.0" encoding="UTF-8"?>
<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_1_0.xsd"
        version="1.0">
        <persistence-unit name="persistencia"
                transaction-type="RESOURCE_LOCAL">
                <provider>org.hibernate.ejb.HibernatePersistence</provider>
                <properties>
                        <property name="hibernate.hbm2ddl.auto" value="create-drop" />
                        <property name="hibernate.format_sql" value="true" />
                        <property name="hibernate.show_sql" value="true" />
                </properties>
        </persistence-unit>
</persistence>

database.properties :

h2.driverClassName=org.postgresql.Driver
h2.url=jdbc:postgresql://localhost:5432/Agenda
h2.username=tpoderoso
h2.password=1109tdp

applicationContext-db.xml :

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
        xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx"
        xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
                http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
                http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
                http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

        <context:property-placeholder location="classpath:META-INF/spring/*.properties" />

        <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
                <property name="entityManagerFactory" ref="entityManagerFactory" />
                <property name="dataSource" ref="dataSource" />
        </bean>
        <tx:annotation-driven transaction-manager="transactionManager" />

        <bean id="entityManagerFactory"
                class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
                <property name="dataSource" ref="dataSource" />
                <property name="jpaVendorAdapter">
                        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                                <property name="generateDdl" value="true" />
                                <property name="database" value="H2" />
                        </bean>
                </property>
                <property name="persistenceUnitName" value="persistencia" />
        </bean>

        <bean id="dataSource"
                class="org.springframework.jdbc.datasource.DriverManagerDataSource">
                <property name="driverClassName" value="${h2.driverClassName}" />
                <property name="url" value="${h2.url}" />
                <property name="username" value="${h2.username}" />
                <property name="password" value="${h2.password}" />
        </bean>

        <bean
                class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />
</beans>

hibernate jpa

asked by anonymous 31.05.2015 / 16:06

1 answer

Select Distinct Keep spacing between elements but no spaces between Tags

score 1 · Accepted Answer

As stated in the comments, you are still using the Hibernate dialect for H2 , as shown in the log:

2015-05-31 10:56:36,415 INFO [org.hibernate.dialect.Dialect] - <HHH000400: Using dialect: org.hibernate.dialect.H2Dialect>

It is not necessary to specify the dialect to be used by Hibernate , if you do not specify which dialect to use, it will try to choose the most appropriate dialect according to the connection.

As you specified the H2 dialect it will consider such a dialect for both DDL and DML , and there are differences in language used by H2 and PostgreSQL . You can see this by checking the H2 " and PostgreSQL .

If you want to specify the dialect for PostgreSQL , these are available (considering the latest stable version):

By your setup you are not doing this directly, but you are telling Spring (through the JPA Vendor ) that you are using H2 and it is telling Hibernate the wrong dialect. Look at this line of your applicationContext-db.xml :

<property name="database" value="H2" />

Na documentation you can check which ones are supported by the vendor. So, for the correct dialect to be used, change to this:

<property name="database" value="POSTGRESQL" />

With this Spring will "talk" to Hibernate which dialect it should use, that is, it will now be PostgreSQL .