I want to use a native query from database to retrieve more data for a given record, and I'm looking for a solution on how to declare the fields that accommodate this data.

If I do not annotate the field, during updates JPA thinks this field should be mapped to a column with the same name (which of course doesn't exist in the table); if I annotate it with @Transient, the field is ignored on update, but also ignored on select (why?).

Let's clarify with an example. I have the entity Discount

class Discount {
    @Column(name = "ID")
    private long id;

    @Column(name = "PRODUCT_ID")
    private long productId;

    // private String productName; <-- Uncommented, error on save; with @Transient empty on read
}

And when I retrieve a discount from database, I also want to use the same query to return the whole record plus the name of the product which comes from Product table.

Using Spring repositories I set up the query as follow:

public interface DiscountRepo extends Repository<Discount, Long> {
    @Query(nativeQuery = true, value = "select d.*,p.name as productName from discount d join product p on d.product_id = d.id")
    List<Discount> getAll();
}

The reason I don't want to have a property like private Product product in the Discount entity, is that on select too much data would be transferred from database, while I only need the product name.

CodePudding user response：

You can use JdbcTempate and then map return value to Discount.class

1. String sqlQuery = "select d.ID as id , d.PRODUCT_ID as productId p.name as productName from discount d join product p on d.product_id = d.id";
2. Map<String, Object> result = jdbcTemplate.queryForMap(sqlQuery);
3. Discount discount = new ObjectMapper().convertValue(result, Discount.class);

CodePudding user response：

Marking a field with @Transient has the same effect as marking something transient for Java serialization processes - it is entirely excluded from DB persistence in both directions. You wouldn't want this to be attempted to be read in as a regular property anyway, as that would interfere with all Discount read operations - and if that is what you want, you should probably map this field in the entity.

I would advise you to use some other, non Entity object for this data operation, so that your business logic does not confuse or mix this Discount object data with the extra 'productName' being populated with other JPA operations on Discount where it will be left out.

That said, you can use a JPA constructor query or constructor operation to have it build any pojo from the data. All you need is a constructor on the object to take in the same parameters and types.

@Entity
class Discount {
    @Column(name = "ID")
    private long id;

    @Column(name = "PRODUCT_ID")
    private long productId;

    @Transient
    private String productName;

    public AbstractDomainObject(Long id, Long productId, String productName)  {
      this.id = id;
      this.productId = productId;
      this.productName = productName;
    }
}

You could then use

public interface DiscountRepo extends Repository<Discount, Long> {
    @Query("select new your.package.Discount(d.id, d.productId, p.productName) from discount d join product p on d.productId = p.id"
    List<Discount> getAll();
}

If your provider doesn't support the join like this (some allow it), you can still have a Discount to product reference. Just make sure to mark it lazy - using it in queries will not force fetching all the data. Just make sure your provider supports lazy relationships as many require something extra, like enhancement, of the classes:

 public interface DiscountRepo extends Repository<Discount, Long> {
    @Query("select new your.package.Discount(d.id, d.productId, p.productName) from discount d join d.product p"
    List<Discount> getAll();
}

Or you can use a Native query still, but might want to define it in on your entity with SqlResultSetMapping that defines how the result is built:

@Entity
@SqlResultSetMappings(
    SqlResultSetMapping(
            name = "summary",
            classes = [
                ConstructorResult(
                        targetClass = Discount.class,
                        columns = [
                            ColumnResult(name = "id", type = Long::class),
                            ColumnResult(name = "productId", type = Long::class),
                            ColumnResult(name = "productName", type = String::class)
                        ]
                )
            ]
    )
)
@NamedNativeQueries(
    NamedNativeQuery(name = "Discount.getAll",
            query = "select d.*,p.name as productName from discount d join product p on d.product_id = p.id", resultSetMapping = "summary")
)
class Discount {
..

Then Spring should look for that query name when executing getAll in your repo:

public interface DiscountRepo extends Repository<Discount, Long> {
    List<Discount> getAll();
}