ibatis中的分页、模糊查询、批改 等基本sql语句

ibatis中的分页、模糊查询、修改 等基本sql语句

创建session单例类：
public class SessionUtil {
private static SqlSessionFactory factory;
static{
Reader reader = null;
try {
reader = Resources.getResourceAsReader("config.xml");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
factory   = new SqlSessionFactoryBuilder().build(reader,"MySql");
}

public static SqlSession  getSession(){
return factory.openSession();
}


}

   <!-- 逻辑分页 -->
SqlSession  session = SessionUtil.getSession();
<select id="getAllDep"  resultType="com.pojos.Dep">
       select * from dep
   </select>
   【
// 从第几个开始，显示几个
// 每页3条，显示第8页
// new RowBounds(3*(8-1),3))
List<Dep> list = session.selectList("stu.getAllDep",null,new RowBounds(6,3));
    】

 

  
   <!-- 物理分页 -->
   <select id="getAllDep2"  resultType="com.pojos.Dep" parameterType="map">
       select * from dep limit #{x},#{y}
   </select>
   【
// 从第几个开始，显示几个
// 每页3条，显示第2页
// new RowBounds(3*(2-1),3))
SqlSession  session = SessionUtil.getSession();
Map map = new HashMap();
map.put("x",3*(2-1));
map.put("y", 3);

List<Dep> list = session.selectList("stu.getAllDep2",map);
    】

     
   <!-- 模糊查询实现 -->
   <select id="mohu" resultType="com.pojos.Dep">
      select * from dep where depname like concat('%',concat(#{depname},'%'))
   </select>
   【
SqlSession  session = SessionUtil.getSession();
List<Dep> list = session.selectList("stu.mohu","要搜索的字");
    】
//sql语句块 用可调用：
   <sql id="whereDep">
       <where>
           <if test="depid!=null and depid!=''">
               depid=#{depid}
           </if>
          
           <if test="depname!=null and depname!=''">
               and depname like concat(#{depname},'%')
           </if>
        </where>
   </sql>
  
   <!-- 动态sql之where和if -->
   <select id="dt1" resultType="com.pojos.Dep" parameterType="com.pojos.Dep">
        select * from dep
          <include refid="whereDep"/>
       
   </select>
  【
SqlSession  session = SessionUtil.getSession();

Dep dep2 = new Dep();
dep2.setDepid(1);
dep2.setDepname("国");
List<Dep> list = session.selectList("stu.dt1",dep2);
   】  


  
   <!-- 动态sql之修改 -->
   <update id="dt2" parameterType="com.pojos.Dep">
       update dep
         <set>
           <if test="depid!=null and depid!=''">
               depid=#{depid},
           </if>
          
           <if test="depname!=null and depname!=''">
               depname=#{depname}
           </if>
         </set>
        
          <if test="depid!=null and depid!=''">
               where depid=#{depid}
           </if>
   </update>
   【
SqlSession  session = SessionUtil.getSession();

Dep dep2 = new Dep();
dep2.setDepid(1);
dep2.setDepname("国");
int n = session.update("stu.dt2",dep2);
session.commit();
session.close();
    】  

  
   <!-- 动态sql之循环  collection="list/arry" -->
   <select id="dt3" resultType="com.pojos.Dep">
         select * from dep where depid in
            <foreach collection="list" item="x" open="(" close=")" separator=",">
               #{x}
            </foreach>
   </select>
   【
SqlSession  session = SessionUtil.getSession();

    List aa = new ArrayList();
aa.add(1);
aa.add(3);
aa.add(4);
aa.add(2);


List<Dep> list = session.selectList("stu.dt3",aa);
    】