mybatis实体类注释
pojo中的实体类属性和表字段不一致时,可以通过添加@Column("表字段")来注释解决。前提是添加如下依赖。@Table("表名") @Id("主键") @Column("表字段")....的使用方式:
@Table(name = "user") public class User { @Id //盖注释加到那个属性上面,意味着这个属性就是表主键 private Integer id; private String name; private String password; private Integer did; //注释该属性不是其user表字段,不属于user表 @Column(insertable = false) private Dept dept; }/** * 部门实体 */ @Data public class Dept { private Integer did; @Column(name = "dept_name")//该注释作用是说depName属性对应的表字段为dept_name private String depName; }
<dependency>
<groupId>javax.persistence</groupId>
<artifactId>persistence-api</artifactId>
<version>1.0.2</version>
<scope>provided</scope>
</dependency>
${}和#{}的区别
#{}在mapper.xml里面为占位符,而${}为字符串拼接。能使用#{}就不要使用${}。
注意:使用${}必须加单引号
模糊查询实现方式
方式一
select * from user where name like '% ${username} %'
方式二
select * from user where name like concat('%', #{username} ,'%')
方式三
select * from user where name like "%" #{username} "%"
批量删除
delete from user where id in ${ids}
ids为前端传递过来的字符串,如:1,2,3
主键回填
<insert id="insertUser" useGeneratedkeys="true" keyProperty="id">
insert into t_user values(null,#{name},#{pwd})
<insert>
useGeneratedkeys 主键回填设置为true
keyProperty 回填之后的主键赋值给id
resultType和resultMap的区别和使用方式
如果返回值是实体类,则使用resultType即可,如果实体类里面包含了不属于该实体类所对应的表属性,使用resultMap自定义映射。如下所示:要返回员工信息同时带部门信息,就要使用resultMap封装pojo实体类属性和表字段的映射关系。
-
部门表
-
员工表
多对一实例(三种实现方式)
//pojo层
/**
*用户实体
*/
@Data
public class User {
@Id
private Integer id;
private String name;
private String password;
private Integer did;
//注释该属性不是其user表字段,不属于user表
@Column(insertable = false)
private Dept dept;
}
/**
* 部门实体
*/
@Data
public class Dept {
private Integer did;
private String depName;
}
//controller层
public class UserController{
@Authoreid
private Userservice userservice;
@ApiOperation(value = "查询员工部门信息")
@GetMapping("/dept/{id}")
public User getUserWithDept(@PathVariable Integer id){
return userServie.getUserWithDept(id);
}
}
//service接口层
public interface UserServie {
User getUserWithDept(Integer id);
}
//service实现层
public class UserserviceImpl implements Userservice{
@Override
public User getUserWithDept(Integer id) {
User userWithDept = userMapper.getUserWithDept(id);
return userWithDept;
}
}
//mapper接口层
/**
* 查询员工部门信息
*/
public class UserMapper{
User getUserWithDept(@Param("id") Integer id);
}
方式一:级联映射方式处理多对一映射方式
<!--返回包含部门信息的用户信息-->
<?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.example.mybatis.mapper.UserMapper">
<resultMap id="baseMap" type="com.example.mybatis.pojo.User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="password" column="password"/>
<result property="did" column="did"/>
<result property="dept.did" column="did"/>
<result property="dept.depName" column="dep_name"/>
</resultMap>
<select id="getUserWithDept" resultMap="baseMap">
select * from user left join dept on user.did=dept.did where user.id=#{id}
</select>
</mapper>
运行结果:
方式二:association标签 处理多对一映射方式
保持方式一不变,只需要替换resultMap中的dept属性即可。
<resultMap id="baseMap" type="com.example.mybatis.pojo.User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="password" column="password"/>
<result property="did" column="did"/>
<association property="dept" javaType="com.example.mybatis.pojo.Dept">
<id property="did" column="did"/>
<result property="depName" column="dep_name"/>
</association>
</resultMap>
方式三:分步查询
1、查询部门信息
部门mapper层
@Repository
public interface DeptMapper {
/**
* 查询部门信息
*/
Dept getDeptById(@Param("did") Integer did);
}
部门mapper.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.example.mybatis.mapper.DeptMapper">
<!--查询部门信息映射-->
<select id="getDeptById" resultType="com.example.mybatis.pojo.Dept">
select * from dept where did =#{did}
</select>
</mapper>
2、查询用户信息,并将1、中查询到的部门信息封装到dept属性中
<?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.example.mybatis.mapper.UserMapper">
<resultMap id="baseMap" type="com.example.mybatis.pojo.User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="password" column="password"/>
<result property="did" column="did"/>
<association
property="dept"
column="did"
select="com.example.mybatis.mapper.DeptMapper.getDeptById">
</association>
</resultMap>
<!--分步查询部门信息-->
<select id="getUserWithDept" resultMap="baseMap">
select * from user where id=#{id}
</select>
</mapper>
注:
<association
property="dept" //指用户实体类中的部门属性dept
column="did" //分步查询的条件,就是1.中查询后属性did被赋的值,作为条件查询2
select="com.example.mybatis.mapper.DeptMapper.getDeptById">//分布查询的方式,指向部门mapper接口层中的具体查询方式的路径
</association>
由上图运行过程可知:确实执行了两次sql。第一次查询user;第二次通过第一次获取的did查询dept。
一对多实例
方式一:collection标签处理一对多映射关系(部门——>多个员工)
pojo层
/**
* 部门实体
*/
@Data
public class Dept {
private Integer did;
private String depName;
@Column(insertable = false)
private List<User> users;
}
/**
* 员工实体
*/
@Data
@Table(name = "user")
public class User {
@Id //盖注释加到那个属性上面,意味着这个属性就是表主键
private Integer id;
private String name;
private String password;
private Integer did;
//注释该属性不是其user表字段,不属于user表
@Column(insertable = false)
private Dept dept;
}
Mapper接口层
@Repository
public interface DeptMapper {
/**
* 查询部门下的员工信息
*/
Dept getDeptAndUsers(@Param("did") Integer did);
}
Mapper.xml持久层
<mapper namespace="com.example.mybatis.mapper.DeptMapper">
<!--查询部门信息映射-->
<select id="getDeptById" resultType="com.example.mybatis.pojo.Dept">
select * from dept where did =#{did}
</select>
<resultMap id="baseMap" type="com.example.mybatis.pojo.Dept">
<id property="did" column="did"/>
<result property="depName" column="dep_name"/>
<collection property="users" ofType="com.example.mybatis.pojo.User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="password" column="password"/>
<result property="did" column="did"/>
</collection>
</resultMap>
<select id="getDeptAndUsers" resultMap="baseMap">
select * from dept left join user on dept.did=user.did where dept.did=#{did}
</select>
</mapper>
controller层
@RestController
@RequestMapping("dept")
@Api(value = "提供部门相关管理", tags = "部门管理")
public class DeptController {
@Autowired
private DeptService deptService;
@ApiOperation(value = "查找部门下的用户")
@GetMapping("/{did}")
public Dept getDeptAndUsers(@PathVariable Integer did){
return deptService.getDeptAndUsers(did);
}
}
Service业务接口层
public interface UserServie {
User getUserWithDept(Integer id);
}
Servie业务实现层
@Service
public class DeptServiceImpl implements DeptService {
@Autowired
private DeptMapper deptMapper;
@Override
public Dept getDeptAndUsers(Integer did) {
return deptMapper.getDeptAndUsers(did);
}
}
注:上图执行过程可知:确实采用左连接一步查询 ,并将数据进行了分装。
方式二:分步查询
注:处了mapper.xml层和方式一不太一样,其他层都差不多,只需要在员工中加一个根据did查找员工的接口和xml即可。
UserMapper接口层
/**
* 分步查询部门下的员工信息,此处为员工查询
*/
List<User> getDeptWithEmployee(@Param("did") Integer did);
userMapper.xml持久层
<!-- List<User> getDeptWithEmployee(@Param("did") Integer did);-->
<select id="getDeptWithEmployee" resultType="com.example.mybatis.pojo.User">
select * from user where did=#{did}
</select>
DeptMapper.xml持久层
<resultMap id="baseMap2" type="com.example.mybatis.pojo.Dept">
<id property="did" column="did"/>
<result property="depName" column="dep_name"/>
<collection
property="users"
column="did"
select="com.example.mybatis.mapper.UserMapper.getDeptWithEmployee">
</collection>
</resultMap>
<select id="getDeptWithEmps" resultMap="baseMap2">
select * from dept where did=#{did}
</select>
注:由上图可知:确实分两部查找。