CODEWITHSUNDEEP
springpostgresqlhibernatemavenwildfly
Morphée
Morphée

Reputation: 35

Hibernate not creating table postgreSQL

With 2 other students, we are working on a classic JEE project : - We use Spring + Hibernate + Maven - PostgreSQL database (Code first) - WildFly 10.x

We did a test to insert a table in the database, following a tutorial

Our problem is that Hibernate is not creating the table in the database (ERROR: relation "phone" does not exist). Mainly, this problem can occurs for 2 reasons : Incorrect JDBC or lacks of privileges for the database user. So, we checked the first one and granted all privileges to our user on database/schema. We figured out hibernate was generating our select queries (Hibernate: select phone0_.id as id1_0_, phone0_.name as name2_0_, phone0_.review as review3_0_ from PHONE phone0_), but not the create table query.

Console output ("ERREUR: la relation « phone » n'existe pas" means "ERROR : Relation "phone" does not exists"): 

15:01:50,099 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("add") failed - address: ([
    ("subsystem" => "datasources"),
    ("data-source" => "PostgresDS")
]) - failure description: {
    "WFLYCTL0412: Required services that are not installed:" => ["jboss.jdbc-driver.SOVECO-0_0_1-SNAPSHOT_war_org_postgresql_Driver_9_4"],
    "WFLYCTL0180: Services with missing/unavailable dependencies" => [
        "jboss.driver-demander.java:/PostgresDS is missing [jboss.jdbc-driver.SOVECO-0_0_1-SNAPSHOT_war_org_postgresql_Driver_9_4]",
        "org.wildfly.data-source.PostgresDS is missing [jboss.jdbc-driver.SOVECO-0_0_1-SNAPSHOT_war_org_postgresql_Driver_9_4]"
    ]
}
15:01:50,105 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("add") failed - address: ([
    ("subsystem" => "datasources"),
    ("data-source" => "PostgresDS")
]) - failure description: {
    "WFLYCTL0412: Required services that are not installed:" => [
        "jboss.jdbc-driver.SOVECO-0_0_1-SNAPSHOT_war_org_postgresql_Driver_9_4",
        "jboss.jdbc-driver.SOVECO-0_0_1-SNAPSHOT_war_org_postgresql_Driver_9_4"
    ],
    "WFLYCTL0180: Services with missing/unavailable dependencies" => [
        "jboss.driver-demander.java:/PostgresDS is missing [jboss.jdbc-driver.SOVECO-0_0_1-SNAPSHOT_war_org_postgresql_Driver_9_4]",
        "org.wildfly.data-source.PostgresDS is missing [jboss.jdbc-driver.SOVECO-0_0_1-SNAPSHOT_war_org_postgresql_Driver_9_4]",
        "org.wildfly.data-source.PostgresDS is missing [jboss.jdbc-driver.SOVECO-0_0_1-SNAPSHOT_war_org_postgresql_Driver_9_4]"
    ]
}
15:01:50,207 INFO  [org.jboss.as.controller] (Controller Boot Thread) WFLYCTL0183: Service status report
WFLYCTL0184:    New missing/unsatisfied dependencies:
      service jboss.jdbc-driver.SOVECO-0_0_1-SNAPSHOT_war_org_postgresql_Driver_9_4 (missing) dependents: [service jboss.driver-demander.java:/PostgresDS, service org.wildfly.data-source.PostgresDS] 

15:01:50,388 ERROR [org.jboss.as] (Controller Boot Thread) WFLYSRV0026: WildFly Full 10.1.0.Final (WildFly Core 2.2.0.Final) started (with errors) in 7071ms - Started 332 of 583 services (4 services failed or missing dependencies, 394 services are lazy, passive or on-demand)

WFLYCTL0185:    Newly corrected services:
      service jboss.jdbc-driver.SOVECO-0_0_1-SNAPSHOT_war_org_postgresql_Driver_9_4 (new available)
15:02:42,941 INFO  [stdout] (default task-5) Hibernate: select phone0_.id as id1_0_, phone0_.name as name2_0_, phone0_.review as review3_0_ from PHONE phone0_
15:02:42,949 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-5) ERREUR: la relation « phone » n'existe pas
  Position : 90
15:02:42,957 ERROR [io.undertow.request] (default task-5) UT005023: Exception handling request to /SOVECO/phones: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
...
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
...
Caused by: org.postgresql.util.PSQLException: ERREUR: la relation « phone » n'existe pas
  Position : 90
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:71)
    ... 93 more
15:08:23,927 INFO  [stdout] (default task-6) Hibernate: select phone0_.id as id1_0_, phone0_.name as name2_0_, phone0_.review as review3_0_ from PHONE phone0_

15:08:23,929 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-6) SQL Error: 0, SQLState: 42P01
15:08:23,929 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-6) ERREUR: la relation « phone » n'existe pas
  Position : 90
15:08:23,931 ERROR [io.undertow.request] (default task-6) UT005023: Exception handling request to /SOVECO/phones: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
...
15:08:41,752 INFO  [stdout] (default task-7) Hibernate: select phone0_.id as id1_0_, phone0_.name as name2_0_, phone0_.review as review3_0_ from PHONE phone0_
15:08:41,756 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-7) SQL Error: 0, SQLState: 42P01
15:08:41,756 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-7) ERREUR: la relation « phone » n'existe pas
  Position : 90
15:08:41,757 ERROR [io.undertow.request] (default task-7) UT005023: Exception handling request to /SOVECO/phones: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.postgresql.util.PSQLException: ERREUR: la relation « phone » n'existe pas

PhoneDAO :

import java.util.List;
import fr.spring.demo.model.Phone;

public interface PhoneDAO {
    public void addPhone(Phone p);

    public void updatePhone(Phone p);

    public List<Phone> listPhones();

    public Phone getPhoneById(int id);

    public void removePhone(int id);
}

Class PhoneDAOImpl :

import java.io.Serializable;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import fr.spring.demo.model.Phone;

@Repository
public class PhoneDAOImpl implements Serializable, PhoneDAO {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private static final Logger logger = LoggerFactory.getLogger(PhoneDAOImpl.class);
    private SessionFactory sessionFactory;

    public void setSessionFactory(SessionFactory sf) {
        this.sessionFactory = sf;
    }

    @Override
    public void addPhone(Phone p) {
        Session session = this.sessionFactory.getCurrentSession();
        session.persist(p);
        logger.info("Phone saved successfully, Phone Details=" + p);
    }

    @Override
    public void updatePhone(Phone p) {
        Session session = this.sessionFactory.getCurrentSession();
        session.update(p);
        logger.info("Phone updated successfully, Phone Details=" + p);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Phone> listPhones() {
        Session session = this.sessionFactory.getCurrentSession();
        List<Phone> phonesList = session.createQuery("from Phone").list();
        for (Phone p : phonesList) {
            logger.info("Phone List::" + p);
        }
        return phonesList;
    }

    @Override
    public Phone getPhoneById(int id) {
        Session session = this.sessionFactory.getCurrentSession();
        Phone p = (Phone) session.load(Phone.class, new Integer(id));
        logger.info("Phone loaded successfully, Phone details=" + p);
        return p;
    }

    @Override
    public void removePhone(int id) {
        Session session = this.sessionFactory.getCurrentSession();
        Phone p = (Phone) session.load(Phone.class, new Integer(id));
        if (null != p) {
            session.delete(p);
        }
        logger.info("Phone deleted successfully, phone details=" + p);
    }
}

PhoneController : 

import java.io.Serializable;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import fr.spring.demo.model.Phone;
import fr.spring.demo.service.PhoneService;

@Controller
public class PhoneController implements Serializable{
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private PhoneService phoneService;

    @Autowired(required = true)
    @Qualifier(value = "phoneService")
    public void setPhoneService(PhoneService ps) {
        this.phoneService = ps;
    }

    @RequestMapping(value = "/phones", method = RequestMethod.GET)
    public String listPhones(Model model) {
        model.addAttribute("phone", new Phone());
        model.addAttribute("listPhones", this.phoneService.listPhones());
        return "phone";
    }

    // For add and update phone both
    @RequestMapping(value = "/phone/add", method = RequestMethod.POST)
    public String addPhone(@ModelAttribute("phone") Phone p) {
        if (p.getId() == 0) {
            // new phone, add it
            this.phoneService.addPhone(p);
        } else {
            // existing phone, call update
            this.phoneService.updatePhone(p);
        }
        return "redirect:/phones";
    }

    @RequestMapping("/remove/{id}")
    public String removePhone(@PathVariable("id") int id) {
        this.phoneService.removePhone(id);
        return "redirect:/phones";
    }

    @RequestMapping("/edit/{id}")
    public String editPhone(@PathVariable("id") int id, Model model) {
        model.addAttribute("phone", this.phoneService.getPhoneById(id));
        model.addAttribute("listPhones", this.phoneService.listPhones());
        return "phone";
    }
}

PostgreSQL log file : 

2017-05-24 11:22:05 CEST ERREUR:  la colonne « procpid » n'existe pas au caractère 16
2017-05-24 11:22:05 CEST INSTRUCTION :  SELECT datname,procpid,current_query FROM pg_stat_activity;
2017-05-24 11:22:26 CEST ERREUR:  la colonne « procpid » n'existe pas au caractère 16
2017-05-24 11:22:26 CEST INSTRUCTION :  SELECT datname,procpid,current_query FROM pg_stat_activity;
2017-05-24 11:27:15 CEST ERREUR:  la relation « phone » n'existe pas au caractère 90
2017-05-24 11:27:15 CEST INSTRUCTION :  select phone0_.id as id1_0_, phone0_.name as name2_0_, phone0_.review as review3_0_ from PHONE phone0_
2017-05-24 11:55:20 CEST ERREUR:  la relation « phone » n'existe pas au caractère 90
2017-05-24 11:55:20 CEST INSTRUCTION :  select phone0_.id as id1_0_, phone0_.name as name2_0_, phone0_.review as review3_0_ from PHONE phone0_
2017-05-24 11:59:37 CEST ERREUR:  la relation « phone » n'existe pas au caractère 90
2017-05-24 11:59:37 CEST INSTRUCTION :  select phone0_.id as id1_0_, phone0_.name as name2_0_, phone0_.review as review3_0_ from PHONE phone0_
2017-05-24 11:59:42 CEST ERREUR:  la relation « phone » n'existe pas au caractère 90
2017-05-24 11:59:42 CEST INSTRUCTION :  select phone0_.id as id1_0_, phone0_.name as name2_0_, phone0_.review as review3_0_ from PHONE phone0_
2017-05-24 12:08:00 CEST ERREUR:  la relation « phone » n'existe pas au caractère 90
2017-05-24 12:08:00 CEST INSTRUCTION :  select phone0_.id as id1_0_, phone0_.name as name2_0_, phone0_.review as review3_0_ from PHONE phone0_
2017-05-24 12:16:10 CEST ERREUR:  la relation « phone » n'existe pas au caractère 90
2017-05-24 12:16:10 CEST INSTRUCTION :  select phone0_.id as id1_0_, phone0_.name as name2_0_, phone0_.review as review3_0_ from PHONE phone0_

AppServlet-servlet.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:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans 
http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/aop 
http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
    http://www.springframework.org/schema/context 
http://www.springframework.org/schema/context/spring-context-4.3.xsd
    http://www.springframework.org/schema/tx 
http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

<context:component-scan
    base-package="fr.spring.demo.controller,fr.spring.demo.dao,fr.spring.demo.model,fr.spring.demo.service" />

<context:annotation-config />

<!-- Resolves views selected for rendering by @Controllers t o .jsp resources 
    in the /WEB-INF/views directory -->
<bean
    class="org.springframework.web.servlet.view.InternalResourceViewResolver">
    <property name="prefix" value="/" />
    <property name="suffix" value=".jsp" />
</bean>

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.postgresql.Driver" />
    <property name="url" value="jdbc:postgresql://localhost:5432/soveco" />
    <property name="username" value="soveco" />
    <property name="password" value="soveco" />
</bean>

<!-- Hibernate 4 SessionFactory Bean definition -->
<bean id="hibernate5AnnotatedSessionFactory"
    class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="annotatedClasses">
        <list>
            <value>
                fr.spring.demo.model.Phone
            </value>
        </list>
    </property>
    <property name="hibernateProperties">
        <props>     
            <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hbm2ddl.auto">create</prop>
        </props>
    </property>
</bean>

<tx:annotation-driven transaction-manager="transactionManager" />

<bean id="phoneDAO" class="fr.spring.demo.dao.PhoneDAOImpl">
    <property name="sessionFactory" ref="hibernate5AnnotatedSessionFactory" />
</bean>
<bean id="phoneService" class="fr.spring.demo.service.PhoneServiceImpl">
    <property name="phoneDAO" ref="phoneDAO">
    </property>
</bean>

<bean id="transactionManager" class="org.springframework.orm.hibernate5.HibernateTransactionManager">
    <property name="sessionFactory"
        ref="hibernate5AnnotatedSessionFactory" />
</bean>
<bean
    class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor"></bean>

pom.xml : 

<?xml version="1.0"?>
<project xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <modelVersion>4.0.0</modelVersion>
  <groupId>SOVECO</groupId>
  <artifactId>SOVECO</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  <properties>
    <java.version>1.8</java.version>
    <servlet.version>3.1.0</servlet.version>
    <primefaces.version>5.3</primefaces.version>
    <server.config>standalone.xml</server.config>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <jsf.version>2.2.12</jsf.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>com.sun.faces</groupId>
      <artifactId>jsf-api</artifactId>
      <version>2.2.6</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>com.sun.faces</groupId>
      <artifactId>jsf-impl</artifactId>
      <version>2.2.12</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.primefaces</groupId>
      <artifactId>primefaces</artifactId>
      <version>6.0</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-core</artifactId>
      <version>5.2.10.Final</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-entitymanager</artifactId>
      <version>5.2.10.Final</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
      <version>3.4</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjrt</artifactId>
      <version>1.8.9</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>javax.inject</groupId>
      <artifactId>javax.inject</artifactId>
      <version>1</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>javax.servlet.jsp-api</artifactId>
      <version>2.3.1</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>9.4.1212</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>4.3.7.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>4.3.7.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>4.3.7.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-orm</artifactId>
      <version>4.3.7.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.21</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>jcl-over-slf4j</artifactId>
      <version>1.7.21</version>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.7.21</version>
      <scope>runtime</scope>
    </dependency>
  </dependencies>
  <repositories>
    <repository>
      <snapshots>
        <enabled>false</enabled>
      </snapshots>
      <id>central</id>
      <name>Central Repository</name>
      <url>https://repo.maven.apache.org/maven2</url>
    </repository>
  </repositories>
  <pluginRepositories>
    <pluginRepository>
      <releases>
        <updatePolicy>never</updatePolicy>
      </releases>
      <snapshots>
        <enabled>false</enabled>
      </snapshots>
      <id>central</id>
      <name>Central Repository</name>
      <url>https://repo.maven.apache.org/maven2</url>
    </pluginRepository>
  </pluginRepositories>
  <build>
  ...

Upvotes: 2

Views: 3992

Answers (1)

Praneeth Ramesh
Praneeth Ramesh

Reputation: 3564

Try using hibernate.hbm2ddl.auto=create-drop in your config. This will drop and recreate your database on everytime. If you have already created and then changed the hibernate Entities this would take care of that.

Upvotes: 2

Related Questions