Mybatis 常用操作、使用技巧
文章目录
- Mybatis 常用操作、使用技巧
- @[toc]
- Mybatis Repalce into 判断批量新增或者修改
- 传参String 类型逗号分隔
- in 查询 超过1000条 处理
- insert batch操作
- 查询结果 Map 接收 key 大小写问题
- 配置全局的配置 统一大小写
- 封装对象继承 HashMap 重写 put
- Insert 多 Value 写法
- Mysql
- Oracle
- DatabaseId 的使用
Mybatis Repalce into 判断批量新增或者修改
Mybatis
批量插入的时候判断,根据主键或者唯一索引(组合索引)- 假如有表
t_menu
,我们用Mybatis
向里面批量添加多条记录的时候,如果表中有唯一性索引(组合索引)。假如t_menu
中的唯一性索引为name
那么用replace into
的时候,当named
的值相同的时候,进行更新操作。否则进行插入操作。假如t_menu
中有组合唯一索引(name
,price
)那么只要有一个索引字段的值不同那么进行插入,只有两个都相同的时候进行更新操作。 Sql
如下:
<!-- 判断如果存在则为更新不在则为新增 -->
<insert id="addorupdate">
replace into t_menu (name,price,flavor) values
<foreach collection="menu" item="item" separator=",">
(#{item.name},#{item.price},#{item.flavor})
</foreach>
</insert>
- 注意用
Repalce into
的时候必须要有主键或者是索引,索引可以是单个唯一索引,也可以是组合唯一索引。
传参String 类型逗号分隔
-
dao
接口如下,记住得要加上@param
,不然xml
中分隔的时候会报错,id
没有get
方法。
List<User> listUsersByIdWithIn(@Param("id") String ids);
-
xml
中sql
如下
<select id="selectWithIn" resultType="map">
<foreach collection="id.split(',')" index="index" item="id" open="(" separator="," close=")">
<if test="(index % 999) ==998"> NUll) or id in (</if>'${id}'
</foreach>
</select>
in 查询 超过1000条 处理
- 当
in
查询超过1000
时候会出错,动态的拼接in
进行查询
<select id="inSelect" resultType="map">
select * from test where id in
<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
<if test="(index % 999) ==998"> NUll) or id in (</if>'${id}'
</foreach>
</select>
insert batch操作
- 批量插入操作
<insert id="insertBatch" parameterType="list">
insert INTO test (id,name,age) VALUES
<foreach collection="list" item="item" separator=",">
(#{item.id}, #{item.name}, #{dependency.age})
</foreach>
</insert>
查询结果 Map 接收 key 大小写问题
配置全局的配置 统一大小写
MapKeyLowerWrapper
public class MapKeyLowerWrapper extends MapWrapper {
private static final String ORACLE = "oracle";
public MapKeyLowerWrapper(MetaObject metaObject, Map<String, Object> map) {
super(metaObject, map);
}
@Override
public String findProperty(String name, boolean useCamelCaseMapping) {
DruidDataSource dataSource = SpringContextUtils.getBean(DruidDataSource.class);
String dbType = dataSource.getDbType();
// Oracle Map 结果中 key转为小写
if(ORACLE.equalsIgnoreCase(dbType)) {
return name == null ? "" : name.toLowerCase();
}
return name;
}
}
MapWrapperFactory
public class MapWrapperFactory implements ObjectWrapperFactory {
@Override
public boolean hasWrapperFor(Object object) {
return object != null && object instanceof Map;
}
@Override
public ObjectWrapper getWrapperFor(MetaObject metaObject, Object object) {
return new MapKeyLowerWrapper(metaObject,(Map)object);
}
}
- 配置
ConfigurationCustomizer
Bean
/**
* 创建 MapWrapperFactory 的 Bean 解决 Map 结果中 key 的大小写
* @return
*/
@Bean
public ConfigurationCustomizer configurationCustomizer() {
return configuration -> configuration.setObjectWrapperFactory(new MapWrapperFactory());
}
封装对象继承 HashMap 重写 put
LowerKeyMap
public class LowerKeyMap extends HashMap {
@Override
public Object put(Object key, Object value) {
if (Objects.nonNull(key)) {
key = key.toString().toLowerCase();
}
return super.put(key, value);
}
}
UpperKeyMap
public class UpperKeyMap extends HashMap {
@Override
public Object put(Object key, Object value) {
if (Objects.nonNull(key)) {
key = key.toString().toUpperCase();
}
return super.put(key, value);
}
}
Insert 多 Value 写法
Mysql
- 方式一:注解形式
@Insert("<script>INSERT INTO test\n" +
" (id,request_id,code,type)\n" +
" values\n" +
" <foreach collection=\"extInfo.list\" item=\"item\" separator=\",\">\n" +
" (#{item.id}, #{item.requestId}, #{item.code}, #{item.type})\n" +
" </foreach></script>")
int insertBatch(TestDo testDO);
Oracle
- 方式一:注解形式
@Insert("<script>INSERT INTO ${extInfo.tableName}\n" +
" (id,requestid,code,columnname,datatype) " +
" <foreach collection=\"extInfo.list\" item=\"item\" separator=\"UNION ALL\">\n" +
" select #{item.id}, #{item.requestId}, #{item.code}, #{item.columnName},#{item.dataType} from dual " +
" </foreach></script>")
void insertBatch(TestDo testDO);
DatabaseId 的使用
-
Mybatis
这个参数标志了当前数据库的标识,使用之前需要配置一下,将对应的标识进行注册,完美支持动态数据源
@Configuration
public class DataBaseConfig {
private static final Logger logger = LoggerFactory.getLogger(DataBaseConfig.class);
@Value("${mybatis.mapper-locations}")
private String mapperLocations;
@Autowired
private DruidDataSource dataSource;
@Bean
public DatabaseIdProvider databaseIdProvider() {
DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
Properties properties = new Properties();
String dbType = dataSource.getDbType();
properties.setProperty(dbType, dbType.toLowerCase(Locale.ROOT));
properties.setProperty("Oracle", "oracle");
properties.setProperty("MySQL", "mysql");
properties.setProperty("PostgreSQL", "postgresql");
properties.setProperty("DB2", "db2");
properties.setProperty("SQL Server", "sqlserver");
databaseIdProvider.setProperties(properties);
logger.info("Module:lowcode-database:DatabaseIdProvider bean registered!");
return databaseIdProvider;
}
@Bean
public SqlSessionFactoryBean sqlSessionFactoryBean(DataSource dataSource) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSource);
factoryBean.setDatabaseIdProvider(databaseIdProvider());
factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
logger.info("Module:lowcode-database:SqlSessionFactoryBean bean registered!");
return factoryBean;
}
}
- 使用实例
xml
:Mybatis 会根据数据源选择不同的sql
执行
<!-- 创建建表记录固化表 -->
<update id="createBCreateTable" databaseId="mysql">
create table if not exists b_create_table
(`ID` varchar(36) NOT NULL,
`SCHEMA` varchar(100) NULL,
`MODEL` varchar(100) NULL,
`TABLENAME` varchar(100) NULL,
`VERSION` varchar(100) NULL,
`DBTYPE` varchar(100) NULL,
CONSTRAINT bcreatetable_pk PRIMARY KEY (id))
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;
</update>
<update id="createBCreateTable" databaseId="postgresql">
create table if not exists b_create_table
(`ID` varchar(36) NOT NULL,
`SCHEMA` varchar(100) NULL,
`MODEL` varchar(100) NULL,
`TABLENAME` varchar(100) NULL,
`VERSION` varchar(100) NULL,
`DBTYPE` varchar(100) NULL,
CONSTRAINT bcreatetable_pk PRIMARY KEY (id))
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;
</update>