MyBatis的使用

不要说JPA、Hibernate这些面向对象、但我开发以来用的最多的就是它MyBatis

 了解更多JAVA后台知识整理：JAVA后台系列目录

介绍一下使用流程（以springboot为例）

     1.  引用jar文件， 配置pom.xml

         <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.21</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>

     2. 配置扫描路径 

#mybaties配置文件
mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

#数据库配置
spring.datasource.basic.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.basic.jdbc-url = jdbc:mysql://localhost:3306/test1
spring.datasource.basic.username = root
spring.datasource.basic.password = 123456

    3. 配置config.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>
    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer" />
        <typeAlias alias="Long" type="java.lang.Long" />
        <typeAlias alias="HashMap" type="java.util.HashMap" />
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
        <typeAlias alias="ArrayList" type="java.util.ArrayList" />
        <typeAlias alias="LinkedList" type="java.util.LinkedList" />
    </typeAliases>
</configuration>

   4.  相关启动配置类

     

package com.example.demo.config.datasource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.basic", sqlSessionTemplateRef  = "basicSqlSessionTemplate")
public class BasicDataSourceConfig {


    @Bean(name = "basicDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.basic")
    @Primary
    public DataSource basicDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "basicSqlSessionFactory")
    @Primary
    public SqlSessionFactory basicSqlSessionFactory(@Qualifier("basicDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/basic/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "basicTransactionManager")
    @Primary
    public DataSourceTransactionManager basicTransactionManager(@Qualifier("basicDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "basicSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate basicSqlSessionTemplate(@Qualifier("basicSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }



}

  5. 配置mapper文件、mapper类、实体类

       实体类：

        

      mapper类

public interface UserRecordMapper {


    /**
     * 保存用户操作记录
     */
    int saveUserRecord(UserRecord userRecord);


}

     mapper文件

        

<?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.example.demo.mapper.operator.UserRecordMapper">

    <resultMap id="BaseResultMap" type="com.example.demo.model.operator.UserRecord">
        <result column="user_id" property="userId"/>
        <result column="record_type" property="userName"/>
        <result column="content" property="content"/>
        <result column="login_ip" property="loginIp"/>
        <result column="create_time" property="createTime"/>
    </resultMap>


    <sql id="Base_Column_List">
        user_id,record_type,content,login_ip,create_time

    </sql>

    <!--保存信息-->
    <insert id="saveUserRecord" parameterType="com.example.demo.model.operator.UserRecord">
        INSERT INTO
        sys_user
        (user_id,record_type,content,login_ip,create_time)
        VALUES
        (#{userId}, #{record_type},#{content},#{loginIp} ,now())
    </insert>


</mapper>

  以上就是使用基本配置、在开发中、有些人喜欢使用mybatis-plus这种插件快速配置、可我不太喜欢那种配置完文件感觉太乱，没事手写一下熟悉数据结构、不管是命名、

  还是后期维护感觉看着舒服、一个人一个习惯吧，不做过多评价，

   介绍下mybatis操作中我如何使用多表联查、数组、like语句、比较符号这类

  Like语句的使用
  <select id="queryUserInfoByPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM sys_user
        WHERE
        1=1
        <if test="userName != null and userName!='' ">
            and user_name LIKE CONCAT('%',#{userName},'%')
        </if>
        ORDER by #{sortBy} #{sortByType}
        limit #{start},#{end}
   </select>

   
     <!--list的使用-->      

    <select id="queryMenuInterFaceList" parameterType="java.util.List" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM sys_menu_interface
        WHERE menu_id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
      
     
      单表统计查询
     <resultMap id="orderStatMap" type="com.example.demo.model.basic.ChargeOrderStatVO">
        <result column="sum(total_money)" property="sumTotalMoney"/>
        <result column="sum(total_elec)" property="sumTotalElec"/>
        <result column="sum(service_fee)" property="sumServiceFee"/>
        <result column="sum(total_power)" property="sumTotalPower"/>
        <result column="count(*)" property="sumCount"/>
        <result column="sum(charge_time)" property="sumChargeTimes"/>
    </resultMap>

        <select id="queryOrderStat" resultMap="orderStatMap">
        SELECT
        sum(total_money) as sumTotalMoney,
        sum(total_elec) as sumTotalElec,
        sum(service_fee) as sumServiceFee,
        sum(total_power) as sumTotalPower,
        count(*) as sumCount,
        sum(charge_time) as sumChargeTimes
        FROM charge_order
        WHERE
        1=1
        <if test="startTime != null">
            AND start_time&gt;#{startTime}
        </if>
       </select>

       多表联查同单表统计相似，但建议少用非用不可，一定要考虑两表数量级
   

   了解更多JAVA后台知识整理：JAVA后台系列目录