MyBatis Study (Fri)

Video View Address: Http://edu.51cto.com/course/14674.html?source=so

1. Dynamic sql1.1, if

Requirements: Query All users, if the user filled out the name, by the name of the fuzzy query, if the user filled out the name and age, then by name and age together to find

1. Interface definition

public List<User> querybyUser(User vo) throws Exception;

2. Mapper.xml Configuration

<select id = "querybyUser" resultMap="UserMap">    select * from tb_user where 1=1    <if test="userName!=null and userName!=‘‘">       and user_Name like #{userName}    </if>    <if test = "age!=null">        and age =#{age}    </if>  </select>

3. Test method

@Test    public void testIf()throws Exception{        User user = new User();        user.setUserName(null);        user.setAge(17);        List<User> list = userMapper.querybyUser(user);        for (User u : list) {            System.out.println(u);        }    }

4. Log

DEBUG - Opening JDBC ConnectionDEBUG - Created connection 276327391.DEBUG - Setting autocommit to false on JDBC Connection [[email protected]]DEBUG - ==>  Preparing: select * from tb_user where 1=1 and age =? DEBUG - ==> Parameters: 17(Integer)DEBUG - <==      Total: 1User [userid=10, userName=阿珂3, pwd=123456, age=17, sex=女, birthday=Mon Aug 13 14:57:50 CST 2018]DEBUG - Resetting autocommit to true on JDBC Connection [[email protected]]DEBUG - Closing JDBC Connection [[email protected]]DEBUG - Returned connection 276327391 to pool.

1.2. where

In the preceding SQL we think of defining a where 1=1 something like this, which we can do with MyBatis's where tag.

<select id = "querybyUser" resultMap="UserMap">    select * from tb_user     <where>        <if test="userName!=null and userName!=‘‘">            and user_Name like #{userName}        </if>        <if test = "age!=null">            and age =#{age}        </if>    </where>  </select>

Where: automatically generated on demand and can be eliminated without the need for and

Direct test can

1.3. foreach

Another necessary operation that is common to dynamic SQL is to iterate over a collection, usually built in the in condition.

interface method definition

public List<User> querybyIn(QueryUser vo) throws Exception;

Mapper.xml

<select id = "querybyIn" resultMap="UserMap">        select * from tb_user        <where>            <if test="userids!=null">                <!--                     collection:要迭代集合或者数组                -->                <foreach collection="userids" open="userid in (" close=")" separator="," item="userid">                    #{userid}                </foreach>            </if>        </where>  </select>

Test method

@Test    public void testforeach()throws Exception{        QueryUser queryUser= new QueryUser();        queryUser.setUserids(new int[]{8,9,2,3});        List<User> list = userMapper.querybyIn(queryUser);        for (User u : list) {            System.out.println(u);        }    }

Log

DEBUG - Opening JDBC ConnectionDEBUG - Created connection 1060925979.DEBUG - Setting autocommit to false on JDBC Connection [[email protected]]DEBUG - ==>  Preparing: select * from tb_user WHERE userid in ( ? , ? , ? , ? ) DEBUG - ==> Parameters: 8(Integer), 9(Integer), 2(Integer), 3(Integer)DEBUG - <==      Total: 4User [userid=8, userName=阿珂, pwd=123456, age=18, sex=女, birthday=Mon Aug 13 14:06:14 CST 2018]User [userid=9, userName=阿珂2, pwd=123456, age=18, sex=女, birthday=Mon Aug 13 14:56:45 CST 2018]User [userid=2, userName=张三, pwd=123456, age=10, sex=男, birthday=Mon Aug 13 10:07:55 CST 2018]User [userid=3, userName=李四, pwd=123456, age=10, sex=男, birthday=Mon Aug 13 10:07:55 CST 2018]DEBUG - Resetting autocommit to true on JDBC Connection [[email protected]]DEBUG - Closing JDBC Connection [[email protected]]DEBUG - Returned connection 1060925979 to pool.

1.4. Set

The set element can be used to dynamically include the updated column without the need for updates (dynamic update)

Interface definition

public int dycUpdate(User vo) throws Exception;

Mapper.xml definition

<update id="dycUpdate">    update tb_user    <set>        <if test="userName!=null and userName!=‘‘">            user_name=#{userName},        </if>        <if test="pwd!=null and pwd!=‘‘">            pwd=#{pwd},        </if>        <if test="age!=null">            age=#{age},        </if>        <if test="sex!=null">            sex=#{sex},        </if>        <if test="birthday!=null">            birthday=#{birthday},        </if>    </set>    where userid=#{userid}  </update>

Test method

@Test    public void testdycUpdate()throws Exception{        User user = new User();        user.setUserName("hello");        user.setSex("男");        user.setBirthday(new Date());        user.setUserid(8);        userMapper.dycUpdate(user);        sqlSession.commit();    }

Log

DEBUG - Opening JDBC ConnectionDEBUG - Created connection 340839523.DEBUG - Setting autocommit to false on JDBC Connection [[email protected]]DEBUG - ==>  Preparing: update tb_user SET user_name=?, sex=?, birthday=? where userid=? DEBUG - ==> Parameters: hello(String), 男(String), 2018-08-14 10:19:19.311(Timestamp), 8(Integer)DEBUG - <==    Updates: 1DEBUG - Committing JDBC Connection [[email protected]]DEBUG - Resetting autocommit to true on JDBC Connection [[email protected]]DEBUG - Closing JDBC Connection [[email protected]]DEBUG - Returned connection 340839523 to pool.

1.5, dynamic add

Interface definition

public int dycInsert(User vo) throws Exception;

Mapper.xml file

 <insert id= "Dycinsert" > INSERT into Tb_user (userid, <trim suffixoverrides                    = "," > <if test= "username!=null and username!=" "> User_name,                    </if> <if test= "Pwd!=null and pwd!=" > pwd,                     </if> <if test= "Age!=null" > Age, </if>                    <if test= "sex!=null" > Sex, </if> <if test= "Birthday!=null" > Birthday, </if> </t rim>) VALUES (seq_user.nextval, <trim suffixoverrides= "," > <if te St= "Username!=null and username!=" "> #{username}, </if> <if test = "Pwd!=null and pwd!="> #{pwd}, </if> <if test= "Age!=null" > # {age}, </if> <if test= "Sex!=null" > #{sex}, &lt            ;/if> <if test= "Birthday!=null" > #{birthday}, </if> </trim>) </insert>

Test method

@Test    public void testdycInsert()throws Exception{        User user = new User();        user.setUserName("yui");        user.setSex("男");        user.setBirthday(new Date());        user.setUserid(8);        userMapper.dycInsert(user);        sqlSession.commit();    }

Log

DEBUG - Opening JDBC ConnectionDEBUG - Created connection 1341177488.DEBUG - Setting autocommit to false on JDBC Connection [[email protected]]DEBUG - ==>  Preparing: insert into tb_user ( userid, user_name, sex, birthday ) values( seq_user.nextval, ?, ?, ? ) DEBUG - ==> Parameters: yui(String), 男(String), 2018-08-14 10:31:06.973(Timestamp)DEBUG - <==    Updates: 1DEBUG - Committing JDBC Connection [[email protected]]DEBUG - Resetting autocommit to true on JDBC Connection [[email protected]]DEBUG - Closing JDBC Connection [[email protected]]DEBUG - Returned connection 1341177488 to pool.

1.6, choose, when, otherwise

Sometimes we don't want to apply all the conditions, instead we want to choose one of many situations. Switch and statements in Java are similar, MyBatis provides choose elements.

Requirements: Query All users

1. If the user fills in the gender and the name, then search by name

2. If the user fills in a name, search by name

3. If the user does not fill in the name, but the gender is filled, then search by gender

Interface declaration

public List<User> queryDyc(QueryUser vo) throws Exception;

Mapper.xml file

<select id="queryDyc" resultMap="UserMap">        select * from tb_user        <where>            <choose>                <when test="name!=null and name!=‘‘">                    user_name=#{name}                </when>                <when test="sex!=null and sex!=‘‘">                    sex=#{sex}                </when>                <otherwise>                    age>5                </otherwise>            </choose>        </where>  </select>

Test method

@Test    public void testIfelse()throws Exception{        QueryUser queryUser= new QueryUser();        queryUser.setName(null);        queryUser.setSex(null);        List<User> list = userMapper.queryDyc(queryUser);        for (User u : list) {            System.out.println(u);        }    }

Log

debug-opening jdbc connectiondebug-created connection 480779844.debug-setting autocommit to false on JDBC connection [[email protected]] Debug-==> Preparing:select * from Tb_user WHERE age>5 DEBUG-==> parameters:debug-<== Total:7use R [Userid=8, Username=hello, pwd=123456, age=18, sex= Male, Birthday=tue-10:19:19 CST 2018]user [userid=9, Username= Alexia 2 , pwd=123456, age=18, sex= female, Birthday=mon 14:56:45 CST 2018]user [userid=10, Username= Alexia 3, pwd=123456, age=17, sex= Female, Birthday=mon 14:57:50 CST 2018]user [userid=2, Username= Zhang San, pwd=123456, age=10, sex= Male, Birthday=mon 13 10:07  : 2018]user CST [userid=3, Username= John Doe, pwd=123456, age=10, sex= Male, Birthday=mon 10:07:55 CST 2018]user [Userid=4, Username= Harry, pwd=123456, age=10, sex= male, Birthday=mon 10:07:55 CST 2018]user [userid=5, Username= 赵六, pwd=123456, AG e=10, sex= Male, Birthday=mon 10:07:55 CST 2018]debug-resetting autocommit to True on JDBC Connection [[Email protected]]debug-closing JDBC Connection [[email protected]]debug-returned Connection 480779844 to pool. 

1.7. SQL Fragment

The SQL fragment is meant to save some commonly used SQL statements and to reference them when needed.

If the SQL fragment and statement are under the same namespace, the direct reference

 <sql id="basesql">        select * from tb_user  </sql>  <select id="queryDyc" resultMap="UserMap">        <include refid="basesql"/>        <where>            <choose>                <when test="name!=null and name!=‘‘">                    user_name=#{name}                </when>                <when test="sex!=null and sex!=‘‘">                    sex=#{sex}                </when>                <otherwise>                    age>5                </otherwise>            </choose>        </where>  </select>

If the SQL fragment and statement are not in the same namespace, then the namespace is used directly. Sqlid form of reference

 <select id="queryDyc" resultMap="UserMap">        <include refid="mycomm.baseselect"/>        <where>            <choose>                <when test="name!=null and name!=‘‘">                    user_name=#{name}                </when>                <when test="sex!=null and sex!=‘‘">                    sex=#{sex}                </when>                <otherwise>                    age>5                </otherwise>            </choose>        </where>  </select>

MyBatis Study (Fri)