Mybatis使用

主配置文件及mapper映射文件

（1）mybatis主配置文件模板去哪找，复制过来的约束文件发红？

解决办法：

模板：

<?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>
    <!--设置sql日志 打印在控制台-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <!--配置别名-->
    <typeAliases>
        <!--此包下的实体类名===别名-->
        <package name="com.ydx.entity"/>
    </typeAliases>
    <!--分页插件-->
          <!--插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
    </plugins>
    <!--数据源 -->
        <environments default="mydb">
            <environment id="mydb">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                      <!--载入驱动等信息-->
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/db3"/>
                    <property name="username" value="root"/>
                    <property name="password" value="123456"/>
                </dataSource>
            </environment>
        </environments>
    <!--管理映射源文件-->
        <mappers>
            <mapper resource="DaoMapper/UserDaoMapper.xml"/>
        </mappers>

    </configuration>

（2）mapper映射文件

为什么叫映射，就是能完全一对一的对应起来；
就是一个接口对应一个mapper；所以一给xml文件只能放一组mapper

方法名字===========》Mapper的namespace
方法返回类型=========》resultMap
方法参数类型===========》parameterType，一般自动推断，不用写
单个对象
①如果是对象，直接将属性字段一一对应#{}依次
②如果是Map，和以上相同

如果是多个对象和参数
要在接口使用@parameter表示对应的关系，
很少使用标号来映射；

传入的参数是容器类型？

 <!--动态Sql: foreach标签, 批量插入-->
        <insert id="dynamicSqlInsertList" useGeneratedKeys="true" keyProperty="id">
            insert into users (name, age, county, date)
            values
            <foreach collection="list" item="user" separator="," >
                (#{user.name}, #{user.age}, #{user.county}, #{user.date})
            </foreach>
        </insert>

返回值的分析

resultMap和resultType
（1）如果我们要求的返回对象要求是基本类型的，那就很简单；+
（2）如过我们要求的返回对象是内嵌引用类型的，那怎么办啊？
使用assolation，关联；

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.mapper.SendResumeMapper">
	<!--投递简历Vo-->
	<resultMap id="resultMap" type="com.xxx.entity.SendResumeVo">
		<id column="id" property="id"/>
		<result column="company_id" property="companyId"/>
		<result column="recruit_id" property="recruitId"/>
		<result column="user_id" property="userId"/>
		<result column="resume_id" property="resumeId"/>
		<result column="status" property="status"/>
		<result column="create_date" property="createDate"/>
		<!--association column的id是对应查询片段中的变量，recruit_id是当前resultMap中查询的列名-->
		<association property="recruitVo" select="com.xxx.mapper.RecruitMapper.selectOneVo" column="{id=recruit_id}" />
		<association property="resumeVo" select="com.xxx.mapper.ResumeMapper.selectOneVo" column="{id=resume_id}" />
	</resultMap>
原文链接：https://blog.csdn.net/lzxomg/article/details/104497127

（3）如果返回对象又内嵌的引用类型，而且还是个容器?

(4)如果返回对象内嵌引用类型，引用类型又嵌套…或者容器
这就引出了递归查询！

resultMap重点介绍
resultMap其实就是对应返回的对象的，但是可能名字不匹配，也可能查出的字段个数和需求的对象模型不匹配，这个时候resultMap就发挥作用了

标签

一样的标签一样要支持增删改查；
#和$ 前者是占位符，后者直接一起编译禁区，可能会注入；
resultMap 用于装载返回数据

动态sql

为什么需要动态sql？
因为前端传来的值是多变的，不能写死写法，比如六个参数，允许空
不可能写六个sql来面对这种情况把…所以有了动态sql
http://t.csdn.cn/IqhKT

配置过程中产生的一些问题

Client does not support authentication protocol requested by server; consider upgrading MySQL client

use mysql;
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
flush privileges;

Cause: java.sql.SQLException: Unknown initial character set index '255' received from server. Initial client character set can be forced via the 'characterEncoding' property.
### Cause: java.sql.SQLException: Unknown initial character set index '255' received from server. Initial client character set can be forced via the 'characterEncoding' property.

mysql-connector 版本和mysql不同，修改pom一致即可；

 Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'username' in 'class useExamp.enity.User'

类里成员变量名为:我把映射类成员字段写为userName,对应数据为驼峰原则写为user_name，所以找不到；把userName改为username即可，或者将数据库字段名字改为user_name；

DAO代理

XXDao dao=session.getMapper（XXDao.class）；
不再每次区实现dao接口才能使用dao；

传统dao的使用

调用session.

public List<Student> selectStudents() {
		SqlSession session = MyBatisUtil.getSqlSession();
		List<Student> studentList = session.selectList(
				"com.bjpowernode.dao.StudentDao.selectStudents");
		session.close();
		return studentList;
}

为什么要使用动态sql？

难题

一对一，一对多查询

<mapper namespace="com.lcb.mapping.userMapper">  
    <!--association  一对一关联查询 -->  
    <select id="getClass" parameterType="int" resultMap="ClassesResultMap">  
        select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}  
    </select>  
 
    <resultMap type="com.lcb.user.Classes" id="ClassesResultMap">  
        <!-- 实体类的字段名和数据表的字段名映射 -->  
        <id property="id" column="c_id"/>  
        <result property="name" column="c_name"/>  
        <association property="teacher" javaType="com.lcb.user.Teacher">  
            <id property="id" column="t_id"/>  
            <result property="name" column="t_name"/>  
        </association>  
    </resultMap>  
 
 
    <!--collection  一对多关联查询 -->  
    <select id="getClass2" parameterType="int" resultMap="ClassesResultMap2">  
        select * from class c,teacher t,student s where c.teacher_id=t.t_id and c.c_id=s.class_id and c.c_id=#{id}  
    </select>  
 
    <resultMap type="com.lcb.user.Classes" id="ClassesResultMap2">  
        <id property="id" column="c_id"/>  
        <result property="name" column="c_name"/>  
        <association property="teacher" javaType="com.lcb.user.Teacher">  
            <id property="id" column="t_id"/>  
            <result property="name" column="t_name"/>  
        </association>  
 
        <collection property="student" ofType="com.lcb.user.Student">  
            <id property="id" column="s_id"/>  
            <result property="name" column="s_name"/>  
        </collection>  
    </resultMap>  
</mapper> 

递归查询

模型

http://t.csdn.cn/7uJJo

<resultMap id="BaseTreeResultMap" type="com.tl.sesa.server.model.LineLoss.LineLoss">
        <result column="TENANT_ID" property="tenantId"/>
        <result column="CL_ID" property="clId"/>
        <result column="CL_P_ID" property="clpId"/>
        <result column="EP_TIZONE" property="epTizone"/>
        <result column="DEP_TYPE" property="depType"/>
        <result column="START_TIME" property="startTime"/>
        <collection column="CL_ID" property="children" javaType="java.util.ArrayList"
                    ofType="com.tl.sesa.server.model.LineLoss.LineLoss" select="getNextNodeTree"/>
    </resultMap>


<resultMap id="NextTreeResultMap" type="com.tl.sesa.server.model.LineLoss.LineLoss">
    <result column="TENANT_ID" property="tenantId"/>
    <result column="CL_ID" property="clId"/>
    <result column="CL_P_ID" property="clpId"/>
    <result column="EP_TIZONE" property="epTizone"/>
    <result column="DEP_TYPE" property="depType"/>
    <result column="START_TIME" property="startTime"/>
    <collection column="CL_ID" property="children" javaType="java.util.ArrayList"
                ofType="com.tl.sesa.server.model.LineLoss.LineLoss" select="getNextNodeTree"/>
</resultMap>

<sql id="Base_Column_List">
    TENANT_ID,
    CL_ID,
    CL_P_ID,
    EP_TIZONE,
    DEP_TYPE,
    START_TIME
</sql>

<select id="getNextNodeTree" resultMap="NextTreeResultMap">
    SELECT
    <include refid="Base_Column_List"/>
    FROM c_line_loss_info
    WHERE CL_P_ID = #{CL_ID}
</select>

<select id="getNodeTree" resultMap="BaseTreeResultMap">
    SELECT
    <include refid="Base_Column_List"/>
    FROM c_line_loss_info
    WHERE CL_P_ID = #{clpId}
</select>

#为什么两个一模一样的map要取不同的id名呢？
我偏向于认为递归查询是一种特殊情况，一个特殊的内嵌对象；
事实证明，没有必要用两个resultMap和两个方法

<mapper namespace="com.proivider.mapper.PlacesMapper">

    <resultMap id="resultMap" type="com.proivider.entity.placeVo.PlaceVo">
        <result property="id" column="id"/>
        <result column="pid" property="pid"/>
        <result column="distance" property="distance"/>
        <result column="size" property="size"/>
        <result column="people_num" property="peopleNum"/>
        <collection column="id" property="placesList" javaType="java.util.ArrayList" ofType="com.proivider.entity.Places" select="getTree"/>
    </resultMap>

<!--    <select id="getNextTreeNode" resultMap="resultMap">-->
<!--        select  * from places where pid=#{id}-->
<!--    </select>-->

    <select id="getTree" resultMap="resultMap">
        select * from places where pid=#{id}
    </select>

</mapper>

使用json显示返回的复合结构的对象

校验

https://www.sojson.com/

解析

https://www.sojson.com/simple_json.html

 List<PlaceVo> list = placesMapper.getTree("昭通市");
        ObjectMapper objectMapper=new ObjectMapper();
        try {
            String json=objectMapper.writeValueAsString(list);
            System.out.println(json);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }

以上的结果：

[{"id":"微信县","pid":"昭通市","distance":0,"size":"20","peopleNum":30,"placesList":[]},{"id":"盐津县","pid":"昭通市","distance":0,"size":"10","peopleNum":100,"placesList":[{"id":"串丝","pid":"盐津县","distance":110,"size":"1","peopleNum":20,"placesList":[]},{"id":"普洱镇","pid":"盐津县","distance":100,"size":"1","peopleNum":30,"placesList":[]}]},{"id":"绥江县","pid":"昭通市","distance":0,"size":"20","peopleNum":30,"placesList":[]},{"id":"镇雄县","pid":"昭通市","distance":0,"size":"10","peopleNum":300,"placesList":[{"id":"簸箕","pid":"镇雄县","distance":200,"size":"2","peopleNum":50,"placesList":[]},{"id":"罗坎","pid":"镇雄县","distance":113,"size":"5","peopleNum":40,"placesList":[]}]}]

在线工具解析后：

[{
	"id": "微信县",
	"pid": "昭通市",
	"distance": 0,
	"size": "20",
	"peopleNum": 30,
	"placesList": []
}, {
	"id": "盐津县",
	"pid": "昭通市",
	"distance": 0,
	"size": "10",
	"peopleNum": 100,
	"placesList": [{
		"id": "串丝",
		"pid": "盐津县",
		"distance": 110,
		"size": "1",
		"peopleNum": 20,
		"placesList": []
	}, {
		"id": "普洱镇",
		"pid": "盐津县",
		"distance": 100,
		"size": "1",
		"peopleNum": 30,
		"placesList": []
	}]
}, {
	"id": "绥江县",
	"pid": "昭通市",
	"distance": 0,
	"size": "20",
	"peopleNum": 30,
	"placesList": []
}, {
	"id": "镇雄县",
	"pid": "昭通市",
	"distance": 0,
	"size": "10",
	"peopleNum": 300,
	"placesList": [{
		"id": "簸箕",
		"pid": "镇雄县",
		"distance": 200,
		"size": "2",
		"peopleNum": 50,
		"placesList": []
	}, {
		"id": "罗坎",
		"pid": "镇雄县",
		"distance": 113,
		"size": "5",
		"peopleNum": 40,
		"placesList": []
	}]
}]