【MyBatis】MyBatis简介+MyBatis的快速入门(Mapper代理开发)
MyBatis简介
JDBC的缺点
- 硬编码
注册驱动,获取连接
SQL语句 - 操作繁琐
手动设置参数
手动封装结果集
MyBatis的快速入门——Mapper代理开发
- 添加依赖
mybatis依赖和mysql驱动依赖
<!-- mybatis依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<!-- mysql驱动
驱动版本必须和mysql版本相对应,否则会连接不上
具体版本可以在IDEA连接数据库设置中查看
-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
- MyBatis核心配置文件——替换JDBC的连接信息,解决硬编码问题
mybatis-config.xml
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!--数据库连接信息-->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--加载sql映射文件-->
<mapper resource="com/EveX/mapper/UserMapper.xml"/>
</mappers>
</configuration>
- 编写SQL映射文件——统一管理sql语句,解决硬编码问题
命名规范:映射表对应的类Xxx,映射文件命名为XxxMapper
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.EveX.mapper.UserMapper">
<select id="selectAll" resultType="com.EveX.domain.User">
select * from tb_user;
</select>
</mapper>
-
定义与SQL映射文件同名的Mapper接口,并且将Mapper接口和SQL映射文件放在同一目录下
这样放置文件,编译生成的字节码文件是在同一个目录下
-
设置SQL映射文件的namespace属性为Mapper接口的全限定名
-
在Mapper接口中定义方法,方法名是SQL映射文件中sql语句的id,需要保持参数类型和返回值类型与sql语句一致
UserMapper.java
package com.EveX.mapper;
import com.EveX.domain.User;
import java.util.List;
public interface UserMapper {
List<User> selectAll();
}
- 通过SqlSession的getMapper方法获取Mapper接口的代理对象,然后调用对应方法执行相应sql语句
mybatisQuickDemo.java
package com.EveX;
import com.EveX.domain.User;
import com.EveX.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.InputStream;
import java.io.IOException;
import java.util.List;
/**
* Mybatis快速入门——Mapper代理开发代码
*/
public class mybatisQuickDemo {
public static void main(String[] args) throws IOException {
/*加载mybatis的核心配置文件,获取SqlSessionFactory*/
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
/*获取SqlSession,用来执行sql*/
SqlSession sqlSession = sqlSessionFactory.openSession();
/*执行sql*/
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.selectAll();
System.out.println(users);
/*释放资源*/
sqlSession.close();
}
}
- 注意:pom.xml中mysql驱动依赖版本需要和连接的数据库版本相对应。具体可以参照IDEA连接数据库下载的驱动版本,如下
MyBatis核心配置文件细节
(1) Mapper接口名称和SQL映射文件名称相同,且在同一目录下,可以使用包扫描的方式简化SQL映射文件的加载
(2)类型别名设置:默认情况下的别名:不需要再使用类的全限定名,类名也不再区分大小写,这样SQL映射文件中的resultType就可以简化书写。也可以自定义别名
mybatis-config.xml包扫描加载SQL映射文件
<mappers>
<!--加载sql映射文件-->
<!-- <mapper resource="com/EveX/mapper/UserMapper.xml"/>-->
<!--Mapper接口名称和SQL映射文件名称相同,且在同一目录下,
可以使用包扫描的方式简化SQL映射文件的加载-->
<package name="com/EveX/mapper"/>
</mappers>
mybatis-config.xml类型别名设置
<!--类型别名设置-->
<typeAliases>
<!--相当于给被扫描的包下的类起了别名,
默认情况下的别名:不需要再使用类的全限定名,类名也不再区分大小写
这样SQL映射文件中的resultType就可以简化书写
-->
<package name="com.EveX.domain"/>
</typeAliases>
配置文件完成SQL增删改查
SQL映射文件细节
实体类属性名和数据库表列名不一致如何解决?
参数占位符
SQL语句设置多个参数的方式
映射接口:BrandMapper.java
/*散装参数,用注解Param与SQL参数占位符映射*/
List<Brand> selectByCondition(@Param("status") int status,
@Param("companyName") String companyName,
@Param("brandName") String brandName);
/*实体类封装参数*/
List<Brand> selectByCondition(Brand brand);
/*map集合封装参数*/
List<Brand> selectByCondition(Map map);
SQL配置文件BrandMapper.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.EveX.mapper.BrandMapper">
<!--用结果映射解决类属性和数据库字段名称不一致的问题-->
<resultMap id="brandMap" type="Brand">
<result column="brand_name" property="brandName"></result>
<result column="company_name" property="companyName"></result>
</resultMap>
<select id="selectAll" resultMap="brandMap">
select * from tb_brand;
</select>
<!--<select id="selectAll" resultType="com.EveX.domain.Brand">
select * from tb_brand;
</select>-->
<select id="selectById" resultMap="brandMap">
select * from tb_brand where id = #{id};
</select>
<select id="selectByCondition" resultMap="brandMap">
select *
from tb_brand
where status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName};
</select>
</mapper>
测试代码
/*散装参数*/
List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
System.out.println(brands);
/*实体类封装参数*/
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);
List<Brand> brands1 = brandMapper.selectByCondition(brand);
System.out.println(brands1);
/*map集合封装参数*/
Map map = new HashMap<>();
map.put("status", status);
map.put("companyName", companyName);
map.put("brandName", brandName);
List<Brand> brands2 = brandMapper.selectByCondition(map);
System.out.println(brands2);
动态SQL
多条件-动态查询
SQL配置文件BrandMapper.xml
<select id="selectByCondition" resultMap="brandMap">
select *
from tb_brand
<where>
<if test="status != null">
and status = #{status}
</if>
<if test="companyName != null and companyName != ''">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName != ''">
and brand_name like #{brandName};
</if>
</where>
</select>
单条件-动态查询
其中<choose>
相当于switch,<when>
相当于case,<otherwise>
相当于default
SQL配置文件BrandMapper.xml
<select id="selectByConditionSingle" resultMap="brandMap">
select *
from tb_brand
<where>
<choose>
<when test="status != null">
status = #{status}
</when>
<when test="companyName != null and companyName != ''">
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != ''">
brand_name like #{brandName};
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>
MyBatis事务
SQL配置文件BrandMapper.xml
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand (brand_name, company_name, ordered, description, status)
VALUES (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
</insert>
MyBatis参数传递
数组参数
mybatis会将数组参数封装为一个Map集合,默认情况下:数组名为array,可以使用@Param注解改变map集合的默认key的名称
遍历数组用<foreach>
SQL配置文件BrandMapper.xml
<delete id="deleteByIds">
delete
from tb_brand
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
;
</delete>