文章目录
主配置文件及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": []
}]
}]