MyBatis annotation usage (understand. Examples of usage of annotations, mainly using XML:

public interface UserMapperAnnotation { @Select("select id,last_name userName ,sex from t_user where id = #{id}") public  User selectUser(int id); @Select("select * from t_user") public List<User> selectUserList(); @Update("update t_user set last_name = #{lastName}, sex = #{sex} where id = #{id}") public int updateUser(User user); @Delete("delete from t_user where id = #{id}") public int deleteUserById(int id); @Insert("insert into t_user(`last_name`,`sex`) values(#{lastName},#{sex})") @SelectKey(before = false, keyProperty = "id", resultType = Integer.class, statement = { "select last_insert_id()" }) public int insertUser(User user); }Copy the code

Import from mybatis-config. XML configuration file

<mappers>
	<mapper class="com.dao.UserMapperAnnotation"/>
</mappers>
Copy the code

1. A common datatype When there is only one common datatype in a method. #{} placeholders can be used in mapper configuration files for placeholder output. In the #{} placeholder, you can write the parameter #{variable name}. You can also write #{value}.

Public int deleteUserById(int id);

#{variable name}

<delete id="deleteUserById" parameterType="int">
	delete from t_user where id = #{id}
</delete>
Copy the code

#{value}

<delete id="deleteUserById" parameterType="int">
	delete from t_user where id = #{value}
</delete>
Copy the code

2. Multiple common data types multiple common parameters. When we need to use #{} placeholder output, we can use param1, param2… ParamN is also known as #{param1}… #{paramN}

Or use @param to name parameters

Use param1 and param2…… ParamN Placeholder output parameter method:

public List<User> findUserByNameAndSex(String username, int sex);
Copy the code

Use param1 and param2…… ParamN is used to hold output parameters

<select id="findUserByNameAndSex" resultType="com.atguigu.bean.User" >
	select id,last_name lastName,sex from t_user where last_name = #{param1} and sex = #{param2}
</select>
Copy the code

3. Name parameter methods with the @param annotation:

public List<User> findUserByNameAndSex(@Param("username") String username, @Param("sex") int sex);
Copy the code

Output with named arguments:

<select id="findUserByNameAndSex" resultType="com.atguigu.bean.User" >
	select id,last_name lastName,sex from t_user where last_name = #{lastName} and sex = #{sex}
</select>
Copy the code

Pass a Map object as an argument when our argument is a Map object. We can use the map object’s key as a placeholder to output data. #{map key} as placeholder output

The following is an example:

Methods:

public List findUserByMap(Map<String, Object> map);

The code to call:

@Test public void findUserByMap() { SqlSession session = sqlSessionFactory.openSession(); try { UserMapper userMapper = session.getMapper(UserMapper.class); Map<String, Object>map = new HashMap<String, Object>(); map.put("lastName", "admin"); map.put("sex", 1); System.out.println( userMapper.findUserByMap(map) ); } finally { session.close(); }}Copy the code

The configuration is as follows:

<select id="findUserByMap" resultType="com.bean.User" >
	select id,last_name lastName,sex from t_user where last_name = #{lastName} and sex = #{sex}
</select>
Copy the code

A Pojo data type when the method argument is an object of complex type. We can use the property name of the object. As a placeholder name. For example: #{attribute name}

public int insertUser(User user);
Copy the code

Configuration in Mapper:

<insert id="insertUser" parameterType="com.atguigu.bean.User" useGeneratedKeys="true" keyProperty="id">
	insert into t_user(`last_name`,`sex`) values(#{lastName},#{sex})
</insert>
Copy the code

Multiple Pojo data types When multiple complex Pojo objects are passed as arguments to a method. We need to fetch the data as a parameter to the SQL. You can use the following method: #{param1. attribute name}…… #{paramN. Attribute name}

You can also name parameters using @param. Give each POJO object an alias. Then pass the #{alias. } to retrieve the data value as a parameter.

Example: The default value is param1, param2, or paramN. The configuration is as follows: Method:

 public List<User> findUserByTwoUser(User user1, User user2);
Copy the code

The configuration is as follows:

<select id="findUserByTwoUser" resultType="com.bean.User" > select id,last_name lastName,sex from t_user where last_name  = #{param1.lastName} and sex = #{param2.sex} </select>Copy the code

@param annotates the form of named arguments:

public List<User> findUserByTwoUser(@Param("user1") User user1, @Param("user2") User user2);
Copy the code

The configuration is as follows:

<select id="findUserByTwoUser" resultType="com.bean.User" > select id,last_name lastName,sex from t_user where last_name  = #{user1.lastName} and sex = #{user2.sex} </select>Copy the code

$$$$$$$$$$$$$$ Number. ${} in the SQL statement of mapper configuration file, it is the value of the output variable, and then the function of string concatenation operation. ${} can only be written in value, or the name of the @param named parameter. This may lead to SQL injection problems such as:

Select * from t_user where id =? Select * from t_user where id =? Select * from T_user where ID = ${value} is equivalent to select * from t_user where id = the value of the output variableCopy the code

Fuzzy queries now query user objects based on user names. You want to query as follows:

Select id,last_name lastName,sex from t_user WHERE last_name like '% % %'Copy the code

Methods:

public List<User> findUserLikeName(String name);
Copy the code

#{} implement configuration and call code:

@Test public void findUserLikeName() { SqlSession session = sqlSessionFactory.openSession(); try { UserMapper userMapper = session.getMapper(UserMapper.class); / / need at the time of passing parameters, yourself before and after the two percent System. Out. The println (userMapper. FindUserLikeName (" % % "a)); } finally { session.close(); }}Copy the code

The configuration is as follows:

<select id="findUserLikeName" resultType="com.bean.User" > select id,last_name lastName ,sex from t_user where last_name  like #{name} </select>Copy the code

The implementation of the configuration and the implementation of the call ${} are just the values of the output parameters as they are. Then do the concatenation of strings.

Calling code:

@Test public void findUserLikeName() { SqlSession session = sqlSessionFactory.openSession(); try { UserMapper userMapper = session.getMapper(UserMapper.class); / / need at the time of passing parameters, yourself before and after the two percent System. Out. The println (userMapper. FindUserLikeName (" a ")); } finally { session.close(); }}Copy the code

The configuration is as follows:

<select id="findUserLikeName" resultType="com.bean.User" >
	select id,last_name lastName ,sex,phone from t_user where last_name like '%${value}%'
</select>
Copy the code

MySQL string concatenation, concat function implementation. In mysql, there is a string concatenation function. It’s called a concat function. When we need to do a query like this. We can use the #{} combination concat to solve the problem of parameter input and not needing to add two %% when passing the parameter. It can also solve SQL injection problems. Use as follows:

Code call:

@Test public void findUserLikeName() { SqlSession session = sqlSessionFactory.openSession(); try { UserMapper userMapper = session.getMapper(UserMapper.class); / / need at the time of passing parameters, yourself before and after the two percent System. Out. The println (userMapper. FindUserLikeName (" a ")); } finally { session.close(); }}Copy the code

The configuration is as follows:

<select id="findUserLikeName" resultType="com.bean.User" > select id,last_name lastName ,sex from t_user where user_name  like concat('%',#{name},'%'); </select>Copy the code