mybatis框架补充

最新推荐文章于 2025-11-27 10:55:11 发布

原创最新推荐文章于 2025-11-27 10:55:11 发布 · 641 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #java #数据库

一，#{} 和${}区别

1.传数值

#{} 占位符，是经过预编译的，编译好SQL语句再取值，#方式能够防止sql注入

eg：#{}：delete from admin where id = #{id}

结果： dalete from admin where id = ？

${}会将值以字符串形式拼接到sql语句，${}方式无法防止SQL注入

eg：sql注入演示

${}：delete from admin where id = '${id or 1}'

结果：这样使整张表的数据被删除了,所以相比于#{},#{}在对单独数据处理时更加安全

2.传列名

select * from admin order by ${column} desc

在 order by ${column} 语句后面可以动态替换列名

二，特殊处理定义resultMap

1.定义resultMap

<resultMap id="adminResultMap" type="Admin">
<id column="id" property="id"/>
<result property="acc" column="account" />
<result property="pwd" column="password" />
</resultMap>

（1）.resultMap的id属性时resultMap的唯一标识，本例中定义为"adminResultMap"

（2）.resultMap的type属性是映射的POJO类型，(指模型类中属性类型,首字母大写)

（3）id标签映射主键，result标签映射非主键

（4）property设置对象属性名称，column映射查询结果的列名称

2.使用resultMap

<select id="findAdminInfoResultMap" resultMap="adminResultMap">
SELECT id ,account,password FROM admin
</select>

（1）本例输出映射使用的是resultMap，而非resultType

（2）resultMap引用了adminResultMap

三，多表关联处理结果集

resultMap元素中association，collection元素

association适用于一对一关系

collection适用于一对多关系

eg1：

查找一个专业及其所包含学生人数（一对多使用Collection）

使用resultMap组装查询结果

<?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">
<mapper namespace="com.ffyc.mybatisdemo.dao.MajorDao">

<resultMap id="majormap" type="Major">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <collection property="students" javaType="arraylist" ofType="Student">
        <id column="sid" property="id"/>
        <result column="sname" property="name"/>
        <result column="snum" property="num"/>
    </collection>
</resultMap>


    <select id="findMajor" parameterType="int" resultMap="majormap">
        select
            m.id,m.name,s.id sid,s.name sname,s.num snum
        from major m inner join student s on m.id = s.majorid
        where m.id = #{id}
    </select>


</mapper>

package com.ffyc.mybatisdemo.dao;

import com.ffyc.mybatisdemo.model.Major;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;

public interface MajorDao {

    Major findMajor(int id);
    /*
        简单的sql，可以在抽象方法上使用注解标签写sql，复杂的还是推荐写道Mapper文件中
    */
    @Insert("insert into major(name)values (#{name})")
    int insertMajor(Major major);


}

package com.ffyc.mybatisdemo.test;

import com.ffyc.mybatisdemo.dao.MajorDao;
import com.ffyc.mybatisdemo.dao.StudentDao;
import com.ffyc.mybatisdemo.model.Major;
import com.ffyc.mybatisdemo.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.io.IOException;

public class TestMajor {

    public static void main(String[] args) throws IOException {

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        MajorDao majorDao = sqlSession.getMapper(MajorDao.class);

        Major major = majorDao.findMajor(1);
        System.out.println(major);

//        Major major = new Major();
//        major.setName("数学");
//
//        System.out.println(majorDao.insertMajor(major));
//

        sqlSession.close();
    }

}

eg2：通过学号查找对应学生的信息（一对一使用associate）

使用resultMap组装查询结果

<?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">
<mapper namespace="com.ffyc.mybatisdemo.dao.StudentDao">


    <resultMap id="studentmap" type="Student">
        <id property="id" column="id"/>
        <result property="num" column="num"/>
        <result property="gender" column="gender"/>
        <result property="name" column="name"/>
        <result property="phone" column="phone"/>
        <!--关联表数据映射   会自动创建关联对象-->
        <association property="major" javaType="Major">
            <id column="mid" property="id"/>
            <result column="mname" property="name"/>
        </association>
    </resultMap>

    <select id="findStudentById" parameterType="int" resultMap="studentmap">
        select
            s.id,s.num,s.gender,s.phone,m.id mid,m.name mname
        from student s inner join major m on s.majorid=m.id
        where s.id = #{id}
    </select>
    <select id="findStdents" resultMap="studentmap">
        select
            s.id,s.num,s.gender,s.phone,m.id mid,m.name mname
        from student s inner join major m on s.majorid=m.id
    </select>
</mapper>

package com.ffyc.mybatisdemo.dao;

import com.ffyc.mybatisdemo.model.Student;

import java.util.ArrayList;

public interface StudentDao {

    Student findStudentById(int id);

    ArrayList<Student> findStdents ();

}

package com.ffyc.mybatisdemo.test;

import com.ffyc.mybatisdemo.dao.StudentDao;
import com.ffyc.mybatisdemo.model.Student;
import com.ffyc.mybatisdemo.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.io.IOException;
import java.util.ArrayList;

public class TestStudent {

    public static void main(String[] args) throws IOException {

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);

        Student student = studentDao.findStudentById(3);

        System.out.println(student.getMajor().getName());

        ArrayList<Student> students = studentDao.findStdents();
        System.out.println(students);


        System.out.println(student);
        sqlSession.close();

    }

}

四.使用注解的方式实现mybatis交互数据库

1.常用注解方式

（1）@Insert ：插入sql , 和xml insert sql语法完全一样

（2）@Select ：查询sql, 和xml select sql语法完全一样

（3）@Update ：更新sql, 和xml update sql语法完全一样

（4）@Delete ：删除sql, 和xml delete sql语法完全一样

（5）@Results ：设置结果集合

（6）@Result ：设置结果

2.使用案例

查询所有信息：

@Select("select * from t_emp")
@Results(id = "empMap",value = {
@Result(column = "emp_id",property = "empId",id = true),
@Result(column = "emp_name",property = "empName"),
@Result(column = "emp_tel",property = "empTel"),
@Result(column = "emp_education",property = "empEducation"),
@Result(column = "emp_birthday",property = "empBirthday")
})
List<Employee> getAll();

查询单个信息：

@Select("select * from t_emp where emp_id=#{empId}")
Employee getById(@Param("empId") Integer empId);

插入信息：

@Insert("insert into t_emp (emp_id, emp_name, emp_tel, " +
" emp_education, emp_birthday, fk_dept_id" +" )" values (#{empId}, #{empName}, #
{empTel}, " +" #{empEducation}, #{empBirthday}, #{fkDeptId}" +" )")
int insert(Employee record);

删除信息：

@Delete("delete from t_emp where id = #{empId}")
void delete(@Param("empId") Integer empId);