I'm using Google App Engine (Java) with JDO. How can I do the JDO equivalent of
select * from table where field like '%foo%'
The only recommendation I have seen so far is to use Lucene. I'm kind of surprised that something this basic is not possible on out-of-the-box GAE.
You can't do substring searches of that sort on App Engine. The reason for this is that the App Engine datastore is built to be scalable, and refuses to execute any query it can't satisfy with an index. Indexing a query like this is next to impossible, because it requires searching the entirety of each record's 'field' property for a match. Any relational database you run this query on will execute it by doing a full table scan and checking each record individually - unscalable, to say the least.
The solution, as you've already found out, is to use full-text indexing, such as Lucene. There are libraries for running Lucene on App Engine, such as GAELucene. This also gives you the power of proper full-text search, rather than naive substring matching.