where set trim sql if foreach 实现xml方式的动态sql

来源：互联网发布：2016年6月进出口数据编辑：程序博客网时间：2024/05/16 09:11

1.student-mapping.xml

<?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="dyna">    <!-- create table STUDENT (                 ID NUMBER not null,                 NAME VARCHAR2(20),                 SEX NUMBER(2),                 HOBBY VARCHAR2(200),                 AGE NUMBER )     -->    <!-- sql用于定义共享的sql 其他select中通过include引用该sql -->    <sql id="queryColumn">       name,sex,age   </sql>   <select id="queryStudnetABCDEFG">      select      <!-- include 应用 -->      <include refid="queryColumn"></include>      from book   </select>    <!-- trim          suffixOverrides          prefixOverrides 要覆盖的字符串         suffix          prefix 替换的字符串    -->    <insert id="addStudent" parameterType="map" >        insert into student(        <!-- trim 前缀替换用法 -->        <trim prefixOverrides="," prefix="">            ,id            <if test="name!=null and name!=null" >                ,name            </if>            <if test="sex!=null and sex!=null" >                ,sex            </if>            <if test="hobby!=null and hobby!=null" >                ,hobby            </if>            <if test="age!=null and age!=null" >                ,age            </if>        </trim>        )        values(        #{id},        <!-- trim 后缀替换用法 -->        <trim suffixOverrides="," suffix="">            <if test="name!=null and name!=''" >                #{name},            </if>            <if test="sex!=null and sex!=''" >                #{sex},            </if>            <if test="hobby!=null and hobby!=''" >                #{hobby},            </if>            <if test="age!=null and age!=''" >                #{age},            </if>        </trim>        )        <!-- selectKey主键生成 -->        <selectKey keyProperty="id"                   order="BEFORE"                   resultType="string"        >            select max(id)+1 from student        </selectKey>    </insert>    <!-- where -->    <select id="queryStudent" parameterType="map" resultType="map">        select * from student        <!-- where 默认代替第一的 and 为 where-->        <where>            <if test="id!=null and id!=''" >                and id=#{id}            </if>            <if test="name!=null and name!=''" >                and name=#{name}            </if>            <if test="sex!=null and sex!=''" >                and sex=#{sex}            </if>            <if test="hobby!=null and hobby!=''" >                and hobby=#{hobby}            </if>            <if test="age!=null and age!=''" >                and age=#{age}            </if>        </where>    </select>    <!-- set -->    <update id="updateStudent" parameterType="map"  >        update student        <!-- set 默认会去掉最后一个   逗号（,）  -->        <set>            <if test="name!=null and name!=''" >                name=#{name},            </if>            <if test="sex!=null and sex!=''" >                sex=#{sex},            </if>            <if test="hobby!=null and hobby!=''" >                hobby=#{hobby},            </if>            <if test="age!=null and age!=''" >                age=#{age},            </if>        </set>        where id=#{id}    </update>    <!-- foreach -->    <select id="queryStudentByHobby" parameterType="map" resultType="map">        <!-- open  循环开始前的字符串              close 循环结束的字符串             collection 循环的参数(该参数是一个集合或数组)             index 循环的索引             item  循环所使用的变量             separator 除了最后一次循环外 所要拼接的字符串（分隔符）        -->        <foreach  open="select * from student where hobby in("                   close=")"                   collection="hobbys"                   index="i"                   item="hobby"                   separator=","        >            #{hobby}        </foreach>    </select></mapper>

private static SqlSession sqlSession;    @Before    public void getSqlSession() throws IOException{        if (sqlSession==null) {            final String mybatisConf = "mybatis-conf.xml";            InputStream in = Resources.getResourceAsStream(mybatisConf);            SqlSessionFactory ssf= new SqlSessionFactoryBuilder() .build(in);            sqlSession=ssf.openSession();        }    }    @After    public void closeSqlSession(){        sqlSession.commit();        sqlSession.close();    }@Test    public void testDynaSqlWhere(){        String statement="dyna.queryStudent";        Map map= new HashMap();        map.put("id", "1");        map.put("name", "oracle");        List result=sqlSession.selectList(statement, map);        System.out.println("--->  " +result);    }    @Test    public void testDynaSqlSet(){        String statement="dyna.updateStudent";        Map map= new HashMap();        map.put("id", "1");        map.put("name", "11122");        map.put("age", "30");        sqlSession.update(statement, map);    }    @Test    public void testDynaSqlForEach(){        String statement="dyna.queryStudentByHobby";        Map map= new HashMap();        String[] hobbys={"playgame","readbook","listenCD"};        map.put("hobbys",hobbys );        List result=sqlSession.selectList(statement, map);        System.out.println("--->  " +result);    }    @Test    public void testDynaSqlTrim(){        String statement="dyna.addStudent";        Map map= new HashMap();        map.put("hobby","playgame");        sqlSession.insert(statement, map);    }

0 0