分别在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();