how to properly specify database schema in spring boot?

Question

in my spring boot / hibernate application, I need to connect to a Postgres database "ax2012_1", which belong to the "dbo" schema.

I have the following application.properties:

# Database db.driver: org.postgresql.Driver db.url: jdbc:postgresql://localhost:5432/ax2012_1 db.username: my_user_name db.password: my_password  spring.datasource.url= jdbc:postgresql://localhost:5432/ax2012_1 spring.datasource.username=my_user_name spring.datasource.password=my_password spring.datasource.schema=dbo   spring.jpa.hibernate.ddl-auto=validate  # Hibernate hibernate.dialect: org.hibernate.dialect.PostgreSQLDialect hibernate.show_sql: true hibernate.hbm2ddl.auto: update entitymanager.packagesToScan: com.mycompany.myproduct.data 

my DatabaseConfig.java looks like this:

package com.uptake.symphony.data.configs;  import java.util.Properties;  import javax.sql.DataSource;  import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.env.Environment; import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor; import org.springframework.jdbc.datasource.DriverManagerDataSource; import org.springframework.orm.jpa.JpaTransactionManager; import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter; import org.springframework.transaction.annotation.EnableTransactionManagement;  @Configuration @EnableTransactionManagement public class DatabaseConfig {    // ------------------------   // PUBLIC METHODS   // ------------------------    /**    * DataSource definition for database connection. Settings are read from    * the application.properties file (using the env object).    */   @Bean   public DataSource dataSource() {     DriverManagerDataSource dataSource = new DriverManagerDataSource();     dataSource.setDriverClassName(env.getProperty("db.driver"));     dataSource.setUrl(env.getProperty("db.url"));     dataSource.setUsername(env.getProperty("db.username"));     dataSource.setPassword(env.getProperty("db.password"));     return dataSource;   }    /**    * Declare the JPA entity manager factory.    */   @Bean   public LocalContainerEntityManagerFactoryBean entityManagerFactory() {     LocalContainerEntityManagerFactoryBean entityManagerFactory =         new LocalContainerEntityManagerFactoryBean();      entityManagerFactory.setDataSource(dataSource);      // Classpath scanning of @Component, @Service, etc annotated class     entityManagerFactory.setPackagesToScan(         env.getProperty("entitymanager.packagesToScan"));      // Vendor adapter     HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();     entityManagerFactory.setJpaVendorAdapter(vendorAdapter);      // Hibernate properties     Properties additionalProperties = new Properties();     additionalProperties.put(         "hibernate.dialect",          env.getProperty("hibernate.dialect"));     additionalProperties.put(         "hibernate.show_sql",          env.getProperty("hibernate.show_sql"));     additionalProperties.put(         "hibernate.hbm2ddl.auto",          env.getProperty("hibernate.hbm2ddl.auto"));     entityManagerFactory.setJpaProperties(additionalProperties);     additionalProperties.put("hibernate.default_schema", "dbo");      return entityManagerFactory;   }    /**    * Declare the transaction manager.    */   @Bean   public JpaTransactionManager transactionManager() {     JpaTransactionManager transactionManager =          new JpaTransactionManager();     transactionManager.setEntityManagerFactory(         entityManagerFactory.getObject());     return transactionManager;   }    @Bean   public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {     return new PersistenceExceptionTranslationPostProcessor();   }    @Autowired   private Environment env;    @Autowired   private DataSource dataSource;    @Autowired   private LocalContainerEntityManagerFactoryBean entityManagerFactory; } 

From the startup log, I can tell, that hibernate is, in fact, discovering my table "custtable":

Hibernate Core {4.3.8.Final} 2016-09-10 15:27:15.866  INFO 69384 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found 2016-09-10 15:27:15.867  INFO 69384 --- [           main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist 2016-09-10 15:27:16.096  INFO 69384 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {4.0.5.Final} 2016-09-10 15:27:18.367  INFO 69384 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect 2016-09-10 15:27:18.378  INFO 69384 --- [           main] o.h.e.jdbc.internal.LobCreatorBuilder    : HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException 2016-09-10 15:27:18.499  WARN 69384 --- [           main] org.hibernate.mapping.RootClass          : HHH000038: Composite-id class does not override equals(): com.myconpany.myproduct.data.CusttableCompositeKey 2016-09-10 15:27:18.499  WARN 69384 --- [           main] org.hibernate.mapping.RootClass          : HHH000039: Composite-id class does not override hashCode(): com.myconpany.myproduct.data.CusttableCompositeKey 2016-09-10 15:27:18.508  INFO 69384 --- [           main] o.h.h.i.ast.ASTQueryTranslatorFactory    : HHH000397: Using ASTQueryTranslatorFactory 2016-09-10 15:27:18.932  INFO 69384 --- [           main] org.hibernate.tool.hbm2ddl.SchemaUpdate  : HHH000228: Running hbm2ddl schema update 2016-09-10 15:27:18.933  INFO 69384 --- [           main] org.hibernate.tool.hbm2ddl.SchemaUpdate  : HHH000102: Fetching database metadata 2016-09-10 15:27:18.943  INFO 69384 --- [           main] org.hibernate.tool.hbm2ddl.SchemaUpdate  : HHH000396: Updating schema 2016-09-10 15:27:18.993  INFO 69384 --- [           main] o.hibernate.tool.hbm2ddl.TableMetadata   : HHH000261: Table found: dbo.custtable 2016-09-10 15:27:18.993  INFO 69384 --- [           main] o.hibernate.tool.hbm2ddl.TableMetadata   : HHH000037: Columns: [custexcludecollectionfee, interestcode_br, servicecodeondlvaddress_br, stateinscription_mx, insscei_br, memo, inventprofiletype_ru, rfidcasetagging, irs1099cindicator, agencylocationcode, enterprisecode, issueownentrycertificate_w, invoicepostingtype_ru, affiliated_ru, contactpersonid, creditcardaddressverification, taxgstreliefgroupheading_my, rfidpallettagging, ccmnum_br, custfinaluser_br, orderentrydeadlinegroupid, accountnum, subsegmentid, entrycertificaterequired_w, salesgroup, bankcentralbankpurposetext, suppitemgroupid, curp_mx, pdscustrebategroupid, commercialregisterinsetnumber, mandatoryvatdate_pl, usecashdisc, inventsiteid, custitemgroupid, defaultdirectdebitmandate, taxwithholdcalculate_th, mcrmergedroot, exportsales_pl, cashdisc, syncversion, federalcomments, girotypeprojinvoice, syncentityid, intercompanyautocreateorders, icmscontributor_br, finecode_br, del_modifiedtime, rfiditemtagging, companynafcode, ienum_br, dataareaid, inventlocation, consday_jp, unitedvatinvoice_lt, foreignerid_br, partition, blocked, vatnum, markupgroup, incltax, custwhtcontributiontype_br, salesdistrictid, girotypefreetextinvoice, currency, maincontactworker, taxgroup, authorityoffice_it, pdsrebatetmagroup, salespoolid, intercompanydirectdelivery, presencetype_br, paymentreference_ee, mcrmergedparent, linedisc, partycountry, commercialregister, multilinedisc, isresident_lv, numbersequencegroup, rfc_mx, paymspec, suframapiscofins_br, fednonfedindicator, invoiceaddress, ouraccountnum, shipcarrierfuelsurcharge, segmentid, defaultdimension, vendaccount, dlvmode, identificationnumber, suframa_br, fiscalcode, taxlicensenum, shipcarrieraccount, destinationcodeid, einvoice, nit_br, cnae_br, paymmode, paymsched, companychainid, girotypeinterestnote, cashdiscbasedays, freightzone, birthcountycode_it, daxintegrationid, packmaterialfeelicensenum, girotypeaccountstatement, defaultinventstatusid, pdsfreightaccrued, shipcarrierid, modifieddatetime, custtradingpartnercode, lvpaymtranscodes, birthplace_it, clearingperiod, enterprisenumber, mandatorycreditlimit, companytype_mx, salescalendarid, einvoiceeannum, websalesorderdisplay, custgroup, dlvreason, factoringaccount, orgid, commercialregistersection, creditcardaddressverificationvoid, paymidtype, dlvterm, residenceforeigncountryregionid_it, paymtermid, statisticsgroup, packagedepositexcempt_pl, forecastdmpinclude, companyidsiret, modifiedby, girotype, birthdate_it, createddatetime, party, taxwithholdgroup_th, shipcarrierblindshipment, onetimecustomer, accountstatement, del_createdtime, lineofbusinessid, taxbordernumber_fi, partystate, expressbilloflading, bankaccount, regnum_w, enddisc, commissiongroup, intercompanyallowindirectcreation, paymdayid, passportno_hu, creditrating, custclassificationid, generateincomingfiscaldocument_br, pbacustgroupid, shipcarrieraccountcode, recid, einvoiceregister_it, intbank_lv, bankcentralbankpurposecode, taxwithholdcalculate_in, usepurchrequest, fiscaldoctype_pl, girotypecollectionletter, pricegroup, invoiceaccount, creditcardaddressverificationlevel, recversion, foreignresident_ru, creditcardcvc, bankcustpaymidtable, cnpjcpfnum_br, inventprofileid_ru, taxperiodpaymentcode_pl, custexcludeinterestcharges, issuercountry_hu, creditmax, suframanumber_br] 2016-09-10 15:27:18.994  INFO 69384 --- [           main] o.hibernate.tool.hbm2ddl.TableMetadata   : HHH000108: Foreign keys: [] 

However, when I run "findById" from my DAO:

public Custtable getById(Class<Custtable> class1, CusttableCompositeKey custtableCompositeKey) {         Custtable ct = entityManager.find(Custtable.class, custtableCompositeKey);         LOG.debug("customer.ct.accountNum:  " + ct.getAccountnum());         return entityManager.find(Custtable.class, custtableCompositeKey);     } 

I get this error:

Hibernate: select custtable0_.accountnum as accountn1_0_0_, custtable0_.dataareaid as dataarea2_0_0_, custtable0_.partition as partitio3_0_0_, custtable0_.accountstatement as accounts4_0_0_, custtable0_.affiliated_ru as affiliat5_0_0_, custtable0_.agencylocationcode as agencylo6_0_0_, custtable0_.authorityoffice_it as authorit7_0_0_, custtable0_.bankaccount as bankacco8_0_0_, custtable0_.bankcentralbankpurposecode as bankcent9_0_0_, custtable0_.bankcentralbankpurposetext as bankcen10_0_0_, custtable0_.bankcustpaymidtable as bankcus11_0_0_, custtable0_.birthcountycode_it as birthco12_0_0_, custtable0_.birthdate_it as birthda13_0_0_, custtable0_.birthplace_it as birthpl14_0_0_, custtable0_.blocked as blocked15_0_0_, custtable0_.cashdisc as cashdis16_0_0_, custtable0_.cashdiscbasedays as cashdis17_0_0_, custtable0_.ccmnum_br as ccmnum_18_0_0_, custtable0_.clearingperiod as clearin19_0_0_, custtable0_.cnae_br as cnae_br20_0_0_, custtable0_.cnpjcpfnum_br as cnpjcpf21_0_0_, custtable0_.commercialregister as commerc22_0_0_, custtable0_.commercialregisterinsetnumber as commerc23_0_0_, custtable0_.commercialregistersection as commerc24_0_0_, custtable0_.commissiongroup as commiss25_0_0_, custtable0_.companychainid as company26_0_0_, custtable0_.companyidsiret as company27_0_0_, custtable0_.companynafcode as company28_0_0_, custtable0_.companytype_mx as company29_0_0_, custtable0_.consday_jp as consday30_0_0_, custtable0_.contactpersonid as contact31_0_0_, custtable0_.createddatetime as created32_0_0_, custtable0_.creditcardaddressverification as creditc33_0_0_, custtable0_.creditcardaddressverificationlevel as creditc34_0_0_, custtable0_.creditcardaddressverificationvoid as creditc35_0_0_, custtable0_.creditcardcvc as creditc36_0_0_, custtable0_.creditmax as creditm37_0_0_, custtable0_.creditrating as creditr38_0_0_, custtable0_.curp_mx as curp_mx39_0_0_, custtable0_.currency as currenc40_0_0_, custtable0_.custclassificationid as custcla41_0_0_, custtable0_.custexcludecollectionfee as custexc42_0_0_, custtable0_.custexcludeinterestcharges as custexc43_0_0_, custtable0_.custfinaluser_br as custfin44_0_0_, custtable0_.custgroup as custgro45_0_0_, custtable0_.custitemgroupid as custite46_0_0_, custtable0_.custtradingpartnercode as custtra47_0_0_, custtable0_.custwhtcontributiontype_br as custwht48_0_0_, custtable0_.daxintegrationid as daxinte49_0_0_, custtable0_.defaultdimension as default50_0_0_, custtable0_.defaultdirectdebitmandate as default51_0_0_, custtable0_.defaultinventstatusid as default52_0_0_, custtable0_.del_createdtime as del_cre53_0_0_, custtable0_.del_modifiedtime as del_mod54_0_0_, custtable0_.destinationcodeid as destina55_0_0_, custtable0_.dlvmode as dlvmode56_0_0_, custtable0_.dlvreason as dlvreas57_0_0_, custtable0_.dlvterm as dlvterm58_0_0_, custtable0_.einvoice as einvoic59_0_0_, custtable0_.einvoiceeannum as einvoic60_0_0_, custtable0_.einvoiceregister_it as einvoic61_0_0_, custtable0_.enddisc as enddisc62_0_0_, custtable0_.enterprisecode as enterpr63_0_0_, custtable0_.enterprisenumber as enterpr64_0_0_, custtable0_.entrycertificaterequired_w as entryce65_0_0_, custtable0_.exportsales_pl as exports66_0_0_, custtable0_.expressbilloflading as express67_0_0_, custtable0_.factoringaccount as factori68_0_0_, custtable0_.federalcomments as federal69_0_0_, custtable0_.fednonfedindicator as fednonf70_0_0_, custtable0_.finecode_br as finecod71_0_0_, custtable0_.fiscalcode as fiscalc72_0_0_, custtable0_.fiscaldoctype_pl as fiscald73_0_0_, custtable0_.forecastdmpinclude as forecas74_0_0_, custtable0_.foreignerid_br as foreign75_0_0_, custtable0_.foreignresident_ru as foreign76_0_0_, custtable0_.freightzone as freight77_0_0_, custtable0_.generateincomingfiscaldocument_br as generat78_0_0_, custtable0_.girotype as girotyp79_0_0_, custtable0_.girotypeaccountstatement as girotyp80_0_0_, custtable0_.girotypecollectionletter as girotyp81_0_0_, custtable0_.girotypefreetextinvoice as girotyp82_0_0_, custtable0_.girotypeinterestnote as girotyp83_0_0_, custtable0_.girotypeprojinvoice as girotyp84_0_0_, custtable0_.icmscontributor_br as icmscon85_0_0_, custtable0_.identificationnumber as identif86_0_0_, custtable0_.ienum_br as ienum_b87_0_0_, custtable0_.incltax as incltax88_0_0_, custtable0_.insscei_br as insscei89_0_0_, custtable0_.intbank_lv as intbank90_0_0_, custtable0_.intercompanyallowindirectcreation as interco91_0_0_, custtable0_.intercompanyautocreateorders as interco92_0_0_, custtable0_.intercompanydirectdelivery as interco93_0_0_, custtable0_.interestcode_br as interes94_0_0_, custtable0_.inventlocation as inventl95_0_0_, custtable0_.inventprofileid_ru as inventp96_0_0_, custtable0_.inventprofiletype_ru as inventp97_0_0_, custtable0_.inventsiteid as invents98_0_0_, custtable0_.invoiceaccount as invoice99_0_0_, custtable0_.invoiceaddress as invoic100_0_0_, custtable0_.invoicepostingtype_ru as invoic101_0_0_, custtable0_.irs1099cindicator as irs102_0_0_, custtable0_.isresident_lv as isresi103_0_0_, custtable0_.issueownentrycertificate_w as issueo104_0_0_, custtable0_.issuercountry_hu as issuer105_0_0_, custtable0_.linedisc as linedi106_0_0_, custtable0_.lineofbusinessid as lineof107_0_0_, custtable0_.lvpaymtranscodes as lvpaym108_0_0_, custtable0_.maincontactworker as mainco109_0_0_, custtable0_.mandatorycreditlimit as mandat110_0_0_, custtable0_.mandatoryvatdate_pl as mandat111_0_0_, custtable0_.markupgroup as markup112_0_0_, custtable0_.mcrmergedparent as mcrmer113_0_0_, custtable0_.mcrmergedroot as mcrmer114_0_0_, custtable0_.memo as memo115_0_0_, custtable0_.modifiedby as modifi116_0_0_, custtable0_.modifieddatetime as modifi117_0_0_, custtable0_.multilinedisc as multil118_0_0_, custtable0_.nit_br as nit_br119_0_0_, custtable0_.numbersequencegroup as number120_0_0_, custtable0_.onetimecustomer as onetim121_0_0_, custtable0_.orderentrydeadlinegroupid as ordere122_0_0_, custtable0_.orgid as orgid123_0_0_, custtable0_.ouraccountnum as ouracc124_0_0_, custtable0_.packagedepositexcempt_pl as packag125_0_0_, custtable0_.packmaterialfeelicensenum as packma126_0_0_, custtable0_.party as party127_0_0_, custtable0_.partycountry as partyc128_0_0_, custtable0_.partystate as partys129_0_0_, custtable0_.passportno_hu as passpo130_0_0_, custtable0_.paymdayid as paymda131_0_0_, custtable0_.paymentreference_ee as paymen132_0_0_, custtable0_.paymidtype as paymid133_0_0_, custtable0_.paymmode as paymmo134_0_0_, custtable0_.paymsched as paymsc135_0_0_, custtable0_.paymspec as paymsp136_0_0_, custtable0_.paymtermid as paymte137_0_0_, custtable0_.pbacustgroupid as pbacus138_0_0_, custtable0_.pdscustrebategroupid as pdscus139_0_0_, custtable0_.pdsfreightaccrued as pdsfre140_0_0_, custtable0_.pdsrebatetmagroup as pdsreb141_0_0_, custtable0_.presencetype_br as presen142_0_0_, custtable0_.pricegroup as priceg143_0_0_, custtable0_.recid as recid144_0_0_, custtable0_.recversion as recver145_0_0_, custtable0_.regnum_w as regnum146_0_0_, custtable0_.residenceforeigncountryregionid_it as reside147_0_0_, custtable0_.rfc_mx as rfc_mx148_0_0_, custtable0_.rfidcasetagging as rfidca149_0_0_, custtable0_.rfiditemtagging as rfidit150_0_0_, custtable0_.rfidpallettagging as rfidpa151_0_0_, custtable0_.salescalendarid as salesc152_0_0_, custtable0_.salesdistrictid as salesd153_0_0_, custtable0_.salesgroup as salesg154_0_0_, custtable0_.salespoolid as salesp155_0_0_, custtable0_.segmentid as segmen156_0_0_, custtable0_.servicecodeondlvaddress_br as servic157_0_0_, custtable0_.shipcarrieraccount as shipca158_0_0_, custtable0_.shipcarrieraccountcode as shipca159_0_0_, custtable0_.shipcarrierblindshipment as shipca160_0_0_, custtable0_.shipcarrierfuelsurcharge as shipca161_0_0_, custtable0_.shipcarrierid as shipca162_0_0_, custtable0_.stateinscription_mx as statei163_0_0_, custtable0_.statisticsgroup as statis164_0_0_, custtable0_.subsegmentid as subseg165_0_0_, custtable0_.suframa_br as sufram166_0_0_, custtable0_.suframanumber_br as sufram167_0_0_, custtable0_.suframapiscofins_br as sufram168_0_0_, custtable0_.suppitemgroupid as suppit169_0_0_, custtable0_.syncentityid as syncen170_0_0_, custtable0_.syncversion as syncve171_0_0_, custtable0_.taxbordernumber_fi as taxbor172_0_0_, custtable0_.taxgroup as taxgro173_0_0_, custtable0_.taxgstreliefgroupheading_my as taxgst174_0_0_, custtable0_.taxlicensenum as taxlic175_0_0_, custtable0_.taxperiodpaymentcode_pl as taxper176_0_0_, custtable0_.taxwithholdcalculate_in as taxwit177_0_0_, custtable0_.taxwithholdcalculate_th as taxwit178_0_0_, custtable0_.taxwithholdgroup_th as taxwit179_0_0_, custtable0_.unitedvatinvoice_lt as united180_0_0_, custtable0_.usecashdisc as usecas181_0_0_, custtable0_.usepurchrequest as usepur182_0_0_, custtable0_.vatnum as vatnum183_0_0_, custtable0_.vendaccount as vendac184_0_0_, custtable0_.websalesorderdisplay as websal185_0_0_ from Custtable custtable0_ where custtable0_.accountnum=? and custtable0_.dataareaid=? and custtable0_.partition=? 2016-09-10 15:28:54.967  WARN 69384 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42P01 2016-09-10 15:28:54.967 ERROR 69384 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "custtable" does not exist 

What can I do to fix this?

Answer 1

For Hibernate as JPA provider use

spring.jpa.properties.hibernate.default_schema=dbo

For plain JDBC, add it to your connection string:

jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema 

Another option is to run the statement set schema 'dbo' early in each query object. (Yuck!)

BTW your spring.datasource.schema=dbo is probably mistake as this property specifies name of the sql file that contains your schema (CREATE/ALTER statements)