上一篇 MyBatis学习笔记 - (1)MyBatis CRUD基本使用，本文在上一篇案例基础上添加分页查询、动态添加、动态查询、动态插入、动态删除功能。
StudentDaoImpl.java中分别添加以下方法，在StudentMapper.xml中添加对应的映射：
1.分页查询(根据索引和每页数据量)
/**
* 分页查询
* */
@Override
public List<Student> findByPager(int pager, int count) {
SqlSession sqlSession = MybatisUtil.getSqlSesson();
Map<String, Integer> map = new LinkedHashMap<>();
map.put("offset", pager * count);
map.put("count", count);
List<Student> studentList = sqlSession.selectList(Student.class.getName() + ".findByPager", map);
sqlSession.commit();
return studentList;
}
分页查询数据sql 语句：
select * from students limit ? offset ?
对应StudentMapper.xml 配置
<!--分页查询-->
<select id="findByPager" parameterType="map" resultType="com.test.mybatis.Student">
select * from students limit #{count } offset #{offset}
</select>
2.动态添加（根据id、name、height是否为空自动拼接sql语句）
/**
* 动态添加
*/
@Override
public void addDynamic(Integer id, String name, Float height) {
Student student = new Student(id, name, height);
SqlSession sqlSession = MybatisUtil.getSqlSesson();
sqlSession.insert(Student.class.getName() + ".addDynamic", student);
sqlSession.commit();
}
添加数据sql 语句：
insert into students (s_id , s_name , s_height) values (? , ? , ? )
对应StudentMapper.xml 配置
<!--动态添加-->
<sql id="key">
<trim suffixOverrides=",">
<if test="id!=null">
s_id,
</if>
<if test="name!=null">
s_name,
</if>
<if test="height!=null">
s_height,
</if>
</trim>
</sql>
<sql id="value">
<trim suffixOverrides=",">
<if test="id!=null">
#{id},
</if>
<if test="name!=null">
#{name},
</if>
<if test="height!=null">
#{height},
</if>
</trim>
</sql>
<insert id="addDynamic" parameterType="com.test.mybatis.Student">
insert into students (<include refid="key"/>) values (<include refid="value"/>)
</insert>
3.动态删除（根据id、name、height是否为空自动拼接sql语句，实现指定条件删除）
/**
* 动态删除
*/
@Override
public void deleteDynamic(Integer [] ids ) {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSesson();
sqlSession.delete(Student.class.getName() + ".deleteDynamic",ids);
sqlSession.commit();
} catch (Exception e) {
sqlSession.rollback();
System.out.println(e.getMessage());
throw e;
} finally {
MybatisUtil.closeSqlSesson();
}
}
删除数据sql 语句：
delete students where s_id in ( ?,?,?)
对应StudentMapper.xml 配置
<delete id="deleteDynamic">
delete from students where s_id in
<foreach collection="array" open="(" close=")" separator="," item="ids">
#{ids}
</foreach>
</delete>
3.动态查询（根据id、name、height是否为空自动拼接sql语句，实现多条件查询）
/**
* 动态查询
*/
@Override
public List<Student> findDynamic(Integer id, String name, Float height) {
SqlSession sqlSession = null;
Map<String, Object> map = new LinkedHashMap<>();
map.put("id", id);
map.put("name", name);
map.put("height", height);
List<Student> studentList;
try {
sqlSession = MybatisUtil.getSqlSesson();
studentList = sqlSession.selectList(Student.class.getName() + ".findDynamic", map);
sqlSession.commit();
} catch (Exception e) {
sqlSession.rollback();
System.out.println(e.getMessage());
throw e;
} finally {
MybatisUtil.closeSqlSesson();
}
return studentList;
}
查询数据sql 语句：
select * from students where s_id = ? and s_name = ? and s_height = ?
对应StudentMapper.xml 配置
<select id="findDynamic" parameterType="map" resultType="com.test.mybatis.Student">
select * from students
<where>
<if test="id!=null">
and s_id=#{id}
</if>
<if test="name!=null">
and s_name=#{name}
</if>
<if test="height!=null">
and s_height=#{height}
</if>
</where>
</select>
4.动态更新（根据id确定记录后，根据其他字段是否为空进行自动拼接sql语句，实现指定字段更新）
/**
* 动态更新
*/
@Override
public void updateDynamic(Integer id, String name, Float height) {
SqlSession sqlSession = null;
try{
sqlSession = MybatisUtil.getSqlSesson();
Map<String, Object> map = new LinkedHashMap<>();
map.put("id", id);
map.put("name", name);
map.put("height", height);
sqlSession.update(Student.class.getName()+".updateDynamic",map);
sqlSession.commit();
} catch (Exception e) {
sqlSession.rollback();
System.out.println(e.getMessage());
throw e;
} finally {
MybatisUtil.closeSqlSesson();
}
}
更新数据sql 语句：
update students set s_name = ? , s_height = ? where s_id = ?
对应StudentMapper.xml 配置
<update id="updateDynamic" parameterType="map">
update students
<set>
<if test="name!=null">
s_name=#{name},
</if>
<if test="height!=null">
s_height=#{height},
</if>
</set>
where s_id=#{id}
</update>
网友评论