How to Map PostgreSQL JSON Data Types in Java Using asentinel-orm
Convert between custom Java types and database types using the Spring framework's ConversionService, particularly for JSON entity attributes.
CORE
·
Join the DZone community and get the full member experience.
Join For FreeIt isn’t seldom when software products need to easily and efficiently manage the direct storage and handling of JSON content directly into the underlying database. The purpose of this article is to exemplify how such tasks can be conveniently accomplished via the asentinel-orm, a lightweight ORM tool built on top of Spring JDBC, which possesses most of the features one would expect from such a project.
We will start by defining a simple entity that contains a JSONB column. Then, we will configure a sample application that uses the asentinel-orm to handle its data access towards a PostgreSQL database that stores such entities. Lastly, we will exemplify and emphasize how the actual JSON data can be queried and stored properly.
Set Up
- Java 21
- Spring Boot 3.5.6
- PostgreSQL Driver version 42.7.7
- asentinel-orm version 1.71.2
Implementation
Considering the PostgreSQL database server is up and running, one may create this simple schema.
create schema articles;Let’s consider the entity in discussion represents an Article and it’s described by an id and a code explicitly, while the rest of the attributes are kept in JSON format.
CREATE TABLE IF NOT EXISTS articles (
id SERIAL PRIMARY KEY,
code VARCHAR NOT NULL UNIQUE,
attributes JSONB NOT NULL
);Let’s assume the representation of the attributes is the following:
{
"title": "How to map JSON columns with asentinel-orm",
"author": "Horatiu Dan",
"words": 1154
}In order to be able to use the ORM annotations and to apply mappings to the Java entity, one needs to add the specific dependency into the pom.xml file.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.asentinel.common</groupId>
<artifactId>asentinel-common</artifactId>
<version>1.71.2</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>Also, to be able to interact with the PostgreSQL database, the postgresql driver dependency is included as well, together with the spring-boot-starter-jdbc, so that the automatic DataSource configuration is ensured. Moreover, since JSON handling is implied, the Jackson dependency is also present, and a mapper instance is constructed and added to the project configuration.
The corresponding Java entity can now be created.
@Table("Articles")
public class Article {
@PkColumn("id")
private int id;
@Column("code")
private String code;
@Column(value = "attributes", sqlParam = @SqlParam("jsonb"))
private Attributes attributes;
...
}@Table– associates the database table with the class and is used when generating automatic SQL queries, as we'll immediately see@PkColumn– allows associating the table primary key with the designated property@Column– associates the indicated table column with the corresponding Java property
In addition to the provided details, let’s insist a bit on the sqlParam attribute of the @Column annotation. Its purpose is to provide pieces of information about the mapped database column type of the annotated member in case it designates a special column, or a user-defined one. Here, it’s JSONB, obviously not a standard SQL one, and thus the @SqlParam annotation is used to indicate it. Later in the article, we’ll see it’s used in conjunction with a ConversionService in order for the custom conversion between the Java and the SQL types and vice-versa to be handled properly.
Resource 1 can be used to find out additional details that can be depicted when browsing the ORM source code.
The purpose of this article is to map PostgreSQL JSON columns and perform operations in both directions. This requires custom data types’ conversion and thus, a ConversionService has to be constructed and injected both into DefaultEntityDescriptorTreeRepository (used when reading from the database) and into the SimpleUpdater (needed when writing into the database). The methods that construct the DefaultEntityDescriptorTreeRepository and OrmOperations instances are below.
@Bean
public DefaultEntityDescriptorTreeRepository entityDescriptorTreeRepository(SqlBuilderFactory sqlBuilderFactory,
@Qualifier("ormConversionService") ConversionService conversionService) {
DefaultEntityDescriptorTreeRepository treeRepository = new DefaultEntityDescriptorTreeRepository();
treeRepository.setSqlBuilderFactory(sqlBuilderFactory);
treeRepository.setConversionService(conversionService);
return treeRepository;
}
@Bean
public OrmOperations orm(JdbcFlavor jdbcFlavor,
SqlQuery sqlQuery,
SqlBuilderFactory sqlBuilderFactory,
@Qualifier("ormConversionService") ConversionService conversionService) {
SimpleUpdater updater = new SimpleUpdater(jdbcFlavor, sqlQuery);
updater.setConversionService(conversionService);
return new OrmTemplate(sqlBuilderFactory, updater);
}
@Bean("ormConversionService")
public ConversionService ormConversionService() {
GenericConversionService conversionService = new GenericConversionService();
conversionService.addConverter(new JsonToObjectConverter());
conversionService.addConverter(new ObjectToJsonConverter());
return conversionService;
}Additionally, appropriate converters shall be added to the configuration and registered with the ConversionService which is now enhanced to be able to convert to/from JSONB respectively.
private static final ObjectMapper MAPPER = JsonMapper.builder().build();
private static class JsonToObjectConverter implements ConditionalGenericConverter {
@Override
public Object convert(Object source, TypeDescriptor sourceType, TypeDescriptor targetType) {
PGobject pgObj = (PGobject) source;
try {
return MAPPER.readValue(pgObj.getValue(), targetType.getType());
} catch (JsonProcessingException e) {
throw new IllegalArgumentException("Failed to convert from JSON.", e);
}
}
@Override
public boolean matches(TypeDescriptor sourceType, TypeDescriptor targetType) {
if (!(sourceType.getType() == PGobject.class)) {
return false;
}
Column column = targetType.getAnnotation(Column.class);
if (column == null) {
return false;
}
return "jsonb".equals(column.sqlParam().value());
}
@Override
public Set<ConvertiblePair> getConvertibleTypes() {
return null;
}
}
private static class ObjectToJsonConverter implements ConditionalGenericConverter {
@Override
public Object convert(Object source, TypeDescriptor sourceType, TypeDescriptor targetType) {
String s;
try {
s = MAPPER.writeValueAsString(source);
PGobject pgo = new PGobject();
pgo.setType("jsonb");
pgo.setValue(s);
return pgo;
} catch (JsonProcessingException | SQLException e) {
throw new IllegalArgumentException("Failed to convert to JSON.", e);
}
}
@Override
public boolean matches(TypeDescriptor sourceType, TypeDescriptor targetType) {
if (!(targetType instanceof SqlParameterTypeDescriptor)) {
return false;
}
SqlParameterTypeDescriptor typeDescriptor = (SqlParameterTypeDescriptor) targetType;
return "jsonb".equals(typeDescriptor.getTypeName());
}
@Override
public Set<ConvertiblePair> getConvertibleTypes() {
return null;
}
}In the earlier created Article entity class, the @SqlParam annotation was used and had the jsonb database type name as its value. It is basically the one that triggers the ConversionService for the Attributes annotated field and causes the 2 converters declared above to be used for reading and writing the field.
The complete configuration of the ORM for this sample application is OrmConfig.
Once done, mapping PostgreSQL JSON columns is straightforward. As the attributes field in the Article class has already been annotated as above; let’s build a service that allows actually writing and reading such records into the database.
@Service
public class ArticleService {
private final OrmOperations orm;
public ArticleService(OrmOperations orm) {
this.orm = orm;
}
@Transactional
public void write(Article article) {
orm.update(article);
}
@Transactional
public Optional<Article> readByCode(String code) {
return orm.newSqlBuilder(Article.class)
.select()
.where().column("code").eq(code)
.execForOptional();
}
}All interaction is done via the OrmOperations instance. The former method saves an Article into the database, while the latter reads one by its unique code. That’s all, as simple as that, no other steps are needed.
Further, to validate the implementation, the following test is run.
@SpringBootTest
@Transactional
class ArticleServiceTest {
@Autowired
private ArticleService articleService;
@Test
void manageArticles() {
var code = UUID.randomUUID().toString();
var attributes = new Attributes("Technically-correct Article", "Horatiu Dan", 1200);
var article = new Article(code, attributes);
articleService.write(article);
Optional<Article> read = articleService.readByCode(code);
Assertions.assertTrue(read.isPresent());
final Article readArticle = read.get();
Assertions.assertEquals(code, readArticle.getCode());
final Attributes readAttributes = readArticle.getAttributes();
Assertions.assertEquals(attributes.getTitle(), readAttributes.getTitle());
Assertions.assertEquals(attributes.getAuthor(), readAttributes.getAuthor());
Assertions.assertEquals(attributes.getWords(), readAttributes.getWords());
}
}As a last step, for the sake of exploration, the above test is transformed into a non-transactional one (not recommended in real projects). Once run for the first time, the contents of the articles database table look as below and depict the aimed representation. attributes column stores data represented as JSON.
+--+------------------------------------+--------------------------------------------------------------------------------+
|id|code |attributes |
+--+------------------------------------+--------------------------------------------------------------------------------+
|5 |10f594cf-90ba-4280-b6d8-9308ab16916a|{"title": "Technically-correct Article", "words": 1200, "author": "Horatiu Dan"}|
+--+------------------------------------+--------------------------------------------------------------------------------+Conclusion
In this article, we exemplified how to map a database JSON column using the asentinel-orm open-source project. Although the underlying database chosen here is PostgreSQL, the same can be accomplished similarly on others, such as MySQL, Oracle, H2, you name it. On the other hand, even if one may find the initial ORM configuration a little bit difficult in the beginning, which requires a bit of ORM understanding, once this is accomplished, its use is fairly easy and intuitive, not to mention the great ORM performance.
Considering that starting with version 1.71.0, asentinel-orm has been allowing adding custom conversion capabilities from Java into database types and vice-versa by leveraging the Spring framework CoversionService, this makes it a great choice not only when working with JSONB type columns, but for implementing the applications’ data access layer in general, thus I really recommend you give it a try.
Resources
Published at DZone with permission of Horatiu Dan. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments