generating sql queries from mybatis xml queries

Question

I have a mybatis mapper file xml with complex queries lot of where clauses with conditions.

Is there any way I can create possible queries combination?

I want to run explain on all these queries as I am planning to add NOT IN on all queries.

Aryanize

Great question. I don't know any way of doing this. Incidentally I was about to write some code on my ORM to automatically produce EXPLAIN for a few databases, and show the plan. – Pi

can you give an example? – Harvison

You can create unit test (to make all possible combinations) plus configuration logging for mybatis to print the query in the logs... It is not ideal but will give you all queries. – Hensel

@Jorge Campos this is too much work. Macro recorder is much faster, no need for code writing at all. Click and play so to say :) – Appel

@AlexandarPetrov I would agree depending on the process to "click everywhere", it would be easier as you state in your answer if everything for that queries depends on direct inputs. But what if it doesn't depend on direct inputs? How would you record that in your macro? That's why I suggested the Unit testing development. It will be even useful for code coverage and tests itself when something need to be changed you will have already a test suit to check if your change works with everything else. :) – Hensel

@JorgeCampos I think here you mean a non interactive type of application. If that is the case I would just let it play for some time and again do data mining on the dumped SQL. – Appel

Answer 1

Inetractive application In your place I would use some sort of load generator, record a macro on Selenium for example with some imputs clicking everywhere that could be clicked on your application so that the targeted SQLs are triggered. Then I would put one recorder to log and dump all the SQL queries. I will analyse the log , pick up all the different samples and run them against the DB with explain plan.

Its a bit of a workaround solution , but I believe it will do the trick.

For a non interactive application, where UI or SOAP or REST recording is not an option. For exaple some sort of networking based application or a batch application or whatever.... If we suppose it is a batch, I would just let it play record the SQLs and again do explain. A lot of databases can do that on the fly actualy. For example if you use "Query monitor" on DB2 it records all queries in certain timeframe and than you can see the heaviest ones, or just the most commonly occuring ones and do explain. My expectation is other databases may have similar functionality if not you just dump the sqls on the application side. Plenty of options there http://www.rgagnon.com/javadetails/java-0602.html

Appel