MyBatis fuzzy query MySQL recorded in the common three ways

MyBatis's fuzzy query function is widely used in MySQL database as an example (different databases, some may not support)
There are three ways to use fuzzy queries:

1. Direct use of the% stitching string, such as ‘%‘#{name}‘%‘ or "%"#{name}"%" , single or double quotation marks can be.
2. Stitching with concat (STR1,STR2) function
3. Use MyBatis's bind tag

There are now records in database Mybatis1 in table users:

User [Id=2,NAME=BB, phone=13422222222, [email protected]163.com]user [Id=5,Name= Guan Yu, phone=13333333333, [email protected]]user [Id=6,Name= Zhang Fei, phone=13344444444, [email protected]]user [id=7, name= Zhao Yun, phone=13355555555, [Email protected]]user [id= 8, name= Jack Huang, Phone=13366666666, [ Email protected]]user [id=9, name= Cao, Phone=13477883429, [Email protected]]user [ Span class= "Hljs-property" >id=10, name= Guo Jia, Phone=13447685234, [Email protected]]user [id= 11, name= Zhang Sanfeng, phone=13423455432, [email protected]]               

To create a new label for the mapped SQL in the Usermapper.xml file

<!--******************** 3 ways to use fuzzy queries: *********************--<SelectId="Getusersbyfuzzyquery"Parametertype="User"Resulttype="User" > select<IncludeRefid="Columns"/> from users<Where><!--method One: directly using the% stitching string Note: This cannot be written as "%#{name}%", #{name} becomes part of the string, an exception occurs: The error occurred while setting parameters , should be written: "%" #{name} "%", i.e. #{name} is a whole, plus and minus<Iftest="Name! = NULL" > name like "%" #{name} "%"</If><!--method Two: Use the concat (STR1,STR2) function to connect two parameters--<Iftest= "Phone! = null" > and phone like concat (concat ("%", #{ Phone}), "%") </if> <!--method Three: Bind the string using the bind tag, and then use the LIKE keyword for the bound string for a fuzzy query--<if test= "email! = NULL" > <bind name=" pattern " value= "'% ' +email+ '% '"/> and email like #{pattern} </if> </ where> </SELECT>      

1. Test name: Pass in the Name property, fuzzy matches the data record containing the word "Zhang" in name

@Testpublic void fuzzyQuery(){    SqlSession session = MybatisUtils.getSession(false);    User u = new User(); u.setName("张"); List<User> userList = session.selectList("com.qcc.mapping.userMapper.getUsersByFuzzyQuery", u); for (User user : userList) { System.out.println(user); }}

Test results:

User [id=6, name=张飞, phone=13344444444, [email protected]com]User [id=11, name=张三丰, phone=13423455432, [email protected]]

2, Test Phone: Incoming phone properties, fuzzy matching phone contains "44" of all user information

@Testpublic void fuzzyQuery(){    SqlSession session = MybatisUtils.getSession(false);    User u = new User();// u.setName("张"); u.setPhone("44"); List<User> userList = session.selectList("com.qcc.mapping.userMapper.getUsersByFuzzyQuery", u); for (User user : userList) { System.out.println(user); }}

Test results:

User [id=6, name=张飞, phone=13344444444, [email protected]]User [id=10, name=郭嘉, phone=13447685234, [email protected]]

3, test email: Incoming email attributes, fuzzy matching email contains all the User information Shu

@Testpublic void fuzzyQuery(){    SqlSession session = MybatisUtils.getSession(false);    User u = new User();// u.setName("张");// u.setPhone("44"); u.setEmail("shu"); List<User> userList = session.selectList("com.qcc.mapping.userMapper.getUsersByFuzzyQuery", u); for (User user : userList) { System.out.println(user); }}

Test results:

User [id=5, name=关羽, phone=13333333333, [email protected]]User [id=6, name=张飞, phone=13344444444, [email protected]]User [id=7, name=赵云, phone=13355555555, [email protected]]User [id=8, name=黄忠, phone=13366666666, [email protected]]

Comprehensive test: Multi-conditional dynamic fuzzy query:

/** * 根据 name, phone, email 字段多条件动态模糊查询 */@Testpublic void fuzzyQuery(){ SqlSession session = MybatisUtils.getSession(false); User u = new User("张", "44", "shu"); List<User> userList = session.selectList("com.qcc.mapping.userMapper.getUsersByFuzzyQuery", u); for (User user : userList) { System.out.println(user); }}

Results:

User [id=6, name=张飞, phone=13344444444, [email protected]]

Found the results of the normal query came out, that the three kinds of fuzzy query writing is not a problem at the same time.

MyBatis fuzzy query MySQL recorded in the common three ways