Error trying to save to Postgresql with Spring & Hibernate

Question

I have a spring-boot 1.2.1 application that uses spring-data-jpa, liquibase and postgresql. I have two entities that I am trying to persist, and I am getting an error that doesnt' seem to make sense (to me at least).

Here is my liquibase definition:

<createTable tableName="ROLE">
     <column name="role_id" type="bigint" autoIncrement="true">
           <constraints primaryKey="true"/>
     </column>
      <column name="role_type" type="varchar(100)"/>
      <column name="role_description" type="varchar(300)"/>
  </createTable>

  <createTable tableName="ROLEMENU">
      <column name="role_menu_id" type="bigint" autoIncrement="true">
           <constraints primaryKey="true"/>
       </column>
       <column name="role_id" type="bigint"/>
       <column name="display_text" type="varchar(500)"/>
       <column name="display_order" type="int"/>
   </createTable>

and the entities:

@Entity
@Table(name = "ROLE")
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long roleId;

    @Enumerated(EnumType.STRING)
    @Column(name = "roleType", nullable = false)
    private RoleType roleType;

    private String roleDescription;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "role", fetch = FetchType.EAGER)
    @Fetch(value = FetchMode.SUBSELECT)
    private List<RoleMenu> roleMenuList = new ArrayList<RoleMenu>();
//getters and setters
}

and

@Entity
@Table(name = "ROLEMENU")
public class RoleMenu {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long roleMenuId;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "roleMenu", fetch = FetchType.EAGER)
    private List<RoleMenuItem> roleMenuItems = new ArrayList<RoleMenuItem>();

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "roleId")
    private Role role;

    private String displayText;
    private int displayOrder;
    //getters and setters
    }

I use a seeder to populate the database:

@Component
public class RoleSeeder {
@Autowired
private RoleRepository roleRepository;

public List<Role> seed() {
    List<Role> roles = new ArrayList<Role>();
    roles.add( RoleFactory.buildRole( RoleType.OWNER ) );
    roles.add( RoleFactory.buildRole( RoleType.VISITOR ) );
    roleRepository.save( roles );
    return roles;
}

which uses this to create:

private static Role buildOwner() {
        Role owner = new Role();
        owner.setRoleDescription( OWNER_DESCRIPTION );
        owner.setRoleType( RoleType.OWNER );
        return owner;
    }

When the CRUDRepository tries to save I get the following exception:

2015-02-12 08:09:32.422 DEBUG 10089 --- [           main] org.hibernate.SQL                        : insert into role (role_description, role_type) values (?, ?)
Hibernate: insert into role (role_description, role_type) values (?, ?)
2015-02-12 08:09:32.435 DEBUG 10089 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : could not execute statement [n/a]

org.postgresql.util.PSQLException: ERROR: relation "role" does not exist
  Position: 13

This works quite happily on H2, but I am not sure what differences PostgreSQL is expecting me to account for.

One thing that is concerning is that if I have my spring.datasource properties set, Liquibase does not get invoked. If I comment them out, the default datasource seems to be created and then the java code executed. here are the values I am using for my Postgresql:

spring.datasource.url=jdbc:postgresql://localhost:5432/myschema
spring.datasource.username=user
spring.datasource.password=password
spring.datasource.driverClassName=org.postgresql.Driver
spring.jpa.database=POSTGRESQL

Maybe your obfuscation layer (aka ORM) created the tables with UPPERCASE using quoted identifiers? Did you check the tables in the database how they are created? — user330315
– user330315, Commented Feb 12, 2015 at 15:30
Have you checked if the table exists? Have you executed the liquibase migration before executing the test? — JB Nizet
– JB Nizet, Commented Feb 12, 2015 at 15:37
The seeder happens at application startup, so liquibase is supposed to create them. However, when I look in pgAdmin, the tables are not there. I think this is because the startup fails and the tables are not committed. But I am fairly new to Liquibase so I am not sure I am right about that. — sonoerin
– sonoerin, Commented Feb 12, 2015 at 15:44
I have never used the seeder, but is autoIncrement supported in PostgreSQL? Shouldn't it log error messages if it fails? — JB Nizet
– JB Nizet, Commented Feb 12, 2015 at 16:18
The code you provided for the seeder doesn't use Liquibase at all. So, when is the liquibase migration executed? — JB Nizet
– JB Nizet, Commented Feb 12, 2015 at 16:29

sonoerin · Accepted Answer · 2015-02-12 17:02:04Z

1

Thanks to the good feedback I was able to realize that I forgot to add spring.jpa.hibernate.ddl-auto=create-drop to my application.properties. After re-reading the Spring-boot documentation, I see that Spring-boot will auto create your schema if you are using in-memory database. Otherwise you need to tell it to create the tables.

answered Feb 12, 2015 at 17:02

sonoerin

5,18527 gold badges81 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error trying to save to Postgresql with Spring & Hibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related