postgres - Neethahiremath/Wiki GitHub Wiki

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>

spring:
  application:
    name: postgres
  dataSource:
    initialization-mode: always
    url: jdbc:postgresql://localhost:5432/postgres
    username: postgres
  jpa:
    hibernate:
      ddl-auto: none
      show-sql: true
    properties:
      hibernate:
        temp:
          use_jdbc_metadata_defaults: false
        dialect: org.hibernate.dialect.PostgreSQLDialect

---
spring:
  profiles: dev
  datasource:
    url: jdbc:postgresql://URL:5432/database
    username: username

    @Bean
    @Primary
    public HikariDataSource dataSource() {

        return DataSourceBuilder.create()
                .type(HikariDataSource.class)
                .url(url)
                .username(username)
                .password(password)
                .build();
    } 

@Modifying
@Transactional
@Query(value = "UPDATE db.animal  SET status = :status WHERE id= :id AND name= :name", nativeQuery = true)
public int updateRollOverStatus(@Param("id") String id,
                                 @Param("name") String name, @Param("status")Boolean status);

package com.lowes.capacity.utility;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
/**
 * To convert Enum to PostgreSQLENUMType
 */
public class PostgreSQLEnumType extends org.hibernate.type.EnumType {
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
            throws HibernateException, SQLException {
        st.setObject(index, value != null ? ((Enum) value).name() : null, Types.OTHER);
    }
}

package com.lowes.capacity.model.entity;
import com.lowes.capacity.utility.FulfilmentType;
import com.lowes.capacity.utility.NodeStatus;
import com.lowes.capacity.utility.PostgreSQLEnumType;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
import javax.persistence.*;
import java.io.Serializable;
import java.sql.Date;
import java.sql.Timestamp;

@Entity
@Table(name = "table")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@TypeDef(name = "pgsql_enum",
        typeClass = PostgreSQLEnumType.class)
public class CapacityAudit implements Serializable {
    private static final long serialVersionUID = -2343243243242432341L;
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "id_generator")
    @SequenceGenerator(name = "id_generator", sequenceName = "seq", allocationSize = 1)
    @Column(name = "id")
    private Long Id;
    @Column(name = "animal_id")
    private String animalId;
    @Column(name = "type")
    @Enumerated(EnumType.STRING)
    @Type(type = "pgsql_enum")
    private Type type;
    @Column(name = "date")
    private Date date;

    @Column(name = "created_timestamp")
    private Timestamp createdTimestamp;
    @Column(name = "status")
    @Enumerated(EnumType.STRING)
    @Type(type = "pgsql_enum")
    private status status;
}

@Bean
@Primary
public HikariDataSource dataSource() {
    return new HikariDataSource(getHikariConfig());
}
private HikariConfig getHikariConfig() {
    HikariConfig hikaConfig = new HikariConfig();
    hikaConfig.setJdbcUrl(url);
    hikaConfig.setUsername(username);
    hikaConfig.setPassword(password);
    hikaConfig.setMaximumPoolSize(maxPoolSize);
    return hikaConfig;
}

 @Override
    public List<Entity> read() {

        ZonedDateTime zonedDateTime = ZonedDateTime.now(ZoneOffset.UTC).minusHours(5);
        LocalDateTime cutOffTime = zonedDateTime.toLocalDateTime().minusHours(cutOffHours);
        Instant start = Instant.now();
        List<Entity> entityList = null;
        List<Entity> entityList = new ArrayList<>();
        try {

            entityList = repository.
                    get(cutOffTime, closed, type);
            Pageable pageable = PageRequest.of(0, 200);
            Page<Entity> old = repository.
                    get(cutOffTime, closed, type, pageable);

            while(!old.isEmpty()){
                pageable = pageable.next();
               entityList.addAll(old.getContent());
                old = repository.
                        get(cutOffTime, closed, type, pageable);
                return entityList;
            }

            if(entityList==null || entityList.size()==0)
                return null;

            log.info("Executed select get statement : {} in {} ms", "Postgres Modify query",
                    java.time.Duration.between(start, Instant.now()).toMillis());
        } catch (NoNodeAvailableException | QueryExecutionException ex) {
            log.error("Failed to execute query during get(): event:{},exception:",
                    ex);
        }

 @Query(value = "SELECT  * FROM table WHERE createdt <= :cutofftime  AND  " +
            "closed=:closedstatus AND type =:type ",
            countQuery = "SELECT  count(*) FROM table WHERE createdt <= :cutofftime  AND" +
                    "  closed=:closedstatus AND type =:type ",nativeQuery = true)
    @Transactional
    Page<Entity> get(
            @Param("cutofftime") LocalDateTime cutofftime,
            @Param("closedstatus") String closedstatus, @Param("type") int type,Pageable pageable);