Spring Mybatis 基本使用 总结

1. 简介

Mybatis库可以简化数据库的操作，专注于sql语句。

2.搭建步骤

2.1 在pom.xml引入mybatis

<dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.11</version>
</dependency>

2.3 在resources下新建mybatis配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Mapper 3.0/EN""https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><settings>
<!--        <setting name="logImpl" value="STDOUT_LOGGING"/>&lt;!&ndash; 开启mybatis的日志输出 &ndash;&gt;--><setting name="mapUnderscoreToCamelCase" value="true"/><!-- 开启驼峰式自动映射 a_big => aBig --></settings><typeAliases><typeAlias alias="goods" type="com.jojo.pojo.Goods"/><!-- 单独设置别名 --><package name="com.jojo.pojo"/><!-- 批量设置别名, com.jojo.pojo包下的所有类名的别名为类的首字母小写--></typeAliases><environments default="development"><environment id="development"><transactionManager type="JDBC"/>  <!-- 自动开启事务 --><dataSource type="POOLED"><!-- mybatis维护连接池 --><property name="driver" value="com.mysql.cj.jdbc.Driver"/><property name="url" value="jdbc:mysql://localhost:3306/mybatis-example"/><property name="username" value="root"/><property name="password" value="a12345678"/></dataSource></environment></environments><mappers><!-- 指定mapper xml文件的位置 --><mapper resource="mappers/GoodsMapper.xml"/></mappers>
</configuration>

2.3 在resources/mapper下新建mapper的xml配置文件:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0/EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jojo.mapper.GoodsMapper"><!-- 对应Mapper的全限定符 -->
<!--    这里写sql语句 --><insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id">insert into goods (name) value(#{name})</insert><update id="update">update goods set name=#{name} where id=#{id}</update><delete id="delete">delete from goods where id = #{id}</delete><select id="selectById" resultType="goods">select * from goods where id = #{id}</select><select id="selectAll" resultType="goods">select * from goods</select>
</mapper>

2.4 新建pojo类

import lombok.Data;
@Data//lombook插件的@Data标签可以自动生成get和set以及toString方法
public class Goods {private Integer id;private String name;
}

2.5 新建mapper接口

public interface GoodsMapper {int insert(Goods goods);int update(Goods goods);int delete(Integer id);Goods selectById(Integer id);List<Goods> selectAll();
}

2.6 测试

public class MybatisTest {@Testpublic void test() throws IOException {//1.读取外部配置文件InputStream ips = Resources.getResourceAsStream("mybatis-config.xml");//2.创建sqlSessionFactorySqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips);//3.根据sqlSessionFactory创建sqlSessionSqlSession sqlSession = sqlSessionFactory.openSession();//4.获取接口的代理对象，调用代理对象的方法就会查找mapper接口的方法GoosdMapper mapper = sqlSession.getMapper(GoosdMapper.class);Goods goods = mapper.queryById(1);System.out.println(goods);//5.提交事务和释放资源//sqlSession.commit();sqlSession.close();}
}

3.常用mapper语句

3.1 传入值

<!-- #{id} = 使用占位符？，防止sql注入攻击，但不能替代表名表项-->
<!-- ${id} = 不使用占位符？，不能防止sql注入攻击，但可以替代表名表项-->
<select id="queryById" resultType="com.jojo.pojo.Employee">select emp_id empId,emp_name empName, emp_salary empSalary from t_emp where emp_id = #{id}
</select><delete id="deleteById">delete from t_emp where emp_id = #{id} <!-- 传入Integer类型，id可以改写成任意字符串-->
</delete><select id="queryBySalary" resultType="com.jojo.pojo.Employee">select emp_id empId,emp_name empName, emp_salary empSalary from t_emp where empSalary = #{salary} <!-- 传入Double类型，salary可以改写成任意字符串-->
</select><insert id="insertEmp">insert into t_emp (emp_name, emp_salary) values (#{empName},#{empSalary});<!-- 传入对象时，要写传入对象的属性 -->
</insert><select id="queryByNameAndSalary" resultType="com.jojo.pojo.Employee">select emp_id empId,emp_name empName, emp_salary empSalary from t_emp where empSalary = #{a} and empName = #{b} <!-- 传入两个基本类型，根据接口中的@Param("名称")来指定-->
</select><select id="queryByNameAndSalary" resultType="com.jojo.pojo.Employee">select emp_id empId,emp_name empName, emp_salary empSalary from t_emp where empSalary = #{arg0} and empName = #{arg1} <!-- 法2：传入两个基本类型，可以根据顺序来取arg0...arg1...-->
</select><select id="queryByNameAndSalary" resultType="com.jojo.pojo.Employee">select emp_id empId,emp_name empName, emp_salary empSalary from t_emp where empSalary = #{param1} and empName = #{param2} <!-- 法3：传入两个基本类型，可以根据顺序来取param1...param2...-->
</select><insert id="insertEmpMap">insert into t_emp (emp_name, emp_salary) values (#{name},#{salary});<!-- 传入Map时，要写传入Map的key -->
</insert>

3.2 返回值

<select id="queryNameById" resultType="string"><!-- resultType指定返回的类型，写类的全限定符或者mybatis提供的别名(在mybatis官网查)-->select emp_name from t_emp where emp_id = #{id} 
</select><select id="queryById" resultType="employee"> <!-- resultType指定返回的为对象时，select的行需要起别名来与类的属性完全一致-->select emp_id empId,emp_name empName, emp_salary empSalary from t_emp where emp_id = #{id} 
</select><select id="queryById" resultType="employee"><!-- resultType指定返回的为对象时，开启驼峰映射(mapUnderscoreToCamelCase)后，select的行不再需要起别名来与类的属性完全一致-->select * from t_emp where emp_id = #{id}  
</select><select id="selectEmpNameAndMaxSalary" resultType="map"> <!-- resultType返回的值没有未定义类时，可以用map接值，map的每一项的key对应一个列名 -->select emp_name 员工姓名, emp_salary 员工工资, (SELECT AVG(emp_salary) from t_emp) 部门平均工资 from t_emp where emp_salary=(select max(emp_salary) from t_emp)
</select><select id="queryNamesBySalary" resultType="string"><!--如果返回类型时List<String>，那么指定String即可-->select emp_name from t_emp where emp_salary > #{ salary};
</select><select id="queryAll" resultType="employee"><!--如果返回类型时List<Employee>，那么指定Employee即可-->select * from t_emp;
</select><insert id="insertEmp" useGeneratedKeys="true" keyColumn="emp_id" keyProperty="empId"><!-- 主键自增长型：插入时，获取插入的id放在empId中 -->insert into t_emp (emp_name, emp_salary) value(#{empName},#{empSalary});
</insert><insert id="insertTeacher"><selectKey order="BEFORE" resultType="string" keyProperty="tId">select replace(UUID(),'-',''); <!-- 插入前由数据库生成uuid并放在tId中--></selectKey>insert into teacher (t_id,t_name) value (#{tId},#{tName})
</insert>

4.多表查询

4.1 一对一

1对1关系：一个A类中包含一个B类：

public class A {private Integer Id;private String aName;private Integer bId;private B b;
}public class B {private Integer bId;private String bName;
}

使用resultMap来装数据：

<resultMap id="aMap" type="a"><!-- a的主键 id标签--><id column="a_id" property="aId"/><!-- order的普通列 custom标签--><result column="a_name" property="aName"/><result column="b_id" property="bId"/><!-- 给第二层对象属性赋值 --><association property="b" javaType="b"><id column="b_id" property="bId"/><result column="b_name"  property="bName"></result></association>
</resultMap><select id="queryAById" resultMap="aMap">SELECT * FROM t_a ta join t_b tb on ta.b_id = tb.b_id where ta.a_id = #{id};
</select>

在config文件中加入：

<settings><!-- 开启驼峰式自动映射 a_big => aBig --><setting name="mapUnderscoreToCamelCase" value="true"/><!-- 开启自动映射 a_big => aBig --><setting name="autoMappingBehavior" value="FULL"/>
</settings>

后可省略主键以外的映射关系：

<resultMap id="aMap" type="a"><!-- a的主键 id标签--><id column="a_id" property="aId"/><!-- 给第二层对象属性赋值 --><association property="b" javaType="b"><id column="b_id" property="bId"/></association>
</resultMap><select id="queryAById" resultMap="aMap">SELECT * FROM t_a ta join t_b tb on ta.b_id = tb.b_id where ta.a_id = #{id};
</select>

4.2 一对多

1对多关系：一个A类中包含多个B类（List）：

public class A {private Integer Id;private String aName;private Integer bId;private List<B> bList;
}public class B {private Integer bId;private String bName;
}

使用resultMap来装数据：

<resultMap id="aMap" type="a"><id column="a_id" property="aId"/><result column="a_name" property="aName"/><result column="b_id" property="bId"/><!--针对List<A>属性使用collection --><collection property="bList" ofType="b"><id column="b_id" property="bId"></id><result column="b_name" property="bName"/></collection>
</resultMap><select id="queryAList" resultMap="aMap">select * from t_a ta join t_b tb on ta.customer_id = tb.customer_id
</select>

在config文件中加入：

<settings><!-- 开启驼峰式自动映射 a_big => aBig --><setting name="mapUnderscoreToCamelCase" value="true"/><!-- 开启自动映射 a_big => aBig --><setting name="autoMappingBehavior" value="FULL"/>
</settings>

后可省略主键以外的映射关系：

<resultMap id="aMap" type="a"><id column="a_id" property="aId"/><!--针对List<A>属性使用collection --><collection property="bList" ofType="b"><id column="b_id" property="bId"></id></collection>
</resultMap><select id="queryAList" resultMap="aMap">select * from t_a ta join t_b tb on ta.customer_id = tb.customer_id
</select>