1.resultMap标签

<!-- 配置 查询结果的列名和实体类的属性名的对应关系 -->
<resultMap id="userMap" type="uSeR">
    <!-- 主键字段的对应 -->
    <id property="userId" column="id"></id>
    <!--非主键字段的对应-->
    <result property="userName" column="username"></result>
    <result property="userAddress" column="address"></result>
    <result property="userSex" column="sex"></result>
    <result property="userBirthday" column="birthday"></result>
</resultMap>


<!-- 查询所有 -->
<select id="findAll" resultMap="userMap">
    <!--select id as userId,username as userName,address as userAddress,sex as userSex,birthday as userBirthday from user;-->
    select * from user;
</select>

若返回单独数据，resultType为返回数据类型，若封装实体，则用resultMap，result的property值为封装实体类的属性，column值为数据库对应字段名，即将封装实体类的属性与数据库字段手动对应

2.动态取值#{ }

<!-- 保存用户 -->
<insert id="saveUser" parameterType="user" >
    <!-- 配置插入操作后，获取插入数据的id -->
    <selectKey keyProperty="userId" keyColumn="id" resultType="int" order="AFTER">
        select last_insert_id();
    </selectKey>
    insert into user(username,address,sex,birthday)values(#{userName},#{userAddress},#{userSex},#{userBirthday});
</insert>

#{}对应parameterType的封装的user类的属性

<!-- 删除用户-->
<delete id="deleteUser" parameterType="java.lang.Integer">
    delete from user where id = #{uid}
</delete>

<!-- 根据id查询用户 -->
<select id="findById" parameterType="INT" resultMap="userMap">
    select * from user where id = #{uid}
</select>

/**
 * 根据Id删除用户
 * @param userId
 */
void deleteUser(Integer userId);

/**
 * 根据id查询用户信息
 * @param userId
 * @return
 */
User findById(Integer userId);

#{uid}应该取自方法的参数userId

3.url与uri

<!-- 配置properties
    可以在标签内部配置连接数据库的信息。也可以通过属性引用外部配置文件信息
    resource属性： 常用的
        用于指定配置文件的位置，是按照类路径的写法来写，并且必须存在于类路径下。
    url属性：
        是要求按照Url的写法来写地址
        URL：Uniform Resource Locator 统一资源定位符。它是可以唯一标识一个资源的位置。
        它的写法：
            http://localhost:8080/mybatisserver/demo1Servlet
            协议      主机     端口       URI

        URI:Uniform Resource Identifier 统一资源标识符。它是在应用中可以唯一定位一个资源的。
-->
<properties url="file:///D:/IdeaProjects/day02_eesy_01mybatisCRUD/src/main/resources/jdbcConfig.properties">
   <!-- <property name="driver" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost:3306/eesy_mybatis"></property>
    <property name="username" value="root"></property>
    <property name="password" value="1234"></property>-->
</properties>

文件夹中的默认 协议与主机是：file://与/。 这里是使用外挂文件连接池的参数，即外部再写jdbcConfig.properties，使用文件夹的路径则为上述的url

4.sql配置xml文件里的mysql配置环境

<!--配置环境-->
<environments default="mysql">
    <!-- 配置mysql的环境-->
    <environment id="mysql">
        <!-- 配置事务 -->
        <transactionManager type="JDBC"></transactionManager>

        <!--配置连接池-->
        <dataSource type="POOLED">
            <property name="driver" value="${jdbc.driver}"></property>
            <property name="url" value="${jdbc.url}"></property>
            <property name="username" value="${jdbc.username}"></property>
            <property name="password" value="${jdbc.password}"></property>
        </dataSource>
    </environment>
</environments>

连接池可以取jdbcConfig.properties的参数，value的动态${jdbc.driver}的参数要与jdbcConfig.properties里属性对应

<!-- 配置映射文件的位置 -->
<mappers>
    <!--<mapper resource="com/itheima/dao/IUserDao.xml"></mapper>-->
    <!-- package标签是用于指定dao接口所在的包,当指定了之后就不需要在写mapper以及resource或者class了 -->
    <package name="com.itheima.dao"></package>
</mappers>

5.抽取重复的sql语句

<!-- 了解的内容：抽取重复的sql语句-->
<sql id="defaultUser">
    select * from user
</sql>
<!-- 查询所有 -->
<select id="findAll" resultMap="userMap">
    <include refid="defaultUser"></include>
</select>

6.where与if标签

<select id="findUserByCondition" resultMap="userMap" parameterType="user">
    select * from user
    <where>
        <if test="userName != null">
            and username = #{userName}
        </if>
        <if test="userSex != null">
            and sex = #{userSex}
        </if>
    </where>
</select>

用了，mybatis就自动优化where条件，就不用写where1=1了，类似于条件语句

7.“foreach"标签

<!-- 根据queryvo中的Id集合实现查询用户列表 -->
<select id="findUserInIds" resultMap="userMap" parameterType="queryvo">
    <include refid="defaultUser"></include>
    <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为字段，open与close为拼接的前后两端，item为其之间的集合（即括号内的内容），separator=","表示用逗号分隔。

8.一对多关系

账户与用户的对应

public class User implements Serializable {

    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;

    //一对多关系映射：主表实体应该包含从表实体的集合引用
    private List<Account> accounts;

public class Account implements Serializable {

    private Integer id;
    private Integer uid;
    private Double money;

    //从表实体应该包含一个主表实体的对象引用
    private User user;

将要关联的属性或者封装实体类引入到主表实体中。

账户信息与该账户的用户的信息整合

public class AccountUser extends Account {

    private String username;
    private String address;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return super.toString()+"        AccountUser{" +
                "username='" + username + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

整合封装类可以继承父类，则有主表的属性，再引入从表属性，则整合了主表与从表，在通过toString打印父类和从表属性。（这方法不常用）

一、查询所有账户同时包含用户名和地址信息

<!-- 定义封装account和user的resultMap -->
<resultMap id="accountUserMap" type="account">
    <id property="id" column="aid"></id>
    <result property="uid" column="uid"></result>
    <result property="money" column="money"></result>
    <!-- 一对一的关系映射：配置封装user的内容-->
    <association property="user" column="uid" javaType="user">
        <id property="id" column="id"></id>
        <result column="username" property="username"></result>
        <result column="address" property="address"></result>
        <result column="sex" property="sex"></result>
        <result column="birthday" property="birthday"></result>
    </association>
</resultMap>

<!-- 查询所有 -->
<select id="findAll" resultMap="accountUserMap">
    select u.*,a.id as aid,a.uid,a.money from account a , user u where u.id = a.uid;
</select>

<!--查询所有账户同时包含用户名和地址信息-->
<select id="findAllAccount" resultType="accountuser">
    select a.*,u.username,u.address from account a , user u where u.id = a.uid;
</select>

二、查询所有用户，同时获取到用户下所有账户的信息

<!-- 定义User的resultMap-->
<resultMap id="userAccountMap" type="user">
    <id property="id" column="id"></id>
    <result property="username" column="username"></result>
    <result property="address" column="address"></result>
    <result property="sex" column="sex"></result>
    <result property="birthday" column="birthday"></result>
    <!-- 配置user对象中accounts集合的映射 -->
    <collection property="accounts" ofType="account">
        <id column="aid" property="id"></id>
        <result column="uid" property="uid"></result>
        <result column="money" property="money"></result>
    </collection>
</resultMap>

<!-- 查询所有用户，同时获取到用户下所有账户的信息 -->
<select id="findAll" resultMap="userAccountMap">
    select * from user u left outer join account a on u.id = a.uid
</select>

<!-- 根据id查询用户 -->
<select id="findById" parameterType="INT" resultType="user">
    select * from user where id = #{uid}
</select>

9.多对多关系

public class Role implements Serializable {

    private Integer roleId;
    private String roleName;
    private String roleDesc;

    //多对多的关系映射：一个角色可以赋予多个用户
    private List<User> users;

public class User implements Serializable {

    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;

    //多对多的关系映射：一个用户可以具备多个角色
    private List<Role> roles;

查询所有角色

<!--定义role表的ResultMap-->
<resultMap id="roleMap" type="role">
    <id property="roleId" column="rid"></id>
    <result property="roleName" column="role_name"></result>
    <result property="roleDesc" column="role_desc"></result>
    <collection property="users" ofType="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="address" property="address"></result>
        <result column="sex" property="sex"></result>
        <result column="birthday" property="birthday"></result>
    </collection>
</resultMap>

<!--查询所有-->
<select id="findAll" resultMap="roleMap">
   select u.*,r.id as rid,r.role_name,r.role_desc from role r
    left outer join user_role ur  on r.id = ur.rid
    left outer join user u on u.id = ur.uid
</select>

查询所有用户

<!-- 定义User的resultMap-->
<resultMap id="userMap" type="user">
    <id property="id" column="id"></id>
    <result property="username" column="username"></result>
    <result property="address" column="address"></result>
    <result property="sex" column="sex"></result>
    <result property="birthday" column="birthday"></result>
    <!-- 配置角色集合的映射 -->
    <collection property="roles" ofType="role">
        <id property="roleId" column="rid"></id>
        <result property="roleName" column="role_name"></result>
        <result property="roleDesc" column="role_desc"></result>
    </collection>
</resultMap>

<!-- 查询所有 -->
<select id="findAll" resultMap="userMap">
    select u.*,r.id as rid,r.role_name,r.role_desc from user u
     left outer join user_role ur  on u.id = ur.uid
     left outer join role r on r.id = ur.rid
</select>

<!-- 根据id查询用户 -->
<select id="findById" parameterType="INT" resultType="user">
    select * from user where id = #{uid}
</select>