title: Dynamically create search criteria and results using Java and sql database
body: <p>I am currently working with a Java based web application (JSF) backed by Hibernate that has a variety of different search pages for different areas.  </p>
  
  <p>A search page contains a search fields section, which a user can customize the search fields that they are interested in.  There are a range of different search field types that can be added (exact text, starts with, contains, multi-select list boxes, comma separated values, and many more).  Search fields are not required to be filled in and are ignored, where as some other search fields require a different search field to have a value for this search field to work.</p>
  
  <p>We currently use a custom search object per area that is specific to that area and has hard coded getter and setter search fields.</p>
  
  <pre><code>public interface Search {
    SearchFieldType getSearchPropertyOne();
    void setSearchPropertyOne(SearchFieldType searchPropertyOne);
  
    AnotherSearchFieldType getSearchPropertyTwo();
    void setSearchPropertyTwo(AnotherSearchFieldType searchPropertyTwo);
  
    ...
  }
  </code></pre>
  
  <p>In this example, SearchFieldType and AnotherSearchFieldType represent different search types like a TextSearchField or a NumericSearchField which has a search type (Starts with, Contains, etc.) or (Greater Than, Equals, Less Than, etc.) respectively and a search value that they can enter or leave empty (to ignore the search field).</p>
  
  <p>We use this search object to prepare a Criteria object</p>
  
  <p>The search results section is a table that can also be customized by the user to contain only columns of the result object that they are interested in. Most columns can be ordered ascending or descending.</p>
  
  <p>We back our results in a Result object per result which also hard codes the columns that can be displayed.  This table is backed by hibernate annotations, but we are trying to use flat data instead of allowing other hibernate backed objects to minimize lazy joining data.</p>
  
  <pre><code>@Entity(table = "result_view")
  public interface Result {
    @Column(name = "result_field_one")
    Long getResultFieldOne();
    void setResultFieldOne(Long resultFieldOne);
  
    @Column(name = "result_field_two")
    String getResultFieldTwo();
    void setResultFieldTwo(String resultFieldTwo);
  
    ...
  }
  </code></pre>
  
  <p>The search page is backed by a view in our database which handles the joining to all the tables needed for every possible outcome.  This view has gotten pretty massive and we take a huge performance hit for every search, even when a user only really wants to search on one field and display a few columns because we have upwards of thirty search field options and thirty different columns they can display and this is all backed by the one view.</p>
  
  <p>On top of this, users request new search fields and columns all the time that they would like added to the page.  We end up having to alter the search and result objects as well as the backing view to make these changes.</p>
  
  <p>We are trying to look into this matter and find alternatives to this.  One approach mentioned was to create different views that we dynamically choose based on the fields searched on or displayed in the results table.  The different views might join different columns and we pick and choose which view we need for any given search.</p>
  
  <p>I'm trying to think about the problem a different way.  I think it might be better to not use a view and instead dynamically join tables we need based on what search fields and result columns are requested.  I also feel that the search and result objects should not contain hard coded getters/setters and should instead be a collection of search fields and a collection (or map) of result columns.  I have yet to completely flesh out my idea.</p>
  
  <p>Would hibernate still be a valid solution to this issue?  I wouldn't want to have to create a Result object used in a hibernate criteria since they result columns can be different.  Both search fields and/or result columns might require joining tables.</p>
  
  <p>Is there a framework I could use that might help solve the problem?  I've been trying to look for something, and the closest thing I have found is SqlBuilder.</p>
  
  <p>Has anyone else solved a similar problem dynamically?</p>
  
  <p>I would prefer not to reinvent the wheel if a solution already exists.</p>
  
  <p>I apologize that this ended up as a wall of text.  This is my first stackoverflow post, and I wanted to make sure I thoroughly defined my problem.</p>
  
  <p>Thanks in advance for your answers!</p>