spring - move validation to the JPQL query level

Question

I am looking for a way to move the validation method from Service to Repository

One Picture has one PictureData.

This is the method:

// TODO move validation to the JPQL query level .filter(pic -> pic.getPictureData().getFileName() != null)

This is my Service

@Service
@ConditionalOnProperty(name = "picture.storage.type", havingValue = "file")
public class PictureServiceFileImpl implements PictureService {

    private static final Logger logger = LoggerFactory.getLogger(PictureServiceFileImpl.class);

    @Value("${picture.storage.path}")
    private String storagePath;

    private final PictureRepository repository;

    @Autowired
    public PictureServiceFileImpl(PictureRepository repository) {
        this.repository = repository;
    }

    @Override
    public Optional<String> getPictureContentTypeById(long id) {
        return repository.findById(id)
                // TODO move validation to the JPQL query level
                .filter(pic -> pic.getPictureData().getFileName() != null)
                .map(Picture::getContentType);
    }

    @Override
    public Optional<byte[]> getPictureDataById(long id) {
        return repository.findById(id)
                // TODO move validation to the JPQL query level
                .filter(pic -> pic.getPictureData().getFileName() != null)
                .map(pic -> Path.of(storagePath, pic.getPictureData().getFileName()))
                .filter(Files::exists)
                .map(path -> {
                    try {
                        return Files.readAllBytes(path);
                    } catch (IOException ex) {
                        logger.error("Can't open picture file", ex);
                        throw new RuntimeException(ex);
                    }
                });
    }

    @Override
    public PictureData createPictureData(byte[] picture) {
        String fileName = UUID.randomUUID().toString();
        try (OutputStream os = Files.newOutputStream(Path.of(storagePath, fileName))) {
            os.write(picture);
        } catch (IOException ex) {
            logger.error("Can't create picture file", ex);
            throw new RuntimeException(ex);
        }
        return new PictureData(fileName);
    }
}

The Entities

@Entity
@Table(name = "pictures")
public class Picture {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "content_type", nullable = false)
    private String contentType;

    @OneToOne(fetch = FetchType.LAZY, cascade= CascadeType.ALL, optional = false, orphanRemoval = true)
    @JoinColumn(name="picture_data_id")
    private PictureData pictureData;

    @ManyToOne
    private Product product;

    public Picture() {
    }

    public Picture(String name, String contentType, PictureData pictureData, Product product) {
        this.name = name;
        this.contentType = contentType;
        this.pictureData = pictureData;
        this.product = product;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getContentType() {
        return contentType;
    }

    public void setContentType(String contentType) {
        this.contentType = contentType;
    }

    public PictureData getPictureData() {
        return pictureData;
    }

    public void setPictureData(PictureData pictureData) {
        this.pictureData = pictureData;
    }

    public Product getProduct() {
        return product;
    }

    public void setProduct(Product product) {
        this.product = product;
    }
}

@Entity
@Table(name = "pictures_data")
public class PictureData {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Lob
    @Type(type="org.hibernate.type.BinaryType") // для правильной работы PostgreSQL
    @Column(name = "data", length = 33554430) // для правильной hibernate-валидации в MySQL
    private byte[] data;

    @Column(name = "file_name")
    private String fileName;

    public PictureData() {
    }

    public PictureData(byte[] data) {
        this.data = data;
    }

    public PictureData(String fileName) {
        this.fileName = fileName;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public byte[] getData() {
        return data;
    }

    public void setData(byte[] data) {
        this.data = data;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }
}

I am struggling to get a query working in JPQL.

public interface PictureRepository extends JpaRepository<Picture, Long> {

    @Query ("SELECT p FROM Picture p JOIN p.pictureData d WHERE d.data  IS NOT NULL ")
    Picture filterPictureWherePictureDataIsNotNull ();
}

Answer 1

Since you already have entity level join, you can directly use below method

public interface PictureRepository extends JpaRepository<Picture, Long> 
{

    @Query ("SELECT p FROM Picture p WHERE p.pictureData.data  IS NOT NULL ")
    Picture filterPictureWherePictureDataIsNotNull ();
}

Another observation as well,

You repo method might return list of Picture and not a one picture.So, return type should ideally be

@Query ("SELECT p FROM Picture p WHERE p.pictureData.data  IS NOT NULL ")
    List<Picture> filterPictureWherePictureDataIsNotNull ();