1)查询条件不确定,需要根据情况产生SQL语法,这种情况叫动态SQL
一 .动态查询
StudentDao.java:
package bean4;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import util.MybatisUtil;
/**
* 持久层
* @author user
*
*/
public class StudentDao {
/**
* 分页查询
* @param begin 表示在mysql中表示从第几条记录的索引号开始 索引从0开始
* @param length 表示在mysql中最多显示 几条记录
* @return
*/
public List<Student> queryAllStudent(Integer id,String name,Double sal){
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
Map<String, Object> map = new LinkedHashMap<>();
map.put("pid", id);
map.put("pname", name);
map.put("psal", sal);
return sqlSession.selectList("studentNameSpace.queryAllStudent", map);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
MybatisUtil.closeSqlsession();
}
return null;
}
public static void main(String[] args) {
StudentDao dao = new StudentDao();
List<Student> list = dao.queryAllStudent(5, null, null);
for (Student student : list) {
System.out.println(student.getId()+" "+student.getName()+" "+student.getSal());
}
}
}
StudentMapper.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">
<!-- 动态sql查询 -->
<!-- namespace属性是名称空间 必须唯一s\ -->
<mapper namespace="studentNameSpace">
<!-- 当实体属性和表字段名不相同的时候, 必须书写以下代码
-->
<resultMap type="bean4.Student" id="studentMap">
<id property="id" column="id"/>
<result property="name" column="name" />
<result property="sal" column="sal" />
</resultMap>
<select id="queryAllStudent" parameterType="map" resultMap="studentMap">
select id,name,sal from student
<where>
<if test="pid != null">
and id = #{pid}
</if>
<if test="pname != null">
and name = #{pname}
</if>
<if test="psal != null">
and sql = #{psal}
</if>
</where>
</select>
</mapper>
二.动态更新
StudentDao.java
/**
* 有条件更新学生
* @param id
* @param name
* @param sal
*/
public void update(int id,String name,Double sal){
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
Map<String, Object> map = new LinkedHashMap<String, Object>();
map.put("pid", id);
map.put("pname", name);
map.put("psal", sal);
sqlSession.update("studentNamespace.update", map);
sqlSession.commit();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
sqlSession.rollback();
}finally {
MybatisUtil.closeSqlsession();
}
}
StudentMapper.xml
<!-- set标签自动判断那个是最后一个字段,会自动去掉最后一个, -->
<update id="update" parameterType="map">
update student
<set>
<if test="pname != null">
name = #{pname},
</if>
<if test="psal != null">
sal = #{psal},
</if>
</set>
where id = #{pid}
</update>
三 动态删除
StudentDao.java
/**
* 有条件删除学生(数组版本)
* @param id
* @param name
* @param sal
*/
public void deleteArray(int[] ids){
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
sqlSession.delete("studentNamespace.deleteArray", ids);
sqlSession.commit();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
sqlSession.rollback();
}finally {
MybatisUtil.closeSqlsession();
}
}
/**
* 有条件删除学生(集合版本)
* @param ids
*/
public void deleteList(List<Integer> list){
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
sqlSession.delete("studentNamespace.deleteList", list);
sqlSession.commit();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
sqlSession.rollback();
}finally {
MybatisUtil.closeSqlsession();
}
}
StudentMapper.xml
<!-- 数组版本 -->
<delete id="deleteArray" >
delete from student where id in
<!-- foreach 用于迭代数组 open 表示开始符号 close 表示结束符号 separator 表示元素间的分隔符 item 表示迭代的数组 -->
<foreach collection="array" open="(" close=")" separator="," item="ids"> <!-- item的值不必和传过来的值一致,但提倡一致 -->
#{ids}
</foreach>
</delete>
<!-- 集合版本 -->
<delete id="deleteList" >
delete from student where id in
<!-- foreach 用于迭代数组 open 表示开始符号 close 表示结束符号 separator 表示元素间的分隔符 item 表示迭代的数组 -->
<foreach collection="list" open="(" close=")" separator="," item="list"> <!-- item的值不必和传过来的值一致,但提倡一致 -->
#{list}
</foreach>
</delete>
四 动态插入
StudentDao.java
public void inseart(Student student){
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
sqlSession.insert("studentNamespace.insert", student);
sqlSession.commit();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
sqlSession.rollback();
}finally {
MybatisUtil.closeSqlsession();
}
}
StudentMapper.xml
<sql id="key">
<!-- 去掉最后一个, -->
<trim suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="name != null">
name,
</if>
<if test="sal != null">
sal,
</if>
</trim>
</sql>
<!-- sql片段 对应 ?-->
<sql id="value">
<!-- 去掉最后一个, -->
<trim suffixOverrides=",">
<if test="id != null">
#{id},
</if>
<if test="name != null">
#{name},
</if>
<if test="sal != null">
#{sal},
</if>
</trim>
</sql>
<insert id="insert" parameterType="bean7.Student">
<!-- <include refid="key"></include> 表示引用上面定义的sql片段的id -->
insert into student(<include refid="key"></include>) values(<include refid="value"></include>)
</insert>
总结动态sql在项目中经常遇到,其中查询、删除、更新操作没有太大难度,插入操作需要去除最后一个,这是一个难点