MyBatis中的动态SQL例子

 

package org.leadfar;

 

import java.util.Date;

 

public class Person {

    private int id;

    private String name;

    private int age;

    private Date birthday;

    public int getId() {

       return id;

    }

    public void setId(int id) {

       this.id = id;

    }

    public String getName() {

       return name;

    }

    public void setName(String name) {

       this.name = name;

    }

    public int getAge() {

       return age;

    }

    public void setAge(int age) {

       this.age = age;

    }

    public Date getBirthday() {

       return birthday;

    }

    public void setBirthday(Date birthday) {

       this.birthday = birthday;

    }

}

 

 

映射文件：

 

       <!-- 测试动态sql01 -->

    <select id="dynamicSql01" parameterType="map" resultType="Person">

           <!-- 对于and或or开头的加上where -->

           select * from t_person

           <where>

           <if test="id!=null">

               id = #{id}

           </if>

           <if test="name!=null">

               and name=#{name}

           </if>

           </where>

    </select>

   

   

   

    <!-- 测试动态sql02 -->

    <select id="dynamicSql02" parameterType="map" resultType="Person">

          

           select * from t_person where id  in

           <foreach collection="ids" open="(" separator="," close=")"  item="id">#{id}</foreach>

          

    </select>

   

   

    <!-- 测试动态sql03 -->

    <select id="dynamicSql03" parameterType="map" resultType="Person">

           <!-- 使用#的话会自动加上一个''，使用$则原样输出 -->

           select * from t_person where id in(${ids})

          

    </select>

 

测试类：

 

 

public void TestDynamicSql01(){

   

    //通过factory获得数据库连接会话

   

    SqlSession session=factory.openSession();

 

 

    try{

       Map map = new HashMap();

       map.put("id",5);

       map.put("name","张总");

       List persons  =(List)session.selectList(Person.class.getName()+".dynamicSql01",map);

       //System.out.println("查出来的Person==="+person.getName()); 

       for (Object object : persons) {

           Person person  = (Person)object;

           System.out.println("查出来的Person==="+person.getName());

       }

      

          

       session.commit();

    }catch(Exception e){

       session.rollback();

       e.printStackTrace();

    }finally{

       session.close();

    }

   

   

   

}

 

 

 

 

public void TestDynamicSql02(){

   

    //通过factory获得数据库连接会话

   

    SqlSession session=factory.openSession();

 

 

    try{

      

       List ids =    new ArrayList();

       ids.add(5);

       ids.add(6);

       ids.add(7);

       ids.add(8);

      

       Map map = new HashMap();

           map.put("ids",ids);

       List persons  =(List)session.selectList(Person.class. getName()+".dynamicSql02",map);

       //System.out.println("查出来的Person==="+person.getName()); 

       for (Object object : persons) {

           Person person  = (Person)object;

           System.out.println("查出来的Person==="+person.getName());

       }

      

          

       session.commit();

    }catch(Exception e){

       session.rollback();

       e.printStackTrace();

    }finally{

       session.close();

    }

   

   

   

}

   

 

 

 

public void TestDynamicSql03(){

   

    //通过factory获得数据库连接会话

   

    SqlSession session=factory.openSession();

 

 

    try{

      

      

      

       Map map = new HashMap();

           map.put("ids","3,4,5");

       List persons  =(List)session.selectList(Person.class. getName()+".dynamicSql03",map);

       //System.out.println("查出来的Person==="+person.getName()); 

       for (Object object : persons) {

           Person person  = (Person)object;

           System.out.println("查出来的Person==="+person.getName());

       }

      

          

       session.commit();

    }catch(Exception e){

       session.rollback();

       e.printStackTrace();

    }finally{

       session.close();

    }

   

   

   

}

 

 

详细配置参考增删插改的例子