title: Using Lucene QueryAPI to access SQL
body: <p>Can you advise on whether I can use just the Query functionality from Lucene to generate SQL queries? Something like an SQLQueryBuilder?</p>
  
  <p>I have a massive SQL database of logs from a webserver cluster containing the original request and response strings plus some other useful/less bits and bobs. What I need to do is analyse the parameters in the original request and compare with the generated responses, looking at ratios, volatility, variability, consistency etc. </p>
  
  <p>This question does not relate to the analysis stage, but only the retrieval of data from database which matches the parameters I'm interested in. So, I could just do this in good old sql queries, manually building the exact queries I need on a case-by-case basis. But that's kinda lame; I reckon we can be a bit smarter than that. Particularly as I can already see large numbers of similar but subtly different queries being useful. And as I'm hoping that I can expose a single search box via a web interface to non-technical end-users, adding sql queries seems like a bad idea... and a recipe for permanent maintenance requests (and can I be the first to say, er no thanks!). </p>
  
  <p>In an ideal world I expose a search form, with the option to write simple queries like </p>
  
  <pre><code>request:"someAttribute=\"someValue\"" AND response="some hoped for result" AND daterange:30
  </code></pre>
  
  <p>which would then hopefully find all instances of requests which contain someAttribute="someValue" over the last 30 days. The results will then be put through standard statistical analyses on the given response text and printed out on-screen. At least, that's the idea.</p>
  
  <p>Much of the actual logic to determine how to handle custom field definitions or special words I'll need to write myself, and that's ok. And NB, my non-technical end users are familiar enough with xml that they can handle a bit of attr="value" syntax, at least for the first iteration of the tool :D</p>
  
  <p>In summary, I want to:
  1) allow users to use google-like search syntax (e.g. via Lucene's QueryAPI) to specify text to match in the logs
  2) allow a layer to manipulate the query based on special words or fields (e.g. this layer could be during a Java object phase)
  3) convert the final query into an sql query appropriate for my database schema
  4) query the database and spit back the resultset for statistical analysis
  5) pretty-print on website:)</p>
  
  <p>Am I completely barking up the wrong tree? It looks like it should be possible, but I can't seem to find much on it. I've been googling for a bit on this, for example trying "Lucene SQLQueryBuilder" as a possible start but didn't really find much by way of a lead. </p>
  
  <p>So, my questions are:</p>
  
  <ul>
  <li>Has anyone tried using Lucene's QueryAPI like this before? Did it work? Any gotchas? </li>
  <li>Are there better query api libraries out there?</li>
  </ul>
  
  <p>Examples, finished discussions and open-source implementations would be most helpful.</p>
  
  <p>Many thanks.</p>
  
  <p>NB: I don't think I want Lucene's search capabilities as such, as I'm only ever looking for exact matches. I just need a query layer on top of the database.</p>