...
| Code Block |
|---|
| language | java |
|---|
| title | Converters |
|---|
| collapse | true |
|---|
|
@Slf4j
@Converter(autoApply = true)
public final class StringToMapConverter implements AttributeConverter<Map<String, ? extends Object>, String> {
private static final ObjectMapper MAPPER = new ObjectMapper();
@Override
public String convertToDatabaseColumn(Map<String, ? extends Object> map) {
try {
return map == null ? null : MAPPER.writeValueAsString(map);
} catch (JsonProcessingException ex) {
throw new IllegalStateException(ex);
}
}
@Override
public Map<String, ? extends Object> convertToEntityAttribute(String dbData) {
if (dbData == null) {
return Map.of();
}
try {
return MAPPER.readValue(dbData, new TypeReference<Map<String, Object>>() {});
} catch (IOException ex) {
log.error("Cannot convert String to Map.");
return Map.of();
}
}
}
@Slf4j
@Converter(autoApply = true)
public final class StringToDocumentConverter implements AttributeConverter<DocumentDto, String> {
private static final ObjectMapper MAPPER = new ObjectMapper();
@Override
public String convertToDatabaseColumn(DocumentDto document) {
try {
return document == null ? null : MAPPER.writeValueAsString(document);
} catch (JsonProcessingException ex) {
throw new IllegalStateException(ex);
}
}
@Override
public DocumentDto convertToEntityAttribute(String dbData) {
if (dbData == null) {
return new DocumentDto();
}
try {
return MAPPER.readValue(dbData, DocumentDto.class);
} catch (IOException ex) {
log.error("Cannot convert String to Map.");
return new DocumentDto();
}
}
}
|
The java code below shows some examples how to use the functionalities offered by PosgreSLQ about Json Type. We can apply where clauses, extract and manipulate data into the Json data. In order to use these functionalities, all queries have to be native (nativeQuery = true).
| Code Block |
|---|
| language | java |
|---|
| title | Repository |
|---|
| collapse | true |
|---|
|
@Repository
public interface ItemRepository extends JpaRepository<Item, String> {
@Query(value = " SELECT * FROM item where document['name'] @> :value ", nativeQuery = true)
Stream<Item> findByName(@Param("value") String value);
@Modifying
@Query(value = " UPDATE item SET document['name']=:value where key=:key ", nativeQuery = true)
void updateNameByKey(@Param("key") String key, @Param("value") String value);
@Query(
value = " SELECT Cast(jsonb_path_query_array(document['elements'], '$[*].keyvalue().value.tags') as varchar) FROM item ",
nativeQuery = true)
Stream<String> findTags();
}
|
In order to make unit test, we can not use H2 due of the incompatibility. Spring supports Testcontainers where we can configure a PostgreSQL Container that will up and running during tests.
| Code Block |
|---|
| language | java |
|---|
| title | Unit Test |
|---|
| collapse | true |
|---|
|
@Testcontainers
@ExtendWith(SpringExtension.class)
@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@ContextConfiguration(
initializers = DemoApplicationTests.Initializer.class,
classes = DemoApplication.class)
@TestPropertySource(locations = {"classpath:application_test.properties"})
class DemoApplicationTests {
@Test
void contextLoads() {
}
@Container
public static PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer("postgres:latest")
.withDatabaseName("db_demo").withUsername("sa").withPassword("sa");
@Configuration
static class Initializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {
@Override
public void initialize(ConfigurableApplicationContext configurableApplicationContext) {
TestPropertyValues
.of("spring.datasource.url=" + postgreSQLContainer.getJdbcUrl(),
"spring.datasource.username=" + postgreSQLContainer.getUsername(),
"spring.datasource.password=" + postgreSQLContainer.getPassword())
.applyTo(configurableApplicationContext.getEnvironment());
}
}
}
|
...
