Is there a way to check on SpringBoot if the xml file has a common query condition?

Question

<select id="findById" resultType="foo">
   SELECT *
   FROM foo_table
   WHERE id = #{id}
   limit 1
</select>

<select id="findByStauts" resultType="foo">
   SELECT *
   FROM foo_table
   WHERE status = #{status}
    limit 1
</select>

I want to make sure that the condition 'limit 1' is in all queries.
Is there a way to check in Spring test ?

Answer 1

You can loop over these XML tags using xPath and check if limit 1 is inside their values:

1. Import the libraries and initialize objects:

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
XPath xPath = XPathFactory.newInstance().newXPath();
Document document;

2. Convert your file in a string. There are lots of ways you can do this. Check here: https://mkyong.com/java/java-convert-file-to-string/.

3. Now, we will loop over all the select tags and check if they contain limit 1. Assuming your string from step 2 is named "myXML":

document = builder.parse(new InputSource(new StringReader(myXML)));

String xpathSelectTag = "yourXPathToSelectTag";
// Count how many select tags exist
Integer countTags = ((NodeList) xPath.evaluate(xpathSelectTag, document, XPathConstants.NODESET)).getLength();

// loop through tags
String selectValue = "";
for (int i = 1; i <= countTags; i++) {
    selectValue = xPath.evaluate(xpathSelectTag+"["+i+"]", document);
    if (selectValue.contains("limit 1")) {
        // your code
    }
}

In this block of code xPath.evaluate(xpathSelectTag+"["+i+"]", we take the value of the select tag that our index i is on. So, we loop through all of them.

Please comment for any problems so that I can help you.