本文详细介绍了MyBatis的基本使用方法，包括映射关系、参数传递、返回类型、SQL打印等，并针对常见问题提供了解决方案。
Mybatis manual
映射关系
不指定resultMap的默认映射 与SQL 查询出的字段名一致
<select id="queryById" parameterType="long" resultMap="dictionary">
select * from sys_dictionary WHERE id = #{id}
</select>
当不指定 resultMap="dictionary"时，默认的设施是按照SQL语句的字段名来match。
如果数据库的字段名和 Class Dictionary里面的命名不同，则要指定resultMap=""， 否则查询出来的内容为空
public Dictionary queryById(long id);
<id column="id" property="id" javaType="Long"/>
<result column="sys_code" property="code" javaType="String"/>
<result column="sys_value" property="value" javaType="String"/>
<result column="sys_name_english" property="nameEnglish" javaType="String"/>
<result column="sys_name_chinese_cn" property="nameChineseCn" javaType="String"/>
<result column="sys_name_chinese_tra" property="nameChineseTra" javaType="String"/>
<result column="sys_order" property="tabOrder" javaType="int"/>
<result column="sys_desc" property="tabDesc" javaType="String"/>
association one to one
column -> java variable
<select id="queryAll" parameterType="String" resultMap="Master">
SELECT DISTINCT m.*,c.*
FROM
master AS m
LEFT JOIN com AS c ON M.compid = c.comp_id
ORDER BY
M.co
</select>
<resultMap id="Master" type="com.book.Master">
<association property="company" javaType="com.book.Company">
<!-- primary keys 2-->
<id column="id" property="companyId" javaType="int"/>
<result column="ab" property="Abbr" javaType="String"/>
<result column="ame" property="ame" javaType="String"/>
</association>
</resultMap>
column -> java obj.variable
适用于 Java 对象中引用主表的对象，可以通过对象引用的方式如下
public class SalesOrderLine extends EntityVO{
public SalesOrderItem soItem;
SELECT * FROM (
SELECT
sd.*,
sh.*,
<result column="sh_bill_address" property="soItem.billAddress" javaType="String"/>
select 单独查询
<association property="soItem"
javaType="com.book.erp.entity.postgresql.so.SalesOrderItem"
select="queryById"
column="{sohNo=sd_soh_no}"
fetchType="eager"/>
可以选择
fetchType="lazy"
<select id="queryById" resultMap="SoItem">
select * from so_hdr WHERE sh_soh_no = #{sohNo}
</select>
子表单独 Select
<association property="custHold" select="queryCustHold" column="crh_cust_no"></association>
<select id="queryCustHold" parameterType="java.util.List" resultMap="CustHold">
SELECT * FROM a
</select>
通过 resultMap 映射
这样方式不需要通过Select 查询， 通过第一次查询的结果自动映射，最好使用这样方式。
- 降低数据库的压力
- 可以根据需要主语句的变化，决定是否需要子查询。
<association property="soItem" column="sh_soh_no" resultMap="SoItem"></association>
column="sh_soh_no" 是主键
SELECT
sd.*,
sh.*,
LEFT JOIN so_hdr AS sh ON sd.sd_soh_no = sh.sh_soh_no
collection one to many
方法1
<select id="queryJob" resultMap="Master">
SELECT
t1.*,ct.*
FROM
(
SELECT
t.*,g.*
FROM
biz_task AS t,
biz_group AS g,
biz_task_group AS tg
WHERE
t.task_id = tg.task_id
) AS t1
LEFT JOIN group AS cg ON t1.gr_id = cg.gr_id
LEFT JOIN templ AS ct ON ct.id= cg.c_id
</select>
<resultMap id="Job" type="com.book.Job">
<collection property="templs" ofType="com.book.Templ">
<id column="id" property="id" javaType="Long"/>
<result column="code" property="Code" javaType="String"/>
<result column="name" property="Name" javaType="String"/>
</collection>
</resultMap>
方法2
此方法分块，清晰。但是坏处是不能把按照子表的条件进行查询，从页面只能获得主表的条件，然后根据主表查询结果再查询子表，
所以子表没有办法直接查询。
关键是 Select = “”
<resultMap id="teItem" type="oteItem">
<collection property = "cnDList" javaType="java.util.List" ofType="" select="queryDetail" column="cn_no">
</collection>
</resultMap>
<resultMap id="CDetail" type="Cetail">
<id column="t_no" property="cNo" javaType="String"/>
</resultMap>
<select id="queryDetail" parameterType="String" resultMap="CDetail">
SELECT * FROM abcc where ab_id= #{cnNo}
</select>
Mybatis
List 和 String【】 的长度size 和 length
public List<String> getAllCustCodeByGroup(String[] custGroup);
public List<String> getAllCustCodeByGroup(List[] custGroup);
<if test="custGroup!=null and custGroup.length>0">
And cm_group IN
<foreach collection="custGroup" item="item" separator="," open="(" close=")" index="">
#{item,javaType=String}
</foreach>
</if>
<if test="custGroup!=null and custGroup.size>0">
PostgreSQL UpSert 插入覆蓋
特性又称UPSERT覆盖写，与MySQL的REPLACE INTO类似
批量插入更新
INSERT INTO user_permission ("user_id", "user_name", "modify_date","unit", "deleted")
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.userId},
#{item.userName},
#{item.modifyDate},
#{item.unit},
#{item.deleted}
)
</foreach>
on conflict (user_id) do update set
user_name = EXCLUDED.user_name,
unit = EXCLUDED.unit,
modify_date = EXCLUDED.modify_date,
deleted = EXCLUDED.deleted
ERROR: syntax error at or near “ON”
PostgreSQL 8.6.4 版本 不支持ON CONFLICT語法
<update id="addOrUpdateabc">
insert INTO abc
(<include refid="abc_column" />)
VALUES
(#{fcCurrCode},#{fcDescEn},#{fcDescTw},#{fcDescCn},#{fcExchgRate},#{fcReverse})
ON CONFLICT DO NOTHING;
</update>
insert INTO abc
(<include refid="abc_column" />)
VALUES
(#{fcCurrCode},#{fcDescEn},#{fcDescTw},#{fcDescCn},#{fcExchgRate},#{fcReverse})
ON CONFLICT (code) DO UPDATE SET
fc_desc_en = EXCLUDED.fc_desc_en,
fc_desc_tw = EXCLUDED.fc_desc_tw,
fc_desc_cn = EXCLUDED.fc_desc_cn,
fc_exchg_rate =EXCLUDED.fc_exchg_rate,
fc_reverse = EXCLUDED.fc_reverse;
語法
Date
MySQL的日期類型包括 DATETIME、TIMESTAMP、DATE、TIME、YEAR。
Java的java.util.Date 和 java.sql.Date兩種Date類型：
（1）ava.util.Date，包括Date + Time
（2） java.sql.Date，只包括Date
通常會使用ava.util.Date 傳給Mybatis，
Mybatis 只有TIMESTAMP 和 DATE兩種Date類型：
（1）jdbcType=DATE，包括Date
（2）jdbcType=TIMESTAMP，包括Date+Time
默認Mybatis會自動轉換成正確的類型
UPDATE sys_user SET last_login = #{loginTime,jdbcType=TIMESTAMP} WHERE user_id = #{id}
同一樣的效果
UPDATE sys_user SET last_logout = #{logoutTime} WHERE user_id = #{id}
都是Date + Time
如果jdbcType=Date只能存儲Date，Mybatis會自動省略Time。
UPDATE sys_user SET last_login = #{loginTime,jdbcType=Date} WHERE user_id = #{id}
boolean
boolean int long 处理方法相同，
MySQL没有Boolean, 使用tinyint 长度1 来代替， Mybatis自动把tinyint 转换成boolean。
此时 如果值为0，Mybatis会当成NULL 处理，所以需要null 判断。
public boolean cremStatus;
And crem_status = #{cremStatus} 同时也要判断 true时 执行条件 And crem_onhold_status = #{holdShip} ## CDATA DateDATE_FORMAT格式化
<![CDATA[DATE_FORMAT(exec_date,'%Y-%m-%d') == DATE_FORMAT(#{date},'%Y-%m-%d')]]
CDATA判断 符号
数字
And <![CDATA[ iff_qty <0]]>
日期
And <![CDATA[ DELIVERY_DATE <= #{deliveryDate,jdbcType=DATE}]]>
& &
< <
> >
" "
’ ’
小于等于 a<=b a <= b a <![CDATA[<= ]]>b
大于等于 a>=b a >= b a <![CDATA[>= ]]>b
不等于 a!=ba <![CDATA[ <> ]]>b a <![CDATA[!= ]]>b
upper case and lower case
And cm_no LIKE '%' || upper(#{codeName}) || '%'
NumberFormatException
字符串判斷時，需要注意類型轉換。
<!-- voided -->
<if test='queryGroup=="V"'>
AND sh_status = 'V'
</if>
<!-- approved and not approved -->
<if test='queryGroup == "AN"'>
AND sh_status != 'V'
AND sd_status != 'V'
</if>
mybiatis是基于OGNL语法，在解析单引号+单字符 ‘Y’ 的解析时会默认时char类型，
单引号+多字符 ‘AA’
双引号+单字符 “A” 解析为String类型。
1
2
3
ON DUPLICATE key update
儅存在主鍵ID 重複時，更改如下字段
es_id = VALUES(es_id),
es_email_subject = VALUES(es_email_subject),
es_email_content = VALUES(es_email_content),
es_email_date = VALUES(es_email_date)
In 在mybatis 的用法
<if test="cList!=null and cList.size>0">
And c_no in
<foreach collection="cList" item="item" separator="," open="(" close=")" index="">
#{item,javaType=String}
</foreach>
</if>
传递不同类型的參數
（1）如果传入的是单参数且参数类型是一个List的时候，collection属性值为list .
List resultTypeList(List codes);
select * from hplan_meeting_deploy where
meeting_code IN
<foreach item="item" collection="list" open="(" separator="," close=")" index="index">
#{item}
</foreach>
注意collection=“list” 固定。
（2）如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array .
（3）如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key.
<if test="beginTime!=null">
And <![CDATA[ sh_cpo_date >= #{beginTime,jdbcType=DATE}]]>
</if>
<if test="endTime!=null">
And <![CDATA[ sh_cpo_date <= #{endTime,jdbcType=DATE}]]>
</if>
<if test="status!=null">
And sh_status in
<foreach collection="status" item="item" separator="," open="(" close=")" index="">
#{item,javaType=String}
</foreach>
</if>
javaType=String 沒有引號
(4) 如果傳入的參數是Date，以及其它參數混合在一起
<update id="updateLoginTime" parameterType="java.util.Map">
UPDATE sys_user SET last_login = #{loginTime,jdbcType=DATE} WHERE user_id = #{id}
</update>
返回类型
没有映射resultMap，可以定义resultType="java.util.Map"，以map的方式获得结果
<select id="getOSSO" resultType="java.util.Map">
class java.util.Date cannot be cast to class java.lang.String
@DateTimeFormat(pattern="yyyy-MM-dd") private Date beginTime;
@DateTimeFormat(pattern="yyyy-MM-dd") private Date endTime;
OR
private Date beginTime;
private Date endTime;
<select id="queryByCondition" parameterType="String" resultMap="StockAging">
<select id="queryByCondition" parameterType="java.util.List" resultMap="StockAging">
PostgreSQL Date 類型
And <![CDATA[ gnh_gr_date >= #{beginTime,jdbcType=DATE}]]>
And <![CDATA[ gnh_gr_date <= #{endTime,jdbcType=DATE}]]>
OR
AND cnh_credit_date between DATE(#{beginTime}) AND DATE(#{endTime})
MySQL 和 PostgreSQL LIKE 語句寫法不同
Cause: org.postgresql.util.PSQLException: ERROR: function concat(character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
PostgreSQL
<!-- And cm_man LIKE '%' || #{man} || '%' -->
<!-- And UPPER(cm_man) LIKE UPPER('%' || #{man} || '%') -->
MYSQL
And CONCAT(cm_man) LIKE CONCAT('%', #{man}, '%');
打印SQL
mybatis:
configuration:
map-underscore-to-camel-case: true
# print sql on the console
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
或者
需要驗證
mybatis:
configuration:
map-underscore-to-camel-case: true
log-impl: com.ylx.apis_plugin_supervise.config.mybatis.log.MybatisLogCustom
問題
Mapper 的参数必须与xml对应，否则xml里面字段会找不到
org.apache.ibatis.binding.BindingException: Parameter ‘emailAddress’ not found. Available parameters are [code, email, param1, param2]
public EmailCode queryItem(String email,String code);
Mapper 会传参数给Mybatis的配置文件xml，如果参数名称不对，就不能传递数据过去。
## near '' ON DUPLICATE key update 最后一个自动不能有”，“ 否则会报错。Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 13
ON DUPLICATE key update
code = VALUES(code),
email_address = VALUES(email_address),
create_time = VALUES(create_time)
ON DUPLICATE key update
code = VALUES(code),
email_address = VALUES(email_address),
create_time = VALUES(create_time),
bind 将导致where的判断错误
<!--
if so, it will make the where judge is not correct
<bind name="userName" value="'%'+_parameter.userName+'%'"/>
<bind name="userFullName" value="'%'+_parameter.userFullName+'%'"/>
<bind name="userTelephone" value="'%'+_parameter.userTelephone+'%'"/>
<bind name="userEmail" value="'%'+_parameter.userEmail+'%'"/>
-->
select <include refid="Column_short" /> from user
<where>
<if test="userName!=null and userName.trim()!=''">
And user_name like '%#{userName}%'
</if>
<if test="userFullName!=null and userFullName.trim()!=''">
AND user_full_name like '%#{userFullName}%'
</if>
<if test='userTelephone!=null and userTelephone.trim()!=""'>
AND user_telephone like '%#{userTelephone}%'
</if>
<if test="userEmail!=null and userEmail.trim()!=''">
AND user_email like '%#{userEmail}%'
</if>
</where>
导致的结果，会少查询一条数据
select * from mybook.user where id not in (
SELECT id FROM mybook.user where user_name like '%test%' AND user_full_name like '%%' AND user_telephone like '%%' AND user_email like '%%'
) and id in (
SELECT id FROM mybook.user where user_name like '%test%'
)
No setter found for the keyProperty ‘menu_id’
<insert id="insertMenu"
useGeneratedKeys="true"
keyProperty="menu_id"
parameterType="com.book.erp.entity.menu.Menu">
数据库定义了自动增长的ID
<insert id="insertMenu"
useGeneratedKeys="true"
parameterType="com.book.erp.entity.menu.Menu">
数据库没有定义自动增长的ID
<insert id="registerReader"
parameterType="com.book.erp.entity.menu.Menu"
useGeneratedKeys=true
keyProperty="menuId"
keyColumn="menu_id">
mybatis 与 postgresql的
扫一扫
1642
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
