mybaits 动态SQL语句

最新推荐文章于 2025-09-12 15:58:40 发布

转载最新推荐文章于 2025-09-12 15:58:40 发布 · 62 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/chenliyong/blog/673603

文章标签：

#python #java

本文详细介绍了使用动态SQL进行数据库操作的方法，包括查询、更新、删除和插入数据。通过配置参数，动态SQL允许在不修改代码的情况下实现对数据库的灵活操作。

2019独角兽企业重金招聘Python工程师标准>>>

-------------------------动态查询---------------------------------------
<select id="findAll" parameterType="map" resultMap="studentMap">
  select * from students
  <where>
   <if test="pid!=null">
    and students_id = #{pid}
   </if>
   <if test="pname!=null">
    and students_name = #{pname}
   </if>
   <if test="psal!=null">
    and students_sal = #{psal}
   </if>
  </where>
</select>

public List<Student> findAll(Integer id,String name,Double sal) throws Exception{
  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);

   return sqlSession.selectList("studentNamespace.findAll",map);
  }catch(Exception e){
   e.printStackTrace();
   throw e;
  }finally{
   MybatisUtil.closeSqlSession();
  }
}
-----------------------------------动态更新-----------------------------------------
<update id="dynaUpdate" parameterType="map">
  update students
  <set>
   <if test="pname!=null">
    students_name = #{pname},
   </if>
   <if test="psal!=null">
    students_sal = #{psal},
   </if>
  </set>
  where students_id = #{pid}
</update>

public void dynaUpdate(Integer id,String name,Double sal) throws Exception{
  SqlSession sqlSession = null;
  try{
   sqlSession = MybatisUtil.getSqlSession();

   Map<String,Object> map = new HashMap<String, Object>();
   map.put("pid",id);
   map.put("pname",name);
   map.put("psal",sal);
   sqlSession.update("studentNamespace.dynaUpdate",map);
   sqlSession.commit();
  }catch(Exception e){
   e.printStackTrace();
   sqlSession.rollback();
   throw e;
  }finally{
   MybatisUtil.closeSqlSession();
  }
}
-----------------------------------动态删除----------------------------------------------
<delete id="dynaDeleteArray">
  delete from students where students_id in
  
  <foreach collection="array" open="(" close=")" separator="," item="ids">
   #{ids}
  </foreach>
</delete>

/**
* 根据ID批量删除学生(数组版本)
*/
public void dynaDeleteArray(int... ids) throws Exception{
  SqlSession sqlSession = null;
  try{
   sqlSession = MybatisUtil.getSqlSession();
   sqlSession.delete("studentNamespace.dynaDeleteArray",ids);
   sqlSession.commit();
  }catch(Exception e){
   e.printStackTrace();
   sqlSession.rollback();
   throw e;
  }finally{
   MybatisUtil.closeSqlSession();
  }
}
/**
* 根据ID批量删除学生(集合版本)
*/
public void dynaDeleteList(List<Integer> ids) throws Exception{
  SqlSession sqlSession = null;
  try{
   sqlSession = MybatisUtil.getSqlSession();
   sqlSession.delete("studentNamespace.dynaDeleteList",ids);
   sqlSession.commit();
  }catch(Exception e){
   e.printStackTrace();
   sqlSession.rollback();
   throw e;
  }finally{
   MybatisUtil.closeSqlSession();
  }
}
------------------------------------动态插入----------------------------------------------

<sql id="key">
  
  <trim suffixOverrides=",">
   <if test="id!=null">
    students_id,
   </if>
   <if test="name!=null">
    students_name,
   </if>
   <if test="sal!=null">
    students_sal,
   </if>
  </trim>
</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="dynaInsert" parameterType="cn.itcast.javaee.mybatis.app14.Student">
  insert into students(<include refid="key"/>) values(<include refid="value"/>)
</insert>

public void dynaInsert(Student student) throws Exception{
  SqlSession sqlSession = null;
  try{
   sqlSession = MybatisUtil.getSqlSession();
   sqlSession.insert("studentNamespace.dynaInsert",student);
   sqlSession.commit();
  }catch(Exception e){
   e.printStackTrace();
   sqlSession.rollback();
   throw e;
  }finally{
   MybatisUtil.closeSqlSession();
  }
}