database.java

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

			{
				out = rs.getInt("ID");
			}
		}catch(SQLException sqle){Logger.log(sqle,"执行DataBase::makeID_Add1(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_Add1(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_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
		}
		return out;
	}


	/**将名称转换为编号*/
	public int toID(String table,String field1,String field2,String value1)
	{
		int out = -1;
		String sql = "";
		try
		{
			sql = "select "+field2+" from "+table+" where "+field1+"='"+value1+"'";
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			if(rs.next())
			{
				out = rs.getInt(field2);
			}
		}catch(SQLException sqle){Logger.log(sqle,"执行DataBase::toID(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::toID(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::toID(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
		}
		return out;
	}

	/**将编号转换为名称*/
	public String toName(String table,String field1,String field2,String value1)
	{
		String out = "";
		String sql = "";
		try
		{
			sql = "select "+field2+" from "+table+" where "+field1+"='"+value1+"'";
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			if(rs.next())
			{
				out = (new DealString()).toString(rs.getString(field2));
			}
		}catch(SQLException sqle){Logger.log(sqle,"执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
		}
		return out;
	}	

	public Vector getOnePage(String sql,int page,int records)
	{
		return getOnePage(sql,page,records,true);
	}
	/**分页时取得一页的数据量*/
	public Vector getOnePage(String sql,int page,int records,boolean useDic)
	{
		//第一个为总页数*/
		//第二...个为Hashtable*/
		Vector vect = new Vector();
		int zdrecords = records;
		try
		{
			if(useDic){
				String strsql = "select XMMC from CODE_ZDB where trim(ZDMC)='每页显示记录条数'";
				pstm = conn.prepareStatement(strsql);
				rs = pstm.executeQuery();
				if(rs.next())
				{
					zdrecords = Integer.parseInt(rs.getString("XMMC"));
				}
				rs.close();
				pstm.close();
			}
			//查询总页数
		//	pstm.clearBatch();
			pstm = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rs = pstm.executeQuery();
			int rows = 0;
			while(rs.next())
			{
				rows++;
			}
			int sum = rows/zdrecords;
			if(rows%zdrecords!=0||rows==0)sum++;
			vect.add(""+sum);

			rs.close();pstm.close();

			//移到当前行
		//	pstm.clearBatch();
			pstm = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rs = pstm.executeQuery();
			rows = (page-1)*zdrecords;
			rs.absolute(rows+1);
			rs.previous();

			DealString ds = new DealString();

			//查询当前页
			int j=0;
			while(rs.next())
			{
				if(j==zdrecords)break;
				j++;

				ResultSetMetaData rsmd = rs.getMetaData();
				int cols = rsmd.getColumnCount();
				Hashtable hash = new Hashtable();
				for(int i=1;i<=cols;i++)
				{
					String field = ds.toString(rsmd.getColumnName(i));
					String value = ds.toString(rs.getString(i));
					hash.put(field,value);
				}
				vect.add(hash);
			}
		}catch(SQLException sqle){Logger.log(sqle,"DataBase::getOnePage(String,int,int)执行SQL语句 "+sql+" 分页至第 "+page+" 页时出错;错误为:");}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"DataBase::getOnePage(String,int,int)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"DataBase::getOnePage(String,int,int)调用SQL语句 "+sql+" 时出错;\r\n错误为:");}
			}
		}
		return vect;
	}

	public Vector getData(String sql)
	{
		Vector vect = new Vector();
		try
		{
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();

			DealString ds = new DealString();
			ResultSetMetaData rsmd = rs.getMetaData();
			int cols = rsmd.getColumnCount();
			while(rs.next())
			{
				Hashtable hash = new Hashtable();
				for(int i=1;i<=cols;i++)
				{
					String field = ds.toString(rsmd.getColumnName(i));
					String value = ds.toString(rs.getString(i));
					hash.put(field,value);
				}
				vect.add(hash);
			}
		}catch(SQLException sqle){Logger.log(sqle,"执行DataBase::getData(String)执行SQL语句 "+sql+" 时出错;错误为:");}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::getData(String)试图释放rs时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"执行DataBase::getData(String)试图释放pstm时出错;\r\n错误为:");}
			}
		}
		return vect;
	}

	/**为某一个字段进行重新排序*/
	public int setSort(String table,String field1,String field2,String wherestr,String orderstr,boolean b)
	{
		//写入序列号,field2为唯一字段*/
		try
		{
			String sql = "select "+field2+" from "+table;
			if(!wherestr.equals(""))sql += " where "+wherestr;	
			sql += " "+orderstr;

			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			PreparedStatement pstm_t = null;
			int i = 1;
			while(rs.next())
			{
				if(b)//为field2整型
				{
					sql = "update "+table+" set "+field1+"="+i+" where "+field2+"="+rs.getString(1);
				}
				else //为field2字符串
				{
					sql = "update "+table+" set "+field1+"="+i+" where "+field2+"='"+rs.getString(1)+"'";
				}
				pstm_t = conn.prepareStatement(sql);
				pstm_t.executeUpdate();
				i++;							
			}
			
			if(pstm_t!=null)pstm_t.close();	
		}catch(SQLException sqle){Logger.log(sqle,"调用MyDataBase.setSort()函数错误:\r\n");}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"调用MyDataBase.setSort()试图释放rs时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"调用MyDataBase.setSort()试图释放pstm时出错;\r\n错误为:");}
			}
		}
		return 0;
	}

	/**查询CLOB类型值*/
	public String QueryCLOB(String table,String wherestr,String clobfield)
	{
		String out = "";
		try
		{
			String sqlCommand = "select "+clobfield+" from "+table;
			if(!wherestr.equals(""))sqlCommand += " where "+wherestr;			
			pstm = conn.prepareStatement(sqlCommand);
			rs = pstm.executeQuery();
			if(rs.next())
			{
				oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(clobfield);
				if (clob != null)
				{
		            Reader is = clob.getCharacterStream();
		            BufferedReader br = new BufferedReader(is);
		            String s = br.readLine();
		            while (s != null)
					{
		                out += s;
		                s = br.readLine();
					}
				}
			}
			//out = (new DealString()).Replace(out,"\"","\"\"");
		}
		catch(SQLException sqle){Logger.log(sqle,"调用MyDataBase.QueryCLOB()函数错误:\r\n");}
		catch(IOException iosql){Logger.log(iosql,"调用MyDataBase.QueryCLOB()函数错误:\r\n"+iosql);}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"试图释放rs时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"试图释放pstm时出错;\r\n错误为:");}
			}

		}
		return out;
	}

	/**修改CLOB类型值*/
	public int UpdateCLOB(String table,String wherestr,String clobfield,String clobvalue)
	{
		try
		{
			/* 设定不自动提交 */
			boolean defaultCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);

			/* 清空原CLOB对象 */
			String sqlCommand = "UPDATE "+table+" set "+clobfield+"=EMPTY_CLOB()";
			if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
			pstm = conn.prepareStatement(sqlCommand);
			pstm.executeUpdate();

			/* 查询CLOB对象并锁定 */
			sqlCommand = "select "+clobfield+" from "+table;
			if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
			sqlCommand += " for update";
			pstm.clearBatch();
			pstm.addBatch(sqlCommand);
			rs = pstm.executeQuery();
			while(rs.next())
			{
				/* 获取此CLOB对象 */
				oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(clobfield);
				clob.putString(1,clobvalue);

				sqlCommand = "update "+table+" set "+clobfield+" = ? ";
				if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
				PreparedStatement pstm_t = conn.prepareStatement(sqlCommand);
				pstm_t.setClob(1,clob);
				pstm_t.executeUpdate();
				pstm_t.close();
			}
			/* 正式提交 */
			conn.commit();
			conn.setAutoCommit(defaultCommit);
		}
		catch(SQLException sqle)
		{
			Logger.log(sqle,"调用MyDataBase.UpdateCLOB()函数错误:\r\n");
			return sqle.getErrorCode();
		}
		catch(Exception e){Logger.log(e,"Exception:");}
		finally	{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"试图释放rs时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"试图释放pstm时出错;\r\n错误为:");}
			}

		}
		return 0;
	}

	/**查询BLOB类型值*/
	public String QueryBLOB(String table,String wherestr,String blobfield)
	{
		String out = "";
		try
		{
			String sqlCommand = "select "+blobfield+" from "+table;
			if(!wherestr.equals(""))sqlCommand += " where "+wherestr;			
			pstm = conn.prepareStatement(sqlCommand);
			rs = pstm.executeQuery();
			if(rs.next())
			{
				oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(blobfield);
				if (blob != null)
				{
		            InputStream is = blob.getBinaryStream();
					InputStreamReader isw = new InputStreamReader(is);
					BufferedReader br = new BufferedReader(isw);
		            String s = br.readLine();
		            while (s != null)
					{
		                out += s;
		                s = br.readLine();
					}
				}
			}
			out = (new DealString()).Replace(out,"\"","\"\"");
		}
		catch(SQLException sqle){Logger.log(sqle,"调用MyDataBase.QueryBLOB()函数错误:\r\n");}
		catch(IOException iosql){Logger.log(iosql,"调用MyDataBase.QueryBLOB()函数错误:\r\n"+iosql);}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){Logger.log(e,"试图释放rs时出错;\r\n错误为:");}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){Logger.log(e,"试图释放pstm时出错;\r\n错误为:");}
			}
		}
		return out;
	}