Spring Boot + Postgresql not creating table when running app

Question

I've looked at a solution1, solution2, and tried the spring tutorial here.

I haven't been able to resolve the problem. As you can see hibernate tries to create the table Hibernate: create table users (id bigserial not null, email varchar(255), name varchar(255), password varchar(255), role varchar(255), primary key (id)), but I never see it in my database. I'm able to manually create the table myself so it doesn't seem to be an issue with postgresql. I have spring security in this project, I'm not sure if that might be causing a conflict.

Also, I think the problem is in the connection. I tried changing the credentials to a fake username/password and hibernate was able to run everything without an error. Any suggestions would be appreciated.

system info:

psql (PostgreSQL) 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)

spring console:

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::                (v3.1.3)

2023-09-06T11:01:28.119-04:00  INFO 75478 --- [  restartedMain] c.a.springblog.SpringBlogApplication     : Starting SpringBlogApplication using Java 20.0.2.1 with PID 75478 (/home/skynet/IdeaProjects/spring-blog/target/classes started by skynet in /home/skynet/IdeaProjects/spring-blog)
2023-09-06T11:01:28.119-04:00  INFO 75478 --- [  restartedMain] c.a.springblog.SpringBlogApplication     : No active profile set, falling back to 1 default profile: "default"
2023-09-06T11:01:28.121-04:00  INFO 75478 --- [  restartedMain] .s.b.d.c.l.DockerComposeLifecycleManager : Using Docker Compose file '/home/skynet/IdeaProjects/spring-blog/compose.yaml'
2023-09-06T11:01:28.332-04:00  INFO 75478 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2023-09-06T11:01:28.334-04:00  INFO 75478 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 1 ms. Found 1 JPA repository interfaces.
2023-09-06T11:01:28.369-04:00  INFO 75478 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2023-09-06T11:01:28.369-04:00  INFO 75478 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2023-09-06T11:01:28.369-04:00  INFO 75478 --- [  restartedMain] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.12]
2023-09-06T11:01:28.372-04:00  INFO 75478 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2023-09-06T11:01:28.372-04:00  INFO 75478 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 92 ms
2023-09-06T11:01:28.377-04:00  INFO 75478 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2023-09-06T11:01:28.387-04:00  INFO 75478 --- [  restartedMain] com.zaxxer.hikari.pool.HikariPool        : HikariPool-2 - Added connection org.postgresql.jdbc.PgConnection@68990f0c
2023-09-06T11:01:28.388-04:00  INFO 75478 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.
2023-09-06T11:01:28.388-04:00  INFO 75478 --- [  restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:postgresql://127.0.0.1:32790/mydatabase'
2023-09-06T11:01:28.398-04:00  INFO 75478 --- [  restartedMain] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2023-09-06T11:01:28.399-04:00  INFO 75478 --- [  restartedMain] o.h.b.i.BytecodeProviderInitiator        : HHH000021: Bytecode provider name : bytebuddy
2023-09-06T11:01:28.399-04:00  INFO 75478 --- [  restartedMain] o.s.o.j.p.SpringPersistenceUnitInfo      : No LoadTimeWeaver setup: ignoring JPA class transformer
2023-09-06T11:01:28.403-04:00  INFO 75478 --- [  restartedMain] o.h.b.i.BytecodeProviderInitiator        : HHH000021: Bytecode provider name : bytebuddy
2023-09-06T11:01:28.411-04:00  INFO 75478 --- [  restartedMain] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
Hibernate: drop table if exists users cascade
2023-09-06T11:01:28.412-04:00  WARN 75478 --- [  restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Warning Code: 0, SQLState: 00000
2023-09-06T11:01:28.412-04:00  WARN 75478 --- [  restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper   : table "users" does not exist, skipping
Hibernate: create table users (id bigserial not null, email varchar(255), name varchar(255), password varchar(255), role varchar(255), primary key (id))
2023-09-06T11:01:28.420-04:00  INFO 75478 --- [  restartedMain] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2023-09-06T11:01:28.438-04:00  WARN 75478 --- [  restartedMain] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2023-09-06T11:01:28.471-04:00  WARN 75478 --- [  restartedMain] .s.s.UserDetailsServiceAutoConfiguration : 

Using generated security password: e99bc0d2-86c6-4590-b802-58eb8b016bcc

This generated password is for development use only. Your security configuration must be updated before running your application in production.

2023-09-06T11:01:28.476-04:00  INFO 75478 --- [  restartedMain] o.s.s.web.DefaultSecurityFilterChain     : Will secure any request with [org.springframework.security.web.session.DisableEncodeUrlFilter@55209b90, org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter@7be130e3, org.springframework.security.web.context.SecurityContextHolderFilter@1f0658f2, org.springframework.security.web.header.HeaderWriterFilter@2a6b2b65, org.springframework.security.web.csrf.CsrfFilter@63322287, org.springframework.security.web.authentication.logout.LogoutFilter@3cf107f0, org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter@1b829270, org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter@6f8dfae7, org.springframework.security.web.authentication.ui.DefaultLogoutPageGeneratingFilter@747f2cfd, org.springframework.security.web.authentication.www.BasicAuthenticationFilter@2f78c877, org.springframework.security.web.savedrequest.RequestCacheAwareFilter@6dd2678f, org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter@1c4eca08, org.springframework.security.web.authentication.AnonymousAuthenticationFilter@1d2d6975, org.springframework.security.web.access.ExceptionTranslationFilter@6782dde0, org.springframework.security.web.access.intercept.AuthorizationFilter@565f0619]
2023-09-06T11:01:28.480-04:00  INFO 75478 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2023-09-06T11:01:28.484-04:00  INFO 75478 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2023-09-06T11:01:28.486-04:00  INFO 75478 --- [  restartedMain] c.a.springblog.SpringBlogApplication     : Started SpringBlogApplication in 0.378 seconds (process running for 18.874)
2023-09-06T11:01:28.487-04:00  INFO 75478 --- [  restartedMain] .ConditionEvaluationDeltaLoggingListener : Condition evaluation unchanged

application.properties:

spring.datasource.url=jdbc:postgresql://localhost:5432/spring_blog
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.database=POSTGRESQL
spring.jpa.show-sql=true


spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.maximum-pool-size=5

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.generate-ddl=true

I also tried a simple config like this:

spring.datasource.url=jdbc:postgresql://localhost:5432/spring_blog
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true

entity:

package com.myblogsite.springblog.entities;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity(name = "users")
public class Users {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String email;
    private String password;
    private String role;

    public Users(){

    }

    public Users(Long id, String name, String email, String password, String role){
        this.id = id;
        this.name = name;
        this.email = email;
        this.password = password;
        this.role = role;
    }

    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return this.email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return this.password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getRole() {
        return this.role;
    }

    public void setRole(String role) {
        this.role = role;
    }
}

repository interface:

package com.myblogsite.springblog.repositories;

import com.myblogsite.springblog.entities.Users;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface IUserRepository extends CrudRepository<Users, Long> {
}

rest controller:

package com.myblogsite.springblog.http.controllers;

import com.myblogsite.springblog.repositories.IUserRepository;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api/users")
public class UserController extends BaseController{

    private final IUserRepository userRepository;

    public UserController(IUserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @GetMapping
    public ResponseEntity<?> getAllUsers() {
        return ResponseEntity.ok(userRepository.findAll());
    }
}

Last but not least

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>3.1.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.myblogsite</groupId>
    <artifactId>spring-blog</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring-blog</name>
    <description>spring-blog</description>
    <properties>
        <java.version>20</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-mail</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.session</groupId>
            <artifactId>spring-session-core</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-docker-compose</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </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>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Edit:

I added this service to check if my connection with the database is working.

package com.myblogsite.springblog.database;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class DatabaseConnectionService {

    @Autowired
    private DataSource dataSource;

    public boolean isDatabaseConnectionValid() {
        try (Connection connection = dataSource.getConnection()) {
            // Execute a simple query to verify the connection.
            String sql = "SELECT 1";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.executeQuery();
                return true; // Connection is valid.
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // Connection is not valid.
        }
    }
}

when I call it in my controller and ping that endpoint it will return an empty array and in the console it says Database connection is valid.

this is what I added to the controller above:

@GetMapping
    public ResponseEntity<?> getAllUsers() {
        boolean isConnectionValid = databaseConnectionService.isDatabaseConnectionValid();

        if (isConnectionValid) {
            System.out.println("Database connection is valid.");
        } else {
            System.out.println("Database connection is not valid.");
        }
        return ResponseEntity.ok(userRepository.findAll());
    }

{insert going crazy emoji here}

TeaBee · Accepted Answer · 2023-09-06 23:11:49Z

1

It seems you have h2 on the classpath. So, your Datasource is pointing to your H2 DB and not to Postgre (as spring will use h2 embedded if it is on the classpath). Comment it out in your pom or scope it to test.

answered Sep 6, 2023 at 23:11

TeaBee

998 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pachuca Over a year ago

can I use spring.h2.console.enabled=false in my app.properites?

Pachuca Over a year ago

You are correct sir, thankfully I just started the project so I was able to make a new one without using h2db. I'll have to cross this bridge again later if I decide to add h2, but for now this answers my problem. Thank you so much!

Adrian Lagartera · Accepted Answer · 2023-09-06 15:44:42Z

0

You need add the @Table with the params you put on the @Entity

From @Entity(name = "users")

to

@Table(name = "users")
@Entity

https://jakarta.ee/specifications/persistence/2.2/apidocs/javax/persistence/table

And I didn't saw but you can't use users in postgre, since is reserved usersId is reserved too

edited Sep 6, 2023 at 15:44

answered Sep 6, 2023 at 15:36

Adrian Lagartera

5061 gold badge7 silver badges21 bronze badges

3 Comments

Pachuca Over a year ago

I already tried that too. It was suggested in another solution, but there was no change. I even tried with just @Entity and still nothing.

Belayer Over a year ago

@AdrianLagartera. In Postgres user is a reserved word. But users nor userId is reserved.

Pachuca Over a year ago

I think the problem is in the connection. I just tried changing the credentials to a fake username/password and hibernate was able to run everything without an error.

siddheshwaran muthusamy · Accepted Answer · 2023-11-10 12:07:32Z

0

Use this configuration application.yml

spring:
  datasource:
    url: jdbc:jdbc:postgresql://localhost:5432/spring_blog
    username: postgres
    password: postgres
    driverClassName: org.postgresql.Driver
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
      ddl-auto: update
    show-sql: true

Entiry Details

@Entity
@Table(name = "users")

answered Nov 10, 2023 at 12:07

siddheshwaran muthusamy

445 bronze badges

Collectives™ on Stack Overflow

Spring Boot + Postgresql not creating table when running app

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related