1:namespace 用于定位sql语句查找范围
<mapper namespace="com.souban.office.dal.building.BuildingDao">
2:resultMap 用于将查询的数据库字段对应到查询的实体中,一般只有能用查询才需要定义
<resultMap id="RM-SOUBAN-OFFICE-Building" type="com.souban.office.domain.building.BuildingDo">
<id column="id" property="id" jdbcType="INTEGER"/> <!-- jdbcType可以省略,建议省略 -->
<result column="name" property="name" jdbcType="VARCHAR"/>
</resultMap>
3:sql用于经常用的查询语句统一定义
<sql id="SOUBAN-OFFICE-Building-Base-Column-List">
id, name, date_create, date_update
from building
</sql>
引用如下
select
<include refid="SOUBAN-OFFICE-Building-Base-Column-List"/>
where id = #{id}
4:<select> 查询语句标签(,update, delete插入语句标签, update与delete默认会返回条件中符合条件的数量)
<select id="queryBuildingById" resultMap="RM-SOUBAN-OFFICE-Building">
select
<include refid="SOUBAN-OFFICE-Building-Base-Column-List"/>
where id = #{id}
</select>
5:insert
<!-- 在返回的实体中,将id注入到实体的id中, 其中useGeneratedKeys,keyProperty可以省略 -->
<insert id="insertFavorite" type="com.souban.office.domain.FavoriteDo" useGeneratedKeys="true"keyProperty="id">
insert into favorite (saleman_id,name,date_create) values (#{salemanId}, #{name}, now())
<selectKeyresultType="long" order="AFTER" keyProperty="id">
SELECT LAST_INSERT_ID() as returnedId
</selectKey>
</insert>
如果要将save方法与update方法合成一个方法
REPLACE INTO building(id, name) values (#{id}, #{name})
6:if标签
字符串判空,<if test="tableName!=null and tableName!='' ">
insert into
<if test="tableName=='building_image'"> building_image (building_id, url, master, date_create)</if>
<if test="tableName=='room_image'">room_image (room_id, url, master, date_create)</if>
<if test="tableName=='incubator_image'">incubator_image (incubator_id, url, master, date_create)</if>
values
(#{pojoId}, #{imageUrl}, #{master}, now())
7:如果有些语句,不需要做xml验证,如有>或<的语句可用下面的标签包起来
<![CDATA[
where temp.theory_pay_time >= #{startDate} and temp.theory_pay_time <= #{endDate}
group by DATE_FORMAT(temp.theory_pay_time,"%Y-%m")
order by temp.theory_pay_time
]]>
8:select,insert,update传参
传空参,parameterMap, parameterType
传空参:如果在dao(mapper)方法中标名传参,那么可以不设置传参
AppLaunchImageDo queryAppLaunchImageByDevice(@Param(value="type")Integertype,
@Param(value="width")Integerwidth,@Param(value="height")Integerheight);
那么可以在select,update,delete语句中直接使用#{type},#{width},#{height};
parameterType:标明传参使用的数据类型
<selectid="findById"resultType="Building"parameterType="java.lang.Integer">
parameterMap很少使用
9:select返回传
resultType, resultMap
resultType:查询结果与实体名的字段完全一致时使用
如果在配置中定义了实体扫描的包名(typeAliasesPackage
),则可直接写实体类名。
如果在配置中指定了遇到下划线,则自动转为驼峰结构(mapUnderscoreToCamelCase
),则可不定义resultMap
resultMap:查询结果是数据库字段,属性值为<resultMap>字义的值。
10:如果查询结果对应的实体类中有list结构
例如:
public class BuildingMultiDo {
private Integerid;
private LocationDolocation;
}
public class LocationDo {
private Stringaddress;
private IdNameDoarea;
private IdNameDoblock;
}
public class IdNameDo {
private Integerid;
private Stringname;
}
<select id="queryBuildingSummaryActive" resultMap="buildingListQueryResultMap">
select buildingId, address, blockId, blockName, areaId,areaName from building ..........
</select>
<resultMap id="buildingListQueryResultMap" type="BuildingMultiDo">
<result property="id" column="buildingId"/>
<association property="location" javaType="LocationDo">
<result property="address"column="address"/>
<association property="area" javaType="IdNameDo">
<id property="id" column="areaId"/>
<result property="name" column="areaName"/>
</association>
<association property="block" javaType="IdNameDo">
<id property="id" column="blockId"/>
<result property="name" column="blockName"/>
</association>
</association>
</resultMap>
11:$与#的区别
#将传入的数据都当成一个字符串,会对自动传入的数据加一个双引号
$将传入的数据直接显示生成在sql中
如:String str = "123,456,789"
where id in (#{str}) 等价于 where id in ("123,456,789")
where id in (${str}) 等价于 where id in (123,456,789)
12:on后面跟and与where后面跟and的区别
select * from contract c left join contract_room cr on c.id = cr.`contract_id` and cr.`room_id` is not null;
select * from contract c left join contract_room cr on c.id = cr.`contract_id` where cr.`room_id` is not null;
第一个是联合查询的room_id为空是对contract_room中room_id的限定,结构依然会将有contract,但没有contract_room的数据查询出来,所以查询出来的依然会有room_id为空的数据。
第二个联合查询是结最终的结构集的限定,所以查询出来的数据room_id均不会为空
13:查询trade时,同时order的数量
select *, (select count(*) from order o where o.trade_id = t.id) as orderCount
from trade t;
14:mysql查询结果如果为空,则设置为0;
ifnull(trade.num,0) 或者 if(trade.num is null, 0, trade.num)
15:mybatis中的like方法与字条串拼接,
building_name like concat("%",#{buildingName},"%")
concat(building_name, building.address, building.area_name) like concat("%",#{buildingName},"%")
16:在select中嵌套查询
select cp.id cpid,c.id, ifnull(cp.pay_money, 0) pay_money,
ifnull((select price from contract_increase ci where ci.contract_id = c.id and ci.year_queue = (select floor(datediff(cp.theory_pay_time, c.rent_start_date)/365)+1 from dual)), c.price) price
from contract_pay cp
left join contract c on cp.contract_id = c.id
合同在不同年份有不同的价格(价格年递增率),根据时间距离开始时间来确定显示租凭价格。