mybatis配置踩坑-oracle数据库
最近在做mysql迁移至oracle,记录一下踩的坑。
模糊查询
mysql中一直使用concat(’%’,field1,’%’),但是oracle中的concat函数只支持两个参数,其他数据库也不一样,下面这种写法支持所有数据库,可以放心使用啦
<if test="lineName != null and lineName!=''">
<bind name="lineName_part" value="'%' + lineName + '%'"/>
AND lineName like #{lineName_part}
</if>
分頁
rownum 是oracle自带的函数,之前用rownum between … and …,只能出来第一页,查资料发现是因为和order by一起使用时,会先生成rownum再排序
SELECT * FROM (SELECT tt.*, ROWNUM AS rowno FROM (
SELECT DISTINCT CATEGORY as "category",LOCATION as "location",'曲线' as "data" FROM T_WYY_FAULT_PICS ORDER BY CATEGORY ASC
) tt WHERE ROWNUM <= 20) table_alias WHERE table_alias.rowno > 10
批量插入语句
insert into table (field1,field2,...,fieldn) values(v1,v2,...,vn)
上面的语句oracle数据库只支持单条插入,批量插入会报错:SQL命令未正确结束。
<insert id="insertBatch">
INSERT INTO table (field1,field2,...,fieldn)
<foreach collection="list" item="line" separator="union">
select #{line.v1,jsbcType=VARCHAR},#{line.v2,jsbcType=VARCHAR},...
#{line.vn,jsbcType=VARCHAR} from dual
</foreach>
</insert>
ORA-01795: 列表中的最大表达式数为 1000
这个错误出现的原因是in里边的数据项不能超过1000个,所以对于大数据量的删改查来说,需要想办法避免。我是在删除的时候遇到的,想过多次调用delete语句,后来发现可以用or语句进行连接:
// 参数是这样的
void deleteData(String tableName, String field, List<List<String>> list);
// 第三个参数是由List<String>转为List<List<String>>
int size = 999;
for (int i = 0; i < Math.ceil(deleteList.size() / Double.valueOf(size)); i++) {
List<String> miniList = deleteList.subList(i * size,(i + 1) * size >= deleteList.size() ? deleteList.size() : (i + 1) * size);
deleteInBatchList.add(miniList);
}
<delete id="deleteData">
delete from ${tableName}
<where>
<if test="list != null and list.size() > 0">
<foreach collection="list" item="item" separator="or">
<if test="item != null and item.size() > 0">
${field} in
<foreach collection="item" item="i" separator="," open="(" close=")">
#{i}
</foreach>
</if>
</foreach>
</if>
</where>
</delete>