[Java] Create a dynamic SQL statement with MyBatis [Search with multiple words]

9 minute read

Thing you want to do

Keywords

  • Convert from full-width space to half-width space (Edit ①)
  • 2 or more half-width spaces → Convert to one half-width space (Edit ②)
  • Delete leading and trailing spaces (Edit ③)

After that, I want to perform an OR search for keywords along with the categories (separated by a space search for any of the keywords included).

environment

OS used-Windows 10 Tools used-Spring Tool Suite 4 4.6.2 Template Engine-Thymeleaf Framework-mybatis

Result (description of xml file)

<select id="findByCategoryIdAndProductName"
resultType="File path of the corresponding domain">
It's a sequel.
SELECT * FROM item_table
WHERE cate_id = #{category}
      AND
It's a sequel.
<foreach collection="keywords" item="keyword" open="(" close=")" index="i" separator="">
<choose>
<when test="i == 0">
(item_name LIKE'%${keyword}%')
</when>
<otherwise>
OR (item_name LIKE'%${keyword}%')
</otherwise>
</choose>
</foreach>
</select>

I don’t think the following is necessary for those who are ok with this. If you don’t understand what you are doing, read on. I didn’t know what benefits mybatis would bring, so I’ll start with the very basics.

What is SQL

One of the computer languages (not a programming language). Used for connecting to a database, the following is the basic CRUD function syntax.

CREATE

INSERT INTO table-name(column-name,column-name,...) values('insert data','insert data', ...);
INSERT INTO table name VALUES('insert data','insert data', ...),('insert data','insert data', ...);

READ

SELECT column name FROM table name;

UPDATE

UPDATE table name SET column name ='overwritten content';

DELETE

DELETE FROM table-name;

Add WHERE after these sentences to specify more detailed data. Since it deviates from the main line, SQL will be done so far.

What is #MyBatis According to MyBatis official site

What is MyBatis?

MyBatis is a good persistence framework for custom SQL, stored procedures and advanced mapping. By using MyBatis, there is almost no need to write code that directly handles JDBC or manually set query parameters and query results. MyBatis settings and database records and Java objects can be associated using XML or annotations.

Roughly speaking, it seems to be a framework that shows its power when writing complicated SQL **.

Main subject

First, prepare a method in the controller to edit the search keyword entered by the user in the beginning.

Keyword processing

The search keyword is declared as String keywords in the form class.

It’s a little off story, but the form class is a file prepared when you want to send information together. There are only setters and getters as contents.

public String search(SampleForm f) {
  String keywords = f.getKeywords().replaceAll(" "," ").replaceAll("\\s{2,}", "").trim();
}

The replaceAll method takes two arguments and replaces a specific string all at once. The first method replaceAll(" "," ")" converts the full-width space to a half-width space (edit ①) In the second methodreplaceAll(“\s{2,}”, “”), convert two or more half-width spaces → one half-width space (edit ②) The third methodtrim()`` removes the white space around it.

About ###replaceAll method

As you can see from the first, I want to add a condition of “more than two” to the second, so I made it a metacharacter.

  • Half-width space → \s However, \ is treated as an escape, so use \\s
  • Repeat the previous pattern n times or more (longest match) → {n,} → This time it is 2 times or more {2,}

Reference: Regular expression sample collection

The question is what kind of SQL should be written in the first place.

“OR search for words that include a certain keyword” means, for example, if you search for the keywords “ai”, “ue”, and “o”, you will find “ai”, “ue”, or “o” somewhere in the character string. The movement of extracting all included words**.

First, the statement that extracts all values that include the word A is

SELECT * FROM table-name WHERE column-name LIKE'%A%';

The sentence that extracts all values that include the words “a”, “i”, and “u” is

SELECT * FROM table-name WHERE (column-name LIKE'%Ai%')
                           OR (column name LIKE'%up%')
                           OR (column name LIKE'%%%');

Reference: How to specify multiple search conditions for one column It doesn’t matter if you put parentheses () because it succeeded.

I was in trouble here

There may be no search keyword or multiple search keywords. And I don’t know how many will be entered. If this is a match (=) condition instead of a search with a LIKE clause, it may be possible to combine them with IN, but this time it is impossible.

Speaking of the same process for multiple items, it is a repeated process, but you can operate with java only the “Ai” part surrounded by %.

solution

This is a solution that can be done because the framework is MyBatis, and it is possible to write the iterative process by setting conditions in SQL statements. This time, we use <foreach><choose><when><otherwise>.

By the way, other tags such as <if><trim><where><set> are prepared.

foreach

From how to use the <foreach> tag.

First of all, it is common to other programming languages The foreach statement is also called an alias extended for statement, and it takes out the value of an item that has multiple elements such as an array or a collection, and processes it.

<foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
     <!-- Write the process you want to repeat here -->
</foreach>

Before writing the process, specify 6 properties. Not required for open and close in some cases.

  • item=: Specify name of value to be handled in foreach tag
  • index=: Specify the name of the index
  • collection=: Specify the collection you want to repeat
  • open=: Specify here if you want to add something at the beginning of processing
  • close=: Specify here when adding something at the end of processing
  • separator=: Specify what words are separated in the collection.

This time, store the keywords in a variable called keywords and specify each value as a keyword in the foreach statement. In addition, since the search is performed together with the category when searching, the outline of the SELECT statement looks like this.

SELECT * FROM item_table
WHERE cate_id = #{category} AND
It's a sequel.
<foreach collection="keywords" item="keyword" open="(" close=")" index="i" separator="">
<!-- Write processing contents here -->
</foreach>
Quiet: Do you need open and close?

From the conclusion, this time is necessary.

For example, if the category is 1 and “ai” and “ue” are specified in the search keyword, the whole foreach process must be enclosed in parentheses.

SELECT * FROM item_table
WHERE cate_id = 1 AND column name LIKE'% meeting %'OR column name LIKE'% upper %'

As a result of such processing, AND (and) and OR (or) are confused, and the machine cannot determine under what conditions the data should be extracted, resulting in an error.

SELECT * FROM item_table
WHERE cate_id = 1 AND (column name LIKE'%me%' OR column name LIKE'%up%')

You’ll know if it’s properly bracketed.

By the way, if there is nothing in the search keyword

SELECT * FROM item_table WHERE cate_id = 1 AND (column name LIKE'%%')

This is the process. There is no error, so there is no problem.

choose, when, otherwise

The <when> tag is if and the <otherwise> tag is else. The difference from the <if> tag is whether or not the processing corresponding to the else is necessary, so use it properly depending on the case.

When using <when><otherwise>, enclose it entirely in <choose> tags.When using the <if> tag, do not use the <choose> tag.

A general if statement is if (conditional expression), but the part corresponding to the conditional expression is specified by test="".

final SQL statement

To think about this code, let’s look at the SELECT statement again.

SELECT * FROM table-name WHERE (column-name LIKE'%Ai%')
                           OR (column name LIKE'%up%')
                           OR (column name LIKE'%%%');

With this kind of feeling, I want to enclose ** the first keyword ** as it is, and add the OR to the beginning of the ** subsequent keywords ** and put the keyword in %. Specify the sentence ↑ in the conditional expression of when.

<select id="findByCategoryIdAndProductName"
resultType="File path of the corresponding domain">
  SELECT * FROM item_table
    WHERE cate_id = #{category}
    AND
<!-- Iterate by fetching values from keywords in order -->
    <foreach collection="keywords" item="keyword" open="(" close=")" index="i" separator="">

      <!-- Surround with choose because I want to use when and otherwise -->
<choose>
        <!-- Processing for index number 0 -->
<when test="i == 0">
(item_name LIKE'%${keyword}%')
</when>
        <!-- Otherwise -->
        <!-- Add OR before the conditional statement to process -->
<otherwise>
OR (item_name LIKE'%${keyword}%')
</otherwise>
</choose>
  </foreach>
</select>

Mapper file settings

The xml file can execute the process described by the existence of the Mapper file with the same name (If the inside is returned, the description contents of XX Mapper.java can be simplified by describing XX Mapper.xml).

Describe as follows in the Mapper class with the same name.

xml file.java

``` same name as java

//import omitted

@Mapper public interface 〇 〇 Mapper { List findByCategoryIdAndProductName( @Param("category") long category, @Param("keywords") String[] keywords); //Because it is handled as a collection in xml, use String[] type instead of String type }


FindBy~ is a statement name, which is named to associate the method written in java file with the SQL statement in xml file.

## Get search results with controller
The description of the controller introduced at the beginning is not enough at all. Well, of course I've done it right now, of course, since I've just edited various keywords.

### Get information

```java
public String search(SampleForm f) {
  //keywords only declared
  String keywords = f.getKeywords().replaceAll(" "," ").replaceAll("\\s{2,}", "").trim();
}

I will add the description here. First, only the function to obtain information. This time, we adopted Tymeleaf as the template engine and called the attribute in html as th:each="item:${items}" and outputting the information of each column. The story around here is too heavy, so googling with Tymeleaf.

``


//import, class and other methods are omitted

public String search(SampleForm f, Model m) {
  String keywords = f.getKeywords().replaceAll(" "," ").replaceAll("\\s{2,}", "").trim();
  // Get list of product information
  List<Item> items;
  
  //Send information of items list to the string "items" in search.html
  m.addAttribute("items", items);
  
  // No search function anywhere yet
  //to search.html
  return "search";
}

Implement search function

Added description about search function.

``


//import, class and other methods are omitted

public String search(SampleForm f, Model m) {
  String keywords = f.getKeywords().replaceAll(" "," ").replaceAll("\\s{2,}", "").trim();
  // Get list of product information
  List<Item> items;
  
  // Search processing
  // That is, pass the information of category and keywords to 〇〇Mapper.java
  // Pass information to Mapper.xml and perform SELECT processing, and assign the result to the items list
  items =   Mapper.findByCategoryIdAndProductName(
          f.getCategory(),
          keywords);

  //Send information of items list to the string "items" in search.html
  m.addAttribute("items", items);
  
  //to search.html
  return "search";
}

Implemented the search function. However, this is not complete.

In this state, keywords are only connected by half-width spaces in search keywords, so it cannot be repeated in foreach, and a compilation error occurs due to type mismatch.

arrange keywords

``


//import, class and other methods are omitted

public String search(SampleForm f, Model m) {
  String keywords = f.getKeywords().replaceAll(" "," ").replaceAll("\\s{2,}", "").trim();
  // Get list of product information
  List<Item> items;
  
  // Search processing
  // That is, pass the information of keywords to 〇Mapper.java
  // Pass information to Mapper.xml and perform SELECT processing, and assign the result to the items list
  items =   Mapper.findByCategoryIdAndProductName(
          f.getCategory(),
          //Split the string with half-width spaces and return as an array
          keywords.split(" "));

  //Send information of items list to the string "items" in search.html
  m.addAttribute("items", items);
  
  //to search.html
  return "search";
}

This is completed. Hooray

``



public String search(SampleForm f, Model m) {
  String keywords = f.getKeywords().replaceAll(" "," ").replaceAll("\\s{2,}", "").trim();
  List<Item> items;
  items =   Mapper.findByCategoryIdAndProductName(f.getCategory(), keywords.split(" "));

  m.addAttribute("items", items);
  
  return "search";
}