Foreword
Use Mybatis frequently used in development and $ # {} {}, there are still many developers to use them is not very clear, the so-called good memory as bad written, hereby summarize.
In mybatis dynamic sql is one of its main features, then the parameters defined in the xml passed in the mapper, mybatis will be dynamically resolved before performing operations.mybatis provides two support dynamic sql syntax of: $ # {} and {}, the biggest difference is the way the former can to a large extent prevent sql injection (safety), the latter method can not prevent Sql injection .what??I do not know what is Sql injection?amount...Sql injection means that the program will resolve your argument passed as part of the original SQL statements, SQL upset the original structure, but usually we just need to pass a parameter only.
A thorough understanding of SQL injection
what?SQL injection still do not understand, I lake the QAQ...It would be the most simple example: general development, certainly in the foreground with two input box, a user name, a password, in the background, the foreground reads incoming these two parameters, makes up for some SQL, For example: select count (1) from tab where usesr = userinput and pass = passinput, put this SQL connection data, see the username / password combination exists, if any, can be successful landing, and if does not exist, reported a failed login error.Right. But this is the case, this is fight out SQL based on user input, if the user intentionally input string can make the background resolution failure, which is SQL injection, for example, when users enter a password, enter '''' '''' ''or 1 = 1 '''', so that the background of the program at the time of the analysis, makes up the SQL statement might look like this: select count (1) from tab where user = userinput and pass = '''' or 1 = 1; see this statement, can know, after parsing, the user does not enter a password, add an identity of condition 1 = 1, so this SQL execution time, the return value is certainly larger than the count 1, logical if the program did not increase too many judgments, so that you can log in with a user name userinput without password. Prevent SQL injection, you must first enter password in single quotes filtered and added behind the other with logic, or no such dynamic SQL spell
About # {}
1, # {} represents a placeholder corresponds to the ? # Sign {} is the set parameter values to achieve the prepareStatement preprocessing statement, sql statement # {} represents a placeholder, i.e.,?
2, # {} incoming data as a string, the incoming data will automatically add a double quote.Such as: if the incoming value 11, then the value is parsed into sql,
3, if only, at this time if the parameter name can have multiple parameters sql statement sql statement, in which case the parameter name should be associated with [attribute name of the entity class] or the [Map set of keywords] with the current form, can not just write , must correspond !As shown below
About $ {}
1, {user_id} where id = 11`
2, the limit values are values corresponding to only write value not just to write, as there is no automatic type conversion jdbc
3, in simple terms, where not support the use of placeholders, you can use
In distinction Mybatis $ # {} {} with the
In simple terms the difference is
# {} Mode can to a large extent prevent sql injection (safety), $ {} methods can not prevent Sql injection
Can be used in the place of the placeholder is preferably preferentially used
Where does not support the use of placeholders, you can only use, typically is the dynamic parameters
For example, there are two tables, respectively, and .If you need to dynamically specify the table name in the query, you can only use $ {}
select * from emp_ $ {year}
Another example is the use of dynamic parameters, only this time using MyBatis sort $ {}
select * from dept order by $ {name}
Code Cases
General Case # $ {} {} and use more places are fuzzy query terms, so the following query to a blur
Use Cases
1, map files
In the User.xml configuration file, add the following:
2, the test program
MybatisTest adding a test method is as follows:
@Test
public void testQueryUserByUsername1 () throws Exception {
// 4. Creating objects SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession ();
// 5. SqlSession execution object to execute a query, get the results User
// query multiple data using a method selectList
List
// 6. Print results
for (Object user: list) {
System.out.println (user);
}
// 7. Release resources
sqlSession.close ();
}
Test results as shown:
Use $ {} case
1, the mapping file:
In the User.xml configuration file, add the following:
2.Test Procedure: MybatisTest adding a test method is as follows:
@Test
public void testQueryUserByUsername2 () throws Exception {
// 4. Creating objects SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession ();
// 5. SqlSession execution object to execute a query, get the results User
// query multiple data using a method selectList
List
// 6. Print results
for (Object user: list) {
System.out.println (user);
}
// 7. Release resources
sqlSession.close ();
}
Of course, the same effect two cases!
That''s all for this article, I want to be helpful to learn, I hope you will support script Home.
You may also be interested in the article: Mybatis difference in # {} and {} $ parameter passing and distinguish the difference between # and $ # {} Summary MyBatis in and explain the difference between the $ {} On mybatis the # and $ and MyBatis prevent sql injection method in the use of $ and # are facing, and to solve the dynamic sql On mybatis under way in # and $ Mybatis distinguish the difference between ## and $$ explain MyBatis in $ {#} and { } proper use (do not mess with) Mybatis the # {} and {} uses the difference between $ Detailed Detailed Mybatis in $ {} {#} and the difference between the $ and # MyBatis usage of in-depth explanation