青岛私人做网站,凡科网站制作,荣盛科技网站建设,代理 网站前置审批核心配置
JavaBeanMapper.xml#xff08;sql映射#xff09;
作用
JavaBeanMapper.xml实现#xff1a; 用来干什么#xff1f; 定义Sql语句映射。相对照JDBC的实现#xff0c;是将原本的Sql代码提取出来#xff0c;最终根据映射关系执行Sql操作。 好处#xff1f; 解…核心配置
JavaBeanMapper.xmlsql映射
作用
JavaBeanMapper.xml实现 用来干什么 定义Sql语句映射。相对照JDBC的实现是将原本的Sql代码提取出来最终根据映射关系执行Sql操作。 好处 解耦mapper只关心定义Sql的映射关系与java代码分离更易维护。 如何使用 先来展示一个基本的mapper xml这里涉及到主要的几个标签元素 SelectInsertUpdateDeleteResultMapSqlCache
?xml version1.0 encodingUTF-8?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceorg.example.daos.UserMapperresultMap iduserMap typeCustomerresult columnpwd propertypassword//resultMapselect idgetUserList resultMapuserMapSELECT * FROM mybatis.user/selectselect idgetUserListByRowBounds resultMapuserMapSELECT * FROM mybatis.user/select!-- 模糊查询1--!--select idgetUserListForFuzzyQuery resultTypeorg.example.pojo.UserSELECT * FROM mybatis.user where name like #{name}/select--!-- 模糊查询2 %--select idgetUserListForFuzzyQuery resultTypeorg.example.pojo.UserSELECT * FROM mybatis.user where name like %#{name}/select!-- 形参只有一个且为基本类型时parameterType可省略parameterTypeint --select idgetUserById resultTypeorg.example.pojo.UserSELECT * FROM mybatis.user where id #{id}/selectinsert idaddUser parameterTypeorg.example.pojo.UserINSERT INTO mybatis.user(id, name, pwd) values (#{id},#{name},#{pwd})/insertupdate idupdateUserByUser parameterTypeorg.example.pojo.UserUPDATE mybatis.user set name#{name}, pwd#{pwd} where id#{id}/updateupdate idupdateUserByMap parameterTypemapUPDATE mybatis.user set name#{userName}, pwd#{userPwd} where id#{userId}/updatedelete iddeleteUser parameterTypeintDELETE FROM mybatis.user where id#{id}/delete
/mapper
具体的标签元素 Select 这里的重点是resultTyperesultMap的使用两者只能二选一 ResultType语句中返回结果的类全限定名或别名。一般是该sql映射方法的返回值类型。特殊的如果是集合类型则只需定义集合的泛型类型即可。ResultMap对外部 resultMap 的命名引用。一般用于处理复杂的映射结果查询比如多表查询一对多多对一
多对一查询
?xml version1.0 encodingUTF-8?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceorg.example.daos.StudentMapper!-- 多对一查询方式1Teacher再查一次:子查询相当于:select id, name, tid from student where tid (select id from teacher where id tid)--!--resultMap idStudentTeacher1 typeStudentid propertyid columnid/result columnname propertyname/association propertyteacher columntid selectgetTeacherById javaTypeTeacher//resultMapselect idgetStudentList resultMapStudentTeacher1select * from student/selectselect idgetTeacherById resultTypeTeacherselect * from teacher where id#{tid}/select--!-- 多对一查询方式2按照结果嵌套, 联表查询相当于select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher t where s.tid t.id--select idgetStudentList resultMapStudentTeacher2select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher t where s.tid t.id/selectresultMap idStudentTeacher2 typeStudentresult propertyid columnsid/result propertyname columnsname/association propertyteacher javaTypeTeacherid columnid propertytid/result propertyname columntname//association/resultMap/mapper一对多查询
?xml version1.0 encodingUTF-8?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceorg.example.daos.TeacherMapper!--一对多方式1联表查询 --select idgetTeacherById resultMapTeacherStudentselect t.id tid, t.name tname, s.id sid, s.name sname from teacher t, student s where t.id s.tid and t.id#{tid}/selectresultMap idTeacherStudent typeTeacherresult propertyid columntid/result propertyname columntname/collection propertystudentList ofTypeStudentresult propertyid columnsid/result propertyname columnsname/result propertytid columntid//collection/resultMap!--一对多方式2子查询 --select idgetTeacherById2 resultMapTeacherStudent2select * from teacher where id#{tid}/selectresultMap idTeacherStudent2 typeTeacherresult columnid propertyid/collection propertystudentList columnid ofTypeStudent selectgetStudentByTid//resultMapselect idgetStudentByTid resultTypeStudentselect * from student where tid#{tid}/select
/mapperInsert涉及到自动生成主键id的设置(keyProperty, useGeneratedKeys)多行插入(foreach)UpdateDelete
!-- 自动生成主键id --
insert idinsertAuthor useGeneratedKeystruekeyPropertyidinsert into Author (username,password,email,bio)values (#{username},#{password},#{email},#{bio})
/insert!-- 多行插入 --
insert idinsertAuthor useGeneratedKeystruekeyPropertyidinsert into Author (username, password, email, bio) valuesforeach itemitem collectionlist separator,(#{item.username}, #{item.password}, #{item.email}, #{item.bio})/foreach
/insertupdate idupdateAuthorupdate Author setusername #{username},password #{password},email #{email},bio #{bio}where id #{id}
/updatedelete iddeleteAuthordelete from Author where id #{id}
/deleteSqlsql语句重用片段。也可动态赋值
sql idif_title_authorif testtitle! nullAND title #{title}/ifif testauthor ! nullAND author #{author}/if
/sqlselect idqueryBlog1 parameterTypemapselect * from blog where 11include refidif_title_author/
/select!-- 动态赋值: ${include_target}, property --
sql idsomeincludefrominclude refid${include_target}/
/sql
select idselect resultTypemapselectfield1, field2, field3include refidsomeincludeproperty nameprefix valueSome//include
/select参数的定义 如果一个列允许使用 null 值并且会使用值为 null 的参数就必须要指定 JDBC 类型jdbcType
#{average,javaTypedouble,jdbcTypeNUMERIC,typeHandlerMyTypeHandler,numericScale2}字符串替换: ${}方式不会被预编译转义可以通过这种方式指定某个字符串column而非对应的数值。但存在sql注入风险。
Select(select * from user where ${column} #{value})
User findByColumn(Param(column) String column, Param(value) String value);association collection collection 用于一对多association用于多对一。
association 联表查询
association propertyauthor columnblog_author_id javaTypeAuthorid propertyid columnauthor_id/result propertyusername columnauthor_username/
/associationassociation 子表查询
resultMap idblogResult typeBlogassociation propertyauthor columnauthor_id javaTypeAuthor selectselectAuthor/
/resultMapcollection子表查询
collection propertyposts columnid ofTypePost selectselectPostsForBlog/collection联表查询
resultMap idblogResult typeBlogid propertyid columnblog_id /result propertytitle columnblog_title/collection propertyposts ofTypePostid propertyid columnpost_id/result propertysubject columnpost_subject/result propertybody columnpost_body//collection
/resultMapOfType
collection propertyposts javaTypeArrayList columnid ofTypePost selectselectPostsForBlog/可以读作 “posts 是一个存储 Post 的 ArrayList 集合” 。且在一般情况下MyBatis 可以推断 javaType 属性因此并不需要填写。 缓存Cache 一级缓存默认开启。SqlSession级别的缓存也叫本地缓存二级缓存基于namespace级别的缓存针对mapper cache, LRU, FIFO开启二级缓存需要在对于mapper上加入标签元素Cache即可当会话sqlSession提交commit或关闭close时一级缓存的数据才会提交到二级缓存中缓存顺序当查询业务来到DAO层时 先查看二级缓存再查看一级缓存最后再查数据库 动态Sql解决在定义Sql映射时拼接sql语句where子句条件SET子句多条语句foreach的编写。参考链接 If, choose, foreach, trim 分页limit Select * from user limit startIndex, pageSizeRowBoundsselectList (String statement, Object parameter, RowBounds rowBounds)Mybatis PageHelper 注解开发 参考链接
