1

The project is currently on spring-boot 2.27(and it's corresponding spring-data-jdbc version) When upgrading the version in maven and running I receive the following:

bad SQL grammar [INSERT INTO "PLAYLIST" ("allocated_funds", "file_uri", "received") VALUES (?, ?, ?)]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "PLAYLIST" does not exist

org.springframework.data.relational.core.conversion.DbActionExecutionException: Failed to execute DbAction.InsertRoot(entity=Playlist(id=null, fileUri=gs://playlists/success.csv, received=2020-05-26T09:34:45.778327, allocatedFunds=20000))

I am using flyway to manage my db and it is being executed by the test:

09:34:43.868 [main] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
09:34:43.897 [main] INFO  o.f.c.i.database.DatabaseFactory - Database: jdbc:postgresql://localhost:32883/test (PostgreSQL 12.3)
09:34:43.947 [main] INFO  o.f.core.internal.command.DbValidate - Successfully validated 4 migrations (execution time 00:00.019s)
09:34:43.970 [main] INFO  o.f.c.i.s.JdbcTableSchemaHistory - Creating Schema History table "public"."flyway_schema_history" ...
09:34:44.010 [main] INFO  o.f.core.internal.command.DbMigrate - Current version of schema "public": << Empty Schema >>
INFO  o.f.core.internal.command.DbMigrate - Migrating schema "public" to version 1 - init
INFO  o.f.core.internal.command.DbMigrate - Migrating schema "public" to version 2 - playlist entry
INFO  o.f.core.internal.command.DbMigrate - Migrating schema "public" to version 3 - splits
INFO  o.f.core.internal.command.DbMigrate - Migrating schema "public" to version 4 - allocated funds
INFO  o.f.core.internal.command.DbMigrate - Successfully applied 4 migrations to schema "public" (execution time 00:00.255s)

The entity looks like:

import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

@Data
@Builder
@AllArgsConstructor
@Table("PLAYLIST")
public class Playlist implements Serializable {
    @Id
    private Long id;

    @Column("file_uri")
    private String fileUri;

    private LocalDateTime received;

    @Column("allocated_funds")
    private BigDecimal allocatedFunds;
}

create script looks like:

CREATE TABLE PLAYLIST(id BIGSERIAL PRIMARY KEY, file_uri VARCHAR(400) NOT NULL, received TIMESTAMP NOT NULL);
ALTER TABLE PLAYLIST ADD COLUMN allocated_funds DECIMAL(10,2) NOT NULL;

Is there some underlying change WRT datetime or bigdecimal handling or lack of ID?

  • Update - adding repository code

import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import treeline.entities.Playlist;

public interface PlaylistRepository extends CrudRepository<Playlist, Long> {
    @Query("SELECT * FROM Playlist p WHERE p.file_uri = :fileUri")
    Playlist findByFileUri(@Param("fileUri") final String fileUri);
}
user3465651
  • 706
  • 1
  • 4
  • 22
  • Quoted object names are case sensitive, so likely you have a table `playlist` (lowercase), but you're inserting into `PLAYLIST`, which doesn't exist. Disable quoting, or try removing the `@Table` annotation, or put the table in in lowercase. – Mark Rotteveel May 26 '20 at 07:54
  • The create statement for the table playlist is in uppercase as has been provided – user3465651 May 26 '20 at 08:00
  • 1
    Yes, but there it is unquoted, and unquoted object names are case insensitive, which means that PostgreSQL will store it in **lowercase** (other database systems for example store unquoted names in **UPPERCASE**). This means that `PLAYLIST` is actually `playlist`, and `"PLAYLIST"` will not match. – Mark Rotteveel May 26 '20 at 08:03
  • Changed to lower case in `@Table` and that has sorted it – user3465651 May 26 '20 at 08:15

1 Answers1

0

Looks like

INSERT INTO "PLAYLIST"

is the problem. Can you try without the quotes instead? Just write the query as follows

INSERT INTO PLAYLIST
DeityWarrior
  • 716
  • 1
  • 6
  • 5
  • The spring-data CrudRepository is generating the query. - Added repository code used to save – user3465651 May 26 '20 at 07:57
  • You pasted the select query not the insert. Anyway, the query is in your control. I am suggesting you remove the quotes from the query. – DeityWarrior May 26 '20 at 08:02
  • The `save` functionality is part of CrudRepository. I'm just showing what I have for that entity. I have sorted out the issue tho. Changed the table names to lower case – user3465651 May 26 '20 at 08:24