Thanks for visiting DZone today,
Please enter at least three characters to search
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 theasentinel-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 theasentinel-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.
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 anArticle and it’s described by anid and acode explicitly, while the rest of theattributes 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 theattributes 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 thepom.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, thepostgresql driver dependency is included as well, together with thespring-boot-starter-jdbc, so that the automaticDataSource 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 propertyIn addition to the provided details, let’s insist a bit on thesqlParam 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’sJSONB, 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 aConversionService 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, aConversionService has to be constructed and injected both intoDefaultEntityDescriptorTreeRepository (used when reading from the database) and into theSimpleUpdater (needed when writing into the database). The methods that construct theDefaultEntityDescriptorTreeRepository andOrmOperations instances are below.
@Beanpublic DefaultEntityDescriptorTreeRepository entityDescriptorTreeRepository(SqlBuilderFactory sqlBuilderFactory, @Qualifier("ormConversionService") ConversionService conversionService) { DefaultEntityDescriptorTreeRepository treeRepository = new DefaultEntityDescriptorTreeRepository(); treeRepository.setSqlBuilderFactory(sqlBuilderFactory); treeRepository.setConversionService(conversionService); return treeRepository;} @Beanpublic 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 theConversionService which is now enhanced to be able to convert to/fromJSONB 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 createdArticle entity class, the@SqlParam annotation was used and had thejsonb database type name as its value. It is basically the one that triggers theConversionService for theAttributes 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 isOrmConfig.
Once done, mapping PostgreSQL JSON columns is straightforward. As theattributes field in theArticle class has already been annotated as above; let’s build a service that allows actually writing and reading such records into the database.
@Servicepublic 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 theOrmOperations instance. The former method saves anArticle into the database, while the latter reads one by its uniquecode. That’s all, as simple as that, no other steps are needed.
Further, to validate the implementation, the following test is run.
@SpringBootTest@Transactionalclass 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 thearticles 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"}|+--+------------------------------------+--------------------------------------------------------------------------------+In this article, we exemplified how to map a database JSON column using theasentinel-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 frameworkCoversionService, this makes it a great choice not only when working withJSONB type columns, but for implementing the applications’ data access layer in general, thus I really recommend you give it a try.
Published at DZone with permission ofHoratiu Dan.See the original article here.
Opinions expressed by DZone contributors are their own.
The likes didn't load as expected. Please refresh the page and try again.
CONTRIBUTE ON DZONE
LEGAL
CONTACT US