SqlSessionFactory has multiple methods to create SqlSession instances, including the following two:
-
SqlSessionFactory.openSession: a transaction will be started by default, but the transaction will not be submitted automatically, which means that the transaction needs to be submitted manually before the update operation data can be persisted in the database
-
SqlSessionFactory.openSession(boolean autoCommit): the parameter is whether to commit automatically. If it is set to true, the transaction does not need to be submitted manually
|Return value | method name | description|
| — | — | — |
|SqlSession | openSession() | use the SqlSession builder object and enable manual transaction submission|
|SqlSession | openSession(boolean autoCommit) | through the SqlSession builder object, if the parameter is true, the automatic transaction submission is enabled|
SqlSession provides all the methods required to execute SQL commands in the database. It also provides operations related to transactions.
The membership method is as follows:
-
sqlSession.commit(): commit a transaction
-
sqlSession.rollback(): rollback transaction
-
sqlSession.close(): release resources
We usually encapsulate the SqlSession instance obtained by mybatis into a tool class
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static{
try {
// Using Mybatis
// Step 1: get sqlSessionFactory object
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//Now that we have SqlSessionFactory, as the name suggests, we can get an instance of SqlSession from it.
public static SqlSession getSqlSession(){
// SqlSession sqlSession = sqlSessionFactory.openSession();
// return sqlSession;
return sqlSessionFactory.openSession();
}
}
===============================================================================
4.0. Traditional development of Mybatis
4.0 traditional implementation of Dao layer
-
Layered idea: controller, service and Dao.
-
Call process
4.1 agent development mode of Dao layer
-
Mapper interface development method only requires programmers to write mapper interface (equivalent to Dao interface)
-
Our previous development specification is to write Dao interface, and then write the implementation class DaoImpl
-
Agent development specification is to write Dao interface, and then write xml configuration file
Mapper interface development needs to follow the following specifications:
-
Mapper. The namespace in the XML file is the same as the fully qualified name of the mapper interface
-
Mapper interface method name and mapper The id of each statement defined in the XML is the same
-
Mapper interface method input parameter type and mapper The parameterType of each sql defined in XML is the same
-
Mapper interface method output parameter type and mapper The resultType of each sql defined in XML is the same
4.2. 1. One parameter
4.2. 1.1 basic parameters
We can use #{} direct to get values. We can get parameters by writing any name. However, it is generally taken by the parameter name of the method.
For example:
- Methods in the interface are defined as follows
public interface UserDao {
User findUser(Integer id);
}
- The contents in the xml are as follows
<select id="findUser" parameterType="int" resultType="com.sangeng.pojo.User">
select * from user where id = #{id}
</select>
- test
@Test
public void findUser() throws IOException {
//Defines the path to the mybatis configuration file
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//Get Sqlsession object
SqlSession sqlSession = sqlSessionFactory.openSession();
//Get UserDao implementation class object
UserDao userDao = sqlSession.getMapper(UserDao.class);
//Call method test
User user = userDao.findUser(2);
System.out.println(user);
//Release resources
sqlSession.close();
}
4.2.1.2,POJO
We can use the property name in the POJO to get the corresponding value.
For example:
- Methods in the interface are defined as follows
public interface UserDao {
User findByUser(User user);
}
- The contents in the xml are as follows
<select id="findByUser" resultType="com.sangeng.pojo.User">
select * from user where id = #{id} and username = #{username} and age = #{age} and address = #{address}
</select>
- test
@Test
public void findByUser() throws IOException {
//Defines the path to the mybatis configuration file
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//Get Sqlsession object
SqlSession sqlSession = sqlSessionFactory.openSession();
//Get UserDao implementation class object
UserDao userDao = sqlSession.getMapper(UserDao.class);
//Call method test
User byUser = userDao.findByUser(new User(2, "PDD", 25, "Shanghai"));
System.out.println(byUser);
//Release resources
sqlSession.close();
}
4.2.1.3,Map
We can use the key in the map to get the corresponding value.
For example:
- Methods in the interface are defined as follows
public interface UserDao {
User findByMap(Map map);
}
- The contents in the xml are as follows
<select id="findByMap" resultType="com.sangeng.pojo.User">
select * from user where id = #{id} and username = #{username} and age = #{age} and address = #{address}
</select>
- test
@Test
public void findByMap() throws IOException {
//Defines the path to the mybatis configuration file
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//Get Sqlsession object
SqlSession sqlSession = sqlSessionFactory.openSession();
//Get UserDao implementation class object
UserDao userDao = sqlSession.getMapper(UserDao.class);
//Call method test
Map map = new HashMap();
map.put("id",2);
map.put("username","PDD");
map.put("age",25);
map.put("address","Shanghai");
User byMap = userDao.findByMap(map);
System.out.println(byMap);
//Release resources
sqlSession.close();
}
4.2. 2. Multiple parameters
Mybatis will put multiple parameters into a Map set. The default key is argx and paramx.
For example:
-
Methods in the interface are defined as follows
- We usually use @ Param to set the parameter name before the method parameter.
public interface UserDao {
User findByCondition(@Param("tid") Integer id,@Param("username")String username);
}
- The contents in the xml are as follows
<select id="findByCondition" resultType="com.sangeng.pojo.User">
select * from user where id = #{tid} and username = #{username}
</select>
- test
@Test
public void findByCondition() throws IOException {
//Defines the path to the mybatis configuration file
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//Get Sqlsession object
SqlSession sqlSession = sqlSessionFactory.openSession();
//Get UserDao implementation class object
UserDao userDao = sqlSession.getMapper(UserDao.class);
//Call method test
User pdd = userDao.findByCondition(2, "PDD");
System.out.println(pdd);
//Release resources
sqlSession.close();
}
4.2. 3. Summary
-
It is suggested that if there is only one parameter, no special processing should be done.
-
If there are multiple parameters, you must add @ Param to set the parameter name.
4.2. 4. Difference between #{} and ${} when getting parameters
-
If #{} It is precompiled SQL, which can prevent SQL injection attacks
-
If ${} is used, the parameter values are directly spliced, which will be in danger of SQL injection
==========================================================================
The select statement has many properties, and each SQL statement can be configured in detail
-
id: is the method name in the corresponding namespace
-
Resulttype: return value of SQL statement execution [complete class name or alias]
-
parameterType: parameter type passed in SQL statement
Example:
- Add corresponding method in UserMapper
public interface UserMapper {
// Query all users
List<User> getUserList();
}
- In usermapper Add Select statement to XML
<mapper namespace="com.kuang.dao.UserMapper">
<select id="getUserList" resultType="com.kuang.pojo.User">
select * from mybatis.user;
</select>
</mapper>
- Test in test class
public void test(){
//Step 1: get SqlSession object
SqlSession sqlSession = MybatisUtils.getSqlSession();
//Method 1: execute SQL
UserMapper userDao = sqlSession.getMapper(UserMapper.class);
List<User> userList = userDao.getUserList();
for (User user : userList) {
System.out.println(user);
}
// Close SqlSession
sqlSession.close();
}
Example 2: query user by id
- Add corresponding method in UserMapper
public interface UserMapper {
// Query user by ID
User getUserById(int id);
}
- In usermapper Add Select statement to XML
<select id="getUserById" parameterType="int" resultType="com.kuang.pojo.User">
select * from mybatis.user where id = #{id}
</select>
- Test in test class
@Test
public void getUserById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
sqlSession.close();
}
Requirement: add a user to the database
- Add the corresponding method in the UserMapper interface
public interface UserMapper{
// insert
int addUser(User user);
}
- In usermapper Add insert statement to XML
<insert id="addUser" parameterType="com.kuang.pojo.User">
insert into mybatis.user(id, name, pwd) VALUES (#{id},#{name},#{pwd})
</insert>
- Test in test class
// Add, delete and modify transactions that need to be committed
@Test
public void addUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int res = mapper.addUser(new User(5, "Ha ha 2", "1234"));
if(res > 0) {
System.out.println("Insert successful");
}
// Commit transaction
sqlSession.commit();
sqlSession.close();
}
-
In the insert operation, use the parameterType property to specify the type of data to insert
-
Use #{entity attribute name} in Sql statement to refer to attribute value in entity
-
Adding, deleting and modifying operations involve changes in database data, so you should use sqlSession object to explicitly commit transactions, that is, sqlSession commit()
Requirement: modify user information
- Add the corresponding method in the UserMapper interface
public interface UserMapper{
// Modify user
int updateUser(User user);
}
- In usermapper Add update statement to XML
<update id="updateUser" parameterType="com.kuang.pojo.User">
update mybatis.user set name=#{name},pwd=#{pwd} where id = #{id};
</update>
- Test in test class
@Test
public void updateUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int res = mapper.updateUser(new User(4, "ha-ha", "123123"));
if(res > 0){
System.out.println("Modified successfully");
}
sqlSession.commit();
sqlSession.close();
}
Requirement: delete a user according to id
- Add the corresponding method in the UserMapper interface
public interface UserMapper{
// Delete a user
int deleteUser(int id);
}
- In usermapper Add update statement to XML
<delete id="deleteUser" parameterType="int">
delete from mybatis.user where id = #{id}
</delete>
- Test in test class
@Test
public void deleteUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int res = mapper.deleteUser(4);
if(res > 0){
System.out.println("Delete succeeded");
}
sqlSession.commit();
sqlSession.close();
}
Assuming that there are too many entity classes or tables, fields or parameters in the database, we should consider using Map!
5.5.1,select
- Add corresponding method in UserMapper
public interface UserMapper{
// Omnipotent map
User getUserById2(Map<String,Object> map);
}
- In usermapper Add insert statement to XML
<select id="getUserById2" parameterType="map" resultType="com.kuang.pojo.User">
select * from mybatis.user where id = #{helloid} and name = #{name};
</select>
- Test in test class
@Test
public void getUserById2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("helloId",1);
sqlSession.close();
}
- Pass parameters from Map and directly get the key from sql!
5.5.2,insert
- Add corresponding method in UserMapper
public interface UserMapper{
// Omnipotent map
int addUser2(Map<String, Object> map);
}
- In usermapper Add insert statement to XML
<insert id="addUser2" parameterType="map">
insert into mybatis.user(id, name, pwd) VALUES (#{id},#{name},#{pwd})
</insert>
- Test in test class
// Pass key of map
@Test
public void addUser2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap<String, Object> map = new HashMap<>();
map.put("id",5);
map.put("name","La La La");
map.put("pwd","123456");
sqlSession.close();
}
- Object transfer parameters, you can directly get the attribute of the object in sql!
=========================================================================
In the actual development, the SQL statement is not as simple as before, and it is often necessary to dynamically generate SQL statements according to the incoming parameters. Mybatis provides dynamic SQL related tags for us to use.
You can use the if tag to judge the condition. Only when the condition is established will the content in the if tag be spliced into the sql statement.
For example:
<select id="findByCondition" resultType="com.sangeng.pojo.User">
select * from user
where id = #{id}
<if test="username!=null">
and username = #{username}
</if>
</select>
-
If the parameter username is null, the executed sql is: select * from user where id =?
-
If the parameter username is not null, the executed sql is: select * from user where id =? and username = ?
-
Note: you do not need to write #{} the parameter in the test attribute. If you write it, there will be problems.
You can use the label to dynamically add prefixes or suffixes, or you can use the label to dynamically eliminate prefixes.
Note: in fact, the trim tag will not be used in real development. It is mainly to understand the following where tag, so it can be understood here
6.2. 1. Prefixoverride property
It is used to set the prefix to be cleared. Multiple values can be separated by |. Note that there should be no spaces before and after |. For example: and|or
For example:
<select id="findByCondition" resultType="com.sangeng.pojo.User">
select * from user
<trim prefixOverrides="and|or" >
and
</trim>
</select>
The final sql executed is: select * from user
6.2. 2. suffixOverrides property
It is used to set the suffix to be cleared. Multiple values can be separated by |. Note that there should be no spaces before and after |. For example: and|or
<select id="findByCondition" resultType="com.sangeng.pojo.User">
select * from user
<trim suffixOverrides="like|and" >
where 1=1 like
</trim>
</select>
The final sql executed is: select * from user without the suffix like
6.2. 3. prefix attribute
It is used to set the prefix added dynamically. If there is content in the label, the prefix set on it will be added
<select id="findByCondition" resultType="com.sangeng.pojo.User">
select * from user
<trim prefix="where" >
1=1
</trim>
</select>
The final sql executed is: select * from user where 1=1, and the prefix where is dynamically added
6.2. 4. suffix attribute
It is used to set the dynamically added suffix. If there is content in the label, the set suffix will be added
<select id="findByCondition" resultType="com.sangeng.pojo.User">
select * from user
<trim suffix="1=1" >
where
</trim>
</select>
The final sql executed is: select * from user where 1=1, and the suffix 1 = 1 is dynamically added
6.2. 5. trim comprehensive use
Dynamically add the prefix where and eliminate the prefix and or
<select id="findByCondition" resultType="com.sangeng.pojo.User">
select * from user
<trim prefix="where" prefixOverrides="and|or" >
<if test="id!=null">
id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
</trim>
</select>
-
When calling the method, if the passed in id and username are null, the executed SQL is: select * from user
-
When calling the method, if the id passed in is null and the username is not null, the SQL executed is: select * from user where username =?
The where tag is equivalent to dynamically adding the prefix where and eliminating the prefix and or
<trim prefix="where" prefixOverrides="and|or" ></trim>
You can use the where tag to dynamically splice where and remove the prefix and or.
For example:
<select id="findByCondition" resultType="com.sangeng.pojo.User">
select * from user
<where>
<if test="id!=null">
id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
</where>
</select>
-
If both id and username are null, the executed sql is: * * select * from user**
-
If the id is null and the username is not null, the executed sql is: * * select * from user where username =**
The set tag is equivalent to dynamically adding the prefix set and clearing the suffix comma
<trim prefix="set" suffixOverrides="," ></trim>
You can use the set tag to dynamically splice the set and remove the comma of the suffix.
For example:
<update id="updateUser">
UPDATE USER
<set>
<if test="username!=null">
username = #{username},
</if>
<if test="age!=null">
age = #{age},
</if>
<if test="address!=null">
address = #{address},
</if>
</set>
where id = #{id}
</update>
If the ID of the User object passed in when calling the method is 2, the username is not null, and other properties are null, the final sql executed is: update User set username =? where id = ?
You can use the foreach tag to traverse the parameters of a collection or array type, obtain the elements therein, and use them to dynamically splice SQL statements.
For example:
- Define methods in interfaces
public interface UserDao{
List<User> findByIds(@Param("ids") Integer[] ids);
}
If you want to dynamically splice SQL statements according to the length of the actually passed in array. For example, an array with a length of 4 is passed in, and the final executed SQL is:
select * from User WHERE id in( ? , ? , ? , ?, ? )
The following writing method can be used in the xml Mapping file
<select id="findByIds" resultType="com.sangeng.pojo.User">
select * from User
<where>
<foreach collection="ids" open="id in(" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
collection: indicates the parameters to traverse.
open: indicates the statement spliced at the beginning of traversal
item: indicates the name of the currently traversed element
Separator: a separator that indicates splicing after each traversal
close: indicates the statement that traverses the splicing for the last time
Note: if the method parameter is an array type, the default parameter name is array. If the method parameter is a list set, the default parameter name is list. It is recommended that parameters of array or collection types be named with the @ Param annotation.
When we don't want to use all the conditions, but just want to choose one from multiple conditions. You can use the choose series label. Similar to switch in java.
For example:
- Methods in the interface are defined as follows
public interface UserDao{
List<User> selectChose(User user);
}
expect:
If the id of the user object is not empty, query through the id.
If id is null and username is not null, query through username.
If both id and username are null, query the user with id 3
- The xml Mapping file is as follows
<select id="selectChose" resultType="com.sangeng.pojo.User">
select * from user
<where>
<choose>
<when test="id!=null">
id = #{id}
</when>
<when test="username!=null">
username = #{username}
</when>
<otherwise>
id = 3
</otherwise>
</choose>
</where>
</select>
last
Authoritative guide - the first Docker book
Lead the installation, deployment, management and expansion of Docker, let it go through the whole development life cycle from test to production, and deeply understand what scenarios Docker is suitable for. In addition, this authoritative guide to Docker introduces the basic knowledge of its components, and then uses Docker to build containers and services to complete various tasks: use Docker to establish a test environment for new projects, demonstrate how to integrate Docker using continuously integrated workflow, how to build application services and platforms, how to use Docker's API, and how to extend Docker.
It includes nine chapters: introduction, installing Docker, getting started with Docker, using Docker image and warehouse, using Docker in testing, using Docker to build services, using Fig to configure Docker, using Docker API, getting help and improving Docker.
The "K8S+Docker learning guide" strongly recommended by Ali - Kubernetes in simple terms: Theory + practice and authoritative guide - the first Docker book are described in two words after reading. I love it!
Separator**
close: indicates the statement that traverses the splicing for the last time
Note: if the method parameter is an array type, the default parameter name is array. If the method parameter is a list set, the default parameter name is list. It is recommended that parameters of array or collection types be named with the @ Param annotation.
When we don't want to use all the conditions, but just want to choose one from multiple conditions. You can use the choose series label. Similar to switch in java.
For example:
- Methods in the interface are defined as follows
public interface UserDao{
List<User> selectChose(User user);
}
expect:
If the id of the user object is not empty, query through the id.
If id is null and username is not null, query through username.
If both id and username are null, query the user with id 3
- The xml Mapping file is as follows
<select id="selectChose" resultType="com.sangeng.pojo.User">
select * from user
<where>
<choose>
<when test="id!=null">
id = #{id}
</when>
<when test="username!=null">
username = #{username}
</when>
<otherwise>
id = 3
</otherwise>
</choose>
</where>
</select>
last
Authoritative guide - the first Docker book
Lead the installation, deployment, management and expansion of Docker, let it go through the whole development life cycle from test to production, and deeply understand what scenarios Docker is suitable for. In addition, this authoritative guide to Docker introduces the basic knowledge of its components, and then uses Docker to build containers and services to complete various tasks: use Docker to establish a test environment for new projects, demonstrate how to integrate Docker using continuously integrated workflow, how to build application services and platforms, how to use Docker's API, and how to extend Docker.
It includes nine chapters: introduction, installing Docker, getting started with Docker, using Docker image and warehouse, using Docker in testing, using Docker to build services, using Fig to configure Docker, using Docker API, getting help and improving Docker.
[external chain picture transferring... (img-7fzhTzhN-1630834319416)]
[external chain picture transferring... (img-T2BCV3gd-1630834319418)]
[external chain picture transferring... (img-Sja8iB5U-1630834319419)]
[external chain picture transferring... (img-XEGc6dON-1630834319421)]
The "K8S+Docker learning guide" strongly recommended by Ali - Kubernetes in simple terms: Theory + practice and authoritative guide - the first Docker book are described in two words after reading. I love it!