dao层多表操作

关系多表取出,根据面向对象思想通常是将数据封装为对象,由一个主对象包装其他子对象,子对象包装子子对象,由主对象的dao进行数据库操作,从而简化逻辑,防止混乱。

  如对teacher与student这种多对多的关系,设计好teacher表,student表,teacher_student表后,在java中设计由teacher来维护三个表,student可以放弃对teacher_student表的操作。当发生学生换老师的这种只改变teacher_student表的操作时,不能使用update,而是先delete后add来实现。

  设计代码如下:

  domain类:

package com.hao.domain;

import java.util.Set;

public class Teacher {
    private String id;
    private String name;
    private double salary;
    private Set<Student> students;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public Set<Student> getStudents() {
        return students;
    }

    public void setStudents(Set<Student> students) {
        this.students = students;
    }
}
package com.hao.domain;

import java.util.Set;

public class Student {
    private String id;
    private String name;
    //这里的teachers字段不是给dao层使用的,这样设计是方便其他层有相应的需求
    private Set<Teacher> teachers;
    
    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<Teacher> getTeachers() {
        return teachers;
    }

    public void setTeachers(Set<Teacher> teachers) {
        this.teachers = teachers;
    }
}

dao层:

package com.hao.dao;

import com.hao.domain.Student;
import com.hao.domain.Teacher;
import com.hao.utils.DaoUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class TeacherDao {
    private QueryRunner qr = new QueryRunner(DaoUtils.getDataSource());

    public void add(Teacher teacher) {
        try {
            //老师加入老师表
            String sql = "insert into teacher(id,name,salary) values(?,?,?)";
            qr.update(sql, teacher.getId(), teacher.getName(), teacher.getSalary());
            Set<Student> students = teacher.getStudents();
            String teacherId = teacher.getId();
            for (Student student : students) {
                //学生加入学生表
                sql = "insert into student(id,name) values(?,?)";
                qr.update(sql, student.getId(), student.getName());
                //关系插入映射表
                sql = "insert into teacher_student(teacher_id,student_id) values(?,?)";
                qr.update(teacherId, student.getId());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void delete(String id) {
        String sql = "delete from teacher where id = ?";
        try {
            qr.update(sql, id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void update(Teacher teacher) {
        try {
            //更新老师表
            String sql = "update teacher set name=?,salary=? where id=?";
            qr.update(sql, teacher.getName(), teacher.getSalary(), teacher.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public Teacher find(String id) {
        try {
            //找到老师
            String sql = "select name,salary from teacher where id=?";
            Teacher teacher = qr.query(sql, new BeanHandler<Teacher>(Teacher.class), id);
            teacher.setId(id);
            //找到老师的学生,这个可以没有,看需求
            sql = "select st.id,st.name from student st,teacher_student te where te.teacher_id=? and te.student_i=dst.id";
            List<Student> students = qr.query(sql, new BeanListHandler<Student>(Student.class), id);
            teacher.setStudents(new HashSet<Student>(students));
            return teacher;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}


  • 2
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值