Блог

Я использую spring data jpa с hibernate в качестве поставщика JPA.

Ниже приведены версии используемых библиотек с использованием gradle в качестве средства автоматизации сборки:

 dependencies {
    compile group: 'org.springframework.boot', name: 'spring-boot-starter', version: '2.1.3.RELEASE'
    compile group: 'org.springframework.boot', name: 'spring-boot-starter-data-rest', version: '2.1.3.RELEASE'
    compile group: 'org.springframework.boot', name: 'spring-boot-starter-data-jpa', version: '2.1.3.RELEASE'
    compile group: 'org.springframework.boot', name: 'spring-boot-starter-validation', version: '2.1.3.RELEASE'
    compile group: 'org.springframework.kafka', name: 'spring-kafka', version: '2.2.4.RELEASE'
    compile group: 'org.springframework', name: 'spring-web', version: '5.1.5.RELEASE'
    runtime group: 'org.postgresql', name: 'postgresql', version: '42.2.5'
    compile group: 'org.apache.commons', name: 'commons-lang3', version: '3.8.1'
    runtimeOnly group: 'org.springframework.boot', name: 'spring-boot-devtools', version: '2.1.3.RELEASE'
}
  

Я создал новый тип пользователя, как показано ниже:

 package *;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;

import java.io.*;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;


public class ErrorMessageJsonType implements UserType {

    @Override
    public int[] sqlTypes() {
        return new int[]{Types.JAVA_OBJECT};
    }

    @Override
    public Class<ErrorMessageJson> returnedClass() {
        return ErrorMessageJson.class;
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
        final String cellContent = rs.getString(names[0]);
        if (cellContent == null) {
            return null;
        }
        try {
            final ObjectMapper mapper = new ObjectMapper();
            return mapper.readValue(cellContent.getBytes("UTF-8"), returnedClass());
        } catch (final Exception ex) {
            throw new RuntimeException("Failed to convert String to Invoice: "   ex.getMessage(), ex);
        }
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.OTHER);
            return;
        }
        try {
            final ObjectMapper mapper = new ObjectMapper();
            final StringWriter w = new StringWriter();
            mapper.writeValue(w, value);
            w.flush();
            st.setObject(index, w.toString(), Types.OTHER);
        } catch (final Exception ex) {
            throw new RuntimeException("Failed to convert Invoice to String: "   ex.getMessage(), ex);
        }
    }

    @Override
    public Object deepCopy(final Object value) throws HibernateException {
        try {
            // use serialization to create a deep copy
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            ObjectOutputStream oos = new ObjectOutputStream(bos);
            oos.writeObject(value);
            oos.flush();
            oos.close();
            bos.close();

            ByteArrayInputStream bais = new ByteArrayInputStream(bos.toByteArray());
            return new ObjectInputStream(bais).readObject();
        } catch (ClassNotFoundException | IOException ex) {
            throw new HibernateException(ex);
        }
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public Serializable disassemble(final Object value) throws HibernateException {
        return (Serializable) this.deepCopy(value);
    }

    @Override
    public Object assemble(final Serializable cached, final Object owner) throws HibernateException {
        return this.deepCopy(cached);
    }

    @Override
    public Object replace(final Object original, final Object target, final Object owner) throws HibernateException {
        return this.deepCopy(original);
    }

    @Override
    public boolean equals(final Object obj1, final Object obj2) throws HibernateException {
        if (obj1 == null) {
            return obj2 == null;
        }
        return obj1.equals(obj2);
    }

    @Override
    public int hashCode(final Object obj) throws HibernateException {
        return obj.hashCode();
    }

}
  

Вот моя реализация ErrorMessageJson:

 import org.json.simple.JSONObject;

import java.io.Serializable;
import java.util.Date;

public class ErrorMessageJson implements Serializable {

    private JSONObject jsonContent;

    public JSONObject getJsonContent() {
        return jsonContent;
    }

    public void setJsonContent(JSONObject jsonContent) {
        this.jsonContent = jsonContent;
    }

}
  

Мой класс Entity выглядит следующим образом:

 package *;

import org.hibernate.annotations.Type;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

import static javax.persistence.TemporalType.TIMESTAMP;

@Entity(name = "ERROR_MESSAGE")
public class ErrorMessage extends Auditable implements Serializable {

    private static final long serialVersionUID = 4353212440991718741L;

    public ErrorMessage() {
        super();
    }

    @Id
    @Column(name="id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "message_headers")
    @Type(type = "ErrorMessageJsonType")
    private ErrorMessageJson messageHeaders;

    @Column(name = "message_payload")
    @Type(type = "ErrorMessageJsonType")
    private ErrorMessageJson messagePayload;

    @Temporal(TIMESTAMP)
    @Column(name="created_dt")
    private Date createdDate;

    @Temporal(TIMESTAMP)
    @Column(name="modified_dt")
    private Date lastModifiedDate;

    @Column(name="message_status",nullable=false)
    private ErrorMessageStatus messageStatus;

    @Column(name="attempt",nullable=false)
    private int attempt;

    @Column(name="message_status_code",nullable=false)
    private String messageStatusCode;

    @Column(name="message_main_topic",nullable=false)
    private String messageMainTopic;

    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public ErrorMessageJson getMessageHeaders() {
        return messageHeaders;
    }

    public void setMessageHeaders(ErrorMessageJson messageHeaders) {
        this.messageHeaders = messageHeaders;
    }

    public ErrorMessageJson getMessagePayload() {
        return messagePayload;
    }

    public void setMessagePayload(ErrorMessageJson messagePayload) {
        this.messagePayload = messagePayload;
    }

    @Override
    public Date getCreatedDate() {
        return createdDate;
    }

    @Override
    public void setCreatedDate(Date createdDate) {
        this.createdDate = createdDate;
    }

    @Override
    public Date getLastModifiedDate() {
        return lastModifiedDate;
    }

    @Override
    public void setLastModifiedDate(Date lastModifiedDate) {
        this.lastModifiedDate = lastModifiedDate;
    }

    public ErrorMessageStatus getMessageStatus() {
        return messageStatus;
    }

    public void setMessageStatus(ErrorMessageStatus messageStatus) {
        this.messageStatus = messageStatus;
    }

    public int getAttempt() {
        return attempt;
    }

    public void setAttempt(int attempt) {
        this.attempt = attempt;
    }

    public String getMessageStatusCode() {
        return messageStatusCode;
    }

    public void setMessageStatusCode(String messageStatusCode) {
        this.messageStatusCode = messageStatusCode;
    }

    public String getMessageMainTopic() {
        return messageMainTopic;
    }

    public void setMessageMainTopic(String messageMainTopic) {
        this.messageMainTopic = messageMainTopic;
    }

    @Override
    public String toString() {
        return "ErrorMessage{"  
                "id="   id  
                ", messageHeaders="   messageHeaders  
                ", messagePayload="   messagePayload  
                ", createdDate="   createdDate  
                ", lastModifiedDate="   lastModifiedDate  
                ", messageStatus="   messageStatus  
                ", attempt="   attempt  
                ", messageStatusCode='"   messageStatusCode   '''  
                ", messageMainTopic='"   messageMainTopic   '''  
                ", createdBy='"   createdBy   '''  
                ", createdDate="   createdDate  
                ", lastModifiedBy='"   lastModifiedBy   '''  
                ", lastModifiedDate="   lastModifiedDate  
                '}';
    }
}
  

Мои реализации репозитория выглядят следующим образом:

 @Repository
public interface ErrorHandlerStorageRepository extends CrudRepository<ErrorMessage, Long> {
    @Modifying(clearAutomatically = true)
@Query(value = "UPDATE ERROR_MESSAGE SET message_status = 3,attempt=attempt 1  WHERE id= :id", nativeQuery = true)
int updateErrorMessageStatus(@Param("id") Long id);
}
  

Я пытаюсь создать здесь логику upsert:

 @Modifying(clearAutomatically = true)
@Query(value = "INSERT INTO public.error_message(n"  
    "    id, created_by, created_dt, modified_by, modified_dt, attempt, message_headers, message_main_topic, "  
    "message_payload, message_status, message_status_code)n"  
    "    VALUES (:id,:createdBy,:createdDate,:modifiedBy,:modifiedDt,:attempt,:messageHeaders,"  
    ":messageMainTopic,:messagePayload,:messageStatus,:messageStatusCode)"  
    "ON CONFLICT ((message_payload->'jsonContent'->>'message_id'))n"  
    "DO UPDATE SET attempt = error_message.attempt   1, ", nativeQuery = true)
int upsertErrorMessage(@Param("id") Long id, @Param("createdBy") String createdBy, @Param("createdDate") Date createdDate,
                       @Param("modifiedBy") String modifiedBy, @Param("modifiedDt") Date modifiedDt,
                       @Param("attempt") int attempt, @Param("messageHeaders") String messageHeaders,
                       @Param("messageMainTopic") String messageMainTopic,
                       @Param("messagePayload") String messagePayload,
                       @Param("messageStatus") int messageStatus,
                       @Param("messageStatusCode") String messageStatusCode);
  

Вопросы:

1. Как мне создать индекс, используя java для свойства json, как показано ниже? СОЗДАЙТЕ УНИКАЛЬНЫЙ ИНДЕКС j_msgid_idx В public.error_message((error_message->’jsonContent’->>’MessageId’));
2. Вышеупомянутый api upsert не работает как fields @Param («messagePayload») Строка messagePayload и . @Param(«MessageHeaders») Заголовки строковых сообщений — это столбцы jsonb в Postgres.

Просто использование API saveErrorMessage (ErrorMessage ошибка сообщения), предлагаемого JPA OOTB, работает нормально.