Mybatis series (3) – Foundation – mybatis multi condition query and fuzzy query example

Time:2020-11-16

Step 1: run first, see the effect, and then learn
Step 2: imitation and troubleshooting
Step 3: Based on the previous step
Step 4: fuzzy query
Step 5: multi condition query

Step 1: run first, see the effect, and then learn

As the old rule, first download the executable items in the download area (click to enter), configure and run them, and then learn what steps to do to achieve this effect.

Step 2: imitation and troubleshooting

After making sure that the runnable project runs correctly, follow the steps of the tutorial and imitate the code again.
It is inevitable that there is code discrepancy in the simulation process, which leads to the failure to get the expected running resultsright key(can run the project) and your own code to locate the problem.
In this way,Learning is effective and troubleshooting is efficientIt can obviously improve the learning speed and cross the threshold of learning.

It is recommended to use diffmerge software for folder comparison. Compare your own project folder with my runnable project folder.
This software is very powerful, you can know which two files in the folder are wrong, and mark them out clearly
Here is a green installation and use tutorial: diffmerge download and use tutorial

Step 3: Based on the previous step

This knowledge point is based on the mybatis tutorial.

Step 4: fuzzy query

1. Modification Category.xml To provide a list gateway byname query statement

select * from   category_  where name like concat('%',#{0},'%')

concat(‘%’,#{0},’%’)This is how to write mysql
If it is Oracle, write it as follows

select * from   category_  where name like '%'||#{0}||'%'

2. Running test

List<Category> cs = session.selectList("listCategoryByName","cat");

Mybatis series (3) - Foundation - mybatis multi condition query and fuzzy query example

  • Category.xml
  • TestMybatis.java
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.how2java.pojo">
    <select id="listCategoryByName"  parameterType="string" resultType="Category">
            select * from   category_  where name like concat('%',#{0},'%')
        </select>
</mapper>

package com.how2java;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.how2java.pojo.Category;

public class TestMybatis {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        List<Category> cs = session.selectList("listCategoryByName", "cat");
        for (Category c : cs) {
            System.out.println(c.getName());
        }
        session.commit();
        session.close();

    }
}

Step 5: multi condition query

Combined with the previous fuzzy query, how many more conditions do you need
One Category.xml Prepare SQL statement

<select id="listCategoryByIdAndName"  parameterType="map" resultType="Category">
    select * from   category_  where id> #{id}  and name like concat('%',#{name},'%')
</select>

2. Test code
Because there are multiple parameters, and the selectlist method only accepts one parameter object, you need to put multiple parameters in the map, and then pass the map object as a parameter

Map<String,Object> params = new HashMap<>();
params.put("id", 3);
params.put("name", "cat");
List<Category> cs = session.selectList("listCategoryByIdAndName",params);

Mybatis series (3) - Foundation - mybatis multi condition query and fuzzy query example

  • Category.xml
  • TestMybatis.java
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.how2java.pojo">
    <select id="listCategoryByName"  resultType="Category">
            select * from   category_  where name like concat('%',#{0},'%')
        </select>
    <select id="listCategoryByIdAndName"  parameterType="map" resultType="Category">
            select * from   category_  where id> #{id}  and name like concat('%',#{name},'%')
        </select>
</mapper>

package com.how2java;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.how2java.pojo.Category;

public class TestMybatis {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        Map<String,Object> params = new HashMap<>();
        params.put("id", 3);
        params.put("name", "cat");

        List<Category> cs = session.selectList("listCategoryByIdAndName",params);
        for (Category c : cs) {
            System.out.println(c.getName());
        }
        session.commit();
        session.close();
    }
}

For more information, click to learn: https://how2j.cn/k/mybatis/mybatis-query/1107.html