1、SpringBoot整合MyBatis
1.1 application.yml
# 数据源配置
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/kh96_ssm_airms?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
username: root
password: root
# mybatis 核心配置
mybatis:
configuration:
map-underscore-to-camel-case: true # 下划线 映射 驼峰
mapper-locations: classpath:mybatis/mapper/*.xml # 自定义mapper映射路径
# config-location: classpath:mybatis-config.xml # mysql配置文件
1.2 实体类
@Data
public class Quality {
//编号
private Integer id;
//地区id
private Integer did;
//检测时间
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone =" GMT+8")
private Date monitorTime;
//pm10
private Integer pm10;
//pm25
private Integer pm25;
//监测站
private String monitorStation;
//修改时间
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone =" GMT+8")
private Date lastModifyTime;
}
1.3 mapper 和 service
1.3.1 QualityMapper
public interface QualityMapper {
//根据空气质量编号，修改空质量
Integer updateQualityById(Quality qualityForm);
}
1.3.2 QualityMapper.xml
<!--
根据空气质量编号，修改空质量
Integer updateQualityById(Quality qualityForm);
-->
<update id="updateQualityById">
update air_quality
<set>
<if test="id != null"> `id` = #{id},</if>
<if test="did != null"> `did` = #{did},</if>
<if test="monitorTime != null"> `monitor_time` = #{monitorTime},</if>
<if test="pm10 != null"> `pm10` = #{pm10},</if>
<if test="pm25 != null"> `pm25` = #{pm25},</if>
<if test="monitorStation != null"> `monitor_station` = #{monitorStation},</if>
<if test="lastModifyTime != null"> `last_modify_time` = #{lastModifyTime},</if>
</set>
where `id` = #{id}
</update>
1.3.3 service
//接口
public interface QualityService {
boolean modifyQualityById(Quality qualityForm);
}
//实现类
@Service
public class QualityServiceImpl implements QualityService {
@Autowired
private QualityMapper qualityMapper;
@Override
public boolean modifyQualityById(Quality qualityForm) {
return qualityMapper.updateQualityById(qualityForm) > 0;
}
}
1.4 测试
1.4.1 控制层
@Slf4j
@RestController
public class AirQualityController {
@Autowired
private QualityService qualityService;
//根据空气质量编号，修改空质量，使用xml映射文件
@PostMapping("/modQuality")
public Map<String,String> testModifyQualityMapperXml(@RequestBody Quality qualityForm){
//返回集合
Map<String,String> returnMap = new HashMap<>();
//调用业务接口，修改空气详情
if(qualityService.modifyQualityById(qualityForm)){
returnMap.put("code","200");
returnMap.put("msg","Success");
return returnMap;
}
returnMap.put("code","9999");
returnMap.put("msg","Fail");
return returnMap;
}
}
1.4.2 Postman 测试
测试：
测试结果：
2、逆向工程 better-mybatis-generator 插件
2.1 下载插件 better-mybatis-generator
2.2 自动生成代码
2.2.1 idea连接数据库
2.2.2 生成代码
2.2.2.1 选中表，右键点击 mybatis-gengrate
2.2.2.2 生成代码 设置
生成的代码：
2.3 测试
2.3.1 service
2.3.1.1 接口
public interface AirQualityService {
//根据条件查询空气质量列表
List<AirQuality> getQualityListByExample(AirQualityExample airQualityExample);
}
2.3.1.2 实现类
@Service
public class AirQualityServiceImpl implements AirQualityService {
@Autowired(required = false)
private AirQualityMapper airQualityMapper;
@Override
public List<AirQuality> getQualityListByExample(AirQualityExample airQualityExample) {
return airQualityMapper.selectByExample(airQualityExample);
}
}
2.3.1 测试代码
@GetMapping("/airQualities")
public List<AirQuality> testAirQualityListUseBackward(){
log.info("------ 根据 查询条件，查询空气质量的列表 --------");
//创建查询条件对象
AirQualityExample airQualityExample = new AirQualityExample();
//如果需要去重，增加条件
airQualityExample.setDistinct(true);
//如果需要排序，增加排序条件
airQualityExample.setOrderByClause(" id desc ");
//添加自定义查询 条件，监测站名称中包含检测的
AirQualityExample.Criteria criteria = airQualityExample.createCriteria();
//criteria.andMonitorStationLike("%"+monitorStation+"%");
criteria.andMonitorStationLike("%监测%");
//区域编号 大于1
//criteria.andDidGreaterThan(1);
//区域编号在指定列表中
//criteria.andDidIn(Arrays.asList(1,2,3,4,5));
//增加or的查询条件
AirQualityExample.Criteria criteriaOr = airQualityExample.createCriteria();
criteriaOr.andPm10GreaterThan(100);
//拼接or的查询条件
airQualityExample.or(criteriaOr);
//如果需要分页，增加分页参数 limit ${offset}, ${limit}
//起始行 （偏移量参数）
airQualityExample.setOffset(2l);
//返回数据量
airQualityExample.setLimit(5);
//调用业务接口，查询空气质量列表
List<AirQuality> qualityListByExample = airQualityService.getQualityListByExample(airQualityExample);
return qualityListByExample;
}
2.3.2 测试结果：
2.4 分析查询条件对象
2.4.1 基本查询条件
2.4.2 字段上的查询条件
2.5 分析 创建 xxxExample.Criteria 自定义查询条件
protected List<Criteria> oredCriteria; //oredCriteria 是一个 Criteria 的 集合
//创建条件类 方法
public Criteria createCriteria() {
Criteria criteria = createCriteriaInternal();
if (oredCriteria.size() == 0) {
oredCriteria.add(criteria); // 创建第一个Criteria的时候会自动加入集合中
}
return criteria;
}
//or 拼接 条件 方法
public void or(Criteria criteria) {
oredCriteria.add(criteria); //其他Criteria 条件，调用方法拼接时 也会 动加入集合中
}
3、JPA
3.1 JPA 配置
# jpa 核心配置
spring:
jpa:
show-sql: true # 显示sql查询
hibernate:
ddl-auto: update #如果不存在，就新建，如果存在只更新
3.2 测试
3.2.1 实体 bean
3.2.1.1 实体类
注意：
1、如果数据库没有该表会自动生成该表；
2、如果该表已经存在，也需要填写这些注解信息，要不然会出现实体跟数据库不对应的错误；
@Data
@Entity
@Table(name = "air_user",catalog = "kh96_ssm_airms")
//指定jpa建表的表名，如果指定，默认使用类名作为表名，catalog是指定数据库实例名
public class AirUser {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
//用户名
@Column(name = "name",length = 32)
private String name;
//密码
@Column(name = "pwd",length = 32)
private String pwd;
//手机号
@Column(name = "tel",length = 11)
private String tel;
}
3.2.1.2 生成的表
启动项目；
3.2.2 AirUserRepository
public interface AirUserRepository extends JpaRepository<AirUser,Integer> {
}
3.2.3 service
3.2.3.1 接口
public interface AirUserService {
//@description : 根据用户编号，查询用户详情
AirUser getAirUserById(Integer id);
}
3.2.3.2 实现类
@Service
public class AirUserServiceImpl implements AirUserService {
@Autowired
private AirUserRepository airUserRepository;
@Override
public AirUser getAirUserById(Integer id) {
return airUserRepository.findOne(id);
}
}
3.2.4 测试代码
@RestController
public class AirQualityController {
@Autowired
private AirUserService airUserService;
@GetMapping("/airUser")
public AirUser AirUserTestAirUserUserJPA(@RequestParam("Id") Integer Id){
return airUserService.getAirUserById(Id);
}
}
