MyBatis(2)
一、增删改查CRUD的映射文件配置
1. 增加Employee
EmployeeMapper接口
public Long addEmp(Employee employee);
xml映射文件
<insert id="addEmp" parameterType="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee"
useGeneratedKeys="true" keyProperty="id">
insert into tbl_employee(last_name,email,gender)
values(#{lastName},#{email},#{gender})
</insert>
主函数
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// 添加
Employee employee = new Employee(null, "jerry", "jerry@Lemon.com", "1");
Long addEmp = mapper.addEmp(employee);
System.out.println(addEmp);
// 获取自增主键的值
System.out.println(employee.getId());
(1)由于是增加操作,所以返回值可以是update的条目数,而输入的参数是Employee对象,所以在xml配置文件中使用parameterType
表示参数类型
(2)对于自增主键id,useGeneratedKeys="true"
表示使用自增主键获取主键值策略;keyProperty="id"
指定对应的主键属性,使得MyBatis获取到主键后,将这个值封装给对象的哪个属性
2. 更新Employee
EmployeeMapper接口
public boolean updateEmp(Employee employee);
xml映射文件
<update id="updateEmp">
update tbl_employee
set last_name=#{lastName},email=#{email},gender=#{gender}
where id=#{id}
</update>
主函数
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// 修改
Employee employee = new Employee(2, "jerry", "jerry@Lemon.com", "0");
boolean b = mapper.updateEmp(employee);
System.out.println(b);
(1)MyBatis会自动根据传入的对象,获取到id值,填充到where条件中进行更新
(2)update可以返回一个boolean类型,表示是否更新成功
3. 根据Id删除Employee
EmployeeMapper接口
public Long deleteEmp(Integer id);
xml映射文件
<delete id="deleteEmp">
delete from tbl_employee
where id=#{id}
</delete>
主函数
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// 删除
Long deleteEmp = mapper.deleteEmp(2);
System.out.println(deleteEmp);
(1)delete可以返回一个Long类型的值,表示删除的条目数
注意
由于在创建SqlSession对象时,默认不会自动提交数据
因此在CRUD操作完成后一定要手动提交数据
openSession.commit();
4. 多个输入参数的查询
方法一:命名参数 @Param(" ")
EmployeeMapper接口:根据id和lastName获取Emp
public Employee getEmpByIdAndLastName(@Param("id") Integer id, @Param("lastName") String lastName);
xml映射文件
<select id="getEmpByIdAndLastName" resultType="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
select * from tbl_employee where id=#{id} and last_name=#{lastName}
</select>
主函数
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// 多个参数:命名参数
Employee employee = mapper.getEmpByIdAndLastName(1, "tom");
System.out.println(employee);
方法二:POJO的Map(将多个参数封装成map)
EmployeeMapper接口:根据id和lastName获取Emp
public Employee getEmpByMap(Map<String, Object> map);
xml映射文件
<select id="getEmpByMap" resultType="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
<!-- 参数值的获取 #{}和${}-->
select * from ${tableName} where id=${id} and last_name=#{lastName}
</select>
主函数
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// Map
Map<String, Object> map = new HashMap<>();
map.put("id", 1);
map.put("lastName", "Tom");
// 使用 ${} 对 表名 进行拼接
map.put("tableName", "tbl_employee");
Employee employee = mapper.getEmpByMap(map);
System.out.println(employee);
注意
参数值的获取方式:#{ }
和${ }
对于表名,只能使用${ }
5. 模糊查询
A. 返回List类型
EmployeeMapper接口:对名字进行模糊查询
public List<Employee> getEmpsByLastNameLike(String lastName);
xml映射文件
<select id="getEmpsByLastNameLike" resultType="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
select * from tbl_employee where last_name like #{lastName}
</select>
主函数
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// 模糊查询(名字含有e字母的),返回集合List类型
List<Employee> like = mapper.getEmpsByLastNameLike("%e%");
for (Employee employee : like) {
System.out.println(employee);
}
(1)如果查询返回的是一个List集合,resultType
要写集合中元素的类型 —— Employee
B. 返回Map类型,key:列名,value:列对应的值
EmployeeMapper接口:根据id查询
public Map<String, Object> getEmpByIdReturnMap(Integer id);
xml映射文件
<select id="getEmpByIdReturnMap" resultType="map">
select * from tbl_employee where id=#{id}
</select>
主函数
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// 返回Map类型
Map<String, Object> map = mapper.getEmpByIdReturnMap(1);
System.out.println(map);
(1)如果返回的是一个Map,resultType
要写map
B. 返回多条记录封装的Map类型,key:主键,value:每一条记录(对象)
EmployeeMapper接口:模糊查询多条记录
@MapKey("id")
public Map<Integer, Employee> getEmpByLastNameLikeReturnMap(String lastName);
xml映射文件
<select id="getEmpByLastNameLikeReturnMap" resultType="mybatis_3_mapper.com.Lemon.mybatis.bean.Employee">
select * from tbl_employee where last_name like #{lastName}
</select>
主函数
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// 多条记录封装成一个map
Map<Integer, Employee> map = mapper.getEmpByLastNameLikeReturnMap("%r%");
System.out.println(map);
(1)如果返回的是一个存储多条记录的Map,resultType要写map中存储的value对象类型
(2)@MapKey("id")
:告诉MyBatis封装这个map时,使用哪个属性作为map的key