---
title: MyBatis
date: 2016-10-18 21:41:21
update: 2016-10-18 21:41:21
categories: MyBatis
tags: [mybatis, mysql]
---
### 判断字段值是否存在并录入数据sql
```xml
INSERT INTO
tp_package_info (
f_station_id,
f_type,
f_buy_count,
f_package_count,
f_cur_count,
f_first_month_count,
f_sec_month_count,
f_third_month_count,
f_first_month_surplus,
f_sec_month_surplus,
f_is_first_accrued,
f_is_sec_accrued,
f_every_month,
f_package_create_time
) SELECT
#{userId},#{combo},0,50000,5000,5000,3000,3000,0,0,0,0,0,#{currentTime}
#{userId},#{combo},0,50000,5000,5000,3000,3000,0,0,0,0,0,#{currentTime}
#{userId},#{combo},0,0,0,0,0,0,0,0,0,0,0,#{currentTime}
FROM
DUAL
WHERE
NOT EXISTS (
SELECT
*
FROM
tp_package_info st
WHERE
st.f_station_id = #{userId})
```
### sql索引
创建唯一索引时，可以使用REPLACE INTO 进行修改或新增数据
13if判断不能加引号
```xml
SELECT
OR t.f_center_id = #{fUserId}
OR EXISTS (
SELECT
f_manager_id
FROM
txm_user_detail d
WHERE
d.f_user_id = #{fUserId}
AND d.f_manager_id = t.f_center_id)
AND t.f_publishstate = #{fPublishState}
```
### mybatis分页sql注意数据类型
一定要BIGINT类型，传进的map中pageStart和pageSize必须将其设置为int
```xml
LIMIT
#{pageStart,jdbcType=BIGINT},#{pageSize,jdbcType=BIGINT}
```
### mybatis日期转换
```xml
f_parcel_id, f_exp_id, f_branch_id,
f_station_id, f_mail_no, f_company_id, f_company_name, f_recipient_name,
f_recipient_mobile, f_frame_code, f_take_code, f_num, f_input_way,
f_input_source, date_format(f_arrive_time,'%Y-%c-%d %h:%i:%s') AS
'f_arrive_time' , f_parcel_status, f_is_detention, f_out_way, f_out_source,
f_app_notice_status, f_weixin_notice_status, f_sms_notice_status,
f_out_time, f_last_time, f_type, f_comment
```
### spring+mybatis
配置参考：https://my.oschina.net/wangt10/blog/508344
### mybatis插入返回逐渐id
```java
public interface PushMapper {
void insertReId(Record record);
}
```
```xml
id, content, sys_id, status, create_time
insert into p_push_record (content,sysId,createTime)
VALUES (#{content},#{sysId},#{createTime});
```
### 报错记录
```
org.apache.ibatis.builder.IncompleteElementException: Could not find result map java.util.LinkedHashMap
```
所有的xml文件中的方法里存在没有和DAO接口对应的返回类型。
DAO接口返回类型为map而xml中方法的返回类型为对象(resultType)，应该为resultMap，否则就会报错
请求参数必须是bean对象，插入成功后，该数据的逐渐id已经写入到请求对象(com.baomidou.springwind.entity.push.Record)中。
### Mybatis批量更新出现的问题
```xml
update tp_user u set u.f_station_name = #{item.fStationName} where u.f_user_id = #{item.fUserId}
```
组织sql语句，在赋值时报错，sql语句错误
```xml
### SQL: update tp_user SET f_station_name = ? WHERE f_user_id = ? ; update tp_user SET f_station_name = ? WHERE f_user_id = ? ; update tp_user SET f_station_name = ? WHERE f_user_id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Erreur de syntaxe près de 'update tp_user
SET f_station_name = 'nanjing'
WHERE f_use' à la ligne 5
```
原因：
配置的 MySQL jdbc 链接字符串 默认不支持一次性执行多个sql 语句，
我们可以在配置文件中链接数据库的url添加一个参数allowMultiQueries=true，代表支持执行多个条sql，问题解决。
jdbc.url=jdbc:mysql://192.168.1.2:3/test4?characterEncoding=utf-8&allowMultiQueries=true
### mybatis一对多映射
Mall实体
```java
import lombok.Data;
import javax.persistence.Table;
import javax.persistence.Transient;
@Data
@Table(name = "t_mall")
public class Mall {
@Id
@Column(name = "Id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
@ApiModelProperty(value = "id", example = "0")
private Integer id;
private String mallName;
private String mallCode;
}
```
MallCity实体
```java
import lombok.Data;
import java.util.List;
@Data
public class MallCity extends BaseEntity {
private int cityCode;
private String city;
private List malls;
private int sort;
}
```
mapper接口
```java
import com.red.star.macalline.data.core.base.MyMapper;
import com.red.star.macalline.data.entity.Fans;
import com.red.star.macalline.data.entity.Mall;
import com.red.star.macalline.data.entity.MallCity;
import com.red.star.macalline.data.entity.MallExpand;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface MallMapper extends MyMapper {
List findCityContaiMall();
}
```
```xml
```
### mybatis插入时有则更新无则新增
```xml
INSERT INTO `student`(`name`, `age`) VALUES('Jack', 19)
ON DUPLICATE KEY
UPDATE `age`=19;
```
当唯一字段name=Jack已经存在则执行UPDATE `age`=19;
当唯一字段name=Jack没有时，则新增
### 树结构数据
菜单实体
```java
import lombok.Data;
import javax.persistence.*;
import java.util.Date;
import java.util.Set;
/**
* 资源
*
* @author liucancan
* @date 2018/12/26
*/
@Data
@Table(name = "tb_p_resources")
public class AccountResource {
/**
* 主键id
*/
@Id
@Column(name = "Id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
/**
* 资源地址
*/
private String url;
/**
* 父级资源id
*/
private Integer pid;
/**
* 排序
*/
private Integer sort;
/**
* 资源名称
*/
private String name;
/**
* 图标
*/
private String iconUrl;
/**
* 资源树
*/
@Transient
private Set childResource;
@Transient
private String roleIds;
}
```
mapper接口
```java
/**
* @Date 16:13 2019/6/26
* @Description 根据角色ids查询该角色拥有的资源, 返回树结构
* @Return
*/
Set selectResourceTreeByParam(@Param("roleIds") String roleIds);
```
mapper.xml
```xml
```
数据结构
```
"resources": [
{
"title": "微商户",
"icon": "fa fa-lg fa-fw PCico02 PCico",
"items": [
{
"icon": "fa fa-cube common_opacity PCico",
"title": "页面管理",
"route": "/mpsaas-web-management/testView/microPage",
"subPage": [
"/mpsaas-web-management/testView/microPageEdit"
]
}
]
},
{
"title": "商品管理",
"icon": "fa fa-lg fa-fw PCico03 PCico",
"items": [
{
"icon": "fa fa-group common_opacity PCico",
"title": "商品分组",
"route": "/mpsaas-web-management/testView/merchandise/group",
"subPage": [
"/mpsaas-web-management/testView/merchandise/group/groupAdd",
"/mpsaas-web-management/testView/merchandise/group/groupEdit"
]
}
]
}
],
```
* 需要注意的是` #{item} `遍历roleIds，否则会`报不能存在Integer类型的roleIds的get和set方法`，所以应该避免这样的遍历。
MyBatis
Copyright: 采用 知识共享署名4.0 国际许可协议进行许可
