Could not write JSON: Unable to access lob stream

Question

I'm writing a server with Spring-Boot using PostgreSQL I'm trying to get information about images that are linked to a specific entity. I'm trying to get User information from the server to my front-end Angular app. In my system user have images linked to his account so i did class ImageEntity

@Entity @Table(name = "image") @Data
public class ImageEntity {
   @Id @GeneratedValue(strategy = GenerationType.SEQUENCE)
   private Long id;
   private String name;
   private String type;
   @Lob
   private byte[] image;

   @JsonIgnore
   public byte[] getImage() {
       return image;
   }
}

Then i linked the list of images to user account class

@Entity @Data
public class UserAccount{
@Id @GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
private String firstName;
private String lastName

@OneToMany(cascade = CascadeType.ALL)
@JoinTable(
        name = "user_images",
        joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")},
        inverseJoinColumns = {@JoinColumn(name = "image_id", referencedColumnName = "id")}
)
private List<ImageEntity> images;

public void addImage(ImageEntity image) {
    images.add(image);
}
}

Then i create endpoint to get user by id

@GetMapping("users/{id}")
public Optional<User> getUserById(@PathVariable Long id) {
    return service.getUserById(id);
}

service method is very simple

@Transactional
public Optional<User> getUserById(Long id) {
    return repository.findById(id);
}

I added some images through another endpoint works fine because i'm able to get image in my front-end.

Problem is when i want to get User info as a JSON from server( and i write @JsonIgnore on @Lob field because i only want to have info of image not the actual image) i get this error

Resolved exception caused by handler execution: org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: Unable to access lob stream; nested exception is com.fasterxml.jackson.databind.JsonMappingException: Unable to access lob stream (through reference chain: com.app.model.user.User["images"])

I read some similar articles and i try to give @JsonIgnore on getter of the Image @Lob image i added @Transactional to service method retrieving elements but it's not working.

I simply want to achieve that kind of message from server:

{
 id: "1"
 firstName: "test",
 lstName: "test_ln",
 images: {
    {
        "id": 10,
        "name": "IMG12.jpg",
        "type": "image/jpeg"
    },
    {
        "id": 20,
        "name": "IMG456.jpg",
        "type": "image/jpeg"
    }
 }
}

Answer 1

The fastest solution (not the best one) is to add fecth = EAGER to the OneToMany images relationship... the problem with this solution is that always will load the images entity (including the byte[] image) when dealing with users entities (which could be a performance problem)...

The next "best" solution is to omit the EAGER configuration described previously, and create a new method in your repository... such method should execute a JPA Query like this:

SELECT ua
FROM
    UserAccount ua
    LEFT JOIN FECTH ua.images img
WHERE
    ua.id = :id

This will load the user and its related images... then in your service, you call such method (the problem with this solution is that loads the byte[] image even if you only want the other attributes of the ImageEntity)

The best solution is to extend the solution #2 to retrieve only the attributes that you want of the ImageEntity, resulting in a query like this:

SELECT
    ua,
    img.id, img.name, img.type
FROM
    UserAccount ua
    LEFT JOIN ua.images img
WHERE
    ua.id = :id

Then, your repository method should return a JPA Tuple and in your service method you transform that tuple into the User that you want return (including the associated images' metadata) ... (UPDATE) Example (using the method that you indicate in your comments):

// @Transactional  // Remove the transactional annotation to avoid cascade issues!
public User getUserById(Long id) {
    List<ImageEntity> images;
    List<Tuple> tuples;
    User user;

    tuples = repository.getUserById(id);
    user   = null;

    if (!tuples.isEmpty()) {
        user   = tuples.get(0).get(0, User.class);
        images = new ArrayList<>();

        for (Tuple t : tuples) {
            if (t.get(1) != null) {
                images.add(new ImageEntity(
                    t.get(1, Long.class),
                    t.get(2, String.class)
                ));
            }
        }
        user.setImages(images);
    }
    return user;
}

In order to this to work, you need:

1. Modify the signature of the method getUserById (in your repository) to return a List of Tuple
2. Create a constructor method in the ImageEntity class with this signature: ImageEntity(long id, String name) { ... }
3. The User Entity should have a method setImages(List<ImageEntity> images) { ... }

UPDATE2: in order to do something like this, while retrieving all users, you will need:

1) Create (or override) a method in the User repository whose query will be like (let's call it, findAll):

SELECT
    ua,
    img.id, img.name, img.type
FROM
    UserAccount ua
    LEFT JOIN ua.images img

2) In your service, implement a method like this:

public List<User> findAll(Long id) {
    List<ImageEntity> images;
    List<Tuple> tuples;
    Map<Long, User> index;

    tuples = repository.findAll();
    index  = new HashMap<>();

    for (Tuple t : tuples) {
        user = t.get(0, User.class);

        if (!index.containsKey(user.getId()) {
            images = new ArrayList<>();

            user.setImages(images);
            index.put(user.getId(), user)
        } else {
            user   = index.get(user.getId());
            images = user.getImages():
        } 

        if (t.get(1) != null) {
            images.add(new ImageEntity(
               t.get(1, Long.class),
               t.get(2, String.class)
            ));
        }
    }
    return index.values();
}

EXPLANATION: The key point is we want to retrieve the user with the image metadata (only code, name and type) avoiding to load the lob attribute (because, the images can be MB and they won't be used/serialized) ... that is why we execute a query like this:

    SELECT
    ua,
    img.id, img.name, img.type
FROM
    UserAccount ua
    LEFT JOIN ua.images img

• The LEFT JOIN force to retrieve all user (including those without images)

• The ORM (i.e. JPA implementation, for example, hibernate) maps this kind of query to a Tuple Object (always)!

• The query produces N x M tuples ... where N are the total of Users and M are total of images... for example, if you have only 1 user with 2 images... the result will be 2 tuples, where first tuple's component is the always the same user, the other components will be the attributes of each images...

• Then, you need transform the tuple object to a User Object (this is what we do in the service methods) ... the key point here is the use of a new ArrayList for the images attribute, before adding a new ImageEntity to it ... we need to do this because the ORM injects a proxy list for each User loaded ... if we add something to this proxy, the ORM executes the lazy loading of such proxy, retrieving the associated images (which is something that we want to avoid)...