Oracle uses Mybatis to retrieve the TOP 10 data records in the table. oraclemybatis

Source: Internet
Author: User

Oracle uses Mybatis to retrieve the TOP 10 data records in the table. oraclemybatis

I have been using mysql and informix databases before. The first 10 pieces of data in the table are very simple:

Original version:

select top * from student

Of course, we can also make it more complex, such as adding some query conditions?

For example, you can query the first 10 students whose scores are greater than 80.

Versions with where query conditions added:

select top * from table where score > 80 

But !! Oracle does not have top !!!! How can this problem be achieved?

Well, you can use rownum!

Original oracle version

select * from student where rownum < 10

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

It is really laborious for beginners of oracle. I posted it here, hoping to reduce the cost for some people!

Version of where query condition added to oracle

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

Analyze the code above. In fact, we first use an embedded SQL statement to query data with a score greater than 80, and then select the first 10 pieces of data in the embedded SQL query results.

Is the mybatis code appended?

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

Both scores and number are variables.

Ps: mybatis obtains the Oracle sequence. The same value is used for troubleshooting.

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

The above mybatis code always obtains the value of the same sequence during the call. It is known that it is a cache problem of mybatis when querying related information:

AdduseCache="false" flushCache="false" Attribute:

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


The above section describes how to use Oracle and Mybatis to retrieve the TOP 10 pieces of table data. I hope this will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.