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

1. What is dynamic SQL

The traditional method of using JDBC, I believe that when combining complex SQL statements, we need to concatenate, a little careless, even if the lack of a space, will lead to errors. The dynamic SQL function of Mybatis is to solve this problem. Through if, choose, when, otherwise, trim, where, set, foreach tags, it can be combined into very flexible SQL statements, thus improving the efficiency of developers.

The SQL statement which is not fixed and will change according to the operation of the foreground user can be called dynamic SQL. In MyBatis, a set of tags are provided to facilitate the implementation of dynamic SQL without the need for java code to concatenate strings.

# 2. Tags in dynamic sql

1.

For conditional judgment, the test attribute represents the judgment result, which is required to be a boolean.

two。

Used to maintain the where clause, usually used together. The functions are as follows:

A) when there are no conditions, the WHERE keyword will not be created

B) the WHERE keyword is automatically generated when there are conditions

C) the and/or keyword of the first condition is automatically removed.

3.

The function is similar to switch. Case... Default, which means multi-branch judgment, can only hold one condition.

Select * from tb_user and id=# {id} and username=# {username} and age # {age} and birthday = # {birthday} and birthday is null

4.

Processing parameters, usually used for fuzzy queries to add wildcards to parameters

And realname like # {realname}

5.

Used in conjunction to extract a generic SQL statement fragment, which is used to reference a SQL fragment

Select id, username, password, realname, age, birthday, reg_time regTime from tb_user and realname like # {realname}

6.

Used to maintain the set clause in update statements, characterized by the ability to remove extra commas

Update tb_user username=# {username}, age=# {age} where id=# {id}

7.

Traversing collections (arrays, List, Set, Map), usually used for in operations or batch additions. Brief introduction of attributes:

A) collection: the collection to traverse

B) item: iterative term

C) open: what character does it begin with

D) close: what character does it end with

E) separator: separator between multiple iterations

Delete from tb_user id in # {id}

8.

Appends and removes specified characters before and after the statement.

Insert into tb_user values, default, # {user.username}, # {user.password}, # {user.realname}, # {user.age}, # {user.birthday}, now ()

Knowledge points: what is the difference between static sql and dynamic sql

From the point of view of compilation and operation, SQL statements can be divided into two types: static SQL and dynamic SQL. These two SQL have their own characteristics in terms of usage, operation mechanism and performance.

Static SQL: static SQL statements are generally used in embedded SQL applications. SQL statements must be determined before the program runs. For example, column names and table names involved in SQL statements must exist. Static SQL statements are compiled before the application runs, and the compiled results are stored inside the database. Then when the program runs, the database will directly execute the compiled SQL statements, reducing the runtime overhead.

Dynamic SQL: dynamic SQL statements are compiled and executed when the application is running. For example, when using DB2's interactive tool CLP to access the database, the SQL statements entered by the user are uncertain, so SQL statements can only be compiled dynamically. There are many applications of dynamic SQL, and common CLI and JDBC applications use dynamic SQL.

Static sql: the statement type must be determined at programming time. such as

Select * from employee where empno='abc'select * from employee where empno='12'

Must be determined, and the only thing that can be changed is the value of abc.

Dynamic sql: the statement type can be specified at run time. For example, clp is the most typical dynamic sql program, and you can enter any command.

The access path of static sql is determined before running, while the access path of dynamic sql is dynamically generated at run time. Therefore, the generated access plan is relatively better, but given the overhead of generating the access path, it is possible that the application will run longer than the static sql.

Summary

This is the end of this article on the Mybatis4 Mybatis dynamic sql implementation code, more relevant mybatis dynamic sql content, please search the previous articles or continue to browse the following related articles hope that you will support more in the future!

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.