Mybatis

mybatis(ORM对象关系映射)

概念:一款优秀的持久层(Dao)框架,支持定制化 SQL、存储过程以及高级映射

优势:

sql 语句有开发人员编写后期优化比较方便
适合传统项目，用于需求变化高的项目(互联网项目电商金融项目...)
市面用的最多的一套持久层框架

Hibernate:自动化ORM框架

可以通过框架来创建
不依赖具体的数据库平台
可以实现基本的CRUD 而不提供sql 直接调用API 方法即可

缺点:

表数据量较大时查询效率比较低
用于需求变化不高的项目传统型项目(OA 后端管理 ERP 系统...)
学习成本较高(实体与实体之间映射 sql 优化)

mybatis搭建

1.创建maven普通项目

2.添加坐标 --pom.xml

3.添加资源文件 --log4j .properties

4.添加全局配置文件 --mybatis.xml

文件模板参考官网 https://mybatis.org/mybatis-3/zh/getting-started.html

<mappers>
        <mapper resource="com/test/mappers/UserMapper.xml"/>
</mappers>

5.添加sql映射文件 --UserMapper.xml

映射文件模本配置 https://mybatis.org/mybatis-3/zh/getting-started.html

<mapper namespace="com.test.mappers.UserMapper">
    <!--
        namespace:命名空间属性
             值唯一  在整个应用程序中不允许出现相同的命名空间  规则:包名.文件名
    -->


    <!--
       select 标签:查询标签
           id:用于区分同一个xml 文件不同的标签  同文件下 标签id 值唯一
           resultType:输出结果类型
           parameterType:输入参数类型
           标签体:待执行的sql 语句   #{}:参数占位符  类似于 原生的sql 中? 占位符
    -->
    <select id="queryUserByUserId" parameterType="int" resultType="com.test.vo.User">
        select id, user_name as userName, user_pwd as userPwd, flag, create_time as createTime from user where id=#{id}
    </select>

</mapper>

6.执行测试

String file = "mybatis.xml";
        InputStream is= Resources.getResourceAsStream(file);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = sqlSessionFactory.openSession();
        /**
         * 参数1-statement:待执行的某个sql 对应的标签具体路径  namespace.标签id
         * 参数2:参数值
         */
        User user= session.selectOne("com.test.mappers.UserMapper.queryUserByUserId",75);
        System.out.println(user);
        session.close();

总结:UserMapper.xml映射测试的selectone

Mybatis 映射文件-输入输出参数

<select id="queryUserByUserId" parameterType="int" resultType="com.test.vo.User">
        select id, user_name as userName, user_pwd as userPwd, flag, create_time as createTime from user where id=#{id}
</select>

<!--
insert:添加记录标签
添加记录返回影响行数
添加记录返回主键
批量添加

添加记录返回主键01
useGeneratedKeys:true mybatis 执行添加时获取添加后的主键值
keyProperty:id 主键值映射到User id 变量

-->

<insert id="saveUserHasKey" parameterType="User" useGeneratedKeys="true" keyProperty="id">
insert into user(user_name, user_pwd, flag, create_time) values (#{userName},#{userPwd},#{flag},#{createTime})
</insert>

<insert id="saveUserHasKey02" parameterType="User">
<selectKey order="AFTER" keyProperty="id" resultType="int">
select last_insert_id()
</selectKey>
insert into user(user_name, user_pwd, flag, create_time) values (#{userName},#{userPwd},#{flag},#{createTime})
</insert>

<update id="updateUser" parameterType="User">
　　update user set user_name=#{userName},user_pwd=#{userPwd},flag=#{flag} where id=#{id}
</update>

parameterType: 基本类型（4类8种）、常见类型(String,日期)、JavaBean,集合(List 、Map)、数组

resultType:输出结果类型基本类型(四类八种) String|日期 JavaBean List Map List<Map>

resultMap

基于接口代理的CRUD

1. mapper.xml中 namespace等于接口类全限定名
2. mapper.java接口中的方法名必须与mapper.xml中statement id 一致
3. mapper.java 输入参数类型必须与mapper.xml中statement 的parameterType参数类型一致
4.mapper.java 中方法的返回值类型必须与mapper.xml 中对应statement 返回值类型一致。
　　接口名与映射文件名称一致（非集成环境）
　　映射文件与接口处于同一个包中(非集成环境)

1.定义接口(com.test.mappers)

public interface AccountMapper {
    public Account queryAccountById(Integer id);
    public List<Account> queryAccountsByParams(AccountQuery accountQuery);
}

2.定义映射文件((com.test.mappers))

<mapper namespace="com.test.mappers.AccountMapper">


    <select id="queryAccountById" parameterType="int" resultType="Account">
        select id, aname, type, money, user_id as userId, create_time as createTime, update_time as updateTime, remark
        from  account
        where id=#{id}
    </select>

    <sql id="account_columns">
        id, aname, type, money, user_id as userId, create_time as createTime, update_time as updateTime, remark
    </sql>

    <select id="queryAccountsByParams" parameterType="AccountQuery" resultType="com.test.vo.Account">
        select <include refid="account_columns"/>
        from account where aname like concat('%',#{aname},'%') and type=#{type}
    </select>
</mapper>

3.添加映射文件到全局mybatis.xml

<mappers>
        <mapper resource="com/test/mappers/UserMapper.xml"/>
        <mapper resource="com/test/mappers/AccountMapper.xml"></mapper>
</mappers>

总结:AccountMapper接口映射AccountMapper.xml

4.测试

 @Before
    public void init() throws IOException {
        String file = "mybatis.xml";
        InputStream is = Resources.getResourceAsStream(file);
        factory = new SqlSessionFactoryBuilder().build(is);
    }


    @Test
    public void test01() {
        SqlSession session = factory.openSession();
        // 获取接口的代理对象
        AccountMapper accountMapperProxy = session.getMapper(AccountMapper.class);
        Account account = accountMapperProxy.queryAccountById(142);
        System.out.println(account);
        session.close();
    }

Mybatis 动态SQL 配置

if 标签

逻辑判断标签判断表达式 true

<if test="aname !=null and aname !=''">
            and  aname like concat('%',#{aname},'%')
</if>

where 标签

条件过滤如果紧跟where 后第一个逻辑判断条件成立时 and|or 单词被忽略

<where>
            <if test="@Ognl@isNotEmpty(aname)">
                and  aname like concat('%',#{aname},'%')
            </if>
            <if test="@Ognl@isNotEmpty(type)">
                and type=#{type}
            </if>
</where>

choose when otherwise 标签

对结果条件判断执行二选一类似 if-else

　　　　 <choose>
            <when test="userId !=null">
                money
            </when>
            <otherwise>
                type
            </otherwise>
        </choose>
        from account
        <where>
            <if test="aname !=null and aname !=''">
                and  aname like concat('%',#{aname},'%')
            </if>
            <if test="type !=null and type !=''">
                and type=#{type}
            </if>
            <if test="userId !=null">
                and user_id=#{userId}
            </if>
            <if test="time !=null and time !=''">
                and create_time &gt;=#{time}
            </if>
        </where>

trim 标签

可以替代where(update) 标签类似功能后第一个逻辑判断条件成立时 and|or 单词被忽略

select <include refid="account_columns"/>
        from account
        <trim prefix="where"  prefixOverrides="and |or" >
            <if test="aname !=null and aname !=''">
                and  aname like concat('%',#{aname},'%')
            </if>
            <if test="type !=null and type !=''">
                and type=#{type}
            </if>
            <if test="userId !=null">
                and user_id=#{userId}
            </if>
            <if test="time !=null and time !=''">
                and create_time &gt;=#{time}
            </if>
        </trim>

set 标签

用于更新如果最后一个字段出现, 该字符会被自动忽略-

<update id="updateAccountById" parameterType="Account">
         update account
         <set>
             <if test="aname !=null">
                 aname=#{aname},
             </if>
             <if test="type !=null">
                 type=#{type},
             </if>
             <if test="money !=null">
                 money=#{money}
             </if>
         </set>
         where id=#{id}
</update>

foreach 标签

用于批量操作(批量添加更新删除)

批量添加

<insert id="saveUserBatch" parameterType="list">
    insert into  user(user_name, user_pwd, flag, create_time) values
    <foreach collection="list" item="item" separator=",">
        (#{item.userName},#{item.userPwd},#{item.flag},#{item.createTime})
    </foreach>
</insert>

批量更新

<update id="updateUserPwdByIdsBatch" parameterType="map">
       update user set user_pwd=#{userPwd}
       where id in
       <foreach collection="ids" item="item" open="(" separator="," close=")" >
           #{item}
       </foreach>
</update>

基于注解sql 配置

添加注解

@Insert

@InsertProvider　　

　　type:Class 产生Sql字符串的Java 类

　　method:方法名称

接口里直接添加sql

@Insert("insert into account(aname,type,money,remark,create_time,update_time,user_id) " +
            " values(#{aname},#{type},#{money},#{remark},#{createTime},#{updateTime},#{userId})")
   // @InsertProvider()
    public int saveAccount(Account account);

@SelectProvider(type = AccountProvider.class,method = "getQueryAccountsByParamsSql01")
public List<Account> queryAccountsByParams01(AccountQuery accountQuery);


public class AccountProvider {
     public String getQueryAccountsByParamsSql01(AccountQuery accountQuery){
       return new SQL(){
            {
                SELECT("id, aname, type, money, user_id as userId, create_time as createTime, update_time as updateTime, remark ");
                FROM("account");
                WHERE(" 1=1 ");
                if(StringUtils.isNotBlank(accountQuery.getAname())){
                    WHERE(" aname like concat('%',#{aname},'%') ");
                }
                if(StringUtils.isNotBlank(accountQuery.getType())){
                    WHERE("  type=#{type} ");
                }
                if(StringUtils.isNotBlank(accountQuery.getTime())){
                    WHERE("  create_time >=#{time}") ;
                }
                if(accountQuery.getUserId()!=null){
                    WHERE(" user_id=#{userId}");
                }
            }
        }.toString();
    }
    
}

@SelectProvider(type = AccountProvider.class,method = "getQueryAccountsByParamsSql02")
public List<Account> queryAccountsByParams02(AccountQuery accountQuery);

public class AccountProvider {
    
public String getQueryAccountsByParamsSql02(AccountQuery accountQuery){
        StringBuffer stringBuffer=new StringBuffer("select  id, aname, type, money, user_id as userId, create_time as createTime, update_time as updateTime, remark " +
                " from account where 1=1 ");
        if(StringUtils.isNotBlank(accountQuery.getAname())){
            stringBuffer.append(" and aname like concat('%',#{aname},'%')  ");
        }
        if(StringUtils.isNotBlank(accountQuery.getType())){
            stringBuffer.append(" and type=#{type}  ");
        }
        if(StringUtils.isNotBlank(accountQuery.getTime())){
            stringBuffer.append(" and create_time >=#{time}  ");
        }
        if(accountQuery.getUserId()!=null){
            stringBuffer.append(" and user_id=#{userId}  ");
        }
        return stringBuffer.toString();
    }
    
}

更新注解

@Update

@UpdateProvider

删除注解

@Delete

@DeleteProvider

resultType 两种接收方式:

方法1：dto接收结果

// 接口定义查询方法  结果使用dto 接收
public UserDto queryUserCardInfoByUserId(Integer userId);


public class UserDto {
    private Integer userId;
    private String userName;
    private String userPwd;
    private String flag;
    private Date createTime;
    private String num;// 身份证编号
    private Integer cardId;// 身份证id
    
    .......
}

<!--
       一对一关联查询 resultType
    -->
    <select id="queryUserCardInfoByUserId" parameterType="int" resultType="UserDto">
        select
        u.id as userId, user_name as userName, user_pwd as userPwd, flag, create_time as createTime,c.id as cardId,c.num
        from
           user u left  join  id_card  c on u.id = c.user_id
        where u.id=#{userId}
    </select>

方法2:使用map

 public Map<String,Object> queryUserCardInfoByUserId02(Integer userId);
<select id="queryUserCardInfoByUserId02" parameterType="int" resultType="map">
        select
        u.id as userId, user_name as userName, user_pwd as userPwd, flag, create_time as createTime,c.id as cardId,c.num
        from
           user u left  join  id_card  c on u.id = c.user_id
        where u.id=#{userId}
    </select>

resultMap 接收

 public User queryUserCardInfoByUserId03(Integer userId);
<resultMap id="user_map" type="User">
        <!--
           主表结果映射
              id:主键映射配置  
        -->
        <id column="userId" property="id"></id>
        <result column="userName" property="userName"></result>
        <result column="userPwd" property="userPwd"></result>
        <result column="flag" property="flag"></result>
        <result column="createTime" property="createTime"></result>
    </resultMap>

<resultMap id="user_card_map" type="User" extends="user_map">
        <!--
           一对一 映射配置
        -->
        <association property="idCard" javaType="IdCard">
            <!--
               从表主键映射配置
            -->
            <id column="cardId" property="id"></id>
            <result column="num" property="num"></result>
        </association>
    </resultMap>
    <select id="queryUserCardInfoByUserId03"  parameterType="int" resultMap="user_card_map">
        select
            u.id as userId, user_name as userName, user_pwd as userPwd, flag, create_time as createTime,c.id as cardId,c.num
        from
           user u left  join  id_card  c on u.id = c.user_id
        where u.id=#{userId}
    </select>

Spring 整合Mybatis

1.创建Maven工程

2.添加坐标依赖

3.添加资源文件

spring.xml 、 mybatis.xml、jdbc.properties、log4j.properties

spring.xml

4.编写代码

以用户模块查询为主进行测试

Mybatis 分页插件整合

1.添加分页插件坐标

<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.10</version>
        </dependency>

2.添加分页插件标签-mybatis.xml

<plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>

3.添加分页查询dao 方法与sql 配置

public interface AccountDao extends BaseMapper<Account,Integer> {

    public List<Account> queryAccountsByParams(AccountQuery accountQuery);

}

 <sql id="account_columns">
        id, aname, type, money, user_id as userId, create_time as createTime, update_time as updateTime, remark
    </sql>
    <!--
        动态sql 标签-if  条件判断标签
    -->
    <select id="queryAccountsByParams" parameterType="AccountQuery" resultType="Account">
        select <include refid="account_columns"/>
        from account
        where 1=1
        <if test="aname !=null and aname !=''">
            and  aname like concat('%',#{aname},'%')
        </if>
         <if test="type !=null and type !=''">
             and type=#{type}
         </if>
         <if test="userId=null">
             and user_id=#{userId}
         </if>
         <if test="time !=null and time !=''">
             and create_time &gt;=#{time}
         </if>
    </select>

4.AccountService 实现分页查询

@Service
public class AccountService extends BaseService<Account,Integer> {

    @Autowired
    private AccountDao accountDao;

    public List<Account> queryAccountsByParams(AccountQuery accountQuery){
        PageHelper.startPage(accountQuery.getPageNum(),accountQuery.getPageSize());
        return accountDao.queryAccountsByParams(accountQuery);
    }


    public PageInfo<Account> queryAccountsByParams02(AccountQuery accountQuery){
        PageHelper.startPage(accountQuery.getPageNum(),accountQuery.getPageSize());
        List<Account> accounts=accountDao.queryAccountsByParams(accountQuery);
        return new PageInfo<Account>(accounts);
    }
}
}

5.执行测试

posted @ 2019-11-22 22:39 小小的小太阳阅读(144) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

小小小太阳