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);
】