Oracle combines Mybatis to obtain 10 pieces of data from the table

I have been using mysql and informix databases before. It is very simple to look up the first 10 pieces of data in the table: This article mainly introduces the relevant information of Oracle and Mybatis to achieve the 10 pieces of data from the table. Friends who need it can refer to it. I hope it can help. Everyone.

Original version:

select top * from student

## Of course, we can also write it more complicated, such as adding Some query conditions?

For example, query the top 10 student information with scores greater than 80 points

Added version of where query conditions:

select top * from table where score > 80

but! ! There is no top in Oracle! ! ! ! So how to achieve it?

Well, you can use rownum!

Original version in oracle

select * from student where rownum < 10

There seems to be nothing complicated about the above. . But here comes the question, what if we still want to add a score greater than 80?

For me, an Oracle beginner, it is really laborious. I just posted it here, hoping it can save some people some effort!

Oracle has added a version of where query conditions

select * from(
  select rownum rn,A.* from student where score > 80) 
where rn < 10

Briefly analyze the above code. In fact, the data with a score greater than 80 is first queried through the embedded sql statement, and then the first 10 data in the embedded sql query result are selected.

Finally, attach the mybatis code?

<select id="selectStudent" parameterType="hashmap" resultMap="BaseResultMap">
  select *
  from (
  select rownum rn, A.* from student A
  where STATUS = &#39;99&#39;
  and score <![CDATA[>]]> #{scores,jdbcType=INTEGER}) 
  where rn <![CDATA[<=]]> #{number,jdbcType=INTEGER}
 </select>

The above scores and number are variables

ps: mybatis takes Oracle sequence, the same value problem processing

<select id="getCode" resultType="java.lang.String">
  select &#39;TRD&#39;||to_char(sysdate,&#39;yyyymmdd&#39;)||lpad(to_char(SQ_ORD_PURCHASE_ID.nextval), 5, &#39;0&#39;) code from dual
</select>

The above mybatis code always gets the value of the same sequence when called. Query the relevant information and find out that it is the cache of mybatis. Question:

Add

useCache="false" flushCache="false" Attribute:

<select id="getCode" resultType="java.lang.String" useCache="false" flushCache="false">
select &#39;TRD&#39;||to_char(sysdate,&#39;yyyymmdd&#39;)||lpad(to_char(SQ_ORD_PURCHASE_ID.nextval), 5, &#39;0&#39;) code from dual
</select>

Related Recommended:

How to change the data file location of the table space in Oracle

How to modify the order of fields in the Oracle database table

oracle query table name and table column name

The above is the detailed content of Oracle combines Mybatis to obtain 10 pieces of data from the table. For more information, please follow other related articles on the PHP Chinese website!