1、@Select 定制SQL语句,不光where参数,连表名、SELECT字段名都可以传进来
@Select("SELECT sum(num) as total, avg(num) as average FROM ${tableName} WHERE statis_date between #{beginDate} and #{endDate}")
public SumAverage averageDayNum(TableParams params);
2、对于java基础类型或Map,parameterType可以简写。
<select id="getTeamSizes" parameterType="map" resultType="com.**.model.NameNumber">
<select id="findUserCorpId" resultType="java.util.Map"> 返回Map的key分别为 corpId、parentId
SELECT corp_id as corpId, parent_id as parentId
FROM organ_department dept inner join organ_dept_staff df on dept.id=df.dept_id
inner join organ_staff_info stf on stf.id=df.staff_id
WHERE user_id=(SELECT id FROM admin_user WHERE username=#{arg0})
</select>
甚至,mybatis的参数类型可以完全不声明
<insert id="batchInsert">
INSERT INTO temp_kakou_passing(kakou_code,lane_code,direction,pass_time,speed,license_no)
VALUES
<foreach collection="collection" item="item" separator=",">
(#{item.kakouCode},#{item.laneCode},#{item.direction},#{item.passTime},#{item.speed},#{item.licenseNo})
</foreach>
</insert>
3、如果参数是一个或多个java基础类型,则SQL语句里面的参数名可以是 “arg0”、"arg1"....这样;
<delete id="deleteTempData" parameterType="java.lang.Integer">
DELETE FROM temp_kakou_passing
WHERE pass_time < date_add(now(), interval #{arg0} day)
</delete>
4、如果想使用参数名,则在Mapper方法里面使用 @Param注解
List<Dictionary> dictionaryPage(@Param("dictionary") Dictionary dictionary, @Param("page")Pagination page);
5、IN操作,如果没有指定参数名的前提下,默认为 "collection"或 "list"或 "array"
<foreach collection="collection" item="item" separator=",">
(#{item.kakouCode},#{item.laneCode},#{item.direction},#{item.passTime},#{item.licenseNo})
</foreach>
6、插入时返回主键
<insert id="insertDuty" keyProperty="id">
INSERT INTO duty_plan (duty_date, duty_phase, intro, duty_type, assigner, scenic_code, corp_code)
VALUES (#{dutyDate}, #{dutyPhase}, #{intro}, #{dutyType}, #{assigner}, #{scenicCode}, #{corpCode})
</insert>
对于oracle,可以在<insert >里面增加子元素:
<selectKey keyProperty="id" resultType="int" order="BEFORE">
查询语句
</selectKey>
7、resultMap中jdbcType要注意:
long->BIGINT, java.util.Date -> TIMESTAMP(而不是DATETIME)
8、在一个statement中可以写多条sql预计,分号隔开
<insert id="updateStatisAnnualCheck">
delete from statis_annual_check where corp_code=#{newCode};
insert into statis_annual_check(corp_code, statis_date,hour,scenic_name,park_name,ticket_model,num,total_price)
select #{newCode}, statis_date,hour,scenic_name,park_name,ticket_model,num,total_price
from statis_annual_check
where statis_date >= date_add(curdate(), interval -92 day)
and scenic_code=#{oldCode} and deleted='F'
</insert>
url: jdbc:mysql://10.20.23.136:3306/db_name?useUnicode=true&characterEncoding=UTF8&useSSL=false&allowMultiQueries=true