database.java

Oracle源码：Oracle 数据库管理与应用系统开发代码

package oa.main;

import java.util.*;
import java.text.*;
import java.sql.*;
import java.io.*;
import java.lang.*;
import javax.naming.*;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.*;
public class DataBase extends Object
{
	Connection conn = null;
	Statement stm = null;
	PreparedStatement pstm = null;
	ResultSet rs = null;
	public static int this_sumnum = 0 ;//连接池之外的计数方法
	public static int this_curnum = 0 ;//连接池之外的计数方法

	boolean connected = false;

	/**构造函数*/
	public DataBase()
	{
	}

	public static void showConnNUM()
	{
		if(Configuration.DB_LOGLEVEL.equals("2")||Configuration.DB_LOGLEVEL.equals("4"))
		{
			//连接池中使用
			DealString ds = new DealString();
			int curnum = Configuration.connMgr.getCurConns(Configuration.ConnectionPoolName);
			int sumnum = Configuration.connMgr.getSumConns(Configuration.ConnectionPoolName);
			int maxnum = Configuration.connMgr.getMaxConns(Configuration.ConnectionPoolName);
			int isNullPool = Configuration.connMgr.getNullPool(Configuration.ConnectionPoolName);
			int isOracleErr = Configuration.connMgr.getOracleErr(Configuration.ConnectionPoolName);
			if(!Configuration.ConnectionMode.equals("syspool"))
			{
				sumnum = this_sumnum;
				curnum = this_curnum;
			}
			System.out.print("\r" + ds.getDateTime ( ).substring ( 11 , 19 ) + "  连接累计> "+ds.toLengthStrRight(sumnum+"",10)+"当前连接> "+ds.toLengthStrRight(curnum+"",10)+"池中连接> "+ds.toLengthStrRight(maxnum+"",10)+"池空未连接> " +ds.toLengthStrRight(isNullPool+"",10)+"DB未响应> " + isOracleErr + "  ");
		}	
	}

	/**取得连接池*/
	public void getConnPool()
	{
		try
		{
			if(connected){
				throw new SQLException("数据库已连接，无须重连！");
			}
			Context initCtx = new InitialContext(); 
			
			
			Context ctx = (Context) initCtx.lookup("java:comp/env"); 

			/**获取连接池对象*/
			Object obj = (Object) ctx.lookup("jdbc/OracleDB"); 
			//////////////////////tmaxtest////////////////////////////////////////////////////////
		//	Context initCtx = new InitialContext();
		//	javax.sql.DataSource ds = (javax.sql.DataSource)initCtx.lookup("jdbc/OracleDB");
      //////////////////////tmaxtest////////////////////////////////////////////////////////
			/**类型转换*/
			javax.sql.DataSource ds = (javax.sql.DataSource)obj; 
			conn = ds.getConnection();

			/**测试连接计数*/
			this_sumnum++;
			this_curnum++;
			connected = true;
			showConnNUM();
		}
		catch(NamingException e){Logger.log(e,"从数据池取得数据库连接时出错;\r\n错误为:");}
		catch(SQLException e2){Logger.log(e2,"从数据池取得数据库连接时出错;\r\n错误为:");}
	}

	/**创建我的连接池*/
	public boolean getMyConnPool()
	{
		conn = Configuration.connMgr.getConnection(Configuration.ConnectionPoolName);
		showConnNUM();
		if(conn == null)
		{
			return false;
		}
		else
		{
			return true;
		}
	}

	/**释放我的连接池*/
	public boolean releaseMyConnPool()
	{
		boolean b;
		if ( conn !=null )
		{
			b = true;
		}
		else
		{
			b = false;
		}
		Configuration.connMgr.freeConnection(Configuration.ConnectionPoolName, conn);
		conn = null ;
		showConnNUM();
		return b;
	}

	/**生成Oracle SQLServer等的连接*/
	public void createConn(String drv,String url,String usr,String pwd)
	{
		try
		{
			if(connected){
				throw new SQLException("数据库已连接，无须重连！");
			}
			Class.forName(drv).newInstance();
			conn = DriverManager.getConnection(url,usr,pwd);
			
			this_sumnum++;
			this_curnum++;
			connected = true;

			/**测试连接计数*/
			showConnNUM();
		}
		catch(ClassNotFoundException ec){Logger.log(ec,"从自身建立数据库连接时出错;\r\n错误为:");}
		catch(SQLException e){Logger.log(e,"从自身建立数据库连接时出错;\r\n错误为:");}
		catch(Exception et){Logger.log(et,"从自身建立数据库连接时出错;\r\n错误为:");}
	}

	/**生成Access连接*/
	public void createConn(String drv,String url)
	{
		try
		{
			if(connected){
				throw new Exception("数据库已连接，无须重连！");
			}
			Class.forName(drv).newInstance();
			conn = DriverManager.getConnection(url);
			this_sumnum++;
			this_curnum++;
			connected = true;

			/**测试连接计数*/
			showConnNUM();
		}
		catch(ClassNotFoundException ec){Logger.log(ec,"从自身建立数据库连接时出错;\r\n错误为:");}
		catch(SQLException e){Logger.log(e,"从自身建立数据库连接时出错;\r\n错误为:");}
		catch(Exception et){Logger.log(et,"从自身建立数据库连接时出错;\r\n错误为:");}
	}

	/**释放数据库连接*/
	public void releaseConn()
	{
		try
		{
			if(!connected){
				//throw new SQLException("数据库未连接！");
			}
			if(conn!=null)
			{
				conn.close();
				this_curnum--;
				connected = false;

				/**测试连接计数*/
				showConnNUM();
			}
		}
		catch(SQLException e){Logger.log(e,"关闭数据库连接时出错;\r\n错误为:");}
	}	

	/**查询记录*/
	public ResultSet QuerySQL(String sql)
	{
		ResultSet rs = null;
		try
		{
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
		}catch(SQLException sqle){
			Logger.log(sqle,"执行DataBase::QuerySQL(String)调用SQL语句 "+sql+" 时出错;\r\n错误为:");
			if(pstm!=null)
			try{pstm.close();}catch(Exception e){Logger.log(e,"执行DataBase::QuerySQL(String)试图关闭错误的声明时出错;\r\n错误为：");}
		}
		return rs;
	}

	/**执行增删改的语句*/
	public int ExecuteSQL(String sql)
	{
		try
		{
			pstm = conn.prepareStatement(sql);
			pstm.executeUpdate();
			conn.commit();
		}
		catch(SQLException sqle)
		{
			//System.out.println("执行DataBase::ExecuteSQL(String)调用SQL语句 "+sql+" 时出错;\r\n错误为:");
			return sqle.getErrorCode();
		}
		finally{
			try{
			pstm.close();
			}catch(SQLException sqle){Logger.log(sqle,"执行DataBase::ExecuteSQL(String)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
		}
		return 0;
	}

	/**产生唯一编号*/
 	public int makeID(String table,String field1,String field2,String value1,boolean type1)
	{
		int out = -1;
		String sql = "";
		try
		{
					//只有唯一主键field1
			sql = "select "+field1+" as ID from "+table+" order by "+field1;
					//有两个主键field1、field2
			if(!value1.equals(""))//当第一个字段不空时，作为条件查询第二个字段
			{
				sql = "select "+field2+" as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
				if(!type1)       //是字符串时 将type1设为false
					sql = "select "+field2+" as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
			}
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			int t1 = 1;
			int t2 = 2;
			if(rs.next())  //有记录
			{
				t1 = rs.getInt("ID");
				out = t1;
				boolean bool = false;
				while(rs.next())   //不止一条纪录
				{
					bool = true;
					t2 = rs.getInt("ID");
					if((t2-t1)>1)break;  //如果t2与t1相差大于1,则跳出去,新编号为t1++（见后面**）
					t1 = t2;			//否则将t2赋给t1
				}
				if(!bool)  //如果只有一条纪录
				{
					if(t1>1)t1 = 1;  //如果已有纪录的ID号大于1，则新编号设为1
					else t1++;
				}
				else t1++;		//**
			}
			if(out>1)out = 1;
			else out = t1;

		}catch(SQLException sqle){Logger.log(sqle,"执行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
		}
		return out;
	}

	/**产生唯一编号*/
 	public int makeID_Add1(String table,String field1,String field2,String value1,boolean type1)
	{
		int out = -1;
		String sql = "";
		try
		{
					//只有唯一主键field1
			sql = "select max("+field1+")+1 as ID from "+table+" order by "+field1;
					//有两个主键field1、field2
			if(!value1.equals(""))//当第一个字段不空时，作为条件查询第二个字段
			{
				sql = "select ("+field2+")+1 as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
				if(!type1)       //是字符串时 将type1设为false
					sql = "select ("+field2+")+1 as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
			}
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			if(rs.next())  //有记录
			{
				out = rs.getInt(1);
			}
		}catch(SQLException sqle){Logger.log(sqle,"执行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
		}
		return out;
	}

 	public int makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)
	{
		int out = -1;
		String sql = "";
		try
		{
					//只有唯一主键field1
			sql = "select "+field1+" as ID from "+table+" order by "+field1;
					//有两个主键field1、field2
			if(!value1.equals(""))//当第一个字段不空时，作为条件查询第二个字段
			{
				sql = "select "+field2+" as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
				if(!type1)       //是字符串时 将type1设为false
					sql = "select "+field2+" as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
			}
			if(!value2.equals(""))//当第二个字段不空时，作为条件查询第三个字段
			{
				sql = "select "+field3+" as ID from "+table+" where "+field1+"="+value1+" and "+field2+"="+value2+" order by "+field3;
				if(!type2)       //是字符串时 将type1设为false
					sql = "select "+field3+" as ID from "+table+" where "+field1+"='"+value1+"' and "+field2+"='"+value2+"' order by "+field3;
			}
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			int t1 = 1;
			int t2 = 2;
			if(rs.next())  //有记录
			{
				t1 = rs.getInt("ID");
				out = t1;
				boolean bool = false;
				while(rs.next())   //不止一条纪录
				{
					bool = true;
					t2 = rs.getInt("ID");
					if((t2-t1)>1)break;  //如果t2与t1相差大于1,则跳出去,新编号为t1++（见后面**）
					t1 = t2;			//否则将t2赋给t1
				}
				if(!bool)  //如果只有一条纪录
				{
					if(t1>1)t1 = 1;  //如果已有纪录的ID号大于1，则新编号设为1
					else t1++;
				}
				else t1++;		//**
			}
			if(out>1)out = 1;
			else out = t1;
		}catch(SQLException sqle){Logger.log(sqle,"执行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
		}
		return out;
	}

	public int makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)
	{
		int out = -1;
		String sql = "";
		try
		{
					//只有唯一主键field1
			sql = "select max("+field1+") as ID from "+table+" order by "+field1;
					//有两个主键field1、field2
			if(!value1.equals(""))//当第一个字段不空时，作为条件查询第二个字段
			{
				sql = "select max("+field2+") as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
				if(!type1)       //是字符串时 将type1设为false
					sql = "select max("+field2+") as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
			}
			if(!value2.equals(""))//当第二个字段不空时，作为条件查询第三个字段
			{
				sql = "select max("+field3+") as ID from "+table+" where "+field1+"="+value1+" and "+field2+"="+value2+" order by "+field3;
				if(!type2)       //是字符串时 将type1设为false
					sql = "select max("+field3+") as ID from "+table+" where "+field1+"='"+value1+"' and "+field2+"='"+value2+"' order by "+field3;
			}
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			if(rs.next())  //有记录