做Android的同学想接触后端，一点点的学习，既然是笔记肯定会频繁的更改，如果有幸被各位大佬翻阅，给小弟个机会，留下你的谆谆教导，感激不尽。
SQL查询的基本原理
第一、单表查询：
根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。
第二、两表连接查询：
对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
第三、多表连接查询：
先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。 理解SQL查询的过程是进行SQL优化的理论依据。
查询语句中调用Java方法
SRT_YEAR = '${@com.xxx.service.impl.MemberServiceImpl@getCurrentYear()}'
代码
@Select({"<script> ",
"select SRT_TARGET_NUM AS targetNum from sal_recruitment_target ",
"WHERE SRT_WEU_ID = #{srtWeuId} ",
"<if test = 'year != null and year != \"\" and month != null and month != \"\"' > ",
"AND SRT_TYPE = 1 AND SRT_YEAR = #{year} AND SRT_MONTH = #{month} </if> ",
"<if test = 'year != null and year != \"\" and (month == null or month == \"\")' > ",
"AND SRT_TYPE = 2 AND SRT_YEAR = #{year} </if> ",
"<if test = '(year == null or year == \"\") and month != null and month != \"\"' > ",
"AND SRT_TYPE = 1 AND SRT_YEAR = '${@com.dv.member.service.impl.MemberInfoServiceImpl@getCurrentYear()}' AND SRT_MONTH = #{month} </if> ",
"<if test = '(year == null or year == \"\") and (month == null or month == \"\")' > ",
"AND SRT_TYPE = 1 AND SRT_YEAR = '${@com.dv.member.service.impl.MemberInfoServiceImpl@getCurrentYear()}' AND SRT_MONTH = '${@com.dv.member.service.impl.MemberInfoServiceImpl@getCurrentMonth()}' </if> ",
"</script>"
})
RecruitmentTargetEntity selectRecruitedTarget(DateDTO dateDTO);
1. 单表查询数据列表
查询列表方法selectList()
QueryWrapper<MemberEntity> queryWrapper = new QueryWrapper<>();
queryWrapper.select("SME_MEM_ID AS memId", "SME_MOBILE AS mobile",
"SME_NAME AS name", "SME_RECRUMENT_DATE AS recrumentDate")
.eq("SME_USERID", userId);
List<MemberEntity> members = memberMapper.selectList(queryWrapper);
返回结果如下，所有的字段都返回了，这不是我想要的结果，我想要的结果只返回四个字段即可，如何实现呢？
{
"memId": 2,
"userid": null,
"parentUserid": null,
"mobile": "18900000000",
"name": "张小北",
"nickname": null,
"recrumentDate": "2021-05-12T15:35:35",
"memberLevel": null,
"gender": null,
"birthday": null,
"email": null,
"country": null,
"province": null,
"city": null,
"district": null,
"address": null,
"avatar": null,
"optIn": null,
"wechat": null,
"lasteditWeuId": null,
"source": null,
"createTime": null,
"createUser": null,
"modifyTime": null,
"modifyUser": null
}
使用selectMaps()方法完美解决
List<Map<String, Object>> members = memberMapper.selectMaps(queryWrapper);
返回结果:
[
{
"recrumentDate": "2020-05-12T22:30:00.000+00:00",
"memId": 5
},
{
"recrumentDate": "2020-05-02T22:30:00.000+00:00",
"mobile": "18966666664",
"name": "徐主任",
"memId": 11
}
]
问题：没有值的字段没有返回，如何解决呢？
修改application.properties文件
mybatis-plus.configuration.call-setters-on-nulls=true
重新运行程序返回结果
[
{
"recrumentDate": "2020-05-12T22:30:00.000+00:00",
"mobile": null,
"name": null,
"memId": 5
},
{
"recrumentDate": "2020-05-02T22:30:00.000+00:00",
"mobile": "18966666664",
"name": "徐主任",
"memId": 11
}
]
2.单表传入List参数查询
方法一：FIND_IN_SET 方法
/**
* @param labelIds 逗号分隔
*/
@Select("SELECT SLI_LABLE_ID AS labelId, SLI_LABLE_NAME AS labelName " +
" FROM sal_lable_info " +
" WHERE SLI_DISPLAY = 1 AND FIND_IN_SET (SLI_LABLE_ID, #{labelIds})")
List<MemberLabelDTO> selectMemberLabelInfoList(String labelIds);
//调用
String labelIds = StringUtils.join(memberLabelIdList.toArray(),",");
List<MemberLabelDTO> memberLabelList =
labelInfoCustomerMapper.selectMemberLabelInfoList(labelIds);
方法二：QueryWrapper.in()方法
QueryWrapper<LableInfoEntity> queryWrapper = new QueryWrapper<>();
queryWrapper.select("SLI_LABLE_ID AS labelId", "SLI_LABLE_NAME AS labelName");
queryWrapper.in("SLI_LABLE_ID", memberLabelIdList);
List<LableInfoEntity> labelInfoEntities = labelInfoCustomerMapper.selectList(queryWrapper);
方法二有一个弊端，返回的对象是整张表的所有字段。方法一使用起来就比较的灵活。
3 分组查询并返回List<Map<String, List>>格式数据
返回数据如下：
"labelCategoryLists": [
{
"categoryId": 3,
"labelInfoList": [
{
"labelId": 7,
"labelName": "雀氏"
},
{
"labelId": 8,
"labelName": "惠氏"
},
{
"labelId": 9,
"labelName": "美赞臣"
}
]
}
]
方法一：使用xml方式现实
实现步骤 第一步 LabelxxxMapper.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.xxxx.mapper.LabelxxMapper">
<resultMap id="labelInfoList" type="com.xxx.dto.LabelxxList">
<id property="categoryId" column="categoryId" />
<collection property="labelInfoList" ofType="com.xxx.dto.LabelxxDTO">
<result property="labelId" column="labelId" />
<result property="labelName" column="labelName" />
</collection>
</resultMap>
<!--resultMap的值为上面resultMap的id。 -->
<select id="selectLabelInfoList" parameterType="map" resultMap="labelInfoList">
select categoryId, labelId, labelName from lable_info_table
group by categoryId, labelId
</select>
</mapper>
第二步 LabelxxxMapper.java文件如下
@Component
public interface LabelxxxMapper extends BaseMapper<LabelxxxEntity> {
List<LabelxxList> selectLabelxxxList();
}
第三步 调用selectLabelxxxList()方法
ServerImp.java
List<LabelxxxList> mapList = labelxxxMapper.selectLabelxxxList();
方法二：完全java代码实现
这里用另外一个例子
返回Jason数据如下：
[
{
"tip": "C",
"nameList": [
{
"date": "2020-03-03",
"mobile": "18966666616",
"name": "曹操",
"xxx": "19",
"avatar": null,
"firstLetter": "C"
},
{
"date": "2020-11-01",
"mobile": "18966666615",
"name": "草草",
"xxx": "18",
"avatar": null,
"firstLetter": "C"
},
{
"date": "2020-10-06",
"mobile": "18966666699",
"name": "赤壁",
"xxx": "28",
"avatar": null,
"firstLetter": "C"
}
]
}
第一步：实现查询语句
@Select(" select xxx, " +
" xxx, xxx," +
" DATE_FORMAT (xxx, '%Y-%m-%d') AS xxx, " +
" xxx," +
" IFNULL(" +
" ELT( INTERVAL( CONV( HEX( left( CONVERT( NAME USING gbk ) ,
1 ) ) , 16, 10 ) , 0xB0A1, 0xB0C5, 0xB2C1, 0xB4EE, 0xB6EA,
0xB7A2, 0xB8C1, 0xB9FE, 0xBBF7, 0xBFA6, 0xC0AC, 0xC2E8, 0xC4C3,
0xC5B6, 0xC5BE, 0xC6DA, 0xC8BB, 0xC8F6, 0xCBFA, 0xCDDA, 0xCEF4,
0xD1B9, 0xD4D1 ) ,
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M', 'N',
'O', 'P', 'Q', 'R', 'S', 'T', 'W', 'X', 'Y', 'Z' ) " +
" , '#') AS firstLetter " +
" from tableName " +
" where xxx = #{id} " +
" ORDER BY CONVERT(NAME using GBK) ASC")
List<RxxxDTO> selectAllxxx(String userId);
第二步：对返回的List数据进行处理
public Result externalxxx(String userId) {
List<RxxxDTO> recruitedMembersDTOS = memberCustomizeMapper.selectAllxxx(userId);
if (recruitedMembersDTOS == null || recruitedMembersDTOS.isEmpty()) {
return Result.OK();
}
List<MembersGroupingDTO> membersGroupingDTOS = new ArrayList<>();
Map<String, List<RxxxDTO>> stringListMap = new HashMap<>();
for (RecruitedMembersDTO recruitedMembersDTO: recruitedMembersDTOS) {
if (stringListMap.containsKey(recruitedMembersDTO.getFirstLetter())) {
stringListMap.get(recruitedMembersDTO.getFirstLetter()).add(recruitedMembersDTO);
continue;
}
if (ObjectUtil.equals("#", recruitedMembersDTO.getFirstLetter())) {
List<RecruitedMembersDTO> recruitedMembers = new ArrayList<>();
recruitedMembers.add(recruitedMembersDTO);
stringListMap.put(recruitedMembersDTO.getFirstLetter(), recruitedMembers);
continue;
}
List<RecruitedMembersDTO> recruitedMembers = new ArrayList<>();
recruitedMembers.add(recruitedMembersDTO);
MembersGroupingDTO membersGroupingDTO = new MembersGroupingDTO();
membersGroupingDTO.setTip(recruitedMembersDTO.getFirstLetter());
membersGroupingDTO.setNameList(recruitedMembers);
membersGroupingDTOS.add(membersGroupingDTO);
stringListMap.put(recruitedMembersDTO.getFirstLetter(), recruitedMembers);
}
if (stringListMap.containsKey("#")) {
MembersGroupingDTO membersGroupingDTO = new MembersGroupingDTO();
membersGroupingDTO.setTip("#");
membersGroupingDTO.setNameList(stringListMap.get("#"));
membersGroupingDTOS.add(membersGroupingDTO);
}
return Result.OK(membersGroupingDTOS);
}
插入数据前判断数据存在与否并返回自增主键ID
<!--插入标签数据-->
<insert id="saveLabel" parameterType="lableInfoEntity">
<selectKey keyProperty="num,lableId" order="BEFORE"
resultType="com.dv.persistence.lableInfo.entity.LableInfoEntity">
select count(*) as num, SLI_LABLE_ID as lableId
from sal_lable_info
where
SLI_CATEGORY_ID = #{categoryId}
and
SLI_LABLE_NAME = #{lableName}
</selectKey>
<if test="num > 0">
update sal_lable_info set SLI_MODIFY_TIME = #{modifyTime}
where
SLI_CATEGORY_ID = #{categoryId}
and
SLI_LABLE_NAME = #{lableName}
</if>
<if test="num == 0">
insert into sal_lable_info(SLI_CATEGORY_ID, SLI_LABLE_NAME, SLI_WECHAT)
values(#{categoryId}, #{lableName}, #{sliWechat})
</if>
</insert>
先记录另外一个问题
spring boot + mybatis 查询数据返回实体类时，实体类个别字段值为null的问题。 原因是没有开启mybatis的字段命名驼峰转换，导致某些表的字段名找不到实体类的对应的属性。在 application.yml添加
mybatis:
configuration:
map-underscore-to-camel-case: true
即可。
异常
1.报错SQL String cannot be empty
Cause: java.sql.SQLException: SQL String cannot be empty
; SQL String cannot be empty; nested exception is java.sql.SQLException: SQL String cannot be empty
xml中配置了标签，如果没有可以生效的sql代码,会抛出下面的异常,仔细检查xml中的sql代码,避免等标签中无sql代码的情况出现。
2."Parameter ‘XXX‘ not found. Available parameters are [arg1, arg0, param1,..."解决的办法
dao层传参加上@Param，超过两个参数需要加上@Param
List<LabelInfoDTO> selectMemberLabelList(@Param("memId") String memId,
@Param("weuId") Integer weuId);
3.Cannot convert string '06-09' to java.time.LocalDate value
问题代码
DTO类
@Data
public class SurveyCommentDTO {
private String star;
private String comment;
private LocalDate commentTime;
}
Mapper类
@Select("SELECT detail.SSD_CHOICE_ANSWER AS star, " +
" detail.SSD_WRITE_ANSWER AS comment, " +
" DATE_FORMAT(detail.SSD_CREATE_TIME, '%m-%d') AS commentTime" +
" FROM sal_survey_result AS result " +
" LEFT JOIN sal_survey_result_detail AS detail " +
" ON result.SSR_ID = detail.SSD_SSR_ID " +
" WHERE result.SSR_WEU_ID = #{seuId} " +
" AND " +
" SSR_CREATE_TIME LIKE '%${@com.dv.member.service.impl.MemberInfoServiceImpl@getCurrentYearMonth()}%'")
List<SurveyCommentDTO> getSurveyComments(Integer seuId);
解决办法：
4.Fix Failed to deserialize java.time.LocalDateTime
@SpringBootApplication
public class ShoppingxxxApplication {
public static void main(String[] args) {
SpringApplication.run(ShoppingxxxApplication.class, args);
}
@Bean
public ObjectMapper serializingObjectMapper() {
JavaTimeModule module = new JavaTimeModule();
LocalDateTimeDeserializer localDateTimeDeserializer = new LocalDateTimeDeserializer(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
module.addDeserializer(LocalDateTime.class, localDateTimeDeserializer);
ObjectMapper objectMapper = Jackson2ObjectMapperBuilder.json()
.modules(module)
.featuresToDisable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS)
.build();
return objectMapper;
}
}
