MyBatis高频面试题

String resource = "org/mybatis/example/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

SqlSession session = sqlSessionFactory.openSession();

public User selectUser(String name, int deptId);
​
<select id="selectUser" resultMap="UserResultMap">
    select * from user
    where user_name = #{0} and dept_id = #{1}
</select>

public User selectUser(@Param("userName") String name, int @Param("deptId") deptId);
​
<select id="selectUser" resultMap="UserResultMap">
    select * from user
    where user_name = #{userName} and dept_id = #{deptId}
</select>

public User selectUser(Map<String, Object> params);
​
<select id="selectUser" parameterType="java.util.Map" resultMap="UserResultMap">
    select * from user
    where user_name = #{userName} and dept_id = #{deptId}
</select>

public User selectUser(User user);
​
<select id="selectUser" parameterType="com.jourwon.pojo.User" resultMap="UserResultMap">
    select * from user
    where user_name = #{userName} and dept_id = #{deptId}
</select>

<select id="getOrder" parameterType="int" resultType="com.jourwon.pojo.Order">
      select order_id id, order_no orderno ,order_price price form orders where order_id=#{id};
</select>

<select id="getOrder" parameterType="int" resultMap="orderResultMap">
select * from orders where order_id=#{id}
</select>
   
<resultMap type="com.jourwon.pojo.Order" id="orderResultMap">
  <!–用id属性来映射主键字段–>
  <id property="id" column="order_id">
  <!–用result属性来映射非主键字段，property为实体类属性名，column为数据库表中的属性–>
<result property ="orderno" column ="order_no"/>
<result property="price" column="order_price" />
</reslutMap>

<select id="listUserLikeUsername" resultType="com.jourwon.pojo.User">
  <bind name="pattern" value="'%' + username + '%'" />
  select id,sex,age,username,password from person where username LIKE #{pattern}
</select>

public class Order {
  private Integer orderId;
  private String orderDesc;
​
  /**
    * 支付对象
    */
  private Pay pay;
  //……
}

<!-- 订单resultMap -->
<resultMap id="peopleResultMap" type="cn.fighter3.entity.Order">
  <id property="orderId" column="order_id" />
  <result property="orderDesc" column="order_desc"/>
  <!--一对一结果映射-->
  <association property="pay" javaType="cn.fighter3.entity.Pay">
      <id column="payId" property="pay_id"/>
      <result column="account" property="account"/>
  </association>
</resultMap>

<select id="getTeacher" resultMap="getTeacherMap" parameterType="int">
      select * from order o
        left join pay p on o.order_id=p.order_id
      where o.order_id=#{orderId}
  </select>

<!-- 关联查询分类和产品表 -->
      <select id="listCategory" resultMap="categoryBean">
          select c.*, p.* from category_ c left join product_ p on c.id = p.cid
      </select>  

public class Category {
  private int categoryId;
  private String categoryName;
 
  /**
  * 商品列表
  **/
  List<Product> products;
  //……
}

<resultMap type="Category" id="categoryBean">
          <id column="categoryId" property="category_id" />
          <result column="categoryName" property="category_name" />
   
          <!-- 一对多的关系 -->
          <!-- property: 指的是集合属性的值, ofType：指的是集合中元素的类型 -->
          <collection property="products" ofType="Product">
              <id column="product_id" property="productId" />
              <result column="productName" property="productName" />
              <result column="price" property="price" />
          </collection>
      </resultMap>

<insert id="insert" useGeneratedKeys="true" keyProperty="userId" >
  insert into user(
  user_name, user_password, create_time)
  values(#{userName}, #{userPassword} , #{createTime, jdbcType= TIMESTAMP})
</insert>

mapper.insert(user);
user.getId;

<select id="findActiveBlogWithTitleLike"
    resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
  AND title like #{title}
</if>
</select>

<select id="findActiveBlogLike"
    resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
  <when test="title != null">
    AND title like #{title}
  </when>
  <when test="author != null and author.name != null">
    AND author_name like #{author.name}
  </when>
  <otherwise>
    AND featured = 1
  </otherwise>
</choose>
</select>

<select id="findActiveBlogLike"
    resultType="Blog">
SELECT * FROM BLOG
<where>
  <if test="state != null">
        state = #{state}
  </if>
  <if test="title != null">
      AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
      AND author_name like #{author.name}
  </if>
</where>
</select>

<update id="updateAuthorIfNecessary">
update Author
  <set>
    <if test="username != null">username=#{username},</if>
    <if test="password != null">password=#{password},</if>
    <if test="email != null">email=#{email},</if>
    <if test="bio != null">bio=#{bio}</if>
  </set>
where id=#{id}
</update>

<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
<where>
  <foreach item="item" index="index" collection="list"
      open="ID in (" separator="," close=")" nullable="true">
        #{item}
  </foreach>
</where>
</select>

<!-- MySQL下批量保存，可以foreach遍历 mysql支持values(),(),()语法 --> //推荐使用
<insert id="addEmpsBatch">
    INSERT INTO emp(ename,gender,email,did)
    VALUES
    <foreach collection="emps" item="emp" separator=",">
        (#{emp.eName},#{emp.gender},#{emp.email},#{emp.dept.id})
    </foreach>
</insert>

<!-- 这种方式需要数据库连接属性allowMutiQueries=true的支持
 如jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true -->  
<insert id="addEmpsBatch">
    <foreach collection="emps" item="emp" separator=";">                                 
        INSERT INTO emp(ename,gender,email,did)
        VALUES(#{emp.eName},#{emp.gender},#{emp.email},#{emp.dept.id})
    </foreach>
</insert>

//批量保存方法测试
@Test  
public void testBatch() throws IOException{
  SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
  //可以执行批量操作的sqlSession
  SqlSession openSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
​
  //批量保存执行前时间
  long start = System.currentTimeMillis();
  try {
      EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
      for (int i = 0; i < 1000; i++) {
          mapper.addEmp(new Employee(UUID.randomUUID().toString().substring(0, 5), "b", "1"));
      }
​
      openSession.commit();
      long end = System.currentTimeMillis();
      //批量保存执行后的时间
      System.out.println("执行时长" + (end - start));
      //批量 预编译sql一次==》设置参数==》10000次==》执行1次   677
      //非批量 （预编译=设置参数=执行 ）==》10000次   1121
​
  } finally {
      openSession.close();
  }
}

public interface EmployeeMapper {  
  //批量保存员工
  Long addEmp(Employee employee);
}

<mapper namespace="com.jourwon.mapper.EmployeeMapper"
    <!--批量保存员工 -->
  <insert id="addEmp">
      insert into employee(lastName,email,gender)
      values(#{lastName},#{email},#{gender})
  </insert>
</mapper>

public SqlSessionFactory build(Reader reader, String environment, Properties properties) {
      SqlSessionFactory var5;
      //省略异常处理
          //xml配置构建器
          XMLConfigBuilder parser = new XMLConfigBuilder(reader, environment, properties);
          //通过转化的Configuration构建SqlSessionFactory
          var5 = this.build(parser.parse());
}

 public SqlSessionFactory build(Configuration config) {
      return new DefaultSqlSessionFactory(config);
  }

Environment environment = this.configuration.getEnvironment();
          TransactionFactory transactionFactory = this.getTransactionFactoryFromEnvironment(environment);
          tx = transactionFactory.newTransaction(environment.getDataSource(), level, autoCommit);
          //通过Configuration创建executor
          Executor executor = this.configuration.newExecutor(tx, execType);
          var8 = new DefaultSqlSession(this.configuration, executor, autoCommit);

public <E> List<E> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
      Statement stmt = null;
​
      List var9;
      try {
          Configuration configuration = ms.getConfiguration();
          StatementHandler handler = configuration.newStatementHandler(this.wrapper, ms, parameter, rowBounds, resultHandler, boundSql);
          stmt = this.prepareStatement(handler, ms.getStatementLog());
          var9 = handler.query(stmt, resultHandler);
      } finally {
          this.closeStatement(stmt);
      }
​
      return var9;
  }

public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
      PreparedStatement ps = (PreparedStatement)statement;
      ps.execute();
      return this.resultSetHandler.handleResultSets(ps);
  }

 public void parameterize(Statement statement) throws SQLException {
      this.parameterHandler.setParameters((PreparedStatement)statement);
  }

public interface ParameterHandler {
  Object getParameterObject();
​
  void setParameters(PreparedStatement var1) throws SQLException;
}

public interface ResultSetHandler {
  <E> List<E> handleResultSets(Statement var1) throws SQLException;
​
  <E> Cursor<E> handleCursorResultSets(Statement var1) throws SQLException;
​
  void handleOutputParameters(CallableStatement var1) throws SQLException;
}

BlogMapper mapper = session.getMapper(BlogMapper.class);

    public <T> T getMapper(Class<T> type, SqlSession sqlSession) {
        MapperProxyFactory<T> mapperProxyFactory = (MapperProxyFactory)this.knownMappers.get(type);
        if (mapperProxyFactory == null) {
            throw new BindingException("Type " + type + " is not known to the MapperRegistry.");
        } else {
            try {
                return mapperProxyFactory.newInstance(sqlSession);
            } catch (Exception var5) {
                throw new BindingException("Error getting mapper instance. Cause: " + var5, var5);
            }
        }
    }

public class MapperProxyFactory<T> {
    private final Class<T> mapperInterface;
    ……
    protected T newInstance(MapperProxy<T> mapperProxy) {
        return Proxy.newProxyInstance(this.mapperInterface.getClassLoader(), new Class[]{this.mapperInterface}, mapperProxy);
    }

    public T newInstance(SqlSession sqlSession) {
        MapperProxy<T> mapperProxy = new MapperProxy(sqlSession, this.mapperInterface, this.methodCache);
        return this.newInstance(mapperProxy);
    }
}

    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        try {
            return Object.class.equals(method.getDeclaringClass()) ? method.invoke(this, args) : this.cachedInvoker(method).invoke(proxy, method, args, this.sqlSession);
        } catch (Throwable var5) {
            throw ExceptionUtil.unwrapThrowable(var5);
        }
    }

 public Object execute(SqlSession sqlSession, Object[] args) {
        Object result;
        Object param;
        ……
        case SELECT:
            if (this.method.returnsVoid() && this.method.hasResultHandler()) {
                this.executeWithResultHandler(sqlSession, args);
                result = null;
            } else if (this.method.returnsMany()) {
                result = this.executeForMany(sqlSession, args);
            } else if (this.method.returnsMap()) {
                result = this.executeForMap(sqlSession, args);
            } else if (this.method.returnsCursor()) {
                result = this.executeForCursor(sqlSession, args);
            } else {
                param = this.method.convertArgsToSqlCommandParam(args);
                result = sqlSession.selectOne(this.command.getName(), param);
                if (this.method.returnsOptional() && (result == null || !this.method.getReturnType().equals(result.getClass()))) {
                    result = Optional.ofNullable(result);
                }
            }
            break;
           ……
    }

public class MyInterceptor implements Interceptor {
  Properties props=null;
​
  @Override
  public Object intercept(Invocation invocation) throws Throwable {
      System.out.println("before……");
      //如果当前代理的是一个非代理对象，那么就会调用真实拦截对象的方法
      // 如果不是它就会调用下个插件代理对象的invoke方法
      Object obj=invocation.proceed();
      System.out.println("after……");
      return obj;
  }
}

@Intercepts({@Signature(
      type = Executor.class, //确定要拦截的对象
      method = "update",       //确定要拦截的方法
      args = {MappedStatement.class,Object.class}   //拦截方法的参数
)})
public class MyInterceptor implements Interceptor {
  Properties props=null;
​
  @Override
  public Object intercept(Invocation invocation) throws Throwable {
      System.out.println("before……");
      //如果当前代理的是一个非代理对象，那么就会调用真实拦截对象的方法
      // 如果不是它就会调用下个插件代理对象的invoke方法
      Object obj=invocation.proceed();
      System.out.println("after……");
      return obj;
  }
}

<plugins>
    <plugin interceptor="xxx.MyPlugin">
       <property name="dbType",value="mysql"/>
    </plugin>
</plugins>