从Spring Boot连接到Heroku Postgres [英] Connecting to Heroku Postgres from Spring Boot
问题描述
我正在寻找最简单的连接 Heroku Postgres 的最简单的方式 a>在使用JPA / Hibernate的 Spring Boot 应用程序中。
在Heroku中没有看到这个组合的完整示例或Spring Boot文档，所以我想在Stack Overflow上记录这一点。
我试着用类似这样的东西：
@Configuration
public class DataSourceConfig {
Logger log = LoggerFactory.getLogger（getClass（））;
$Be
@Profile（postgres）
public DataSource postgresDataSource（）{
String databaseUrl = System.getenv（DATABASE_URL）
log.info（初始化PostgreSQL数据库：{}，databaseUrl）;
URI dbUri;
尝试{
dbUri = new URI（databaseUrl）;
}
catch（URISyntaxException e）{
log.error（String.format（Invalid DATABASE_URL：％s，databaseUrl），e）;
返回null;
}
字符串用户名= dbUri.getUserInfo（）。split（：）[0];
String password = dbUri.getUserInfo（）。split（：）[1];
String dbUrl =jdbc：postgresql：//+ dbUri.getHost（）+'：'
+ dbUri.getPort（）+ dbUri.getPath（）;
//完全限定的类名，用于从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）;
返回dataSource;
}
}
我正在使用个人资料，这似乎很不错我想要的：在Heroku SPRING_PROFILES_ACTIVE 设置为 postgres ，而在本地开发中 spring .profiles.active 是 h2 来使用H2内存数据库（其配置在这里省略）。这种方法似乎工作正常。
在 application-postgres.properties （个人资料特定的属性 ）：
spring.jpa.database-platform = org.hibernate.dialect.PostgreSQLDialect
spring.datasource.driverClassName = org.postgresql.Driver
DataSource from Tomcat似乎是一个很好的选择，因为默认的依赖包括它，并且因为 Spring Boot参考指南说：
我们更喜欢Tomcat池DataSource的性能和
并发性，所以如果可以的话，我们总是选择它。
m也可以从Commons DBCP看到 BasicDataSource 与Spring Boot一起使用。但对我来说，这看起来并不像最简洁的选择，因为默认依赖不包含Commons DBCP。一般来说，我想知道在2015年Apache Commons是否真的可以成为连接到Postgres的推荐方式......另外 Heroku文档提供了 BasicDataSource 在这种情况下;我认为这是指Commons DBCP，因为我在Spring本身没有看到这样的类。）
依赖关系：
<依赖性>
< groupId> org.springframework.boot< / groupId>
< artifactId> spring-boot-starter-web< / artifactId>
< /依赖关系>
< dependency>
< groupId> org.springframework.boot< / groupId>
< artifactId> spring-boot-starter-actuator< / artifactId>
< /依赖关系>
< dependency>
< groupId> org.springframework.boot< / groupId>
< artifactId> spring-boot-starter-test< / artifactId>
< scope> test< / scope>
< /依赖关系>
< dependency>
< groupId> org.springframework.boot< / groupId>
< artifactId> spring-boot-starter-data-jpa< / artifactId>
< /依赖关系>
< dependency>
< groupId> com.h2database< / groupId>
< artifactId> h2< / artifactId>
< /依赖关系>
< dependency>
< groupId> org.postgresql< / groupId>
< artifactId> postgresql< / artifactId>
< version> 9.4-1205-jdbc42< / version>
< /依赖关系>
当前状态：无法加载JDBC驱动程序作为driverClassName属性：
eConfig $$ EnhancerBySpringCGLIB $$ 463388c1：初始化PostgreSQL数据库：postgres：[...]
j.LocalContainerEntityManagerFactoryBean：为持久化单元'默认'构建JPA容器EntityManagerFactory
org.hibernate.cfg.Environment：HHH000206：找不到hibernate.properties
[...]
oatomcat。 jdbc.pool.PooledConnection：由于driverClassName属性为null，因此不加载JDBC驱动程序。
o.a.tomcat.jdbc.pool.PooledConnection：由于driverClassName属性为null，因此不加载JDBC驱动程序。
[...]
org.hibernate.dialect.Dialect：HHH000400：使用方言：org.hibernate.dialect.PostgreSQLDialect
在日志中，我发现我的 我的具体问题 为了使数据库连接正常工作在设置中，我在问题中描述： 因此，我的DataSourceConfig的固定版本： 仅在 现在，我所遇到的两个问题可能都是来自Tomcat的数据源（ 我愿意争取更简单/更好的解决方案，所以请随时发布，尤其是如果您可以在问题结束时解决疑惑2-4！ 更新：请注意，使用 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: I'm using Profiles, which seems a good match for what I want: on Heroku In We prefer the Tomcat pooling DataSource for its performance and
concurrency, so if that is available we always choose it. (I'm also seeing Dependencies: Current status: failing with "Not loading a JDBC driver as driverClassName property is null": In logs I see that my My specific questions
To get the database connection working (in a stable manner) two things were missing in the setup I described in the question: So, the fixed version of my DataSourceConfig: With only this in Now, both of the problems I had may be specific to the DataSource from Tomcat ( I'm open to competing / simpler / better solutions, so feel free to post, especially if you can address the doubts 2–4 at the end of the question! Update: Note that using 这篇关于从Spring Boot连接到Heroku Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！ postgresDataSource 被调用的很好，而
PostgreSQLDialect em>正在使用中（没有这个，它失败了，当'hibernate.dialect'没有设置时，访问DialectResolutionInfo不能为null」）。
spring.datasource.driverClassName ，所以为什么不加载JDBC驱动程序作为driverClassName属性为空？
DataSource 好，还是会推荐别的东西？
dataSource.setDriverClassName（org.postgresql.Driver）;
spring.datasource.driverClassName 属性没有任何效果。据我的理解，由于 DATABASE_URL ，我需要自定义数据源才能使其工作。）
org.postgresql.util.PSQLException：该连接已关闭。在应用程序运行一段时间后。有点令人惊讶的解决方案（基于这个答案）是启用某些测试，如 testOnBorrow ：
dataSource.setTestOnBorrow（true）;
dataSource.setTestWhileIdle（true）;
dataSource.setTestOnReturn（true）;
dataSource.setValidationQuery（SELECT 1）;
@Configuration
public class DataSourceConfig {
Logger log = LoggerFactory.getLogger（getClass（））;
$Be
@Profile（postgres）
public DataSource postgresDataSource（）{
String databaseUrl = System.getenv（DATABASE_URL）
log.info（初始化PostgreSQL数据库：{}，databaseUrl）;
URI dbUri;
尝试{
dbUri = new URI（databaseUrl）;
}
catch（URISyntaxException e）{
log.error（String.format（Invalid DATABASE_URL：％s，databaseUrl），e）;
返回null;
}
字符串用户名= dbUri.getUserInfo（）。split（：）[0];
String password = dbUri.getUserInfo（）。split（：）[1];
String dbUrl =jdbc：postgresql：//+ dbUri.getHost（）+'：'
+ dbUri.getPort（）+ dbUri.getPath（）;
org.apache.tomcat.jdbc.pool.DataSource dataSource
= new org.apache.tomcat.jdbc.pool.DataSource（）;
dataSource.setDriverClassName（org.postgresql.Driver）;
dataSource.setUrl（dbUrl）;
dataSource.setUsername（username）;
dataSource.setPassword（password）;
dataSource.setTestOnBorrow（true）;
dataSource.setTestWhileIdle（true）;
dataSource.setTestOnReturn（true）;
dataSource.setValidationQuery（SELECT 1）;
返回dataSource;
}
}
application-postgres.properties ：
spring.jpa.database-platform = org。 hibernate.dialect.PostgreSQLDialect
org.apache.tomcat .jdbc.pool ）。 显然 BasicDataSource（Commons DBCP）有更合理的默认值。但正如问题中提到的那样，我宁愿使用Spring Boot自带的东西，尤其是因为它是强烈认可参考指南。
使用
JDBC_DATABASE _ * 变量
JDBC_DATABASE _ * 比上述要简单得多，因为 指出在这个答案 。很长时间以来，我一直认为 DATABASE_URL 应该是首选，但现在我不再那么确定了。@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;
}
}
SPRING_PROFILES_ACTIVE is set to postgres, while in local development spring.profiles.active is h2 to use a H2 in-memory database (whose config omitted here). This approach seems to work fine.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:
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.)<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>
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
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").
spring.datasource.driverClassName, so why "Not loading a JDBC driver as driverClassName property is null"?DataSource fine or would you recommend something else? postgresql dependency as above with a specific version? (I was getting "no suitable driver found" error without this.)
dataSource.setDriverClassName("org.postgresql.Driver");spring.datasource.driverClassName property to have no effect. And to my understanding, due to the dynamic nature of Heroku's DATABASE_URL, I need custom datasource to make it work.)org.postgresql.util.PSQLException: This connection has been closed. after the app had been running for a while. A somewhat surprising solution (based on this answer) was to enable certain tests such as testOnBorrow on the Tomcat DataSource:
dataSource.setTestOnBorrow(true);
dataSource.setTestWhileIdle(true);
dataSource.setTestOnReturn(true);
dataSource.setValidationQuery("SELECT 1");@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();
org.apache.tomcat.jdbc.pool.DataSource dataSource
= new org.apache.tomcat.jdbc.pool.DataSource();
dataSource.setDriverClassName("org.postgresql.Driver");
dataSource.setUrl(dbUrl);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setTestOnBorrow(true);
dataSource.setTestWhileIdle(true);
dataSource.setTestOnReturn(true);
dataSource.setValidationQuery("SELECT 1");
return dataSource;
}
}
application-postgres.properties:spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
org.apache.tomcat.jdbc.pool). Apparently BasicDataSource (Commons DBCP) has more sensible defaults. But as mentiond in the question, I rather used something that comes with Spring Boot by default, especially as it's strongly endorsed in the reference guide. Using
JDBC_DATABASE_* variables insteadJDBC_DATABASE_* is much simpler than the above, as pointed out in this answer. For a long time I was under the impression that DATABASE_URL should be preferred, but nowadays I'm not so sure anymore.
