mybatis

mybatis调用的方法

//1，读取配置文件

InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");

//2，创建sqlSessionFactory工厂

SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);

//3，是用工厂生产sqlSession对象

SqlSession session = factory.openSession();

//4，使用sqlSession创建接口的代理对象

IUserDao userDao = session.getMapper(IUserDao.class);

//5，使用代理对象执行方法

List<User> users = userDao.findAll();
for (User user : users) {
System.out.println(user);
}

//6，释放资源

session.close();
in.close();

mybatis的主配置文件

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

配置properties
resource:用于指定配置文件的位置，是按照类路径的写法来写，并且必须存在于类路径下
resource="jdbcConfig.properties"
url:Uniform Resource Locator 统一资源定位符 可以唯一标识一个资源的位置
http://localhost:8080/mybatis/demo01Servlet
协议 主机 端口 URI（uri）
UR（uri）I:Uniform Resource Identifier 统一资源标识符 他是在某个应用中唯一定位资源的。

url="file:///D:/JavawebStudy/day02_01MybatisCRUD/src/main/resources/jdbcConfig.properties"

<properties resource="jdbcConfig.properties"></properties>

使用typeAliases配置别名 只能用于domain中类的别名

方法1

typeAliases用于配置别名 type属性指的是实体类全限定定类名 alias属性指的是别名 指定了别名就不用再区分大小写了

   <typeAlias type="com.itheima.domain.User" alias="user"></typeAlias>

方法2

package该包下的实体类都会注册别名 并且类名就是别名 不在区分大小写

   <typeAliases>
        <package name="com.itheima.domain"></package>
   </typeAliases>

配置环境

    <environments default="mysql">

配置mysql的环境

        <environment id="mysql">

配置事务的类型

            <transactionManager type="JDBC"></transactionManager>

配置数据源（连接池)

            <dataSource type="POOLED">

配置连接数据库的4个基本信息
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/com_mybatis
jdbc.username=root
jdbc.password=123456

                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

指定映射配置文件的位置，映射配置文件指的是每个dao独立的配置文件

方法1

    <mappers>
        <mapper resource="com/itheima/dao/UserDao.xml"/>
    </mappers>
</configuration>

方法2

package 标签是用于指定dao接口所在的包,当指定了就不需要再写resource或者class了

 <mappers>
        <package name="com.itheima.dao"/>
    </mappers>
</configuration>

映射配置文件 基于xml的

映射配置文件必须和dao层对应的到的包路径相同
com.itheima.dao.UserDao
com.itheima.dao.IUserDAo.xml

namespace:指定dao

<mapper namespace="com.itheima.dao.UserDao">

1，配置查询所有

id ： 查询方法，resultType：返回值类型

    <select id="findAll" resultType="com.itheima.domain.User">
        select * from user;
    </select>
</mapper>

2，保存用户

parameterType插入数据类型

<insert id="saveUser" parameterType="com.itheima.domain.User">
        <!--配置插入操作后，获取插入数据的id-->
        <selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
            select last_insert_id();
        </selectKey>
        insert into user(username,address,sex,birthday) values(#{username},#{address},#{sex},#{birthday});
    </insert>

3，更新操作

<update id="update" parameterType="com.itheima.domain.User">
        update user set username=#{username},address=#{address},sex=#{sex},birthday=#{birthday} where id=#{id};
    </update>

4，删除

<delete id="delete" parameterType="java.lang.Integer">
        delete from user where id = #{id};
    </delete>

5，通过id查询

<select id="findById" parameterType="java.lang.Integer" resultType="com.itheima.domain.User">
        select * from user where id=#{id};
    </select>

6，通过name查询

select * from user where username like '%${value}%'

    <select id="findByName" parameterType="String" resultType="com.itheima.domain.User">
        select * from user where username like #{name};
    </select>

7，查询总记录数

<select id="findTotal" resultType="int">
        select count(*) from user;
    </select>

8，根据QueryVo的条件来查询用户

  <select id="findUserByVo" parameterType="com.itheima.domain.QueryVo" resultType="com.itheima.domain.User">
        select * from user where username like #{user.username}
    </select>

9，动态的查询

1

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

2

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

Account 账户类
User 用户类

10，一对一的关系映射

在Account中设置User变量的对象 设置get和set方法 从表实体包含一个主表实体的引用
在IAccountDao.xml
定义封装account和user的resultMap
property: 原名字
column: 别名

    <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 property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="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>

查询账户和对应的用户 并创建一个AccountUser只有姓名和地址

 <select id="findAllAccount" resultType="AccountUser">
        select a.*,u.username,u.address from account a , user u where u.id = a.uid
    </select>

11，一对多关系映射

在User中设置Account变量的对象 设置get和set方法 主表实体应该包含从表实体集合引用
在IUserDao.xml
定义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对象中Account集合的映射 -->
        <collection property="accounts" ofType="account">
            <id property="id" column="aid"></id>
            <result property="uid" column="uid"></result>
            <result property="money" column="money"></result>
        </collection>
    </resultMap>

配置查询User对象和所对应Account

<select id="findAll" resultMap="userAccountMap">
        select *  from user u left outer join account a on u.id = a.UID
    </select>

User 用户类
Role 角色类
user_role用户角色类

12，多对多的关系映射，一个角色可以赋予多个用户

在Role类中，创建User变量的对象 设置get和set方法

<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 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>
    </resultMap>

查询角色对应的用户

     <select id="findAll" resultMap="roleMap">
        select u.*,r.id as rid,r.ROLE_NAME,r.ROLE_DESC from role r left join user_role ur on r.ID = ur.RID left outer join user u on u.id = ur.UID
    </select>

13，多对多的映射关系，一个用户可以赋予多个角色

在User类中，创建Role变量的对象 设置get和set方法

<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>

14，延迟加载

在主配置文件中加入

<settings>
        <!--开启mybatis延迟加载的开关-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

1,一对一的关系映射，配置封装user的内容
select属性指定的，查询用户的唯一标识
column属性指定内容，用户根据id查询时，所需要参数的值

<resultMap id="accountUserMap" type="account">
        <id property="id" column="id"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <association property="user" column="uid" javaType="user" select="com.itheima.dao.IUserDao.findById"></association>
    </resultMap>

<select id="findAll" resultMap="accountUserMap">
        select * from account
    </select>
<select id="findAccountByUid" resultType="account">
        select * from account where uid=#{uid}
    </select>

2,一对多的映射关系

<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对象中Account集合的映射 -->
        <collection property="accounts" ofType="account" select="com.itheima.dao.IAccountDao.findAccountByUid" column="id"></collection>
    </resultMap>

<select id="findAll" resultMap="userAccountMap">
        select *  from user
    </select>
    
    <select id="findById" parameterType="java.lang.Integer" resultType="user">
        select * from user where id=#{id}
    </select>

15,二级缓存

在著配置文件中加入，二级缓存的配置文件

    <settings>
        <setting name="cacheEnabled" value="true"/>
    </settings>

使用注解配置查询方法

主配置文件里要改
注解

<mappers>
        <mapper class="com.itheima.dao.IUserDao"></mapper>
    </mappers>

@Select

public interface IUserDao {
    /**
     * 查询所有操作
     * @return
     */
    @Select("select * from user")
    List<User> findAll();
}

1，查询所有和所有人的账户信息

    @Select("select * from user")
    List<User> findAll();

2，保存用户

    @Insert("insert into user(username,address,sex,birthday) values(#{username},#{address},#{sex},#{birthday})")
    void saveUser(User user);

3，更新用户

    @Update("update user set username=#{username},address=#{address},sex=#{sex},birthday=#{birthday} where id=#{id}")
    void update(User user);

4，删除操作

   @Delete("delete from user where id = #{id};")
    void delete(Integer id);

5，根据id查询用户

    @Select("select * from user where id=#{id}")
    User findById(Integer id);

6，通过名字查询

    @Select("select * from user where username like #{name};")
    List<User> findUserByName(String username);

7，查询总记录数

    @Select("select count(*) from user")
    int findTotal();

8,查询所有用户，并且获取每个账户所属的用户信息

 @Select("select * from account")
    @Results(id = "accountMap",value = {
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "uid",column = "uid"),
            @Result(property = "money",column = "money"),
            @Result(property = "user",column = "uid",one = @One(select="com.itheima.dao.IUserDao.findById",fetchType= FetchType.EAGER))
    })
    List<Account> findAll();

根据用户uid查询

    @Select("select * from account where uid = #{userId}")
    @ResultMap("accountMap")
    List<Account> findByUid(Integer userId);

9,查询所有和所有人的账户信息

@Select("select * from user")
    @Results(id = "userMap",value = {
            @Result(id = true,property = "userId",column = "id"),
            @Result(property = "userName",column ="username"),
            @Result(property = "userAddress",column ="address"),
            @Result(property = "userSex",column ="sex"),
            @Result(property = "userBirthday",column ="birthday"),
            @Result(property = "accounts",column = "id",
                    many = @Many(select = "com.itheima.dao.IAccountDao.findByUid", fetchType = FetchType.LAZY))
    })
    List<User> findAll();

根据id查询用户

 @Select("select * from user where id=#{id}")
    @ResultMap("userMap")
    User findById(Integer id);