2

SOLVED On the server, the tables had been created with the default database instead of mine using pgsql. Now I need to check if there is a way to precise the database in an SQL script, or to set up a default database for my specific user.

I am having a weird problem with the queries in my Spring Boot web app. It is a maven java webapp running on Tomcat, using JPA and criterias, with a Postgres DB. The app is working fine on my Windows computer, with a local Postgres 11 and Tomcat 9.

I have the same on a debian server (Postgres, Tomcat), but when I am deploying the war on the Tomcat, SQL queries are not working :

Caused by: org.postgresql.util.PSQLException: ERROR: relation "my_schema.reseau" does not exist
  Position: 62
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
        ... 134 common frames omitted

I have tried many things with the schema configuration, modifying the way to configure the default schema directly with hibernate properties, with jpa properties, with hikari properties... In case it would have been a schema problem or a case problem, I am currently having the following annotation for every entity (which I don't like much), but still not working anyway :

@Table(name = "reseau", schema = "my_schema")

Here is the query as displayed in the logs :

select reseau0_.id as id1_10_, reseau0_.nom as nom2_10_ from my_schema.reseau reseau0_

When executing it as is in psql on the server, it is working!

Could you please help me? I am a bit stuck for the moment...Thanks a lot!

Here is my persistence configuration file :

@Configuration
@ComponentScan
@EnableTransactionManagement
@EnableJpaRepositories
public class PersistenceJPAConfig {

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
    em.setDataSource(dataSource());
    em.setPackagesToScan(new String[] { "com.company.project.server.dao.model" });
    JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    em.setJpaVendorAdapter(vendorAdapter);
    em.setJpaProperties(additionalProperties());
    return em;
}

@Bean
public DataSource dataSource() {
    DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
    dataSourceBuilder.driverClassName("org.postgresql.Driver");
    dataSourceBuilder.url("jdbc:postgresql://127.0.0.1:5433/db");
    dataSourceBuilder.username("login");
    dataSourceBuilder.password("xxxxxxxx");
    return dataSourceBuilder.build();
}

@Bean
public PlatformTransactionManager transactionManager(EntityManagerFactory emf) {
    JpaTransactionManager transactionManager = new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(emf);
    return transactionManager;
}

@Bean
public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
    return new PersistenceExceptionTranslationPostProcessor();
}

Properties additionalProperties() {
    Properties properties = new Properties();
    properties.setProperty("spring.jpa.properties.hibernate.dialect", "org.hibernate.dialect.PostgreSQL95Dialect");
    //properties.setProperty("spring.jpa.properties.hibernate.default_schema", "my_schema");
    properties.setProperty("spring.jpa.properties.hibernate.hikari.dataSource.currentSchema", "my_schema");
    properties.setProperty("spring.jpa.show-sql", "true");
    properties.setProperty("spring.jpa.properties.hibernate.format_sql", "true");
    properties.setProperty("spring.jpa.properties.hibernate.id.new_generator_mappings", "true");
    return properties;
}

}

zyrion
  • 33
  • 5
  • why do you need the schema there? can you try removing it from the entity annotation? – aballaci Jul 01 '20 at 22:38
  • I guess that the required are not available on the debian Server. Do all the Tables already exist on the Debian Postgres Server ? If not make sure you have the properties set as described in [this post](https://stackoverflow.com/questions/26881739/unable-to-get-spring-boot-to-automatically-create-database-schema#26882135). – eckad158 Jul 02 '20 at 05:38
  • @ArmandoBallaci Thanks for your help. I have been trying things because without this schema there, it is not mentionned in the query, the query is : select...from reseau instead of my_schema.reseau. There is something not working with both spring.jpa.properties.hibernate.hikari.dataSource.currentSchema and spring.jpa.properties.hibernate.default_schema I tried both with and without the annotation and it is still not working. – zyrion Jul 02 '20 at 08:23
  • @springe Thanks for your help. Everything is OK on the database. I have an SQL script for creating it, and I am using the same locally (where it is working) and on the server. Both the schema and the table the query is looking for exist in the DB (checked with pgsql on the server running \dt my_schema.*). – zyrion Jul 02 '20 at 08:36

0 Answers0