Cacher is the code snippet organizer for pro developers

一、内容介绍

1. mybatis自带数据源
2. 事务问题
3. 动态的sql语句：if标签和循环标签
4. 多表之间的关系配置 一对一 一对多 多对多

二、mybatis自带数据源

<!--environments: 环境-->
    <environments default="development">
        <environment id="development">
            <!-- transactionManager : 事务管理-->
            <transactionManager type="JDBC"/>
            <!-- 数据源的配置:
                type： 类型
                    POOLED： 使用连接池
                            一般在开发时使用第三方的连接池：c3p0, dbcp ,spring 连接池，德鲁伊
                    UNPOOLED : 不使用连接池
                    JNDI: 需要自定义服务
            -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

三、事务问题

@Test
    public void testDelete() throws IOException {
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));

        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        //获取某接口的动态代理对象(获取某接口的一个实现类)
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        mapper.delById(7);

        sqlSession.close();
    }

四、动态sql语句

1、if语句

<select id="findByCondition01" resultType="user">
        select
          <include refid="userColumn"></include>
        from user where 1=1
        <if test="username != null">
            and username like "%"#{username}"%"
        </if>
        <if test="sex != null">
            and sex = #{sex}
        </if>
    </select>

2、where语句

<select id="findByCondition" resultType="user">
        select * from user
--         使用where标签可以自动帮助处理第一个and符号
        <where>
            <if test="username != null">
                and username like "%"#{username}"%"
            </if>
            <if test="sex != null">
                and sex = #{sex}
            </if>
        </where>
    </select>

3、SQL片段

<sql id="userColumn">
        username,password,sex,birthday
    </sql>

    <select id="findByCondition01" resultType="user">
        select
          <include refid="userColumn"></include>
        from user where 1=1
        <if test="username != null">
            and username like "%"#{username}"%"
        </if>
        <if test="sex != null">
            and sex = #{sex}
        </if>
    </select>

4、foreach语句

<!--delete from user where id in (1,2,3)-->
    <!--
        foreach: 循环
            属性：
                collection： array(数组类型) ,list(集合类型)
                index ： 索引
                open: 前缀
                close: 后缀
                item: 子项目（数组或者集合中的每一个元素）
                separator: 分隔符
    -->
    <delete id="deleteByArray" parameterType="int[]" >
        delete from user where id
        <foreach collection="array" open="in (" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </delete>

    <delete id="deleteByList" parameterType="list" >
        delete from user where id
        <foreach collection="array" open="in (" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </delete>

五、多表关联

1.2、一对一

a. 表映射

create table account(
    aid int primary key auto_increment,
    accountname varchar(20),
    money float(10,2),
    userId int,
    FOREIGN key(userId) REFERENCES user(id)

)

insert into account values(null, '123456789', 100000, 1);
insert into account values(null, '234567891', 100001, 6);

/**
 * 继承Account， 添加user的属性
 * @author 黑马程序员
 * @Company http://www.ithiema.com
 * @Version 1.0
 */
public class AccountUser  extends Account{
    private Integer id;
    private String username;
    private String password;
    private String sex;
    private Date birthday;
 }

b.配置文件

<select id="findAll" resultType="accountUser">
        select * from account a, user u where a.userId = u.id
</select>

1.2 一对一

a. 表映射

public class Account {
    private Integer aid;
    private String accountName;
    private Float money;
    private Integer userId;
//    一个账户对应一个用户
    private User user;
}

b.配置文件

<?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.itheima.dao.AccountDao">
    <!--结果映射-->
    <resultMap id="accountMap" type="account">
        <id property="aid" column="aid"></id>
        <result property="accountName" column="accountName"></result>
        <result property="money" column="money"></result>
        <result property="userId" column="userId"></result>
        <!--association： 关联 ， 一对一映射
            注意：如果使用了association  标签， 那么所有的属性与列必须手动映射
        -->
        <!--
            property： 属性名
            javaType: 属性的类型
        -->
        <association property="user" javaType="User">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="sex" property="sex"></result>
            <result column="birthday" property="birthday"></result>
        </association>
    </resultMap>

    <select id="findAll" resultMap="accountMap">
        select * from account a, user u where a.userId = u.id
    </select>

</mapper>

2、一对多

a. 实体类映射

public class User {
    private Integer id;
    private String username;
    private String password;
    private String sex;
    private Date birthday;

//    一个用户对应多个账户
    private List<Account> accountList;
}

b.配置文件

<?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.itheima.dao.UserDao">
    <!--
        column :列
        property：  属性名
    -->
    <!--属性名与列名不一致的情况下使用-->
    <resultMap id="userMap" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="sex" property="sex"></result>
        <result column="birthday" property="birthday"></result>
        <!--一对一 : association -->
        <!--一对多：collection -->
        <!--property ： 属性名 -->
        <!--ofType: 集合的泛型-->
        <collection property="accountList" ofType="account">
            <id property="aid" column="aid"></id>
            <result property="accountName" column="accountName"></result>
            <result property="money" column="money"></result>
            <result property="userId" column="userId"></result>
        </collection>
    </resultMap>

    <!--
        要查询所有的用户，查询该用户对应的账户信息
       需要使用外连接， 没有账户的用户也要查询出来

       左外连接：左表中所有的数据都会查询出来
       右外连接：右表中所有的数据都会查询出来
    -->
    <select id="findAll" resultMap="userMap">
        select * from user u left join account a on u.id = a.userId
    </select>

</mapper>

3、多对多

a. 表映射

create table role(
    id int primary key auto_increment,
    roleName varchar(20)
)
insert into role values(null, 'admin');
insert into role values(null, 'user');

-- 创建中间表 
create table user_role(
    userId int ,
    roleId int ,
    primary key(userId, roleId), -- 联合主键
    foreign key(userId) references user(id),
    foreign key(roleId) references role(id)
)

public class Role {

    private Integer id;
    private String roleName;

//    一个角色对应多个用户
    private List<User> userList;
}

public class User {
    private Integer id;
    private String username;
    private String password;
    private String sex;
    private Date birthday;

//    一个用户可以是多个角色
    private List<Role> roleList;
}
    

b.配置文件

RoleDao.xml

<?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.itheima.dao.RoleDao">

    <resultMap id="roleMap" type="role">
        <id column="roleid" property="id"></id>
        <result column="roleName" property="roleName"></result>
        <!--映射roleList-->
        <collection property="userList" ofType="user">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="sex" property="sex"></result>
            <result column="birthday" property="birthday"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="roleMap">
       select r.id roleId, r.roleName ,u.* from role r left join user_role ur
       on r.id = ur.roleId left join user u on u.id = ur.userId
    </select>
</mapper>

UserDao.xml

<?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.itheima.dao.UserDao">

    <resultMap id="userMap" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="sex" property="sex"></result>
        <result column="birthday" property="birthday"></result>
        <!--映射roleList-->
        <collection property="roleList" ofType="role">
            <id column="roleid" property="id"></id>
            <result column="roleName" property="roleName"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        select u.*,r.id roleid ,r.roleName from user u left join user_role ur
        on u.id = ur.userId left join role r on ur.roleId = r.id
    </select>
</mapper>