Thing you want to do

Keywords

Convert from full-width space to half-width space (edit ①)
Convert from 2 or more half-width spaces to 1 half-width space (edit ②)
Delete leading and trailing spaces (edit ③)

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

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">
	
	SELECT * FROM item_table
	  WHERE cate_id = #{category}
      AND
	
	  <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 have become confused with this. If you have no idea what you are doing, please read on. I wasn't sure what the benefits of using mybatis would be, so I'll start with the very basics.

What is SQL

One of the computer languages (** not a programming language **). The following is the basic CRUD function syntax used when connecting to a database.

** 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 (reference) **

SELECT column name FROM table name;

** UPDATE **

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

** DELETE **

DELETE FROM table name;

Add WHERE to the end of these sentences to specify more detailed data. Since it deviates from the main line, I will stop here for SQL.

What is MyBatis

According to MyBatis's Official Site

What is MyBatis?

MyBatis is a great persistence framework for custom SQL, stored procedures and advanced mapping processing. By using MyBatis, there is almost no need to write code that handles JDBC directly or manually set query arguments and query results. You can configure MyBatis and associate database records with Java objects using XML or annotations.

Roughly speaking, it seems to be a ** framework that works well when writing complex SQL **.

Main subject

First, prepare a method with the controller to edit the search keyword entered from the user side to the beginning form.

Keyword processing

Search keywords are declared as String keywords in the form class.

The story is a little off, but the form class is a file prepared when you want to send information in a batch. There are only setters and getters inside.

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

The replaceAll method takes two arguments and replaces a specific character string at once. The first method replaceAll ("", "") converts full-width space to half-width space (edit ①). In the second method replaceAll ("\\ s {2,}", ""), two or more half-width spaces are converted to one half-width space (edit ②). The third method, trim (), removes leading and trailing whitespace.

About the replaceAll method

You can see the first one, but for the second one, I want to add a condition of "two or more", so I use metacharacters.

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

Reference: Regular expression sample collection

About SQL statement for search

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

"OR search for a word that contains a certain keyword" is, for example, ** If you search for the keywords "ai", "ue", and "o", you can find any of "ai", "ue", or "o" somewhere in the character string. The movement of extracting all the words it contains **.

First, the sentence that extracts all the values that include the word A

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

The sentence that extracts all the values including the words "a", "i", and "u"

SELECT *FROM table name WHERE(Column name LIKE'%Ai%')
                           OR (Column name LIKE'%up%')
                           OR (Column name LIKE'%O%');

Reference: [How to specify multiple search conditions for one column](https://docs.microsoft.com/ja-jp/sql/ssms/visual-db-tools/specify-multiple-search-conditions -for-one-column-visual-database-tools? view = sql-server-ver15) I succeeded without parentheses (), so either one may be fine.

I was in trouble here

There may be no search keyword, or there may be multiple search keywords. Moreover, I don't know how many will be entered. If this is not a search with the LIKE clause but a match (=) condition, it seems possible to connect them all together with IN, but this time it is impossible.

The same process for multiple things is an iterative process, but java can only operate the "ai" part enclosed in%.

solution

This is a solution that can be done because the framework is MyBatis, ** You can write iterative processing by setting conditions in the SQL statement **. This time, I will use four of <foreach> <choose> <when> <otherwise>.

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

foreach From how to use the <foreach> tag.

First of all, as a major premise, it is common with other programming languages The ** foreach statement **, also known as the ** extended for statement **, takes out the values of ** multiple elements such as arrays and collections in order and processes them **.

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

Before describing the process, specify six properties. In some cases, open and close are not necessary.

`ʻitem appropriately: Specify the name of the value to be handled in the foreach tag
`ʻindex appropriately: Specify the name of the index
`` collection merely: Specify the collection you want to iterate
`ʻopen appropriately: Specify here to add something to the beginning of the process
`` close incorrectly: Specify here if you want to add something at the end of the process
`` separator separately: Specifies what words are separated by what is stored in the collection

This time, the keywords are stored in a variable called keywords, and each value is specified as a keyword in the foreach statement. Also, when searching, the search is performed together with the category, so the outline of the SELECT statement looks like this.

SELECT * FROM item_table
　　WHERE cate_id = #{category} AND
	
	<foreach collection="keywords" item="keyword" open="(" close=")" index="i" separator="" >
	  <!--Write the processing content here-->	
	</foreach>

Quiet: Do you need open and close?

From the conclusion, it is necessary this time.

For example, if the category is 1 and "Ai" and "Ue" are specified as search keywords, the entire foreach process must be enclosed in parentheses.

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

In such a process, 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'%Ai%' OR Column name LIKE'%up%')

You can tell if it is properly enclosed in parentheses ().

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 processing corresponding to else is required, so use it properly depending on the case.

When using <when> <otherwise>, enclose the whole thing in the <choose> tag. When using the <if> tag, do not use the <choose> tag.

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

Final SQL statement

To consider 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'%O%');

Like this, I want to enclose the ** first keyword ** as it is in%, and for the ** subsequent keywords **, add an OR at the beginning and put the keyword in%. Specify the sentence of ↑ 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	
　  <!--Extract values from keywords in order and repeat processing-->
    <foreach collection="keywords" item="keyword" open="(" close=")" index="i" separator="" >

      <!--I want to use when and otherwise, so enclose it in choose-->
	  <choose>
        <!--Processing when index number 0-->
		<when test="i == 0">
			(item_name LIKE '%${keyword}%')
		</when>
        <!--Otherwise-->
        <!--Process by adding OR before the conditional statement-->	
		<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 (turning it over, the description content of XX Mapper.java can be simplified by describing XX Mapper.xml).

In the Mapper class with the same name, describe as follows.

`Same name as xml file.java`


//import omitted

@Mapper
public interface 〇〇Mapper {
    List<MstProduct> findByCategoryIdAndProductName(
    @Param("category") long category,
    @Param("keywords") String[] keywords);
    //Since it is treated as a collection in xml, it is not a String type but a String[]Make a mold
}

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

Get search results with controller

The description of the controller introduced at the beginning is not enough. Well, the only thing I've done right now is that I just edited the keywords in various ways, so it's natural.

Get information

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

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

`python`


//Omit imports, classes and other methods

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

Implemented search function

Added a description about the search function.

`python`


//Omit imports, classes and other methods

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

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

Implemented search function. However, this is not complete.

In this state, keywords are just the search keywords connected by a half-width space, so it cannot be iteratively processed by foreach, and a compile error occurs due to a type mismatch.

Arrange keywords

`python`


//Omit imports, classes and other methods

public String search(SampleForm f, Model m) {
  String keywords = f.getKeywords().replaceAll("　", " ").replaceAll("\\s{2,}", " ").trim();
  //Get a list of product information
  List<Item> items;
  
  //Search process
  //In other words, 〇〇Mapper.Pass the keywords information to java
  //Furthermore, 〇〇Mapper.Pass information to xml, SELECT process, and assign the result to items list
  items = 〇〇Mapper.findByCategoryIdAndProductName(
          f.getCategory(),
          //Split the character string with a half-width space with the split method and return it as an array
          keywords.split(" "));

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

That's all there is to it. Hooray

`python`



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";
}

Create a dynamic SQL statement with MyBatis [Allow search by multiple words]

Thing you want to do

environment

Result (description of xml file)

What is SQL

What is MyBatis

Main subject

Keyword processing

About the replaceAll method

About SQL statement for search

I was in trouble here

solution

Quiet: Do you need open and close?

Final SQL statement

Mapper file settings

Same name as xml file.java

Get search results with controller

Get information

python

Implemented search function

python

Arrange keywords

python

python

`Same name as xml file.java`

`python`

`python`

`python`

`python`