Mybatis学习常见bug及其解决办法-sql映射篇4

bug1:

org.apache.ibatis.builder.IncompleteElementException: Could not find result map 'edu.xlh.dao.UserMapper.BaseResultMap' referenced from 'edu.xlh.dao.UserMapper.getUserList'

cause1:

没有定义resultMap

<select id="queryUserList" resultMap="BaseResultMap">
        select id , sid as ssid , sname as realName , password as passwd , superuser as suser , state as st from student2
    </select>

work1:

<resultMap id="BaseResultMap" type="edu.xlh.entity.User">
        <id property="id" column="id" />
        <result property="sid" column="ssid" />
        <result property="sname" column="realName" />
        <result property="password" column="passwd" />
        <result property="superuser" column="suser" />
        <result property="state" column="st" />
    </resultMap>
     
    <select id="queryUserList" resultMap="BaseResultMap">
        select id , sid as ssid , sname as realName , password as passwd , superuser as suser , state as st from student2
    </select>

bug2:

Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error building SqlSession.
### The error may exist in SQL Mapper Configuration
### Cause: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: java.lang.IllegalArgumentException: No enum constant org.apache.ibatis.session.AutoMappingBehavior.None

cause2:

    <settings>
        <setting name="autoMappingBehavior" value="None"/> <!--NONE写成了None -->
    </settings>

work2:

![<settings>
        <setting name="autoMappingBehavior" value="NONE"/>
    </settings>](https://img-blog.csdnimg.cn/20201125102849227.png#pic_center)

拓展2：

name="autoMappingBehavior" value="NONE"的作用是关闭自动映射
接口类：

public interface UserMapper {
    List<User> queryUserList();
}

映射文件

    <resultMap id="BaseResultMap" type="edu.xlh.entity.User">
        <id property="id" column="id" /> <!-- 不用写完字段，默认自动映射 -->
    </resultMap>
    <select id="queryUserList" resultMap="BaseResultMap">
       select * from student2

测试类:

    @Test
    public void test08() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();


        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);



        List<User> userList = userMapper.queryUserList();
//执行查询（命名空间+.+标签ID名称）
//            List<User> userList = sqlSession.selectList("test.findUserListBySid" , "01");
//循环遍历
        for (User user : userList) {
            System.out.println(user);

        }
    }

没关闭自动映射之前：

关闭自动映射后：

官网解释：
官网相关链接

bug3:

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'd.order_detail_id' in 'field list'
### The error may exist in mapper/OrdersMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select o.*, d.order_detail_id, d.orders_id, d.items_id, d.items_num from orders o inner join orderdetail d on o.id = d.orders_id
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'd.order_detail_id' in 'field list'

cause3:

 <resultMap id="BaseResultMap" type="edu.xlh.entity.Orders">
        <id property="id" column="id" />
        <result property="user_id" column="user_id" />
        <result property="number" column="number" />
        <result property="createTime" column="createTime" />
        <result property="note" column="note" />
        <result property="username" column="username" />
    </resultMap>

<!-- 一对多 -->
    <resultMap id="orderAndDetailResultMap" type="edu.xlh.entity.Orders" extends="BaseResultMap">
        <collection property="orderDetailList" ofType="edu.xlh.entity.OrderDetail">
            <id property="id" column="order_detail_id" />
            <result property="ordersId" column="orders_id" />
            <result property="itemsId" column="items_id" />
            <result property="itemdNum" column="items_num" />
        </collection>
    </resultMap>

<select id="findOrderListWithDetail" resultMap="orderAndDetailResultMap">
select
o.*,
d.order_detail_id, //主键不能直接使用d.order_detail_id
d.orders_id,
d.items_id,
d.items_num
from
orders o
inner join orderdetail d on o.id = d.orders_id
</select>

测试类：

@Test
    public void test03(){
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        List<Orders> ordersList = ordersMapper.findOrderListWithDetail();
        for (Orders orders : ordersList) {
            System.out.println("订单号 ："+orders.getId()+" "+"订单编号 ："+orders.getNumber());
            for (OrderDetail orderDetail : orders.getOrderDetailList()) {
                System.out.println(orderDetail);
            }
        }

        MyBatisUtil.closeSqlSession(sqlSession);
    }

work3:

<select id="findOrderListWithDetail" resultMap="orderAndDetailResultMap">
select
o.*,
d.id as order_detail_id,//改为d.id as order_detail_id
d.orders_id,
d.items_id,
d.items_num
from
orders o
inner join orderdetail d on o.id = d.orders_id
</select>

bug4:

订单号 ：1 订单编号 ：1
OrderDetail{id=1, ordersId=null, itemsId=null, itemdNum=null}
订单号 ：2 订单编号 ：2
OrderDetail{id=2, ordersId=null, itemsId=null, itemdNum=null}
OrderDetail{id=3, ordersId=null, itemsId=null, itemdNum=null}
订单号 ：3 订单编号 ：3
OrderDetail{id=4, ordersId=null, itemsId=null, itemdNum=null}
OrderDetail{id=5, ordersId=null, itemsId=null, itemdNum=null}
OrderDetail{id=6, ordersId=null, itemsId=null, itemdNum=null}
订单号 ：4 订单编号 ：1
OrderDetail{id=7, ordersId=null, itemsId=null, itemdNum=null}
订单号 ：5 订单编号 ：2
OrderDetail{id=8, ordersId=null, itemsId=null, itemdNum=null}
OrderDetail{id=9, ordersId=null, itemsId=null, itemdNum=null}
订单号 ：6 订单编号 ：1
OrderDetail{id=10, ordersId=null, itemsId=null, itemdNum=null}

cause4:

 <resultMap id="BaseResultMap" type="edu.xlh.entity.Orders">
        <id property="id" column="id" />
        <result property="user_id" column="user_id" />
        <result property="number" column="number" />
        <result property="createTime" column="createTime" />
        <result property="note" column="note" />
        <result property="username" column="username" />
    </resultMap>

<!-- 一对多 -->
    <resultMap id="orderAndDetailResultMap" type="edu.xlh.entity.Orders" extends="BaseResultMap">
        <collection property="orderDetailList" ofType="edu.xlh.entity.OrderDetail">
            <id property="id" column="order_detail_id" />
            <!-- 别名与sql语句中别名不对应-->
            <result property="ordersId" column="orders_id1" />
            <result property="itemsId" column="items_id1" />
            <result property="itemdNum" column="items_num1" />
        </collection>
    </resultMap>

<select id="findOrderListWithDetail" resultMap="orderAndDetailResultMap">
select
o.*,
d.id as order_detail_id,
d.orders_id,
d.items_id,
d.items_num
from
orders o
inner join orderdetail d on o.id = d.orders_id
</select>

work4:

<!-- 一对多 -->
    <resultMap id="orderAndDetailResultMap" type="edu.xlh.entity.Orders" extends="BaseResultMap">
        <collection property="orderDetailList" ofType="edu.xlh.entity.OrderDetail">
            <id property="id" column="order_detail_id" />
            <!-- 将别名改为与sql语句中使用的别名一致即可-->
            <result property="ordersId" column="orders_id" />
            <result property="itemsId" column="items_id" />
            <result property="itemdNum" column="items_num" />
        </collection>
    </resultMap>

总结4：

很多时候不报错，字段值却为空，往往都是别名的问题，定义的映射中的别名与sql语句中的不一致等。