姚姚的小站

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>