I have a spring boot application deployed on heroku, which uses a postgres db hosted on heroku.
I have configured config vars on heroku and am using them in my spring boot application like this :
and in my code I use a connectionService:
public static Connection getConnection() {
try {
final String dbUrl = System.getenv("SPRING_DATASOURCE_URL");
final String dbUserName = System.getenv("SPRING_DATASOURCE_USERNAME");
final String dbPassword = System.getenv("SPRING_DATASOURCE_PASSWORD");
return DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
} catch(final SQLException e) {
LoggerService.writeErrorMsg("There was an error with the connection : [errorMsg: \n" + e + " ]");
}
return null;
}
}
And I use DAOs with CRUD methods to access the database, for example :
public static AdminAccount getAdminAccountById(final int id) throws SQLException {
final Connection connection = ConnectionService.getConnection();
final String sql = "select * from admin_account where id = ?";
final PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
final ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
final AdminAccount adminAccount = new AdminAccount();
adminAccount.setId(resultSet.getInt("id"));
adminAccount.setFacility(resultSet.getString("facility"));
adminAccount.setFirstName(resultSet.getString("first_name"));
adminAccount.setLastName(resultSet.getString("last_name"));
adminAccount.setEmail(resultSet.getString("email"));
connection.close();
return adminAccount;
} else {
return null;
}
}
This is working fine on the deployed app, however it no longer works in localhost since I removed the hardcoded db credentials. Also, after reading about best practices, I have figured out that it's best to have a separate DB only for testing/development, so you don't actually run queries against the production DB.
I have tried setting up a h2 memory database but with no luck. It doesn't seem to even start when I check the logs.
This is my pom.xml :
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>...</groupId>
<artifactId>...</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>rbooking</name>
<description>...</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.6</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</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>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<excludes>
<exclude>app/**</exclude>
</excludes>
</resource>
</resources>
<plugins>
<plugin>
<groupId>com.heroku.sdk</groupId>
<artifactId>heroku-maven-plugin</artifactId>
<version>2.0.8</version>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
And this is my application.properties:
#PORT
server.port = 8081
#LOGGING
logging.file.name=src/main/resources/logs/application.log
logging.file.path=src/main/resources/logs
logging.level.root = INFO
#SECURITY
security.basic.enable= false
security.ignored=/**
#DB
spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.defer-datasource-initialization=true
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
#DEVTOOLS
When I try to access the h2 database at localhost:8081/h2-console, I get this error msg:
What am I missing here to make it start and recognize my application.properties variables? I have used the exact same application.properties configuration for another project and the h2 database starts and runs without issues
EDIT I was able to make it work by specifying an older version of the h2 maven dependency :
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.193</version>
</dependency>
