Spring boot queries wrong table

Question

I'm new to spring boot and I'm currently following this tutorial(https://www.callicoder.com/spring-boot-rest-api-tutorial-with-mysql-jpa-hibernate/). I've managed to create a api which can query table for getAll and addOne. Problem is that it doesnt use existing table on these queries. Instead my code generates new table and executes queries in that table.. How to configure my project that it uses my original table?

Database table which I'd like to use as datasource in this example. I have one row of data already stored in this table.

mysql> show columns from palauteUser;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| userUUID  | varchar(30) | NO   | PRI | NULL    |       |
| firstName | varchar(30) | YES  |     | NULL    |       |
| lastName  | varchar(30) | YES  |     | NULL    |       |
| email     | varchar(30) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

Database table generated by spring boot

mysql> show columns from palaute_user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| useruuid   | varchar(255) | NO   | PRI | NULL    |       |
| email      | varchar(255) | YES  |     | NULL    |       |
| first_name | varchar(255) | YES  |     | NULL    |       |
| last_name  | varchar(255) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

Projects 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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>fi.oppari.mysql.service</groupId>
    <artifactId>SpringService</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>SpringService</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</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-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-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>

application.properties file

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/oppariDB?useSSL=false
spring.datasource.username = root
spring.datasource.password = root

spring.jpa.show-sql=true

## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update
server.port=9090

Model class

package fi.oppari.mysql.service.SpringService.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;



@Entity
@Table(name = "palauteUser")
public class PalauteUser {

    @Id
    private String userUUID;

    @Column(name = "firstName")
    private String firstName;

    @Column(name = "lastName")
    private String lastName;

    @Column(name = "email") 
    private String email;

    public String getUserUUID() {
        return userUUID;
    }

    public void setUserUUID(String userUUID) {
        this.userUUID = userUUID;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

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

}

Repository interface:

package fi.oppari.mysql.service.SpringService.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import fi.oppari.mysql.service.SpringService.model.PalauteUser;

@Repository
public interface PalauteUserRepository extends JpaRepository<PalauteUser, String>{

}

Controller:

package fi.oppari.mysql.service.SpringService.controller;

import java.util.List;

import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import fi.oppari.mysql.service.SpringService.model.PalauteUser;
import fi.oppari.mysql.service.SpringService.repository.PalauteUserRepository;

@RestController
@RequestMapping("/api")
public class PalauteUserController {

    @Autowired
    PalauteUserRepository palauteUserRepository;

    @GetMapping("/all")
    public List<PalauteUser> getAll() {
        System.out.println("Getting all");
        return palauteUserRepository.findAll();
    }

    @PostMapping("/addOne")
    public PalauteUser addUser(@Valid @RequestBody PalauteUser user) {
        return palauteUserRepository.save(user);
    }

}

After mvn spring-boot:run command I use postman with GET localhost:9090/api/all which returns empty array. POST localhost:9090/api/addOne with json data is also success and it adds information to palaute_user table.

Any advices are welcome for this problem and I thank in advance for them! :)

Answer 1

The first recommendation as Maruf said is changing the naming strategy. That's because Spring converts palauteUser to palate_user and tries to find this table on DB.

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

The second recommendation would be getting rid of the auto ddl generation:

#spring.jpa.hibernate.ddl-auto = update

Instead, I recommend you to use a database version control such as FlyWay or Liquibase. The simplest one is FlyWay and you should add it on the classpath (maven or graddle). It bootstraps automatically when the application starts, compares what's the latest file that you have on the /resources/db/migration/ and runs all the scripts until the versions match. You'll see that Flyway creates a table in DB to keep track of the versions, files and checksums.

Answer 2

Put the following line on your application.properties

spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl