MySQL Full Text Searches

[Brad Huggins]

I am trying to build a full text search using QueryDSL with JPA on a MySQL database.  Here is my code sample:

JPAQuery qry = new JPAQuery(entityManager);

QForumPosts posts = QForumPosts.forumPosts;
qry.from(posts);
qry.where(posts.message.matches("+testing +search"));

I expected it to generate the following:

select * from forumPosts where MATCH(message) AGAINST('+testing +search' IN BOOLEAN MODE)

however it generated:

select * from forumPosts where message like :a1 escape '!'

Any idea on what I can do to do this actual query?

Thanks

[timowest]

Hi.

If you use Hibernate you would at first need to create a dialect that supports this syntax, e.g. via

public class MySQLCustomDialect extends MySQL5Dialect {

    public MySQLCustomDialect() {

        registerFunction("matches", new SQLFunctionTemplate(BooleanType.INSTANCE, "MATCH(?1) AGAINST (?2) IN BOOLEAN MODE"));

    }

}

Then you could use that for example via 

Expressions.booleanTemplate("matches({0},{1}", message, "+testing +search")

To have stringPath.matches do the same you will need to customize Querydsl as well via 

new HQLTemplates() {{

    add(Ops.MATCHES, "matches({0},{1}");

}}

And use that customized HQLTemplates version in the JPAQuery constructor.

Lots of customizations, but at least the querydsl part is easier ;)

Br,

Timo