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

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>

Summary

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!