九悦机试 · 空白默写模板

用法每天限时默写一遍不看项目代码。能独立填完 80% 以上考试就稳。姓名__日期__用时__完成度__%一、项目骨架15 分钟1. pom.xml 依赖写出 artifactId 即可依赖artifactIdWebspring-boot-starter-__参数校验spring-boot-starter-__MyBatismybatis-spring-boot-starterMySQLmysql-connector-javaLomboklombokSpring Boot 版本__Java 版本__2. application.ymlserver: port: __________ ​ spring: datasource: url: jdbc:mysql://localhost:3306/__________?... username: __________ password: __________ jackson: date-format: __________ ​ mybatis: mapper-locations: __________ type-aliases-package: __________ configuration: map-underscore-to-camel-case: __________3. 启动类SpringBootApplication __________(com.example.demo.mapper) // 注解名 public class Demo4Application { public static void main(String[] args) { SpringApplication.run(__________, args); } }4. 建表 SQLCREATE TABLE doctors ( id INT PRIMARY KEY AUTO_INCREMENT, dname VARCHAR(50) NOT NULL, dept VARCHAR(50) NOT NULL, title VARCHAR(20) NOT NULL ); ​ CREATE TABLE schedules ( id INT PRIMARY KEY AUTO_INCREMENT, did INT NOT NULL, sdate DATE NOT NULL, shift VARCHAR(10) NOT NULL );外键可选schedules.did → doctors.idON DELETE__二、统一响应 异常10 分钟Result.javaData public class ResultT { private Integer __________; // 业务状态码 private String __________; // 提示信息 private T __________; // 业务数据 ​ public static T ResultT success(T data) { ... code__________ ... } public static T ResultT fail(Integer code, String message) { ... } }PageResult.javaData public class PageResultT { private Long __________; // 总记录数 private ListT __________; // 当前页数据 }GlobalExceptionHandler.javaRestControllerAdvice public class GlobalExceptionHandler { ​ ExceptionHandler(__________.class) // 异常类 public ResultVoid handleBusinessException(BusinessException e) { return Result.fail(e.getCode(), e.getMessage()); } }三、编程题 1新增医生及排班要求至少 3 条排班 · 事务 · 参数传入ControllerRestController RequestMapping(__________) public class DoctorController { ​ Autowired private DoctorService doctorService; ​ __________ // HTTP 方法注解 public Result__________ addDoctor( __________ RequestBody DoctorAddRequest request) { return Result.success(doctorService.__________(request)); } }填空路径POST__返回类型__Service 接口__________ addDoctorWithSchedules(DoctorAddRequest request);ServiceImpl按步骤写注释 关键代码Service public class DoctorServiceImpl implements DoctorService { ​ Autowired private DoctorMapper doctorMapper; Autowired private ScheduleMapper scheduleMapper; ​ Override __________(rollbackFor Exception.class) // 事务注解 public DoctorWithSchedulesVO addDoctorWithSchedules(DoctorAddRequest request) { ​ // 步骤1DTO 转 Doctor 实体 Doctor doctor new Doctor(); doctor.set__________(request.getDname()); // ... ​ // 步骤2插入医生 doctorMapper.__________(doctor); ​ // 步骤3校验 id 是否回填 if (doctor.getId() __________) { throw new BusinessException(__________); } ​ // 步骤4循环插入排班 for (ScheduleItemRequest item : request.getSchedules()) { Schedule schedule new Schedule(); schedule.setDid(__________); // ★ 填什么 schedule.setSdate(item.getSdate()); schedule.setShift(item.getShift()); scheduleMapper.__________(schedule); } ​ // 步骤5组装 DoctorWithSchedulesVO 返回 return __________; } }MapperDoctorMapper.javaint insert(__________ doctor);DoctorMapper.xmlinsert idinsert useGeneratedKeys__________ keyProperty__________ INSERT INTO doctors (dname, dept, title) VALUES (#{dname}, #{dept}, #{title}) /insertScheduleMapper.javaint insert(__________ schedule);PostmanPOST http://localhost:8080/__________ Content-Type: application/json ​ { dname: , dept: , title: , schedules: [ {sdate: 2025-06-10, shift: morning}, {sdate: __________, shift: __________}, {sdate: __________, shift: __________} ] }四、编程题 2删除医生要求级联删排班 · 不存在返回 404Controller__________(/{id}) public ResultVoid deleteDoctor(__________ Integer id) { doctorService.__________(id); return Result.success(删除成功, null); }填空HTTP 方法__路径DELETE__Service 接口void deleteDoctor(__________ id);ServiceImplOverride Transactional(rollbackFor Exception.class) public void deleteDoctor(Integer id) { ​ // 步骤1查医生 Doctor doctor doctorMapper.__________(id); ​ // 步骤2不存在抛异常 if (doctor __________) { throw new BusinessException(__________, __________); } ​ // 步骤3删排班先 scheduleMapper.__________(id); ​ // 步骤4删医生后 doctorMapper.__________(id); }MapperDoctorMapper.javaDoctor selectById(__________ id); int deleteById(__________ id);ScheduleMapper.javaint deleteByDoctorId(Param(did) __________ did);ScheduleMapper.xmldelete iddeleteByDoctorId DELETE FROM schedules WHERE did #{__________} /delete五、编程题 3修改排班班次要求按 id 升序 · morning/afternoon/evening · 返回修改后列表Controller__________(/{id}/schedules/shift) public ResultListScheduleVO updateScheduleShifts(PathVariable Integer id) { return Result.success(doctorService.__________(id)); }填空HTTP 方法__路径PUT__Service 接口ListScheduleVO updateScheduleShifts(__________ doctorId);ServiceImplprivate static final String[] TARGET_SHIFTS { __________, __________, __________ }; ​ Override Transactional(rollbackFor Exception.class) public ListScheduleVO updateScheduleShifts(Integer doctorId) { ​ // 步骤1校验医生 Doctor doctor doctorMapper.selectById(doctorId); if (doctor null) { throw new BusinessException(__________, __________); } ​ // 步骤2查排班按 id 升序 ListSchedule schedules scheduleMapper.__________(doctorId); ​ // 步骤3循环修改 ListScheduleVO result new ArrayList(); for (int i 0; i schedules.size(); i) { Schedule schedule schedules.get(i); if (i TARGET_SHIFTS.__________) { // length schedule.setShift(TARGET_SHIFTS[i]); scheduleMapper.updateShift(schedule.getId(), schedule.getShift()); } // 步骤4组装 ScheduleVO含 dname、dept // ... result.add(vo); } return result; }MapperScheduleMapper.javaListSchedule selectByDoctorIdOrderById(Param(did) Integer did); int updateShift(Param(id) Integer id, Param(shift) String shift);ScheduleMapper.xmlselect idselectByDoctorIdOrderById resultMapScheduleResultMap SELECT id, did, sdate, shift FROM schedules WHERE did #{did} ORDER BY id __________ !-- ASC 还是 DESC -- /select ​ update idupdateShift UPDATE schedules SET shift #{shift} WHERE id #{__________} /update六、编程题 4分页查询排班要求dname/dept 模糊查 · pageNo/pageSize · 返回 total recordsControllerRestController RequestMapping(__________) public class ScheduleController { ​ GetMapping(/page) public ResultPageResultScheduleVO pageQuery( RequestParam(defaultValue __________) Integer pageNo, RequestParam(defaultValue __________) Integer pageSize, RequestParam(required false) String dname, RequestParam(required false) String dept) { return Result.success(scheduleService.pageQuery(pageNo, pageSize, dname, dept)); } }填空路径GET__pageNo 默认值__pageSize 默认值__Service 接口PageResultScheduleVO pageQuery(Integer pageNo, Integer pageSize, String dname, String dept);ServiceImplOverride public PageResultScheduleVO pageQuery(Integer pageNo, Integer pageSize, String dname, String dept) { ​ // 步骤1默认值 if (pageNo null || pageNo 1) pageNo __________; if (pageSize null || pageSize 1) pageSize __________; ​ // 步骤2计算 offset公式 long offset (__________ - 1) * __________; ​ // 步骤3查总数 long total scheduleMapper.__________(dname, dept); ​ // 步骤4查当前页 ListScheduleVO records scheduleMapper.__________(dname, dept, offset, pageSize); ​ // 步骤5组装 PageResult PageResultScheduleVO pageResult new PageResult(); pageResult.set__________(total); pageResult.set__________(records); return pageResult; }这题需要 Transactional 吗□ 需要 □ 不需要MapperScheduleMapper.javaListScheduleVO selectSchedulePage(Param(dname) String dname, Param(dept) String dept, Param(offset) long offset, Param(pageSize) long pageSize); long countSchedulePage(Param(dname) String dname, Param(dept) String dept);ScheduleMapper.xml重点默写select idselectSchedulePage resultTypecom.example.demo.dto.ScheduleVO SELECT s.id, s.did, s.sdate, s.shift, d.__________, d.__________ !-- 医生姓名、科室 -- FROM schedules s __________ JOIN doctors d ON s.did d.id !-- INNER 还是 LEFT -- where if testdname ! null and dname ! AND d.dname LIKE CONCAT(%, #{dname}, %) /if if testdept ! null and dept ! AND d.dept LIKE CONCAT(%, #{dept}, %) /if /where ORDER BY s.did __________, s.id __________ LIMIT #{offset}, #{pageSize} /select ​ select idcountSchedulePage resultTypelong SELECT COUNT(1) FROM schedules s INNER JOIN doctors d ON s.did d.id !-- 同上 where 条件 -- /select七、四题对比速记填表题HTTP路径要事务吗核心 Mapper 方法1 新增/doctors2 删除/doctors/{id}3 改班次/doctors/{id}/schedules/shift4 分页/schedules/page八、SQL 题空白模板7 道SQL 1每个读者的借阅次数含 0 次SELECT r.rid, r.rname, COUNT(__________) AS borrow_count FROM readers r __________ JOIN borrow_records br ON r.rid br.rid -- LEFT 还是 INNER GROUP BY __________;关键词即使没有借阅也要显示 → 用__JOINSQL 2读者张三的借阅记录SELECT br.brid, br.bid, br.bdate, br.duedate FROM borrow_records br __________ JOIN readers r ON br.rid r.rid WHERE r.rname __________;SQL 3每本图书的借阅次数SELECT b.bid, b.bname, COUNT(br.brid) AS borrow_count FROM books b __________ JOIN borrow_records br ON b.bid br.bid GROUP BY __________;SQL 4定价高于平均值的图书SELECT bid, bname, price FROM books WHERE price (SELECT __________(price) FROM books);SQL 5每个作者的图书数量降序SELECT a.aid, a.aname, COUNT(b.bid) AS book_count FROM authors a __________ JOIN books b ON a.aid b.aid GROUP BY a.aid, a.aname ORDER BY book_count __________;SQL 6各出版社借阅统计仅 returned降序SELECT p.pid, p.pname, COUNT(br.brid) AS borrow_total FROM publishers p INNER JOIN books b ON p.pid b.pid INNER JOIN borrow_records br ON b.bid br.bid WHERE br.status __________ GROUP BY p.pid, p.pname ORDER BY borrow_total __________;SQL 7逾期未还borrowing 且应还日期早于今天SELECT r.rname, b.bname, br.bdate, br.duedate FROM borrow_records br INNER JOIN readers r ON br.rid r.rid INNER JOIN books b ON br.bid b.bid WHERE br.status __________ AND br.duedate __________(); -- 当前日期函数九、自测打分表模块满分得分备注项目骨架10pom/yml/启动类统一响应 异常10Result/PageResult/Handler题1 新增20事务循环插入题2 删除15404级联题3 改班次20排序规则题4 分页20JOINLIKELIMITSQL 7道60见 sql 部分合计155十、15 分钟极限速记最后考前看题1POST /doctors → Transactional → insert doctor → 循环 insert schedule 题2DELETE /doctors/{id} → selectById → 404 → deleteByDoctorId → deleteById 题3PUT /doctors/{id}/schedules/shift → ORDER BY id ASC → morning/afternoon/evening 题4GET /schedules/page → offset(pageNo-1)*pageSize → count JOIN select LIMIT ​ SQL口诀 含0统计 → LEFT JOIN COUNT GROUP BY 指定条件 → INNER JOIN WHERE 高于平均 → 子查询 AVG 排序统计 → GROUP BY ORDER BY count DESC 逾期 → statusborrowing AND duedate CURDATE()参考答案默写完成后再对照​点击展开答案​四题对比题HTTP要事务核心方法1POST✅insert × 22DELETE✅selectById deleteByDoctorId deleteById3PUT✅selectByDoctorIdOrderById updateShift4GET❌countSchedulePage selectSchedulePageSQL 关键词LEFT JOININNER JOIN, 张三LEFT JOINAVGLEFT JOIN, DESCreturned, DESCborrowing, CURDATE/details