Populate DTO using query with JOIN

I have this main Product table:

@Table(name = "product")
public class Product implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private int id;

    @Column(name = "user_id", length = 20)
    private Integer userId;

    @Column(name = "title", length = 75)
    private String title;

    @Column(name = "meta_title", length = 100)
    private String metaTitle;

    @Column(name = "status", length = 100)
    private String status;
}

Additional table for storing categories that should be returned as List:

@Table(name = "product_category")
public class ProductCategory implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private int id;

    @Column(name = "product_id", length = 4)
    private Integer productId;

    @Column(name = "category_id", length = 20)
    private Integer categoryId;

}

Additional table for storing Payment Methods that should be returned as List:

@Table(name = "product_payment_methods")
public class ProductPaymentMethods implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private int id;

    @Column(name = "product_id", length = 20)
    private Integer productId;

    @Column(name = "payment_methods", length = 20000)
    private String paymentMethods;
}

I want to return a result like this:

id | title | categoryId | paymentMethods |
1  | test  |    34, 43  |    345, 7, 5   |
5  | test2 |    64,5, 3 |    654, 3, 5   |

I tried this:

SELECT *
FROM Product
INNER JOIN product_category ON Product.id = product_category.productId
INNER JOIN product_payment_methods ON Product.id = product_payment_methods.productId
WHERE userId = 1

What is the proper way to populate this DTO?

public class ProductFullDTO {
   private int id;

   private Integer userId;

   private List<Integer> categories;

   private List<String> paymentMethods;
}

If, as indicated in your comments, you need query your information with HQL a good way to proceed can be the following.

First, modify your Product entity an include relationships for both ProductCategory and ProductPaymentMethods, something like:

@Table(name = "product")
public class Product implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private int id;

    @Column(name = "user_id", length = 20)
    private Integer userId;

    @Column(name = "title", length = 75)
    private String title;

    @Column(name = "meta_title", length = 100)
    private String metaTitle;

    @Column(name = "status", length = 100)
    private String status;

    @OneToMany(mappedBy = "product", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<ProductCategory> categories;


    @OneToMany(mappedBy = "product", cascade = CascadeType.ALL, orphanRemoval = true)
    private List< ProductPaymentMethods> paymentMethods;

    // Setters and getters, omitted for brevity
}

Modify both ProductCategory and ProductPaymentMethods to accommodate the entities relationship:

@Table(name = "product_category")
public class ProductCategory implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private int id;

    // Please, feel free to change the insertable and updatable attributes to 
    // fit your needs
    @Column(name = "product_id", length = 4, insertable=false, updatable=false)
    private Integer productId;

    @ManyToOne(fetch= FetchType.LAZY)
    @JoinColumn(name="product_id")
    private Product product;

    @Column(name = "category_id", length = 20)
    private Integer categoryId;

    // Setters and getters, omitted for brevity

}

@Table(name = "product_payment_methods")
public class ProductPaymentMethods implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private int id;

    // Please, feel free to change the insertable and updatable attributes to 
    // fit your needs. By the way, why here the length is 20 and not 4?
    @Column(name = "product_id", length = 20, insertable=false, updatable=false)
    private Integer productId;

    @ManyToOne(fetch= FetchType.LAZY)
    @JoinColumn(name="product_id")
    private Product product;

    @Column(name = "payment_methods", length = 20000)
    private String paymentMethods;
}

With this setup, as you can see in the Hibernate documentation - it is for an old Hibernate version, but it is correct today, you can use fetch joins to obtain the required information:

> A "fetch" join allows associations or collections of values to be initialized along with their parent objects using a single select. This is particularly useful in the case of a collection.

For your example, consider the following HQL (assume outer join semantics, modify it as appropriate):

select product
from Product as product
     left join fetch product.categories
     left join fetch product.paymentMethods
where product.userId = 1

This will provide you the list of products for userId 1, with all the associated references to categories and payment methods initialized.

The conversion between the entity and the DTO should be straightforward:

Session session = ...
List<Product> products = session.createQuery(
    "select product " +
    "from Product as product " +
    "     left join fetch product.categories " +
    "     left join fetch product.paymentMethods " +
    "where product.userId = :userId", Product.class)
.setParameter( "userId", 1)
.getResultList();

List<ProductFullDTO> productFullDTOs = null;
if (products != null) {
  productFullDTOs = products.stream()
      .map((product -> {
        ProductFullDTO productFullDTO = new ProductFullDTO();
        productFullDTO.setId(product.getId());
        productFullDTO.setUserId(product.getUserId());
        
        List<ProductCategory> categories = product.getCategories();
        if (categories != null) {
          List<Integer> categoriesIds = categories.stream()
              .map(ProductCategory::getCategoryId)
              .collect(Collectors.toList())
          ;
          
          productFullDTO.setCategories(categoriesIds);
        }

        List<ProductPaymentMethods> paymentMethods = product.getPaymentMethods();
        if (paymentMethods != null) {
          List<String> paymentMethodsIds = paymentMethods.stream()
              .map(ProductPaymentMethods::getPaymentMethods)
              .collect(Collectors.toList())
          ;

          productFullDTO.setPaymentMethods(paymentMethodsIds);
        }
        
        return productFullDTO;
      }))
      .collect(Collectors.toList())
  ;
}

System.out.println(productFullDTOs == null ? "No products found." : productFullDTOs.size() + " products found.");

You should use TypeHandler to finish this job. I just give paymentMethods as example.

    @Results({
@Result(column = "product.id", property = "id"),
@Result(column = "user_id", property = "userId"),
@Result(column = "category_id", property = "categories"),
@Result(column = "payment_methods", property = "paymentMethods" ,typeHandler= StrListTypeHandler.class),
})
@Select("SELECT * FROM Product INNER JOIN product_category ON Product.id = product_category.productId "
+ " INNER JOIN product_payment_methods ON Product.id = product_payment_methods.productId "
+ " WHERE userId = 1")
List<ProductFullDTO> getProduct();
// the below is TypeHandler implementation
@Component
public class StrListTypeHandler implements TypeHandler<List<String>> {
@Override
public void setParameter(PreparedStatement preparedStatement, int i, List<String> strings, JdbcType jdbcType) throws SQLException {
StringBuffer sb = new StringBuffer();
for (String s : strings) {
sb.append(s).append(",");
}
preparedStatement.setString(i, sb.toString().substring(0, sb.toString().length() - 1));
}
@Override
public List<String> getResult(ResultSet resultSet, String s) throws SQLException {
String[] arr = resultSet.getString(s).split(",");
return Arrays.asList(arr);
}
@Override
public List<String> getResult(ResultSet resultSet, int i) throws SQLException {
String[] arr = resultSet.getString(i).split(",");
return Arrays.asList(arr);
}
@Override
public List<String> getResult(CallableStatement callableStatement, int i) throws SQLException {
String[] arr = callableStatement.getString(i).split(",");
return Arrays.asList(arr);
}
}