Örnek - Insert
INSERT INTO room_categories (name, code, metadata)
VALUES (?, ?, ? ::jsonb );
Örnek - Update
Elimizde şöyle bir kod
olsun@Entity
@Table(name = “room_categories”)
@TypeDef(name = “jsonb”, typeClass = JsonBinaryType.class)
public class RoomCategory extends AbstractEntity implements Serializable {
private String name;
private String code;
@Type(type = "jsonb")
@Column(columnDefinition = "json")
private Metadata metadata;
}
public class Metadata implements Serializable {
private String field1;
private String field2;
}
int updateCount = entityManager.createNativeQuery("""
UPDATE
room_categories
SET
metadata = :metadata
WHERE
code = :code AND
metadata ->> 'field1' is null
""")
.setParameter("code ", "123-ABC")
.setParameter(
"metadata",
new Metadata()
.setField1("ABC")
.setField2("123")
)
.executeUpdate();
Hata
şöyle. Metadata sınıfı Serializable olduğu için Hibernate sınıfı bytea tipine çevirmeye çalışıyor.
ERROR: column “metadata” is of type jsonb but expression is of type bytea Hint: You will need to rewrite or cast the expression.
int updateCount = entityManager.createNativeQuery("""
UPDATE
room_categories
SET
metadata = :metadata
WHERE
code = :code AND
metadata ->> 'field1' is null
""")
.setParameter("code ", "123-ABC")
.unwrap(org.hibernate.query.Query.class) // Burada Hibernate tipine çevir
.setParameter(
"metadata",
new Metadata()
.setField1("ABC")
.setField2("123"),
JsonBinaryType.INSTANCE // setParameter metoduna JsonBinary tip olduğunu belirt
)
.executeUpdate();
First, we had to unwrap the JPA Query to a Hibernate org.hibernate.query.Query and call the setParameter method that takes a Hibernate Type instance.
Now, Hibernate will know that the metadata parameter needs to be handled by the JsonBinaryType, and not by the SerializableType.
Örnek - Select
Şöyle
yaparız. Burada tabloyu dolduruyoruz.
CREATE TABLE item (
id INT8 NOT NULL,
jsonb_content JSONB,
PRIMARY KEY (id)
)
INSERT INTO item (id, jsonb_content) VALUES (1,
'{"top_element_with_set_of_values":["TAG1","TAG2","TAG11","TAG12","TAG21","TAG22"]}');
-- item without any properties, just an empty json
INSERT INTO item (id, jsonb_content) VALUES (6, '{}');
-- int values
INSERT INTO item (id, jsonb_content) VALUES (7, '{"integer_value": 132}');
-- double values
INSERT INTO item (id, jsonb_content) VALUES (10, '{"double_value": 353.01}');
-- enum values
INSERT INTO item (id, jsonb_content) VALUES (13, '{"enum_value": "SUPER"}');
-- string values
INSERT INTO item (id, jsonb_content) VALUES (16,
'{"string_value": "this is full sentence"}');
-- inner elements
INSERT INTO item (id, jsonb_content) VALUES (21, '{"child": {"pets" : ["dog", "cat"]}}');Şöyle
yaparız. Entity nesnemize jsonb sütun için String tipinden bir alan ekliyoruz. Sütun tipinin jsonb olduğunu columnDefinition ile gelirtiyoruz.
@Entity
@Table(name = "item")
public class Item {
@Column(name = "jsonb_content", columnDefinition = "jsonb")
private String jsonbContent;
...
}
Şöyle
yaparız. Burada entityManager.createNativeQuery() ile sorgu yapıyoruz
private EntityManager entityManager;
public List<Item> findAll(String expression) {
return entityManager
.createNativeQuery("SELECT * FROM item i WHERE "
+ "i.jsonb_content#>>'{string_value}' LIKE '"
+ expression + "'", Item.class)
.getResultList();
}
