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

This article will explain in detail the differences between the values of ${} and # {} in mybatis. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

When using the mybatis framework, there are two ways to get the parameters passed in the sql statement:

${paramName}

# {paramName}

Then how to understand these two ways of transmitting parameters? The following takes you to the secret meaning behind you.

Let's first review the native Jdbc query:

Public static void main (String [] args) throws Exception {/ / sql statement String sql = "select id,name from customer limit 2"; / / 1. Load the driver. The mysql driver package used here is version 8.0. For version 5.0 +, modify the following classpath Class.forName ("com.mysql.cj.jdbc.Driver"); / / 2. Get the database connection String url = "jdbc:mysql://localhost:3306/work?useSSL=false&useUnicode=true" + "& characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true" + "& useLegacyDatetimeCode=false&serverTimezone=UTC"; Connection conn = DriverManager.getConnection (url, "root", "123456"); / / 3, get the object Statement st = conn.createStatement () that can execute the sql statement; / / 4, use the object to execute the SQL statement ResultSet rs = st.executeQuery (sql) / / 5. Process the result set while (rs.next ()) returned by the sql statement {/ / get a row of data Integer id = rs.getInt ("id"); String name = rs.getString ("name"); System.out.println ("sql query: id =" + id + ", name =" + name);} / / 6, release resources rs.close (); st.close (); conn.close ();}

Console print:

Sql query: id = 1, name = Li Bai

Sql query: id = 2, name = du Fu

Anyone who knows Jdbc will know that steps 3 and 4 can also be replaced with the following two statements:

/ / 3. Create a PreparedStatement object to execute sqlPreparedStatement preparedStatement = conn.prepareStatement (sql); / / 4. Execute the sql statement ResultSet rs = preparedStatement.executeQuery ()

Let's compare the differences:

The sql statement is passed in when the PreparedStatement object is created, and there is no need to pass in sql when the statement is executed, while Statement is just the opposite

This leads to the concept of precompilation:

If you use the PreparedStatement object, now that you have passed in sql when you execute step 3, it means that the sql will be compiled by the database (the compilation of sql statements by the database is also quite complex), so there is no need to pass in sql when step 4 is executed, because the database already knows the sql you are going to execute, you only need to pass in parameters

If you use Statement objects, it's easy to understand that the database doesn't parse your sql in advance because you don't pass in when you create objects; when sql is executed, the database compiles and executes.

See here, probably only remember a pre-compiled sql, one is not pre-compiled, and do not understand the difference in actual development, the following examples will be illustrated.

Is it true that the PreparedStatement object approach is necessarily better than the Statement object approach?

It's not that absolute. You have to understand:

The advantage of the PreparedStatement object is that the sql has been compiled in advance, and all that is left is to pass in the parameters. The compiled sql can be reused and different parameters are passed in, and the database fills the compiled sql with the corresponding parameters. The Statement object is passed in sql every time and thrown to the database for compilation and execution; but the overhead of creating PreparedStatement objects is greater than that of Statement objects.

When we go back to daily development, we don't care about the above differences at all. In fact, we use PreparedStatement objects in 90% of the scenarios, which may not be perceived at ordinary times, because this is encapsulated by the framework. Furthermore, when there is a performance problem with the system, it is definitely not because of these two objects.

The above briefly reviews the PreparedStatement and Statement objects in Jdbc

It can be expected that the two values of ${} and # {} in mybatis correspond to the difference between PreparedStatement and Statement objects.

# {} passing parameters means that sql has been precompiled, and the parameters you pass in are really just parameters!

Pass the parameter ${}, pass it as you like, and then I will compile it uniformly after the pass.

What is the difference in use? Understand the following two scenarios:

1. Look at the following service and sql statements

@ Overridepublic List listUser () {String param = "and name ='Li Bai'"; return indexMapper.listUser (param);} select * from customer where 1 = 1 # {param}

Can the above code be queried normally?

# Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have

An error in your SQL syntax; check the manual that corresponds to your MySQL

Server version for the right syntax to use near''and name =\' Li Bai\''

I can't! A rule error in the sql statement will be reported. As mentioned earlier, a value of # {} means that sql has been compiled, and you pass in only the parameters.

Corresponding to the above example:

Sql means: select * from customer where 1 = 1

Parameter refers to: and name ='Li Bai'

At this point, sql can run correctly, but not with the parameters passed in. Understand that what you pass in are really just parameters, so don't mix up with the previous sql.

But this is obviously wrong, because what I'm trying to say is:

Sql means: select * from customer where 1 = 1 and name =?

Parameter refers to:'Li Bai'

At this point, replacing the parameter with a placeholder will run the entire statement normally.

So you should use ${param} at this time, because using ${} doesn't precompile what belongs to sql.

This example shows that when you pass a parameter that is not just a parameter, but actually a small piece of sql, and you want to splice it with the original sql, you have to pass the parameter with ${}, which is equivalent to throwing it to the

Database to parse the whole statement

The question mark in sql represents a parameter placeholder, which is also one of the features of the PreparedStatement object. It replaces all the parameters you pass in into the placeholder.

The opposite is as follows:

@ Overridepublic List listUser () {String param = "Li Bai"; return indexMapper.listUser (param);} select * from customer where 1 = 1 and name = # {param}

It is right to use # {} in this case, because the parameters passed in are just parameters and can be replaced in the sql statement.

two。 Influence on parameter types

@ Overridepublic List listUser () {String param = "Li Bai"; return indexMapper.listUser (param);} select * from customer where 1 = 1 and name = ${param}

Can the above code be executed successfully?

In theory, only parameters are passed in, which should be executed regardless of whether they are precompiled or not, but an error will actually be reported.

This is the sql statement printed out at execution time:

Select * from customer where 1 = 1 and name = Li Bai

Obviously, the problem is that the parameter does not have single quotation marks, the name field is a string type, and the string is passed in, but there is no single quotation mark after the mybatis conversion.

So when passing in a string type parameter, you should use # {} to take the value, which is automatically enclosed in single quotation marks.

Take a look at the following sentence:

@ Overridepublic List listUser () {String param = "name"; return indexMapper.listUser (param);} select * from customer where 1 = 1 order by ${param} desc

The parameter passed in at this time is the name of the field to be sorted. As mentioned earlier, if you use the value of # {}, you will actually put single quotation marks automatically, but do the sorting fields after order by need single quotation marks?

No, so you can only use the value of ${} in this case.

You may find that using the value of # {} here will not report an error, that is because mysql supports this writing, but the result of the query is not correct.

In daily development, as long as you can understand the above two situations, then you can correctly use ${} and # {}. Because of the difference in the value principle of the two methods, it is easy to understand that # {} this way can prevent sql injection.

This is the end of the article on "what is the difference between the values of ${} and # {} in mybatis". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.