Course objectives

• Build MyBatis development environment
• Complete the operations of adding, deleting, modifying and querying a single table based on annotations
• Complete the operations of adding, deleting, modifying and querying multiple tables based on annotations
• Complete the operations of adding, deleting, modifying and querying a single table based on the general Mapper
• Complete paging operation
• Complete the addition, deletion, modification and query of single table based on XML
• Complete the dynamic SQL operation based on XML
• Complete multi table query based on XML

1. Getting started with mybatis

1.1 general

MyBatis is an excellent persistence layer framework. It encapsulates the process of jdbc operating the database, so that developers only need to pay attention to SQL itself, and do not need to spend energy dealing with complex process codes such as registering drivers, creating connections, creating statements, manually setting parameters, result set search and mapping.

• Historical process

  MyBatis was apache An open source project iBatis In 2010, the project was migrated from apache software foundation to google code and renamed mybatis. In essence, mybatis made some improvements to iBatis.

• Learn more

  Same layer products: Commons dbutils, spring JDBC, Hibernate

1.2 download

The code of MyBaits is managed by github.com at: https://github.com/mybatis/mybatis-3/releases

• You can Copy the teacher's JAR package directly

1.3 comparison with JDBC

• Steps to write DAO using JDBC

  

• Steps for writing DAO with MyBatis

  

1.4 introduction case: setting up environment

1.4.1 construction project

• 1) Create a new project: Java -- > Java EE -- > Web Application

• 2) Create project name: mybatis-demo01

• 3) Add the jar package under the WEB-INF/lib directory (the directory does not exist and needs to be created manually)

• 4) Add jar

1.4.2 database and table: User

# Create database
CREATE DATABASE ssm_db1;
# Use database
USE ssm_db1;
# 1.1 create user table
CREATE TABLE `user` (
  `uid` VARCHAR(32) NOT NULL,
  `username` VARCHAR(20) DEFAULT NULL,	#user name
  `password` VARCHAR(32) DEFAULT NULL,	#password
  `name` VARCHAR(20) DEFAULT NULL,		#nickname
  `email` VARCHAR(30) DEFAULT NULL,		#E-mail
  `telephone` VARCHAR(20) DEFAULT NULL,	#Telephone
  `birthday` DATE DEFAULT NULL,			#birthday
  `sex` VARCHAR(10) DEFAULT NULL,		#Gender
  `state` INT(11) DEFAULT 0,			#Status: 0 = inactive, 1 = activated
  `code` VARCHAR(64) DEFAULT NULL,		#Activation code
  PRIMARY KEY (`uid`)
) ;
# 1.2 initialize user default data
INSERT INTO `user` VALUES ('u001','jack','1234','jack','jack@czxy.com','13612345678','2015-11-04','male',0,NULL);
INSERT INTO `user` VALUES ('u002','rose','1234','shredded meat','rose@czxy.com','13612345679','2015-11-05','female',0,NULL);
INSERT INTO `user` VALUES ('373eb242933b4f5ca3bd43503c34668b','ccc','ccc','aaa','bbb@store.com','15723689921','2015-11-04','male',0,'9782f3e837ff422b9aee8b6381ccf927bdd9d2ced10d48f4ba4b9f187edf7738'),('3ca76a75e4f64db2bacd0974acc7c897','bb','bb','Zhang San','bbb@store.com','15723689921','1990-02-01','male',0,'1258e96181a9457987928954825189000bae305094a042d6bd9d2d35674684e6'),('62145f6e66ea4f5cbe7b6f6b954917d3','cc','cc','Zhang San','bbb@store.com','15723689921','2015-11-03','male',0,'19f100aa81184c03951c4b840a725b6a98097aa1106a4a38ba1c29f1a496c231'),('c95b15a864334adab3d5bb6604c6e1fc','bbb','bbb','Lao Wang','bbb@store.com','15712344823','2000-02-01','male',0,'71a3a933353347a4bcacff699e6baa9c950a02f6b84e4f6fb8404ca06febfd6f'),('f55b7d3a352a4f0782c910b2c70f1ea4','aaa','aaa','Xiao Wang','aaa@store.com','15712344823','2000-02-01','male',1,NULL);

1.5 introduction case: query all

1.5.1 JavaBean: User

package com.czxy.ssm.domain;

import java.util.Date;

/**
 *
 Create Table

 CREATE TABLE `user` (
 `uid` varchar(32) NOT NULL,
 `username` varchar(20) DEFAULT NULL,
 `password` varchar(20) DEFAULT NULL,
 `name` varchar(20) DEFAULT NULL,
 `email` varchar(30) DEFAULT NULL,
 `birthday` date DEFAULT NULL,
 `sex` varchar(10) DEFAULT NULL,
 `state` int(11) DEFAULT NULL,
 `code` varchar(64) DEFAULT NULL,
 PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

 */

public class User {

	private String uid;
	private String username;
	private String password;
	private String name;
	private String email;
	private Date birthday;
	private String sex;
	private Integer state;
	private String code;


	@Override
	public String toString() {
		return "User{" +
				"uid='" + uid + '\'' +
				", username='" + username + '\'' +
				", password='" + password + '\'' +
				", name='" + name + '\'' +
				", email='" + email + '\'' +
				", birthday=" + birthday +
				", sex='" + sex + '\'' +
				", state=" + state +
				", code='" + code + '\'' +
				'}';
	}

	public String getUid() {
		return uid;
	}

	public void setUid(String uid) {
		this.uid = uid;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public Integer getState() {
		return state;
	}

	public void setState(Integer state) {
		this.state = state;
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public User(String uid, String username, String password, String name, String email, Date birthday, String sex, Integer state, String code) {

		this.uid = uid;
		this.username = username;
		this.password = password;
		this.name = name;
		this.email = email;
		this.birthday = birthday;
		this.sex = sex;
		this.state = state;
		this.code = code;
	}

	public User() {

	}
}

1.5.2 writing Dao: UserMapper

In the previous development, we wrote user dao, which is called Mapper in MyBatis.

Therefore, all dao interfaces are named Mapper.

• In MyBatis, you only need to write an interface. The implementation class is automatically generated by MyBatis and executed automatically when testing the program.

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.User;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public interface UserMapper {
    /**
     * Query all
     * @return
     */
    @Select("select * from user")
    public List<User> selectAll();
}

1.5.3 write core configuration file: SqlMapConfig.xml

• Configuration file name: SqlMapConfig.xml

• Configuration file location: src

• Profile content:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

	<!-- Environmental Science: default What does the value of refer to id，It means what database to use-->
	<environments default="db1">
		<!-- Configure database connection information and transactions -->
		<environment id="db1">
			<!-- Indicates the use of transactions: the default is to automatically start transactions -->
			<transactionManager type="JDBC" />
			<!-- Use connection pool -->
			<dataSource type="POOLED">
				<!-- Basic information of database connection -->
				<property name="driver" value="com.mysql.cj.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/ssm_db1" />
				<property name="username" value="root" />
				<property name="password" value="1234" />
			</dataSource>
		</environment>
	</environments>

	<mappers>
		<!-- Indicates that all files under this package are loaded dao Interface-->
		<package name="com.czxy.ssm.mapper"/>
	</mappers>
</configuration>

1.5.4 testing

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class Test01_SelectAll {
    public static void main(String[] args) throws IOException {

        //1 load configuration file
        // 1.1 obtaining resource flows
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 1.2 obtaining factory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //2 get session (connection)
        SqlSession session = factory.openSession();

        //3 obtain functional interface
        UserMapper userMapper = session.getMapper(UserMapper.class);

        //4 call function
        List<User> userList = userMapper.selectAll();

        //5. Print query results
        for (User user : userList) {
            System.out.println(user);
        }
        
        //6 release resources
        session.close();
    }

}

1.6 summary

• Here, the introductory case of MyBatis has been completed. Let's summarize:
  • Write SqlMapConfig.xml to configure the data source and Mapper to be loaded
  • Write the UserMapper.java interface, which is used to execute the binding of method and SQL statement
  • The basic API uses the following processes: loading resources, obtaining factories, obtaining sessions, and obtaining Mapper.

2. Basic operation: add, delete, modify and query

2.1 fuzzy query

• Methods in functional interfaces

  • If the parameter is of simple type, the sql statement needs to use value [not recommended]

    @Select("select * from user where name like '%${value}%'")
    public List<User> selectByName(String name);
    

  • If @ Param is used, it can be named accordingly [recommended]

    @Select("select * from user where name like '%${name}%'")
    public List<User> selectByName(@Param("name") String name);
    

  • Parameter substitution summary

    • #{}, replace all, for example: where uid = #{uid}
    • ， Department branch for change ， example as : w h e r e n a m e l i k e ′ {}, partial replacement, for example: where name like '% 1' , partial replacement, for example: where namelike '{name}%'

• Test class

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class Test02_Like {
    public static void main(String[] args) throws IOException {

        //1 load configuration file
        // 1.1 obtaining resource flows
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 1.2 obtaining factory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //2 get session (connection)
        SqlSession session = factory.openSession();

        //3 obtain functional interface
        UserMapper userMapper = session.getMapper(UserMapper.class);

        //4 call function
        List<User> userList = userMapper.selectByName("king");

        //5. Print query results
        for (User user : userList) {
            System.out.println(user);
        }
    }

}

2.2 insert data

• Methods in functional interfaces

    /**
     * insert data
     * @param user
     */
    @Insert("insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})")
    public Integer insert(User user);

• Test class (Note: transactions need to be committed)

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class Test03_Insert {
    public static void main(String[] args) throws IOException {

        //1 load configuration file
        // 1.1 obtaining resource flows
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 1.2 obtaining factory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //2 get session (connection)
        SqlSession session = factory.openSession();

        //3 obtain functional interface
        UserMapper userMapper = session.getMapper(UserMapper.class);

        //4 call function
        User user = new User();
        user.setUid("1");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("jack");
        user.setEmail("itcast_lt@163.com");
        user.setBirthday(new Date());
        user.setSex("male");
        user.setSex("0");
        Integer result = userMapper.insert(user);
        System.out.println(result);

        //5 submission of resources
        session.commit();

        //6 release resources
        session.close();

    }
}

2.3 update data

• Methods in functional interfaces

    /**
     * insert data
     * @param user
     */
    @Insert("update user set username=#{username}, password=#{password}, name=#{name}, email=#{email},birthday=#{birthday},sex=#{sex}, state=#{state} where uid=#{uid}")
    public Integer update(User user);

• Test class

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class Test04_Update {
    public static void main(String[] args) throws IOException {

        //1 load configuration file
        // 1.1 obtaining resource flows
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 1.2 obtaining factory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //2 get session (connection)
        SqlSession session = factory.openSession();

        //3 obtain functional interface
        UserMapper userMapper = session.getMapper(UserMapper.class);

        //4 call function
        User user = new User();
        user.setUid("1");
        user.setUsername("jack1");
        user.setPassword("12341");
        user.setName("jack");
        user.setEmail("itcast_lt@163.com");
        user.setBirthday(new Date());
        user.setSex("male");
        user.setSex("0");
        Integer result = userMapper.update(user);
        System.out.println(result);

        //5 submission of resources
        session.commit();

        //6 release resources
        session.close();


    }

}

2.4 deleting data

• Methods in functional interfaces

    /**
     * Delete by id
     * @param uid
     */
    @Delete("delete from user where uid = #{uid}")
    public Integer deleteByPrimaryKey(@Param("uid") Integer uid);

• Test class

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class Test05_Delete {
    public static void main(String[] args) throws IOException {

        //1 load configuration file
        // 1.1 obtaining resource flows
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 1.2 obtaining factory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //2 get session (connection)
        SqlSession session = factory.openSession();

        //3 obtain functional interface
        UserMapper userMapper = session.getMapper(UserMapper.class);

        //4 call function

        Integer result = userMapper.deleteByPrimaryKey(1);
        System.out.println(result);

        //5 submission of resources
        session.commit();

        //6 release resources
        session.close();


    }

}

3. Logs and tools

3.1 log

3.1.1 what is a log

• Record the process details of program operation. For example, we can see SQL statements on the console

3.1.2 integration log

• 1) Add jar package (added)

  

• 2) Add profile

  

# 2. Output format
## log4j.appender.stdout = output position (fixed value, provided by log4j)
## log4j.appender.stdout.Target = method
## log4j.appender.stdout.layout = layout (fixed value)
## log4j.appender.stdout.layout.ConversionPattern = format

# 2.1 output log to console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c:%L - %m%n

# 2.2 output log to file
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=d:/file.log
log4j.appender.file.Append=false
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c:%L - %m%n

#1.log4j.rootLogger = log level, output mode 1, output mode 2
## Log level: debug, info, warn, error
log4j.rootLogger=debug, stdout, file

# 3. Custom log level
## log4j.logger. Package = log level

#log4j.logger.com.ibatis = debug    
#log4j.logger.com.ibatis.common.jdbc.SimpleDataSource = debug    
#log4j.logger.com.ibatis.common.jdbc.ScriptRunner = debug    
#log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate = debug    
#log4j.logger.java.sql.Connection = debug    

#log4j.logger.java.sql.Statement = debug    
#log4j.logger.java.sql.PreparedStatement = debug    
#log4j.logger.java.sql.ResultSet =debug

log4j.logger.org.apache.ibatis.transaction = info
log4j.logger.org.apache.ibatis.io = info
log4j.logger.org.apache.ibatis.datasource = info
log4j.logger.org.apache.ibatis.logging = info

3.2 tools

3.2.1 copying tools

• When we complete the addition, deletion, modification and query, we find a large number of duplicate codes. We will provide tool classes to simplify the duplicate codes.

  

package com.czxy.ssm.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class MyBatisUtils {

    // Session factory
    private static SqlSessionFactory factory;
    static{
        try {
            // 1.1 loading core configuration file
            InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
            // 1.2 obtaining factory
            factory = new SqlSessionFactoryBuilder().build(is);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private static ThreadLocal<SqlSession> local = new ThreadLocal<>();

    /**
     * Get a new meeting
     * @return
     */
    private static SqlSession openSession(){
        SqlSession sqlSession = local.get();
        if(sqlSession == null){
            sqlSession = factory.openSession();
            local.set(sqlSession);
        }
        return sqlSession;
    }

    /**
     * Get mapper
     * @param clazz
     * @return
     */
    public static <T> T getMapper(Class<T> clazz){
        return openSession().getMapper(clazz);
    }

    /**
     * Release resources
     */
    public static void close() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.close();
        }
    }

    /**
     * Commit and release resources
     */
    public static void commitAndClose() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.commit();
            close();
        }
    }

    /**
     * Rollback and free resources
     */
    public static void rollbackAndClose() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.rollback();
            close();
        }
    }

}

3.2.2 principle analysis of tools

• Principle analysis of tool class implementation

3.2.3 testing

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;

import java.util.Date;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class Test06_Utils {
    public static void main(String[] args) {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = new User();
        user.setUid("1");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("jack");
        user.setEmail("itcast_lt@163.com");
        user.setBirthday(new Date());
        user.setSex("male");
        user.setSex("0");
        Integer result = userMapper.insert(user);
        System.out.println(result);
        MyBatisUtils.commitAndClose();
    }
}

4.API details

4.1 detailed explanation of core configuration file

In the actual development, the developer will prepare the configuration file template in advance, then Copy the template to the project, and then modify the important configuration parameters. In this way, developers do not need to memorize the structure of the configuration file. But developers still need to be able to read configuration files. Let's learn about the MyBatis core configuration file.

4.1.1 root label: < configuration >

• When configuring sub tags, there is a sequence between multiple tags, which must be paid attention to when using.

• The following is the order of use of the four commonly used labels:

  <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE configuration
  	PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  	"http://mybatis.org/dtd/mybatis-3-config.dtd">
  <configuration>
  	<properties></properties>
  	
  	<typeAliases></typeAliases>
  
  	<environments></environments>
  
  	<mappers></mappers>
  </configuration>
  

• Introduction to common sub Tags

Label name	describe
properties	Property configuration, or external properties files can be imported
settings	System parameter configuration. All parameters have default values, which are usually not configured
typeAliases	Type aliases simplify development and only reduce the redundancy of class fully qualified names. For xml development.
plugins	Plug in for adding extension functions.
environments	Environment configuration, which provides factory configuration information
mappers	Mapper to determine the location of SQL statements

4.1.2 properties: < Properties >

Extract easily modified configuration contents into properties for unified management. mybatis provides two ways to maintain properties: the property tag and the properties configuration file.

Method 1: property tag

• 1) Configuration: configure the required content in < property name = "Id" value = "value" / >

  <properties>
  	<property name="jdbc.driver" value="com.mysql.cj.jdbc.Driver"/>
  	<property name="jdbc.url" value="jdbc:mysql://localhost:3306/ssm_db1"/>
  	<property name="jdbc.username" value="root"/>
  	<property name="jdbc.password" value="1234"/>
  </properties>
  

• 2) Use: get content through ${identity} elsewhere in the configuration file

  <!-- Use connection pool -->
  <dataSource type="POOLED">
  	<!-- Basic information of database connection -->
  	<property name="driver" value="${jdbc.driver}"/>
  	<property name="url" value="${jdbc.url}"/>
  	<property name="username" value="${jdbc.username}"/>
  	<property name="password" value="${jdbc.password}"/>
  </dataSource>
  

• Overall schematic diagram

Method 2: properties configuration file

In the actual development, the configuration information of our common database is stored in the properties file, and mybatis also provides support for the configuration file.

• 1) Add db.properties configuration file

  jdbc.driver=com.mysql.cj.jdbc.Driver
  jdbc.url=jdbc:mysql://localhost:3306/ssm_db1
  jdbc.username=root
  jdbc.password=1234
  

• 2) Load the properties file and modify the core configuration file

  <properties resource="db.properties" />
  

• Overall schematic diagram

matters needing attention

• The contents loaded by the properties configuration file will overwrite the contents of the < property > tag configuration.
  • Load the content of < property > tag configuration first
  • Then load the contents of the properties configuration file specified by the resource, that is, the latter overwrites the former.

<!--last url The value of is db.properties Content of configuration -->
<properties resource="db.properties" >
	<property name="jdbc.url" value="jdbc:mysql://localhost:3306/ssm_db2"/>
</properties>

<!-- [[it is not recommended to mix and match in development] -->

4.1.3 settings: < Settings >

• MyBatis framework system configuration settings are used to change the runtime behavior of MyBatis.

• mybatis provides a variety of settings for system optimization.

Set parameters	describe	Effective value	Default value
mapUnderscoreToCamelCase	Whether to enable automatic hump naming rule mapping, that is, from the classic database column name a_ A similar mapping from column to the classic Java property name aColumn.	true | false	false
autoMappingBehavior	Specifies whether and how MyBatis automatically maps the specified column to a field or attribute. NONE means to cancel automatic mapping; PARTIAL automatically maps only result sets that do not have nested result set mappings defined. FULL automatically maps any complex result set	NONE, PARTIAL, FULL	PARTIAL
useGeneratedKeys	JDBC is allowed to support automatic generation of primary keys. Driver compatibility is required.	true | false	false
useColumnLabel	Use column labels instead of column names.	true | false	true
cacheEnabled	This configuration affects the global switch of the cache configured in all mappers.	true | false	true
lazyLoadingEnabled	Global switch for delayed loading.	true | false	false

• Hump naming settings

	<settings>
		<!-- java Object attribute hump, corresponding to the underline of the data -->
		<setting name="mapUnderscoreToCamelCase" value="true"/>
	</settings>

4.1.4 environment: < environments >

• MyBatis configures database connection and transaction management through environments.

• Determine which configuration to use

  <!--
  	default Used to determine which configuration the current environment uses
  	<environment id="">It is used to provide a set of configuration scheme, id The value of is provided to default use.
  -->
  <environments default="Configuration 2">
  	<environment id="Configuration 1">
  	</environment>
  	
  	<environment id="Configuration 2">
  	</environment>
  </environments>
  

• Configuration content

  <!--
  	<transactionManager> Used to configure transaction management scenarios
  		type: Used to determine the specific management transaction scheme. Value: JDBC,MANAGED
  			JDBC，jdbc Underlying transaction management, commit Submitted, rollback Review.
  			MANAGED，It is managed by other frameworks. It does not do transaction operations, but only closes the connection.
  	<dataSource>Used to configure connection pools (pools of multiple connections)
  		type: It is used to determine the scheme of management connection. The value is: POOLED,UNPOOLED,JNDI
  			POOLED: Manage connection creation and destruction through connection pool.
  			UNPOOLED: Instead of using connection pools, connections are created and destroyed each time.
  			JNDI: Save connection pool to JNDI Yes.
  -->
  <environment id="development">
  	<!-- use jdbc Management services -->
  	<transactionManager type="JDBC"></transactionManager>
  	<!-- Configure data sources(configure connections) -->
  	<dataSource type="POOLED">
  		<property name="driver" value="${jdbc.driver}"/>
  		<property name="url" value="${jdbc.url}"/>
  		<property name="username" value="${jdbc.username}"/>
  		<property name="password" value="${jdbc.password}"/>
  	</dataSource>
  </environment>
  
  

• Extension (check the source code to determine the type configuration content)

  1. Configuration item for transaction

     

  2. Configuration item for connection pool

     

  3. MANAGED closes only the connection regardless of the transaction

     

4.1.5 mappers: < mappers >

• Load all interfaces of the specified package.

  <!-- Mapping file -->
  <mappers>
  	<package name="com.czxy.ssm.mapper"/>
  </mappers>
  

4.2 input: vo condition query

In development, the query conditions of an object are more than the attributes encapsulated in the object. For example, user has birthday. We need to query the data from 2010 to 2020. The time period beginTime and endTime do not provide attributes in the user object. In actual development, a user-defined object is generally provided, such as UserVo.

• 1) Write vo

  package com.czxy.ssm.vo;
  
  /**
   * @author Uncle Tong
   * @email liangtong@itcast.cn
   */
  public class UserVo {
      private String beginTime;       // start time
      private String endTime;         // End time
  
      public UserVo() {
      }
  
      public UserVo(String beginTime, String endTime) {
          this.beginTime = beginTime;
          this.endTime = endTime;
      }
  
      public String getBeginTime() {
          return beginTime;
      }
  
      public void setBeginTime(String beginTime) {
          this.beginTime = beginTime;
      }
  
      public String getEndTime() {
          return endTime;
      }
  
      public void setEndTime(String endTime) {
          this.endTime = endTime;
      }
  }
  
  

• 2) Modify Mapper and add condition method

  

      /**
       * Condition query
       * @param userVo
       * @return
       */
      @Select("select * from user where birthday >= #{beginTime} and birthday <= #{endTime}")
      public List<User> condition(UserVo userVo);
  

• 3) Test class

  package com.czxy.ssm.test;
  
  import com.czxy.ssm.domain.User;
  import com.czxy.ssm.mapper.UserMapper;
  import com.czxy.ssm.utils.MyBatisUtils;
  import com.czxy.ssm.vo.UserVo;
  
  import java.util.Date;
  import java.util.List;
  
  /**
   * @author Uncle Tong
   * @email liangtong@itcast.cn
   */
  public class Test07_Condition {
      public static void main(String[] args) {
          UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);
  
          List<User> list = userMapper.condition(new UserVo("2010-01-01", "2021-01-01"));
          // Print
          list.forEach(user -> {
              System.out.println(user);
          });
  
          MyBatisUtils.commitAndclose();
      }
  }
  
  

4.3 output: Result set mapping Result

In actual development, if the database field and Java object field cannot correspond, we need to write the corresponding relationship.

annotation	describe
@Results	Mapping between objects and tables.
@Result	The mapping relationship between an object attribute and a table field.
@ResultMap	Mapping relationship, using the mapping relationship declared by @ Results.

4.3.1 basic use

• grammar

/** 
	@Results(value = A set of results)
*/    
@Results(value = {
     @Result(property = "Attribute name", column = "Field name", id = true),
     @Result(property = "Property name 2", column = "Field name 2"),
     @Result(property = "Property name 3", column = "Field name 3")
})

• Basic use

    /**
     * Query all
     * @return
     */
    @Select("select * from user")
    @Results({
            @Result(property = "uid", column = "uid", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password")
    })
    public List<User> selectAll();

4.3.2 reuse

• grammar

  @Select(...)
  @Results(id = "identification", value = {...})
  public Return value method name();
  
  @Select(...)
  @ResultMap("identification")
  public Return value method name 2();
  

• use

      /**
       * Query all
       * @return
       */
      @Select("select * from user")
      @Results(id = "userResult", value = {
              @Result(property = "uid", column = "uid", id = true),
              @Result(property = "username", column = "username"),
              @Result(property = "password", column = "password")
      })
      public List<User> selectAll();
  
      /**
       * Query details by id
       * @param uid
       * @return
       */
      @Select("select * from user where uid = #{uid}")
      @ResultMap("userResult")
      public User selectById(@Param("uid") String uid);
  

4.4 summary of existing notes

Annotation name	describe
@Insert	Add sql
@Update	Update sql
@Delete	Delete sql
@Select	Query sql
@Param	Formal parameter naming
@Results	Mapping between objects and tables.
@Result	The mapping relationship between an object attribute and a table field.

5. Association query: one to many

5.1 user and order data model

Table 5.1.1 relationship

CREATE TABLE `orders` (
  `oid` VARCHAR(32) PRIMARY KEY NOT NULL,
  `ordertime` DATETIME DEFAULT NULL,		#Order time 
  `total_price` DOUBLE DEFAULT NULL,		#Total price
  `state` INT(11) DEFAULT NULL,			#Order status: 1 = unpaid; 2 = paid but not delivered; 3 = shipped, confiscated; 4 = receiving, order end
  `address` VARCHAR(30) DEFAULT NULL,		#Harvest address
  `name` VARCHAR(20) DEFAULT NULL,		#Reaper
  `telephone` VARCHAR(20) DEFAULT NULL,		#Consignee telephone
  `uid` VARCHAR(32) DEFAULT NULL,
  CONSTRAINT `order_fk_0001` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`)
) ;

INSERT INTO `orders` VALUES ('x001','2010-10-10',10,1,'Jiangsu','Zhang San','12345','u001');
INSERT INTO `orders` VALUES ('x002','2010-11-11',20,2,'Hebei','Li Si','67890','u001');
INSERT INTO `orders` VALUES ('x003','2011-10-10',30,3,'Shanxi','Wang Wu','66666','u002')

5.1.2 JavaBean

• By default, two JavaBean s are not related

package com.czxy.ssm.domain;

import java.util.Date;

/**
 *
 Create Table

 CREATE TABLE `orders` (
 `oid` varchar(32) NOT NULL,
 `ordertime` datetime DEFAULT NULL,
 `total` double DEFAULT NULL,
 `state` int(11) DEFAULT NULL,
 `address` varchar(30) DEFAULT NULL,
 `name` varchar(20) DEFAULT NULL,
 `telephone` varchar(20) DEFAULT NULL,
 `uid` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`oid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

 */
public class Order {

	private String oid; 		// Order No
	private Date ordertime; 	// Order time 
	private Double total; 		// Total order amount
	private Integer state; 		// Order status 0 unpaid 1 paid 2 shipped 3 received
	private String address;		// Consignee address
	private String name;		// Consignee Name 
	private String telephone;	// Consignee telephone
	private String uid;

	@Override
	public String toString() {
		return "Order{" +
				"oid='" + oid + '\'' +
				", ordertime=" + ordertime +
				", total=" + total +
				", state=" + state +
				", address='" + address + '\'' +
				", name='" + name + '\'' +
				", telephone='" + telephone + '\'' +
				", uid='" + uid + '\'' +
				'}';
	}

	public String getOid() {
		return oid;
	}

	public void setOid(String oid) {
		this.oid = oid;
	}

	public Date getOrdertime() {
		return ordertime;
	}

	public void setOrdertime(Date ordertime) {
		this.ordertime = ordertime;
	}

	public Double getTotal() {
		return total;
	}

	public void setTotal(Double total) {
		this.total = total;
	}

	public Integer getState() {
		return state;
	}

	public void setState(Integer state) {
		this.state = state;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getTelephone() {
		return telephone;
	}

	public void setTelephone(String telephone) {
		this.telephone = telephone;
	}

	public String getUid() {
		return uid;
	}

	public void setUid(String uid) {
		this.uid = uid;
	}


	public Order(String oid, Date ordertime, Double total, Integer state, String address, String name, String telephone, String uid) {
		this.oid = oid;
		this.ordertime = ordertime;
		this.total = total;
		this.state = state;
		this.address = address;
		this.name = name;
		this.telephone = telephone;
		this.uid = uid;
	}

	public Order() {

	}
}

5.1.3 JavaBean relationships

• Describe the relationship between two JavaBean s in the method of object

• JavaBean: User

  public class User {
  
  	private String uid;
  	private String username;
  	private String password;
  	private String name;
  	private String email;
  	private Date birthday;
  	private String sex;
  	private Integer state;
  	private String code;
  	// One to many: one user has multiple users
  	private List<Order> orderList = new ArrayList<>();
      // ...
  }
  

• JavaBean: Order

  public class Order {
  
  	private String oid; 		// Order No
  	private Date ordertime; 	// Order time 
  	private Double total; 		// Total order amount
  	private Integer state; 		// Order status 0 unpaid 1 paid 2 shipped 3 received
  	private String address;		// Consignee address
  	private String name;		// Consignee Name 
  	private String telephone;	// Consignee telephone
  	private String uid;
  	// Many to one. Multiple orders belong to one user
  	private User user;
      // ...
  }
  

5.2 one to many

5.2.1 syntax

In Mybatis annotation development, the association relationship needs to be described through @ Result.

• One to Many: you need to use the Many attribute and the @ Many annotation.

@Result(
    property = "1 surface JavaBean Attribute name", 
    column = "1 Table field name",
    many = @Many(select = "Multi table Mapper Method signature for")
)

5.2.2 demand & Analysis

• Demand: query the order corresponding to each user while querying users
• analysis:
  • Modify OrderMapper to query all orders through uid
  • Modify UserMapper to query the corresponding order when querying user information

5.2.3 order function: query all orders through uid

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public interface OrderMapper {

    /**
     * Query details by id
     * @param uid
     * @return
     */
    @Select("select * from orders where uid = #{uid}")
    public Order findOrdersByUserId(@Param("uid") String uid) ;

}

5.2.4 user function: association query

public interface UserMapper {
    /**
     * Query all
     * @return
     */
    @Select("select * from user")
    @Results(id = "userResult", value = {
            @Result(property = "uid", column = "uid", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password"),
            @Result(property="orderList" , many=@Many(select="com.czxy.ssm.mapper.OrderMapper.findOrdersByUserId"), column="uid")
    })
    public List<User> selectAll();

}

5.3 many to one

5.3.1 syntax

In Mybatis annotation development, the association relationship needs to be described through @ Result.

• Many to One: you need to use the One attribute and the @ One annotation

@Result(
    property = "Multi table JavaBean Attribute name", 
    column = "Multi table field name",
    one = @One(select ="1 surface Mapper Method signature for")
)

5.3.2 demand & Analysis

• Demand: when querying an order, query the associated user information

• analysis:

  1) Via user_id query user details

  2) Query order details by id

5.4.3 user function: query user details through id (existing)

    /**
     * Query details by id
     * @param uid
     * @return
     */
    @Select("select * from user where uid = #{uid}")
    @ResultMap("userResult")
    public User selectById(@Param("uid") String uid);

5.4.4 order function: query order details through id

    /**
     *
     * @param id
     * @return
     */
    @Select("select * from orders where oid = #{oid}")
    @Results({
            @Result(property="oid" , column="oid"),
            @Result(property="ordertime" , column="ordertime"),
            @Result(property="total" , column="total"),
            @Result(property="state" , column="state"),
            @Result(property="address" , column="address"),
            @Result(property="name" , column="name"),
            @Result(property="telephone" , column="telephone"),
            @Result(property="uid" , column="uid"),
            @Result(property="user" , one=@One(select="com.czxy.ssm.mapper.UserMapper.selectById") , column="uid"),
    })

    public Order selectById(@Param("oid") String id);

6. Association query: many to many

6.1 student and teacher data model

6.1.1 relationship between tables

#Teacher table
CREATE TABLE teacher(
 tid INT PRIMARY KEY,
 NAME VARCHAR(50)
); 
#Student list
CREATE TABLE student(
 sid INT PRIMARY KEY,
 NAME VARCHAR(50)
);
#Intermediate table
CREATE TABLE teacher_student(
 teacher_id INT ,
 student_id INT,
 CONSTRAINT ts_t_fk FOREIGN KEY (teacher_id) REFERENCES teacher(tid),
 CONSTRAINT ts_s_fk FOREIGN KEY (student_id) REFERENCES student(sid)
);

INSERT INTO teacher VALUES (1,'Miss Xiao'); 
INSERT INTO teacher VALUES (2,'Miss Ma'); 

INSERT INTO student VALUES (1,'Zhang San');
INSERT INTO student VALUES (2,'Li Si');
INSERT INTO student VALUES (3,'Wang Wu');

INSERT INTO teacher_student VALUES (1,1);
INSERT INTO teacher_student VALUES (1,2);
INSERT INTO teacher_student VALUES (1,3);
INSERT INTO teacher_student VALUES (2,1);
INSERT INTO teacher_student VALUES (2,2);

6.1.2 JavaBean s and their relationships

• JavaBean: Student

  package com.czxy.ssm.domain;
  
  import java.util.ArrayList;
  import java.util.List;
  
  /**
   * #Student list
   * CREATE TABLE student(
   *  sid INT PRIMARY KEY,
   *  NAME VARCHAR(50)
   * );
   * @author Uncle Tong
   * @email liangtong@itcast.cn
   */
  public class Student {
      private Integer sid;
      private String name;
  
      private List<Teacher> teacherList = new ArrayList<>();
  
      public Integer getSid() {
          return sid;
      }
  
      public void setSid(Integer sid) {
          this.sid = sid;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      public List<Teacher> getTeacherList() {
          return teacherList;
      }
  
      public void setTeacherList(List<Teacher> teacherList) {
          this.teacherList = teacherList;
      }
  
      @Override
      public String toString() {
          return "Student{" +
                  "sid=" + sid +
                  ", name='" + name + '\'' +
                  ", teacherList=" + teacherList +
                  '}';
      }
  }
  

• JavaBean: Teacher

  package com.czxy.ssm.domain;
  
  import java.util.ArrayList;
  import java.util.List;
  
  /**
   * #Teacher table
   * CREATE TABLE teacher(
   *  tid INT PRIMARY KEY,
   *  NAME VARCHAR(50)
   * );
   * @author Uncle Tong
   * @email liangtong@itcast.cn
   */
  public class Teacher {
      private Integer tid;
      private String name;
  
      private List<Student> studentList = new ArrayList<>();
  
      public Integer getTid() {
          return tid;
      }
  
      public void setTid(Integer tid) {
          this.tid = tid;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      public List<Student> getStudentList() {
          return studentList;
      }
  
      public void setStudentList(List<Student> studentList) {
          this.studentList = studentList;
      }
  
      @Override
      public String toString() {
          return "Teacher{" +
                  "tid=" + tid +
                  ", name='" + name + '\'' +
                  ", studentList=" + studentList +
                  '}';
      }
  }
  
  

6.2 many to many: teachers – > students

• You need to query all the corresponding student IDS in the m id dle table according to the teacher tid

6.2.1 student mapping

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public interface StudentMapper {
    /**
     * Query the corresponding students through tid
     * @param tid
     * @return
     * @throws Exception
     */
    @Select("select * from student s where s.sid in (select student_id from teacher_student where teacher_id = #{tid} )")
    public Student findStudentByTeacherId(@Param("tid") Integer tid) throws Exception;
}

6.2.2 teacher mapping

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Teacher;
import org.apache.ibatis.annotations.*;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public interface TeacherMapper {

    @Select("select * from teacher t where  t.tid = #{tid}")
    @Results({
            @Result(property="tid" , column="tid"),
            @Result(property="name" , column="name"),
            @Result(property="studentList" , many=@Many(select="com.czxy.ssm.mapper.StudentMapper.findStudentByTeacherId") , column="tid"),
    })
    public Teacher selectById(@Param("tid") Integer tid);

}

6.2.3 testing

package com.czxy.ssm.test;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.Teacher;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.TeacherMapper;
import com.czxy.ssm.utils.MyBatisUtils;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class Test09_SelectTeacher {
    public static void main(String[] args) {
        TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class);

        Teacher teacher = teacherMapper.selectById(1);
        // Print
        System.out.println(teacher);

        MyBatisUtils.commitAndclose();
    }
}

6.2.4 consolidation exercise

• Query all teachers and students corresponding to teachers.

  package com.czxy.ssm.mapper;
  
  import com.czxy.ssm.domain.Teacher;
  import org.apache.ibatis.annotations.*;
  
  import java.util.List;
  
  /**
   * @author Uncle Tong
   * @email liangtong@itcast.cn
   */
  public interface TeacherMapper {
  
      /**
       * Query details by id
       * @param tid
       * @return
       */
      @Select("select * from teacher t where  t.tid = #{tid}")
      @Results(id = "teacherResult" , value = {
              @Result(property="tid" , column="tid"),
              @Result(property="name" , column="name"),
              @Result(property="studentList" , many=@Many(select="com.czxy.ssm.mapper.StudentMapper.findStudentByTeacherId") , column="tid"),
      })
      public Teacher selectById(@Param("tid") Integer tid);
  
      /**
       * Query all
       * @return
       */
      @Select("select * from teacher")
      @ResultMap("teacherResult")
      public List<Teacher> selectList();
  
  }
  

• Test class

  package com.czxy.ssm.test;
  
  import com.czxy.ssm.domain.Teacher;
  import com.czxy.ssm.mapper.TeacherMapper;
  import com.czxy.ssm.utils.MyBatisUtils;
  
  import java.util.List;
  
  /**
   * @author Uncle Tong
   * @email liangtong@itcast.cn
   */
  public class Test10_SelectAllTeacher {
      public static void main(String[] args) {
          TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class);
  
          List<Teacher> teacherList = teacherMapper.selectList();
          // Print
          teacherList.forEach(teacher -> {
              System.out.println(teacher);
          });
  
          MyBatisUtils.commitAndclose();
      }
  }
  

7. Paging query

MyBatis does not provide paging support. You need to write your own limit statement.

In the development, we use the PageHelper plug-in.

7.1 construction environment

7.1.1 import jar package

7.1.2 adding plug-ins

	<!-- plug-in unit -->
	<plugins>
		<plugin interceptor="com.github.pagehelper.PageHelper">
			<!-- dialect -->
			<property name="dialect" value="mysql"/>
			<!-- Set to true When using RowBounds Paging occurs count query -->
			<property name="rowBoundsWithCount" value="true"/>
		</plugin>
	</plugins>

7.2 grammar

1) Set paging data
PageHelper.startPage(int pageNum, int pageSize)
	Parameter 1: pageNum What page
	Parameter 2: pageSize Number of page displays
2) Encapsulate paging results PageInfo
	new PageInfo(Query results)	  //Create paging object
	pageInfo.getTotal(),	//Total number of automatic queries
	pageInfo.getPages(),	//Total pages

7.3 use

package com.czxy.ssm.test;

import com.czxy.ssm.domain.Teacher;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.TeacherMapper;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

import java.util.List;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class Test11_Page {
    public static void main(String[] args) {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        // Set paging**
        PageHelper.startPage(1,2);

        // query
        List<User> users = userMapper.selectAll();

        // Get encapsulated object** 
        PageInfo<User> pageInfo = new PageInfo<>(users);

        // Print paging information
        long total = pageInfo.getTotal();
        List<User> list = pageInfo.getList();
        System.out.println("Total number of pieces:" + total);
        System.out.println("Paging data:");
        list.forEach(user -> {
            System.out.println(user);
        });


        MyBatisUtils.commitAndclose();
    }
}

8. General Mapper

8.1 general

• General Mapper is a third-party toolkit for simplifying MyBatis.

• General Mapper provides an interface called Mapper < T > to automatically complete the addition, deletion, modification and query of a single table.

public interface UserMapper extends Mapper<User> {
}

• If the methods in general Mapper are not enough to meet your needs, you can directly add custom methods.

8.2 construction environment

8.2.1 importing jar

8.2.2 modifying tools

• Add content copy from official documents

package com.czxy.ssm.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
import tk.mybatis.mapper.entity.Config;
import tk.mybatis.mapper.mapperhelper.MapperHelper;

import java.io.InputStream;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class MyBatisUtils {

    // Session factory
    private static SqlSessionFactory factory;
    static{
        try {
            // 1.1 loading core configuration file
            InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
            // 1.2 obtaining factory
            factory = new SqlSessionFactoryBuilder().build(is);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private static ThreadLocal<SqlSession> local = new ThreadLocal<>();

    /**
     * Get a new meeting
     * @return
     */
    private static SqlSession openSession(){
        SqlSession sqlSession = local.get();
        if(sqlSession == null){
            sqlSession = factory.openSession();

            //Create a MapperHelper
            MapperHelper mapperHelper = new MapperHelper();
            //Special configuration
            Config config = new Config();
            // Set UUID generation policy
            // The OGNL expression is required to configure the UUID generation policy
            // Default 32-bit length: @ Java. Util UUID@randomUUID ().toString().replace("-", "")
            //config.setUUID("");
            // The default value is MYSQL. Please refer to the document for details
//	        config.setIDENTITY("HSQLDB");
            // Support annotations on Methods
            // 3.3.1 version added
            config.setEnableMethodAnnotation(true);
            config.setNotEmpty(true);
            // The sequence acquisition rule uses the {num} format parameter. The default value is {0}.nextval, which is for Oracle
            // There are 3 optional parameters, corresponding to 0, 1 and 2, respectively SequenceName, columnname and propertyname
            //config.setSeqFormat("NEXT VALUE FOR {0}");
            // Set the global catalog, which is empty by default. If the value is set, the sql when operating the table will be catalog.tablename
            //config.setCatalog("");
            // Set the global schema, which is empty by default. If a value is set, the sql when operating the table will be schema.tablename
            // If catalog is also set, catalog.tablename is preferred
            //config.setSchema("");
            // The execution sequence of the PK auto increment and write back method is AFTER by default, and the optional value is (BEFORE|AFTER)
            //config.setOrder("AFTER");
            //set configuration
            mapperHelper.setConfig(config);
            // Register the general tk.mybatis.mapper.common.Mapper interface - inherited interfaces can be registered automatically
            mapperHelper.registerMapper(Mapper.class);
            mapperHelper.registerMapper(MySqlMapper.class);
//	        mapperHelper.registerMapper(SqlServerMapper.class);
//	        mapperHelper.registerMapper(IdsMapper.class);
            //After the configuration is completed, perform the following operations
            mapperHelper.processConfiguration(sqlSession.getConfiguration());


            local.set(sqlSession);
        }
        return sqlSession;
    }

    /**
     * Get mapper
     * @param clazz
     * @return
     */
    public static <T> T getMapper(Class<T> clazz){
        return openSession().getMapper(clazz);
    }

    /**
     * Release resources
     */
    public static void close() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.close();
        }
    }

    /**
     * Commit and release resources
     */
    public static void commitAndclose() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.commit();
            close();
        }
    }

    /**
     * Rollback and free resources
     */
    public static void rollbackAndclose() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.rollback();
            close();
        }
    }

}

8.3 writing Mapper

• Write the interface and inherit the tk.mybatis.mapper.common.Mapper interface
  • Note: Mapper interface starts with tk

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.User;
import tk.mybatis.mapper.common.Mapper;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public interface UserMapper2 extends Mapper<User> {
}

8.4 general API

• Query method

  Method name	describe
  T selectOne(T t)	When querying according to the attributes in the entity, there can only be one return value, multiple results throw exceptions, and the query criteria use the equal sign
  List select(T t)	Query according to the attribute value in the entity, and the query criteria use the equal sign
  List selectAll()	Query all results
  int selectCount(T t)	According to the total number of attribute queries in the entity and query criteria, use the equal sign
  T selectByPrimaryKey(Object key)	Query by primary key field
  boolean existsWhithPrimaryKey(Object key)	Query whether records exist according to the primary key field
  List selectByExample(Object example)	Query according to the Example condition
  T selectOneByExample(Object example)	When querying according to the Example condition, there can only be one return value
  int selectCountByExample(Object example)	Number of records queried according to the Example condition

• Insertion method

  Method name	describe
  int insert(T t)	When an entity is saved, the null attribute will also be saved, and the database default value will not be used
  int intsertSelective(T t)	When saving an entity, the null attribute will not be saved, and the database default value will be used

• Update method

  Method name	describe
  int updateByPrimaryKey(T t)	Update all fields of the entity according to the primary key, and the null value will be updated
  int updateByPrimaryKeySelective(T t)	Update the fields in the entity with non null values according to the primary key

• Delete method

  Method name	describe
  int delete(T t)	Delete according to the entity attribute as a condition, and the query condition uses the equal sign
  int deletePrimaryKey(Object key)	Delete by primary key field
  int deleteByExample(Object example)	Delete data according to the Example condition

8.4.1 query by primary key

• 1) Determine the primary key, otherwise all fields are primary keys

  

• 2) Testing

  package com.czxy.ssm.test;
  
  import com.czxy.ssm.domain.User;
  import com.czxy.ssm.mapper.UserMapper;
  import com.czxy.ssm.mapper.UserMapper2;
  import com.czxy.ssm.utils.MyBatisUtils;
  import org.junit.Test;
  
  import java.io.IOException;
  import java.util.List;
  
  /**
   * @author Uncle Tong
   * @email liangtong@itcast.cn
   */
  public class Test13_Mapper {
      @Test
      public void testSelectByPrimaryKey() {
          UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
          User user = userMapper2.selectByPrimaryKey("1");
          System.out.println(user);
  
          MyBatisUtils.commitAndclose();
  
      }
  }
  

8.4.2 query all

    @Test
    public void testSelectAll() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
        List<User> list = userMapper2.selectAll();
        // Print
        list.forEach(System.out::println);

        MyBatisUtils.commitAndclose();
    }

8.4.3 addition

@Test
    public void testInsert() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        User user = new User();
        user.setUid("2");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("jack");
        user.setEmail("itcast_lt@163.com");
        user.setBirthday(new Date());
        user.setSex("male");
        user.setSex("0");

        int result = userMapper2.insert(user);

        // Print
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

8.4.4 modification

@Test
    public void testUpdate() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        User user = new User();
        user.setUid("2");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("jack");
        user.setEmail("itcast_lt@163.com");
        user.setBirthday(new Date());
        user.setSex("male");
        user.setSex("0");

        int result = userMapper2.updateByPrimaryKey(user);

        // Print
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

8.4.5 deletion

    @Test
    public void testDelete() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        int result = userMapper2.deleteByPrimaryKey("2");

        // Print
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

8.4.6 multi criteria query

• Syntax:

// Get multi condition object
Example example = new Example(object.class);
Example.Criteria criteria = example.createCriteria();

// Common conditions and methods
andLike()					//Fuzzy query
andEqualTo()				//Equivalent query
andLessThanOrEqualTo()		//< = query
andGreaterThanOrEqualTo()	//>=Inquiry
andBetween()				//Interval query

@Test
    public void testCondition() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        Example example = new Example(User.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andLike("name", "%king%");
        criteria.andEqualTo("sex", "male");

        List<User> list = userMapper2.selectByExample(example);
        for (User user : list) {
            System.out.println(user);
        }

        MyBatisUtils.commitAndclose();
    }

9. XML based

9.1 construction environment

9.1.1 create project

• 1) Select Web app

• 2) Create project mybatis-demo02

9.1.2 add jar package

9.1.3 copy configuration class

9.1.4 copying tools

9.1.5 copying JavaBean s

9.2 introduction case: query all

9.2.1 preparation process

• Step 1: write Dao interface to determine method name

• Step 2: write Mapper.xml file to write SQL statements

• Step 3: write the SqlMapConfig.xml core configuration file and add the mapper xml file

• Step 4: Test

9.2.2 writing Mapper interface

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.User;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public interface UserMapper {
    /**
     * Query details by id
     * @param uid
     * @return
     */
    public User selectById(String uid);
}

9.2.3 writing Mapper xml configuration file

<?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.czxy.ssm.mapper.UserMapper">

    <select id="selectById" parameterType="string" resultType="com.czxy.ssm.domain.User">
		select * from user where uid = #{id}
	</select>
</mapper>

9.2.4 modify core configuration file

• The core configuration file < mapper resource = "" / > is used to determine the location of the mapper configuration file

	<mappers>
		<!-- Indicates that all files under this package are loaded dao Interface-->
		<mapper resource="mapper/UserMapper.xml"/>
	</mappers>

9.2.5 testing

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import org.junit.Test;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class TestUserMapper {


    @Test
    public void testSelectById() {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = userMapper.selectById("1");
        System.out.println(user);

        MyBatisUtils.commitAndclose();
    }

}

9.3 detailed description of configuration file

9.3.1 Mapper basic structure

<?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="Mapper class">
	<!-- query -->
    <select id="Method name">SQL sentence</select>
    
	<!-- add to -->
	<insert id="Method name">SQL sentence</insert>
    
    <!-- to update -->
	<update id="Method name">SQL sentence</update>
    
    <!-- delete -->
	<delete id="Method name">SQL sentence</delete>
</mapper>

9.3.2 Mapper parameter type: parameterType

• In the mapping file mapper, we use parameterType to set the type of request parameters

<?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="mapper class">
    <select id="Method name" parameterType="Parameter type">SQL sentence</select>
</mapper>

• Simple type:

  <!-- adopt id query -->
  <select id="findUserById" parameterType="int" resultType="com.czxy.domain.User">
  	select * from user where uid = #{id}
  </select>
  

• POJO type:

  <!-- Add user -->
  <insert id="insertUser" parameterType="com.czxy.domain.User">
  	insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})
  </insert>
  

9.3.3 Mapper result type: resultType

• In the mapping file mapper, we use resultType to set the query result type

<?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="mapper class">
    <select id="Method name" resultType="Result type">SQL sentence</select>
</mapper>

• Simple type

  <!-- Total records -->
  <select id="findUserCount"  resultType="int">
  	select count(*) from user
  </select>
  

• POJO type

  <!-- adopt id query -->
  <select id="findUserById" parameterType="int" resultType="com.czxy.domain.User">
  	select * from user where uid = #{id}
  </select>
  

9.3.4 Mapper mapping relationship: ResultMap

• resultType can specify a pojo to encapsulate the query results into the pojo, but the property name of the pojo needs to be consistent with the column name of the sql query.

• If the field name of sql query is inconsistent with the property name of pojo, you can make a corresponding relationship between the field name and the property name through resultMap.

• Syntax:

  <!--Declaration mapping-->
  <resultMap type="JavaBean type" id="resultMap name">
  	<id column="Table column name" property="JavaBean Attribute name"/>		<!--Mapping for configuring primary keys-->
  	<result column="Table column name" property="JavaBean Attribute name"/>	<!--Used to configure normal result set mappings-->
  </resultMap>
  <!--Use mapping-->
  <select resultMap="resultMap name">
  </select>
  

• example:

  	<resultMap type="com.czxy.ssm.domain.User" id="userResultMap">
  		<id column="uid" property="uid"/>
  		<result column="username" property="username"/>
  	</resultMap>
  
      <select id="selectById" parameterType="string" resultMap="userResultMap">
  		select * from user where uid = #{id}
  	</select>
  

9.3.5 default alias

alias	mapping type
_byte	byte
_long	long
_short	short
_int	int
_integer	int
_double	double
_float	float
_boolean	boolean
string	String
byte	Byte
long	Long
short	Short
int	Integer
integer	Integer
double	Double
float	Float
boolean	Boolean
date	Date
decimal	BigDecimal
bigdecimal	BigDecimal
map	Map

9.3.6 user defined alias

• Declare the alias in the SqlMapConfig.xml configuration file

  • Method 1: define one class at a time

    <typeAliases>
    	<typeAlias type="type" alias="alias"/>
    </typeAliases>
    

  • Method 2: define all classes under one package at a time

    <typeAliases>
    	<package name="Package name"/>
    </typeAliases>
    

  

• Use aliases and use them directly in mapper

  <!--Use alias-->
  <select id="selectById" parameterType="string" resultType="user">
  	select * from user where uid = #{id}
  </select>
  

9.3.7 loading Mapper files

• In the SqlMapConfig.xml file, load the mapping file through < mappers > and < mapper >

• Method 1: load the specified file

  	<mappers>
  		<!-- Indicates that all files under this package are loaded dao Interface-->
  		<mapper resource="mapper/UserMapper.xml"/>
  	</mappers>
  

• Method 2: load all mapping files under the specified package

  • Requirement: the mapper interface name and mapper mapping file name should be the same and placed in the same directory

  	<mappers>
  		<package name="com.czxy.ssm.mapper"/>
  	</mappers>
  

9.4 addition, deletion, modification and query

9.4.1 core configuration file

9.4.2 mapper interface

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.vo.UserVo;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public interface UserMapper {
    /**
     * Query details by id
     * @param uid
     * @return
     */
    public User selectById(String uid);

    /**
     * Query all
     * @return
     */
    public List<User> selectAll();


    /**
     * Fuzzy query
     * @param name
     * @return
     */
    public List<User> selectByName(@Param("name") String name);

    /**
     * insert data
     * @param user
     */
    public Integer insert(User user);

    /**
     * insert data
     * @param user
     */
    public Integer updateByPrimaryKey(User user);

    /**
     * Delete by id
     * @param uid
     */
    public Integer deleteByPrimaryKey(@Param("uid") String uid);

    /**
     * Condition query
     * @param userVo
     * @return
     */
    public List<User> condition(UserVo userVo);
}

9.4.3 Mapper mapping file

<?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.czxy.ssm.mapper.UserMapper">

	<resultMap type="com.czxy.ssm.domain.User" id="userResultMap">
		<id column="uid" property="uid"/>
		<result column="username" property="username"/>
	</resultMap>

    <select id="selectById" parameterType="string" resultMap="userResultMap">
		select * from user where uid = #{id}
	</select>

	<select id="selectAll" parameterType="string" resultMap="userResultMap">
		select * from user
	</select>

	<select id="selectByName" parameterType="string" resultMap="userResultMap">
		select * from user where name like #{name}
	</select>

	<insert id="insert" parameterType="user">
		insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})
	</insert>

	<update id="updateByPrimaryKey" parameterType="user">
		update user set username=#{username}, password=#{password}, name=#{name}, email=#{email},birthday=#{birthday},sex=#{sex}, state=#{state} where uid=#{uid}
	</update>

	<delete id="deleteByPrimaryKey" >
		delete from user where uid = #{uid}
	</delete>

</mapper>

9.4.4 testing

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import org.junit.Test;

import java.util.Date;
import java.util.List;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class TestUserMapper {


    @Test
    public void testSelectById() {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = userMapper.selectById("1");
        System.out.println(user);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testSelectAll() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
        List<User> list = UserMapper.selectAll();
        // Print
        list.forEach(System.out::println);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testSelectByName() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
        List<User> list = UserMapper.selectByName("%Zhang%");
        // Print
        list.forEach(System.out::println);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testInsert() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = new User();
        user.setUid("2");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("jack");
        user.setEmail("itcast_lt@163.com");
        user.setBirthday(new Date());
        user.setSex("male");
        user.setSex("0");

        int result = UserMapper.insert(user);

        // Print
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testUpdate() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = new User();
        user.setUid("2");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("jack");
        user.setEmail("itcast_lt@163.com");
        user.setBirthday(new Date());
        user.setSex("male");
        user.setSex("0");

        int result = UserMapper.updateByPrimaryKey(user);

        // Print
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testDelete() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);

        int result = UserMapper.deleteByPrimaryKey("2");

        // Print
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

}

9.5 dynamic SQL

9.5.1 what is dynamic SQL

Dynamic SQL is that Mybatis allows you to control the final patchwork result of SQL statements through labels in the mapping file.

9.5.2 < if > label

• Syntax: in the mapper mapping file, the < if > tag is equivalent to the if statement in Java. If the condition is true, the SQL statement in the tag is valid.

  <select>
  	<if test="condition">
  		//Valid SQL statement fragments
  	</if>
  <select>
  

• Encapsulated object: UserVo

  public class UserVo {
      private String name;            // Fuzzy query
      private String beginTime;       // start time
      private String endTime;         // End time
      // ...
  }
  

• Multi condition query, pieced identity condition where 1=1

  	<select id="condition" parameterType="userVo" resultType="user" >
  		select * from user where 1=1
  		<if test="name != null and name != ''">
  			and name like '%${name}%'
  		</if>
  	</select>
  

• test

      @Test
      public void testCondtion() {
          UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
  
          UserVo userVo = new UserVo();
          userVo.setName("Zhang");
  
          List<User> list = UserMapper.condition(userVo);
          // Print
          list.forEach(System.out::println);
  
          MyBatisUtils.commitAndclose();
      }
  

9.5.3 < where > label

• In multi condition query, we use a trick "where 1=1", and mybatis provides a < where > to replace it.

• Modify mapping file

  

  	<select id="condition" parameterType="userVo" resultType="user" >
  		select * from user
  		<where>
  			<if test="name != null and name != ''">
  				and name like '%${name}%'
  			</if>
  		</where>
  	</select>
  

9.5.4 < foreach > label

• Syntax:

  <!--
  <foreach> Is to traverse a set of data and piece it up according to the specified content SQL Statement fragment
  		collection ，The data to be traversed. If the actual parameter is the array itself, it needs to be used here array
  		item ，The variables that store data in each cycle can be passed in the label body ${item value}Get traversal data
  		open ，SQL Development location of statement fragments
  		separator，Separator
  		close ，SQL End position of statement fragment
  -->
  
  <!-- For example: uid in (1,2,4,6) Piece together
  	start (Variable separator)*4 end
  -->
  <foreach collection="array" open="uid in (" item="variable" separator=","  close=")">
  </foreach>
  

• Encapsulated object

  public class UserVo {
      private List<String> ids = new ArrayList<>();
      private String name;            // Fuzzy query
      private String beginTime;       // start time
      private String endTime;         // End time
      // ... 
  }
  

• Multi condition query

  <select id="condition" parameterType="userVo" resultType="user" >
  		select * from user
  		<where>
  			<!-- uid in (1,2,4,6) -->
  			<foreach collection="ids" open="uid in (" item="id" separator="," close=")">
  				'${id}'
  			</foreach>
  		</where>
  	</select>
  

• test

      @Test
      public void testCondtion() {
          UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
  
          UserVo userVo = new UserVo();
          userVo.setIds(Arrays.asList("u001","u002"));
  
          List<User> list = UserMapper.condition(userVo);
          // Print
          list.forEach(System.out::println);
  
          MyBatisUtils.commitAndclose();
      }
  

9.5.5 < choose > label: multiple conditions

• grammar

	<select id="">
		<choose>
			<when test=""></when>
			<when test=""></when>
			<otherwise></otherwise>
		</choose>
	</select>

9.5.6 < SQL > tags

• MyBatis provides us with code fragments. Use < SQL > to define public SQL statements, and use < include > to piece together the required SQL fragments to the specified location.

<!-- All field names SQL fragment -->
<sql id="user_all_column">uid, username, password, name, email, birthday, sex, state</sql>

<select id="selectAll" parameterType="string" resultMap="userResultMap">
	select <include refid="user_all_column" /> from user
</select>

9.6 relationship mapping: one to many

9.6.1 mapper interface

• UserMapper, existing

  public interface UserMapper {
      /**
       * Query details by id
       * @param uid
       * @return
       */
      public User selectById(String uid);
  }
  

• OrderMapper

  package com.czxy.ssm.mapper;
  
  import com.czxy.ssm.domain.Order;
  import org.apache.ibatis.annotations.Param;
  
  /**
   * @author Uncle Tong
   * @email liangtong@itcast.cn
   */
  public interface OrderMapper {
  
      /**
       * Query all orders of the specified user
       * @param uid
       * @return
       */
      public Order findOrdersByUserId(@Param("uid") Integer uid) ;
  }
  
  

9.6.2 Mapper mapping file

• OrdersMapper.xml

  <?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.czxy.ssm.mapper.OrderMapper">
  
  
      <select id="findOrdersByUserId" parameterType="string" resultType="order" >
  		select * from orders where uid = #{uid}
  	</select>
  
  </mapper>
  

• UserMapper.xml

  <?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.czxy.ssm.mapper.UserMapper">
  
  	<resultMap type="com.czxy.ssm.domain.User" id="userResultMap">
  		<id column="uid" property="uid"/>
  		<result column="username" property="username"/>
  		<collection property="orderList" column="uid" select="com.czxy.ssm.mapper.OrderMapper.findOrdersByUserId" />
  	</resultMap>
  
  
  
      <select id="selectById" parameterType="string" resultMap="userResultMap">
  		select * from user where uid = #{id}
  	</select>
      
  </mapper>
  

9.6.3 core configuration file

9.6.4 testing

public class TestUserMapper {

    @Test
    public void testSelectById() {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = userMapper.selectById("u001");
        System.out.println(user);

        MyBatisUtils.commitAndclose();
    }
    
}

9.7 relationship mapping: many to one

9.7.1 mapper interface

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public interface OrderMapper {

    /**
     *
     * @param oid
     * @return
     */
    public Order selectById(@Param("oid") String oid);
}

9.7.2 mapping file: OrdersMapper.xml

<?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.czxy.ssm.mapper.OrderMapper">

	<resultMap id="ordersResultMap" type="order">
		<id property="oid" column="oid"></id>
		<result property="ordertime" column="ordertime" ></result>
		<result property="total" column="total" ></result>
		<result property="state" column="state" ></result>
		<result property="address" column="address" ></result>
		<result property="name" column="name" ></result>
		<result property="telephone" column="telephone" ></result>
		<result property="uid" column="uid" ></result>
		<association property="user" select="com.czxy.ssm.mapper.UserMapper.selectById" column="uid" />
	</resultMap>
	<select id="selectById" resultMap="ordersResultMap">
		select * from orders where oid = #{oid}
	</select>

</mapper>

9.7.3 testing

package com.czxy.ssm.test;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import com.czxy.ssm.vo.UserVo;
import org.junit.Test;

import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * @author Uncle Tong
 * @email liangtong@itcast.cn
 */
public class TestOrderMapper {


    @Test
    public void testSelectById() {
        OrderMapper orderMapper = MyBatisUtils.getMapper(OrderMapper.class);

        Order order = orderMapper.selectById("x001");
        System.out.println(order);

        MyBatisUtils.commitAndclose();
    }

}