[FIXED] Pom.xml and Properties for org.springframework.boot 2.1.8.RELEASE Redshift Cluster V1.0.10393

Issue

The first error I'm getting

Invalid operation: SQL command "drop sequence if exists hibernate_sequence" not supported

Further down in the exceptions I see the following. I assume I have to change the way indexes are incremented in hibernate_sequence.

Caused by: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: SQL command "create sequence hibernate_sequence start 1 increment 1" not supported.;
    ... 49 common frames omitted
2019-10-07 14:02:30,237 WARN  [main] org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl: GenerationTarget encountered exception accepting command : Error executing DDL "create sequence hibernate_sequence start 1 increment 1" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create sequence hibernate_sequence start 1 increment 1" via JDBC Statement

While building a java Spring Boot application, seeking to connect to and create a new redshift DB Cluster V1.0.10393.

What are the POM and properties files supposed to look like? I'm transitioning from a working PostgreSQL database to a redshift.

Current Properties

spring.datasource.driver-class-name=com.amazon.redshift.jdbc42.Driver
spring.datasource.url=jdbc:redshift://redshift-cluster-1.cwirererbv4xb.us-east-2.redshift.amazonaws.com:5439/db
spring.datasource.username=awsuser
spring.datasource.password=werwerwerew
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQL9Dialect
spring.datasource.hikari.connectionTimeout=20000
spring.datasource.hikari.maximumPoolSize=5
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.open-in-view=false
spring.jpa.hibernate.ddl-auto=create

Current POM

<?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>com.lifograph</groupId>
    <artifactId>formD</artifactId>
    <version>1.0</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.8.RELEASE</version>
    </parent>



    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-rest</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
        </dependency>


        <dependency>
            <groupId>com.amazon.redshift</groupId>
            <artifactId>redshift-jdbc42-no-awssdk</artifactId>
            <version>1.2.10.1009</version>
        </dependency>

        <dependency>
            <groupId>com.opencsv</groupId>
            <artifactId>opencsv</artifactId>
            <version>4.6</version>
        </dependency>
        <dependency>
            <groupId>javax.interceptor</groupId>
            <artifactId>javax.interceptor-api</artifactId>
            <version>1.2.2</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-javadoc-plugin</artifactId>
                <configuration>
                    <additionalparam>-Xdoclint:none</additionalparam>
                </configuration>
            </plugin>

        </plugins>

    </build>

    <repositories>

        <repository>
            <id>redshift</id>
            <url>http://redshift-maven-repository.s3-website-us-east-1.amazonaws.com/release</url>
        </repository>
        <repository>
            <id>in-project</id>
            <name>custom jars</name>
            <url>file://${project.basedir}/lib</url>
        </repository>
        <repository>
            <id>spring-releases</id>
            <url>https://repo.spring.io/libs-release</url>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-releases</id>
            <url>https://repo.spring.io/libs-release</url>
        </pluginRepository>

    </pluginRepositories>
</project>

Regards Conteh

Solution

The errors I was getting were occurring because I was using generated ID

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@javax.persistence.Id
private long id;

I've replaced those with @EmbeddedId. As well as named indexes were removed.

Answered By - conteh
Answer Checked By - Mildred Charles (JavaFixing Admin)