-
1. Re: Managing unwieldy number of parameters
If you use Seam's CRUD generation, you'll see that the pattern of entering the search criteria in a POJO is the way to go. In CRUD generation, Seam uses the
Query by example
pattern (the search criteria is an instance of the same entity bean managed by the EntityQuery), but you're not limited to that, you can use whatever search criteria you need.So, create a javabean with the search criteria you need and either inject it into the EntityQuery component or, as Seam's generator does, just put an instance of the javabean inside the EntityQuery and wire the search form to it. Afterwards, you can add whatever restrictions needed to satisfy the criteria:
class MySearchCriteria { private String criterion1; // whatever other criteria you need and getters and setters } @Name("myQuery") class MyEntityQuery extends EntityQuery<MyResultEntityBean> { private MySearchCriteria criteria; // restrictions and other functionality that applies }Regarding RESTful search, a Seam generated search page also shows how it can be accomplished: you simply need to declare all search criteria as search parameters in your page.xml descriptor and wire them to the search criteria bean. Consult the documentation regarding page parameters, but basically all you need to do is:
<page> <param name="criterion1" value="#{myQuery.criteria.criterion1}" /> </page>Now you can invoke the URL as
searchPage.seam?criterion1=anyValue
and Seam will populate the fields of the criteria bean with the values passed in the request.Again, if you have the entities already in place, it is perhaps easier to just make Seam's reverse engineer generate the search page and modify it adding the criteria you need.
-
2. Re: Managing unwieldy number of parameters
Thank you for the excellent explanation of how to do this Emir.
It also helped me realize I didn't specify my question exactly.
I am using Seam CRUD generation as a starting point for my application. The main difficulty I'm having is that I have 8 tables, all related to one another, and a possible 20 search criteria.
Using hibernate restrictions this works pretty well to handle the myriad of search criteria. This is a subset of RESTRICTIONS, but it's not the number of restrictions I'm worried about particularly in this scenario. It's all the data fetched by the joins.
This does work:
public String getEjbql() { return "select devices from Devices devices " + "left join fetch devices.office office " + "left join fetch devices.assetTag assetTag " + "left join fetch devices.devInterfaces devInterfaces " + "left join fetch devices.office.rings rings " + "left join fetch devices.office.siteCodeMaps siteCodeMap" + "left join fetch devices.office.siteTypeMaps siteTypeMap"; private final String[] RESTRICTIONS = { "devices.devId=#{devices.devId}", "office.officeId in #{officeIds}", "lower(devices.serialNum) like concat('%',lower(#{devices.serialNum}),'%')", "lower(devices.name) like concat('%',lower(#{devices.name}),'%')", "lower(devices.ip) like concat('%',lower(#{devices.ip}),'%')", "lower(devices.platform) like concat('%',lower(#{devices.platform}),'%')", "devices.osVer=#{exactOsVer}", "devices.platform=#{exactPlatform}", "office.state = #{state}", "lower(assetTag.assetTag) like concat('%',lower(#{assettag}),'%')", "lower(devInterfaces.peRouter) like concat('%',lower(#{perouter}),'%')", "lower(rings.ring)=lower(#{ring})", "lower(siteCodeMap.siteCodes.siteCode) = lower(#{sitecode})", "siteTypeMap.siteTypes.siteType = #{sitetype}" };The problems are these: If the result set is large the app runs out of java heap space. The first page displays fine, but try to page forward and you run out of memory fast!
Also, the view only displays data from 2 of the entities. So why fetch from 7 if I'm not using it?
Here's another idea I had. Restrict the Ejbql to
"select devices from Devices devices"
and Restrictions to anything from devices
private final String[] RESTRICTIONS = { "devices.devId=#{devices.devId}", "devices.devId in #{devIdList}" "lower(devices.serialNum) like concat('%',lower(#{devices.serialNum}),'%')", "lower(devices.name) like concat('%',lower(#{devices.name}),'%')", "lower(devices.ip) like concat('%',lower(#{devices.ip}),'%')", "lower(devices.platform) like concat('%',lower(#{devices.platform}),'%')", "devices.osVer=#{exactOsVer}", "devices.platform=#{exactPlatform}", };I could use the other search criteria and a sql query to find the list of devIds and populate devIdList.
-
3. Re: Managing unwieldy number of parameters
First of all, don't use the fetch modifier in the joins, unless you really need it (otherwise, you're instructing hibernate to fetch all those associated entities...)
Secondly, the problem is that the joins are introducing cartesian results, since the restrictions are applied to the query only if the EL expression in the restriction evaluates to a non-null value.
So, what you need is to make the ejbql dynamic (i.e., add the join to the
office
table only when the #{officeIds} expression is not null). This can be accomplished overriding the getEjbql() method, although is not as easy as it sounds, specially if you're using AJAX to update the result table.Finally, page the results using setMaxResults, otherwise your result set will be very large. If you're using richfaces paginator, keep in mind that it doesn't play very well with the EntityQuery component (I suppose that's the reason why the CRUD generator doesn't use richfaces pagination and column sorting).
-
4. Re: Managing unwieldy number of parameters
Yes, those
cartesion results
, not to mention the impossibility of sorting all that stuff was really a problem.Modifying getEjbql is not a bad idea, but I think I've decided instead to keep getEjbql simple with
select d from Devices d IN #{list of devIds}or whatever the correct syntax is.
The handling of the complex set of search parameters would be relegated to building a SQL query for devIds based on search parameters:
Note the real code will be much more complicated than this:
public void buildQuery() { String query = "select dev_id from devices "; String whereClause = "where "; if assettag != null whereClause = whereClause + "assettag=#{assettag} " if perouter != null whereClause = whereClause + "AND perouter=#{perouter} " etc. etc. }Final question -- does anyone reading this have an opinion on where such a query should go? Perhaps in getEjbql? Or does it matter as long as its run before getEjbql?
Hope this makes sense and helps someone else. I think you've explained some concepts very clearly Emir. Thanks.
TDR