preface

#{} and ${} are often used in the development of Mybatis, there are still many developers are not very clear about the use of the two, just the so-called good memory is better than bad writing, hereby summarize.

Dynamic SQL is one of the main features in MyBatis. After the parameters defined in Mapper are passed to XML, MyBatis will dynamically parse them before the operation. Mybatis provides two syntax support for dynamic SQL: #{} and ${}, the biggest difference is that the former method can prevent SQL injection (security), the latter method can not prevent SQL injection. What?? Not sure what Sql injection is? Er… Sql injection is when the program parses the parameters you pass in as part of the original Sql statement, disrupting the structure of the original Sql statement. Normally, we only need to pass in one parameter.

Understand SQL injection thoroughly

What? Not knowing SQL injection yet, I am not sure about QAQ… Let’s take the simplest example: general development, there must be two input boxes in the foreground, a user name, a password, will be in the background, read the two parameters passed in the foreground, put together a section of SQL, for example: Select count(1) from TAB where usesr=userinput and pass = passinput select count(1) from TAB where usesr=userinput and pass = passinput Just report a login failure error. Isn’t it. For example, if the user enters a password, enter ”” ‘or 1=1 “, so that the background program can parse the SQL statement. Select count(1) from TAB where user=userinput and pass=” or 1=1; SQL > select count (userinput, userinput, userinput, userinput, userinput, userinput, userinput, userinput); To prevent SQL injection, filter the single quotation marks in the password input and add other logical judgments after them, or do not use such dynamic SQL spelling

About # {}

1. #{} indicates that a placeholder is equivalent to a JDBC? The #{} symbol is used to set parameter values to the prepared statement in prepareStatement. In SQL statements, #{} represents a placeholder, i.e.?

2, #{} will pass the data as a string, automatically passed data around a double quote. Select * from user where id= #{user_id} select * from user where id= #{user_id}

3, If the SQL statement has only one parameter, the parameter name can be arbitrary. If the SQL statement has multiple parameters, the parameter name should be associated with the current table [entity class attribute name] or [Map set keyword]. The following figure

About ${}

1. {user_id}, if the value passed in is 11, then the value parsed into SQL iswhere id=11`

${value} does not automatically convert JDBC types. ${value} does not automatically convert JDBC types

In short, ${} can be used wherever JDBC does not support placeholders.

${} = ${

The simple difference is

#{} prevents SQL injection to a large extent (security). ${} does not prevent SQL injection

Where JDBC can use placeholders, #{} is preferred.

Where JDBC does not support placeholders, only ${} is used, typically for dynamic parameters

For example, there are two tables, emp_2017 and EMP_2018. If you need to specify the table name dynamically in a query, you can only use ${}

<select>
      select *  from emp_ ${year}
<select>
Copy the code

${} ${} ${} ${} ${}

<select>
       select  *  from dept order by ${name}
</select>
Copy the code

Code case

${} ${} ${} ${} ${} ${} ${} ${} ${} ${

Use the #{} case

1. Map files

Add the following to the user.xml configuration file:

<! If multiple results are returned, myBatis will automatically place the returned results in the list container.<! The resultType configuration is the same as the return result configuration -->
	<select id="queryUserByUsername1" parameterType="string"
		resultType="cn.itcast.mybatis.pojo.User">
		SELECT * FROM `user` WHERE username LIKE #{username}
	</select>
Copy the code

2. Test procedures

Add the following test method to MybatisTest:

@Test
	public void testQueryUserByUsername1() throws Exception {
		// 4. Create an SqlSession object
		SqlSession sqlSession = sqlSessionFactory.openSession();

		Run the SqlSession object and obtain the query result User
		// Use selectList to query multiple sets of data
		List<Object> list = sqlSession.selectList("queryUserByUsername1".King "% %");

		// 6. Print the result
		for (Object user : list) {
			System.out.println(user);
		}

		// 7. Release resources
		sqlSession.close();
	}
Copy the code

The test effect is shown as follows:

Use the ${} case

1. Mapping file:

Add the following to the user.xml configuration file:

<! ${} = 'value'; ${} = 'value';<select id="queryUserByUsername2" parameterType="string"
		resultType="cn.itcast.mybatis.pojo.User">
		SELECT * FROM `user` WHERE username LIKE '%${value}%'
	</select>

Copy the code

2. Test program: MybatisTest add test method as follows:

@Test
public void testQueryUserByUsername2() throws Exception {
	// 4. Create an SqlSession object
	SqlSession sqlSession = sqlSessionFactory.openSession();

	Run the SqlSession object and obtain the query result User
	// Use selectList to query multiple sets of data
	List<Object> list = sqlSession.selectList("queryUserByUsername2"."The king");

	// 6. Print the result
	for (Object user : list) {
		System.out.println(user);
	}

	// 7. Release resources
	sqlSession.close();
}

Copy the code

Of course both cases have the same effect!

If this article helped you at all, please give it a thumbs up. Thanks

Finally, if there is insufficient or improper place, welcome to criticize, grateful! If you have any questions welcome to leave a message, absolutely the first time reply!

Welcome everyone to pay attention to my public number, discuss technology together, yearning for technology, the pursuit of technology, good come is a friend oh…