set up a database

create database 'mybatis'
use 'mybatis';
create table 'user'(
	'id' int(20) not null primary key,
	'name' varchar(30) default null,
	'pwd' varchar(30) default null
)engine = innodb default charset= utf-8;

insert into 'user'('id','name','pwd') values
(1,'Mad God','123456'),
(2,'Mad God','123456'),
(3,'Mad God','123456');

//Mybatis-config.xml is the core configuration file of mabatis, which is written under the resources file

<configuration>
	<environments default="development">
	<environment>
		<transactionManager type="JDBC"/>
		<dataSource>
			<property name="driver" value=""/>
			<property name="url" value=""/>
			<property name="username" value=""/>
			<property name="password" value=""/>
			
		</dataSource>
	</environment>
</environments>

be careful::::::::::::::every last Mapper.xml All need to be in mabatis Register in core profile
<mappers>
	<mapper resource = "com/kuang/dao/UserMapper.xml">
</mappers>

</configuration>

//Tool class sqlsessionfactory -- > sqlsession

public class MybatisUtils{
	private static SqlSessionFactory sqlSessionFactory;
	static{
		String resource="mybatis-config.xml"
		InputStream inputStream = Resources.getResourceAsStream(resource);
		sqlSessionFactory = new  SqlSessionFactoryBuilder().build(inputStream)
	}
	//With SqlSession, you can obtain an instance of SqlSession from the summary
	//SqlSession completely contains all the methods required to execute Sql commands for the database
	public static SqlSession getSqlSession(){
		return sqlSessionFactory.openSession();	
	}
		
}

//Entity class

public class User{
	private int id;
	private String name;
	private String pwd;
	
	public User(){}
	public User(){
		this.id = id;
		this.name = name;
		this.pwd = pwd;
	}
	
	
	get   set method  toString
}

//dao

public  interface UserDao{
	//Extensions: fuzzy queries
	List<User> getUserLike(String value);
	//Query all users
	List<User> getUserList();
	//Query user by id
	User getUserById(int id);
	//insert a user
	int addUser(User user);
	//By map
	int addUser(Map <String,Object>map)
	//Modify user
	int updateUser(User user);
	//Delete a user
	boolean deleteUser(int id);	
}

public class UserDaoImpl implements UserDao{
	public List<User> getUserList(){
		//Execute sql
		String sql = "select * from mybatis.user"
		//Result set 
		resultSet
	   	return null;
	}
}

//The interface implementation class is transformed from the original UserDaoImpl to a Mapper configuration file

//UserMapper.xml

<?xml version=1.0 encoding ="utf-8"?>
<!DOCTYPE=mapper PUBLIC "">
//namespace = bind a corresponding Dao/Mapper interface
<mapper namespace="com.kuang.dao.UserDao" >

	<select id ="getUserLike" resultType="com.kuang.pojo.User">
		select * from  mybatis.user where  name like "%"#{value}"%"
	</select>
	
	//The select query statement id is equivalent to the method name in the original impl implementation class	
	<select id = "getUserList" resultType="com.kuang.pojo.User">
		select * from mabatis.user
	</select>
	
	//Query user by ID
	<select id ="getUserById" parameterType="int" resultType="com.kuang.pojo.User" >
		select * from mybatis.user where id = #{id}
	</select>
	
	//insert a user
	<insert id ="addUser" parameterType="com.kuang.pojo.User">
		insert into  mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
	</insert>
		
	//The universal map does not need to pass all parameters. It can only pass some parameters, not as many parameters as the object
	<insert id ="addUser" parameterType="map">
		insert into imybatis.user (id,pwd) values (#{userid},#{password})
	</insert>
	
	//Modify user
	<update  id = "upadateUser" parameterType="com.kuang.pojo.User">
		update mybatis.user set name= #{name},pwd=#{pwd} where id =#{id};
	</update>
	
	//Delete a user
	<delete id = "deleteUser" parameterType="int">
		delete from mybatis.user where id = #{id};
	</delete>
	
</mapper>

public class UserDaoTest{
	@Test
	public void getUserLike(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		
		List<User> userlist = mapper.getUserLike("%Lee%");
		for(User user:userList){
			
		}
		
		sqlSession.commit();
		sqlSession.close();
	}

	@Test 
	public void test(){
		//Step 1: get SqlSession object
		SqlSesson sqlSession = MybatisUtils.getSqlSession();
		
		
		//Method 1: getMapper recommendation
		UserDao userDao = sqlSession.getMapper(UserDao.class)；
		List<User> userList = userDao.getUserList();
		
		//Mode 2:
		List<User> userList = sqlSession.selectList("com.kuang.dao.UserDao.getUserList");
		
		for(User user :userList){
			System.out.println(userList);
		}
		sqlSession.close();		
	}
	
	@Test
	public void getUserById(){
		SqlSesson sqlSession = MybatisUtils.getSqlSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		User user = mapper.getUserById(1);
		System.out.println(user);
		
		sqlSession.close();
	}
	//Add, delete and modify transactions that need to be committed
	@Test 
	public void addUser(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		UserMapper mapper  = sqlSession.getMapper(UserMapper.class) //Change the name of UserDao.class to UserMapper in the video
	
		
		int res = mapper.addUser(new User(4,"ha-ha","123132"));
		//Commit transaction (must be,,, otherwise the database will not change)
		sqlSession.commit();
		
		sqlSession.close();
	}
	
	@Test
	public void updateUser(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		int num = mapper.upadateUser(new User(4,"seven seven","15646"));
		sqlSession.commit();
		sqlSession.close();
	}
	@Test
	public void deleteUser(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		UserMapper mapper = SqlSession.getMapper(UserMapper.class);
		boolean result = mapper.deleteUser(4);
		sqlSession.commit();
		sqlSession.close();
		
	}
	
	//Universal map, with a map, you don't need to pass all the parameters
	@Test
	public void addUser2(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		UserMapper mapper = sqlSession(UserMapper.class);
		Map <Srting,Object>map = new HashMap<String,Object>();
		map.put ("userid",5);
		map.put("password","33333");
		mapper.addUser2(map);
		sqlSession.close();
	}
	
}

//The package name in name should be consistent with the package name of Dao/mapper interface

select
Select query statement
id: is the method name in the corresponding namespace;
resultType: return value of Sql statement execution
parameterType: parameter type

Omnipotent map
Assuming that there are too many tables,, fields or parameters in our entity class or database, we should consider using Map

Map passes parameters. You can directly get the key from sql. parameterType = "map"
You can pass on whatever you want, which is equivalent to customization,,

Object passes parameters. You can directly get the attribute of the object in sql. parameterType = "object"

If there is only one basic parameter type, it is directly removed from sql,
boolean deleteUser(int id);

Map or annotation is used for multiple parameters.
boolean deleteUser(int id ,ArgType arg);

In xml, all tags can define order

Configuration resolution

Introducing external profiles into core profiles

<properties resource = "db.properties">
	<property name="username" value="root"/>
	<property name="pwd" value="1111"/>
</properties>

You can import external files directly
You can add some attribute configurations
If two files have the same field, the external configuration file is preferred

Core profile
mybatis-02
------------Db.properties (reintroduced in config to write parameter names directly)

driver = com.mysql.jdbc.Driver
url= jdbc:mysql:localhost:3306:mybatis？
username = root
password=root

Type alias (note the location in the configuration file) resultType = "com.kuang.pojo.User"
A type alias is a short name for a java type
The meaning of existence is only to reduce the redundancy of fully qualified names

You can give an entity class its alias



You can also specify a package name. MyBatis will search for the required Java beans under the package name
Scan the package of the entity class, and its default alias is the class name of the entity class, with lowercase initial

The first one is used when there are few entity classes
When there are many entity classes, the second one is recommended
The first one can customize the alias, and the second one can't. If you have to change it, you need to annotate the entity class

Last in lastName database_ Name is because it is converted to uppercase in oracal to increase readability

Other configurations
typeHandlers
objectFactory (object factory)
plugins plug-in
mybatis_generator=core
mybatis-plus
General mapper

mappers
MapperRegistry: register and bind our Mapper file

Mode 1:
<mappers>
	<mapper resource="com/kuang/dao/UserMapper.xml"
</mappers>
Mode 2: use class File binding registration
<mappers>
	<mapper class ="com.kuang.dao.UserMapper"/>
</mappers>

Note:
Interface and its Mapper configuration file must have the same name
The interface and its Mapper configuration file must be in the same file
Method 3: use scan package for injection binding



Note:
Interface and its Mapper configuration file must have the same name
The interface and its Mapper configuration file must be in the same file

Lifecycle and scope
Lifecycle and scope are critical, because incorrect usage leads to very serious concurrency problems
SqlSessionFactoryBuilder:
Once the ship has SqlSessionFactory, it is not needed
local variable
SqlSessionFactory
To put it bluntly, it is the database connection pool (it always exists after creation, waiting for others to connect, and finally recycled)
Once the SqlSessionFactory is pierced, it should always exist during the operation of the application. There is no reason to discard it or recreate another instance
Therefore, the best scope of SqlSessionFactory is the application scope

The simplest is to use local mode or static singleton mode.

SqlSession
 A request to connect to the connection pool
SqlSession The instance of is not thread safe, so it cannot be co absorbed. Therefore, its best scope is the request or method scope
 Close it immediately after use, otherwise the resources will be occupied

The sqlsessionfactorybuilder car factory built 100 cars and sold them to the sqlsessionfactory. Then it went bankrupt

Solve the problem of inconsistency between attribute name and field name
select * from mybatis.user where id = #{id}
select id,name,pwd from mybatis.user where id = #{id}
resolvent:

	Method 1: alias  
	<select id="get UserById" resultType="com.kuang.pojo.User">
		select id,name ,pwd  as password from mybatis.user where id = #{id}
	</select>
			
	Method 2: result mapping set resultMap
<resultMap id ="UserMap" type="User">
	<result column="id" property="id">  column Fields in the database, property Properties in entity classes
	<result column="name" property="name">
	<result column="pwd" property="password">
</resultMap>

<select id="getUserById" resultMap="UserMap">
		select * from mybatis.user where id = #{id}
</select>

The resultMap element is the most important and powerful element in MyBatis
The design idea of ResultMap is that there is no need to match simple statements at all
Set explicit result mapping, and for more complex statements, you only need to describe their relationship

journal:
Log factory
If an exception occurs in a database operation, we need the troubleshooting log 94 hi's best assistant
Once South debug
Now: log factory
In the mybatis core configuration file, configure our logs
Import package configuration file
to configure

Use limit paging-----------

select * from user limit startIndex,pageSize;
select * from user limit 0,3;

public interface UserMapper{
	//paging
	List<User> getUserLimit(Map<String,Integer> map);
}

<mapper namespace ="com.kuang.dao.UserMapper">
	//paging
	<select id = "getUserLimit" parameterType="map" /*resultType="user"*/ resultMap="UserMap">
		select * from mybatis.user limit #{startIndex},#{pageSize}
	</select>
</mapper>

public calss UserMapperTest{
	@Test
	public void getUserByLimit(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		
		HashMap<String,Integer> map = new HashMap<String,Integer>();
		map.put("startIndex",0);
		map.put("pageSize",2);
		
		List<User> userByLimit = mapper.getUserByLimit(map);
		for(User user:userList){
			System.out.println(user);
		} 

		sqlSession.close();
	}
}

RowBounds paging---------
No longer use sql to implement paging

How to use annotations

public interface UserMapper{
	@Select("select * from user");
	List<User> getUsers();	
	
	//Method has multiple parameters. All parameters must be preceded by @ Param
	@Select("select * from user where id=#{id}")
	User getUserById(@Param("id") int id);
	
	@Insert("insert into user(id,name,pwd) value(#{id},#{name},#{pwd})")
	int addUser(User user);
		
	@Update("update user set name=#{name},pwd=#{password} where id = #{id}")
	int updateUser(User user);	
		
	@Delete("delete from user where id=#{id}")
	int deleteUser(@Param("uid") int id);	
}
<mappers>
	<mapper class="com.kaung.dao.UserMapper"/>
</mappers>

public class UserMapperTest{
	SqlSession sqlSession = MybatisUtils.getSqlSession();
	UserMapper mapper = sqlSession.getMapper(UserMapper.class);
	//List<User> user = mapper.getUsers();
	//for(User user :users){
	//	System.out.println(user);
	//}
	
	User userById = mapper.getUserById(1);
	System.out.println(userById);
	
	mapper.addUser(new User(5,"Hello","1231"));
		
	mapper.updateUser(new User(5,"hhh","123115"));
	
	mapper.deleteUser(5);
	
	sqlSession.close();
	
}

About @ Param() annotation
Basic type parameters or String types need to be added
Reference type does not need to be added
If there is only one basic type, it can be ignored, but it is recommended to add
What is referenced in sql is the reasonable attribute name set in @ Param()

#{} prevent sql injection,, ${}

Installing Lombok plug-in in DIEA
Find a jar package such as Lombok in the project
Just annotate the entity class

@Data: get set tostring hashcode equals
@AllArgsConstructor
@NoArgsConstructor

Many to one----------------
Multiple students, corresponding to a senior four and
For students, how about associating multiple students? When associating one, many to one
For teachers, set

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, Miss Qin); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `student` (`id`, `name`, `tid`) VALUES (1, Xiao Ming, 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (2, Xiao Hong, 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (3, Xiao Zhang, 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (4, petty thief, 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (5, Xiao Wang, 1);

com.kuang.pojo

@Data
public classs Student{
	private int id;
	private String name;	
	//Students need to associate with a teacher
	private Teacher teacher;
}

@Data
public class Teacher{
	private  int id;
	private String name;
}

public interface StudentMapper{
	//Query all student information and corresponding teacher information	
	public List<Student> getStudent();
	public List<Student> getStudent2();
}

public interface TeacherMapper{
	@Select("select * from teacher where id =#{uid}")
	Teacher getTeacher(@Param("tid")int id);	
}

resources build package com.kaung.dao

StudentMapper.xml
=====================Nested processing by query

<mapper namespace="com.kuang.dao.StudentMapper">
	//<select id ="getStudent" resultType="Student">
	<select id ="getStudent" resultMap="StudentTeacher">		
		select * from student
	</select>
	
	<resultMap id="StudentTeacher" type="Student">
		<result property="id" column="id"/>
		<result property="name" column="name"/>
		//Complex attributes need to be handled separately, such as object association and collection
		<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>		
	</resultMap>
	
	<select id="getTeacher" resultType="Teacher">
		select * from teacher where id = #{id}
	</select>	
</mapper>

=====================Nested processing according to results

<mapper namespace="com.kuang.dao.StudentMapper">
	<select id ="getStudent2" resultMap="StudentTeacher2">
		select s.id sid,s.name sname,t.name tname
		from studnet s,teacher t
		where s.tid= t.id;		
	</select>
	
	<resultMap id = "StudentTeacher2" type="Student">
		<result property="id" column="sid"/>
		<result property="name" column="sname"/>
		<association property="teachar" javaType="Teacher">
			<result property="name" column="tname"/>
		</association>
	</resultMap>
</mapper>

TeacherMapper.xml

<mapper namespace="com.kuang.dao.TeacherMapper">
</mapper>

mybatis.config.xml Add in
<mappers>
	<mapper resource="com/kuang/dao/*.xml"/>
</mappers>

public class MyTest{
	public static void main(String [] args){
		SqlSession	sqlSession = MybatisUtils.getSqlSession();
		TeacherMapper mapper =sqlSession.getMapper(TeacherMapper.class);		
		Teacher teacher = mapper.getTeacher(1);
		System.out.println(teacher);
		SqlSession.close();
	}
	
	@Test
	public void testStudent(){
		SqlSession	sqlSession = MybatisUtils.getSqlSession();		
		StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
		List<Student> student= mapper.getStudent();
		for(Student student:studentList){
			System.out.println(student);
		}
		
		SqlSession.close();
	}
	
}

Test environment construction
Import lombok
New entity class Teacher,Student
New Mapper interface
Create Mapper.XML file
Bind and register my Mapper interface file in the core configuration file
Test whether the query can succeed

One to many processing
For example, a teacher has multiple students
For teachers, it is one to many

@Data
public class Studnet{
	private int id ;
	private String name;
	private int tid;
}

@Data
public class Teacher{
	private int id;
	private String name;
	//A teacher has more than one student
	private List<Student> students;
}

public interface TeacherMapper{
	//Get teacher
	List<Teacher> getTeacher();
	//Get the information of all students and teachers under the specified teacher
	Teacher getTeacher(@Param(tid) int id);
	
	Teacher getTeacher2(@Param(tid) int id);
	
}

select s.id sid,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.tid = t.id
====================Nested query by result

<mapper namespace="com.kuang.dao.TeacherMapper">
	<select id="getTeacher" resultMap="TeacherStudent">
		select s.id sid,s.name sname,t.name tname,t.id tid
		from student s,teacher t
		where s.tid = t.id and t.id = #{tid}
	</select>
	
	<resultMap id="TeahcerStudent"  type="Teacher">
		<result property="id" column="tid"/>
		<result property="name" column="tname">
		//For complex attributes, we need to deal with the object association collection separately: collection
		//javaType = "specify the type of attribute", the generic information in the collection. We use ofType to get it
		<collection property="students" ofType="Student">
			<result property="name" column="sname">
			<result property="id" column="sid">
			<result property="tid" column="tid">
		</collection>
	</resultMap>
</mapper>

====================Nested processing by query

<select id = "getTeacher2" resultMap="TeacherStudent2">
	select * from mybatis.teacher where id = #{tid}
<select>
<resultMap id="TeacherStudent2" type="Teacher">
	<result property="id" column="tid"/>
	<result property="name" column="tname"/>
	
	<collection property="students" column="id" javaType="ArrayList" ofType="Student">
	</collection>
	
	<select id = "getStudentByTeacherId" resultType="Student">
		select * from mybatis.student where tid=#{tid} 
	</select>
</resultMap>

public classs MyTest{
	@Test
	public void test(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
		List<Teachers> teacher = mapper.getTeacher();
		for(Teacher teacher:teachers){
			System.out.println(teacher);
		}
		sqlSession.close();
	}
	@Test
	public void test(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
		Teacher teacher = mapper.getTeacher(1);
		System.out.println(teacher);
		
		sqlSession.close();
	}
}

Summary:
association: many to one----------------
Collection: collection one to many
javaType & ofType
javaType is used to specify the type of attribute in the entity class
ofType is used to specify the pojo type mapped to the List or collection, and the constraint type in the generic type;
Note: ensure the readability of sql and make it easy to understand
Note the problem of attribute names and fields in one to many and many to one
If the problem is difficult to troubleshoot, log can be used. log4j is recommended