目录
一、什么是MyBatis?
二、MyBatis快速入门
三、Mapper代理开发
四、MyBatis核心配置文件
五、配置文件完成增删改查
5.1 环境准备
5.2 功能清单列表
5.2.1查询
5.2.2添加
5.2.3 修改
5.2.4 删除
六、MyBatis参数传递
七、注解完成增删改查
八、MyBatis的逆向工程
九、分页插件
一、什么是MyBatis?
- MyBatis是一款优秀的持久层框架,用于简化JDBC开发。
- MyBatis本来是Apache的一个开源项目iBatis,2010年这个项目由apache software foundation迁移到了google code,并且改名为MyBatis。2013年11月迁移到Github
- 官网:http://mybatis.org/mybatis-3/zh/index.html
持久层:负责将数据保存到数据库的安那一层代码。
JavaEE三层架构:表现层、业务层、持久层
框架:框架就是一个半成品软件,是一套可重用的、通用的。软件基础代码模型。
在框架的基础上构建软件编写更加高效、规范、通用、可扩展。
二、MyBatis快速入门
mybatis-config.xml的基础配置:
userMapper:
Java代码:
public class MyBatisDemo {public static void main(String[] args) throws IOException {//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取SqlSession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sqlList users = sqlSession.selectList("test.selectAll");System.out.println(users);//4.释放资源sqlSession.close();}}
三、Mapper代理开发
目的:
1. 解决原生方式中的硬编码。
2.简化后期执行SQL
步骤:使用Mapper代理方式完成入门案例
- 定义与sql映射文件(.xml文件)同名的Mapper接口,并且将Mapper接口和sql映射文件放置在同一目录下
- 设置sql映射文件的namesapce属性为Mapper全限定名
- 在Mapper接口中定义方法,方法名就是sql映射文件中sql语句中的id,并保持参数类型和返回值类型一致
- 编码
/** * MyBatis 代理开发 */public class MyBatisDemo2 {public static void main(String[] args) throws IOException {//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取SqlSession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sql//List users = sqlSession.selectList("test.selectAll");//3.1获取UserMapper接口的代理对象UserMapper userMapper = sqlSession.getMapper(UserMapper.class);List users = userMapper.selectAll();System.out.println(users);//4.释放资源sqlSession.close();}}
四、MyBatis核心配置文件
五、配置文件完成增删改查
5.1 环境准备
- 数据库表tb_brand
- 实体类Brand
- 测试用例
- 安装MyBatisX插件
建议安装1.4X版本,该版本下的resultType实体类对象别名不爆红
5.2 功能清单列表
5.2.1查询
- 查询所有数据
- 查看详情
- 条件查询
1. 查询所有数据
①编写接口的方法:Mapper接口
- 参数:无
- 结果:List
②Map接口
List selectAll();
编写SQL语句:编写SQL映射文件
select * from tb_brand;
③执行方法,测试
@Testpublic void testSelectAll() throws IOException {//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession();//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法List brands = brandMapper.selectAll();System.out.println(brands);//5. 释放资源sqlSession.close();}
解决数据库表的列名(brand_name)与实体类(brandName)的属性名不匹配方案:
数据库表的字段名称和实体类属性名称不一样,则不能自动封装数据
方案一:起别名:对不一样的列名起别名,让别名和实体类的属性名也一样
缺点:每次查询都要定义一次别名
select id, brand_name as brandName, company_name as companyName,ordered,description, statusfrom tb_brand;
改进:
采用sql片段
id, brand_name as brandName, company_name as companyName, ordered, description, status selectfrom tb_brand;
缺点:不灵活
改进:
使用resultMap
resultMap:id为唯一标识
1.定义标签
2.标签中使用resultMap属性来替换resultType属性
- id:完成主键字段的映射
- column:数据库表的列名
- property:对应的实体类属性名
select *from tb_brand;
方案二:在mybatis-config.xml中设置中的标签的value值为true
- 是否开启驼峰命名自动映射,即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn。
<!--数据库表的字段名称和实体类属性名称不一样,则不能自动封装数据*方案一:起别名:对不一样的列名起别名,让别名和实体类的属性名也一样*缺点:每次查询都要定义一次别名select id, brand_name as brandName, company_name as companyName, ordered, description, statusfrom tb_brand;*改进:sql片段 id, brand_name as brandName, company_name as companyName, ordered, description, statusselectfrom tb_brand;*缺点:不灵活*resultMap:id为唯一标识1.定义标签2.标签中使用resultMap属性来替换resultType属性id:完成主键字段的映射column:数据库表的列名property:对应的实体类属性名select *from tb_brand;*方案二:在mybatis-config.xml中设置中的标签的value值为true是否开启驼峰命名自动映射,即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn。--> select *from tb_brand; <!-- select *from tb_brand;-->
2. 查看详情
①编写接口的方法:Mapper接口
- 参数:id
- 结果:Brand对象
②Map接口
Brand selectById(int id);
编写SQL语句:编写SQL映射文件
select *from tb_brand where id = #{id};
*参数占位符1. #{}:将其替换为 " />③执行方法,测试
<!--*参数占位符1. #{}:将其替换为 ? ,为了防止SQL注入2. ${}:拼SQL,会存在SQl注入3. 使用时机:*参数传递时,用#{}*表名或者列名不固定的情况下,${}一定会存在SQL注入问题 * 参数类型:parameterType:可以省略 *特殊字符处理:1.转义字符(eg:'<'转义为'select *from tb_brand where id = #{id};
3. 条件查询
- 多条件查询:
①编写接口的方法:
- 参数:所有查询条件
- 结果:List
②编写SQL语句
散装参数(Mapper接口):
List selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
对象参数(Mapper接口):
List selectByCondition(Brand brand);
map集合(Mapper接口):
List selectByCondition(Map map);
SQL语句:编写SQL映射文件
select *from tb_brandwhere status = #{status}and company_name like #{companyName}and brand_name like #{brandName}
③执行方法,测试
**参数接收:*1.散装参数(如果方法中有多个参数,需要使用@Param(“SQL参数占位符名称”)@Testpublic void testSelectByCondition() throws IOException {//接收参数:int status = 1;String companyName = "华为";String brandName = "华为";//处理参数companyName = "%" + companyName + "%";brandName = "%" + brandName + "%";//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession();//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法List brands = brandMapper.selectByCondition(status, companyName, brandName); System.out.println(brands);//5. 释放资源sqlSession.close();}
*2.对象参数@Testpublic void testSelectByCondition() throws IOException {//接收参数:int status = 1;String companyName = "华为";String brandName = "华为";//处理参数companyName = "%" + companyName + "%";brandName = "%" + brandName + "%";//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession();//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法List brands = brandMapper.selectByCondition(status, companyName, brandName); System.out.println(brands);//5. 释放资源sqlSession.close();}
*3.map集合参数@Testpublic void testSelectByCondition() throws IOException {//接收参数:int status = 1;String companyName = "华为";String brandName = "华为";//处理参数companyName = "%" + companyName + "%";brandName = "%" + brandName + "%";//封装对象Map map = new HashMap();map.put("status",status);map.put("companyName",companyName);map.put("brandName",brandName);//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession();//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法List brands = brandMapper.selectByCondition(map);System.out.println(brands);//5. 释放资源sqlSession.close();}
- 动态条件查询:
多条件-动态条件查询
①编写接口的方法:Mapper接口
参数:所有查询条件
结果:List
②Map接口
List selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
编写SQL语句:编写SQL映射文件
select *from tb_brandstatus = #{status}and company_name like #{companyName}and brand_name like #{brandName}
③执行方法,测试
多条件-动态条件查询 if:条件判断test:逻辑表达式问题:只有没有第一个条件时会出现SQL语法错误*恒等式:添加"1=1"并将后续语句改为条件一致,构造SQLselect *from tb_brandwhere 1 = 1andstatus = #{status}and company_name like #{companyName}and brand_name like #{brandName}
*用标签 替换 where 关键字select *from tb_brandstatus = #{status}and company_name like #{companyName} and brand_name like #{brandName}
@Testpublic void testSelectByCondition() throws IOException {//接收参数:int status = 1;String companyName = "华为";String brandName = "华为";//处理参数companyName = "%" + companyName + "%";brandName = "%" + brandName + "%";//封装对象 /* Brand brand = new Brand();brand.setStatus(status);brand.setCompanyName(companyName);brand.setBrandName(brandName);*/Map map = new HashMap();map.put("status",status);map.put("companyName",companyName);map.put("brandName",brandName);//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession();//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法//List brands = brandMapper.selectByCondition(status, companyName, brandName);//List brands = brandMapper.selectByCondition(brand);List brands = brandMapper.selectByCondition(map);System.out.println(brands);//5. 释放资源sqlSession.close();}
单条件-动态条件查询
- 从多个条件中选择一个
- choose(when,otherwise):选择,类似于Java中的switch语句
①编写接口的方法:Mapper接口
- 参数:所有查询条件
- 结果:List
②Mapper接口
List selectByConditionSingle(Brand brand);
编写SQL语句:编写SQL映射文件
<!--<!–相当于switch–>--><!----><!--<!–相当于case–>--><!----><!--<!–相当于case–>--><!----><!--<!–相当于case–>--><!--<!–相当于default–>--><!----><!----><!---->select *from tb_brandstatus = #{status}company_name like #{companyName}brand_name like #{brandName}
③执行方法,测试
单条件-动态条件查询 choose(when,otherwise):选择,类似于Java中的switch语句test:逻辑表达式问题:只有没有条件时会出现SQL语法错误*恒等式:添加"1=1"构造SQL使之符合语法。select *from tb_brandwherestatus = #{status}company_name like #{companyName}brand_name like #{brandName}1 = 1
*用标签 替换 where 关键字select *from tb_brandstatus = #{status}company_name like #{companyName}brand_name like #{brandName}
@Testpublic void testSelectByCondition() throws IOException {//接收参数:int status = 1;String companyName = "华为";String brandName = "华为";//处理参数companyName = "%" + companyName + "%";brandName = "%" + brandName + "%";//封装对象Brand brand = new Brand();//brand.setStatus(status);brand.setCompanyName(companyName);//brand.setBrandName(brandName);//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession();//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法//List brands = brandMapper.selectByCondition(status, companyName, brandName);//List brands = brandMapper.selectByCondition(brand);List brands = brandMapper.selectByCondition(map);System.out.println(brands);//5. 释放资源sqlSession.close();}
5.2.2添加
①编写接口的方法:Mapper接口
void add(Brand brand);
- 参数:除id之外的所有数据
- 结果:void
②编写SQL语句、编写SQL映射文件
insert into tb_brand (brand_name, company_name, ordered, description, status)values (#{brandName},#{companyName},#{ordered},#{description},#{status});
③执行方法,测试
@Testpublic void testadd() throws IOException {//接收参数:int status = 1;String companyName = "波导手机";String brandName = "波导";String description = "手机中的战斗机";int ordered = 100;//封装对象Brand brand = new Brand();brand.setStatus(status);brand.setCompanyName(companyName);brand.setBrandName(brandName);brand.setDescription(description);brand.setOrdered(ordered);//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象//SqlSession sqlSession = sqlSessionFactory.openSession();SqlSession sqlSession = sqlSessionFactory.openSession(true);//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法brandMapper.add(brand);//提交事务//sqlSession.commit();//5. 释放资源sqlSession.close();}
Mybatis事务:
openSession():默认开启事务,进行增删改后需要使用sqlSession.commit();手动提交事务
openSession(true):可以设置为自动提交事务(默认为false:手动提交事务)
****添加—主键返回 ****
在数据添加成功后,需要获取插入数据库数据的主键的值
比如:添加订单和订单项
1.添加订单
2.添加订单项,订单项中需要设置所属订单的id
insert into tb_brand (brand_name, company_name, ordered, description, status)values (#{brandName},#{companyName},#{ordered},#{description},#{status});
5.2.3 修改
- 修改所有字段
- 修改动态字段
1.修改所有字段
①编写接口的方法:Mapper接口
void update(Brand brand);
- 参数:所有数据
- 结果:void
②编写SQL语句:编写SQL映射文件
update tb_brandset brand_name = #{brandName},company_name = #{companyName},ordered = #{ordered},description = #{description},status = #{status}where id = #{id};
③执行方法,测试
/** * 修改 * @throws IOException */@Testpublic void testUpdate() throws IOException {//接收参数:int status = 1;String companyName = "波导手机";String brandName = "波导";String description = "波导手机,手机中的战斗机";int ordered = 200;int id = 5;//封装对象Brand brand = new Brand();brand.setStatus(status);brand.setCompanyName(companyName);brand.setBrandName(brandName);brand.setDescription(description);brand.setOrdered(ordered);brand.setId(id);//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession(true);//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法int count = brandMapper.update(brand);System.out.println(count);//5. 释放资源sqlSession.close();}
2. 修改动态字段
①编写接口的方法:Mapper接口
void update(Brand brand);
- 参数:所有数据
- 结果:void
②编写SQL语句:编写SQL映射文件
update tb_brandbrand_name = #{brandName},company_name = #{companyName},ordered = #{ordered},description = #{description},status = #{status}where id = #{id};
③执行方法,测试
/** * 修改动态字段 * @throws IOException */@Testpublic void testUpdate() throws IOException {//接收参数:int status = 0;String companyName = "波导手机";String brandName = "波导";String description = "波导手机,手机中的战斗机";int ordered = 200;int id = 7;//封装对象Brand brand = new Brand();brand.setStatus(status);//brand.setCompanyName(companyName);//brand.setBrandName(brandName);//brand.setDescription(description);//brand.setOrdered(ordered);brand.setId(id);//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession(true);//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法int count = brandMapper.update(brand);System.out.println(count);//5. 释放资源sqlSession.close();}
5.2.4 删除
- 删除一个
- 批量删除
1. 删除一个
①编写接口的方法:Mapper接口
void update(Brand brand);
- 参数:id
- 结果:void
②编写SQL语句:编写SQL映射文件
delete from tb_brand where id = #{id};
③执行方法,测试
/** * 根据Id删除一个 * @throws IOException */@Testpublic void testDeleteById() throws IOException {//接收参数:int id = 9;//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession(true);//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法brandMapper.deleteById(id);//5. 释放资源sqlSession.close();}
2. 批量删除
①编写接口的方法:Mapper接口
void update(Brand brand);
- 参数:id数组
- 结果:void
②编写SQL语句:编写SQL映射文件
<!-- delete from tb_brand where id in/***separator:分隔符**open:循环开始前的字符**close:循环结束后的字符*/ #{id} ;-->delete from tb_brand where idin/***separator:分隔符**open:循环开始前的字符**close:循环结束后的字符*/#{id};
③执行方法,测试
/** * 根据Id删除一个 * @throws IOException */@Testpublic void testDeleteById() throws IOException {//接收参数:int id = 9;//1.获取SqlSessionFactory//加载mybatis的核心配置文件,获取SqlSessionFactoryString resource = "mybatis-config.xml";//返回一个字节输入流InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2. 获取sqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession(true);//3. 获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//4. 执行方法brandMapper.deleteById(id);//5. 释放资源sqlSession.close();}
注意:
Mybatis会将数组参数封装为一个Map集合。*默认:array = 数组*可以使用@Param注解改变map集合的默认key名称
delete from tb_brand where id in/***separator:分隔符**open:循环开始前的字符**close:循环结束后的字符*/ #{id} ;
六、MyBatis参数传递
七、注解完成增删改查
使用注解来映射简单语句会使代码显得更加简洁,但对于稍微复杂一点的语句,Java 注解不仅力不从心,还会让本就复杂的 SQL 语句更加混乱不堪。 因此,如果你需要做一些很复杂的操作,最好用 XML 来映射语句。
选择何种方式来配置映射,以及是否应该要统一映射语句定义的形式,完全取决于你和你的团队。 换句话说,永远不要拘泥于一种方式,你可以很轻松地在基于注解和 XML 的语句映射方式间自由移植和切换。
八、MyBatis的逆向工程
自动生成Mapper接口和对应的实体类以及映射文件
generatorConfig.xml:
mybatis-config.xml:
pom文件中引入的依赖:
jarorg.mybatismybatis3.5.7junitjunit4.12testlog4jlog4j1.2.17mysqlmysql-connector-java8.0.16org.mybatis.generatormybatis-generator-maven-plugin1.3.0org.mybatis.generatormybatis-generator-core1.3.2mysqlmysql-connector-java8.0.16
然后点击Idea中右侧Maven中的mybatis-generator插件即可自动生成。
九、分页插件
limit index,pageSize
index:当前页的起始索引,index=(pageNum-1)*pageSize
pageSize:每页显示条数
pageNum:当前页的页码
count:总记录数
totalPage:总页数
if(count%pageSize !=0){
totalPage+=1;
}
导入依赖:
com.github.pagehelperpagehelper5.2.0
同时还需要在核心配置文件中配置:
查询功能开启前要开启分页功能:
Page
分页数据:
PageInfo pageInfo = new PageInfo(emps, 5);
分页相关数据:
PageInfo{
pageNum=8, pageSize=4, size=2, startRow=29, endRow=30, total=30, pages=8,
list=Page{count=true, pageNum=8, pageSize=4, startRow=28, endRow=32, total=30,
pages=8, reasonable=false, pageSizeZero=false},
prePage=7, nextPage=0, isFirstPage=false, isLastPage=true, hasPreviousPage=true,
hasNextPage=false, navigatePages=5, navigateFirstPage4, navigateLastPage8,
navigatepageNums=[4, 5, 6, 7, 8]
}
pageNum:当前页的页码
pageSize:每页显示的条数
size:当前页显示的真实条数
total:总记录数
pages:总页数
prePage:上一页的页码
nextPage:下一页的页码
isFirstPage/isLastPage:是否为第一页/最后一页
hasPreviousPage/hasNextPage:是否存在上一页/下一页
navigatePages:导航分页的页码数
navigatepageNums:导航分页的页码,[1,2,3,4,5]