mybatis一对多两种mapper写法实例
mybatis一对多两种mapper写法
第一种
<resultMap type="com.example.demo.model.TuserModel" id="extendMapper"> <id column="id" property="id" /> <result column="user_name" property="userName" /> <result column="nick_name" property="nickName" /> <result column="avatar" property="avatar" /> <result column="email" property="email" /> <result column="signature" property="signature" /> <result column="create_time" property="createTime" /> <result column="update_time" property="updateTime" /> <result column="del_flag" property="delFlag" /> <collection property="tpluginModels" ofType="com.example.demo.model.TpluginModel" column="id"> <id column="pid" property="id" /> <result column="user_id" property="userId" /> <result column="name" property="name" /> <result column="icon" property="icon" /> <result column="vsersion" property="vsersion" /> <result column="tags" property="tags" /> <result column="description" property="description" /> <result column="bcreate_time" property="createTime" /> <result column="bupdate_time" property="updateTime" /> <result column="del_flag" property="delFlag" /> </collection> </resultMap>
sql语句用联表查询
u.*,p.id as pid,p.user_id,p.name,p.icon,p.vsersion,p.tags,p.description,p.create_time as bcreate_time,p.update_time as bupdate_time,p.del_flag from t_user u LEFT JOIN t_plugin p ON u.id=p.user_id and u.del_flag=0 and p.del_flag=0 WHERE u.user_name LIKE CONCAT('%',#{name},'%') OR u.nick_name LIKE CONCAT('%',#{name},'%')
第二种
<resultMap type="com.example.demo.model.TuserModel" id="extendMapper"> <id column="id" property="id" /> <result column="user_name" property="userName" /> <result column="nick_name" property="nickName" /> <result column="avatar" property="avatar" /> <result column="email" property="email" /> <result column="signature" property="signature" /> <result column="create_time" property="createTime" /> <result column="update_time" property="updateTime" /> <result column="del_flag" property="delFlag" /> <collection property="tpluginModels" column="id" ofType="com.example.demo.model.TpluginModel" select="pluginByUid" /> //column='id' 为关联查询所需条件 </resultMap>
sql语句使用两个sql语句返回结果
<select id="selectTuserBynameOrNick" resultMap="extendMapper"> SELECT * FROM t_user WHERE del_flag = 0 AND ( user_name LIKE CONCAT( '%', #{name},'%') OR nick_name LIKE CONCAT( '%', #{name},'%')) </select> //下个sql语句依赖上个 <select id="pluginByUid" resultType="com.example.demo.model.TpluginModel"> SELECT id,user_id as userId,name,icon,vsersion,tags,description, create_time as createTime ,update_time as updateTime ,del_flag as delFlag FROM t_plugin WHERE del_flag = 0 AND user_id = #{id} </select>
补充知识:Mybatis 一个dao 对应多个Mapper.xml
由于项目中的mybatis的mapper是用mybatis generator自动生成的,但是生成的mapper满足不了我的业务,需要自己扩展,所以就研究了下、
添加接口
创建mapper.xml
修改配置
1.添加接口
在原dao中加个接口
/** ---------------自定义Mapper--------------- **/
List<PcacheCluster> select(ClusterInstanceBO clusterInstanceBO);
2. 创建mapper.xml
PcacheClusterMapperExtend.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.oppo.paas.pcache.manager.mapper.PcacheTemplateMapper"> <select id="select" parameterType="com.oppo.paas.pcache.manager.entity.PcacheTemplate" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from t_pcache_template <where> <if test="templateId != null and templateId != ''"> and template_id = #{templateId} </if> <if test="templateName != null and templateName != ''"> and template_name = #{templateName} </if> <if test="templateType != null and templateType != ''"> and template_type = #{templateType} </if> <if test="createUser != null and createUser != ''"> and create_user = #{createUser} </if> <if test="createTime != null "> and create_time = #{createTime,jdbcType=TIMESTAMP} </if> </where> order by create_time desc </select> </mapper>
3. 修改配置
项目目录:
添加mapper扫描路径
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <!-- 自动扫描mapping.xml文件 --> <property name="mapperLocations" > <array> <value>classpath:mybatis/mappers/*Mapper.xml</value> <!-- 扩展mapper.xml --> <value>classpath:mybatis/mappers/extend/*MapperExtend.xml</value> </array> </property> <property name="configLocation" value="classpath:mybatis/mybatis-config.xml"></property> <property name="plugins"> <array> <bean class="com.github.pagehelper.PageInterceptor"> <!-- 这里的几个配置主要演示如何使用,如果不理解,一定要去掉下面的配置 --> <property name="properties"> <value> helperDialect=mysql reasonable=true supportMethodsArguments=true params=count=countSql autoRuntimeDialect=true </value> </property> </bean> </array> </property> </bean>
mybatis generator 已经过时了哦,太麻烦,耦合性高,建议使用通用Mapper,完美继承spring,springboot
学习地址:https://gitee.com/free/Mapper/wikis/Home
以上这篇mybatis一对多两种mapper写法实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们。
赞 (0)