做排行的网站,长春网架公司,python怎么读,wordpress 主题开发文章目录 常用MyBatis参数映射单个参数多个参数使用索引【不推荐】Param注解Map传参POJO【推荐】List数组 动态标签\if标签\trim标签\where标签\set标签\foreach标签 MyBatis查询一对一一对多 常用MyBatis参数映射
单个参数
XML中可… 文章目录 常用MyBatis参数映射单个参数多个参数使用索引【不推荐】Param注解Map传参POJO【推荐】List数组 动态标签\if标签\trim标签\where标签\set标签\foreach标签 MyBatis查询一对一一对多 常用MyBatis参数映射
单个参数
XML中可以通过 #{xxx}, #{param1} 来获取Mapper接口中的单个参数没有任何限制即使前后名字不一致的同时也没有Param注解别名也能传入给XML
但为了开发规范尽量使用和入参时一样使用Param注解约定好名称
// Mapper
User selectUserById(Long id);// XML
select idselectUserById parameterTypelong resultMapBaseResultMapSELECT * FROM user WHERE id #{123}
/select多个参数
使用索引【不推荐】
// Mapper
User selectUserByIdAndName(Long id, String username);// XML
select idselectUserByIdAndName resultMapBaseResultMapSELECT * FROM user WHERE id #{param1} AND username #{param2}
/selectParam注解
// Mapper
User selectUserByIdAndName(Param(value id) Long id, Param(value username) String username);// XML
select idselectUserByIdAndName resultMapBaseResultMapSELECT * FROM user WHERE id #{id} AND username #{username}
/selectMap传参
Mybatis底层也是通过 Map 传参因此传入也可以一个 Map 作为参数。Map 中的 key 就对应 XML 中 #{key}
单Map参数
// Mapper
User selectUserByIdAndNameMap(MapString, Object map);// XML
select idselectUserByIdAndNameMap parameterTypemap resultMapBaseResultMapSELECT * FROM user WHERE id #{id} AND username #{username}
/select简单类型map
// Mapper
User selectUserByIdAndNameMap(Param(value id) Long id, MapString, Object map);// XML
select idselectUserByIdAndNameMap resultMapBaseResultMapSELECT * FROM user WHERE id #{id} AND username #{map.username}
/select简单类型map
// Mapper
User selectUserByIdAndNameMap(Param(value id) Long id, Param(value map) MapString, Object map);// XML
select idselectUserByIdAndNameMap resultMapBaseResultMapSELECT * FROM user WHERE id #{id} AND username #{map.username}
/select测试
MapString, Object mapnew HashMap();
map.put(id, 1);
map.put(username, Jack);
// 单 map 参数
User user userMapper.selectUserByIdAndNameMap(map);
// 简单参数map
User user userMapper.selectUserByIdAndNameMap(2L, map);POJO【推荐】
多个参数可以使用实体类封装key就是属性名实体类需要有 getXXX()方法
不使用Param注解
// Mapper
User selectUserByEntity(User user);// XML
select idselectUserByEntity parameterTypeapp.domain.po.User resultMapBaseResultMapSELECT * FROM user WHERE id #{id} AND username #{username}
/select使用Para注解
// Mapper
User selectUserByEntity(Param(value user) User user);// XML
select idselectUserByEntity parameterTypeapp.domain.po.User resultMapBaseResultMapSELECT * FROM user WHERE id #{user.id} AND username #{user.username}
/select测试
User usernew User();
user.setId(1L);
user.setUsername(Jack);
User entity userMapper.selectUserByEntity(user);List
// Mapper
ListUser selectList(Param(value ids) ListLong ids);// XML
select idselectList parameterTypelist resultMapBaseResultMapSELECT * FROM user WHERE id INforeach collectionids itemitem separator, open( close)#{item}/foreach
/select测试
ListLong ids List.of(1L, 2L, 3L);
ListUser userList userMapper.selectList(ids);数组
// Mapper
ListUser selectArray(Param(value ids) Long[] ids);// XML
select idselectArray parameterTypearraylist resultMapBaseResultMapSELECT * FROM user WHERE id INforeach collectionids itemitem separator, open( close)#{item}/foreach
/select测试
Long[] idsArray ids.toArray(new Long[0]);
userList userMapper.selectArray(idsArray);动态标签
if标签
if标签中的test是传入对象的属性
mapper
int addUser(Param(value userInfo) UserInfo userInfo);xml
insert idaddUser useGeneratedKeystrue parameterTypeapp.model.UserInfo keyColumnid keyPropertyidINSERT INTO userinfo(username,passwordif testuserInfo.photo ! null,photo/if)VALUES(#{userInfo.name},#{userInfo.password}if testuserInfo.photo ! null,#{userInfo.photo}/if)
/inserttrim标签
prefix表示整个语句块以prefix的值作为前 缀suffix表示整个语句块以suffix的值作为后缀prefixOverrides表示整个语句块要去除掉的前 缀suffixOverrides表示整个语句块要去除掉的后缀
mapper
int addUser(Param(value userInfo) UserInfo userInfo);xml
insert idaddUser useGeneratedKeystrue parameterTypeapp.model.UserInfo keyColumnid keyPropertyidINSERT INTO userinfotrim prefix( suffix) suffixOverrides,username,password,if testuserInfo.photo ! nullphoto,/if/trimVALUEStrim prefix( suffix) suffixOverrides,#{userInfo.name},#{userInfo.password},if testuserInfo.photo ! null#{userInfo.photo}/if/trim
/insertwhere标签
where标签会自动清除掉第一个if标签中第一个多余的 and字符串因此也可以用 trim prefixwhere preffixOverridesand/trim 替换掉where标签。但一般不这么用
ListUserInfo selectUserByCondition(Param(value userInfo) UserInfo userInfo);xml
select idselectUserByCondition parameterTypeapp.model.UserInfo resultMapBaseMapSELECT *FROM userinfowhereif testuserInfo.name ! nulland username #{userInfo.name}/ifif testuserInfo.photo ! nulland photo #{userInfo.photo}/ifif testuserInfo.createTime ! nulland DATE_FORMAT(createtime, %Y-%m-%d %H:%i:%s) DATE_FORMAT(#{userInfo.createTime}, %Y-%m-%d %H:%i:%s)/ifif testuserInfo.updateTime ! nulland DATE_FORMAT(updatetime, %Y-%m-%d %H:%i:%s) DATE_FORMAT(#{userInfo.updateTime}, %Y-%m-%d %H:%i:%s)/ifif testuserInfo.state ! nulland state #{userInfo.state}/if/where
/selectset标签
set标签会自动去除掉最后一个SQL的逗号“,”。同理也可以是用 trime prefixset suffixOverridex,/trim 替换掉set标签。但一般不这么做
mapper
int updateUserByCondition(Param(userInfo) UserInfo userInfo);xml
update idupdateUserByConditionUPDATE userinfosetif testuserInfo.name ! nullusername #{userInfo.name},/ifif testuserInfo.photo ! nullphoto #{userInfo.photo},/if/setwhereif testuserInfo.id ! nulland id #{userInfo.id}/ifif testuserInfo.createTime ! nulland DATE_FORMAT(createtime, %Y-%m-%d %H:%i:%s) DATE_FORMAT(#{userInfo.createTime}, %Y-%m-%d %H:%i:%s)/if/where
/updateforeach标签
collection绑定方法参数中的集合的名称如 ListSetMap或数组对象「一般配合Param注解搭配名称使用」open语句块开头的字符串close语句块结束的字符串item遍历时的每一个对象separator每次遍历之间间隔的字符串
mapper
int deleteUserByIds(Param(idList) ListInteger idList);xml
delete iddeleteUserByIdsDELETEFROM userinfoWHERE id INforeach collectionidList open( close) itemid separator,#{id}/foreach
/deleteMyBatis查询
一对一
数据库
CREATE TABLE orders (id int NOT NULL AUTO_INCREMENT,amount decimal(11,2) DEFAULT NULL,uid int NOT NULL,PRIMARY KEY (id,uid) USING BTREE
)CREATE TABLE user (id int NOT NULL AUTO_INCREMENT,username varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,PRIMARY KEY (id)
)一个用户有多个订单【这里只查询一个订单一对多中会查询多个订单】一个订单只从属于一个用户
SELECT o.id o_id,o.amount,o.uid o_uid,u.id u_id,u.username
FROM orders oLEFT JOIN user u ON o.uid u.id;用户实体类
import lombok.Data;Data
public class User {private Long id;private String username;
}订单实体类
import lombok.Data;Data
public class Order {private Long id;private Double amount;private Long uid;// 一对一: 当前订单从属于哪个用户private User user;
}XML方案一通过构造新的实体类resultMap 完成数据封装返回
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceapp.mapper.OrderMapperresultMap idOrderResultMap typeapp.domain.po.Order!--id: 主键property: 实体类属性column: 查询出来的数据库字段--id propertyid columno_id/result propertyamount columnamount/result propertyuid columno_uid/!-- 一对一 --result propertyuser.id columnu_id/result propertyuser.username columnusername//resultMapselect idselectAllOrder resultMapOrderResultMapSELECT o.id o_id,o.amount,o.uid o_uid,u.id u_id,u.usernameFROM orders oLEFT JOIN user u ON o.uid u.id;/select
/mapperXML方案二使用 resultMap 中的 association 完成封装
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceapp.mapper.OrderMapperresultMap idOrderResultMap typeapp.domain.po.Orderid propertyid columno_id/result propertyamount columnamount/result propertyuid columno_uid/!-- 一对一 --association propertyuserid propertyid columnu_id/result propertyusername columnusername//association/resultMapselect idselectAllOrder resultMapOrderResultMapSELECT o.id o_id,o.amount,o.uid o_uid,u.id u_id,u.usernameFROM orders oLEFT JOIN user u ON o.uid u.id;/select
/mapperMapper接口
ListOrder selectAll();一对多
数据库
CREATE TABLE orders (id int NOT NULL AUTO_INCREMENT,amount decimal(11,2) DEFAULT NULL,uid int NOT NULL,PRIMARY KEY (id,uid) USING BTREE
)CREATE TABLE user (id int NOT NULL AUTO_INCREMENT,username varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL,PRIMARY KEY (id)
)一个用户有多个订单一个订单只从属于一个用户
SELECT u.id u_id,u.username,o.id o_id,o.amount,o.uid o_uid
FROM user uLEFT JOIN orders o ON u.id o.uid;用户实体类
import lombok.Data;import java.util.List;Data
public class User {private Long id;private String username;// 一对多: 当前用户有哪些订单private ListOrder orderList;
}订单实体类
import lombok.Data;Data
public class Order {private Long id;private Double amount;private Long uid;// 一对一: 当前订单从属于哪个用户private User user;
}XML
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceapp.mapper.UserMapperresultMap idUserResultMap typeapp.domain.po.Userid propertyid columnu_id/result propertyusername columnusername/!-- 一对多ofType: 指定集合中的数据类型--collection propertyorderList ofTypeapp.domain.po.Orderid propertyid columno_id/result propertyamount columnamount/result propertyuid columno_uid//collection/resultMapselect idselectAll resultMapUserResultMapSELECT u.id u_id,u.username,o.id o_id,o.amount,o.uid o_uidFROM user uLEFT JOIN orders o ON u.id o.uid/select
/mapperMapper
ListUser selectAll();
