MyBatis中的动态SQL,实现不确定条件的CRUD-----使用Map来传递查询的参数

来源：互联网发布：学校校园铃声软件编辑：程序博客网时间：2024/05/21 10:17

1、MyBatis中的动态SQL,实现不确定条件的CRUD的实现

将查询条件封装到map里面,通过 if 和 where 标签的使用,实现sql的动态性

2、动态SQL，实现不确定条件的查询

2.1 不确定条件查询的案列

2.2 不确定条件查询的实现

2.2.1 mapper接口

public interface UserMapper {      // 动态查询用户      public List<User> findUserByIdDongTaiSql(Map<String, Object> map);  }

2.2.2 mapper.xml <where> 和 <if> 标签的用法

<!-- 利用动态sql的查询 -->  <select id="findUserByIdDongTaiSql" parameterType="map"      resultType="user">      select * from user      <where>          <if test="id!=null">              and id = #{id}          </if>          <if test="username!=null">              and username like '%${username}%'          </if>      </where>  </select>

2.2.3 测试代码

public class Mybatis_Four_Test {      // 会话工厂      private SqlSessionFactory sqlSessionFactory;        @Before      public void createSqlSessionFactory() throws IOException {          // 配置文件          String resource = "SqlMapConfig.xml";          InputStream inputStream = Resources.getResourceAsStream(resource);          // 使用SqlSessionFactoryBuilder从xml配置文件中创建SqlSessionFactory          sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);      }        // one list foreach      @Test      public void testfindAll() {          // 获取session          SqlSession session = sqlSessionFactory.openSession();          // 获取mapper接口的代理对象          UserMapper userMapper = session.getMapper(UserMapper.class);          HashMap<String, Object> map = new HashMap<String, Object>();          map.put("id", 16);          map.put("username", "小明");          List<User> users = userMapper.findUserByIdDongTaiSql(map);          for (int i = 0; i < users.size(); i++) {              System.out.println(users.get(i));          }          // 关闭session          session.close();      }  }

3、动态SQL，实现不确定条件的更新

3.1 不确定条件更新的案列

3.2 不确定条件更新的实现

3.2.1 mapper接口

// 动态更新用户  public void updateUserDongTaiSql(Map<String, Object> map);

3.2.2 mapper.xml

<!-- 利用动态sql的更新 -->  <update id="updateUserDongTaiSql" parameterType="map">       update user       <!--set标签，会自动判断哪个是最后一个字段，并去除逗号 -->       <set>         <if test="id!=null">           username = #{username},         </if>         <if test="username!=null">           address = #{address},        </if>      </set>       where id = #{id}  </update>

3.2.3 测试代码

 public void testupdateUserDongTaiSql() {  // 获取session  SqlSession session = sqlSessionFactory.openSession();  // 获取mapper接口的代理对象  UserMapper userMapper = session.getMapper(UserMapper.class);  HashMap<String, Object> map = new HashMap<String, Object>();  map.put("id", 29);  map.put("username", "张小三");  map.put("address", "浙江省");  userMapper.updateUserDongTaiSql(map);  session.commit();  // 关闭session  session.close();

4、动态SQL，实现不确定条件的删除

4.1 不确定条件删除的案列

4.2 不确定条件删除的实现

4.2.1 Array形式的实现

4.2.1.1 mapper接口

// 动态删除,Array的形式      public void deleteUserDongTaiSqlArray(int[] ids);

4.2.1.2 mapper.xml

<!--利用动态sql的删除 -->  <!--根据User的id,删除用户 delete from user where id in(1,2,3) -->  <!--Array的形式 -->  <delete id="deleteUserDongTaiSqlArray" parameterType="map">      delete from user where id in      <foreach collection="array" open="(" close=")" separator=","          item="id">          #{id}      </foreach>  </delete>

4.2.1.3 测试代码

// 数组的形式      @Test      public void testdeleteUserDongTaiSqlArray() {          // 获取session          SqlSession session = sqlSessionFactory.openSession();          // 获取mapper接口的代理对象          UserMapper userMapper = session.getMapper(UserMapper.class);          int ids[] = { 26, 29 };          session.commit();          userMapper.deleteUserDongTaiSqlArray(ids);          // 提交          session.commit();          // 关闭session          session.close();      }

4.2.2 AList形式的实现

4.2.2.1 mapper接口

// 动态删除用户,List的形式      public void deleteUserDongTaiSqlList(List<Integer> ids);

4.2.2.2 mapper.xml

<!--list的形式 -->      <delete id="deleteUserDongTaiSqlList" parameterType="java.util.List">          delete from user where id in          <foreach collection="list" open="(" close=")" separator=","              item="id">              #{id}          </foreach>      </delete>

4.2.2.3 测试代码

// list的形式  @Test  public void testdeleteUserDongTaiSqlList() {      // 获取session      SqlSession session = sqlSessionFactory.openSession();      // 获取mapper接口的代理对象      UserMapper userMapper = session.getMapper(UserMapper.class);      List<Integer> list = Arrays.asList(34, 35, 36);      userMapper.deleteUserDongTaiSqlList(list);      session.commit();      // 关闭session      session.close();  }

5、动态SQL，实现不确定条件的插入

5.1 不确定条件查询的案列

5.2 不确定条件查询的实现

5.2.1 mapper接口

// 动态插入用户  public void insertUserDongTaiSql(User user);

5.2.2 mapper.xml

<!-- sql片段，对应字段名称,id属性值任意 -->      <sql id="key">          <trim suffixOverrides=",">              <if test="username!=null">                  username,              </if>              <if test="sex!=null">                  sex,              </if>          </trim>      </sql>      <!-- sql片段,对用？,id属性值任意 -->      <sql id="value">          <trim suffixOverrides=",">              <if test="username!=null">                  #{username},              </if>              <if test="sex!=null">                  #{sex},              </if>          </trim>      </sql>      <!-- 动态sql的插入 -->      <insert id="insertUserDongTaiSql" parameterType="user">          insert into user(          <include refid="key" />          ) values(          <include refid="value" />          )      </insert>

5.2.3 测试代码

public void testinsertUserDongTaiSqlList() {          // 获取session          SqlSession session = sqlSessionFactory.openSession();          // 获取mapper接口的代理对象          UserMapper userMapper = session.getMapper(UserMapper.class);          User user = new User();          user.setAddress("广东釜山");          user.setSex("1");          user.setUsername("李小三");          userMapper.insertUserDongTaiSql(user);          session.commit();          // 关闭session          session.close();      }

1 0