1.Mybatis是什么
MyBatis-Spring 会帮助你将 MyBatis 代码无缝地整合到 Spring 中。它将允许 MyBatis 参与到 Spring 的事务管理之中,创建映射器 mapper 和 SqlSession
并注入到 bean 中,以及将 Mybatis 的异常转换为 Spring 的 DataAccessException
。 最终,可以做到应用代码不依赖于 MyBatis,Spring 或 MyBatis-Spring。
2.将所需的包导入pom
<dependencies>
<!--mysql连接数据库-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!--省略getset方法的jar-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
<!--测试类-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!--日志-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!--mybatis分页-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.11</version>
</dependency>
</dependencies>
3.Mybatis的主配置文件
<?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 resource="db.properties"/>
<!--分页插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="param1" value="value1"/>
</plugin>
</plugins>
<environments default="development">
<!-- 连接环境信息,取一个任意唯一的名字 -->
<environment id="development">
<!-- mybatis使用jdbc事务管理方式 -->
<transactionManager type="JDBC" />
<!-- mybatis使用连接池方式来获取连接 -->
<dataSource type="POOLED">
<!-- 配置与数据库交互的4个必要属性 ${}来取properties文件中设置的值-->
<property name="driver" value="${jdbc.driverName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<mappers>
<!--<mapper resource="mapper/userMapper.xml"/>-->
<!--加载userMapper.xml文件-->
<mapper resource="mapper/classaMapper.xml"/>
</mappers>
</configuration>
4.创建Mybatis主配置文件中需要加载影射的Mapper.xml文件
4.1.多对一嵌套方法
<?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">
<!--namespace:命名空间:
必须和dao接口对应。
-->
<mapper namespace="com.dao.StudentDao">
<!--resultMap 自定义返回数据类型
id:可以随便写,保存唯一即可
type:对应要返回的数据类型
autoMapping="true":开启自动映射
-->
<resultMap id="aa" type="com.entity.Student">
<!--id主键必写,不然可能会出一些错误
result:普通列
property:对应实体中的变量名
column:对应数据库中的列名-->
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
<result property="cId" column="class_id"/>
<!--association:多对一
property 对应多的一方实体中,定义的一的一方实体
javaType 使用结果值来决定使用哪个结果映射
column 对应多的一方的外键
select 嵌套的第二条sql的id
-->
<association property="classa" javaType="com.entity.Classa"
column="class_id" select="selectClass" autoMapping="true">
<id property="classId" column="c_id"/>
<result property="cName" column="c_name"/>
</association>
</resultMap>
<resultMap id="ab" type="com.entity.Classa">
<id property="classId" column="c_id"/>
<result property="cName" column="c_name"/>
</resultMap>
<!--使用自定义映射resultMap返回值类型中应该对应以上设置的resultMap的id
-->
<!--select:查询方法
id:唯一即可,对应dao接口中的方法名
insert:新增
update:修改
delete:删除
-->
<select id="selectStudent" resultMap="aa">
select * from student where class_id=${oid}
</select>
</mapper>
4.2多对一链表
<?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.dao.StudentDao">
<resultMap id="aa" type="com.entity.Student">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
<result property="cId" column="class_id"/>
<association property="classa" javaType="com.entity.Classa" autoMapping="true">
<id property="classId" column="c_id"/>
<result property="cName" column="c_name"/>
</association>
</resultMap>
<select id="selectStudent" resultMap="aa">
select * from student s join class c on s.class_id=c.c_id where class_id=#{id}
</select>
</mapper>
4.3一对多链表
collection 一对多
ofType 指定对象类型
<resultMap id="aa" type="entity.Classa">
<id property="classId" column="c_id"/>
<result property="cName" column="c_name"/>
<collection property="student" ofType="entity.Student" autoMapping="true">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
<result property="cId" column="class_id"/>
</collection>
</resultMap>
<select id="selectAll" resultMap="aa">
select * from class c join student s on c.c_id=s.class_id where class_id=#{id}
</select>
5.dao接口
package com.dao;
import com.entity.Student;
import lombok.Data;
import java.util.List;
import java.util.Map;
public interface StudentDao {
/*查询出的是一个数组 类型是Student实体,所以使用list<Student>来接
selectStudent对应mapper中sql语句的唯一id
(如果需要接产,写在这lim)
在需要接多个参数时
可以封装Map
或者实体
如果不封实体又想接多个参数
@Param()中定义一下参数名
或者sql中起别名来接收
一个参时不需要写
*/
List<Student> selectStudent(Integer id);
}
6.测试类
import com.dao.ClassaDao;
import com.dao.StudentDao;
import com.entity.Classa;
import com.entity.Student;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
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 org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @program: mybatis02_145
* @description:
* @author: zlh
* @create: 2021-12-02 08:44
**/
public class ClassaTest {
private SqlSession sqlSession;
@Before //在执行方法之前,先执行
public void before() throws IOException {
Reader reader= Resources.getResourceAsReader("mybatis.xml");//获取xml文件
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
sqlSession= sqlSessionFactory.openSession();
}
@Test
public void selectA(){
ClassaDao clas=sqlSession.getMapper(ClassaDao.class);
Map map =new HashMap();
//map.put("name","xs");
PageHelper.startPage(1,4);//开启分页,第二页每页四条数据
List<Student> classas = clas.selectA(map);
PageInfo<Student> page=new PageInfo<Student>(classas);
System.out.println(page.getPageNum());//当前第几页
System.out.println(page.getPages());//总共多少也
System.out.println(page.getTotal());//总共多少条
System.out.println(page.getList());//当前页数据
System.out.println(page.isIsFirstPage());//是不是第一页
System.out.println(classas);
}
//增删改需要手动提交 sqlSession.commit(); 关闭sqlSession.close();
}
7.实体
7.1多对一实体
package com.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @program: mybatis03_145
* @description:
* @author: zlh
* @create: 2021-12-02 16:03
**/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private Integer id;
private String name;
private Integer cId;
private Classa classa;
}
package com.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
/**
* @program: mybatis03_145
* @description:
* @author: zlh
* @create: 2021-12-02 16:04
**/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Classa {
private Integer classId;
private String cName;
}
7.2一对多实体
package com.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
/**
* @program: mybatis03_145
* @description:
* @author: zlh
* @create: 2021-12-02 16:04
**/
@Data //get set 方法 tostring方法
@NoArgsConstructor //无参构造
@AllArgsConstructor //有参构造
public class Classa {
private Integer classId;
private String cName;
private List<Student> student;
}
package com.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @program: mybatis03_145
* @description:
* @author: zlh
* @create: 2021-12-02 16:03
**/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private Integer id;
private String name;
private Integer cId;
}
8.资源配置文件
8.1mybatis需要的数据库连接的资源
jdbc.url=jdbc:mysql://localhost:3306/role?serverTimezone=Asia/Shanghai
jdbc.driverName=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=密码
8.2事务配置文件,文件名必须是log4j.properties
### 设置###
log4j.rootLogger = debug,stdout,D,E
### 输出信息到控制抬 ###
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 = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n
### 输出DEBUG 级别以上的日志到=E://logs/error.log ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = D://logs/log.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n
### 输出ERROR 级别以上的日志到=E://logs/error.log ###
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =D://logs/error.log
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR
log4j.appender.E.layout = org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n
9.特殊字符处理
<![CDATA[ sql语句 ] ] >
10.动态sql
10.1 if
<select id="selectStudent" parameterType="java.util.Map" resultType="com.entity.Studentinfo">
<!--test里面写判断条件-->
select * from studentinfo where 1=1
<if test="name!=null">
and studentname like concat('%',#{name},'%')
</if>
<if test="ageBegin!=null">
and age >=#{ageBegin}
</if>
<if test="ageEnd!=null">
and age <= #{ageEnd}
</if>
<if test="gender!=null">
and gender like '%${gender}%'
</if>
order by ${ordername}
</select>
10.2 choose (when, otherwise)
相当于switch语句
当满足条件1时退出
如果不满足继续向下判断
都不满足执行otherwise里面的语句
<select id="selectStudent2" parameterType="java.util.Map" resultType="com.entity.Studentinfo">
select * from studentinfo where 1=1
<choose>
<when test="state==1">
and gender='男'
</when>
<when test="state==2">
and gender='女'
</when>
<when test="state==3">
and gender is null
</when>
<otherwise>
and gender in('男','女')
</otherwise>
</choose>
</select>
10.3 trim ---补全标签
<select id="selectStudent3" parameterType="java.util.Map" resultType="com.entity.Studentinfo">
select * from studentinfo
<!--trim标签中间的内容最前边拼接一个where 然后中间的内容第一个and给覆盖掉-->
<trim prefix="where" prefixOverrides="and">
<if test="name!=null">
and studentname like concat('%',#{name},'%')
</if>
<if test="ageBegin!=null">
and age >=#{ageBegin}
</if>
<if test="ageEnd!=null">
and age <= #{ageEnd}
</if>
<if test="gender!=null">
and gender like '%${gender}%'
</if>
</trim>
order by ${ordername}
</select>
<update id="updateStudent" parameterType="com.entity.Studentinfo">
update studentinfo
<!--前补全set 标签内容最后的,给覆盖 set:前补全set 后覆盖,-->
<trim prefix="set" suffixOverrides=",">
<if test="studentName!=null">
studentName=#{studentName},
</if>
<if test="gender!=null">
gender=#{gender},
</if>
<if test="age!=null and age!=0">
age=#{age},
</if>
<if test="studentNo!=null">
studentNo=#{studentNo},
</if>
<if test="classId!=null and classId!=0">
classId=#{classId},
</if>
<if test="phone!=null">
phone=#{phone}
</if>
</trim>
where studentid=#{studentId}
</update>
10.4 where
order by 排序
< 等同于<
<select id="selectStudent3" parameterType="java.util.Map" resultType="com.entity.Studentinfo">
select * from studentinfo
<!--trim标签中间的内容最前边拼接一个where 然后中间的内容第一个and给覆盖掉-->
<!--<trim prefix="where" prefixOverrides="and">-->
<!--where:前补全一定是where 覆盖第一个and或者or-->
<where>
<if test="name!=null">
and studentname like concat('%',#{name},'%')
</if>
<if test="ageBegin!=null">
and age >=#{ageBegin}
</if>
<if test="ageEnd!=null">
and age <= #{ageEnd}
</if>
<if test="gender!=null">
and gender like '%${gender}%'
</if>
<!--</trim>-->
</where>
order by ${ordername}
</select>
10.5 set
<update id="updateStudent" parameterType="com.entity.Studentinfo">
update studentinfo
<set>
<if test="studentName!=null">
studentName=#{studentName},
</if>
<if test="gender!=null">
gender=#{gender},
</if>
<if test="age!=null and age!=0">
age=#{age},
</if>
<if test="studentNo!=null">
studentNo=#{studentNo},
</if>
<if test="classId!=null and classId!=0">
classId=#{classId},
</if>
<if test="phone!=null">
phone=#{phone}
</if>
</set>
where studentid=#{studentId}
</update>
10.6 foreach
自己封装Map,自定义key
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
//int[] ints= {1,2,3,4};
List list=new ArrayList();
list.add(1);list.add(2);list.add(3);list.add(4);
Map map=new HashMap<>();//检索条件
map.put("ints",list);
List<Studentinfo> list2=studentMapper.selectStudent4(map);
<select id="selectStudent4" parameterType="java.util.Map"
resultType="com.entity.Studentinfo">
select * from studentinfo where classid in
<!--(1,2,3,4)
collection是遍历的对象:map的key或者实体的属性
item:每一行的变量名 cc=ints[i]
open:以哪个字符打开——最开头写啥
close:以哪个字符结尾——最后边写啥
separator:以什么分割
-->
<foreach collection="ints" item="cc" open="(" close=")" separator=",">
#{cc}
</foreach>
</select>
11.模糊查询
<!--#{name} ?占位 如果字符串,特殊形式赋值进去,相当于自带''
#{}不能出现在''里边 也不能出现 表名和列名
${}取值方式,只取值,实现的是字符串拼接
——可以放到字符串里边 放到表名列名的位置
-->
11.1 concat字符串拼接
select * from 表名 where name like concat('%',#{name},'%')
11.2 ${}取值
select * from 表面 where name like '%${name}%'