Mockrunner(Java) query with Regex

Question

I am using Mockrunner to mock Sql DB for my unit tests. Following is my query:-

"select * from table where userId in (" + userIds + ")"

Now my userIds is state dependent. I don't need my test cases dependent on the arrangement inside the list - userIds. So I don't need exact match but regex matching. I have already enabled regex matching by below code:-

    StatementResultSetHandler statementHandler = connection.getStatementResultSetHandler();
    usersResult = statementHandler.createResultSet("users");
    statementHandler.setUseRegularExpressions(true);
    //How to write this regex query?
    statementHandler.prepareResultSet("select * from table where userId in .*", campaignsResult);

But as it is noted, I have no idea about the regex syntax supported by Mockrunner.

Edit: I unable to match queries like "Select * from tables" with "Select * from tab .*". So It has to do something with the way I using regex with Mockrunner

Are you sure that the regex syntax is even usable as part of the `IN ...` clause? I'd think it was more intended for matching against fields, like `SELECT * FROM users WHERE email = '.*gmail\.com';`? — aroth, Nov 17 '15 at 05:50
Why not? Query is just a string. For example I need a regex for "select * from table where **whatever here** group by type, date;" — Mangat Rai Modi, Nov 17 '15 at 05:52
Yep, you're correct. I see what it's doing now. You may find some of the [examples here](http://grepcode.com/file/repo1.maven.org/maven2/com.mockrunner/mockrunner-j2ee1.3-jdk1.3/0.4/com/mockrunner/example/jdbc/BookstoreTest.java) useful in understanding the regex syntax. It looks like you probably want `... where userId in \\(.*\\)`. — aroth, Nov 17 '15 at 05:55
@aroth Thanks for the link. However replacing * with .* in my question still not helping. I am reading more on the link to see If I am doing anything wrong :( — Mangat Rai Modi, Nov 17 '15 at 06:11
I think that's because you need to escape the literal `*` in your query. Otherwise, as a regex, it matches "selectfromtable ...". If you want to use a regex and have a literal `*` in your query, the literal character needs to be escaped. — aroth, Nov 17 '15 at 06:21
@Aroth it is working, Thanks for the help. Basically table names, query syntax etc. could not be matched with regex. Only column names, values in where clause etc. could be matched. Could be great if you could write your answer and I could accept — Mangat Rai Modi, Nov 17 '15 at 06:22
@aroth, oops. yup. Escaping * with \\ solved the issue. Thanks! — Mangat Rai Modi, Nov 17 '15 at 06:26

score 1 · Accepted Answer · answered Nov 17 '15 at 06:42

There are some helpful examples available here. For instance:

public void testCorrectSQL() throws Exception {
    MockResultSet result = getStatementResultSetHandler().createResultSet();
    getStatementResultSetHandler().prepareResultSet("select.*isbn,.*quantity.*", result);
    List orderList = new ArrayList();
    orderList.add("1234567890");
    orderList.add("1111111111");
    Bookstore.order(getJDBCMockObjectFactory().getMockConnection(), orderList);
    verifySQLStatementExecuted("select.*isbn,.*quantity.*\\(isbn='1234567890'.*or.*isbn='1111111111'\\)");
}

From this, I surmise that it's using standard Java regex syntax. In which case, you probably want:

prepareResultSet("select \\* from table where userId in \\(.*\\)", campaignsResult);

...or perhaps more succinctly (and depending upon exactly how fine-grained your tests need to be):

prepareResultSet("select .* from table where userId in .*", campaignsResult);

The main caveat to be aware of when enabling the regex matching is that any literal special characters that you want in your query (such as *, (, and ) literals) need to be escaped in your regex before it will work properly.

Mockrunner(Java) query with Regex

1 Answers1