Use of mapper in mybatis and precautions
The project was done in the company these days, and it was at the end of the process, but I found that I had left a lot of pitfalls for myself when using mybatis, so I spent two days debugging and reconceiving the files I wrote. Finally, I have gained something. I will share it with you here.
1. What is
It is an important part of mybatis and is also one of the core ones. What we can see is that it is defined by some defined database query statements and some XML specifications.
When we use mybatis, we can see that the mapper file here is equivalent to an implementation of a dao-layer interface, because the queries defined in the mapper must be consistent with the dao-layer method, so that queries can be performed.
The most powerful feature of mybatis here is this mapper configuration file, because mybatis can read maps from the contents and generate SQL statements for querying.
2. How to write mapper and precautions
There are many ways to use our mapper, here is a brief explanation and summary:
(1) Use it directly as a SQL statement
We can use the <select><udpate><insert><delete> tag in mapper to splice our sql statement. The tag written here is the header for mybatis parsing, so our corresponding sql statement must be written under the corresponding tag before it can be successful.
(2) Conditional query statement for adding judgment
In mapper, we can add the due judgment to the conditions to conduct some sub-conditional query, especially in the where clause. It is noted here that the use judgment, that is, if is also a judgment condition labeled <if test=""></if>, where in test is if, the judgment here is some judgment judgment, and it has no other function, but the writing method needs to be paid attention to. You can directly use the variables passed down in the Dao interface to make the judgment directly.
The following statement adds a judgment to splice SQL statements:
<if test='state=="0"'>
AND t_acceptanceform.acceptancestate!='0'
</if>
<if test='state!=0'>
AND t_acceptanceform.acceptancestate =#{state}
</if>Here is where different statements are selected through different variable conditions to make SQL statements more flexible.
(3) Add some variable collections to query or return values
There are actually two types of variable sets referred to here. One is a map type collection, and the other is a custom query field collection in SQL. In the code that we use to make return values and mybatis automatically generates is this map collection, which is generally called resultmap. It can correspond to entity classes of pojo and other types. It is used to receive and send query return values. It can be automatically converted by the mybatis mechanism into some List collections or other collections.
Format:
<resultMap id="" type="" > <!--idTo identify thismapThe only logo of，Can't be repeated，typeFor the correspondingpojoPackage path--> <id column="" property="" jdbcType="" /> <!--columnIt's the column name，propertyFor the correspondingprojoObject name，typeIt's the type in the database--> <result column="" property="" jdbcType="" /> </resultMap>
The second is the general query result set with the <sql> tag as the header. Its advantage is that it can be customized or some judgment can be added. We can generally use it when doing complex queries. The following is an example I wrote:
<sql > outputrepositorycode, outdate, consignee, ordersno, repocode, transferpath, acceptanceformcode, address, totalproducts, createtime, batchcount, comments, customerid, pitposition </sql>
Queries used:
<select resultType="">
SELECT
<include ref/>
FROM
t_outputorderb , t_acceptanceform ,t_orders
WHERE
ordercode=belongorderid AND ordercode=ordersNo AND outputrepositorycode=#{opCode}
</select>Here we attach the <sql> tag content automatically generated by the software. We have not carefully studied the things inside, but the for loop and if used should be a logical judgment, which is similar to the writing conditions.
<sql >
<where >
<foreach collection="oredCriteria" item="criteria" separator="or" >
<if test="" >
<trim prefix="(" suffix=")" prefixOverrides="and" >
<foreach collection="" item="criterion" >
<choose >
<when test="" >
and ${}
</when>
<when test="" >
and ${} #{}
</when>
<when test="" >
and ${} #{} and #{}
</when>
<when test="" >
and ${}
<foreach collection="" item="listItem" open="(" close=")" separator="," >
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>This is a splicing judgment used in mybatis for querying splicing query conditions. You can study it carefully.
3. Pay attention to passing values
We all know that when writing queries in mapper, we will use some query conditions to correlate incoming variables. Generally, this type of person uses #{variable name} or ${variable name} to get values. So what is the difference between them?
1. #{param} will generate a PreparedStatement and can safely set the value of the parameter (=?). I thought the SQL statement had been precompiled, and the SQL statement would not be re-production when the parameters were passed. High safety.
2. ${parem} directly inserts the param in the {} number into the string, which will cause SQL injection problem:
For example:
select * from userwhere userName= ${userName} The output result is
select * from userwhere userName= “Xiao Ming”
3. In specific scenarios, for example, if you are using order by '{param}', you can use ${}
4. The #{} method can prevent SQL injection to a large extent, while the ${} method cannot prevent SQL injection
5. The ${} method is generally used to pass in database objects, such as passing in table names.
String replacement:
By default, using the #{} format syntax causes MyBatis to create a preprocessed statement property and set a safe value with it as the background (such as?). This is safe and quick, and sometimes you just want to insert a string that doesn't change directly into the SQL statement.
For example, like ORDER BY, you can use it like this:
ORDER BY ${columnName}Here MyBatis will not modify or escape strings.
Important: It is not safe to accept content output from the user and provide it to an unchanged string in the statement. This can lead to potential SQL injection attacks, so you should not allow users to enter these fields, or usually escape and check them yourself.
It is important to note here that when you can use #{param}, try not to use ${param}.
Use of mybatis universal mapper
At present, general mapper only supports operations on single tables. It does not require writing corresponding SQL statements. It only requires us to call the corresponding interface, which is extremely convenient for rapid development.
1. First, in the maven project, introduce the mapper dependency
<dependency> <groupId></groupId> <artifactId>mapper</artifactId> <version>3.3.8</version> </dependency>
Add scan path to the configuration file
<bean class=""> <property name="basePackage" value=".**.mapper" /> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" /> </bean>
3. Set the primary key of the entity class
@Table(name = "op_virtual_ip")
public class VirtualIpBean {
//Primary key @Id
Long id;
@Column
String data;
}4. The business mapper interface inherits the general mapper and only determines the entity generics.
import ;
public interface IVirtualIPMapper extends Mapper<VirtualIpBean> {
}5. Actual use of business mapper interface
import ;
import ;
import ;
import ;
import ;
@Service
public class VirtualIpService {
@Autowired
IVirtualIPMapper vipMapper;
public void test(){
VirtualIpBean vipBean = new VirtualIpBean();
(new Date());
// (1) Mapper basic interface //select interface List<VirtualIpBean> vipList = (vipBean);//Check according to the attribute value in the entity, use the equal sign for the query conditions VirtualIpBean vip = (vipBean);//Query according to the properties in the entity, only one return value can be found, multiple results are thrown, and the query conditions use equal sign List<VirtualIpBean> vipList2 = ();//Query all results, the select(null) method can achieve the same effect VirtualIpBean vip2 = (1);//Check according to the primary key field. The method parameters must contain complete primary key attributes. The query conditions use equal signs. int count = (vipBean);//Check the total number of attributes in the entity, and use the equal sign for the query conditions //insert interface int a = (vipBean);//Save an entity, the null attribute will also be saved, and the database default value will not be used. int a1 = (vipBean);//Save the entity, the null attribute will not be saved, and the database default value will be used //update interface int b = (vipBean);//Update the value whose attribute is not null according to the primary key int c = (vipBean);//Update all fields of the entity according to the primary key, the null value will be updated //delete interface int d = (vipBean);//Delete according to entity attributes as conditions, use equal sign for query conditions int e = (1);//Delete according to the primary key field, the method parameters must contain the complete primary key attributes //(2) Example method Example example = new Example();
().andEqualTo("id", 1);
().andLike("val", "1");
//Custom query List<VirtualIpBean> vipList3 = (example);
}
}Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.