分别在mysql和postgreSQL中存储json对象

1.添加maven依赖

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

2. 申明类型

@TypeDefs({
    @TypeDef(name = "json", typeClass = JsonStringType.class),
    @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
@MappedSuperclass
public class BaseEntity {
    //Code omitted for brevity
}

MYSQL

Entity

@Entity(name = "Event")
@Table(name = "event")
public class Event extends BaseEntity {
 
    @Type(type = "json")
    @Column(columnDefinition = "json")
    private Location location;
 
    public Location getLocation() {
        return location;
    }
 
    public void setLocation(Location location) {
        this.location = location;
    }
}
 
@Entity(name = "Participant")
@Table(name = "participant")
public class Participant extends BaseEntity {
 
    @Type(type = "json")
    @Column(columnDefinition = "json")
    private Ticket ticket;
 
    @ManyToOne
    private Event event;
 
    public Ticket getTicket() {
        return ticket;
    }
 
    public void setTicket(Ticket ticket) {
        this.ticket = ticket;
    }
 
    public Event getEvent() {
        return event;
    }
 
    public void setEvent(Event event) {
        this.event = event;
    }
}

测试

final AtomicReference<Event> eventHolder = new AtomicReference<>();
final AtomicReference<Participant> participantHolder = new AtomicReference<>();
 
doInJPA(entityManager -> {
    Event nullEvent = new Event();
    nullEvent.setId(0L);
    entityManager.persist(nullEvent);
 
    Location location = new Location();
    location.setCountry("Romania");
    location.setCity("Cluj-Napoca");
 
    Event event = new Event();
    event.setId(1L);
    event.setLocation(location);
    entityManager.persist(event);
 
    Ticket ticket = new Ticket();
    ticket.setPrice(12.34d);
    ticket.setRegistrationCode("ABC123");
 
    Participant participant = new Participant();
    participant.setId(1L);
    participant.setTicket(ticket);
    participant.setEvent(event);
 
    entityManager.persist(participant);
 
    eventHolder.set(event);
    participantHolder.set(participant);
});

INSERT INTO event (location, id)
VALUES (NULL(OTHER), 0)
 
INSERT INTO event (location, id)
VALUES ('{"country":"Romania","city":"Cluj-Napoca"}', 1)
 
INSERT INTO participant (event_id, ticket, id)
VALUES (1, {"registrationCode":"ABC123","price":12.34}, 1)

Event event = entityManager.find(Event.class, eventHolder.get().getId());
 
assertEquals("Cluj-Napoca", event.getLocation().getCity());
 
Participant participant = entityManager.find(
    Participant.class, participantHolder.get().getId());
assertEquals("ABC123", participant.getTicket().getRegistrationCode());

List<String> participants = entityManager.createNativeQuery(
    "SELECT p.ticket -> \"$.registrationCode\" " +
    "FROM participant p " +
    "WHERE JSON_EXTRACT(p.ticket, \"$.price\") > 1 ")
.getResultList();

event.getLocation().setCity("Constanța");
entityManager.flush();

UPDATE event
SET location = '{"country":"Romania","city":"Constanța"}'
WHERE id = 1

POSTGRESQL

@Type(type = "jsonb")
@Column(columnDefinition = "json")
private Location location;
 
@Type(type = "jsonb")
@Column(columnDefinition = "json")
private Ticket ticket;

List<String> participants = entityManager.createNativeQuery(
    "SELECT p.ticket ->>'registrationCode' " +
    "FROM participant p " +
    "WHERE p.ticket ->> 'price' > '10'")
.getResultList();

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private Location location;
 
@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private Ticket ticket;

List<String> participants = entityManager.createNativeQuery(
    "SELECT jsonb_pretty(p.ticket) " +
    "FROM participant p " +
    "WHERE p.ticket ->> 'price' > '10'")
.getResultList();

转载来源：https://blog.csdn.net/ninja_/article/details/108916530

原文地址：http://www.cnblogs.com/hefeng2014/p/16876586.html