Java-后端多表增删改查

public abstract class BaseTest {
    protected JDBCTemplate jdbcTemplate = new JDBCTemplate() ;
    // 初始化 测试环境
    @Before
    public void before(){
        //清空表数据  truncate table 截断表  无法使用 关联关系  先删除儿子 在删除爹
        jdbcTemplate.update("delete from t_student ") ;
        jdbcTemplate.update("delete from t_clazz ") ;

        //插入班级数据
        jdbcTemplate.update("insert into t_clazz(bno,name) values ('B001','Java177')") ;
        jdbcTemplate.update("insert into t_clazz(bno,name) values ('B002','Java178')") ;
        //插入 68 条测试数据
        for (int i = 0; i < 68; i++) {
            jdbcTemplate.update("insert into t_student(sid,name,age,sex,bno) values ('S"+(i+1)+"','张三"+(i+1)+"',18,0,'B001')") ;
        }
    }

}

public class Clazz {
    private String bno ;
    private String name ;
}

public class Student {
    private String sid ;
    private String name ;
    private Integer age ;
    private Integer sex ;
    private Clazz clazz = new Clazz()  ;
}

// t_student表 t_clazz表 同名列 二义性 条件查询需指定 哪个表的name
if (query!=null &&  StringUtils.isNotEmpty(query.getName())) {
            sb.append(" and s.name like ?") ;
            paramList.add("%"+query.getName()+"%");
 }

public interface ClazzDAO {
    List<Clazz> selectAll() ;
}

public class ClazzDAOImpl implements ClazzDAO {
    private JDBCTemplate jdbcTemplate = new JDBCTemplate() ;

    @Override
    public List<Clazz> selectAll() {
        String DQL = "select bno,name from t_clazz" ;
        Object[] paramAy = {} ;
        return jdbcTemplate.queryList(DQL,Clazz.class,paramAy);
    }

}

public class ClazzService {
    private ClazzDAO clazzDAO = new ClazzDAOImpl() ;

    /**
     * 查询所有班级
     * @return
     */
    public Collection<Clazz> list(){
        return clazzDAO.selectAll();
    }

}

@WebServlet(..."/toAddView-stu.do"})
public class StudentServlet extends HttpServlet {
    private ClazzService clazzService = new ClazzService() ;
    
    ... service(..){
        else if ("/toAddView-stu.do".equals(requestURI)){
            this.toAddView(request,response) ;
        }
    }
    
    //跳转到新增学生界面 
    protected void toAddView(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        Collection<Clazz> clazzList = clazzService.list();
        request.setAttribute("clazzList",clazzList);
        request.getRequestDispatcher("/add-stu.jsp").forward(request,response);
    }
}

@Override
public boolean insert(Student student) {
    String DQL = "insert into t_student(sid,name,age,sex,bno) values (?,?,?,?,?)" ;
    Object[] paramAy = {student.getSid(),student.getName(),student.getAge(),student.getSex(),student.getClazz().getBno()} ;
    return jdbcTemplate.update(DQL,paramAy);
}

//重定向 进行查询
      response.sendRedirect(request.getContextPath()+"/page-stu.do");

protected void view(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
      //1.获得查看的学生编号
      String sid = request.getParameter("sid");
      //2.调用业务方法
      Student student = studentService.get(sid);
      //3.request作用域中设置 学生信息
      request.setAttribute("student",student);
      Collection<Clazz> clazzList = clazzService.list();
      request.setAttribute("clazzList",clazzList);
      //4.跳转页面  服务器内部跳转
      request.getRequestDispatcher("/modify-stu.jsp").forward(request,response);
  }

String DQL = "select sid,s.name,age,sex,c.bno \"clazz.bno\",c.name \"clazz.name\" from t_student s left join t_clazz c on s.bno = c.bno where s.sid=?" ;

@Override
public boolean update(Student student) {
    String DQL = "update t_student set name=?,age=?,sex=?,bno=? where sid=?" ;
    Object[] paramAy = {student.getName(),student.getAge(),student.getSex(),student.getClazz().getBno(),student.getSid()} ;
    return jdbcTemplate.update(DQL,paramAy);
}

@WebServlet(name = "ClazzServlet",urlPatterns = {"/list-clazz.do"})
public class ClazzServlet extends HttpServlet {
    /** 班级业务实例 */
    private ClazzService clazzService = new ClazzService() ;

    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1.获得请求的url  包含项目名
        String requestURI = request.getRequestURI();
        //2. 去掉项目名 /list-stu.do  /add-stu.do
        requestURI = requestURI.replace(request.getContextPath(),"");
        //3.判断 url 与方法 调用
        if ("/list-clazz.do".equals(requestURI)){
            this.list(request,response) ;
        }
    }

    /**
     *
     * @param request
     * @param response
     * @throws ServletException
     * @throws IOException
     */
    protected void list(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1. 调用业务方法
        Collection<Clazz> clazzCollection = clazzService.list();
        //2. 把查询数据保存 四种(pageContext,request,session,application)属性作用域中  set  ==> list-stu.jsp  get
        request.setAttribute("clazzList",clazzCollection);
        //3. 跳转到 list-stu.jsp
        request.getRequestDispatcher("/list-clazz.jsp").forward(request,response);
    }
}

//  ClazzServlet
@WebServlet(name = "ClazzServlet",urlPatterns = {"/list-clazz.do","/toAddView-clazz.do"})
....
    else if("/toAddView-clazz.do".equals(requestURI)){
            this.toAddView(request,response) ;
        }

//跳转到新增班级界面
protected void toAddView(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    request.getRequestDispatcher("/add-clazz.jsp").forward(request,response);
}

//添加新的请求 url
@WebServlet(name = "ClazzServlet",urlPatterns = {"/list-clazz.do","/toAddView-clazz.do","/add-clazz.do"})

//添加判断  调用自定义的方法
else if("/add-clazz.do".equals(requestURI)){
            this.add(request,response) ;
}


protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1.设置请求中文乱码
        request.setCharacterEncoding("UTF-8");
        //调用 自定义 工具类  实现：解析请求自动封装 pojo中
        Clazz clazz = RequestUtil.parseParameter(request, Clazz.class);
        //4. 调用业务方法
        boolean saveRs = clazzService.save(clazz);
        //5. 根据业务方法的返回值
        if (saveRs){
            //6.响应
            //重定向 进行查询
            response.sendRedirect(request.getContextPath()+"/list-clazz.do");
            return;
        }
        //新增失败  view实现显示效果
        request.setAttribute("errorMsg","班号已经存在！！！");
        //使用服务器内部跳转
        request.getRequestDispatcher("/toAddView-clazz.do").forward(request,response);
}

... ClazzService {
    public boolean save(Clazz clazz) {
        //获得新增的班号
        String bno = clazz.getBno() ;
        //1.判断是否 新增的班号 db 中已经存在
        Clazz dbClazz = clazzDAO.selectById(bno);
        if (dbClazz!=null){
            return false ;
        }
        //班号不存在  向数据库插入数据
        return clazzDAO.insert(clazz) ;
    }
}

... ClazzDAO{
    Clazz selectById(String bno);

    boolean insert(Clazz clazz);
}

... ClazzDAOImpl ... {
    @Override
    public Clazz selectById(String bno) {
        String DQL = "select bno,name from t_clazz where bno=?" ;
        Object[] paramAy = {bno} ;
        return jdbcTemplate.queryObject(DQL,Clazz.class,paramAy);
    }

    @Override
    public boolean insert(Clazz clazz) {
        String DQL = "insert into t_clazz(bno,name) values (?,?)" ;
        Object[] paramAy = {clazz.getBno(),clazz.getName()} ;
        return jdbcTemplate.update(DQL,paramAy);
    }
}    

@WebServlet(name = "ClazzServlet",urlPatterns = {"/list-clazz.do","/toAddView-clazz.do","/add-clazz.do","/del-clazz.do"})

else if("/del-clazz.do".equals(requestURI)){
            this.del(request,response) ;
}

protected void del(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1.获得 删除的班号
        String[] idAy = request.getParameterValues("bno");
        //把数组 拼接成一个 字符串
        String id ="";
        for (String s : idAy) {
            id +=s+"," ;
        }
        id = id.substring(0,id.length()-1) ;
        //2. 调用业务方法
        boolean removeRs = clazzService.remove(id);
        String msg = removeRs?"":"del-fail" ;
        response.sendRedirect(request.getContextPath()+"/list-clazz.do?msg="+msg);
}

public class ClazzService {
    private StudentDAO studentDAO = new StudentDAOImpl() ;

    public boolean remove(String id) {
        //字符串 转成数组
        String[] sidAy = id.split(",");
        for (String bno : sidAy) {
            //根据FK 查询学生列表
            List<Student> studentList = studentDAO.selectByFk(bno);
            //删除班级编号 在学生表中出现了   不能删除
            if (!studentList.isEmpty()){
                return false ;
            }
        }
        //调用批处理删除
        return clazzDAO.delete(sidAy) ;
    }
}

List<Student> selectByFk(String bno) ;

@Override
public List<Student> selectByFk(String bno) {
    String DQL = "select sid,name,age,sex from t_student where bno=?" ;
    Object[] paramAy = {bno} ;
    return jdbcTemplate.queryList(DQL,Student.class,paramAy);
}

boolean delete(String ... idAy) ;

public class ClazzDAOImpl implements ClazzDAO {
    @Override
    public boolean delete(String... sidAy) {
        String DML = "delete from t_clazz where bno=?" ;
        Object[][] paramAy = new Object[sidAy.length][1] ;
        for (int i = 0; i < sidAy.length; i++) {
            paramAy[i][0] = sidAy[i] ;
        }
        return jdbcTemplate.updateBatch(DML,paramAy);
    }

}

@WebServlet(name = "ClazzServlet",urlPatterns = {"/list-clazz.do","/toAddView-clazz.do","/add-clazz.do","/del-clazz.do","/view-clazz.do"})

else if("/view-clazz.do".equals(requestURI)){
      this.view(request,response) ;
}


protected void view(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    //1.获得查看的学生编号
    String bno = request.getParameter("bno");
    //2.调用业务方法
    Clazz clazz = clazzService.get(bno) ;
    //3.request作用域中设置 学生信息
    request.setAttribute("clazz",clazz);
    //4.跳转页面  服务器内部跳转
    request.getRequestDispatcher("/modify-clazz.jsp").forward(request,response);
}

 public Clazz get(String bno) {
    //N+1查询
    //1. 根据班级id 查询 班级对象  单表查询
    Clazz clazz = clazzDAO.selectById(bno);
    //2. 根据班级id 查询当前班级的学生列表  单表查询
    List<Student> studentList = studentDAO.selectByFk(bno);
    //3.当前班级对象的 stuList属性进行赋值
    clazz.setStudentList(studentList);
    return clazz ;
}

@WebServlet(name = "ClazzServlet",urlPatterns = {"/list-clazz.do","/toAddView-clazz.do","/add-clazz.do","/del-clazz.do","/view-clazz.do","/modify-clazz.do"})


else if("/modify-clazz.do".equals(requestURI)){
     this.modify(request,response) ;
}

protected void modify(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    //1.设置请求中文乱码
    request.setCharacterEncoding("UTF-8");
    //调用 自定义 工具类  实现：解析请求自动封装 pojo中
    Clazz clazz = RequestUtil.parseParameter(request, Clazz.class);
    //4. 调用业务方法
    boolean modifyRs = clazzService.modify(clazz);
    //5. 根据业务方法的返回值
    if (modifyRs){
        //6.响应
        //重定向 进行查询
        response.sendRedirect(request.getContextPath()+"/list-clazz.do");
        return;
    }
    request.setAttribute("errorMsg","修改班级失败！！！");
    request.getRequestDispatcher("/view-clazz.do").forward(request,response);
}

public boolean modify(Clazz clazz) {
        return clazzDAO.update(clazz) ;
}

boolean update(Clazz clazz);

@Override
public boolean update(Clazz clazz) {
    String DQL = "update t_clazz set name=? where bno=?" ;
    Object[] paramAy = {clazz.getName(),clazz.getBno()} ;
    return jdbcTemplate.update(DQL,paramAy);
}

@Before
public void before(){
    //清空表数据  truncate table 截断表  无法使用 关联关系  先删除儿子 在删除爹
    jdbcTemplate.update("delete from t_student_teacher ") ;
    jdbcTemplate.update("delete from t_teacher ") ;
    jdbcTemplate.update("delete from t_student ") ;
    jdbcTemplate.update("delete from t_clazz ") ;

    //插入班级数据
    jdbcTemplate.update("insert into t_clazz(bno,name) values ('B001','Java177')") ;
    jdbcTemplate.update("insert into t_clazz(bno,name) values ('B002','Java178')") ;
    //插入学生 68 条测试数据
    for (int i = 0; i < 68; i++) {
        jdbcTemplate.update("insert into t_student(sid,name,age,sex,bno) values ('S"+(i+1)+"','张三"+(i+1)+"',18,0,'B001')") ;
    }
    //插入教师数据
    jdbcTemplate.update("insert into t_teacher(tno,tname) values ('T001','赵贺贺')") ;
    jdbcTemplate.update("insert into t_teacher(tno,tname) values ('T002','赵健')") ;
    jdbcTemplate.update("insert into t_teacher(tno,tname) values ('T003','马美平')") ;

    //插入学生与教师关系表 68*2 条测试数据
    for (int i = 0; i < 68; i++) {
        jdbcTemplate.update("insert into t_student_teacher(sid,tno) values ('S"+(i+1)+"','T001'),('S"+(i+1)+"','T002')") ;
    }
}

public class Teacher {
    private String tno ;
    private String tname ;
}

public class Student {
    ...
    /**Many2Many 一个学生拥有多个老师 */
    private List<Teacher> teacherList ;
}

public interface TeacherDAO {
    List<Teacher> selectBySid(String sid) ;
}

public class TeacherDAOImpl implements TeacherDAO {
    private JDBCTemplate jdbcTemplate = new JDBCTemplate() ;
    @Override
    public List<Teacher> selectBySid(String sid) {
        String DQL = "select tno,tname from t_teacher where tno in (select tno from t_student_teacher where sid = ?)" ;
        Object[] paramAy = {sid} ;
        return jdbcTemplate.queryList(DQL, Teacher.class,paramAy);
    }
}

public class StudentService {
    private TeacherDAO teacherDAO = new TeacherDAOImpl() ;

    public void selectPage(Page<Student, StudentQuery> page){
        //1.统计总记录数
        long count = studentDAO.count(page.getCond());
        //2.查询当前页面的数据
        List<Student> studentList = studentDAO.selectPage(page);
        //循环所有的学生列表
        for (Student student : studentList) {
            //根据学号查询老师信息
            List<Teacher> teacherList = teacherDAO.selectBySid(student.getSid());
            //设置到 当前学生的 关联老师集合属性
            student.setTeacherList(teacherList);
        }
        //3. 设置总记录数
        page.setTotalRecord(count);
        //4. 设置当前页的数据
        page.setList(studentList);
    }
}

public interface TeacherDAO {
    List<Teacher> selectAll();
}

public class TeacherDAOImpl implements TeacherDAO {
    @Override
    public List<Teacher> selectAll() {
        String DQL = "select tno,tname from t_teacher" ;
        Object[] paramAy = {} ;
        return jdbcTemplate.queryList(DQL, Teacher.class,paramAy);
    }
}

public class StudentService {
    private TeacherDAO teacherDAO = new TeacherDAOImpl() ;

    /**
     * 根据id 查询学生信息
     * @param sid
     * @return
     */
    public Student get(String sid){
        //学生信息  班级信息
        Student student =  studentDAO.selectById(sid) ;
        // N+1 根据学生id 查询关联的教师
        List<Teacher> teacherList = teacherDAO.selectBySid(sid);
        // 设置当前学生关联的教师信息
        student.setTeacherList(teacherList);
        return student ;
    }
}

@WebServlet(.... "/toSelectView-teacher.do"})


else if ("/toSelectView-teacher.do".equals(requestURI)){
     this.toSelectTeacherView(request,response) ;
}


protected void toSelectTeacherView(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    //1.获得学生的id
    String sid = request.getParameter("sid");
    //2.查询当前学生信息
    Student student = studentService.get(sid);
    //3.查询所有的老师信息
    List<Teacher> teacherList = teacherService.list() ;
    //4. 数据存放在作用域中
    request.setAttribute("student",student);
    request.setAttribute("teacherList",teacherList);
    request.getRequestDispatcher("/select-teacher.jsp").forward(request,response);
}

public interface StudentDAO {
    boolean deleteRefBySid(String sid) ;

    boolean insertRef(Student student) ;
}

public class StudentDAOImpl implements StudentDAO {
    @Override
    public boolean deleteRefBySid(String sid) {
        String DQL = "delete from t_student_teacher where sid = ?" ;
        Object[] paramAy = {sid} ;
        return jdbcTemplate.update(DQL,paramAy);
    }

    @Override
    public boolean insertRef(Student student) {
        //获得当前学生的学号
        String sid = student.getSid();
        //获得当前学生关联的教师信息
        List<Teacher> teacherList = student.getTeacherList();
        String DML = "insert into t_student_teacher(sid,tno) values(?,?)" ;
        Object[][] paramAy = new Object[teacherList.size()][2] ;
        for (int i = 0; i < teacherList.size(); i++) {
            //教师信息
            Teacher teacher = teacherList.get(i);
            paramAy[i][0] = sid ;
            paramAy[i][1] = teacher.getTno() ;
        }
        return jdbcTemplate.updateBatch(DML,paramAy);
    }
}

public class StudentService {

    public boolean setTeacher(Student student){
        //1.中间表删除 当前学号 关联所有信息
        boolean delRs = studentDAO.deleteRefBySid(student.getSid()) ;
        //2.批量新增 中间表 关联信息
        boolean insertRs = studentDAO.insertRef(student) ;
        return delRs && insertRs ;
    }
}

@WebServlet(..."/set-teacher.do"})

else if ("/set-teacher.do".equals(requestURI)){
     this.setTeacher(request,response) ;
}


protected void setTeacher(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // 获得学生的id
    String sid = request.getParameter("sid");
    // 关联的教师工号
    String[] tnoAy = request.getParameterValues("tno");
    //封装学生对象
    Student student = new Student();
    student.setSid(sid);
    List<Teacher> teacherList = new ArrayList<>() ;
    for (String tno : tnoAy) {
        Teacher teacher = new Teacher();
        teacher.setTno(tno);
        teacherList.add(teacher);
    }
    //设置关联关系
    student.setTeacherList(teacherList);
    boolean rs = studentService.setTeacher(student) ;
    //重新查询
    response.sendRedirect(request.getContextPath()+"/page-stu.do");
}