dbutility.java

web应用中

/***********************************************************************
 * Module:  DBUtility.java
 * Author:  Wang Jianan
 * Purpose: Defines the JavaBean DBUtility
 ***********************************************************************/
package PPTCWEB;

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

public class DBUtility {
	/** 驱动 */
	private String driver = "com.sybase.jdbc2.jdbc.SybDriver";
	/** 用户名 */
	private String userID = "pptcweb";
	/** 密码 */
	private String password = "pptcweb";
	/** JDBC URL */
	private String url = "jdbc:sybase:Tds:150.1.1.10:8888/pptcweb?";

	/** Connection */
	public Connection connection;
	/** Statement */
	public Statement statement;
	/** Result 结果集 */
	public ResultSet results;

	/** 查询的结果集,Vector中的每个元素是一个project */
	private Vector sqlVector;
	
	/** 查询结果集的列数 */
	private int columnCount;
	/** 查询结果集的行数 */
	private int rowCount;
	/** 查询语句 */
	private String querySql;
	/** 插入语句 */
	private String insertSql;
	/** 删除语句 */
	private String deleteSql;
	/** 更新语句 */
	private String updateSql;
	
	/** 结果集的列名 */
	private String[] columnsNames;

	public DBUtility() 
	{
		
	}

	 /** Returns the driver */
	public String getDriver()
	{
		return driver;
	}
	
	/** @param driver */
	public void setDriver(String driver)
	{
		this.driver = driver;
	}

	 /** Returns the userID */
	public String getUserID()
	{
		return userID;
	}

	/** @param userID */
	public void setUserID(String userID)
	{
		this.userID = userID;
	}

	 /** Returns the passWord */
	public String getPassword()
	{
		return password;
	}

	/** @param passWord */
	public void setPassword(String password)
	{
		this.password = password;
	}

	 /** Returns the url */
	public String getUrl()
	{
		return url;
	}

	/** @param url */
	public void setUrl(String url)
	{
		this.url = url;
	}

	 /** Returns the columnCount */
	public int getColumnCount()
	{
		// return columnCount;
		if (columnsNames == null)
			return 0;
		else
			return columnsNames.length;
	}

	/** @param columnCount */
	public void setColumnCount(int columnCount)
	{
		this.columnCount = columnCount;
	}

	 /** Returns the rowCount */
	public int getRowCount()
	{
		// return rowCount;
		if (sqlVector == null)
			return 0;
		else
			return sqlVector.size();
	}

	/** @param rowCount */
	public void setRowCount(int rowCount)
	{
		this.rowCount = rowCount;
	}

	 /** Returns the querySql */
	public String getQuerySql()
	{
		return querySql;
	}

	/** @param querySql */
	public void setQuerySql(String querySql)
	{
		this.querySql = querySql;
	}
	
	 /** Returns the insertSql */
	public String getInsertSql()
	{
		return insertSql;
	}

	/** @param querySql */
	public void setInsertSql(String insertSql)
	{
		this.insertSql = insertSql;
	}
	
	 /** Returns the deleteSql */
	public String getDeleteSql()
	{
		return deleteSql;
	}

	/** @param deleteSql */
	public void setDeleteSql(String deleteSql)
	{
		this.deleteSql = deleteSql;
	}
	
	 /** Returns the updateSql */
	public String getUpdateSql()
	{
		return updateSql;
	}

	/** @param updateSql */
	public void setUpdateSql(String updateSql)
	{
		this.updateSql = updateSql;
	}
	 /** Returns the columnsNames */
	public String[] getColumnsNames()
	{
		return columnsNames;
	}

	/** @param columnsNames */
	public void setColumnsNames(String[] columnsNames)
	{
		this.columnsNames = columnsNames;
	}
	
	 /** Returns the sqlVector */
	public Vector getSqlVector()
	{
		return sqlVector;
	}
	
	/** @param sqlVector */
	public void setSqlVector(Vector sqlVector)
	{
		this.sqlVector = sqlVector;
	}
	
	/** 通过查询语句从数据库中获取项目的结果集*/
	public Vector SelectSQL(String querySql)
	{
		if (connection == null)
			openConnection();
		executeSQLProject(querySql);
		return sqlVector;
	}
	
	/** 通过查询语句从数据库中获取用户信息的结果集*/
	public Vector SelectUserInfo(String querySql)
	{
		if (connection == null)
			openConnection();
		executeSQLUserInfo(querySql);
		return sqlVector;
	}
	
	/** 通过查询语句从数据库中获取用户单位信息的结果集*/
	public Vector SelectDeptInfo(String querySql)
	{
		if (connection == null)
			openConnection();
		executeSQLDeptInfo(querySql);
		return sqlVector;
	}
	
	/** 对数据库进行插入操作 */
	public int Insert(String insertSql)
	{
		if (connection == null)
			openConnection();
	    return executeInsert(insertSql);
	}
	
	/** 对数据库进行删除操作 */
	public int Delete(String deleteSql)
	{
		if (connection == null)
			openConnection();
		return executeDelete(deleteSql);
	}
	
	/** 对数据库进行更新操作 */
	public int Update(String updateSql)
	{
		if (connection == null)
			openConnection();
		return executeUpdate(updateSql);			
	}

	/** 打开与数据库的连接 */
	public void openConnection()
	{
		try 
		{
			Class.forName(driver);
			connection = DriverManager.getConnection(url, userID, password);
			statement = connection.createStatement();
		} 
		catch (ClassNotFoundException cnfe) 
		{
			cnfe.printStackTrace();
		} 
		catch (SQLException sqle) 
		{
			sqle.printStackTrace();
		}
	}

	/** 执行项目查询语句 */
	private Vector executeSQLProject(String querySql)
	{
		if (querySql == null)
			sqlVector = null;
		else 
		{
			try 
			{
				sqlVector = setProjectResultSet(statement.executeQuery(querySql));
			} 
			catch (SQLException sqle)
			{
				sqle.printStackTrace();
				sqlVector = null;
			}
		}
		return sqlVector;
	}
	
	/** 执行用户信息查询语句 */
	private Vector executeSQLUserInfo(String querySql)
	{
		if (querySql == null)
			sqlVector = null;
		else 
		{
			try 
			{
				sqlVector = setUserInfoResultSet(statement.executeQuery(querySql));
			} 
			catch (SQLException sqle)
			{
				sqle.printStackTrace();
				sqlVector = null;
			}
		}
		return sqlVector;
	}
	
	/** 执行用户单位信息查询语句 */
	private Vector executeSQLDeptInfo(String querySql)
	{
		if (querySql == null)
			sqlVector = null;
		else 
		{
			try 
			{
				sqlVector = setDeptInfoResultSet(statement.executeQuery(querySql));
			} 
			catch (SQLException sqle)
			{
				sqle.printStackTrace();
				sqlVector = null;
			}
		}
		return sqlVector;
	}
	
	/** 执行插入语句 */
	private int executeInsert(String insertSql)
	{
		try
		{
			return statement.executeUpdate(insertSql);
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
			return 0;
		}
	}
	
	/** 执行删除语句 */
	private int executeDelete(String deleteSql)
	{
		try
		{
			return statement.executeUpdate(deleteSql);
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
			return 0;
		}
	}
	
	/** 执行更新语句 */
	private int executeUpdate(String updateSql)
	{
		try
		{
			return statement.executeUpdate(updateSql);
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
			return 0;
		}
	}

	/** 关闭与数据库的连接 */
	public void colseConnection()
	{
		try
		{	
			if (results != null)
				results.close();
			if (statement != null)
			    statement.close();
		    if (connection != null)
			    connection.close();
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
		}
	}

	/** 设置项目查询的结果集 */
	private Vector setProjectResultSet(ResultSet results)throws SQLException 
	{
		ResultSetMetaData rsmetadata = results.getMetaData();
		Project proj;
		int columns = rsmetadata.getColumnCount();
	    setColumnCount(columns);
		setRowCount(results.getRow());
		columnsNames = new String[columns];
		for (int i = 0; i < columns; i++)
			columnsNames[i] = rsmetadata.getColumnLabel(i + 1);

		sqlVector = new Vector();

		while (results.next())
		{
            proj = new Project();
            
	        proj.setKyC(results.getString("ky_c"));
	        proj.setYear(results.getString("year"));
	        proj.setNumber(results.getInt("number"));
	        proj.setFromdept(results.getString("fromdept"));
	        proj.setRepman(results.getString("repman"));
	        proj.setTime(results.getString("time"));
	        proj.setItemid(results.getString("itemid"));
	        proj.setItemname(results.getString("itemname"));
	        proj.setExedept(results.getString("exedept"));
	        proj.setBranch1(results.getString("branch1"));
	        proj.setBranch2(results.getString("branch2"));
	        proj.setKind(results.getString("kind"));
	        proj.setTotalinvest(results.getFloat("totalinvest"));
	        proj.setUnit(results.getString("unit"));
	        proj.setAmount(results.getString("amount"));
	        proj.setLastinvest(results.getFloat("lastinvest"));
	        proj.setInvest(results.getFloat("invest"));
	        proj.setItemstatus(results.getString("itemstatus"));
	        proj.setAdjustproj(results.getString("adjustproj"));
	        proj.setAttachnote(results.getString("attachnote"));
	        proj.setPassnote(results.getString("passnote"));
	        proj.setPassflag(results.getString("passflag"));
	        proj.setLittleflag(results.getString("littleflag"));
	        
            sqlVector.add(proj);
		}
		return sqlVector;
	}
	
	/** 设置用户信息查询的结果集 */
	private Vector setUserInfoResultSet(ResultSet results)throws SQLException 
	{
		ResultSetMetaData rsmetadata = results.getMetaData();
		UserInfo userinfo;
		int columns = rsmetadata.getColumnCount();
	    setColumnCount(columns);
		setRowCount(results.getRow());
		columnsNames = new String[columns];
		for (int i = 0; i < columns; i++)
			columnsNames[i] = rsmetadata.getColumnLabel(i + 1);

		sqlVector = new Vector();

		while (results.next())
		{
			userinfo = new UserInfo();
			
			userinfo.setKyC(results.getString("ky_c"));
			userinfo.setUserid(results.getInt("userid"));
			userinfo.setUsername(results.getString("username"));
			userinfo.setPassword(results.getString("password"));
			userinfo.setDeptname(results.getString("deptname"));
			userinfo.setLinkman(results.getString("linkname"));
			userinfo.setTelphone(results.getString("telephone"));
			userinfo.setUserrole(results.getString("userrole"));
			userinfo.setRegdate(results.getString("regadate"));
			userinfo.setEnabled(results.getString("enabled"));
	        
            sqlVector.add(userinfo);
		}
		return sqlVector;
	}
	
	/** 设置用户信息查询的结果集 */
	private Vector setDeptInfoResultSet(ResultSet results)throws SQLException 
	{
		ResultSetMetaData rsmetadata = results.getMetaData();
		DeptInfo deptinfo;
		int columns = rsmetadata.getColumnCount();
	    setColumnCount(columns);
		setRowCount(results.getRow());
		columnsNames = new String[columns];
		for (int i = 0; i < columns; i++)
			columnsNames[i] = rsmetadata.getColumnLabel(i + 1);

		sqlVector = new Vector();

		while (results.next())
		{
			deptinfo = new DeptInfo();
			
			deptinfo.setKyC(results.getString("kyC"));
			deptinfo.setDeptcode(results.getInt("deptcode"));
			deptinfo.setDeptname(results.getString("deptname"));
			deptinfo.setDeptkind(results.getString("deptkind"));
			deptinfo.setAddress(results.getString("address"));
			deptinfo.setPostcode(results.getString("postcode"));
			        
            sqlVector.add(deptinfo);
		}
		return sqlVector;
	}
}