I need to query look like this.
{noformat}select
psaer.excel_row_seq as excel_ro1_156_0_,
psaer.excel_row_no as excel_ro2_156_0_,
psaer.prod_info_batch_excel_seq as prod_inf3_156_0_,
psaer.prod_no as prod_no4_156_0_,
psaer.row_msg as row_msg5_156_0_,
psaer.row_status as row_stat6_156_0_,
IF(psaer.prod_no REGEXP '[^[:digit:]]+', NULL, psaer.prod_no) as formula88_0_
from
prod_status_active_excel_row psaer
;
{noformat}
When I do with following code,
{noformat} @Nullable
@Valid
@NotFound(action = NotFoundAction.IGNORE) // -> EAGER!
// @JoinFormula("CASE "
// + " WHEN " + MappedProdStatusActiveExcelRow.COLUMN_NAME_PROD_NO + " REGEXP '[^[:digit:]]+'"
// + " THEN NULL"
// + " ELSE " + MappedProdStatusActiveExcelRow.COLUMN_NAME_PROD_NO
// + " END")
@JoinFormula("IF("
+ MappedProdStatusActiveExcelRow.COLUMN_NAME_PROD_NO + " REGEXP '[^[:digit:]]+'"
+ ", NULL"
+ ", " + MappedProdStatusActiveExcelRow.COLUMN_NAME_PROD_NO
+ ")")
@ManyToOne(optional = true, fetch = FetchType.LAZY)
// @JoinColumn(name = COLUMN_NAME_PROD_NO, nullable = true, insertable = false, updatable = false,
// foreignKey = @ForeignKey(ConstraintMode.NO_CONSTRAINT))
@EqualsAndHashCode.Exclude
@ToString.Exclude
private Prod prod;{noformat}
Hibernate seems generate sql look like this.
{noformat}select
prodstatus0_.excel_row_seq as excel_ro1_156_0_,
prodstatus0_.excel_row_no as excel_ro2_156_0_,
prodstatus0_.prod_info_batch_excel_seq as prod_inf3_156_0_,
prodstatus0_.prod_no as prod_no4_156_0_,
prodstatus0_.row_msg as row_msg5_156_0_,
prodstatus0_.row_status as row_stat6_156_0_,
IF(prodstatus0_.`prod_no` prodstatus0_.REGEXP '[^[:digit:]]+', -- column alias?
NULL,
prodstatus0_.prod_no) as formula88_0_
from
prod_status_active_excel_row prodstatus0_
...{noformat}
The column alias between {{IF(prodstatus0_.\`prod_no`}} and {{REGEX ..}} seems wrong.
What can I do for this? Thanks. |
|
