sqlbean.java

为解决项目研发过程中Bug管理混乱、不科学以及中小型企业Bug管理过程中出现考核标准错误、规章制度过严等问题

package core;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class SQLBean {
	
	private Vector userList;	// 向量数组
	
	java.sql.PreparedStatement pstmt = null;
    private Connection conn = null;
    private ResultSet rs = null;
    private String driverName = 
    	"com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private String url = 
		"jdbc:sqlserver://localhost:1433;" +
		   "databaseName=BugMgr;user=sa;password=123";
    
	public SQLBean() {
		try
		{
			Class.forName(driverName).newInstance();
			conn = DriverManager.getConnection(url);
	   	}
		catch(SQLException e){	System.out.println(e.getMessage()); }
		catch(Exception ex){ System.out.println(ex.getMessage());}

	}
	
	public Vector getUserList()
	{
		return userList;
	}

	// 管理人员登陆验证
    public UserBean checkUsersLogin(String userName, String userPwd)
    {
        UserBean ub = null;
        if (!checkParameter(userName + userPwd))
        {
            userName = "null";
            userPwd = "null";
        }
        try
        {
            String sql =
                "select count(*) from manager where mana_username=? and mana_password=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userName);
            pstmt.setString(2, userPwd);
            rs = pstmt.executeQuery();
            if (rs.next())
            {
                if (rs.getInt(1) > 0)
                {
                    ub = this.getUser(userName);
                }
                else
                {
                    ub = null;
                }
            }
        }
        catch (Exception e)
        {
            ub = null;
            e.printStackTrace();
        }
        return ub;
    }

    public boolean checkParameter(String para) //过滤非法字符
    {
        int flag = 0;
        flag += para.indexOf("'") + 1;
        flag += para.indexOf(";") + 1;
        flag += para.indexOf("1=1") + 1;
        flag += para.indexOf("|") + 1;
        flag += para.indexOf("<") + 1;
        flag += para.indexOf(">") + 1;
        if (flag != 0)
        {
            System.out.println("提交了非法字符!!!");
            return false;
        }
        return true;
    }
    
    // 提取登陆管理员用户信息
    public UserBean getUser(String userName)
    {
        UserBean ub = new UserBean();
        String sql = "select * from manager where mana_username=?";
        try
        {
        	pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userName);
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                ub.setUserName(rs.getString("mana_username"));
                ub.setPassword(rs.getString("mana_password"));
                ub.setUserId(rs.getString("mana_id"));
                ub.setTrueName(rs.getString("mana_name"));
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }

        return ub;
    }
    
    // 提取所有项目人员信息
    public boolean getItemPerson()
    {
        String sql = "select * from ItemPerson";
        int rscount = 0;
        try
        {
        	pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rscount = pstmt.getMaxRows();
            userList = new Vector(rscount+1);
            userList.clear();
            while (rs.next())
            {
            	String s1 = rs.getString("pers_username");
            	String s2 = rs.getString("pers_name");
            	String s3 = rs.getString("role_id");// 不是"role_name"
            	IPUser user = new IPUser(s1,s2,s3);
            	user.setIP_id(rs.getString("pers_id"));
            	userList.addElement(user);
            }
            rs.close();
            return true;
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
            return false;
        }
    }
    
    // 项目人员登录验证
    public IPUser checkIPUserLogin(String userName, String userPwd)
    {
        IPUser user = null;
        if (!checkParameter(userName + userPwd))
        {
            userName = "null";
            userPwd = "null";
        }
        try
        {
            String sql =
                "select count(*) from ItemPerson where pers_username=? and pers_password=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userName);
            pstmt.setString(2, userPwd);
            rs = pstmt.executeQuery();
            if (rs.next())
            {
                if (rs.getInt(1) > 0)
                {
                	user = this.getItemPerson(userName);
                }
                else
                {
                    user = null;
                }
            }
        }
        catch (Exception e)
        {
            user = null;
            e.printStackTrace();
        }
        return user;
    }
    
    // 提取项目人员登录信息
    public IPUser getItemPerson(String userName)
    {
        IPUser user = new IPUser();
        String sql = "select * from ItemPerson where pers_username=?";
        try
        {
        	pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userName);
            rs = pstmt.executeQuery();
            while (rs.next())
            {
            	user.setIP_id(rs.getString("pers_id"));
            	user.setIp_username(rs.getString("pers_username"));
            	user.setIp_name(rs.getString("pers_name"));
            	user.setIp_roleId(rs.getString("role_id"));
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }

        return user;
    }
    
    // 根据项目人员用户名改变其角色
    public void changeRole(String roleId,String username)
    {
    	String sql = "update ItemPerson set role_id=? where pers_username=?";
        try
        {
        	pstmt = conn.prepareStatement(sql);
        	pstmt.setString(1,roleId);
        	pstmt.setString(2, username);
        	pstmt.executeUpdate();
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
        }
    }
    
    // 新建项目人员信息
    public void addIP(String username,String password,String truename,String roleId)
    {
    	String sql = "insert into ItemPerson values (?,?,?,?)";
        try
        {
        	pstmt = conn.prepareStatement(sql);
        	pstmt.setString(1,username);
        	pstmt.setString(2,password);
        	pstmt.setString(3,truename);
        	pstmt.setString(4,roleId);
        	pstmt.execute();
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
        }
    }
    
    // 获取角色信息
    public boolean getRoleInfo()
    {
    	String sql = "select * from Role";
        int rscount = 0;
        try
        {
        	pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rscount = pstmt.getMaxRows();
            userList = new Vector(rscount+1);
            userList.clear();
            while (rs.next())
            {
            	String s1 = rs.getString("role_id");
            	String s2 = rs.getString("role_name");
            	String s3 = rs.getString("role_right");// 不是"role_name"
            	RoleBean role = new RoleBean(s1,s2,s3);
            	userList.addElement(role);
            }
            rs.close();
            return true;
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
            return false;
        }
    }

    // 根据角色 ID获取角色权限
    public String getRight(String roleId)
    {
    	String right = "";
    	
        String sql = "select * from Role where role_id=?";
        try
        {
        	pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, roleId);
            rs = pstmt.executeQuery();
            while (rs.next())
            {
            	right = rs.getString("role_right");
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }

        return right;
    }
    
    // 获取 Bug信息
    public boolean getBug()
    {
    	String sql = "select * from Bug";
        int rscount = 0;
        try
        {
        	pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rscount = pstmt.getMaxRows();
            userList = new Vector(rscount+1);
            userList.clear();
            Bug bug = null;
            
            while (rs.next())
            {
            	String s1 = rs.getString("bug_id");
            	String s2 = rs.getString("bug_state");
            	String s3 = rs.getString("bug_title");// 不是"role_name"
            	String s4 = rs.getString("bug_description");
            	String s5 = rs.getString("pers_id");
            	
            	bug = new Bug(s3,s4,s5,s2,s1);
            	userList.addElement(bug);
            }
            rs.close();
            return true;
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
            return false;
        }
    }
    
    // 根据 bug_id获取bug信息
    public boolean getBug(String id)
    {
    	String sql = "select * from Bug where bug_id=?";
        int rscount = 0;
        try
        {
        	pstmt = conn.prepareStatement(sql);
        	pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            rscount = pstmt.getMaxRows();
            userList = new Vector(rscount+1);
            userList.clear();
            Bug bug = null;
            
            while (rs.next())
            {
            	String s1 = rs.getString("bug_id");
            	String s2 = rs.getString("bug_state");
            	String s3 = rs.getString("bug_title");// 不是"role_name"
            	String s4 = rs.getString("bug_description");
            	String s5 = rs.getString("pers_id");
            	
            	bug = new Bug(s3,s4,s5,s2,s1);
            	userList.addElement(bug);
            }
            rs.close();
            return true;
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
            return false;
        }
    }
    
    //根据 bug属主获取bug信息
    public boolean getPersBug(String persId)
    {
    	String sql = "select * from Bug where pers_id=?";
        int rscount = 0;
        try
        {
        	pstmt = conn.prepareStatement(sql);
        	pstmt.setString(1, persId);
            rs = pstmt.executeQuery();
            rscount = pstmt.getMaxRows();
            userList = new Vector(rscount+1);
            userList.clear();
            Bug bug = null;
            
            while (rs.next())
            {
            	String s1 = rs.getString("bug_id");
            	String s2 = rs.getString("bug_state");
            	String s3 = rs.getString("bug_title");// 不是"role_name"
            	String s4 = rs.getString("bug_description");
            	String s5 = rs.getString("pers_id");
            	
            	bug = new Bug(s3,s4,s5,s2,s1);
            	userList.addElement(bug);
            }
            rs.close();
            return true;
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
            return false;
        }
    }
    
    // 将解决方案更新到数据库
    public void submitSolution(String attention,String bugId)
    {
    	String sql = "update Bug set bug_state=4,bug_attention=? where bug_id=?";
        try
        {
        	pstmt = conn.prepareStatement(sql);
        	pstmt.setString(1, attention);
        	pstmt.setString(2, bugId);
        	pstmt.executeUpdate();
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
        }
    }
    
    // 根据 bugId改变其状态,置为 New
    public void changeState(String state,String bugId)
    {
    	String sql = "update Bug set bug_state=? where bug_id=?";
        try
        {
        	pstmt = conn.prepareStatement(sql);
        	pstmt.setString(1,state);
        	pstmt.setString(2, bugId);
        	pstmt.executeUpdate();
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
        }
    }

    // 根据角色 ID更改其权限
    public void changeRight(String id,String right)
    {
    	String sql = "update Role set role_right=? where role_id=?";
        try
        {
        	pstmt = conn.prepareStatement(sql);
        	pstmt.setString(1,right);
        	pstmt.setString(2, id);
        	pstmt.executeUpdate();
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
        }
    }
    
    // 新建角色权限
    public void addRole(String rolename,String right)
    {
    	String sql = "insert into Role values (?,?)";
        try
        {
        	pstmt = conn.prepareStatement(sql);
        	pstmt.setString(1,rolename);
        	pstmt.setString(2,right);
        	pstmt.execute();
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
        }
    }
    
    // 根据 bug的ID修改标题和内容
    public void editBug(String title,String content,String bugId)
    {
    	String sql = "update Bug set bug_title=?,bug_description=? where bug_id=?";
        try
        {
        	pstmt = conn.prepareStatement(sql);
        	pstmt.setString(1,title);
        	pstmt.setString(2,content);
        	pstmt.setString(3,bugId);
        	pstmt.executeUpdate();
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
        }
    }
    
    // 提交 bug，插入操作需要考虑数据类型，如String和int
    public void bugSubmit(
    		int state,
    		String title,
    		String content,
    		int solution,
    		String attention,
    		String file,
    		int persId)
    {
    	String sql = "insert into Bug values (?,?,?,?,?,?,?)";
        try
        {
        	pstmt = conn.prepareStatement(sql);
        	pstmt.setInt(1,state);
        	pstmt.setString(2,title);
        	pstmt.setString(3,content);
        	pstmt.setInt(4,solution);
        	pstmt.setString(5,attention);
        	pstmt.setString(6,file);
        	pstmt.setInt(7,persId);
        	pstmt.execute();
        }
        catch (SQLException ex)
        {
        	ex.printStackTrace();// important
        }
    }
    
	//关闭resultset、statement和connection
	public void close()  
	{
	  try{	
		if(rs!=null) rs.close();
		if(pstmt!=null) pstmt.close();
		if(conn!=null) conn.close();
	  }catch(SQLException e){ System.err.println(e.getMessage());}
	}
}