oracle多表关联分页条件查询
- 最近使用oracle数据库,在查询数据时,需要多表关联查询,而mybatis-plus的selectPage()方法只能实现单表的分页条件查询,而且发现时间模糊查询查不到数据,因此需要自己写SQL语句
- 分页SQL
方式一:SELECT * FROM ( SELECT t.*, rownum rn FROM ( SELECT * FROM 表名) t ) WHERE rn > (当前页-1)*每页的数据条数 AND rn <= 当前页*每页的数据条数;
方式二:SELECT * FROM (SELECT ROWNUM AS rowno, t.* FROM CUSTOMER t WHERE ROWNUM <= 5) table_alias WHERE table_alias.rowno >= 1;
-
当我们需要通过xml格式处理sql语句时,经常会用到< ,<=,>,>=等符号,但是很容易引起xml格式的错误,这样会导致后台将xml字符串转换为xml文档时报错,从而导致程序错误。
这样的问题在iBatiS中或者自定义的xml处理sql的程序中经常需要我们来处理。其实很简单,我们只需作如下替换即可避免上述的错误:
-
serviceimp
@Override public CommonResult<Customer> findCustomers(Integer page, Integer limit, Customer customer) { Integer start = (page-1)*limit;//开始数 Integer end = page * limit;//结束数 List<Customer> customers = customerMapper.findCustomers(start,end,customer); Long count = customerMapper.findCustomerCount(customer); return CommonResult.success(customers,count); }
-
xml映射文件
<select id="findCustomers" resultMap="customer">
SELECT * FROM ( SELECT t.*, rownum rn FROM (SELECT * FROM CUSTOMER <include refid="customerSql"/>) t ) WHERE rn > #{start} AND rn <= #{end}
</select>
<resultMap id="customer" type="com.woniuxy.web.entity.Customer">
<id column="ID" property="id"/>
<result column="NICKNAME" property="nickname"/>
<result column="HEAD_PORTRAIT" property="headPortrait"/>
<result column="SEX" property="sex"/>
<result column="BIRTHDAY" property="birthday"/>
<result column="USERINFO_ID" property="userinfoId"/>
<result column="VIP_GRADE_ID" property="vipGradeId"/>
<result column="INTEGRAL" property="integral"/>
<result column="REGIST_TIME" property="registTime"/>
<result column="LAST_LOGIN_TIME" property="lastLoginTime"/>
<result column="LAST_LOGIN_IP" property="lastLoginIp"/>
<result column="PAY_CODE" property="payCode"/>
<result column="IS_LOCK" property="isLock"/>
<result column="UPDATE_TIME" property="updateTime"/>
<collection property="vipGarde" ofType="com.woniuxy.web.entity.VipGarde" column="VIP_GRADE_ID" select="selvipGarde"/>
</resultMap>
<select id="selvipGarde" resultType="com.woniuxy.web.entity.VipGarde">
select * from VIP_GARDE where ID = #{VIP_GRADE_ID}
</select>
<sql id="customerSql">
<where>
<if test="customer.id!=null and customer.id!=''">
and ID = #{customer.id}
</if>
<if test="customer.nickname!=null and customer.nickname!=''">
and like '%${customer.nickname}%' //oracle数据库模糊查询用法
</if>
</where>
</sql>
racle数据库模糊查询用法