flexiblesch检查报告定义中的空参数Hybris

Question

我想在后台创建一个报表定义项，它将检索一些列。查询需要像这样检索我需要的内容:

SELECT {a}, {b} , {c} FROM {model} WHERE {d} IS NULL AND {e} IS NULL

所有值(a,b，…e)都是字符串。我想对{d}进行参数化和{e}列，但我希望能够完全搜索我之前编写的查询。我开始这样做:

SELECT {a}, {b}, {c} FROM {model} WHERE {d}=?dStatus AND {e}=?eStatus

和dStatus和eStatusjava.lang.String类型，但这不起作用

有可能使这个灵活的查询{d}和{e}列参数化，以搜索报告定义中的空值?

Answer 1

嗨，如果你想让一个报告定义参数化，这是可能的。

你可以通过下面的步骤来做。

<itemtype code="testBasicOrderEntryReport" extends="Item" metatype="ViewType"
jaloonly="true" jaloclass="de.hybris.platform.util.ViewResultItem" generate="false">
<description>Basic Order Entry Report</description>
<custom-properties>
<property name="legacyPersistence">
<value>java.lang.Boolean.TRUE</value>
</property>
<property name="query">
<value>
" SELECT "
+ "{BaseStore.name} AS country, "
+ "{Order.code} AS orderCode, "
+ "DATE({Order.date}) AS orderDate, "
+ "{Product.code} AS productCode, "
+ "{OrderEntry.quantity} AS qty, "
+ "{OrderMargin.margin} AS CVR "
+ "FROM { OrderEntry "
+ "JOIN Order ON {OrderEntry.order} = {Order.pk} "
+ "JOIN BaseStore ON {Order.store} = {BaseStore.pk} "
+ "JOIN Product ON {OrderEntry.product} = {Product.pk} "
+ "LEFT JOIN OrderMargin ON {OrderEntry.margin} = {OrderMargin.pk} "
+ "} WHERE "
+ "{Order.store}=?baseStoreParam "
+ "AND "
+ "{Order.date}&gt;=?orderStartDateParam "
+ "AND "
+ "{Order.date}&lt;=?orderEndDateParam "
+ "AND "
+ "{Order.versionID} is null "
</value>
</property>
</custom-properties>
<attributes>
<attribute qualifier="baseStoreParam" type="BaseStore" metatype="ViewAttributeDescriptor">
<persistence type="jalo"/>
<modifiers write="false" search="false" optional="false"/>
<custom-properties>
<property name="param">
<value>Boolean.TRUE</value>
</property>
<property name="position">
<value>Integer.valueOf(0)</value>
</property>
</custom-properties>
</attribute>
<attribute qualifier="orderStartDateParam" type="java.util.Date" metatype="ViewAttributeDescriptor">
<persistence type="jalo"/>
<modifiers write="false" search="false" optional="false"/>
<custom-properties>
<property name="param">
<value>Boolean.TRUE</value>
</property>
<property name="position">
<value>Integer.valueOf(1)</value>
</property>
</custom-properties>
</attribute>
<attribute qualifier="orderEndDateParam" type="java.util.Date" metatype="ViewAttributeDescriptor">
<persistence type="jalo"/>
<modifiers write="false" search="false" optional="false"/>
<custom-properties>
<property name="param">
<value>Boolean.TRUE</value>
</property>
<property name="position">
<value>Integer.valueOf(2)</value>
</property>
</custom-properties>
</attribute>
<attribute qualifier="country" type="java.lang.String" metatype="ViewAttributeDescriptor">
<persistence type="jalo"/>
<modifiers write="false" search="false" optional="false"/>
<custom-properties>
<property name="param">
<value>Boolean.FALSE</value>
</property>
<property name="position">
<value>Integer.valueOf(0)</value>
</property>
</custom-properties>
</attribute>
<attribute qualifier="orderCode" type="java.lang.String" metatype="ViewAttributeDescriptor">
<persistence type="jalo"/>
<modifiers write="false" search="false" optional="false"/>
<custom-properties>
<property name="param">
<value>Boolean.FALSE</value>
</property>
<property name="position">
<value>Integer.valueOf(1)</value>
</property>
</custom-properties>
</attribute>
<attribute qualifier="orderDate" type="java.lang.String" metatype="ViewAttributeDescriptor">
<persistence type="jalo"/>
<modifiers write="false" search="false" optional="false"/>
<custom-properties>
<property name="param">
<value>Boolean.FALSE</value>
</property>
<property name="position">
<value>Integer.valueOf(2)</value>
</property>
</custom-properties>
</attribute>
<attribute qualifier="productCode" type="java.lang.String" metatype="ViewAttributeDescriptor">
<persistence type="jalo"/>
<modifiers write="false" search="false" optional="false"/>
<custom-properties>
<property name="param">
<value>Boolean.FALSE</value>
</property>
<property name="position">
<value>Integer.valueOf(3)</value>
</property>
</custom-properties>
</attribute>
<attribute qualifier="qty" type="java.lang.Integer" metatype="ViewAttributeDescriptor">
<persistence type="jalo"/>
<modifiers write="false" search="false" optional="false"/>
<custom-properties>
<property name="param">
<value>Boolean.FALSE</value>
</property>
<property name="position">
<value>Integer.valueOf(4)</value>
</property>
</custom-properties>
</attribute>
<attribute qualifier="CVR" type="java.lang.Double" metatype="ViewAttributeDescriptor">
<persistence type="jalo"/>
<modifiers write="false" search="false" optional="false"/>
<custom-properties>
<property name="param">
<value>Boolean.FALSE</value>
</property>
<property name="position">
<value>Integer.valueOf(5)</value>
</property>
</custom-properties>
</attribute>
</attributes>
</itemtype>

这里的baseStoreParam, orderStartDateParam, orderEndDateParam参数。

注意:根据预期的结果值和类型，我们需要在<attributes>中定义该属性的位置和类型。标签。

Answer 2

可以拆分原始查询和它的条件

String query = "SELECT {a}, {b}, {c} FROM {model} WHERE {d} %s AND {e} %s";
String dStatusParam = "";
String eStatusParam = "";
if (null == dStatus) {
dStatusParam = "IS NULL";
} else {
dStatusParam = "= '" + dStatus + "'";
}
if (null == eStatus) {
eStatusParam = "IS NULL";
} else {
eStatusParam = "= '" + eStatus + "'";
}
query = String.format(query, dStatusParam, eStatusParam);