Android students want to touch the backend, a little bit of learning, since the notes will be frequently changed, if lucky enough to be read by you, give my younger brother a chance to leave your indoctrination, thank you very much.
Fundamentals of SQL queries
First, single table query:
Filter the records in the table according to the WHERE condition to form an intermediate table (this intermediate table is not visible to the user); Then SELECT the corresponding column according to the SELECT column to return the final result.
SQL > select * from table_name;
The product of two tables (Cartesian product) is filtered by ON condition and join type to form intermediate table. The records of the intermediate table are then filtered based on WHERE criteria and the query results are returned based on the columns specified by SELECT.
SQL > alter table join query
Query the first and second tables according to the two-table join query, and then use the query result and the third table join query, and so on, until all tables are joined, finally form an intermediate result table, and then filter the records of the middle table according to the WHERE condition, and return the query result according to the SELECT specified column. Understanding the process of SQL queries is the theoretical basis for SQL optimization.
A Java method is invoked in a query statement
SRT_YEAR = ‘${@com.xxx.service.impl.MemberServiceImpl@getCurrentYear()}’
code
@Select({"" })
RecruitmentTargetEntity selectRecruitedTarget(DateDTO dateDTO);
Copy the code1. Query the data list in a single table
Query list method 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);
Copy the codeThe result is as follows: all fields are returned. This is not what I want. I want to return only four fields.
{
"memId": 2."userid": null."parentUserid": null."mobile": "18900000000"."name": "Zhang Xiaobei"."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
}
Copy the codeThis works perfectly with the selectMaps() method
List<Map<String, Object>> members = memberMapper.selectMaps(queryWrapper);
Copy the codeReturn result:
[{"recrumentDate": "The 2020-05-12 T22:30:00. 000 + 00:00"."memId": 5
},
{
"recrumentDate": "The 2020-05-02 T22:30:00. 000 + 00:00"."mobile": "18966666664"."name": "Director Xu"."memId": 11}]Copy the codeProblem: a field with no value is not returned.
Modify the application.properties file
mybatis-plus.configuration.call-setters-on-nulls=true
Copy the codeRerun the program returns the result
[{"recrumentDate": "2020-05-12T22:30:00.000+00:00", "mobile": null, "name": null, "memId": 5}, {"recrumentDate": "The 2020-05-02 T22:30:00. 000 + 00:00", "mobile" : "18966666664", "name" : "xu, director of", "memId" : 11}]Copy the code2. Enter the List parameter for a single table
Method 1: FIND_IN_SET
/ * * *@paramLabelIds comma separated */
@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);
/ / call
String labelIds = StringUtils.join(memberLabelIdList.toArray(),",");
List<MemberLabelDTO> memberLabelList =
labelInfoCustomerMapper.selectMemberLabelInfoList(labelIds);
Copy the codeMethod 2: QueryWrapper.in() method
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);
Copy the codeMethod 2 has the disadvantage of returning all the fields of the entire table. The method is relatively flexible when used.
3 Group query and return List<Map<String, List>> data
The following data is returned:
"LabelCategoryLists ": [{"categoryId": 3, "labelInfoList": [{"labelId": 7, "labelName":" "}, {"labelId": 8, "labelName" : "wyeth"}, {" labelId ": 9," labelName ":" mead Johnson "}]}]Copy the codeMethod one: Use XML for reality
The labelxxxmapper.xml content is as follows
<! DOCTYPEmapper 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>
<! -- The resultMap value is the resultMap ID. -->
<select id="selectLabelInfoList" parameterType="map" resultMap="labelInfoList">
select categoryId, labelId, labelName from lable_info_table
group by categoryId, labelId
</select>
</mapper>
Copy the codeThe second labelxxxmapper. Java file is as follows
@Component
public interface LabelxxxMapper extends BaseMapper<LabelxxxEntity> {
List<LabelxxList> selectLabelxxxList(a);
}
Copy the codeThe third step calls the selectLabelxxxList() method
ServerImp.java
List<LabelxxxList> mapList = labelxxxMapper.selectLabelxxxList();
Copy the codeMethod two: complete Java code implementation
Here’s another example
Jason returns the following data:
[{"tip": "C"."nameList": [{"date": "2020-03-03"."mobile": "18966666616"."name": "Cao cao"."xxx": "19"."avatar": null."firstLetter": "C"
},
{
"date": "2020-11-01"."mobile": "18966666615"."name": "Hastily"."xxx": "18"."avatar": null."firstLetter": "C"
},
{
"date": "2020-10-06"."mobile": "18966666699"."name": "Red cliff"."xxx": "28"."avatar": null."firstLetter": "C"}}]Copy the codeStep 1: Implement the query statement
@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);
Copy the codeStep 2: Process the returned List data
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);
}
Copy the codeCheck whether data exists before inserting data and return the auto-increment primary key ID
<! Insert label data -->
<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>
Copy the codeLet’s start with another question
Spring Boot + mybatis query data return entity class individual field value null problem. The reason is that mybatis field name hump conversion is not enabled, resulting in some table field names can not find the corresponding attribute of the entity class. In the application. Yml added
mybatis:
configuration:
map-underscore-to-camel-case: true
Copy the codeCan.
abnormal
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
Copy the codeIf there is no SQL code that can take effect, the following exception is thrown. Check the SQL code in the XML to avoid the problem that there is no SQL code in the tag.
Parameter ‘XXX’ not found. Available parameters are [arg1, arg0, param1,
Dao layer to participate in @param, more than two parameters need to add @param
List<LabelInfoDTO> selectMemberLabelList(@Param("memId") String memId,
@Param("weuId") Integer weuId);
Copy the code3.Cannot convert string ’06-09′ to java.time.LocalDate value
The problem code
DTO classes
@Data
public class SurveyCommentDTO {
private String star;
private String comment;
private LocalDate commentTime;
}
Copy the codeThe Mapper class
@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);
Copy the codeSolutions:
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(a) {
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();
returnobjectMapper; }}Copy the code