Hi All, I am using springboot and spring-data-jpa...

# server

[Muhammad Abdullah]

Hi All,

I am using springboot and spring-data-jpa to create a rest-api that handles crud operations. I am using Postgresql as the DB.

I can get it to work fine, however given the JSON example below I want to store the phoneNumbers, emails, addresses, urls, settings as JSON in the DB column for each one respectively.

Is there a known way that this can be done? My data class is fairly simple:

I have tried String, Map and Any as the data type for those fields none of which works.

@Table("organisations")
data class Organisation(
    @Id val id: UUID?,
    val name: String = "",
    val information: String = "",
    val urls: Any?,
    val emails: Any?,
    val phoneNumbers: Any?,
    val addresses: Any?,
    val settings: Any?,
    val active: Boolean = true,
    val createdAt: Instant = Instant.now(),
    val updatedAt: Instant = Instant.now(),
)

{
“name”: “Business Name”,
“information”: “Business Info”,
“phoneNumbers”: {
“office”: “[phone]”,
“imam”: “[phone]”
},
“emails”: {
“info”: “[e-mail]”,
“commity”: “[e-mail]”
},
“addresses”: {
“address”: “2345 SomeStreet Road, MyTOWN, 1234",
“postal address”: “PO Box 123"
},
“urls”: {},
“settings”: {
“timeFormat”: “dd/MM/yyyy”,
“currency”: “AUD”
}
}

Any pointers or help would be greatly appreciated.

Thanks

[Ties]

Well, json is just a string, so I imagine that should work. Why didnt that work for you?

[Muhammad Abdullah]

Thanks for the response.

I get the following error when the request is deserialized:
Resolved [org.springframework.http.converter.HttpMessageNotReadableException: JSON parse error: Cannot deserialize value of type java.lang.String from Object value (token JsonToken.START_OBJECT); nested exception is com.fasterxml.jackson.databind.exc.MismatchedInputException: Cannot deserialize value of type java.lang.String from Object value (token JsonToken.START_OBJECT)
at [Source: (PushbackInputStream); line: 4, column: 21] (through reference chain: com.masjidlink.api.Organisation[“phoneNumbers”])]

[Muhammad Abdullah]

My controller method that processes the request is:

@PostMapping
fun createNewOrganisation(@RequestBody newOrganisation: Organisation): ResponseEntity<Organisation> =
    ResponseEntity<Organisation>(service.saveOrganisation(newOrganisation), HttpStatus.OK)

[Muhammad Abdullah]

So, it appears that the parts that I want to be stored as JSON strings are being translated to objects even though the field definition is a String.

[Ties]

ah ok 🙂 I think I see what is happening here. Basicalliy what is going on is that you use the same object for a database entity and as a json object. This can sometimes indeed cause issues (on multiple fronts, even security).

[Ties]

As a start i would suggest making a seperate object that's being used in the API and one thats being used for the database. This gives you a lot more control over the exact types everywhere (because that is what your current problem is, if you find it interesting i can go into more details about that, but i think by making it 2 separate classes your problem will already fix itself, or at least makes it easy to fix it yourself)

[Muhammad Abdullah]

So basically a DTO that contains for example a list of address objects and then I translate that to the format I want for the data class?

[Ties]

you should make a OrganisationAPI class and a OrganisationEntity class (both can be data classes) and use the api class in the @RequestBody and use the @Table on the OrganisationEntity class

[Ties]

now you will have to do some manual mapping between the 2, but you can specify the exact types you want to use in both classes, for example, emails can be a string in the Entity class, but be a JSONObject in the API class (and you will have to map it from one type to another when storing or retrieving it)

[Muhammad Abdullah]

I understand. That makes sense. Thanks for the help. I was trying to avoid duplicating code but I guess sometimes you can’t.

[Ties]

In this case I basically always have different classes for API and entity, yes it may sound as duplication at first, but in practice there are a lot of reasons why you want to have separate classes for this; Sometimes (quite often) you do not want the same information (for example you might not want to expose the password field in a user object), sometimes the types don't match up (like you are experiencing), you dont always want to change the service API everytime you have a database change (or vice-versa) for backwards compatibility etc... so in practice in most cases it is good to have 2 classes for this, even if it means a bit of duplication

[Muhammad Abdullah]

Thanks for the help. That makes perfect sense.

[Ties]

Happy to help 🙂

[Ties]

and let me know if you need any more background information 🙂

[Muhammad Abdullah]

Much appreciated. I will.

[Łukasz Pięta]

Hi,
you can also make a use of this repository -> https://github.com/vladmihalcea/hibernate-types

With this dependency you can simply mark column as jsonb type (which is generally speaking json optimized for certain operations such as exctracting the value of object associated with some key) in your entity class.

@Entity
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
class Foo(
    @Id val id: UUID,
    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    val bar: Bar
)

[Muhammad Abdullah]

Thanks @Łukasz Pięta. Sorry, I missed this. I am trying to avoid using Hibernate.