Error switching H2 application to Postgresql

Asked

Viewed 390 times

1

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

At the end of the implementation I changed the connection to JPA to the PostgreSQL, only 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

Would someone like to tell me why this is happening?

EDITION: 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

  • By this log is still using the dialect of H2. Have to update with your persistence.xml and the application context spring, to see the settings?

  • Hello Bruno, thank you so much for your help! Follow persistence.xml (http://pastebin.com/rpYhtETj) and applicationContext (http://pastebin.com/ivRz22gq) and my database.properties (http://pastebin.com/G72MH1Ms)

  • See if the included answer helps you Thiago

1 answer

1


As stated in the comments is still using the dialect Hibernate to the H2, how the log shows:

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 one to use, it will try to choose the most appropriate dialect according to the connection.

How you specified the dialect of H2 he will consider such dialect both to DDL how much to DML and there are differences from language used by H2 and by PostgreSQL. You can see this by checking the source of the dialect of H2 and of PostgreSQL.

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

By your setup you’re not doing this directly, but you’re telling Spring (through JPA Vendor) who is using the H2 and he’s informing the Hibernate the wrong dialect. See this line of your applicationContext-db.xml:

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

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

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

With this Spring will "speak" to Hibernate which dialect it should use, ie now will be the PostgreSQL.

  • Hello Bruno! I made the modification in the applicationContext that you commented on, staying that way (http://pastebin.com/e8GgBSD6), but it is still returning error (http://pastebin.com/YhnysBWH) Again, I thank you for your help!

  • @Thiago in this log has no error and is now using the correct dialect (org.hibernate.dialect.PostgreSQLDialect). Try to delete your bank and create another one, to the Hibernate create the "zero" tables using the new dialect.

  • Yeah, I recreated the bank, but it still doesn’t persist the object.

  • @Thiago and gives error? What happens after he calls nextval of sequences ? The way it is configured it has to generate the DML of Insert.

  • So he just gives me this output (http://pastebin.com/HcWwbH5X) and does not persist

  • @Thiago only with this is impossible to help, is not mistake now, is not persisting because not given flush, so it’s still in "memory". Updates your question with the method you use to persist the object, but there is no error now, it has nothing to do with your initial error.

  • @Thiago is not recommended, but since you can’t do everything in one question, add this to your persistence.xml: <property name="hibernate.connection.autocommit" value="true" />. I suggest we take a closer look at how it works JPA also.

  • Jewel Bruno, I’ll try here, it must be something I left behind! For sure after stirring so much the code I must have done something wrong, I will pull again the project and do here. The question you raised already solves the first problem! Thank you very much :)

  • @Thiago Yes, if it’s another post mistake too, can help other people =)

  • 1

    Bruno! It was my own mistake, I did not mark the field as mandatory and it was mandatory.... Again, thank you for the quick help!!

  • @Thiago legal =)

Show 6 more comments

Browser other questions tagged hibernate jpa

You are not signed in. Login or sign up in order to post.