Mybatis框架2【企业模式】

---

前言

通过dao和映射文件的关联来完成操作—企业开发模式

---

一、创建一个dao接口

public interface UserDao {
    /**
     * 查询所有
     * @return
     */
    public List<User> findAll();
}

二、映射文件

1.建立映射文件

注意: namespace必须和dao接口一样，而且标签的id必须和你接口的方法名一样。

<?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">
<!--namespace:命名空间
       必须和dao相同
-->
<mapper namespace="com.ykq.dao.UserDao">
    <select id="findAll" resultType="com.ykq.entity.User">
        select * from tb_user
    </select>
</mapper>

2.测试

代码如下（示例）：

@Test
    public void testFindAll() throws Exception{
        Reader resourceAsReader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(resourceAsReader);
        SqlSession session=factory.openSession();
		//获取相应接口的代理对象
        UserDao userDao=session.getMapper(UserDao.class);
        List<User> list = userDao.findAll();
        System.out.println(list);
        session.commit();
        session.close();
    }

---

二、sql的多种用法

1.传递多个参数

提示：我们在dao接口中某些方法可能需要传递多个参数

public interface TbUserDao {
    List<TbUser> findOne(@Param("username") String username, @Param("realname") String realname, @Param("email") String email);
    Integer delete(@Param("userid") Integer userid);
    Integer update(@Param("userid") Integer userid, @Param("username") String username, @Param("realname") String realname, @Param("email") String email);
    Integer insert(@Param("username") String username, @Param("realname") String realname, @Param("email") String email);
}

映射文件

<?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.aaa.dao.TbUserDao">

<resultMap id="s" type="com.aaa.entity.TbUser">
    <!--colum：sql列表列名，property:实体类对应的属性名-->
    <id column="userid" property="userid"/>
    <result column="username" property="username"></result>
    <result column="realname" property="realname"></result>
    <result column="email" property="email"></result>
</resultMap>

    <select id="findOne" resultType="com.aaa.entity.TbUser">
        select *from tb_user
        <where>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="realname!=null">
                and realname=#{realname}
            </if>
            <if test="email!=null">
                and email=#{email}
            </if>
        </where>
    </select>

    <delete id="delete" parameterType="com.aaa.entity.TbUser">
        delete from tb_user
        <where>
            <if test="userid!=null">
                and userid=#{userid}
            </if>
        </where>
    </delete>

    <update id="update">
        update tb_user set username=#{username},realname=#{realname},email=#{email}
        <where>
            <if test="userid!=null">
                and userid=#{userid}
            </if>
        </where>
    </update>

<insert id="insert">
    insert into tb_user value (null,#{username},#{realname},#{email})
    </insert>
</mapper>

测试类

 @Test
    public void findOne() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        TbUserDao tbUserDao = session.getMapper(TbUserDao.class);
        List<TbUser> one = tbUserDao.findOne(null,null,null);
        System.out.println(one);
        session.commit();
        session.close();
    }

当查询的属性都为null时是查询的所有

2.添加时如何返回递增的主键值

 <!--添加用户
          useGeneratedKeys:设置使用生成的主键
          keyProperty: 赋值给哪个属性
    -->
    <insert id=" insert" parameterType="com.ykq.entity.User"
            useGeneratedKeys="true" keyProperty="userId">
          insert into tb_user value (null,#{username},#{realname},#{email})
    </insert>

3.解决列名和属性名不一致

1.为查询的列起别名，而别名和属性名一致

 <!--根据id查询学生信息-->
    <select id="findOne" resultType="com.ykq.entity.Student">
        select userid id,username name,realname real from tb_user where userid=#{id}
    </select>

2.使用resultMap完成列和属性之间的映射关系

<resultMap id="StuMapper" type="com.ykq.entity.Student">
         <!--主键的映射关系 column:列名 property:属性名-->
         <id column="userid" property="id"/>
         <!--普通列的映射关系-->
         <result column="username" property="name"/>
         <result column="realname" property="real"/>
    </resultMap>

 <!--resultType和ResultMap二者只能用一个-->
    <select id="findOne" resultMap="StuMapper">
        select * from tb_user where userid=#{id}
    </select>

注意：列名和属性名有些一致的，可以在resultMap中不写映射关系

4.mybatis中动态sql标签

元素	作用和描述
if	条件判断；单条件判断
choose（when，otherwise）	条件判断，相当于Java when；多条件判断
where，set	辅助；处理sql语句拼接问题
foreache	循环；循环

1.if标签–单条件判断

 //如果name不为null则按照name查询 如果为null则查询所有
    public List<TbUser> findOne(
     @Param("username") String username,
     @Param("realname") String realname, 
     @Param("email") String email);

 <select id="findOne" resultType="com.aaa.entity.TbUser">
        select *from tb_user where 1=1
            <if test="username!=null and username='' ">
                and username=#{username}
            </if>
            <if test="realname!=null">
                and realname=#{realname}
            </if>
            <if test="email!=null">
                and email=#{email}
            </if>
    </select>

2. choose标签 多条件分支判断

<select id="findOne" resultType="com.aaa.entity.TbUser">
         select *from tb_user where 1=1
         <choose>
            <when test="username!=null and username='' ">
                and username=#{username}
            </when>
            <when test="realname!=null">
                and realname=#{realname}
            </when>
            <otherwise>
                and email=0}
            </otherwise>
            </choose>
    </select>

3.where标签

作用:可以自动为你添加where关键字，并且可以帮你去除第一个and |or
用where标签后就可以不用加where 1=1

 <select id="findOne" resultType="com.aaa.entity.TbUser">
        select *from tb_user
        <where>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="realname!=null">
                and realname=#{realname}
            </if>
            <if test="email!=null">
                and email=#{email}
            </if>
        </where>
    </select>