MyBatis是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。总的来说,MyBatis就是帮助我们更好地操作MySQL数据库。
1 Lombok插件
安装Lombok插件,类上通过注解的方式,会自动完成getter和setter的编写,提高效率。
<!--所需依赖-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
<scope>provided</scope>
</dependency>
<!--注解-->
@AllArgsConstructor:生成全参构造器。
@NoArgsConstructor:生成无参构造器。
@Getter/@Setter: 作用类上,生成所有成员变量的getter/setter方法;作用于成员变量上,生成该成员变量的getter/setter方法。可以设定访问权限及是否懒加载等。
@Data:作用于类上,是以下注解的集合:@ToString @EqualsAndHashCode @Getter @Setter @RequiredArgsConstructor
@Log:作用于类上,生成日志变量。针对不同的日志实现产品,有不同的注解。
在lombok的作用下,在类上添加@Slf4j注解,便可直接使用日志--log.debug()
2 MyBatis基本操作
通过MyBaits进行MySQL数据库的增删改查。
<!--MyBatis核心依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!--数据库驱动依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.32</version>
</dependency>
<!--日志依赖-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>${logback-classic.version}</version>
</dependency>
<!--druid数据源依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.9</version>
</dependency>
2.1【基本操作】
@Slf4j
public class MyBatisALLTest {
//SQLsession工厂,用于获取会话连接
SqlSessionFactory sqlSessionFactory = null;
@Before
public void MyBatisBefore() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
log.debug("sqlSessionFactory is [{}]", sqlSessionFactory);
}
@Test
public void MyBatisWay(){
try (SqlSession session = sqlSessionFactory.openSession()) {
PageHelper.startPage(1, 5);
List<Object> objects = session.selectList(IUserMapper.class.getName() + ".selectAll");
log.debug("result is [{}]", objects);
}
}
@Test
public void MyBatisLikeUN(){
try (SqlSession session = sqlSessionFactory.openSession()) {
IUserMapper mapper = session.getMapper(IUserMapper.class);
List<User> blog = mapper.selectLikeUN("%" + "s" + "%");
log.debug("result is [{}]", blog);
}
}
@Test
public void MyBatisELD(){
try (SqlSession session = sqlSessionFactory.openSession(true)) {
IEmployeeMapper mapper = session.getMapper(IEmployeeMapper.class);
List<Employee> blog = mapper.selectEmployee();
log.debug("result is [{}]", blog);
}
}
}
2.2【结果集映射】
如果实例对象的成员变量和数据库的变量命名对应不上,查出的值会为null。
//UserMapper.xml
<resultMap id="UserMap" type="User">
<!-- id为主键 -->
<id column="id" property="id"/>
<!-- column是数据库表的列名 , property是对应实体类的属性名 -->
<result column="user_name" property="username"/>
<result column="password" property="password"/>
</resultMap>
<select id="selectUserById" resultMap="UserMap">
select id, username, password from user where id = #{id}
</select>
2.3【动态SQL】
MyBatis提供了对SQL语句动态的组装能力,大量的判断都可以在 MyBatis的映射XML文件里面配置,以达到许多我们需要大量代码才能实现的功能,大大减少了我们编写代码的工作量。
①if
<select id="selectByUser" resultType="User">
select * from user
where 1 =1
<if test="id != null">
AND id = #{id}
</if>
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="password != null and password != ''">
AND password = #{password}
</if>
</select>
②where
<select id="selectByUser" resultType="User">
select * from user
<where>
<if test="id != null">
AND id = #{id}
</if>
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="password != null and password != ''">
AND password = #{password}
</if>
<where>
</select>
③trim
下面的SQL表示,当id,username和password当中有一个存在时,会把sql语句中最前面的一个“AND”替换成“WHERE”,防止最后的sql语句出现语法错误。
<select id="selectByUser" resultType="User">
select * from user
<trim prefix="WHERE" prefixOverrides="AND">
<if test="id != null">
AND id = #{id}
</if>
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="password != null and password != ''">
AND password = #{password}
</if>
</trim>
</select>
④choose、when、otherwise
<select id="selectByUP" resultType="User">
select * from user
<trim prefix="WHERE" prefixOverrides="AND">
<choose>
<when test="id != null">
AND id = #{id}
</when>
<when test="username != null and username != ''">
AND username = #{username}
</when>
<when test="password != null and password != ''">
AND password = #{password}
</when>
<otherwise>
<!-- 此处设置默认执行的语句 -->
</otherwise>
</choose>
</trim>
</select>
⑤set
set元素遇到','会自动把','去掉。
<update id="updateUser" parameterType="User">
UPDATE user
<set>
<if test="username != null and username != ''">
username = #{username},
</if>
<if test="password != null and password != ''">
password = #{password}
</if>
</set>
WHERE id = #{id}
</update>
⑥foreach
foreach元素是一个循环语句,它的作用是遍历集合,可以支持数组、List、Set接口。
<!--批量删除-->
int deleteByIds(@Param("ids")List<Integer> ids);
<delete id="deleteByIds" parameterType="User">
delete from user
WHERE id IN
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
<!--foreach的参数-->
1.collection配置的是传递进来的参数名称。
2.item配置的是循环中当前的元素。
3.index配置的是当前元素在集合的位置下标。
4.open和 close配置的是以什么符号将这些集合元素包装起来。
5.separator是各个元素的间隔符。
<!--批量插入-->
int addManyUser(@Param("users")List<User> users);
<insert id="addManyUser" parameterType="User">
insert into `user`(username, password)
values
<foreach collection="users" item="user" separator=",">
(#{user.username}, #{user.password})
</foreach>
</insert>
2.4 SQL片段
针对经常使用的SQL语句,将其封装起来,方便使用。
<sql id="if-title-author">
<trim prefix="WHERE" prefixOverrides="AND">
<if test="id != null">
AND id = #{id}
</if>
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="password != null and password != ''">
AND password = #{password}
</if>
</trim>
</sql>
<sql id="param">
id, username, password
</sql>
<!--使用sql片段,节省代码量-->
<select id="selectByUser" resultType="User">
select <include refid="param"/> from user
<include refid="if-title-author" />
</select>
2.5 多表联查
public class Employee implements Serializable {
private static final Long serialVersionUID = 1L;
private Integer id;
private String name;
//维护表关系
private Dept dept;
}
public class Dept implements Serializable {
private static final Long serialVersionUID = 1L;
private Integer id;
private String name;
//维护表关系
private List<Employee> employee;
}
#association--员工级联部门,多对一
#按查询嵌套
一种级联查询的方式,会产生多个sql语句,第一个sql的查询语句结果会触发第二个查询语句。
<!--DeptMapper.xml-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cql.dao.IDeptMapper">
<resultMap id="DeptMap" type="Dept">
<id column="id" property="id"/>
<result column="name" property="name"/>
</resultMap>
<sql id="param">
`id`, `name`
</sql>
<select id="selectDept" resultMap="DeptMap">
select <include refid="param"/>
from dept where id = #{id}
</select>
</mapper>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cql.dao.IEmpolyeeMapper">
<!-- 结果集映射 -->
<!-- 按查询嵌套 -->
<resultMap id="EmployeeMap" type="Employee">
<id column="id" property="id" />
<result column="name" property="name"/>
<!-- 多表联查 -->
<!-- 通过向 DeptMapper.xml传递did,查询出类Dept的属性值,将其传递给Employee类的dept成员变量,property指代的是Employee中的dept成员变量名 -->
<association property="dept" column="did" javaType="Dept"
select="com.cql.dao.IDeptMapper.selectDept">
<id column="id" property="id"/>
<result column="name" property="name"/>
</association>
</resultMap>
<sql id="param">
`id`, `name`, `did`
</sql>
<!-- 按查询嵌套 -->
<select id="selectEmployee" resultMap="EmployeeMap">
select <include refid="param" />
from employee
</select>
</mapper>
#按结果嵌套--常用
使用复杂查询,在根据结果的字段进行对象的封装,本质只会发送一个sql。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cql.dao.IEmpolyeeMapper">
<!-- 结果集映射 -->
<!-- 按结果嵌套 -->
<resultMap id="EmployeeMap" type="Employee">
<id column="eid" property="id" />
<result column="ename" property="name"/>
<association property="dept" javaType="dept" >
<id column="did" property="id"/>
<result column="dname" property="name"/>
</association>
</resultMap>
<sql id="param">
`id`, `name`, `did`
</sql>
<!-- 按结果嵌套 -->
<select id="selectEmployee" resultMap="EmployeeMap" >
select e.id eid,e.name ename, d.id did,d.name dname
from employee e left join dept d
on d.id = e.did
</select>
</mapper>
#collection--部门级联员工,一对多
#按查询嵌套
<!--Employee.xml-->
<mapper namespace="com.cql.dao.IEmpolyeeMapper">
<!-- 结果集映射 -->
<!-- 按查询嵌套 -->
<resultMap id="EmployeeMap2" type="Employee">
<id column="eid" property="id" />
<result column="ename" property="name"/>
</resultMap>
<sql id="param">
`id`, `name`, `did`
</sql>
<!-- 通过部门查询员工 -->
<select id="selectEmployeeByDId" resultMap="EmployeeMap2">
select id eid, name ename from employee where did = #{id}
</select>
</mapper>
<mapper namespace="com.cql.dao.IDeptMapper">
<resultMap id="DeptMap" type="Dept">
<id column="id" property="id"/>
<result column="name" property="name"/>
<!-- 将其传递给Dept类的employee成员变量,返回结果是List,链表成员变量是Employee -->
<collection property="employee" javaType="List" ofType="Employee"
column="id" select="com.cql.dao.IEmpolyeeMapper.selectEmployeeByDId">
<id column="eid" property="id"/>
<result column="ename" property="name"/>
</collection>
</resultMap>
<sql id="param">
`id`, `name`
</sql>
<select id="selectALL" resultMap="DeptMap">
select <include refid="param"/>
from dept
</select>
</mapper>
#按结果嵌套--常用
<mapper namespace="com.cql.dao.IDeptMapper">
<resultMap id="DeptMap" type="Dept">
<id column="did" property="id"/>
<result column="dname" property="name"/>
<!-- 将其传递给Dept类的employee成员变量,返回结果是List,链表成员变量是Employee -->
<collection property="employee" javaType="List" ofType="Employee">
<id column="eid" property="id"/>
<result column="ename" property="name"/>
</collection>
</resultMap>
<sql id="param">
`id`, `name`
</sql>
<select id="selectDept" resultMap="DeptMap">
select <include refid="param"/>
from dept where id = #{id}
</select>
<select id="selectALL" resultMap="DeptMap">
select d.id did, d.name dname, e.id eid, e.name ename
from dept d
left join employee e on d.id = e.did
</select>
</mapper>
3 切换数据源
public class DruidDataSourceFactory implements DataSourceFactory {
private Properties properties;
@Override
public void setProperties(Properties properties) {
this.properties = properties;
}
@Override
public DataSource getDataSource() {
DruidDataSource dataSource= new DruidDataSource();
dataSource.configFromPropety(properties);
return dataSource;
}
}
4 注解配置总结
<!--用于自动生成getter和setter-->
@Data
@AllArgsConstructor
@NoArgsConstructor
<!--多参数匹配问题-->
@Param
int addUserP(@Param("ids") Integer id, @Param("usernames") String username, @Param("passwords") String password);
<insert id="addUserP" parameterType="User">
insert into user (id,username,password) values (#{ids},#{usernames},#{passwords})
</insert>
<!--接口中添加注解开发,无需使用XML文件配置命令,Java注解的表达力和灵活性十分有限,仅作为了解-->
@Select()
@Update()
@Delete()
@Insert()
@Insert("insert into user (id,username,password) values (#{ids},#{usernames},#{passwords})")
int addUserP(@Param("ids") Integer id, @Param("usernames") String username, @Param("passwords") String password);
5 XMl配置文件配置总结
<!--mybatisconfig.xml-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--数据库账户密码-->
<properties>
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/myself_ssm?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</properties>
<!--设置日志-->
<settings>
<!-- <setting name="logImpl" value="SLF4J"/>-->
<setting name="logPrefix" value="MyBatisSQL."/>
</settings>
<!--为每个返回类型起别名-->
<typeAliases>
<typeAlias type="com.cql.entity.User" alias="User"/>
<typeAlias type="com.cql.entity.Employee" alias="Employee"/>
<typeAlias type="com.cql.entity.Dept" alias="Dept"/>
<typeAlias type="com.cql.datasource.DruidDataSourceFactory" alias="DRUID"/>
</typeAliases>
<!--分页插件-->
<plugins>
<plugin interceptor="com.cql.plugin.ExamplePlugin">
<property name="someProperty" value="100"/>
</plugin>
<!-- com.github.pagehelper为PageHelper类所在包名 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 使用下面的方式配置参数,后面会有所有的参数介绍 -->
<!-- <property name="param1" value="value1"/> -->
</plugin>
</plugins>
<!--设置所使用的数据源-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="DRUID">
<property name="druid.driverClassName" value="${driver}"/>
<property name="druid.url" value="${url}"/>
<property name="druid.username" value="${username}"/>
<property name="druid.password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--定义mapper资源,将配置文件注册到主配置文件中-->
<mappers>
<mapper resource="Mapper/UserMapper.xml"/>
<mapper resource="Mapper/EmployeeMapper.xml"/>
<mapper resource="Mapper/DeptMapper.xml"/>
</mappers>
</configuration>
<!--UserMapper.xml-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cql.Dao.IUserMapper">
<select id="selectAll" resultType="User">
select * from user
</select>
<!--#{password} 与实体类中参数的变量名要一致,resultType是返回类型-->
<select id="selectByUser" resultType="User">
select * from user where password = #{password} and username = #{username}
</select>
<select id="selectByMap" resultType="User">
select * from user where password = #{password} and username = #{username}
</select>
<!--模糊查询-->
<select id="selectLikeUN" resultType="User">
select * from user where username like #{name}
</select>
<insert id="addUser" parameterType="User">
insert into user (id,username,password) values (#{id},#{username},#{password})
</insert>
</mapper>
6 依赖配置总结
<!--lombok依赖-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
<scope>provided</scope>
</dependency>
<!--MyBatis核心依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!--数据库驱动依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.32</version>
</dependency>
<!--日志依赖-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>${logback-classic.version}</version>
</dependency>
<!--druid数据源依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.9</version>
</dependency>