英文:
Wiring Up pre-existing Docker container running Postgres with Spring Boot
问题
My goal is: 使用运行Postgres数据库的Docker容器。使用该容器构建Spring服务和数据访问层。所有这些都在我的本地环境中进行。
我正在进行设置在Docker容器中运行Postgres数据库。我已经启动了容器。我已经创建了表、视图、触发器、序列等等。据我所知，一切都正常。我可以使用pgAdmin连接。
现在，我转向设置Spring Boot、Repository、Model以及单表的单元测试。该测试只向表中插入了几条记录。我尝试了几个示例、教程和博客，但在使用Maven构建和测试项目时，我收到了以下错误。
根据错误信息，我理解Spring Boot找不到表的序列，测试也无法证明容器正在运行。
以下是创建映像的方式：
docker run --name plaid_postgres2 --rm -d -p 54321:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=ach postgres
这是环境设置的更多信息。
以下是我的应用程序属性设置：
# database connectivity
spring.jpa.open-in-view=true
spring.jpa.database=POSTGRESQL
spring.datasource.platform=org.hibernate.dialect.PostgreSQLDialect
spring.datasource.url=jdbc:postgresql://localhost:54321/ach
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.use-new-id-generator-mappings=false
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
这是实体声明。请注意，我在Postgres中为特定表使用了序列。
@Entity
@Table(schema = "investor", name="vendor_tbl")
public class Vendor implements Serializable {
// ...
}
这是我的测试类。
public class VendorRepositoryTest {
// ...
}
在搜索了两天之后，我不知道如何解决问题。任何建议或指导将不胜感激。
谢谢，Russ
英文:
My goal is: Use a Docker container running a Postgres database. Use this container for building the Spring Services and data access layer. All of this is within my local environment.
I am working through setting up a Postgres database within a Docker container. I have the container up and running. I am have created the tables, views, triggers, sequences, etc. It works fine from all I can tell. I am able to connect using pgAdmin.
I have now moved to setting up Spring Boot, the Repository, the Model, and the unit test for a single table. The test only inserts a couple of records into the table. I have tried following several examples, tutorials and blogs.
When I build and test using maven for the project, I received the below error.
2020-08-10 16:01:49.407 INFO 83573 --- [ main] DeferredRepositoryInitializationListener : Triggering deferred initialization of Spring Data repositories…
2020-08-10 16:01:49.771 INFO 83573 --- [ main] DeferredRepositoryInitializationListener : Spring Data repositories initialized!
2020-08-10 16:01:49.789 INFO 83573 --- [ main] n.c.p.s.repository.VendorRepositoryTest : Started VendorRepositoryTest in 6.623 seconds (JVM running for 7.917)
2020-08-10 16:01:49.932 INFO 83573 --- [ main] o.t.d.DockerClientProviderStrategy : Loaded org.testcontainers.dockerclient.UnixSocketClientProviderStrategy from ~/.testcontainers.properties, will try it first
2020-08-10 16:01:50.660 INFO 83573 --- [ main] o.t.d.UnixSocketClientProviderStrategy : Accessing docker with local Unix socket
2020-08-10 16:01:50.661 INFO 83573 --- [ main] o.t.d.DockerClientProviderStrategy : Found Docker environment with local Unix socket (unix:///var/run/docker.sock)
2020-08-10 16:01:50.816 INFO 83573 --- [ main] org.testcontainers.DockerClientFactory : Docker host IP address is localhost
2020-08-10 16:01:50.871 INFO 83573 --- [ main] org.testcontainers.DockerClientFactory : Connected to docker:
Server Version: 19.03.12
API Version: 1.40
Operating System: Docker Desktop
Total Memory: 1991 MB
2020-08-10 16:01:51.966 INFO 83573 --- [ main] org.testcontainers.DockerClientFactory : Ryuk started - will monitor and terminate Testcontainers containers on JVM exit
2020-08-10 16:01:51.966 INFO 83573 --- [ main] org.testcontainers.DockerClientFactory : Checking the system...
2020-08-10 16:01:51.967 INFO 83573 --- [ main] org.testcontainers.DockerClientFactory : ✔︎ Docker server version should be at least 1.6.0
2020-08-10 16:01:52.131 INFO 83573 --- [ main] org.testcontainers.DockerClientFactory : ✔︎ Docker environment should have more than 2GB free disk space
2020-08-10 16:01:52.154 INFO 83573 --- [ main] 🐳 [postgres:9.6.12] : Creating container for image: postgres:9.6.12
2020-08-10 16:01:52.254 INFO 83573 --- [ main] 🐳 [postgres:9.6.12] : Starting container with ID: 0c1ea5de4bc47651ac2ce02d1e85590a9165d9c31b7584ea1924d88fc85eeaad
2020-08-10 16:01:52.614 INFO 83573 --- [ main] 🐳 [postgres:9.6.12] : Container postgres:9.6.12 is starting: 0c1ea5de4bc47651ac2ce02d1e85590a9165d9c31b7584ea1924d88fc85eeaad
2020-08-10 16:01:57.539 INFO 83573 --- [ main] 🐳 [postgres:9.6.12] : Container postgres:9.6.12 started in PT7.619S
Hibernate: select nextval ('vendor_tbl_vendor_id_seq')
2020-08-10 16:01:57.587 ERROR 83573 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: relation "vendor_tbl_vendor_id_seq" does not exist
Position: 17
Hibernate: select vendor0_.id as id1_0_, vendor0_.vendor_env as vendor_e2_0_, vendor0_.lpl_client_id as lpl_clie3_0_, vendor0_.public_key as public_k4_0_, vendor0_.secret_key as secret_k5_0_, vendor0_.vendor_name as vendor_n6_0_ from investor.vendor_tbl vendor0_
[ERROR] Tests run: 3, Failures: 0, Errors: 2, Skipped: 0, Time elapsed: 15.57 s <<< FAILURE! - in net.clouddeveloper.plaid.services.repository.VendorRepositoryTest
[ERROR] addVendor Time elapsed: 8.052 s <<< ERROR!
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at net.clouddeveloper.plaid.services.repository.VendorRepositoryTest.addVendor(VendorRepositoryTest.java:84)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at net.clouddeveloper.plaid.services.repository.VendorRepositoryTest.addVendor(VendorRepositoryTest.java:84)
Caused by: org.postgresql.util.PSQLException:
ERROR: relation "vendor_tbl_vendor_id_seq" does not exist
Position: 17
at net.clouddeveloper.plaid.services.repository.VendorRepositoryTest.addVendor(VendorRepositoryTest.java:84)
[ERROR] validateDatabaseRunning Time elapsed: 0.005 s <<< ERROR!
java.lang.IllegalStateException: Mapped port can only be obtained after the container is started
at net.clouddeveloper.plaid.services.repository.VendorRepositoryTest.performQuery(VendorRepositoryTest.java:56)
at net.clouddeveloper.plaid.services.repository.VendorRepositoryTest.validateDatabaseRunning(VendorRepositoryTest.java:46)
From what I understand from the error, Spring Boot can not find the sequence for the table and the test can not prove can not prove the container is running.
Here is how I create the image:
docker run --name plaid_postgres2 --rm -d -p 54321:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=ach postgres
This is more of the environment settings.
Here is how I have the application properties setup:
# database connectivity
spring.jpa.open-in-view=true
spring.jpa.database=POSTGRESQL
spring.datasource.platform=org.hibernate.dialect.PostgreSQLDialect
spring.datasource.url=jdbc:postgresql://localhost:54321/ach
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.use-new-id-generator-mappings=false
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
This is the Entity declaration. Note I am using a sequence in Postgres for the specific table.
@Entity
@Table(schema = "investor", name="vendor_tbl")
public class Vendor implements Serializable {
private static final long serialVersionUID = -2343243243242432341L;
@Id
@Column(name="vendor_id")
@SequenceGenerator(schema="investor",
name="vendor_tbl_vendor_id_seq",
sequenceName="vendor_tbl_vendor_id_seq",
allocationSize=1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator="vendor_tbl_vendor_id_seq")
private long vendor_id;
This is my Test Class:
public class VendorRepositoryTest {
@Autowired
private VendorRepository repository;
@Container
private static final PostgreSQLContainer postgresqlContainer = new PostgreSQLContainer()
.withDatabaseName("ach")
.withUsername("postgres")
.withPassword("postgres");
@Test
@DisplayName("Validate Database is Running")
void validateDatabaseRunning() throws Exception {
ResultSet resultSet = performQuery(postgresqlContainer, "SELECT 1");
resultSet.next();
int result = resultSet.getInt(1);
assertEquals(1, result);
assertTrue(postgresqlContainer.isRunning());
}
private ResultSet performQuery(PostgreSQLContainer protgres, String query) throws SQLException {
String jdbcURL = postgresqlContainer.getJdbcUrl();
String userName = postgresqlContainer.getUsername();
String password = postgresqlContainer.getPassword();
Connection conn = DriverManager.getConnection(jdbcURL, userName, password);
return conn.createStatement().executeQuery(query);
}
@Test
@DisplayName("Test Find All Vendors")
public void find_all_vendors() {
Iterable<Vendor> vendors = repository.findAll();
int numOfCities =4;
assertThat(vendors).isEmpty(); //.hasSize(numOfCities);
}
@Test
@DisplayName("Test Add Vendor")
public void addVendor(){
String clientID = "blahblahblah";
String secret_key = "blahblah";
String public_key = "blahblah";
String environment = "blah";
String vendor_name = "Russ Test";
Vendor vendor1 = new Vendor(clientID, secret_key, public_key, environment, vendor_name);
Vendor vendor2 = new Vendor(clientID, secret_key, public_key, environment, vendor_name);
this.repository.save(vendor1);
this.repository.save(vendor2);
}
}
After of two days of searching for answers, I have lost direction on how to resolve. Any suggestions or guidance would be greatly appreciated.
Thanks,
Russ
答案1
得分: 1
以下是翻译好的部分：
不必使用Dockerfile，但您需要定义并传递给Docker容器的用户名、密码和数据库名称。您也可以在运行命令中执行此操作：
docker run --rm --name postgresContainer -d -p 54320:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=yourDatabase postgres
默认用户名是postgres，所以您不需要指定它。命令中的最后一个postgres单词是镜像的名称。
我还将yourDatabase命名为不同的名称，因为我认为postgres中有一个默认的数据库叫做postgres。
在运行上述命令后，您可以执行以下命令来检查您的数据库是否正在运行：
docker exec -it postgresContainer bash
su postgres
psql
\list
这将显示所有可用的数据库。现在键入：
\c yourDatabase
然后您已连接到postgres Docker中的数据库。随时运行创建表/选择或其他操作。
要退出，只需键入\q和exit，然后再次键入。容器将继续运行。
不要忘记更新您的Spring属性以匹配：
spring.datasource.url=jdbc:postgresql://localhost:54320/yourDatabase
spring.datasource.username=postgres
spring.datasource.password=postgres
此外，我认为您应该从您的Java代码中删除schema参数，我从未使用过它，我担心它会开始搜索'investor'数据库。
英文:
You don't have to use Dockerfile but you need to define and pass to Docker container username, password and database name. You could do that also in the run command:
docker run --rm --name postgresContainer -d -p 54320:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=yourDatabase postgres
default username is postgres so you don't need to specify that. Last postgres word in command is the name of the image
I also called yourDatabase differently because I think there is a default database in postgres called postgres.
After running the above command you can execute the following to check that your database is running:
docker exec -it postgresContainer bash
su postgres
psql
\list
This will show you all of the databases available. Now type:
\c yourDatabase
and you are connected to your database in postgres Docker. Feel free to run create tables/selects or whatever.
to exit just type \q and exit like 2 times. Container will keep running.
Don't forget to update you spring properties to match
spring.datasource.url=jdbc:postgresql://localhost:54320/yourDatabase
spring.datasource.username=postgres
spring.datasource.password=postgres
also I think you should remove the schema parameter from your java code, I never used it and I am afraid it will start searching for 'investor' database.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论