Mybatis realizes data up, down, top and bottom

introduce

In some management systems, there are often some requirements to move the generated list data up, down, top and bottom. At this time, some SQL skills are required. Here is an introduction to the use of Mybatis technology.

Concrete realization

Database table

The first design of the table needs to have some requirements, here a menu tree as an example to illustrate.

Create database table

The table name is menu tree, and the database uses MYSQL5.7. The table creation statement is as follows:

CREATE TABLE MENU_TREE (
	SID VARCHAR(20),
	CODE VARCHAR(20),
	NAME VARCHAR(50),
	GRADE CHAR(1),
	PRENT_ID VARCHAR(20),
	ORDER_VAL NUMERIC(22,0),
	STATE CHAR(1),
	DEL_TAG VARCHAR(1) DEFAULT '0',
	CRT_OPT VARCHAR(20),
	CRT_TIME TIMESTAMP,
	UPD_OPT VARCHAR(20),
	UPD_TIME TIMESTAMP
) ;

Insert data presentation original sort

You can insert data at will. In this example, after inserting data, the order is as follows:

Business realization

Data upwards

It is required that "human resources department" should be moved up to "human resources department" before "member Department".

SQL implementation

update MENU_TREE st set st.ORDER_VAL= 4 where st.ORDER_VAL= 3 AND prent_id='0';
update MENU_TREE st set st.ORDER_VAL= 3 where st.SID='4';

You need to add BEGIN...END statement in Oracle database.

XML file for Mybatis

<update>
update MENU_TREE st set st.ORDER_VAL=':orderVal' where st.ORDER_VAL=':orderVal'-1 AND prent_id=':prentId';
update MENU_TREE st set st.ORDER_VAL=':orderVal'-1 where st.SID=':sid';
</update>

The BEGIN...END statement needs to be added to Oracle (it will not be repeated later). Here: parameter, which means the parameter passed in from outside. These parameters are the corresponding values of the current operation object (Human Resources Department) (not described in detail later).

Move up results

It can be seen that "human resources department" has moved to the front of "member Department".

Data downwards

This is the opposite operation of data moving up. We move the "member Department" down as follows:

SQL implementation

update MENU_TREE st set st.ORDER_VAL=4 where st.ORDER_VAL=5 AND st.prent_id='0';
update MENU_TREE st set st.ORDER_VAL=5 where st.SID='3';

XML file for Mybatis

<update>
update MENU_TREE st set st.ORDER_VAL=':orderVal' where st.ORDER_VAL=':orderVal'+1 AND st.prent_id=':prentId';
update MENU_TREE st set st.ORDER_VAL=':orderVal'+1 where st.SID=':sid';
</update>

Results after move down

Data placement

The "strategic development department" will be put at the top as follows:

SQL implementation

update MENU_TREE st set st.ORDER_VAL=st.ORDER_VAL+1 where st.ORDER_VAL<6 AND st.prent_id='0';
update MENU_TREE st set st.ORDER_VAL='1' where st.SID='6';

XML file for Mybatis

<update>
update MENU_TREE st set st.ORDER_VAL=st.ORDER_VAL+1 where st.ORDER_VAL&lt;':orderVal' AND st.prent_id=':prentId';
update MENU_TREE st set st.ORDER_VAL='1' where st.SID=':sid';
</update>

Effect after topping

Data placement

Put the "President Office" at the bottom, as follows:

SQL implementation

This is the implementation of MySQL

update MENU_TREE st set st.ORDER_VAL=st.ORDER_VAL-1 where st.ORDER_VAL&gt;'3' AND st.prent_id='0';
update MENU_TREE st INNER JOIN (select max(ORDER_VAL)+1 max from MENU_TREE WHERE prent_id='0') pt SET st.ORDER_VAL=pt.max where st.SID='2';

In mysql, you need to pay attention to the way in which update directly uses the result of select.

Oracle or SqlServer:

update MENU_TREE st set st.ORDER_VAL=st.ORDER_VAL-1 where st.ORDER_VAL&gt;'3' AND st.prent_id='0';
update MENU_TREE st set st.ORDER_VAL=(select max(ORDER_VAL)+1 from MENU_TREE WHERE prent_id='0' ) where st.SID='2';

XML file for Mybatis

This is the implementation of MySQL

<update>
update MENU_TREE st set st.ORDER_VAL=st.ORDER_VAL-1 where st.ORDER_VAL&gt;':orderVal' AND st.prent_id=':prentId';
update MENU_TREE st INNER JOIN (select max(ORDER_VAL)+1 max from MENU_TREE WHERE prent_id=':prentId') pt SET st.ORDER_VAL=pt.max where st.SID=':sid';
</update>

Oracle implementation:

<update>
begin 
update MENU_TREE st set st.ORDER_VAL=st.ORDER_VAL-1 where st.ORDER_VAL&gt;':orderVal' AND st.prent_id=':prentId';
update MENU_TREE st set st.ORDER_VAL=(select max(ORDER_VAL)+1 from MENU_TREE WHERE prent_id=':prentId' ) where st.SID=':sid';
end;
<update>

Bottom effect

The above is the realization of moving up, down, top and bottom. Let's take a look at Mybatis, which is useful for processing greater than or less than.

Greater than less than processing in Mybatis

There are two processing methods here, the first is:

Original symbol“
Replace symbol & lt; & lt; = & gt; & gt; = & apos; & quote;

For example, sql is as follows: publish_time >= #{startTime} and publish_time <= #{endTime}

The second way to deal with it:

Greater than or equal to
<![CDATA[ >= ]]>
Less than or equal to
<![CDATA[ <= ]]>

For example, sql is as follows: create_date_time <![CDATA[ >= ]]> #{startTime} and create_date_time <![CDATA[ <= ]]> #{endTime}

This detail needs attention when using Mybatis.

Tags: Database Mybatis SQL Oracle

Posted on Fri, 08 Nov 2019 05:59:21 -0500 by gateUK