<p>I have been trying to find an easy way to parse a search query and convert it to an SQL query for my DB. </p>

<p>I have found two solutions:</p>

<ol>
<li><em><a href="https://lucene.apache.org/" rel="nofollow noreferrer">Lucene</a></em>: Powerful Java-based search engine, contains a query parser but it isn't very configurable and I could find a way to easily hack/adapt it to create SQL queries.</li>
<li><em><a href="http://www.antlr.org/" rel="nofollow noreferrer">ANTLR</a></em>: A veteran text lexer-parser. Used for building anything from compilers to sky scrapers. ANTLR is highly configurable but everyone touching the code from now on will have to learn a new language...</li>
</ol>

<p>Any other ideas?</p>

[{'answer_id': 13871, 'author': 'GaryF', 'author_id': 1035, 'author_profile': 'https://Stackoverflow.com/users/1035', 'pm_score': 0, 'selected': False, 'text': "<p>Depends a lot on the kind of queries you've got to parse and somewhat on the structure of the data in your database. I'm going to assume that you're not trying to do full text search in a DB (i.e. a search engine across your entire DB) because, as most Information Retrieval people will tell you, the performance for that is terrible. Inverted indexes are most certainly the best way of doing that.</p>\n\n<p>Tell us a bit more about the actual problem: what are the users going to input, what are they expecting as output, and what's the data model like. Design a search solution without those pieces of information, and you'll get a far from optimal result.</p>\n"}, {'answer_id': 13873, 'author': 'pbh101', 'author_id': 1266, 'author_profile': 'https://Stackoverflow.com/users/1266', 'pm_score': 2, 'selected': False, 'text': '<p>What exactly do you have in mind?  I\'ve used Lucene for text-searching, but where it excels is building an index and searching that <em>instead</em> of hitting the database at all.</p>\n\n<p>I recently set up an system where I index a table in Lucene by concatenating all the columns (separated by spaces) into one field, and popping that into Lucene, and then also adding the primary key in a separate column.  Lucene does all the searching and returned a list of primary keys, which I used to pull up a populated set of results and display to the user.</p>\n\n<p>Converting a search query into a SQL statement would seem to me to be a little messy.</p>\n\n<p>Also, here\'s <a href="http://www.darksleep.com/lucene/" rel="nofollow noreferrer">a great beginning tutorial explaining the basic structure of Lucene</a>.</p>\n'}, {'answer_id': 13877, 'author': 'Barak Schiller', 'author_id': 1651, 'author_profile': 'https://Stackoverflow.com/users/1651', 'pm_score': 0, 'selected': False, 'text': '<p>You are correct to assume that I am not looking for full text search.\nThe information looks something like this schema for book info:\nName: string, publisher:string, num_pages int, publish_date:date...</p>\n\n<p>The search queries are of the sort:</p>\n\n<ol>\n<li><strong>Harry Potter</strong> (search any books whos name has both Harry and Potter)</li>\n<li><strong>publisher:Nature*</strong> pages>100 (books from a publisher starting with Nature with more than 100 books)</li>\n<li><strong>("New years" or Christmas) and present</strong> (you get the picture...)</li>\n<li><strong>physics and publish>1/1/2008</strong> (new physics books)</li>\n</ol>\n'}, {'answer_id': 154639, 'author': 'Mr. Shiny and New 安宇', 'author_id': 7867, 'author_profile': 'https://Stackoverflow.com/users/7867', 'pm_score': 1, 'selected': False, 'text': '<p>You could try using something like <a href="https://javacc.java.net/" rel="nofollow noreferrer">javacc</a> (Java Compiler Compiler) to implement a parser or else just manually parse the string by brute force.  Every time you come across an expression you represent it as an object.  Then you just have to translate your expression tree into a where clause.</p>\n\n<p>For example:  "Harry Potter" becomes </p>\n\n<pre><code>new AndExp(new FieldContainsExp("NAME", "Harry"), new FieldContainsExp("NAME", "Potter")\n</code></pre>\n\n<p>And "publisher:Nature* pages > 100" becomes</p>\n\n<pre><code>new AndExp(new FieldContainsExp("PUBLISHER", "Nature"), FieldGreaterThan("PAGES", 100))\n</code></pre>\n\n<p>Then, once you have these, it\'s pretty easy to turn them into SQL:</p>\n\n<pre><code>FieldContainsExp.toSQL(StringBuffer sql, Collection&lt;Object&gt; args) {\n  sql.append(fieldName);\n  sql.append(" like ");\n  sql.append("\'%?%\'");\n  args.add(value);\n}\n\nAndExp.toSQL(StringBuffer sql, Collection&lt;Object&gt; args) {\n    exp1.toSQL(sql, args);\n    sql.append(" AND ");\n    exp2.toSQL(sql, args);\n}\n</code></pre>\n\n<p>You can imagine the rest.  You can nest And expressions as deeply as you want.  </p>\n'}, {'answer_id': 154774, 'author': 'Dónal', 'author_id': 2648, 'author_profile': 'https://Stackoverflow.com/users/2648', 'pm_score': 2, 'selected': False, 'text': '<p><a href="http://sqlorm.sourceforge.net/dynamic_query_builder_doc.html" rel="nofollow noreferrer">SQL-ORM</a> is a very lightweight Java library which includes the ability to construct a (dynamic) SQL query in Java as a graph of objects</p>\n\n<p>IMHO, this is a far better technique for building dynamic SQL queries than the usual String concatentation method.</p>\n\n<p>Disclaimer: I have made some <strong>very</strong> minor contributions to this project</p>\n'}, {'answer_id': 1295474, 'author': 'Shimi Bandiel', 'author_id': 15100, 'author_profile': 'https://Stackoverflow.com/users/15100', 'pm_score': 0, 'selected': False, 'text': '<p>Try to combine an ORM tool (like openJPA) and Compass (framework for OSEM).\nIt automatically indexes the updates done through the ORM tools and gives you the Lucene power for search. After that you can of-course retrieve the object from the DB.\nIt out-performs any SQL-based searching solution.</p>\n'}, {'answer_id': 19051117, 'author': 'user2815040', 'author_id': 2815040, 'author_profile': 'https://Stackoverflow.com/users/2815040', 'pm_score': -1, 'selected': False, 'text': '<p>String [] array;</p>\n\n<pre><code>int checkWord(String searchWord)\n{\n    for(int i = 0; i &lt; array.length; i++)\n    {\n        if(searchWord.equals(array[i]))\n            return i;\n    }\n    return 0;\n\n}\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/13863', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1651/']