Spring boot2 JPA naming strategy for SQLite

Question

I want to implement spring boot 2 application with CrudRepository. I can`t figure out what going on with naming strategy in my application. When i call a method in repository (for example findAll()), it transforms the name of table in database, and query crashes. Ofcourse i tried solutions from similar questions: ImprovedNamingStrategy no longer working in Hibernate 5 Hibernate naming strategy changing table names

spring boot 2.1.8 (spring-boot-starter-data-jpa)

Spring boot completely ignores naming strategy properties from application.properties file (other properties work well)

In source code of spring boot 2.1.8 i found names of strategy props and copied them to properties:

The fully qualified class name of the physical and the implicit strategy implementations can be configured by setting the spring.jpa.hibernate.naming.physical-strategy and spring.jpa.hibernate.naming.implicit-strategy properties, respectively. Alternatively, if ImplicitNamingStrategy or PhysicalNamingStrategy beans are available in the application context, Hibernate will be automatically configured to use them. But it doesn`t work.

Thank you.

application.properties

spring.application.name=file parser
driverClassName=org.sqlite.JDBC
url=jdbc:sqlite:C:\\Java\\ParseSite\\sample.db
user=admin
pass=123

#nothing works
spring.jpa.hibernate.naming.implicit-strategy=ru.laz.db.ImplicitNamingStrategyImpl
spring.jpa.hibernate.naming.physical-strategy=ru.laz.db.PhysicalNamingStrategyImpl
spring.jpa.properties.hibernate.physical_naming_strategy = ru.laz.db.PhysicalNamingStrategyImpl

spring.jpa.properties.hibernate.dialect = ru.laz.db.SQLitePrefs.SQLiteDialect

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>
    <properties>
        <spring.boot.version>2.1.8.RELEASE</spring.boot.version>
        <java.version>1.8</java.version>
    </properties>

    <groupId>ru.laz</groupId>
    <artifactId>parse-site</artifactId>
    <version>0.1.0</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>${spring.boot.version}</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring.boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.asynchttpclient</groupId>
            <artifactId>async-http-client</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.jsoup</groupId>
            <artifactId>jsoup</artifactId>
            <version>1.11.3</version>
        </dependency>
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.16.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>${spring.boot.version}</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.9.10</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

NewsBlockRepo.java

@Repository
public interface NewsBlockRepo extends CrudRepository<NewsBlock,Integer> {

    //only this method works ok
    @Modifying
    @Transactional
    @Query(value = "insert or replace into news_blocks (date, title, url, body, sent) values (:date, :title, :url, :body, :sent)", nativeQuery = true)
    void insertOrIgnore(@Param("date") String date, @Param("title") String title, @Param("url") String url, @Param("body") String body, @Param("sent") int sent);
}

NewsBlock.java

package ru.laz.db;


import javax.persistence.*;

@Entity
@Table(name = "news_blocks")
public class NewsBlock {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
        private int id;
    @Column(name = "date")
        private String date = "";//for unique constaint in sqlite
    @Column(name = "title")
        private String title = "";
    @Column(name = "url")
        private String url = "";
    @Column(name = "body")
        private String body = "";
    @Column(name = "sent")
        private int sent = 0;

    public long getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }
    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getBody() {
        return body;
    }

    public void setBody(String body) {
        this.body = body;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public int getSent() {
        return sent;
    }

    public void setSent(int sent) {
        this.sent = sent;
    }

    @Override
    public boolean equals(Object o) {
        if (o == this) return true;
        if (!(o instanceof NewsBlock)) {
            return false;
        }
        NewsBlock nb = (NewsBlock) o;
        return this.url.equals(nb.getUrl()) && this.title.equals(nb.getTitle()) && this.body.equals(nb.getBody() );
    }

    @Override
    public String toString() {
        return "date: " + date + " title: "+title + " url: " + url + " text: " + body;
    }
}

my PhysicalNamingStrategyImpl

    public class PhysicalNamingStrategyImpl extends PhysicalNamingStrategyStandardImpl implements Serializable {

        public static final PhysicalNamingStrategyImpl INSTANCE = new PhysicalNamingStrategyImpl();

        @Override
        public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
            String nameModified = name.getText();
            // Do whatever you want with the name modification
            return new Identifier(nameModified, name.isQuoted());
        }
}

and even ImplicitNamingStrategyImpl

public class ImplicitNamingStrategyImpl extends ImplicitNamingStrategyJpaCompliantImpl {

        @Override
    protected Identifier toIdentifier(String stringForm, MetadataBuildingContext buildingContext) {
        return super.toIdentifier(stringForm, buildingContext);
    }
}

finaly controller to call method from repository

    @RequestMapping("/getUnsent")
    public String getUnsent() throws Exception {
        return objectMapper.writeValueAsString(newsBlockRepo.findAll());
    }

Error:

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such column: newsblock0_.id)

Answer 1

The error is no such column: newsblock0_.id. Clearly it says in the news_block block table, there is no id which means, spring-boot considered the id in your Entity class since you declare it. Entity class is more or less equal to your table.

It expects all variable that you declare must be in the table, But sometimes you declare a variable which is no need to be in the table. So you should denote @Transient.

@Transient annotation is used to indicate that a field is not to be persisted in the database.