sql:
-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2012 年 03 月 10 日 09:14
-- 服务器版本: 5.5.8
-- PHP 版本: 5.3.5
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- 数据库: `commonrole`
--
-- --------------------------------------------------------
--
-- 表的结构 `rights`
--
CREATE TABLE IF NOT EXISTS `rights` (
`rightId` int(11) NOT NULL AUTO_INCREMENT,
`rightName` varchar(20) NOT NULL,
PRIMARY KEY (`rightId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- 转存表中的数据 `rights`
--
INSERT INTO `rights` (`rightId`, `rightName`) VALUES
(1, '修改文章'),
(2, '删除文章');
-- --------------------------------------------------------
--
-- 表的结构 `role`
--
CREATE TABLE IF NOT EXISTS `role` (
`roleId` int(11) NOT NULL AUTO_INCREMENT,
`roleName` varchar(20) NOT NULL,
PRIMARY KEY (`roleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
--
-- 转存表中的数据 `role`
--
INSERT INTO `role` (`roleId`, `roleName`) VALUES
(1, 'aaa'),
(2, 'bbb'),
(3, 'admin');
-- --------------------------------------------------------
--
-- 表的结构 `roleright`
--
CREATE TABLE IF NOT EXISTS `roleright` (
`roleId` int(11) NOT NULL,
`rightId` int(11) NOT NULL,
PRIMARY KEY (`roleId`,`rightId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `roleright`
--
INSERT INTO `roleright` (`roleId`, `rightId`) VALUES
(1, 1),
(1, 2);
-- --------------------------------------------------------
--
-- 表的结构 `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`userId` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`enable` tinyint(1) NOT NULL,
`remainUses` int(11) NOT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
--
-- 转存表中的数据 `users`
--
-- --------------------------------------------------------
--
-- 表的结构 `usersright`
--
CREATE TABLE IF NOT EXISTS `usersright` (
`userId` int(11) NOT NULL,
`rightId` int(11) NOT NULL,
PRIMARY KEY (`userId`,`rightId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `usersright`
--
-- --------------------------------------------------------
--
-- 表的结构 `usersrole`
--
CREATE TABLE IF NOT EXISTS `usersrole` (
`userId` int(11) NOT NULL,
`roleId` int(11) NOT NULL,
PRIMARY KEY (`userId`,`roleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `usersrole`
--
domain实体类:
package com.ethan.role.domain;
public class User {
private int userId;
private String userName;
private String password;
private short enable;
private int remainUses;
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public short getEnable() {
return enable;
}
public void setEnable(short enable) {
this.enable = enable;
}
public int getRemainUses() {
return remainUses;
}
public void setRemainUses(int remainUses) {
this.remainUses = remainUses;
}
}
package com.ethan.role.domain;
import java.util.Set;
import java.util.Set;
public class Role {
private int roleId;
private String roleName;
private Set<Right> rights;
public Role(int roleId, String roleName) {
this.roleId = roleId;
this.roleName = roleName;
}
public Role(int roleId, String roleName, Set<Right> rights) {
this.roleId = roleId;
this.roleName = roleName;
this.rights = rights;
}
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;
}
@Override
public String toString() {
return "Role [roleId=" + roleId + ", roleName=" + roleName + "]";
}
public Set<Right> getRights() {
return rights;
}
public void setRights(Set<Right> rights) {
this.rights = rights;
}
}
package com.ethan.role.domain;
public class Right {
private int rightId;
private String rightName;
public Right(int rightId, String rightName) {
this.rightId = rightId;
this.rightName = rightName;
}
public int getRightId() {
return rightId;
}
public void setRightId(int rightId) {
this.rightId = rightId;
}
public String getRightName() {
return rightName;
}
public void setRightName(String rightName) {
this.rightName = rightName;
}
@Override
public String toString() {
return "Rights [rightId=" + rightId + ", rightName=" + rightName + "]";
}
}
DAO数据访问层:
package com.ethan.role.domain;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;
public abstract class AbstractBaseDAO {
public final Connection getConnection() throws SQLException {
final String driver = "com.mysql.jdbc.Driver";
final String url = "jdbc:mysql://localhost/commonrole?characterEncoding=UTF-8";
final String userName = "root";
final String password = "";
//不需要容器做支持datasource
final BasicDataSource bds = new BasicDataSource();
bds.setDriverClassName(driver);
bds.setUrl(url);
bds.setUsername(userName);
bds.setPassword(password);
return bds.getConnection();
}
}
package com.ethan.role.domain;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;
public class RoleDAO extends AbstractBaseDAO {
private RoleDAO() {}
private static RoleDAO dao;
public static RoleDAO getInstance() {
if(dao==null) {
synchronized(RoleDAO.class) {
if(dao==null) {
dao = new RoleDAO();
return dao;
}
}
}
return dao;
}
public Role getRoleById(final int id) throws SQLException {
return getRole(new RoleGetter() {
@Override
public void setValue(PreparedStatement pstmt) throws SQLException {
pstmt.setInt(1, id);
}
@Override
public String addCondition() {
return "roleId=?";
}
});
}
public Role getRoleByName(final String name) throws SQLException {
return getRole(new RoleGetter() {
@Override
public void setValue(PreparedStatement pstmt) throws SQLException {
pstmt.setString(1, name);
}
@Override
public String addCondition() {
return "roleName=?";
}
});
}
public void insertRole(Role role) throws SQLException {
Connection conn = this.getConnection();
String sql = "insert into role values(0,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, role.getRoleName());
int i = ps.executeUpdate();
if (i <= 0)
throw new SQLException("插入数据出现了错误");
}
public Role getRoleAndRightsById(final int id) throws SQLException {
return getRoleAndRights(new RoleGetter() {
@Override
public void setValue(PreparedStatement pstmt) throws SQLException {
pstmt.setInt(1, id);
}
@Override
public String addCondition() {
return " r.roleId=?";
}
});
}
public Role getRoleAndRightsByName(final String name) throws SQLException {
return getRoleAndRights(new RoleGetter() {
@Override
public void setValue(PreparedStatement pstmt) throws SQLException {
pstmt.setString(1, name);
}
@Override
public String addCondition() {
return " roleName=?";
}
});
}
public Role getRoleAndRights(final RoleGetter getter ) throws SQLException {
Connection conn = this.getConnection();
String sql = "select * from role r,roleright rr where r.roleId=rr.roleId and "+getter.addCondition();
PreparedStatement pstmt = conn.prepareStatement(sql);
getter.setValue(pstmt);
ResultSet rs = pstmt.executeQuery();
int roleId = 0;
String roleName = "";
//不重复
Set<Right> rights = new HashSet<Right>();
while(rs.next()) {
roleId = rs.getInt(1);
roleName = rs.getString(2);
// int rightId = (Integer) rs.getObject("rightId");
int rightId = rs.getInt(4);
System.out.println(rightId);
Right right = RightDAO.getInstance().getRightsById(rightId);
rights.add(right);
}
return new Role(roleId,roleName,rights);
}
/**
* 私有内部接口
*/
private interface RoleGetter {
public void setValue(final PreparedStatement pstmt) throws SQLException;
public String addCondition();
}
/**
* 至查询了一个表role,延迟加载了 关联对象
* @param getter
* @return
* @throws SQLException
*/
private Role getRole(final RoleGetter getter) throws SQLException {
Connection conn = this.getConnection();
String sql = "select * from role where " +getter.addCondition();
PreparedStatement pstmt =conn.prepareStatement(sql);
//传递pstmt,进行占位符 赋值
getter.setValue(pstmt);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
final int roleId = rs.getInt(1);
final String roleName = rs.getString(2);
return new Role(roleId,roleName);
}
return null;
}
}
package com.ethan.role.domain;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class RightDAO extends AbstractBaseDAO {
private RightDAO() {}
private static RightDAO dao;
public static RightDAO getInstance() {
if(dao==null) {
synchronized(RightDAO.class) {
if(dao==null) {
dao = new RightDAO();
return dao;
}
}
}
return dao;
}
public void insertRight(Right right) throws SQLException {
Connection conn = this.getConnection();
String sql = "insert into rights values(0,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, right.getRightName());
int i = ps.executeUpdate();
if (i <= 0)
throw new SQLException("插入数据出现了错误");
}
public Right getRightsById(final int id) throws SQLException {
return getRights(new RightGetter() {
@Override
public void setValue(PreparedStatement pstmt) throws SQLException {
pstmt.setInt(1, id);
}
@Override
public String addCondition() {
return " rightId=?";
}
});
}
public Right getRightsByName(final String name) throws SQLException {
return getRights(new RightGetter() {
@Override
public void setValue(PreparedStatement pstmt) throws SQLException {
pstmt.setString(1, name);
}
@Override
public String addCondition() {
return " rightName=?";
}
});
}
public Right getRights(RightGetter getter) throws SQLException {
Connection conn = this.getConnection();
String sql = "select * from rights where " +getter.addCondition();
PreparedStatement pstmt =conn.prepareStatement(sql);
//传递pstmt,进行占位符 赋值
getter.setValue(pstmt);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
final int rightId = rs.getInt(1);
final String rightName = rs.getString(2);
return new Right(rightId,rightName);
}
return null;
}
/**
* 私有内部接口
*/
private interface RightGetter {
public void setValue(final PreparedStatement pstmt) throws SQLException;
public String addCondition();
}
}
user,role,right 这三张表,数据模型关系为多为多。如果是给user 某个权限,直接操纵中间表,向中间表插入数据。
测试类:
package com.ethan.role.test;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Set;
import org.junit.Test;
import com.ethan.role.domain.Right;
import com.ethan.role.domain.RightDAO;
import com.ethan.role.domain.Role;
import com.ethan.role.domain.RoleDAO;
public class TestRole {
@Test
public void selectRoleById() {
RoleDAO dao = RoleDAO.getInstance();
try {
System.out.println(dao.getRoleById(1));
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void selectRightById() throws SQLException {
RightDAO dao = RightDAO.getInstance();
System.out.println(dao.getRightsById(1));
}
@Test
public void selectRightByName() throws SQLException {
RightDAO dao = RightDAO.getInstance();
System.out.println(dao.getRightsByName("修改文章"));
}
@Test
public void insertRole() throws SQLException {
RoleDAO dao = RoleDAO.getInstance();
dao.insertRole(new Role(1,"admin"));
}
@Test
public void insertRight() throws SQLException {
RightDAO dao = RightDAO.getInstance();
dao.insertRight(new Right(1,"删除文章"));
}
@Test
public void getRoleAndRightsById() throws SQLException {
RoleDAO dao = RoleDAO.getInstance();
Role role = dao.getRoleAndRightsById(2);
System.out.println(role);
Set<Right> rights = role.getRights();
if(rights==null||rights.size()<=0) {
System.out.println("该角色没有权限");
} else {
Iterator<Right> it = rights.iterator();
while(it.hasNext()) {
Right rr = it.next();
System.out.println(rr.getRightName());
}
}
}
}
这里没有给出UserDAO!