Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how to sort the database by mybatis". The editor shows you the operation process through the actual case. The operation method is simple, fast and practical. I hope that this article "how to sort the database with mybatis" can help you solve the problem.

Mybatis database sorting

Today, it is used to output the database in reverse order. Because I just came into contact with mybatis, I don't know much about it. After searching the Internet for a long time, I finally found some useful information.

My goal is that the information in the database is divided into invalid and valid, and the valid values of the information in the database are output in reverse order and output to the table.

I saw such a sentence on the Internet, SELECT * FROM photo ORDER BY id DESC;, so I just took it and used it, but there was a problem, because mine was conditionally sorted.

After several attempts, I found that it was written like this, select

From manager_user where is_valid = 1

In this way, the output of ORDER BY id DESC; achieves my goal. If you replace desc in the statement with asc, you can sort it in ascending order.

Mybatis order by sorting

When using MyBatis to parse xml for sorting, I encountered the problem of invalid sorting!

# treat all the incoming data as a string and put a double quotation mark on the automatically passed data. For example: order by # {user_id}, if the value passed in is 111, then the value parsed to sql is order by "111l". If the value passed in is id, the parsed sql is order by "id".

The incoming data is directly displayed and generated in sql. For example: order by ${user_id}, if the value passed in is 111, then the value parsed to sql is order by 111.If the value passed in is id, the parsed sql is order by id.

The method can prevent sql injection to a great extent.

The $method cannot prevent Sql injection.

The $method is generally used to pass in database objects, such as table names.

Don't use $if you can use #.

Use $instead of # after order by

Attached solution code snippet:

Where orderByField is the parameter value passed in for sorting!

Order by ${orderByField} ASC order by ${orderByField} DESC order by id DESC

ORDER BY # {sort,jdbcType=VARCHAR} # {order,jdbcType=VARCHAR} id asc, create_time asc

The reason is: # {order,jdbcType=VARCHAR}, MyBatis automatically treats the sort field as a string, which is equivalent to order by 'create_time'' desc', can be executed, but not valid, unlike the order by create_time desc result

Solution: using order,Mybatis will treat it as a direct variable, and when the variable is replaced successfully, it will not be quoted as a string, and the sort order will be the same.

{order}, so

ORDER BY ${sort} ${order} id asc, create_time asc

# can greatly prevent SQL injection $cannot prevent Sql injection $for incoming database objects, statements within this symbol will not be treated as strings, but directly as sql statements, such as to execute a stored procedure.

When writing sql statements in a mapper file, when you encounter special characters, such as:

Etc., recommended

This is the end of the content about "how to sort the database with mybatis". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.