Can't connect to my database from IntelliJ

已回答

创建于 2024年06月19日 15:46

I have a reactive postgres database defined as follows in my application.properties file:

spring.datasource.url=r2dbc:postgresql://psql-db:5432/db
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=*****
spring.datasource.password=*****

And here is my docker-compose.yml file:

version: "3.8"

services:
  psql-db:
    image: postgres
    container_name: psql-db
    restart: always
    environment:
      - POSTGRES_USER=*****
      - POSTGRES_PASSWORD=*****
      - POSTGRES_DB=db
    ports:
      - '5432:5432'

  spring-app:
    container_name: spring-app
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - '8080:8080'
    environment:
      - SPRING_DATASOURCE_URL=jdbc:postgresql://psql-db:5432/db
      - SPRING_DATASOURCE_USERNAME=*****
      - SPRING_DATASOURCE_PASSWORD=*****
      - SPRING_JPA_HIBERNATE_DDL_AUTO=update
    depends_on:
      - psql-db

I'm trying to access it via the following properties:

But when I'm testing the connection, I get:
DBMS: PostgreSQL (no ver.)
Case sensitivity: plain=mixed, delimited=exact
The connection attempt failed.

33 条评论

Jacky Liu

创建于 2024年07月24日 12:33

I checked your project again. If you are talking about the database in https://intellij-support.jetbrains.com/hc/en-us/community/posts/19638866362514/comments/19658551063570, it's the database on your local machine but not the docker machine's database, localhost and 0.0.0.0 will point to different dabase server.

Your project is Spring JDBC-based, so no table will be auto-created. You have to create a table before running it in the docker. Only JPA has such a feature for auto table creation, take a good example here: https://www.baeldung.com/spring-data-jpa-generate-db-schema

If you want to start the app in docker, please follow the guide here:

https://www.jetbrains.com/help/idea/docker-compose.html

A simple way is just run it from the gutter run icon in file docker-compose.yml.

Ariel

创建于 2024年07月24日 14:45

Why do you think no table will be auto-created?

I'm running this project in docker via:
mvn clean package
docker compose up

Then, I'm calling the following endpoints in Postman:
http://localhost:8080/song/add
http://localhost:8080/song/list

They do work as intended, proving that a table has been created properly.

Jacky Liu

创建于 2024年07月26日 17:22

Hello,

Your project has some problems, and it is not IDEA-related.
Your data is stored and loaded from the embedded H2 database but not from the postgress db. Here is the simple way to prove it:

1. create file src/main/resources/application.properties

spring.profiles.active=dev

2. comment out application-dev.properties 's debug log

logging.level.root=DEBUG

3. open http://localhost:8080/song/list

Now, please check the output in the Java application(you can even run this application just from IDEA but not in docker):

2024-07-27T01:09:18.605+08:00 DEBUG 47079 --- [java] [ctor-http-nio-2] o.s.r2dbc.core.DefaultDatabaseClient : Executing SQL statement [SELECT SONG.* FROM SONG]
2024-07-27T01:09:18.606+08:00 DEBUG 47079 --- [java] [ctor-http-nio-2] io.r2dbc.h2.client.SessionClient : Request: SELECT SONG.* FROM SONG
2024-07-27T01:09:18.607+08:00 DEBUG 47079 --- [java] [ctor-http-nio-2] io.r2dbc.h2.client.SessionClient : Response: org.h2.result.LocalResult@206fb2da columns: 3 rows: 0 pos: -1

io.r2dbc.h2 is the embed database used by Spring JDBC, it doesn't use the postgresql db. Also as I said, the table is not auto created in postgresql, it's created in the embed H2 db.

I'm not quite sure what's the reason but since it's not related with IDEA so maybe check some other examples. Also the spring doc has sections about this: https://docs.spring.io/spring-framework/reference/data-access/jdbc/initializing-datasource.html But all the configuration is YAML (application.yml) based.

请先登录再写评论。