CODEWITHSUNDEEP
javaspring-bootspring-data-jpaspring-boot-jpa
Kevin Valdez
Kevin Valdez

Reputation: 389

Prevent @Entity from re creating a database table - Spring Boot

I'm kinda new to spring boot data jpa, as far as I know @Entity is used to represent a database table in the application, for this project im using spring-boot 2.2.5.RELEASE and H2 as in memory database.

So far I've got this.

inside of resources/data.sql

CREATE TABLE CURRENCY (
  id INT AUTO_INCREMENT  PRIMARY KEY,
  name VARCHAR(250) NOT NULL,
  code VARCHAR(250) NOT NULL
);

CREATE TABLE EXCHANGE_CURRENCY (
  id INT AUTO_INCREMENT  PRIMARY KEY,
  IdFx1 INT NOT NULL,
  IdFx2 INT NOT NULL,
  equivalent DECIMAL NOT NULL,
  FOREIGN KEY (IdFx1) REFERENCES CURRENCY(id),
  FOREIGN KEY (IdFx2) REFERENCES CURRENCY(id)
);

My entity class

import javax.persistence.*;

@Entity
@Table(name = "CURRENCY")
public class Currency {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String code;
}

Repository

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CurrencyRepository extends CrudRepository<Currency, Long> {

    @Query("SELECT c FROM CURRENCY WHERE c.code LIKE %:code%")
    List<Currency> findCurrencyByCode(@Param("code") String code);

}

And service

import com.currency.canonical.models.Currency;
import com.currency.canonical.request.ExchangeValueRequest;
import com.currency.canonical.response.ExchangeValueResponse;
import com.currency.dao.CurrencyService;
import com.currency.dao.repository.CurrencyRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class CurrencyConversionServiceImpl implements CurrencyConversionService {

    Logger logger = LoggerFactory.getLogger(CurrencyConversionServiceImpl.class);

    @Autowired
    private CurrencyRepository currencyRepository;

    @Override
    public ExchangeValueResponse performCurrencyConversion(ExchangeValueRequest request) {
        final long initialTime = System.currentTimeMillis();

        ExchangeValueResponse objExchangeValueResponse = new ExchangeValueResponse();

        try {
            List<Currency> currencyList = currencyRepository.findCurrencyByCode(request.getMonedaOrigen());
            currencyList.forEach(System.out::println);

        } catch (Exception e) {

        }

        return objExchangeValueResponse;
    }
}

When executing the app I got this error

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Initialization of bean failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #2 of URL [file:/C:/Users/Usuario/Documents/IdeaProjects/currency-converter/currency-converter-resource/target/classes/data.sql]: CREATE TABLE CURRENCY ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(250) NOT NULL, code VARCHAR(250) NOT NULL ); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Tabla "CURRENCY" ya existe
Table "CURRENCY" already exists; SQL statement:
CREATE TABLE CURRENCY ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(250) NOT NULL, code VARCHAR(250) NOT NULL ) [42101-200]

Why is the @Entity trying to re create a table that is supposed to only represent, and is there a way to disable this?

Upvotes: 3

Views: 9190

Answers (3)

Velu
Velu

Reputation: 1901

JPA has features for DDL generation, and these can be set up to run on startup against the database. This is controlled through two external properties:

  1. spring.jpa.generate-ddl (boolean) switches the feature on and off and is vendor independent.
  2. spring.jpa.hibernate.ddl-auto (enum) is a Hibernate feature that controls the behavior in a more fine-grained way.

Reference spring boot JPA docs

How does the spring.jpa.hibernate.ddl-auto property work? you can refer below config's based on environment.

Upvotes: 3

Tanuj
Tanuj

Reputation: 2260

The issue here is the name of the file data.sql, as suggested by spring there are two important files that will help you control the database creation, these are namely

  • schema.sql - This file as the name suggest hold are the DDL statements required of creating schema of the database.
  • data.sql - This file will hold all the DML statements required to populate the initial database correctly.

Issue with your application is that the DDL is specified in the data.sql file, this confuses spring and it tries to execute DDL as considering it's DML.

The solution to you issue is simply rename data.sql to schema.sql and spring will handle the rest.

Also I found one more issue with the repository, since you are using a custom Query @Query("SELECT code FROM CURRENCY WHERE code LIKE %:code%") would likely cause and error when the repository is initiated because java entity names are case-sensitive. You can solve this issue in following ways -

A. Since its a like query spring repository already supports its and you can rewrite method like -

List<Currency> findByCodeLike(@Param("code") String code);

B. Use JPQL queries, same as you did in the code except changing the table name since JPA entity names are case-sensitive

@Query("SELECT code FROM Currency WHERE code LIKE %:code%")
List<Currency> findCurrencyByCode(@Param("code") String code);

C. If you still wanna keep the current query with table name as in db schema "CURRENCY", then you can use nativeQuery flag in @Query to let spring know that you are using native queries and not JPQL -

@Query(value = "SELECT code FROM CURRENCY WHERE code LIKE %:code%", nativeQuery = true)
List<Currency> findCurrencyByCode(@Param("code") String code);

Hope this helps!

Upvotes: 4

backdoor
backdoor

Reputation: 901

An error occurs because when you run your application it tries to recreate the table, as a workaround for the same is as follow:

  1. Add a drop table if exists [tablename] in your data.sql before you create the table.
  2. Change the statement from CREATE TABLE to CREATE TABLE IF NOT EXISTS

Upvotes: 1

Related Questions