This paper connects MyBatis basic learning knowledge point 1 and continues to discuss the following two issues
1. What is Dao configuration file mainly used for? How to configure?
2. How to test program running with test method? What does each statement do?
catalogue
What is dao configuration file mainly used for? How to configure?
How to test program running with test method? What does each statement do?
Pass multiple simple type parameters
Pass parameters using entity class properties
#The difference between placeholder and $placeholder
Encapsulate MyBatis output results
When the query result is a user-defined type
When the result of the query is a simple type
When the query result is Map type
resultMap custom search results
What is dao configuration file mainly used for? How to configure?
We take out the simplest dao layer configuration file for analysis
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ling.mybatis.dao.UserDao">
<!--The path must be between packages.separate-->
<select id="findAllUser" resultType="com.ling.mybatis.pojo.User">
select * from user
</select>
</mapper>You can see that the label body of the configuration file is http://mybatis.org/dtd/mybatis-3-mapper.dtd Under the constraints of
The most important one is the mapper tag
mapper tag
The mapper tag is used to configure the mapping of dao interfaces
namespace property
It is used to define the namespace for accessing sql statements, which we can see when writing the final execution code
@Test
public void test1() throws IOException {
//Define the path of mybatis core configuration file under classes
String mybatisPath = "mybatisConfig.xml";
//Get byte input stream object according to path
InputStream is = Resources.getResourceAsStream(mybatisPath);
//Create SqlSessionFactoryBuilder object
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//Create a SqlSessionFactory object through the build method of the SqlSessionFactoryBuilder object
SqlSessionFactory factory = sqlSessionFactoryBuilder.build(is);
//Obtain the sql execution object through the openSession method of the factory class
SqlSession sqlSession = factory.openSession();
//selectOne method of sql execution object
//Get the first parameter, i.e. execution id: by namespace ++ SQL statement label id
String id = "com.ling.mybatis.dao.UserDao" + "." + "findUserById";
User user = sqlSession.selectOne(id);
System.out.println(user);
//Close sqlSession
sqlSession.close();
}Finally, the sql statement will be executed by the SqlSession object. When the sql statement is executed, a string needs to be passed in, which is composed of namespace and sql statement id. writing in this way can make the program more formatted and hierarchical.
The value of namespace is recommended to be set to the full path name of dao interface under src. In order to facilitate the identification of structure hierarchy and the use of subsequent tools, it is used to participate in the identification of sql statements.
select tag
It is mainly used to write select related sql statements
id attribute
Set the name of sql statement. In order to distinguish the structure hierarchy and the use of subsequent tools, it is recommended to use the corresponding method name
resultType property
Set the return value type of the found final result. The type needs to write the full path name under src
For example:
<select id="findAllUser" resultType="com.ling.mybatis.pojo.User">
select * from user
</select>insert tag
It is mainly used to write insert related sql statements
id attribute
Set the name of sql statement. In order to distinguish the structure hierarchy and the use of subsequent tools, it is recommended to use the corresponding method name
<insert id="insertUser">
insert into user values(#{id},#{username},#{password})
</insert>update tag
It is mainly used to write sql statements related to update
id attribute
Set the name of sql statement. In order to distinguish the structure hierarchy and the use of subsequent tools, it is recommended to use the corresponding method name
<update id="updateUser">
update user set password = #{password} WHERE id = #{id};
</update>delete tag
It is mainly used to write delete related sql statements
id attribute
Set the name of sql statement. In order to distinguish the structure hierarchy and the use of subsequent tools, it is recommended to use the corresponding method name
<delete id="deleteUser">
delete from user where id = #{id}
</delete>How to test program running with test method? What does each statement do?
First, we use the following basic execution code as an example
@Test
public void test1() throws IOException {
//Define the path of mybatis core configuration file under classes
String mybatisPath = "mybatisConfig.xml";
//Get byte input stream object according to path
InputStream is = Resources.getResourceAsStream(mybatisPath);
//Create SqlSessionFactoryBuilder object
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//Create a SqlSessionFactory object through the build method of the SqlSessionFactoryBuilder object
SqlSessionFactory factory = sqlSessionFactoryBuilder.build(is);
//Obtain the sql execution object through the openSession method of the factory class
SqlSession sqlSession = factory.openSession();
//selectOne method of sql execution object
//Get the first parameter, i.e. execution id: by namespace ++ SQL statement label id
String id = "com.ling.mybatis.dao.UserDao" + "." + "findUserById";
User user = sqlSession.selectOne(id);
System.out.println(user);
//Close sqlSession
sqlSession.close();
}Basic steps
Step 1: set the path of the central configuration file
Step 2: use the Resources static method to load the central configuration file into the byte input stream
Step 3: get the SqlSessionFactoryBuilder object whose main task is to create a factory object
Step 4: use the build method of SqlSessionFactoryBuilder object to pass in the byte input stream, parse the central configuration file, initialize the database connection pool, and obtain each dao configuration file.
Step 5: obtain the sqlSession execution object through the openSession method of the SqlSessionFactory object, which is mainly used for the execution of sql statements.
Step 6: define the string and lock the statement to be executed
Step 7: execute sql statement
SqlSessionFactory interface
Interface function
SqlSessionFactory interface is the factory interface of sqlsession, which is mainly used to create sqlsession objects
SqlSessionFactory has many functions, and the creation process is slower than others. It requires more time and space. You can have one in the project.
Interface method
openSession(); Get a default SqlSession object. By default, transactions need to be submitted manually
Opensession (Boolean): the Boolean parameter indicates whether the transaction is automatically committed
true: create a SqlSession that automatically commits transactions
false: equivalent to openSession without parameters
SqlSession interface
Interface function
It provides a large number of methods to execute sql statements, and the thread is not safe
Thread safe, not required
Use steps
1. Obtain the sqlSession object before executing the sql statement inside the method
2. Call sqlSession method object and execute sql statement
3. Close sqlSession and execute the close method of sqlSession
In this way, the sqlSession exists only in the method and in the thread space where the method is executed, so other threads cannot obtain the data object in this thread.
Interface method
selectOne(String, Object): the execution result with only one line of return value. If there is more than one line, the execution error will occur
selectMap(String, Object): executes an interface that returns a map type
selectList(String, Object): the execution returns a list collection data
insert(String, Object): perform the add operation
update(String, Object): perform modification
delete(String, Object): delete
Commit: perform transaction commit
Rollback: execute transaction rollback
MybatisUtils tool class
From the above implementation steps, we can see that we need to carry out the above seven steps for each database operation, but there is a lot of code redundancy in the seven steps, and the code of one to five steps is basically the same. Therefore, we can write and construct a MybatisUtils tool class, which is specially used for mybatis initialization.
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory = null;
//Through the static code block, the value is assigned when the class is loaded
static {
String sqlPath = "mybatisConfig.xml";
try {
//Get byte input stream according to the path of core configuration file
InputStream resource = Resources.getResourceAsStream(sqlPath);
//Use the build method of the Builder object to obtain the factory object
sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource);
} catch (IOException e) {
e.printStackTrace();
}
}
//Get SqlSession object (do not automatically commit transactions)
public static SqlSession getSqlSession(){
//If there are no parameters in the openSession method, the automatic submission will be canceled. If true is passed in, auto submit will be turned on
return sqlSessionFactory.openSession();
}
}At this time, we simplified the first five steps, but the simple string splicing in step 6 and the execution of the method in step 7 are still not simplified. Moreover, we found that the interface seems to have no function at all, the method of the interface has not been called, and the interface has no implementation class. The interface seems to be written in vain?
Implement dao interface
According to the code writing practice of the three-tier architecture in the Java Web learning stage, we create an impl folder and create dao interface implementation classes inside the folder.
public class UserDaoImpl implements UserDao {
@Override
public List<User> findAllUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
String sqlPath = "com.ling.mybatis.dao.UserDao.findAllUser";
List<User> list = sqlSession.selectList(sqlPath);
sqlSession.close();
return list;
}
}We put the operation steps of step 6 and step 7 into the interface implementation class, which completes the integration. When we need to call methods for sql statement operation, we can create dao interface implementation class objects and execute their corresponding methods.
However, the above integration scheme can not help us simplify the steps of program writing, but simply integrate the data operation. We need a method that can directly complete the data operation
Use proxy mode
dao agent of mybatis
Code writing
Getmapper (class class of Dao interface)
@Test
public void test2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
userDao.findUserById(1);
sqlSession.close();
}Get the proxy object through the getMapper method of sqlSession. We learned the proxy proxy mode when learning the Aop enhancement of Spring framework. The main working principle is to create a subclass of the target enhancement object through the reflection mechanism, and strengthen the parameters, content and return value of the method without changing the content of the original method.
The getMapper method here is to use the proxy mode to create the implementation class object of userdao, obtain the path name and method name of userdao through the class loader and reflection mechanism, automatically complete the splicing, and select the appropriate sqlSession method to add, delete, modify and query. Here is why we set the namespace attribute of the dao layer configuration file to the full path under the src of the dao interface, and why the label id of the sql statement should be set to the method name.
Understanding parameters
Pass the data into the sql statement in the mapper file through the Java program. The parameters here mainly refer to the formal parameters in the dao interface method
parameterType
Represents the parameter type and specifies the formal parameter data type of dao method. The data type of this parameter is for mybatis. Mybatis is used when assigning values to the parameters of sql statements. We learned in jdbc to prevent sql statement injection and use the preparedStatement object. Are we right? The corresponding setXXX (index, value) method needs to be used for input when injecting the attribute value into the content. The parameterType here is to select the injected attribute type.
<select id="findUserById" parameterType="java.lang.Integer" resultType="com.ling.mybatis.pojo.User">
select * from user where id = #{id}
</select>mybatis can use the reflection mechanism to obtain the parameter type of dao interface method, so the parameterType can be omitted
Pass a simple type parameter
Simple type: the basic data type in Java
When the passed parameter is a simple type parameter, the mapper file uses #{any character} to get the parameter value
<select id="findUserById" resultType="com.ling.mybatis.pojo.User">
select * from user where id = #{id}
</select>Pass multiple simple type parameters
The @ Param annotation is used to name the parameter. It is used in front of the formal parameter of the method to define the parameter name. This name should be used in the mapper file, that is, the value in #{} is the name after the parameter is named
List<User> findUserByIdOrUsername(@Param("Sid") Integer id, @Param("Susername") String username); <select id="findUserByIdOrUsername" resultType="com.ling.mybatis.pojo.User">
select * from user where id = #{Sid} or username = #{Susername}
</select>Pass parameters using entity class properties
If the method parameter is directly passed into an entity class, mybatis will automatically resolve the attribute of the entity class, and find the parameter name in #{} the configuration file according to the attribute name. If it matches, assign a value.
int insertUser(User user);
<insert id="insertUser">
insert into user values(#{id},#{username},#{password})
</insert>Pass parameters by location
By passing parameters by location, multiple parameters in the interface do not need to be aliased with @ Param annotation. You only need to use #{arg index} in the sql statement to specify the parameters to be passed in
List<User> findUserByIdOrUsername(Integer id, String username);
<select id="findUserByIdOrUsername" resultType="com.ling.mybatis.pojo.User">
select * from user where id = #{arg0} or username = #{arg1}
</select>Using map to pass parameters
The key value in the map corresponds to the name in #{} and the value value is the injected value.
List<User> findUserByIdOrUsername(Map map);
<select id="findUserByIdOrUsername" resultType="com.ling.mybatis.pojo.User">
select * from user where id = #{id} or username = #{username}
</select>#The difference between placeholder and $placeholder
The # placeholder is actually an injection of value after the PrepareStatement compiles the sql Statement, while the $placeholder is a simple string splicing, that is, using an ordinary Statement.
$features:
1. The Statement object is used, which is inefficient when executing SQL statements
2.$ {} placeholder, which uses string splicing, has the risk of sql injection and code security problems
3.$ {} data is used as is, and data types are not distinguished
4.$ {} is often used as a table name or column name, and ${} is used when data security can be guaranteed
#Features:
1. The PrepareStatement object is used to execute sql statements with high efficiency.
2. Using the PrepareStatement object can avoid the problem of SQL statement injection and make the execution of SQL statements safer
3.# {} is often used as a column value, which is located to the right of the equal sign. The value of #{} position is related to the data type
Encapsulate MyBatis output results
resultType
resultType attribute: used when executing select. It appears as the attribute of < Select > tag
resultType: indicates the result type. mysql executes sql statements to get the type of java object. There are two kinds of values
1. Fully qualified name of Java type (we have been using it before)
2. Alias set in central profile
When I explained the central configuration file in the first article, I said in detail that it is set inside the typeAliases tag of the central configuration file. If you use the package tag to take the alias, then the whole package will be taken together. If you use the typeAlias tag, it will be taken one by one. The specific advantages and disadvantages can be seen from me First article.
Central profile:
<typeAliases>
<!-- Pathnames are separated by / or Any separation -- >
<!-- Alias all classes under a package. The alias is the class name, which is not case sensitive -- >
<package name="com.ling.mybatis.pojo"/>
<!-- Give an individual class any alias -- >
<typeAlias type="com.ling.mybatis.pojo.User" alias="user"></typeAlias>
</typeAliases>
dao layer profile:
<select id="findUserById" resultType="user">
select * from user where id = #{id}
</select>
When the query result is a user-defined type
In fact, we have been writing to take User as the return value result, which is to encapsulate the query result into the class object.
After the sql statement queries the data, mybatis will use java reflection to call the parameterless constructor to create the object according to the resultType according to the configuration content, and then compare the key value corresponding to the obtained result set with the object attribute value. If the attribute name is the same, inject the value into the object. If multiple rows of data are queried, multiple objects will be created and stored in the list collection.
User findUserById(Integer id);
<select id="findUserById" resultType="user">
select * from user where id = #{id}
</select>When the result of the query is a simple type
Set resultType directly to the path in java
<select id="getSum" resultType="java.lang.Integer">
select count(*) from user
</select>Integer getSum();
When the query result is Map type
mybatis will add the query results of sql statements to the map set in the form of key value pairs. However, if map is used as the return value, the execution result of the program can only have one line of query results, and an error will be reported if there is more
<select id="getMapper" resultType="java.util.Map">
select * from user where id = #{id}
</select>Map<Object,Object> getMapper(Integer id);
resultMap custom search results
Using resultMap, you can customize the corresponding relationship between attributes in the result set and object attributes, which is often used when the object attributes are different from the column names of query results.
How to use:
1. The primary key name uses the id tag, column specifies the column name in the result set, and property specifies the property name in the class.
2. Other column names use the result tag. Column specifies the column name in the result set, and property specifies the property name in the class.
3.id attribute is used to set the name of the result set
4. The type attribute is used to specify the class to match
5. Set the resultMap attribute in the select tag to the id value in the resultMap tag
List<UserDocument> findAllUserDocument();
<resultMap id="userDocument" type="com.ling.mybatis.pojo.UserDocument">
<!--Set the correspondence between column names and class properties-->
<!--Primary key name usage id One to one correspondence of labels-->
<id column="id" property="userId"></id>
<!--Use of other attributes result One to one correspondence of labels-->
<result column="age" property="userAge"></result>
<result column="game" property="userGame"></result>
</resultMap>
<select id="findAllUserDocument" resultMap="userDocument">
select * from userdocument
</select>The select tag attribute resultMap obtains the unique name of the resultMap tag of the configuration related information, finds the configured resultMap through the unique name, obtains the return value type according to the type attribute, parses the contents of the id tag and the result tag, obtains the corresponding relationship of the attribute value, and finally assigns the found value according to the corresponding relationship first, and then assigns the value normally.
Query about like
When query statements need to use like
For example: select * from user where name like% Zhang%;
The first is to pass% zhang% as a whole as a string
List<User> findUserLike1(String username);
<select id="findUserLike1" resultType="com.ling.mybatis.pojo.User">
select * from user where username like #{username}
</select>@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> list = userDao.findUserLike1("%z%");
for (User user : list) {
System.out.println(user);
}
sqlSession.close();
}The second method: write in sql statements according to the standard, "%" value "%" value There must be a space between% and value
List<User> findUserLike2(String username);
<select id="findUserLike2" resultType="com.ling.mybatis.pojo.User">
select * from user where username like "%" #{username} "%"
</select>@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> list = userDao.findUserLike1("z");
for (User user : list) {
System.out.println(user);
}
sqlSession.close();
}