【Java】MyBatis应用(三)Mapper.mxl

正文索引 [隐藏]

Mapper.xml

  • statement标签:select、update、delete、insert分别对应查询、修改、删除、添加操作
  • parameType:参数数据类型
      基本数据类型,通过ID来查询Account
    <select id="findById" parameterType="long" resultType="com.mybatistest.entity.Account">
    select * from t_accout where id = #{id}
    </select>
      常用String类型,通过name来查询Account
    <select id="findByName" parameterType="String" resultType="com.mybatistest.entity.Account">
    select * from t_accout where username = #{username}
    </select>
      包装类,通过ID来查询Account
    <select id="findById2" resultType="com.mybatistest.entity.Account" parameterType="long">
    select * from t_accout where id = #{id}
    </select>
      多个参数,通过name和age来查询Account
    <select id="findByNameAndAge" resultType="com.mybatistest.entity.Account">
    select * from t_accout where username = #{param1} and age = #{param2}
    </select>
      JavaBean作为参数

    <update id="update" parameterType="com.mybatistest.entity.Account">
    update t_accout set username = #{username},password = #{password},age = #{age} where id = #{id}
    </update>
  • resultType:结果类型
      基本数据类型,统计Account总数
    <select id="count" resultType="int">
    select count(*) from t_accout
    </select>
      包装类,统计Account总数
    <select id="count2" resultType="java.lang.Integer">
    select count(*) from t_accout
    </select>
      String类型,通过ID来查询Account的name
    <select id="findNameById" resultType="java.lang.System">
    select username from t_accout where id = #{id}
    </select>
      JavaBean类型

    <select id="findById" parameterType="long" resultType="com.mybatistest.entity.Account">
    select * from t_accout where id = #{id}
    </select>

及联查询

  • 一对多
  • Student类

    package com.mybatistest.entity;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import java.awt.*;
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class Student {
    private long id;
    private String name;
    private Classes classes;
    }

    Classes类
    package com.mybatistest.entity;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import org.apache.ibatis.annotations.Update;
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class Classes {
    private long id;
    private String name;
    }

    StudentRepository
    package com.mybatistest.repository;
    import com.mybatistest.entity.Student;
    public interface StudentRepository {
    public Student findByID(long id);
    }

    StudentRepository.xml
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.mybatistest.repository.StudentRepository">
    <resultMap id="studentMap" type="com.mybatistest.entity.Student">
    <id column="id" property="id"></id>
    <result column="name" property="name"></result>
    <association property="classes" javaType="com.mybatistest.entity.Classes">
    <id column="cid" property="id"></id>
    <result column="cname" property="name"></result>
    </association>
    </resultMap>
    <select id="findByID" parameterType="long" resultMap="studentMap">
    SELECT student.id,student.name,classes.id as cid,classes.name AS cname FROM student,classes WHERE student.id = #{id} and student.cid = classes.id
    </select>
    </mapper>

    ClassesRepository
    package com.mybatistest.repository;
    import com.mybatistest.entity.Classes;
    public interface ClassesRepository {
    public Classes findById(long id);
    }

    ClassesRepository.xml
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.mybatistest.repository.ClassesRepository">
    <resultMap id="classestMap" type="com.mybatistest.entity.Classes">
    <id column="cid" property="id"></id>
    <result column="cname" property="name"></result>
    <collection property="students" ofType="com.mybatistest.entity.Student">
    <id column="id" property="id"></id>
    <result column="name" property="name"></result>
    </collection>
    </resultMap>
    <select id="findById" parameterType="long" resultMap="classestMap">
    SELECT student.id,student.name,classes.id as cid,classes.name AS cname FROM student,classes WHERE classes.id = #{id} and student.cid = classes.id
    </select>
    </mapper>
  • 多对多
  • Customer类

    package com.mybatistest.entity;
    import lombok.Data;
    import java.util.List;
    @Data
    public class Customer {
    private long id;
    private String name;
    private List<Goods>goods;
    }

    Goods类
    package com.mybatistest.entity;
    import lombok.Data;
    import java.util.List;
    @Data
    public class Goods {
    private long id;
    private String name;
    private List<Customer> customers;
    }

    CustomerRepository
    package com.mybatistest.repository;
    import com.mybatistest.entity.Customer;
    public interface CustomerRepository {
    public Customer findById(long id);
    }

    GoodsRepository
    package com.mybatistest.repository;
    import com.mybatistest.entity.Goods;
    public interface GoodsRepository {
    public Goods findById(long id);
    }

    CustomerRepository.xml
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.mybatistest.repository.CustomerRepository">
    <resultMap id="customertMap" type="com.mybatistest.entity.Customer">
    <id column="cid" property="id"></id>
    <result column="cname" property="name"></result>
    <collection property="goods" ofType="com.mybatistest.entity.Goods">
    <id column="gid" property="id"></id>
    <result column="gname" property="name"></result>
    </collection>
    </resultMap>
    <select id="findById" parameterType="long" resultMap="customertMap">
    SELECT c.id as cid,c.name as cname,g.id as gid,g.name as gname FROM customer c,goods g,customer_goods cg WHERE c.id = #{id} AND cg.cid = c.id AND cg.gid = g.id
    </select>
    </mapper>

    GoodsReposotory.xml
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.mybatistest.repository.GoodsRepository">
    <resultMap id="goodstMap" type="com.mybatistest.entity.Goods">
    <id column="gid" property="id"></id>
    <result column="gname" property="name"></result>
    <collection property="customers" ofType="com.mybatistest.entity.Customer">
    <id column="cid" property="id"></id>
    <result column="cname" property="name"></result>
    </collection>
    </resultMap>
    <select id="findById" parameterType="long" resultMap="goodstMap">
    SELECT g.id as gid,g.name as gname,c.id as cid,c.name as cname FROM customer c,goods g,customer_goods cg WHERE g.id = #{id} AND cg.cid = c.id AND cg.gid = g.id
    </select>
    </mapper>
blank