userbean.java

strutsBUG管理系统

package com.yourcompany.struts.action;

import java.sql.*;
import java.util.*;

public class UserBean {
	
	public static final String DRIVER="sun.jdbc.odbc.JdbcOdbcDriver";
//	public static final String DBURL="jdbc:odbc:test";
	public static final String DBURL="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=c:/test.mdb";
	Connection conn;
	Statement stmt;
	ResultSet rst;
	
	public UserBean() {
		try {
			Class.forName(DRIVER);
			System.out.println("Driver...");
			conn=DriverManager.getConnection(DBURL,"","");
			System.out.println("DBURL...");
		} catch (Exception e) {
			e.printStackTrace();
		}		
	}
	
	public String getPwd(String loginID){
		String password="";
		String sql="select password from member where loginID ='"+loginID+"'";
		try {
			 stmt=conn.createStatement();
			 rst=stmt.executeQuery(sql);
			 if(rst.next()){
				 password=rst.getString("password");
//				 System.out.println("##..."+password);
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return password;
	}

	public String getName(String loginID){
		String name="";
		String sql="select name from member where loginID ='"+loginID+"'";
		try {
			 stmt=conn.createStatement();
			 rst=stmt.executeQuery(sql);
			 if(rst.next()){
				 name=rst.getString("name");
//				 System.out.println(name);
				 
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return name;
	}

	public String getRoleID(String loginID){
		String roleID="";
		String sql="select roleID from memberToRole where loginID ='"+loginID+"'";
		try {
			 stmt=conn.createStatement();
			 rst=stmt.executeQuery(sql);
			 if(rst.next()){
				 roleID=rst.getString("roleID");
//				 System.out.println("@#$%"+roleID);		 
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return roleID;
	}
	
	public String getRoleName(String roleID){		
		String roleName="";
		String sql="select roleName from role where roleID ='"+roleID+"'";
		try {
			 stmt=conn.createStatement();
			 rst=stmt.executeQuery(sql);
			 if(rst.next()){
				 roleName=rst.getString("roleName");
//				 System.out.println(roleName);
				 
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return roleName;
	}
	
	public String getPowerIDFromP(String powerName){		
		String powerID="";
		String sql="select powerID from power where powerName ='"+powerName+"'";
		try {
			 stmt=conn.createStatement();
			 rst=stmt.executeQuery(sql);
			 if(rst.next()){
				 powerID=rst.getString("powerID");
//				 System.out.println(roleName);
				 
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return powerID;
	}
	
	public void deleteMember(String loginID){
		String sql="delete from member where loginID ='"+loginID+"'";
		try {
			 stmt=conn.createStatement();
			 stmt.executeUpdate(sql);
			 rst=stmt.executeQuery("select * from member");
//			 while(rst.next()){
//				 loginID=rst.getString("loginID");
//				 System.out.println(loginID);
//				 
//			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		String sql2="delete from memberToRole where loginID ='"+loginID+"'";
		try {
			 stmt=conn.createStatement();
			 stmt.executeUpdate(sql2);
			 rst=stmt.executeQuery("select * from memberToRole");
//			 while(rst.next()){
//				 loginID=rst.getString("loginID");
//				 System.out.println(loginID);
//				 
//			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public String getRoleIDFromRoleM(String roleName){		
		String roleID="";
		String sql="select roleID from role where roleName ='"+roleName+"'";
		try {
			 stmt=conn.createStatement();
			 rst=stmt.executeQuery(sql);
			 while(rst.next()){
				 roleID=rst.getString("roleID");
//				 System.out.println(roleName);
				 
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return roleID;
	}
	
	public Vector getPowerID(String roleID){
		Vector v=new Vector();
		String sql="select powerID from roleToPower where roleID ='"+roleID+"'";
		try {
			stmt=conn.createStatement();
			rst=stmt.executeQuery(sql);
			while(rst.next()){
				v.add(rst.getString("powerID"));
//				System.out.println("##"+rst.getString("powerID")+"##");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return v;
	}
	
	public Vector getPower(String loginID){
		String id=this.getRoleID(loginID);
		Vector vc=this.getPowerID(id);
		Vector v=new Vector();
		String powerID="";
//		String sql="select powerName from power where powerID ='"+powerID+"'";
		try {
			for(int ii=0;ii<vc.size();ii++){
				powerID=vc.elementAt(ii).toString();
				String sql="select powerName from power where powerID ='"+powerID+"'";
				stmt=conn.createStatement();
				rst=stmt.executeQuery(sql);
				if(rst.next()){
					v.add(rst.getString("powerName"));
//					System.out.println("##"+rst.getString("powerName")+"##");
				}
			} 
		}catch (SQLException e) {
			e.printStackTrace();
		}
			
		return v;
	}
	
	public Vector getAllPower(){
		Vector vc=new Vector();
		try {
			stmt=conn.createStatement();
			rst=stmt.executeQuery("selete * from power");
			while(rst.next()){
				vc.add(rst.getString("powerName"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return vc;
	}
	
	public Vector getRolePower(Vector vPowerID){
		Vector v=new Vector();
		String powerID="";
//		String sql="select powerName from power where powerID ='"+powerID+"'";
		try {
			for(int ii=0;ii<vPowerID.size();ii++){
				powerID=vPowerID.elementAt(ii).toString();
				String sql="select powerName from power where powerID ='"+powerID+"'";
				stmt=conn.createStatement();
				rst=stmt.executeQuery(sql);
				if(rst.next()){
					v.add(rst.getString("powerName"));
//					System.out.println("##"+rst.getString("powerName")+"##");
				}
			} 
		}catch (SQLException e) {
			e.printStackTrace();
		}
			
		return v;
	}
	public Vector getBug(String loginID){
		Vector v=new Vector();
		String sql="select bugID,description from bug where bugOwner ='"+loginID+"'";
		try {
			stmt=conn.createStatement();
			rst=stmt.executeQuery(sql);
			while(rst.next()){
				v.add(new Bug(rst.getString("bugID"),rst.getString("description")));
//				System.out.println("##"+rst.getString("powerID")+"##");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return v;
	}
	
	public Vector getMemberID(String roleID){
		Vector v=new Vector();
		String sql="select loginID from memberToRole where roleID='"+roleID+"'";
		try {
			stmt=conn.createStatement();
			rst=stmt.executeQuery(sql);
			while(rst.next()){
				v.add(rst.getString("loginID"));
//				System.out.println("##"+rst.getString("loginID")+"##");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return v;
	}
	
	public void addMember(String iD,String email,String password,String address,String name){
		String sql="insert into member(loginID,email,password,address,name)" +
				" values('"+iD+"','"+email+"','"+password+"','"+address+"','"+name+"')";
		try {
			stmt=conn.createStatement();
			stmt.executeUpdate(sql);
			rst = stmt.executeQuery("select * from member");
//			while(rst.next()){
//				System.out.println("##"+rst.getString("loginID")+"##"+rst.getString("name"));
//			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
	}
	
	public void addMemberToRole(String loginID,String roleID){
		String sql="insert into memberToRole(loginID,roleID) values('"+loginID+"','"+roleID+"')";
		try {
			stmt=conn.createStatement();
			stmt.executeUpdate(sql);
			rst = stmt.executeQuery("select * from memberToRole");
//			while(rst.next()){
//				System.out.println("##"+rst.getString("loginID")+"##"+rst.getString("name"));
//			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void addPower(String roleID,String powerID){
		String sql="insert into power(roleID,powerID) values('"+roleID+"','"+powerID+"')";
		try {
			stmt=conn.createStatement();
			stmt.executeUpdate(sql);
			rst = stmt.executeQuery("select * from power");
//			while(rst.next()){
//				System.out.println("##"+rst.getString("roleID")+"##"+rst.getString("powerID"));
//			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void deletePower(String roleID,String powerID){
		String sql="delete from power(roleID,powerID) where roleID='"+roleID+"' and powerID='"+powerID+"';";
		try {
			stmt=conn.createStatement();
			stmt.executeUpdate(sql);
			rst = stmt.executeQuery("select * from power");
//			while(rst.next()){
//				System.out.println("##"+rst.getString("roleID"));
//			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void setRole(String loginID,String roleID){
		String sql="update memberToRole set roleID='"+roleID+"' where loginID='"+loginID+"'";
		try {
			stmt=conn.createStatement();
			stmt.executeUpdate(sql);
			rst=stmt.executeQuery("select * from memberToRole where loginID='"+loginID+"'");
			while(rst.next()){
				System.out.println(rst.getString("loginID")+"#"+rst.getString("roleID"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
	
	public void changeInfo(String loginID,String email,String password,String address,String name){
		String sql="update member set email='"+email
			+"',password='"+password+"',address='"+address+"',name='"+name+"'" +
			" where loginID='"+loginID+"'";
		try {
			stmt=conn.createStatement();
			stmt.executeUpdate(sql);
			rst=stmt.executeQuery("select * from member where loginID='"+loginID+"'");
			while(rst.next()){
				System.out.println(rst.getString("email")+"#"+rst.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
	
	public Vector viewMember(String roleID){
		Vector vRst=new Vector();
		if(roleID.equals("role3")){
			Vector vm=this.getMemberID("role1");
			for(int ii=0;ii<vm.size();ii++){
				String loginID=(String)vm.elementAt(ii);
				try {
					stmt=conn.createStatement();
					rst=stmt.executeQuery("select loginID,email,address,name from " +
							"member where loginID='"+loginID+"'");
					if(rst.next()){
						vRst.add(new Member(rst.getString("loginID"),rst.getString("email"),
								rst.getString("address"),rst.getString("name")));
//						System.out.println("##"+rst.getString("loginID")+"##");
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			return vRst;
		}else if(roleID.equals("role4")){
			Vector vm=this.getMemberID("role2");
			for(int ii=0;ii<vm.size();ii++){
				String loginID=(String)vm.elementAt(ii);
				try {
					stmt=conn.createStatement();
					rst=stmt.executeQuery("select loginID,email,address,name from " +
							"member where loginID='"+loginID+"'");
					if(rst.next()){
						vRst.add(new Member(rst.getString("loginID"),rst.getString("email"),
								rst.getString("address"),rst.getString("name")));
//						System.out.println("##"+rst.getString("loginID")+"##");
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			return vRst;
		}else if(roleID.equals("role5")){
			String sql="select loginID,email,address,name from member";
			try {
				stmt=conn.createStatement();
				rst=stmt.executeQuery(sql);
				while(rst.next()){
					vRst.add(new Member(rst.getString("loginID"),rst.getString("email"),
							rst.getString("address"),rst.getString("name")));
//					System.out.println("##"+rst.getString("loginID")+"##");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return vRst;
		}else{
			return null;
		}
	}
	
//	public static void main(String args[]){
//		UserBean ub=new UserBean();	
//		ub.setRole("member111", "role5");
//		Vector v=ub.viewMember("role3");
//		ub.changeInfo("member111", "xx", "xx", "xx", "xx");
//		String str=ub.getPwd("169");
//		ub.getName("member111");
//		ub.getRoleID("member111");
//		ub.getRoleName(str);
//		Vector v=ub.getPowerID(ub.getRoleID("member111"));
//		for(int kk=0;kk<v.size();kk++){
//			System.out.println("##"+v.elementAt(kk)+"##");
//		}
//		Vector v=ub.getPower("member119");
//		for(int kk=0;kk<v.size();kk++){
//			Member mm=(Member)v.elementAt(kk);
//			System.out.println("##"+mm.getName()+"##");
//		}
//		String roleID=ub.getRoleIDFromRoleM("tester");
//		System.out.println("##"+roleID);
//	}
}