尚大(硅谷)SpringMVC课件
尚硅谷Spring课件(自己整理的)
此课件是我自己整理的,所以代码部分可能只有我能看懂,请见谅,如果想要MD版本的,有空我会传到GitHub上,如有需要,评论私我。
1、配置文件
Mybatis-config配置文件
<?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文件,此后就可以在当前文件中使用${key}的方式访问value> -->
<properties resource="jdbc.properties" />
<!--
typeAliases:设置类型别名,在mybatis的范围内,就可以使用别名表示一个具体的类型
-->
<typeAliases>
<!--
type:设置需要起别名的类型
alias:设置某个类型的别名
若不设置,当前的类型拥有的是全类名
-->
<!--<typeAlias type="com.atguigu.mybatis.pojo.User" alias="abc"></typeAlias>-->
<!--通过包来设置类型别名,指定包下所有类型将全部拥有默认的别名,即类名且不区分大小写-->
<package name="com.atguigu.mybatis.pojo"/>
</typeAliases>
<environments default="development">
<!--
environments:配置连接数据库的环境
default:设置默认使用的环境的id
environment:设置一个具体的连接数据库的环境属性
id:设置环境的唯一标识,不能重复
transactionManager:设置事务管理器
type:设置事务管理的方式 JDBC|MANAGED
JDBC:表示使用JDBC中原生的事务管理方式
MANAGED:被管理,例如spring
-->
<environment id="development">
<transactionManager type="JDBC"/>
<!--
dataSource:设置数据源
属性:
type:设置数据源的类型
type="POOLED|UNPPPLED|JDNI"
POOLED:表示使用数据库连接池
UNPPPLED表示不使用数据库连接池
JDNI:表示使用上下文中的数据源
-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ssm"/>
<property name="username" value="root"/>
<property name="password" value="980126ws"/>
</dataSource>
</environment>
</environments>
<!--引入mybatis的映射文件-->
<mappers>
<!--<mapper resource="mappers/UserMapper.xml"/>-->
<!--
以包的方式引入映射文件,但是必须满足两个条件:
1.mapper接口和映射文件所在的包必须一致
2.mapper接口的名字和映射文件必须一致
-->
<package name="com.atguigu.mybatis.mapper"/>
</mappers>
</configuration>
Mapper映射文件
<?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.atguigu.mybatis.mapper.UserMapper">
<!--mapper接口和映射文件要保证两个一致
1.mapper接口的全类名和映射文件的namespace一致
2.mapper接口中的方法的方法名要和映射文件中的sql的id保持一致
-->
<!--int insertUser()-->
<insert id="insertUser">
insert into t_user values (null,'admin','123456',23,'男','12345@qq.com')
</insert>
<update id="updateUser">
update t_user set username='root',password='123'where id = 3;
</update>
<delete id="deleteUser">
delete from t_user where id = 3;
</delete>
<!--resultType 设置结果类型,即查询的数据要转换为的java类型
resultMap:自定义映射,处理多对一或者一对多的映射关系
-->
<select id="getUserById" resultType="com.atguigu.mybatis.pojo.User">
select * from t_user where id = 1;
</select>
<select id="getAllUser" resultType="user">
select * from t_user
</select>
</mapper>
Mapper接口
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.User;
import java.util.List;
public interface UserMapper {
//添加用户信息
int insertUser();
//修改用户信息
int updateUser();
//删除用户信息
void deleteUser();
//根据ID查询
User getUserById();
List<User> getAllUser();
}
测试
public void testInsert() throws IOException {
//获取核心配置文件的输入流
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//获取sqlSessionFactoryBuilder
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//获取sqlSessionFactoryBuilder
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//获取sql的会话对象sqlSession,是Mybatis提供的操作数据库的对象
//不会自动提交事务
//SqlSession sqlSession = sqlSessionFactory.openSession(true);
//会自动提交事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//获取userMapper的代理实现类对象
/*
提供sql以及的唯一表示找到sql并执行,唯一标识是namespace.sqlID
UserMapper mapper = sqlSession.getMapper(“com.atguigu.mybatis.mapper.UserMapper.insertUser");
*/
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//调用mapper接口中的方法,实现添加用户信息的功能
int result = mapper.insertUser();
System.out.println("结果=" + result);
sqlSession.commit();
sqlSession.close();
}
2、Mybatis获取参数的两种方式
MyBatis获取参数值的两种方式:${}和#{}
**${}的本质就是字符串拼接,#{}**的本质就是占位符赋值
**${}**使用字符串拼接的方式拼接sql,若为字符串类型或日期类型的字段进行赋值时,需要手动加单
引号;
但是**#{}**使用占位符赋值的方式拼接sql,此时为字符串类型或日期类型的字段进行赋值时,
可以自动添加单引号;
2.1 单个字面量类型的参数
-
若mapper接口中的方法参数为单个的字面量类型
-
此时可以使用KaTeX parse error: Expected 'EOF', got '#' at position 4: {}和#̲{}以任意的名称获取参数的值,…{}需要手动加单引号
2.2 多个字面量类型的参数
若mapper接口中的方法参数为多个时,此时MyBatis会自动将这些参数放在一个map集合中,以arg0,arg1…为键,以参数为值;以
param1,param2…为键,以参数为值;因此只需要通过${}和#{}访问map集合的键就可以获取相
对应的值,注意${}需要手动加单引号;
2.3 map集合类型的参数
若mapper接口中的方法需要的参数为多个时,此时可以手动创建map集合,将这些数据放在
map中,只需要通过KaTeX parse error: Expected 'EOF', got '#' at position 4: {}和#̲{}访问map集合的键就可以获…{}需要手动加单引号
2.4 实体类类型的参数
若mapper接口中的方法参数为实体类对象时,此时可以使用KaTeX parse error: Expected 'EOF', got '#' at position 4: {}和#̲{},通过访问实体类对象中的属…{}需要手动加单引号
2.5 使用@Param标识参数
可以通过@Param注解标识mapper接口中的方法参数
此时,会将这些参数放在map集合中,以@Param注解的value属性值为键,以参数为值;以
param1,param2…为键,以参数为值;只需要通过${}和#{}访问map集合的键就可以获取相对应
的值,
注意${}需要手动加单引号
2.6 代码部分
ParameterTest
package com.atguigu.mybatis;
import com.atguigu.mybatis.mapper.UserMapper;
import com.atguigu.mybatis.pojo.User;
import com.atguigu.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
public class ParameterTest {
//根据用户名查询
@Test
public void testGetUserByUsername() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUsername("admin");
System.out.println(user);
}
//验证用户登录
@Test
public void testCheckUserByUsername() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.checkLogin("admin","123456");
System.out.println(user);
}
@Test
public void testCheckUserByMap() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String,Object> map = new HashMap<>();
map.put("username","admin");
map.put("password","123456");
User user = mapper.checkLoginByMap(map);
System.out.println(user);
}
@Test
public void testInsertUser() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User(null, "root", "123456", 33, "女", "123@qq.com");
mapper.insertuser(user);
System.out.println(user);
}
@Test
public void testCheckLoginByParam() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.checkLoginByParam("admin", "123456");
System.out.println(user);
}
}
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.atguigu.mybatis.mapper.UserMapper">
<select id="getUsername" resultType="user">
select * from t_user where username = #{username};
</select>
<select id="checkLogin" resultType="user">
select * from t_user where username = #{arg0} and password =#{arg1}
</select>
<select id="checkLoginByMap" resultType="user">
select * from t_user where username = #{username} and password = #{password}
</select>
<insert id="insertuser">
insert into t_user values (null,#{username},#{password},#{age},#{gender},#{email})
</insert>
<select id="checkLoginByParam" resultType="user">
select * from t_user where username = #{username} and password = #{password}
</select>
</mapper>
UserMapper接口
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;
import java.util.Map;
public interface UserMapper {
//根据用户名查询用户信息
User getUsername(String username);
//验证登录
User checkLogin(String username,String password);
//验证登录2
User checkLoginByMap(Map<String,Object> map);
//添加用户信息
void insertuser(User user);
//验证登录,使用注解
User checkLoginByParam(@Param("username") String username,@Param("password") String password);
}
3、Mybatis的各种查询功能
3.1查询一个实体类对象
3.2查询一个list集合
3.3查询单个数据
3.4查询一条数据为map集合
3.5查询多条数据为map集合
方式一
方式二
3.6 代码部分
SelectMapperTest
package com.atguigu.mybatis;
import com.atguigu.mybatis.mapper.SelectMapper;
import com.atguigu.mybatis.pojo.User;
import com.atguigu.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
import java.util.List;
import java.util.Map;
public class SelectMapperTest {
@Test
public void testGetUserById() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
}
@Test
public void testGetAllUser() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
List<User> user = mapper.getAllUser();
System.out.println(user);
}
@Test
public void testGetCount() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Integer count = mapper.getCount();
System.out.println(count);
}
@Test
public void testGetuserByIdToMap() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Map<String, Object> map = mapper.getUserByIdMap(1);
System.out.println(map);
}
@Test
public void testGetAllUserToMap() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Map<String, Object> map = mapper.getAllUserToMap();
// List<Map<String, Object>> map = mapper.getAllUserToMap();
System.out.println(map);
}
}
selectMapper.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.atguigu.mybatis.mapper.SelectMapper">
<select id="getUserById" resultType="user">
select * from t_user where id = #{id};
</select>
<select id="getAllUser" resultType="user">
select * from t_user
</select>
<select id="getCount" resultType="java.lang.Integer">
select count(1) from t_user;
</select>
<select id="getUserByIdMap" resultType="map">
select * from t_user where id = #{id};
</select>
<select id="getAllUserToMap" resultType="map">
select * from t_user
</select>
</mapper>
SelectMapper接口
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.User;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface SelectMapper {
//查询一个User对象,通过Id
User getUserById(@Param("id") Integer id);
//查询所有的用户信息
List<User> getAllUser();
//查询用户的总数量
Integer getCount();
//根据用户id查询用户信息为map的集合
Map<String,Object> getUserByIdMap(@Param("id")Integer id);
/**
* 查询所有的用户信息为map的集合
*
* 若查询的数据有多条时,并且要将每条数据转换为map集合
* 此时有两种解决方案,
* 1.List<Map<String,Object>>getAllUserToMap();
* 2.如下
* @return
*/
@MapKey("id")
Map<String,Object>getAllUserToMap();
}
4、特殊SQL的执行
4.1模糊查询
4.2批量删除
4.3动态设置表名
4.4添加功能获取自增的主键
4.5 代码部分
SpeaialSQLTest
package com.atguigu.mybatis;
import com.atguigu.mybatis.mapper.SpecialSQLMapper;
import com.atguigu.mybatis.pojo.User;
import com.atguigu.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
import java.util.List;
public class SpeaialSQLTest {
@Test
public void testGetUserByLike() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
List<User> list = mapper.getUserByLike("a");
list.forEach(System.out::println);
}
@Test
public void testDeleteMoreUser() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
mapper.deleteMoreUser("9,10");
}
@Test
public void testGetUserList() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
List<User> t_user = mapper.getUserList("t_user");
t_user.forEach(System.out::println);
}
@Test
public void testInsertUser() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
User user = new User(null, "xiaoming", "123456", 23, "男", "123@qq.com");
mapper.inserUser(user);
}
}
SpecialSQLMapper.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.atguigu.mybatis.mapper.SpecialSQLMapper">
<select id="getUserByLike" resultType="user">
/*
解决方案
/*select * from t_user where username like '%#{mohu}%'*/ 错误
*/
/*select * from t_user where username like '%${mohu}%'*/
/*select * from t_user where username like concat('%',#{mohu},'%')*/
select * from t_user where username like "%"#{mohu}"%"
</select>
<delete id="deleteMoreUser">
delete from t_user where id in (${ids})
</delete>
<select id="getUserList" resultType="user">
select * from ${tableName};
</select>
<!--
useGeneratedKeys:表示当前添加功能是同自增的主键
keyProperty:将添加的数据的自增主键为实体类型的参数的属性赋值
-->
<insert id="inserUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user values (null,#{username},#{password},#{age},#{gender},#{email})
</insert>
</mapper>
SpecialSQLMapper
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface SpecialSQLMapper {
//通过用户名模糊查询
List<User> getUserByLike(@Param("mohu") String mohu);
//批量删除
void deleteMoreUser(@Param("ids") String ids);
//动态设置表名
List<User> getUserList(@Param("tableName") String tableName);
//添加用户信息,并获取自增的主键
void inserUser(User user);
}
5、自定义映射resultMap
5.1 resultMap处理字段和属性的映射关系
若字段名和实体类中的属性名不一致,则可以通过resultMap设置自定义映射
5.2 多对一映射处理
场景模拟:
查询员工信息以及员工所对应的部门信息
5.2.1 级联方式处理映射关系
5.2.2 使用association处理映射关系
5.2.3 分步查询
①查询员工信息
②根据员工所对应的部门id查询部门信息
5.3 一对多映射处理
5.3.1 collection
5.3.2 分步查询
①查询部门信息
②根据部门id查询部门中的所有员工
5.4 代码部分
ResultMapTest
import com.atguigu.mybatis.mapper.DeptMapper;
import com.atguigu.mybatis.mapper.EmpMapper;
import com.atguigu.mybatis.pojo.Dept;
import com.atguigu.mybatis.pojo.Emp;
import com.atguigu.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
public class ResultMapTest {
@Test
public void testGetEmpByEmpId() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpByEmpId(1);
System.out.println(emp);
}
@Test
public void testGetEmpAndDeptByEmpId() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpAndDeptByEmpId(1);
System.out.println(emp);
}
@Test
public void testGetEmpAndDeptByStep() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpAndDeptByStepOne(2);
System.out.println(emp);
}
@Test
public void testGetDeptAndEmpByDeptId() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept dept = mapper.getDeptAndEmpByDeptId(1);
System.out.println(dept);
}
@Test
public void testGetDeptAndEmpByStep() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept dept = mapper.getDeptAndEmpByStepOne(1);
System.out.println(dept);
}
}
EmpMapper.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.atguigu.mybatis.mapper.EmpMapper">
<!-- 字段名和属性名不一致的情况下,如何处理:-->
<!-- 1.为查询的的字段设置别名,和属性名保持一致-->
<!-- 2.当字段符合MySQL的要求使用_,而属性符合java的要求使用驼峰-->
<!-- 此时可以在Mybatis的核心配置文件中设置一个全局配置,可以自动将下划线映射为驼峰-->
<!-- 3.使用resultMap自定义映射
-->
<!--第一种-->
<!--<select id="getEmpByEmpId" resultType="emp">
select emp_id empId,emp_name empName ,age,gender from t_emp where emp_id = #{empId};
</select>-->
<!--第二种-->
<!--<select id="getEmpByEmpId" resultType="emp">
select * from t_emp where emp_id = #{empId};
</select>-->
<!--第三种-->
<!--resultMap:设置自定义的映射关系
id:唯一标识
type:处理映射关系的实体类的类型
-->
<resultMap id="empResultMap" type="Emp">
<!--id 为主键
result:为其他普通字段
column:设置映射关系中的字段名,必须是sql查询出的某个字段
property:这只映射关系中的属性的属性名,必须是处理的实体类类型中的属性名
association:处理多对一的映射关系(来处理实体类类型的属性)
collection:处理一对多的映射关系(处理集合类型的属性)
-->
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
</resultMap>
<select id="getEmpByEmpId" resultMap="empResultMap">
select * from t_emp where emp_id = #{empId};
</select>
<!--处理多对一的映射关系
1.级联方式处理
2.association
3.分步查询
-->
<!--处理一对多的关系:
1.collection
2.分步查询
-->
<resultMap id="empAndDeptResultMapOne" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<result column="dept_id" property="dept.deptId"></result>
<result column="dept_name" property="dept.deptName"></result>
</resultMap>
<resultMap id="empAndDeptResultMap" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<association property="dept" javaType="Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
</association>
</resultMap>
<select id="getEmpAndDeptByEmpId" resultMap="empAndDeptResultMap">
select *from t_emp
left join t_dept on t_emp.dept_id = t_dept.dept_id
where t_emp.emp_id = #{empId}
</select>
<resultMap id="empAndDeptByStepResultMap" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<!--
property:设置需要处理映射关系的属性的属性名
select:设置分布查询的sql的唯一标识
column:将查询出的某个字段作为分步查询的sql的条件
fetchType:在开启了延迟加载的环境中,通过该属性设置当前的分步查询是否使用延迟加载
fetchType:eager 立即加载 lazy:延迟加载
-->
<association property="dept" fetchType="eager"
select="com.atguigu.mybatis.mapper.EmpMapper.getEmpAndDeptByStepOne"
column="dept_id">
</association>
</resultMap>
<select id="getEmpAndDeptByStepOne" resultMap="empAndDeptByStepResultMap">
select * from t_emp where emp_id = #{empId}
</select>
<!--List<Emp> getDeptAndEmpByStepTwo(@Param("deptId") Integer deptId);-->
<select id="getDeptAndEmpByStepTwo" resultType="Emp">
select * from t_dept where dept_id = #{deptId}
</select>
</mapper>
DeptMapper.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.atguigu.mybatis.mapper.DeptMapper">
<select id="getEmpAndDeptByStepTwo" resultType="Dept">
select * from t_dept where dept_id = #{deptId}
</select>
<resultMap id="deptAndEmpResultMap" type="Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
<collection property="emps" ofType="Emp">
<id column="emp_id" property="empId"></id>
<id column="emp_name" property="empName"></id>
<id column="age" property="age"></id>
<id column="gender" property="gender"></id>
</collection>
</resultMap>
<select id="getDeptAndEmpByDeptId" resultMap="resultMap">
select *
from t_dept
left join t_emp
on t_dept.dept_id = t_emp.dept_id
where t_dept.dept_id = #{deptId}
</select>
<resultMap id="deptAndEmpResultMapByStep" type="Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
<collection property="emps"
select="com.atguigu.mybatis.mapper.DeptMapper.getDeptAndEmpByStepTwo"
column="dept_id">
</collection>
</resultMap>
<select id="getDeptAndEmpByStepOne" resultMap="">
select * from t_dept where dept_id = #{deptId}
</select>
</mapper>
EmpMapper
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.Emp;
import org.apache.ibatis.annotations.Param;
public interface EmpMapper {
//根据Id查询员工信息
Emp getEmpByEmpId(@Param("empId") Integer empId);
//获取员工以及所对应的部门信息
Emp getEmpAndDeptByEmpId(@Param("empId") Integer empId);
//通过分步查询员工以及所对应的部门信息的第一步
Emp getEmpAndDeptByStepOne(@Param("empId") Integer empId);
}
DeptMapper
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.Dept;
import com.atguigu.mybatis.pojo.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface DeptMapper {
//通过分步查询员工以及所对应的部门信息的第二步
Dept getEmpAndDeptByStepTwo(@Param("deptId") Integer deptId);
//查询部门以及部门中的员工信息(一对多)
Dept getDeptAndEmpByDeptId(@Param("deptId") Integer deptId);
//通过分步查询查询部门以及部门中的员工信息的第一步
Dept getDeptAndEmpByStepOne(Integer deptId);
//通过分步查询查询部门以及部门中的员工信息的第二步
List<Emp> getDeptAndEmpByStepTwo(@Param("deptId") Integer deptId);
}
6、动态SQL
Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了
解决 拼接SQL语句字符串时的痛点问题。
6.1 IF
if标签可通过test属性的表达式进行判断,若表达式的结果为true,则标签中的内容会执行;反之
标签中的内容不会执行
6.2 where
where和if一般结合使用:
a>若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字
b>若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的
and去掉
注意:where标签不能去掉条件最后多余的and
6.3 trim
trim用于去掉或添加标签中的内容
常用属性:
prefix:在trim标签中的内容的前面添加某些内容
prefixOverrides:在trim标签中的内容的前面去掉某些内容
suffix:在trim标签中的内容的后面添加某些内容
suffixOverrides:在trim标签中的内容的后面去掉某些内容
6.4 choose、when、otherwise
choose、when、 otherwise相当于if…else if…else
6.5 foreach
6.6 SQL片段
6.7 代码部分
DynamicMapperTest
package com.atguigu.mybatis.test;
import com.atguigu.mybatis.mapper.DynamicSQLMapper;
import com.atguigu.mybatis.pojo.Emp;
import com.atguigu.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
public class DynamicMapperTest {
@Test
//where 标签 (One and Two)
public void testGetEmpByCondition() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp = new Emp(null,"张三",20,"男");
List<Emp> list = mapper.getEmpByCondition(emp);
list.forEach(System.out::println);
}
@Test
//where 标签 (One and Two)
public void testGetEmpByChoose() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp = new Emp(null,"张三",20,"");
List<Emp> list = mapper.getEmpByChoose(emp);
list.forEach(System.out::println);
}
@Test
public void testInsertMoreEmp() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp1 = new Emp(null,"小明",20,"男");
Emp emp2 = new Emp(null,"小明",20,"男");
Emp emp3 = new Emp(null,"小明",20,"男");
List<Emp> list = Arrays.asList(emp1, emp2, emp3);
mapper.insertMoreEmp(list);
}
@Test
public void testdeleteMoreEmp() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Integer[] empIds = new Integer[]{6, 7};
mapper.deleteMoreEmp(empIds);
}
}
DynamicSQLMapper.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.atguigu.mybatis.mapper.DynamicSQLMapper">
<!--
3.trim用于去掉或添加标签中的内容
常用属性:
prefix:在trim标签中的内容的前面添加某些内容
prefixOverrides:在trim标签中的内容的前面去掉某些内容
suffix:在trim标签中的内容的后面添加某些内容
suffixOverrides:在trim标签中的内容的后面去掉某些内容
-->
<select id="getEmpByCondition" resultType="Emp">
<trim prefix="where" suffixOverrides="and">
select * from t_emp
<if test="empName != null and empName != ''">
emp_name = #{empName} and
</if>
<if test="age != null and age != ''">
age = #{age} and
</if>
<if test="gender != null and gender != ''">
gender = #{gender}
</if>
</trim>
</select>
<!--
2.where 标签:where和if一般结合使用:
a>若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字
b>若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的
and去掉
注意:where标签不能去掉条件最后多余的and
-->
<select id="getEmpByConditionTwo" resultType="Emp">
select * from t_emp
<where>
<if test="empName != null and empName != ''">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</where>
</select>
<select id="getEmpByConditionOne" resultType="Emp">
select * from t_emp where 1 = 1
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</select>
<!--
1.if标签:可通过test属性的表达式进行判断,若表达式的结果为true,则标签中的内容会执行;反之
标签中的内容不会执行
-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp where
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</select>
<!--
4.choose、when、 otherwise相当于if...else if..else
-->
<select id="getEmpByChoose" resultType="Emp">
select * from t_emp
<where>
<choose>
<when test="empName != null and empName != ''">
emp_name = #{empName}
</when>
<when test="age != null and age != ''">
age = #{age}
</when>
<when test="gender != null and gender != ''">
gender = #{gender}
</when>
</choose>
</where>
</select>
<!--
5.foreach标签:
collection:设置要循环的数组或集合
item:用一个字符串表示数组或集合的每一个数据
separator:设置每次循环的数据之间的分隔符
open:循环以什么开始
close:循环以什么结束
-->
<insert id="insertMoreEmp">
insert into t_emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.gender},null)
</foreach>
</insert>
<delete id="deleteMoreEmp">
/*
都一种
*/
<!-- delete from t_emp where emp_id in-->
<!-- -->
<!-- <foreach collection="empIds" item="empId" separator="," open="(" close=")">-->
<!-- #{empId}-->
<!-- </foreach>-->
/*
第二种
*/
delete from t_emp where
<foreach collection="empIds" item="empId" separator="or">
emp_id = #{empId}
</foreach>
</delete>
</mapper>
DynamicSQLMapper
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface DynamicSQLMapper {
//根据条件查询员工信息
List<Emp> getEmpByCondition(Emp emp);
//根据使用choose查询员工信息
List<Emp> getEmpByChoose(Emp emp);
//批量添加员工信息
void insertMoreEmp(@Param("emps") List<Emp> emps);
//批量删除
void deleteMoreEmp(@Param("empIds") Integer[] empIds);
}