//1.创建用户(传入用户信息封装成对象)insert into 。。。
//2.需要从dao层获取权限表内容,前台递归展示权限表,设置用户权限 select * from
//3.给中间表设置用户权限(rid,pid) rid是用户自增的id需要用一个方法获取
//自始至终只需要动两张表中间表(角色权限表)和角色表
//dao层新增万用户返回新增id,service层获得id后传回给dao层,用来设置中间表
需要用到的sql语句有三条
-- 角色与权限(多对多)
role permission role_per
-- 新增角色 给新的角色 分配功能
-- 执行的sql:
-- 分配权限 看的基础之上选择
select * from permisson; -- 层级展示 1 2 3
-- 一条sql 就是dao里面的一个方法
insert into role (role_name,role_status,role_desc) values (?,?,?);
insert into role_per (rid,pid) values (?,?),(?,?),(?,?)....
-- rid 是角色的id 刚刚的新增
-- pid 权限id
dao层及实现类
//需求: 新增角色 并关联角色对应的权限。
public interface RoleDao {
/**
* 将r传至数据库
* @param r 封装角色信息的对象
* @return 返回新增用户id
*/
int addRole(Role r);
/**
* 查询所有权限
* @return 以集合形式返回
*/
List<Permission> showAuthority();
/**
* 在中间表添加角色权限
* @param rid 角色id
* @param choice 权限
* @return 受影响的条数
*/
int addRolePer(int rid,String choice);
}
public class RoleDaoImpl implements RoleDao {
Connection conn;
public RoleDaoImpl(Connection conn) {
this.conn = conn;
}
@Override
public int addRole(Role r) {
String sql = "INSERT INTO Role(role_name, role_status, role_desc) VALUES(?,?,?)";
PreparedStatement ps = null;
try {
//编译时,默认不返回自增id,需要再传一个参数
ps = conn.prepareStatement(sql);
ps.setString(1, r.getRoleName());
ps.setString(2,r.getRoleStatus());
ps.setString(3,r.getRoleDesc());
ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
int anInt = 0;
//新增完毕后,执行sql查询自增用户id select last_insert_id();
try {
PreparedStatement p = conn.prepareStatement("select last_insert_id()");
ResultSet rs = p.executeQuery(); //执行sql,获取结果集
//判断结果集是否为空
if(rs.next())
anInt = rs.getInt(1); //返回的自增id
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return anInt;
}
@Override
public List<Permission> showAuthority() {
PreparedStatement ps = null;
ResultSet rs = null;
List<Permission> l = new ArrayList<>(10);
try {
ps = conn.prepareStatement("SELECT * FROM permission");
rs = ps.executeQuery();
while(rs.next())
{
Permission p = new Permission();
p.setId(rs.getInt("id"));
p.setPerName(rs.getString("per_name"));
p.setPerUrl(rs.getString("per_url"));
p.setPerIdentity(rs.getString("per_identity"));
p.setPerLogo(rs.getString("per_logo"));
p.setPerStatus(rs.getInt("per_status"));
p.setParentPerId(rs.getInt("parent_per_id"));
l.add(p);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
rs.close();
ps.close();
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return l;
}
@Override
public int addRolePer(int rid, String choice) {
StringBuilder sql = new StringBuilder("Insert Into role_per(rid,pid) values");
String[] c = choice.split(",");
for (String s : c) {
sql.append("(?,?),");
}
sql.delete(sql.lastIndexOf(","),sql.length());
int i = 0;
try {
PreparedStatement ps = conn.prepareStatement(sql.toString());
int num = 0;
for (String s : c) {
ps.setInt(++num,rid);
ps.setInt(++num,Integer.parseInt(s));
}
i = ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return i;
}
}
service层及实现类
public interface RoleService {
/**
* 用来操作dao层方法
* @param r
* @param choice
* @return
*/
int insertRole(Role r,String choice);
}
public class RoleServiceImpl implements RoleService {
RoleDao role = new RoleDaoImpl(Connjdbc.conn());
@Override
public int insertRole(Role r,String choice) {
int rid = role.addRole(r);
int i = role.addRolePer(rid, choice);
return i;
}
}
测试类
public class RoleTest {
//1.创建用户(传入用户信息封装成对象)insert into 。。。
//2.需要从dao层获取权限表内容,前台递归展示权限表,设置用户权限 select * from
//3.给中间表设置用户权限(rid,pid) rid是用户自增的id需要用一个方法获取
//自始至终只需要动两张表中间表(角色权限表)和角色表
//dao层新增万用户返回新增id,service层获得id后传回给dao层,用来设置中间表
static final RoleDao d = new RoleDaoImpl(Connjdbc.conn());
static final RoleService c = new RoleServiceImpl();
public static void main(String[] args) {
insertRole();
}
private static void permissionDao() {
List<Permission> permissions = d.showAuthority();
String str = "|-";
permissions.parallelStream().filter(per->per.getParentPerId()==0)
.peek(parentper-> {
System.out.println(parentper.getId() + "----" + parentper.getPerName());
findChild(parentper,permissions,"| "+str);
}).count();
}
private static void findChild(Permission p, List<Permission> permissions, String s) {
permissions.parallelStream().filter(per->per.getParentPerId()==p.getId())
.peek(cp->{
System.out.println(s+cp.getId()+"---"+cp.getPerName());
findChild(cp,permissions,"| "+s);
}).count();
}
private static void insertRole() {
Scanner a = new Scanner(System.in);
Role r = new Role();
System.out.println("请输入角色名");
r.setRoleName(a.next());
System.out.println("请输入角色状态");
r.setRoleStatus(a.next());
System.out.println("请输入角色描述");
r.setRoleDesc(a.next());
//显示权限表
permissionDao();
System.out.println("请输入该角色权限(1,2,3)");
String choice = a.next();
c.insertRole(r,choice);
}
}