I am looking for a way to compare two MySQL queries in a unit test. Do you know any library that allows that (all of these asserts should pass):
SQLAssert.assertEquals("select id, name from users", "select id, name from users")
SQLAssert.assertEquals("select id, name from users", "select `id`,`name` from `users`")
While running the queries against an in-memory database and comparing results is the best answer, I think there are less-comprehensive and more brittle options that are nevertheless useful.
In practice it's likely that you can place additional constraints on the syntax of the queries. In your example, there are only select statements, a single table, no where clause, and the only query differences are backticks and spaces, so writing a method that normalizes queries with those constraints would probably be doable. Something like:
private String normalize(String str) {
return str.replaceAll(" +", " ").replaceAll("`", "");
}
These normalized strings can then be compared. This way of doing things is very brittle (and therefore not future proof), but that doesn't mean it can't provide value in certain circumstances. Sure, there are quite a few valid sql statements that would cause this to break, but you don't have to deal with the full set of strings that valid sql entails. You just have to deal with whatever subset of sql your queries use.
If your queries are different enough to make this code unreasonable, it might be easier to use a parser library like JSqlParser to parse out the pieces and then navigate the structure to do your comparison. Again, you don't have to support all of SQL, just whatever subset your queries use. Also, the tests don't have to test full logical equivalence to be useful. A test might just make sure that all the tables mentioned in two queries are the same regardless of joins and ordering. That doesn't make them equivalent, but it does guard against a particular kind of error and is more useful than nothing.
An example of a situation where this could be useful is if you are doing large groups of refactorings on your query-builders and you want to make sure the end queries are equivalent. In this case you aren't testing the queries themselves but the query-building.
I wouldn't suggest doing this as a regular practice in unit tests, but I think it can be useful in very particular circumstances.
You could use JSqlParser to parse your queries. Then you could use the so called Deparser of JSqlParser to get version of your SQL without additional spaces, tabs, linefeeds. From this on, you could use a simple String equality check. Sure you have to process all kinds of quotations like " or [] but it works, like the example code shows.
This does not work, of quotation comes into play or different orders of columns or expression within you SQL. The quotation problem is simple to solve through an extention to the expression deparser.
Statement stmt1 = CCJSqlParserUtil.parse("select id, name from users");
Statement stmt2 = CCJSqlParserUtil.parse("select id, name from users");
Statement stmt3 = CCJSqlParserUtil.parse("select `id`,`name` from `users`");
//Equality
System.out.println(stmt1.toString().equals(stmt2.toString()));
ExpressionDeParser exprDep = new ExpressionDeParser() {
@Override
public void visit(Column tableColumn) {
tableColumn.setColumnName(tableColumn.getColumnName().replace("`", ""));
super.visit(tableColumn);
}
};
SelectDeParser stmtDep = new SelectDeParser() {
@Override
public void visit(Table tableName) {
tableName.setName(tableName.getName().replace("`", ""));
super.visit(tableName);
}
};
exprDep.setBuffer(stmtDep.getBuffer());
stmtDep.setExpressionVisitor(exprDep);
((Select)stmt3).getSelectBody().accept(stmtDep);
String stmt3Txt = stmtDep.getBuffer().toString();
System.out.println(stmt1.toString().equals(stmt3Txt));
I would suggest that the only way to assert that 2 queries return the same result is to actually run them. Of course, what you don't want to do is have unit tests connect to a real database. There are a number of reasons for this:
The tests can affect what is in the database, and you don't want to introduce a load of test data into a production database
Each test should be self contained, and work the same way each time it is run, which requires the database be in the same known state at the start of each run. This requires a reset for each test - not something to do with a production (or dev environment) database.
With these constraints in mind, I suggest you look into DBUnit, which is designed for database-driven JUnit tests. I also suggest, instead of using MySQL for unit tests, use an in-memory database (the examples use HSQLDB), that way, you can test queries without having test data actually persisted.
