Mysql---CURD(实习笔记)

1. Mybaitis-Plus+模糊查询

String where=" where schedule.isDel="+UserConstant.DELETED_STATE_NO+" and schedule.startdate='"+DateUtils.dateToString(new Date(),DateUtils.DATE_FORMAT_yyyy_MM_dd)+"'";
		where += " and schedule.taskId is not null";
		where += " and schedule.isDeal!=1"; // 不是已处理的
		where +="  and (schedule.mainParticipantId="+empId+" or schedule.otherParticipantId like '%"+empId+"%')";
		String hql = "select count(schedule.id) from Schedule schedule  "+where;
		return super.getAllEntityNumberByHql(hql);

  @Override
    public List<Schedule> todayList(Long empId) {
        LambdaQueryWrapper<Schedule> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(Schedule::getIsDel,UserConstant.DELETED_STATE_NO)
                .eq(Schedule::getStartdate, LocalDate.now())
                .ne(Schedule::getTaskId,null)
                .eq(Schedule::getMainParticipantId,empId)
                .eq(Schedule::getOtherParticipantId,empId)
                .and(i -> i.eq(Schedule::getMainParticipantId,empId).or().like(Schedule::getOtherParticipantId,empId))
                .orderByAsc(Schedule::getIsDeal)
                .orderByAsc(Schedule::getIsAllDay)
                .orderByAsc(Schedule::getStarttime);
        List<Schedule> list = scheduleMapper.selectList(wrapper);
        return list;
    }

2.多表查询+返回单表属性

 select DISTINCT emps.empid
        from case_lawer p,emp_info emps
        where p.isDel=0
          and emps.isDel=0
          and p.status=1
          and p.empid=emps.empid
          and p.caseid=#{caseid}
          and p.managerType=0
          limit 1

// sql 语句
  <select id="getDocListBySyncData" resultType="java.util.List">
        select * from document doc
        where doc.docStatus in (2,4,6,7,8,10)
          and doc.isDel=0
          and doc.documentId in
              (select docOpponent.docId
               from document_opponent docOpponent
               where docOpponent.isDel=0)
          and doc.docType in (54,55)
          and doc.documentId not in
              (select conflict.docId
               from search_conflict_lettertarget conflict
               where conflict.isDel=0)
    </select>

3 . 多表查询+返回部分多表属性（比如查询3个表，返回2个表对象）

解决办法：定义一个DTO把多个表的属性封装起来，用Sql语句操作DTO这个对象，最后根据从返回的DTO中一顿set 和get 获取所需要的对象

//案例
public List listAllAliyunPushLinkByNotification(Integer page, Integer pagesize) {
		String hql = "select link,userinfo,emp from AppAliyunPushLink link,PubUserInfo userinfo,EmpInfo emp where link.isDel="+UserConstant.DELETED_STATE_NO+" and userinfo.isDel="+UserConstant.DELETED_STATE_NO+" and emp.isDel="+UserConstant.DELETED_STATE_NO;
		hql += " and link.accountId=userinfo.accountId";
		hql += " and userinfo.empid=emp.id";
		hql += " and userinfo.isvalid=1"; // 有效账号
		hql += " and userinfo.isPreferred=1"; // 首选登录账号
		hql += " and emp.isPushTodayNotification=1"; // 开启了今日安排提醒
		return super.getListDataByPage((page-1)*pagesize, pagesize, hql, null);
	}

//封装DTO
public class TodayScheduleNotificationJobDto {

//    private AppAliyunpushLink appAliyunpushLink;
//    private PubUserInfo pubUserInfo;
//    private EmpInfo empInfo;

    @TableId("id")
    private Long id;

    @TableField("accountId")
    private Long accountId;

    @TableField("createUserId")
    private Long createUserId;

    /**
     * ANDROID/IOS
     */
    @TableField("device")
    private String device;

    /**
     * 从推送服务商获取的设备ID
     */
    @TableField("deviceId")
    private String deviceId;、
    ................................................
    ...............................................
    太多了

 @Override
    public int todayCount(Long empId) {
        LambdaQueryWrapper<Schedule> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(Schedule::getIsDel,UserConstant.DELETED_STATE_NO)
                .eq(Schedule::getStartdate, LocalDate.now())
                .ne(Schedule::getTaskId,null)
                .ne(Schedule::getIsDeal,1)
                .and(i -> i.eq(Schedule::getMainParticipantId,empId).or().like(Schedule::getOtherParticipantId,empId));
        Long count = scheduleMapper.selectCount(wrapper);
        return count.intValue();
    }

//获取部分对象
 for (int i = 0; i < aliyunLinkList.size(); i++) {
     TodayScheduleNotificationJobDto jobDto = aliyunLinkList.get(i);
       AppAliyunpushLink link = new AppAliyunpushLink();
       link.setId(jobDto.getId());
       link.setAccountId(jobDto.getAccountId());
       link.setCreateUserId(jobDto.getCreateUserId());
       link.setDevice(jobDto.getDevice());
       link.setDeviceId(jobDto.getDeviceId());
       link.setAlias(jobDto.getAlias());
       link.setTag(jobDto.getTag());
       link.setMobileBrand(jobDto.getMobileBrand());
       link.setSupplier(jobDto.getSupplier());
       link.setCreateTime(jobDto.getCreateTime());
       link.setCompany(jobDto.getCompany());
       link.setIsDel(jobDto.getIsDel());
		略。。。。。。。
}

3-1. Sql 查询2个表，只返回一个表的所有属性

1. 可读性高 ：一个个属性列举出来
2. 表的别名.*
   案例如下:

    <select id="getAllCorpYearPoolList" resultType="com.kinglex.executor.entity.FinanceCorpYearPool">
        select corpYearPool.* from finance_corp_pool pool,finance_corp_year_pool corpYearPool
        where corpYearPool.isDel= 0
          and corpYearPool.company=#{arg2}
          and pool.isDel=0
          and pool.company=#{arg2}
          and pool.id = corpYearPool.poolId
          and corpYearPool.state = 1
          and pool.corpId=#{arg0}
          and corpYearPool.periodId=#{arg1}
          order by corpYearPool.createTime
    </select>

4. 多表查询—jion on的使用

    <select id="getListAllWechatLinkByNotification" resultType="java.lang.Long">
        select emp.empid
        from emp_info emp
                 join pub_user_info userinfo on userinfo.empid = emp.empid
                 join app_wechat_link link on link.accountId = userinfo.accountId
        where link.isDel = 0
          and userinfo.isDel = 0
          and emp.isDel = 0
-- 有效账号
          and userinfo.isvalid = 1
-- 首选登录账号
          and userinfo.isPreferred = 1
-- 开启了今日安排提醒
          and emp.isPushTodayNotification = 1
    </select>

5. 多表查询 “in”的使用 返回一个表的部分属性

错误案例：相当于题目吧
select transactor.workHour from WorkLog log,WorkLogReview review,WorkLogTransactor transactor
        where log.isDel= 0
              and review.isDel= 0
              and transactor.isDel= 0
              and log.id = review.logId
              and log.id= transactor.logId
              and review.step= 1
              and log.submitTo= empId
              and log.state=  2
              and review.status= 1

select transactor.workHour from work_log_transactor transactor
        where  transactor.isDel= 0
                and  transactor.logId in
                (select log.id from work_log log
                    where log.isDel= 0
                    and log.state=  2
                    and log.submitTo= empId
                    and log.id in
                    (select review.logId from work_log_review review
                         where review.isDel= 0
                         and review.step= 1
                         and review.status= 1
                    )
                )

6 . Xml 一些转义字符

链接: Xml 一些转义字符.
案例

    <select id="acquireMeWorkLogList" resultType="java.util.List">
        select * from work_log log,emp_info emp
        where log.isDel=0
        and emp.isDel=0
        and log.createEmpId = emp.empid
        and log.submitTo= #{empId}
        and log.state = 2
        and log.createTime >= #{beginDate}
        and log.createTime &lt;= #{endDate}
        order by log.createEmpId,log.createTime desc;
    </select>

7. sql 如何传入多个参数#{xxx}

7.1 通过注解

 * @author Amir
 * @since 2022-01-19
 */
@Mapper
public interface ProcessTaskRecoverWayMapper extends BaseMapper<ProcessTaskRecoverWay> {

    List<ProcessTaskRecoverWay> getRecoverWayList(
    				@Param("nowTaskId") Long nowTaskId,
                    @Param("condition") int condition,
                    @Param("givenTaskResultId") Long givenTaskResultId);
}

   <select id="getRecoverWayList" resultType="com.kinglex.executor.entity.ProcessTaskRecoverWay">
        select * from process_task_recover_way 
        where isDel=UserConstant.DELETED_STATE_NO
             and givenTaskId=#{nowTaskId}
             and recoverCondition=#{condition}
    </select>

7.2 通过arg依次传参

<select id="getAllConsultantByADate" resultType="com.kinglex.executor.entity.CaseInfo">
        select *
        from case_info caseinfo
        where caseinfo.isDel = 0
        and caseinfo.caseScope = 1
        and caseinfo.caseid in (
            select visible.caseId
            from case_visible_team visible
            where visible.isDel = 0
            and FIND_IN_SET(visible.teamId, #{arg0}))
        and caseinfo.enddate >= #{arg1}
        and caseinfo.enddate &lt; #{arg2}
        and caseinfo.caseStatus not in (8, 11, 15, 16, 19)
        and caseinfo.bizStatus not in (800204, 800205)
        and caseinfo.caseTypeValue = 5
        order by caseinfo.enddate asc,caseinfo.acceptDate desc,caseinfo.createTime desc
    </select>

8. 如何获取非数据库字段？

同过getById，getByName通过反射获得实体类，实体类.getEmpname()本质上又去执行了一条sql语句

  for (int l = 0; l < userIdList.size(); l++) {
      Long userid = userIdList.get(l);
      if (userid != userInfo.getUserid()) {
//  	 s += pubUserInfoService.getById(userid).getEmpname();
   		 s += empInfoService.getEmpname(pubUserInfoService.getById(userid).getEmpid());
         if (l != userIdList.size() - 1) {
           s += ",";
          }
 	}
 }

9. sql中if语句使用及如何写一个存储过程

（实际工程使用sql-if）
@Override
	public void deleteTaskEmp(Long taskId, Integer empType) {
		String hql = "update ProcessTaskEmp set isDel="+UserConstant.DELETED_STATE_YES+" where taskId="+taskId;
		if(empType!=null){
			hql += " and empType="+empType;
		}
		super.execute(hql);
	}

也可用wrapper来写，如下所示：

  @Override
    public void deleteTaskEmp(Long taskId, Integer processTaskempMain) {

        LambdaUpdateWrapper<ProcessTaskEmp> wrapper = new LambdaUpdateWrapper<>();
        if(processTaskempMain !=null){
            wrapper.eq(ProcessTaskEmp::getTaskId, taskId)
                    .eq(ProcessTaskEmp::getEmpType,processTaskempMain)
                    .set(ProcessTaskEmp::getIsDel, 1);
        }else{
            wrapper.eq(ProcessTaskEmp::getTaskId, taskId)
                    .set(ProcessTaskEmp::getIsDel, 1);
        }
        processTaskEmpMapper.update(null, wrapper);
    }

链接: 如何写一个存储过程.

10. mysql思维导图

链接: 清晰版思维导图MySQL.