Keywords
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 **).
OS used-Windows 10 Tools used-Spring Tool Suite 4 4.6.2 Template engine-Thymeleaf Framework-mybatis
<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.
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.
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 **.
First, prepare a method with the controller to edit the search keyword entered from the user side to the beginning form.
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.
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.
\\ s
**{2,}
**Reference: Regular expression sample collection
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.
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%.
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.
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>
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 = "" `` **.
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>
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.
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.
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";
}
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.
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";
}
Recommended Posts