MyBatis(4)

最新推荐文章于 2024-07-24 17:27:32 发布

山ii

最新推荐文章于 2024-07-24 17:27:32 发布

阅读量212

点赞数

分类专栏： Java基础文章标签： java

本文链接：https://blog.csdn.net/weixin_52821030/article/details/123166420

版权

Java基础专栏收录该内容

50 篇文章 1 订阅

订阅专栏

1.MyBatis中引入数据值时使用的${}和#{}

${}--MyBatis的核心配置文件，配置<dataSource>
例如:

    mydata.properties
    myname=root

    <properties resource="mydata.properties"></properties>

    <dataSource>
        <property name="username" value="${myname}"></property>
    </dataSource>

#{}--SQL映射文件中，给sql语句补充具体数据值
例如

<insert id="insertStudent" pra....>
		insert  into  t_student  value(null,#{stuname},#{stuage});
	</insert>

实际上在SQL映射文件中除过可以使用#{}，也可以使用${}

Mybatis-config.xml 设置sql语句在控制台输出

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

public interface StudentMapper {
    //演示${}
    List<StudentBean> selectLike1(HashMap<String,String> map);
    //演示#{}
    List<StudentBean> selectLike2(String stuname);
}

<!-- 演示${}-->
<select id="selectLike1" parameterType="java.util.HashMap" resultMap="stuMap">
  select * from t_student where stu_name like ${stuname};
</select>

HashMap<String,String> map=new  HashMap<String,String>();
map.put("stuname","'%zhang%'");
List<StudentBean> studentBeanList=studentMapper.selectLike1(map);

"'%zhang%'"
select * from t_student where stu_name like '%zhang%';

<!--演示#{}-->
<select id="selectLike2" parameterType="java.lang.String" resultMap="stuMap">
  select * from t_student where stu_name like #{stuname};
</select>

测试selectLike2---演示#{}
StudentMapper studentMapper=(StudentMapper)sqlSession.getMapper(StudentMapper.class);
String stuname="zhang";
List<StudentBean> studentBeanList=studentMapper.selectLike2(stuname);

"zhang"
select * from t_student where stu_name like ?

总结：

${}---'%zhang%'---jdbc中的Stament执行sql语句的处理方式---【利用拼接字符串的方式】
${}实现sql语句的拼接操作，不做数据类型转换，需要自行判断数据类型，不能防止sql注入。
#{}---？--- jdbc中的PreperedStament执行sql语句的处理方式---【预处理，用占位符的方式】
#{}实现的是sql语句的预处理，之后执行的sql中用？号代替。使用时不需要关注参数的数据类型。mybatis会自动实现数据类型转换，并且可以防止sql注入。

2.MyBatis中的动态SQL

动态SQL--拼接sql语句--通过提供标签自动拼接
如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。
String 拼接

String sql="insert  into t_student value(null,'"+
	stu.getStuname()+"',"+stu.getStuage()+",'"+stu.getStuaddress()+"')";

拼接的时候要确保不能忘了必要的空格,"'"，还要注意省掉列名列表最后的逗号。

为了避免在拼接SQL 语句的时候出现忘记空格,"'"，“,”这种情况，所以MyBatis提供了动态SQL元素。

常见的动态SQL

foreach动态SQL元素

格式：

每一个具体的数据对象
</foreach>
foreach元素，实现批量添加

    //演示批量添加
    boolean insertMore(List<StudentBean> studentBeanList);

  <!-- foreach批量添加 -->
    <insert id="insertMore" parameterType="java.util.List">
        insert into t_student value
    <foreach collection="list" item="stu" separator=",">
        (null,#{stu.stuname},#{stu.stupass},#{stu.stuage},#{stu.stuaddress})
    </foreach>
    </insert>

public static void testInsertMore(){

        SqlSession sqlSession=null;
        try{
            SqlSessionFactory sqlSessionFactory= new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
            sqlSession=sqlSessionFactory.openSession();
            List<StudentBean> studentBeanList=new ArrayList<StudentBean>();
            for(int i=1;i<=5;i++){
                StudentBean studentBean=new StudentBean();
                studentBean.setStuname("初号机"+i);
                studentBean.setStupass("0"+i);
                studentBean.setStuage(24+i);
                studentBean.setStuaddress("新东京"+i);
                studentBeanList.add(studentBean);
            }
            StudentMapper studentMapper=(StudentMapper) sqlSession.getMapper(StudentMapper.class);
            studentMapper.insertMore(studentBeanList);
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }

    }

insert into t_student value (null,?,?,?) , (null,?,?,?) , (null,?,?,?) , (null,?,?,?) , (null,?,?,?)

  //演示批量删除
    boolean deleteMore(int id[]);


    <!-- foreach批量删除 -->
    <delete id="deleteMore" parameterType="java.util.List">
    delete  from t_student where st_uid in
    <foreach collection="array" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
    </delete>


    public static void testDeleteMore(){

        SqlSession sqlSession=null;
        try{
            SqlSessionFactory sqlSessionFactory= new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
            sqlSession=sqlSessionFactory.openSession();
            int id[]={9,10,11,12,13};
            StudentMapper studentMapper=(StudentMapper) sqlSession.getMapper(StudentMapper.class);
            studentMapper.deleteMore(id);
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }

    }

delete from t_student where stu_id in ( ? , ? , ? )

if动态SQL元素

例如:判断是否有用户名，如果有就根据用户名模糊查询，如果没有就查询所有

    //if动态SQL元素
    //判断是否由用户名，如果由就根据用户名和模糊查询，如果没有就查询所有
    List<StudentBean>  selectIf(HashMap<String,Object> map);

 <select id="selectIf" parameterType="java.util.HashMap" resultMap="stuMap">
        select * from t_student where 1=1
        <if test="name!=null and name!=''">
            and stu_name like #{name}
        </if>
    </select>


    public static void testSelectIf(){
        SqlSession sqlSession=null;
        try{

            SqlSessionFactory  sqlSessionFactory=  new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
            sqlSession=sqlSessionFactory.openSession();
           StudentMapper studentMapper= sqlSession.getMapper(StudentMapper.class);
           HashMap<String,Object> map=new HashMap<String,Object>();
           map.put("name","");
            List<StudentBean> list=studentMapper.selectIf(map);
            for (StudentBean studentBean:list){
                System.out.println(studentBean.getStuname());
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
        }

测试choose (when, otherwise)

相当于java中switch结构
switch（）{
   case xxx: xxxxxx ; break
   case xxx: xxxxxx ; break
   case xxx: xxxxxx ; break
default :xxxxx ;break
}

需求：

查询用户信息，如果输入了用户名，根据用户名进行模糊查找，返回
如果输入了年龄，根据年龄进行匹配查找，返回
如果输入了地址，根据地址进行模糊查找，返回
如果查询条件都为空，那么就查询所有。
【每次只匹配一个条件】

   //choose(when,otherwise),相当于java种switch结构
    List<StudentBean> selectChoose(HashMap<String,Object> map);

  <!-- 演示choose(when,otherwise)动态sql元素 -->
    <select id="selectChoose" parameterType="java.util.HashMap" resultMap="stuMap">
        select * from t_student where 1=1
        <choose>
                <when test="name!=null and name!=''">
                    and stu_name like #{name}
                </when>
            <when test="age!=null and age!=''">
                and stu_age like #{age}
            </when>
            <when test="address!=null and address!=''">
                and stu_address like #{address}
            </when>
        </choose>
    </select>

      public static void selectChoose(){
         SqlSession sqlSession=null;
         try{
          SqlSessionFactory sqlSessionFactory= new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
          sqlSession=sqlSessionFactory.openSession();
         StudentMapper studentMapper= sqlSession.getMapper(StudentMapper.class);
         HashMap<String,Object> map=new HashMap<String,Object>();
             map.put("name","");
             map.put("age",22);
             map.put("address","西安");
            List<StudentBean> studenlist= studentMapper.selectChoose(map);
            for (StudentBean studentbean: studenlist){
                System.out.println(studentbean.getStuname());
            }

         }catch(Exception e){
           e.printStackTrace();
         }finally{
             sqlSession.close();
         }
        }

测试where元素


    //where动态sql元素
    List<StudentBean> selectWhere(HashMap<String,Object> map);

 <select id="selectWhere" parameterType="java.util.HashMap" resultMap="stuMap">
        select * from  t_student
        <where>
            <if test="name!=null and name!=''">
                and stu_name like #{name}
            </if>
        </where>
    </select>

     public static void selectWhere (){
            SqlSession sqlSession=null;
            try{
                SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
                sqlSession=sqlSessionFactory.openSession();
                StudentMapper studentMapper=(StudentMapper) sqlSession.getMapper(StudentMapper.class);
                HashMap<String ,Object> map=new HashMap<String,Object>();
                map.put("name","");
                studentMapper.selectWhere(map);
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                sqlSession.close();
            }
        }

测试set元素

   //set动态sql元素
    boolean updateSet(HashMap<String,Object> map);

    <!-- 演示set动态sql元素-->
    <update id="updateSet" parameterType="java.util.HashMap">
        update t_student
        <set>
            <if test="name!=null and name!=''">
                stu_name=#{name},
            </if>
            <if test="age !=null and age !=0">
                stu_age=#{age},
            </if>
            <if test="address !=null and address !=''">
                stu_address=#{address},
            </if>
            <if test="pass !=null and pass !=''">
                stu_pass=#{pass},
            </if>

        </set>
            <where> st_uid=#{stuid}</where>
    </update>

 public static void testUpdateSet (){
        SqlSession sqlSession=null;
        try{
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
            sqlSession=sqlSessionFactory.openSession();
            StudentMapper studentMapper=(StudentMapper) sqlSession.getMapper(StudentMapper.class);
            HashMap<String ,Object> map=new HashMap<String,Object>();
            map.put("stuid",1);
            map.put("name","lisisi");
            map.put("pass","253125");
            map.put("age","133");
            map.put("address","北京");
            studentMapper.updateSet(map);
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }

update t_student SET stu_name=?, stu_age = ?, stu_address = ? WHERE stu_id = ?

山ii

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis(4)

1.MyBatis中引入数据值时使用的${}和#{}${}--MyBatis的核心配置文件，配置<dataSource>例如: mydata.properties myname=root <properties resource="mydata.properties"></properties> <dataSource> <property name="username" ...
复制链接

扫一扫

专栏目录