两个数据库分页还是分批查sql肯定是不兼容的
然后limit的第二个参数是偏移量，不是between xx and yy
其次是limit后面不支持运算符 有种方式是 set @sql = concat(‘select* from user where id= 123456 andcode= 111 and create_date >= 20190101 and create_date <= 20190202 limit’, (1-1)*1,’,20’); prepare texts from @sql; execute texts; 使用变量用concat来连接sql，然后这个字符串prepare texts from @sql;执行； 但是很可惜的mybatis中是不能用这个的。。
其次如果在mysql中想要实用类似rownum的话需要使用变量
如下： Select @rownum :=@rownum +1 as rownum From (select @rownum:=0) varibleT
建一个变量即可，但是很可惜的是不可用在mybatis中,同样
我试了能行的兼容方式：
<select id=''> <if test="_databaseId=='oracle'"> select tt.MODEL_ID, tt.MODEL_DETAIL_ID, tt.COLUMN_NAME, tt.DIM_HIERARCHY_CODE, tt.sort_num, tt.DS_ID, tt.DEFINITION_ID, tt.rw from (select t.MODEL_ID, t.MODEL_DETAIL_ID, t.COLUMN_NAME, t.DIM_HIERARCHY_CODE, t.sort_num, t.DS_ID, t.DEFINITION_ID, ROWNUM as rw from (select md.MODEL_ID, md.MODEL_DETAIL_ID, md.COLUMN_NAME, md.DIM_HIERARCHY_CODE, md.sort_num, m.DS_ID, m.DEFINITION_ID from HRF_MODEL_DETAIL md inner join HRF_MODEL m on md.MODEL_ID = m.MODEL_ID where md.IS_OPEN = 'Y' and md.COLUMN_NAME is not null and md.DIM_HIERARCHY_CODE is not null order by md.MODEL_ID, md.sort_num, md.COLUMN_NAME) t) tt where tt.rw between #{start} and #{end} </if> <if test="_databaseId=='mysql'"> select md.MODEL_ID, md.MODEL_DETAIL_ID, md.COLUMN_NAME, md.DIM_HIERARCHY_CODE, md.SORT_NUM, m.DS_ID, m.DEFINITION_ID from HRF_MODEL_DETAIL md inner join HRF_MODEL m on md.MODEL_ID = m.MODEL_ID where md.IS_OPEN = 'Y' and md.COLUMN_NAME is not null and md.DIM_HIERARCHY_CODE is not null order by md.MODEL_ID, md.SORT_NUM, md.COLUMN_NAME limit #{start} , #{end} </if> </select>还有一种写法是用java代码设置数据源类型然后mybatis自动查databaseId为当前环境的 /**
数据源类型适配MyBatis会加载不带databaseId属性和带有匹配当前数据库databaseId属性的所有语句。如果同时找到了带有databaseId和不带databaseId的相同语句，则不带databaseId的将被舍弃。 */ @Configuration public class datasourceTypeConfig { @Bean public DatabaseIdProvider databaseIdProvider() { DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider(); Properties p = new Properties(); p.setProperty("Oracle", "oracle"); p.setProperty("MySQL", "mysql"); databaseIdProvider.setProperties(p); return databaseIdProvider; } }然后再mapper.xml中写两个
<select id="" resultType="String" databaseId="mysql"> xxxx </select> <select id="" resultType="String" databaseId="oracle"> xxxx </select>