多对多【Java实现多表操作笔记四】

多对多

原则：一方存多方的集合，多方存一方的对象

但现在双方都是多方数据，此时原则为：多方存另一方的集合

多对多的关系需要创建第三张表来实现，但中间表没有实体类

 

实例：菜单表和角色表模拟实现多对多关系：一个菜单项对应多个角色项，一个角色项对应多个菜单项

 

实现过程：

1、javabean

数据库中使用外键列保证两表关系，实体类中使用属性保证两表关系

[src.bean.Menu.java]
public class Menu {
    private int menuId;
    private String menuName;

    //多方存另一方的集合
    private List<Role> roleList;
    public List<Role> getRoleList() {return roleList;}
    public void setRoleList(List<Role> roleList) {this.roleList = roleList;}

    public int getMenuId() {return menuId;}
    public void setMenuId(int menuId) {this.menuId = menuId;}
    public String getMenuName() {return menuName;}
    public void setMenuName(String menuName) {this.menuName = menuName;}
}
[src.bean.Role.java]
public class Role {
    private int roleId;
    private String roleName;

    //多方存另一方的集合
    private List<Menu> menuList;
    public List<Menu> getMenuList() {return menuList;}
    public void setMenuList(List<Menu> menuList) {this.menuList = menuList;}

    public int getRoleId() {return roleId;}
    public void setRoleId(int roleId) {this.roleId = roleId;}
    public String getRoleName() {return roleName;}
    public void setRoleName(String roleName) {this.roleName = roleName;}
}

2、dao  实现对应数据关系

/*接口：[src.dao.RoleDao.java(interface)]*/
public interface RoleDao {
    //查询某个菜单信息（包含角色）
    public Menu findByMenuId(int mid);
    //查询某个角色信息(要求包含菜单)
    public Role findByRoleId(int roleid);
}

/*实现接口：[src.dao.impl.RoleDaoImpl.java]*/
@Override
public Menu findByMenuId(int mid) {
    Menu menu = new Menu();
    ArrayList<Role> roles = new ArrayList<>();
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = getConnection();
        preparedStatement = connection.prepareStatement("select * from menu m ,role r, middle where m.menuid=middle.mid and r.roleid=middle.rid and m.menuid=?");
        preparedStatement.setInt(1,mid);
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            //1.先各自存数据
            menu.setMenuName(resultSet.getString("menuname"));
            Role role = new Role();
            role.setRoleName(resultSet.getString("rolename"));
            //2.建立二者关系
            roles.add(role);
        }
        menu.setRoleList(roles);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        close(connection,preparedStatement,resultSet);
    }
    return menu;
}
@Override
public Role findByRoleId(int roleid) {
    Role role = new Role();
    ArrayList<Menu> menuArrayList = new ArrayList<Menu>();
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = getConnection();
        preparedStatement = connection.prepareStatement("select * from menu m ,role r, middle where m.menuid=middle.mid and r.roleid=middle.rid and r.roleid=?");
        preparedStatement.setInt(1,roleid);
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            //1.先各自存数据
            Menu menu = new Menu();
            menu.setMenuName(resultSet.getString("menuname"));
            role.setRoleName(resultSet.getString("rolename"));
            //2.建立二者关系
            menuArrayList.add(menu);
        }
        role.setMenuList(menuArrayList);
    } catch (SQLException throwables) {
         throwables.printStackTrace();
    } finally {
         close(connection,preparedStatement,resultSet);
    }
    return role;
}

 

3、Demo4.java  测试关系

public class Demo4 {
    public static void main(String[] args) {
        RoleDaoImpl roleDao = new RoleDaoImpl();
        /*Menu menu = roleDao.findByMenuId(2);
        System.out.println(menu.getMenuName());
        List<Role> roleList = menu.getRoleList();
        for (Role role : roleList) {
            System.out.println(role.getRoleName());
        }*/
        Role role = roleDao.findByRoleId(2);
        System.out.println(role.getRoleName());
        List<Menu> menuList = role.getMenuList();
        for (Menu menu : menuList) {
            System.out.println(menu.getMenuName());
        }
    }
}