从 Spring Boot 连接到 Heroku Postgres [英] Connecting to Heroku Postgres from Spring Boot
问题描述
我正在寻找连接
现在打开一个新的浏览器选项卡,并转到您的 Heroku 应用程序的 Settings
选项卡.点击 Reveal Config Vars
并创建以下环境变量:
SPRING_DATASOURCE_URL
= jdbc:postgresql://YourPostgresHerokuHostNameHere:5432/YourPostgresHerokuDatabaseNameHere(注意前面的jdbc:
和ql
添加到postgres
!)SPRING_DATASOURCE_USERNAME
= YourPostgresHerokuUserNameHereSPRING_DATASOURCE_PASSWORD
= YourPostgresHerokuPasswordHereSPRING_DATASOURCE_DRIVER-CLASS-NAME
=org.postgresql.Driver
(这并不总是需要的现在这就是你所要做的!你的 Heroku 应用程序在你每次更改配置变量时都会重新启动 - 所以你的应用程序现在应该在本地运行 H2,并且应该准备好与 PostgreSQL 连接时部署在 Heroku 上.
如果你问:为什么我们要配置 Tomcat JDBC 而不是 Hikari
您可能已经注意到,我们在 pom.xml 中添加了
<块引用>tomcat-jdbc
依赖项并将SPRING_DATASOURCE_TYPE=org.apache.tomcat.jdbc.pool.DataSource
配置为一个环境变量.只有 关于这个的文档中有一点暗示说您可以完全绕过该算法并指定连接通过设置 spring.datasource.type 属性来使用的池.这是如果您在 Tomcat 容器中运行您的应用程序,这一点尤为重要,...
我切换回 Tomcat 池化数据源而不是使用 Spring Boot 2.x 标准 HikariCP 有几个原因.正如我已经在这里解释,如果你不指定
spring.datasource.url
,Spring将尝试自动连接嵌入式内存 H2 数据库而不是我们的 PostgreSQL 数据库.Hikari 的问题是,它只支持spring.datasource.jdbc-url
.其次,如果我尝试使用 Hikari 所示的 Heroku 配置(因此省略
SPRING_DATASOURCE_TYPE
并将SPRING_DATASOURCE_URL
更改为SPRING_DATASOURCE_JDBC-URL
) 我遇到以下异常:Caused by: java.lang.RuntimeException: Driver org.postgresql.Driver 声称不接受 jdbcUrl, jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
所以我没有让 Spring Boot 2.x 在 Heroku & 上工作Postgres 带有 HikariCP,但带有 Tomcat JDBC - 而且我也不想中断包含预先描述的本地 H2 数据库的开发过程.请记住:我们正在寻找使用 JPA/Hibernate 在 Spring Boot 应用程序中连接到 Heroku Postgres 的最简单、最干净的方法!
I'm looking for the simplest, cleanest way of connecting to Heroku Postgres in a Spring Boot app using JPA/Hibernate.
I don't see a good, complete example for this combo in either Heroku or Spring Boot documentation, so I'd like to document this on Stack Overflow.
I'm trying to go with something like this:
@Configuration public class DataSourceConfig { Logger log = LoggerFactory.getLogger(getClass()); @Bean @Profile("postgres") public DataSource postgresDataSource() { String databaseUrl = System.getenv("DATABASE_URL") log.info("Initializing PostgreSQL database: {}", databaseUrl); URI dbUri; try { dbUri = new URI(databaseUrl); } catch (URISyntaxException e) { log.error(String.format("Invalid DATABASE_URL: %s", databaseUrl), e); return null; } String username = dbUri.getUserInfo().split(":")[0]; String password = dbUri.getUserInfo().split(":")[1]; String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':' + dbUri.getPort() + dbUri.getPath(); // fully-qualified class name to distuinguish from javax.sql.DataSource org.apache.tomcat.jdbc.pool.DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource(); dataSource.setUrl(dbUrl); dataSource.setUsername(username); dataSource.setPassword(password); return dataSource; } }
I'm using Profiles, which seems a good match for what I want: on Heroku
SPRING_PROFILES_ACTIVE
is set topostgres
, while in local developmentspring.profiles.active
ish2
to use a H2 in-memory database (whose config omitted here). This approach seems to work fine.In
application-postgres.properties
(profile-specific properties):spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect spring.datasource.driverClassName=org.postgresql.Driver
DataSource
from Tomcat seemed like a good option since the default dependencies include it, and because Spring Boot reference guide says:We prefer the Tomcat pooling DataSource for its performance and concurrency, so if that is available we always choose it.
(I'm also seeing
BasicDataSource
from Commons DBCP being used with Spring Boot. But to me this does not seem like the cleanest choice as the default dependencies do not include Commons DBCP. And in general I'm wondering if Apache Commons could really, in 2015, be the recommended way to connect to Postgres... Also Heroku documentation offers "BasicDataSource
in Spring" for this kind of scenario; I assume this refers to Commons DBCP, since I don't see such class in Spring itself.)Dependencies:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>9.4-1205-jdbc42</version> </dependency>
Current status: failing with "Not loading a JDBC driver as driverClassName property is null":
eConfig$$EnhancerBySpringCGLIB$$463388c1 : Initializing PostgreSQL database: postgres:[...] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default' org.hibernate.cfg.Environment : HHH000206: hibernate.properties not found [...] o.a.tomcat.jdbc.pool.PooledConnection : Not loading a JDBC driver as driverClassName property is null. o.a.tomcat.jdbc.pool.PooledConnection : Not loading a JDBC driver as driverClassName property is null. [...] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect
In logs I see that my
postgresDataSource
is called just fine, and that PostgreSQLDialect is in use (without this it was failing with "Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set").My specific questions
- Well, how to get this working? I am setting
spring.datasource.driverClassName
, so why "Not loading a JDBC driver as driverClassName property is null"? - Is the use of Tomcat's
DataSource
fine or would you recommend something else? - Is it mandatory to define
postgresql
dependency as above with a specific version? (I was getting "no suitable driver found" error without this.) - Is there a simpler way to do all this (while sticking to Java code and/or properties; no XML please)?
解决方案Simplest cleanest way for Spring Boot 2.x with Heroku & Postgres
I read all answers, but didn´t find what Jonik was looking for:
I'm looking for the simplest, cleanest way of connecting to Heroku Postgres in a Spring Boot app using JPA/Hibernate
The development process most people want to use with Spring Boot & Heroku includes a local H2 in-memory database for testing & fast development cycles - and the Heroku Postgres database for staging and production on Heroku.
- First thing is - you don´t need to use Spring profiles for that!
- Second: You don´t need to write/change any code!
Let´s have a look on what we have to do step by step. I have a example project in place that provides a fully working Heroku deployment and configuration for Postgres - only for the sake of completeness, if you want to test it yourself: github.com/jonashackt/spring-boot-vuejs.
The pom.xml
We need the following depencencies:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <!-- In-Memory database used for local development & testing --> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> </dependency> <!-- Switch back from Spring Boot 2.x standard HikariCP to Tomcat JDBC, configured later in Heroku (see https://stackoverflow.com/a/49970142/4964553) --> <dependency> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> </dependency> <!-- PostgreSQL used in Staging and Production environment, e.g. on Heroku --> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.2</version> </dependency>
One tricky thing here is the usage of
tomcat-jdbc
, but we´ll cover that in a second.Configure Environment Variables on Heroku
In Heroku Environment Variables are named
Config Vars
. You heard right, all we have to do is to configure Enviroment Variables! We just need the correct ones. Therefore head over to https://data.heroku.com/ (I assume there´s already a Postgres database configured for your Heroku app, which is the default behavior).Now click on your application´s corresponding
Datastore
and switch over to theSettings
tab. Then click onView Credentials...
, which should look something similar like this:Now open a new browser tab and go to your Heroku application´s
Settings
tab also. Click onReveal Config Vars
and create the following Environment Variables:SPRING_DATASOURCE_URL
= jdbc:postgresql://YourPostgresHerokuHostNameHere:5432/YourPostgresHerokuDatabaseNameHere (mind the leadingjdbc:
and theql
addition topostgres
!)SPRING_DATASOURCE_USERNAME
= YourPostgresHerokuUserNameHereSPRING_DATASOURCE_PASSWORD
= YourPostgresHerokuPasswordHereSPRING_DATASOURCE_DRIVER-CLASS-NAME
=org.postgresql.Driver
(this isn´t always needed since Spring Boot can deduce it for most databases from the url, just for completeness here)SPRING_JPA_DATABASE-PLATFORM
=org.hibernate.dialect.PostgreSQLDialect
SPRING_DATASOURCE_TYPE
=org.apache.tomcat.jdbc.pool.DataSource
SPRING_JPA_HIBERNATE_DDL-AUTO
=update
(this will automatically create your tables according to your JPA entities, which is really great - since you don´t need to hurdle withCREATE
SQL statements or DDL files)
In Heroku this should look like this:
Now that´s all you have to do! Your Heroku app is restarted every time you change a Config Variable - so your App should now run H2 locally, and should be ready connected with PostgreSQL when deployed on Heroku.
Just if you´re asking: Why do we configure Tomcat JDBC instead of Hikari
As you might noticed, we added the
tomcat-jdbc
dependency to our pom.xml and configuredSPRING_DATASOURCE_TYPE=org.apache.tomcat.jdbc.pool.DataSource
as a Environment variable. There´s only a slight hint in the docs about this sayingYou can bypass that algorithm completely and specify the connection pool to use by setting the spring.datasource.type property. This is especially important if you run your application in a Tomcat container, ...
There are several reasons I switched back to Tomcat pooling DataSource instead of using the Spring Boot 2.x standard HikariCP. As I already explained here, if you don´t specifiy
spring.datasource.url
, Spring will try to autowire the embedded im-memory H2 database instead of our PostgreSQL one. And the problem with Hikari is, that it only supportsspring.datasource.jdbc-url
.Second, if I try to use the Heroku configuration as shown for Hikari (so leaving out
SPRING_DATASOURCE_TYPE
and changingSPRING_DATASOURCE_URL
toSPRING_DATASOURCE_JDBC-URL
) I run into the following Exception:Caused by: java.lang.RuntimeException: Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
So I didn´t get Spring Boot 2.x working on Heroku & Postgres with HikariCP, but with Tomcat JDBC - and I also don´t want to brake my development process containing a local H2 database described upfront. Remember: We were looking for the simplest, cleanest way of connecting to Heroku Postgres in a Spring Boot app using JPA/Hibernate!
这篇关于从 Spring Boot 连接到 Heroku Postgres的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
- Well, how to get this working? I am setting