The Way to SSM Learning--Mybatis Day 3_Dynamic sql Statement

I. where and if tags

1. New Method in IUserDao Interface

2. Write in IUserDao.xml

test inside if here is the condition of the judgement statement and the parameter name inside corresponds to the field in the java file instead of the database
Splicing of statements with sql content in if tag
Where tag is actually an override of select * from user where 1=1

It is important to note that when there are multiple if s, because the tag content is a splice of sql statements, you must not forget that the connection of and is as follows
username like #{userName} and sex = ...

<select id="findUserByCondition" resultMap="userMap" parameterType="user">
        select  * from user
        <where>
            <if test="userName != null and userName.length() > 0">
                username like #{userName}
            </if>
            <if test="userSex != null and userSex.length() > 0">
                and sex = #{userSex}
            </if>
        </where>
    </select>

Or mention that the resultMap above is:

<resultMap id="userMap" type="user">
        <id property="userId" column="id"></id>
        <result property="userName" column="username"></result>
        <result property="userBirthday" column="birthday"></result>
        <result property="userSex" column="sex"></result>
        <result property="userAddress" column="address"></result>
    </resultMap>

3. Write test methods

public void testFindUserByCondition(){
        User user = new User();
        user.setUserName("%Old and new%");
        user.setUserSex("female");
        List<User> users = userDao.findUserByCondition(user);
        for (User u : users){
            System.out.println(u);
        }
    }

2. foreach and sql Tags

1. Write method in IUserDao interface

Returns a List passed as a QueryVo entity wrapper class

2. Add fields and get&set to QueryVo entity class

Create a List with all the IDS to find (multiple)

private List<Integer> ids = new ArrayList<Integer>();

public List<Integer> getIds() {
    return ids;
}

public void setIds(List<Integer> ids) {
    this.ids = ids;
}

3. Write test classes

Creating a List named ids is important, as you will see below
Just look at the notes for the rest

public void testFindUserByIds(){
    List<Integer> ids = new ArrayList<Integer>();
    //Create a vo
    QueryVo vo = new QueryVo();
    //Add ids to the ids list to find
    ids.add(67);  
    ids.add(73);  
    ids.add(74);  
    //Pass ids to vo entity class
    vo.setIds(ids);
    //Produce Results List
    List<User> users = userDao.findUserByIds(vo);
    for (User user: users) {
        System.out.println(user);
    }
}

4. Add in xml

The parameter passed in here is a queryvo object
test in if is still the corresponding lookup condition

A key:

Explanation of parameters in <foreach>tag:
Collection: The object name of the collection that needs to be passed in
Open: condition, note that there is a left parenthesis (open parenthesis, so open)
item: the content corresponds to the ID in #{id} below
For example, item="id" corresponds to #{id}, item="uid" corresponds to #{uid}, which is the alias of the incoming parameter.
Close: close parentheses, used with open
separator:sql statement separator between multiple parameters, comma here

<select id="findUserByIds" resultMap="userMap" parameterType="queryvo">
    select * from user
    <where>
        <if test="ids != null and ids.size() >0 ">
            <foreach collection="ids" open="and id in (" item="id" close=")" separator=",">
                #{id}
            </foreach>
        </if>
    </where>
</select>
Six original articles were published. Approved 0. Visits 46
Private letter follow

Tags: SQL xml Java REST

Posted on Sun, 12 Jan 2020 20:34:05 -0500 by saras