mybatis学习二

于 2020-07-16 21:33:23 发布
阅读量127 收藏
点赞数
分类专栏： 框架 mybati 文章标签： java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/boyi_520/article/details/107394381
版权

mybatis02

1mybatis事务

mybatis事务的管理也是通过setAutoCommit() 进行控制，

默认为手动提交

  @Override
  public SqlSession openSession() {
    return openSessionFromDataSource(configuration.getDefaultExecutorType(), null, false);
  }

在手动提交方式遇到增删改时，需要调用session.commit();

自动提交

   SqlSession sqlSession = sqlSessionFactory.openSession(true);

2Mybatis 的动态 SQL 语句

动态sql语句

1.IStudentDao接口中增加

     List<Student> findStudentByCondition(Student student);

2.在IStudentDao.xml增加

    <select id="findStudentByCondition" resultType="student" parameterType="student">
        select * from student_tb where 1=1

            <if test="name != null">
                and name like #{name}
            </if>

            <if test="sex != null">
                and sex like #{sex}
            </if>

    </select>

当if条件符合要求是才会拼接sql语句，注意where 1=1 是为了解决两个条件都符合时sql的where条件

3.测试


    private static void findStudentByCondition(IStudentDao studentDao) {

        Student student = new Student();
        student.setName("%ang%");
        student.setSex("%M%");

        List<Student> studentList  = studentDao.findStudentByCondition(student);
        for (Student studentTemp:studentList){
            System.out.println(studentTemp);
        }
    }

动态sql语句

可以替代sql语句中的where

沿用上一个例子，将配置文件改为一下，避免where 1=1

 <select id="findStudentByCondition" resultType="student" parameterType="student">
        select * from student_tb
        <where>
            <if test="name != null">
                and name like #{name}
            </if>
            <if test="sex != null">
                and sex like #{sex}
            </if>

        </where>

动态语句

标签是为解决mybatis接受集合参数设置的。适用场景如下

select * from student  where id in（43，44，58，59）；

1.IStudentDao接口中增加

    List<Student> findStudentByIds(QuestData questData);

将请求的参数集合封装为对象

public class QuestData {

    List<Integer> ids;

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }
}

2.在IStudentDao.xml增加

    <select id="findStudentByIds" resultType="student"  parameterType="QuestData">
         select * from student_tb
         <where>
            <if test="ids!=null and ids.size()>0">
                
                <foreach collection="ids" open="and id in(" close=")" item="uid" separator=",">
                        #{uid}
                </foreach>

            </if>
         </where>
    </select>
  • collection：为接受ids的变量名
  • open：表示以什么字段开始
  • close：表示以什么字段结束
  • item：为遍历集合中的元素
  • separator：为切分集合到的标记

3.测试

    private static void findStudentByIds(IStudentDao studentDao) {

        QuestData questData = new QuestData();
        List<Integer> list = new ArrayList<Integer>();
        list.add(32);
        list.add(34);
        list.add(35);
        questData.setIds(list);

        List<Student> studentList  =  studentDao.findStudentByIds(questData);
        for (Student studentTemp:studentList){
            System.out.println(studentTemp);
        }

    }

动态语句

在xml配置我们可以通过完成语句的声明，通过 标签进行应用拼接

        <sql id="findAllStudentsql">
            select * from student_tb
        </sql>

        <select id="findAllStudent" resultType="com.wgz.entity.Student">
            <include refid="findAllStudentsql"></include> where id >40
        </select>

3.mybatis多表查询

一对一查询

通过两张表关联查出所需要的信息

学生表与分数表之间使用学生id进行关联

查询方式一

将两张表的字段封装到一个实体内

1IStudentScoreDao接口中增加

 List<StudentScore>  findAllScore();

实体类

public class Score {

    private int scoreid;

    private String coursename;

    private int score;

    private int studentid;

    private Student student;

    public Student getStudent() {
        return student;
    }

    public void setStudent(Student student) {
        this.student = student;
    }

    public int getScoreid() {
        return scoreid;
    }

    public void setScoreid(int scoreid) {
        this.scoreid = scoreid;
    }

    public String getCoursename() {
        return coursename;
    }

    public void setCoursename(String coursename) {
        this.coursename = coursename;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    public int getStudentid() {
        return studentid;
    }

    public void setStudentid(int studentid) {
        this.studentid = studentid;
    }

    @Override
    public String toString() {
        return "Score{" +
                "scoreid=" + scoreid +
                ", coursename='" + coursename + '\'' +
                ", score=" + score +
                ", studentid=" + studentid +
                ", student=" + student +
                '}';
    }
}


public class StudentScore extends Score {

    private String studentName;

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }


    @Override
    public String toString() {
        return super.toString()+ "StudentScore{" +
                "studentName='" + studentName + '\'' +
                '}';
    }
}

2.在IStudentScoreDao.xml增加


    <!-- 定义封装StudentScore和user的resultMap -->
    <resultMap id="studentScoreMap" type="StudentScore">

        <id property="scoreid" column="scoreid"></id>
        <result property="coursename" column="coursename"></result>
        <result property="score" column="score"></result>
        <result property="studentName" column="name"></result>

    </resultMap>

    <!-- 查询所有 -->
    <select id="findAllScore" resultMap="studentScoreMap">
        select a.scoreid,a.coursename,a.score,b.name  from score_tb a , student_tb b where a.studentid = b.id;
    </select>

3.测试


			 IStudentScoreDao studentScoreDao = sqlSession.getMapper(IStudentScoreDao.class);
           List<StudentScore> studentScoreList =  studentScoreDao.findAllScore();
            for (StudentScore studentScore:studentScoreList){
                System.out.println("studentScore:"+studentScore);
            }

查询方式二

在Score实体类增加Student的属性

1IStudentScoreDao接口中增加


public class Score {

    private int scoreid;

    private String coursename;

    private int score;

    private int studentid;

    private Student student;

    public Student getStudent() {
        return student;
    }

    public void setStudent(Student student) {
        this.student = student;
    }

    public int getScoreid() {
        return scoreid;
    }

    public void setScoreid(int scoreid) {
        this.scoreid = scoreid;
    }

    public String getCoursename() {
        return coursename;
    }

    public void setCoursename(String coursename) {
        this.coursename = coursename;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    public int getStudentid() {
        return studentid;
    }

    public void setStudentid(int studentid) {
        this.studentid = studentid;
    }

    @Override
    public String toString() {
        return "Score{" +
                "scoreid=" + scoreid +
                ", coursename='" + coursename + '\'' +
                ", score=" + score +
                ", studentid=" + studentid +
                ", student=" + student +
                '}';
    }
}

2.在IStudentScoreDao.xml增加

   <!-- 定义封装StudentScore和user的resultMap -->
    <resultMap id="ScoreWithStudentMap" type="Score">
        <id property="scoreid" column="scoreid"></id>
        <result property="coursename" column="coursename"></result>
        <result property="score" column="score"></result>

        <!-- 一对一的关系映射：配置封装Student的内容-->
        <association property="student" column="studentid" javaType="Student">
            <id property="id" column="studentid"></id>
            <result  property="name" column="name"></result>

        </association>
    </resultMap>

    <select id="findAllScoreWithStudent" resultMap="ScoreWithStudentMap">
            select a.scoreid,a.coursename,a.score,a.studentid,b.name  from score_tb a , student_tb b where a.studentid = b.id;
    </select>
  • association：为多表一对一的的应映射关系
  • javaType：为所用属性的java类型

3.测试


            IStudentScoreDao studentScoreDao = sqlSession.getMapper(IStudentScoreDao.class);
            List<Score> scoreList =  studentScoreDao.findAllScoreWithStudent();
            for (Score score:scoreList){
                System.out.println("score:"+score);
            }

一对多查询

通过学生表查询出学生的所有成绩

1.IStudentDao接口中增加

     List<Student>  findAllStudentWithScoreList();

实体类Student

/**
 * 学生实体类 与 student_tb一一对应
 */
public class Student implements Serializable {

    private int id;

    private String name;

    private String sex;

    private int age;

    private float height;

    private Date birthday;

    private List<Score> scoreList;


    public List<Score> getScoreList() {
        return scoreList;
    }

    public void setScoreList(List<Score> scoreList) {
        this.scoreList = scoreList;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public float getHeight() {
        return height;
    }

    public void setHeight(float height) {
        this.height = height;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", height=" + height +
                ", birthday=" + birthday +
                ", scoreList=" + scoreList +
                '}';
    }
}

2.在IStudentDao.xml增加

    <resultMap id="findAllStudentWithScoreListMap" type="Student">
        <id property="id" column="id"></id>
        <result property="sex" column="name"></result>
        <result property="age" column="age"></result>
        <result property="height" column="height"></result>
        <result property="birthday" column="birthday"></result>

        <!--
        collection 用于指定 学生的所有成绩 一对多
        property 指向学生类中的成绩变量名
        ofType  指向学生类中的成绩的类型
        -->
        <collection property="scoreList" ofType="Score">
            <id property="scoreid" column="scoreid"></id>
            <result property="coursename" column="coursename"></result>
            <result property="score" column="score"></result>
        </collection>

    </resultMap>


    <select id="findAllStudentWithScoreList" resultMap="findAllStudentWithScoreListMap">
         select a.*,b.scoreid,b.coursename,b.coursename  from  student_tb a left  join score_tb b  on a.id = b.studentid ;
    </select>
  • collection ：用于指定 学生的所有成绩 一对多
    property ：指向学生类中的成绩变量名
    ofType ：指向学生类中的成绩的类型

3.测试


            //  4.使用sqlSession 创建代理对象

            // 方式一 通过代理：
            IStudentDao studentScoreDao = sqlSession.getMapper(IStudentDao.class);


            List<Student> studentList =  studentScoreDao.findAllStudentWithScoreList();
            for (Student student:studentList){
                System.out.println("student:"+student);
            }

多对多查询

以学生表，与学生角色表为例，一个学生可以有多个角色，一个角色可以有多个学生，两张表通过学生-角色中间表进行关联

本质：两张表的多对多只是 ，一对的叠加

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NfgiKQyI-1594906275261)(mybatis02.assets/1588821070567.png)]

查询一个角色下的所有学生

1.IRoleDao接口中增加


public interface IRoleDao {


    List<Role> findAllRole();


}

2.在IRoleDao.xml增加


    <resultMap id="findAllRoleMap" type="Role">

        <id property="id" column="roleid"/>
        <result property="roleName" column="roleName"/>

        <collection property="studentList" ofType="Student">
            <id property="id" column="id"></id>
            <result property="name" column="name"></result>
            <result property="sex" column="sex"></result>
            <result property="age" column="age"></result>
            <result property="height" column="height"></result>
            <result property="birthday" column="birthday"></result>
        </collection>

    </resultMap>


    <select id="findAllRole" resultMap="findAllRoleMap">
         select r.id as roleid ,r.roldename roleName, s.*  from  role_tb r left  join student_role_tb rs  on r.id = rs.roleid left join student_tb s on rs.studentid = s.id ;
    </select>

3.测试

  //  4.使用sqlSession 创建代理对象

            // 方式一 通过代理：
            IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);


            List<Role> roleList =  roleDao.findAllRole();
            for (Role role:roleList){
                System.out.println("role:"+role);
            }
查询一个学生下的所有角色

hday">

</resultMap>


<select id="findAllRole" resultMap="findAllRoleMap">
     select r.id as roleid ,r.roldename roleName, s.*  from  role_tb r left  join student_role_tb rs  on r.id = rs.roleid left join student_tb s on rs.studentid = s.id ;
</select>


3.测试

// 4.使用sqlSession 创建代理对象

        // 方式一 通过代理：
        IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);


        List<Role> roleList =  roleDao.findAllRole();
        for (Role role:roleList){
            System.out.println("role:"+role);
        }


#### 查询一个学生下的所有角色

和以上案例一样
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值