database.java

运用java+jsp写的一个新闻发布系统

		            String s = br.readLine();
		            while (s != null)
					{
		                out += s;
		                s = br.readLine();
					}
				}
			}
			out = (new DealString()).Replace(out,"\"","\"\"");
		}
		catch(SQLException sqle){System.out.println("??MyDataBase.QueryBLOB()????:\r\n"+sqle);}
		catch(IOException iosql){System.out.println("??MyDataBase.QueryBLOB()????:\r\n"+iosql);}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){System.out.println("????rs???;\r\n???:"+e);}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){System.out.println("????pstm???;\r\n???:"+e);}
			}
		}
		return out;
	}

	/**??BLOB???*/
	public int UpdateBLOB(String table,String wherestr,String blobfield,String blobvalue)
	{
		try
		{
			/* ??????? */
			boolean defaultCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);
			
			/* ???CLOB?? */
			String sqlCommand = "UPDATE "+table+" set "+blobfield+"=EMPTY_BLOB()";
			if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
			pstm = conn.prepareStatement(sqlCommand);
			pstm.executeUpdate();

			/* ??CLOB????? */
			sqlCommand = "select "+blobfield+" 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.BLOB blob = (oracle.sql.BLOB)rs.getBlob(blobfield);
				BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
				byte b[] = blobvalue.getBytes();
				out.write(b,0,b.length);
			}
			/* ???? */
			conn.commit();
			conn.setAutoCommit(defaultCommit);
		}
		catch(SQLException sqle)
		{
			System.out.println("??MyDataBase.UpdateBLOB()????:\r\n"+sqle);
			return sqle.getErrorCode();
		}
		catch(Exception e){System.out.println("??MyDataBase.UpdateBLOB()??:"+e);}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){System.out.println("??MyDataBase.UpdateBLOB()????rs???;\r\n???:"+e);}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){System.out.println("??MyDataBase.UpdateBLOB()????pstm???;\r\n???:"+e);}
			}
		}
		return 0;
	}

	/**??BLOB???*/
	public String QueryBLOB_JNDI(String table,String wherestr,String blobfield)
	{
		String sql = "select "+blobfield+" from "+table;
		if(!wherestr.equals(""))sql += " where "+wherestr;
		prepareStatement(sql);
		executeQuery();
		next();
		//InputStream is = rs.getBinaryStream("ziduan");
		//return (new DealFile()).readCHStr(is);
		String out  = getObject(blobfield,"BLOB");
		closeRs();
		closePstm();
		return out;
	}
	
	/**??BLOB???*/
	public int UpdateBLOB_JNDI(String table,String wherestr,String blobfield,String blobvalue)
	{

		String sql =  "UPDATE "+table+" set "+blobfield+"=?";
		if(!wherestr.equals(""))sql += " where "+wherestr;
		prepareStatement(sql);
		setObject(1,blobvalue,"BLOB");
		executeUpdate();
		closePstm();
		return 0;
	}

	/**???????????CLOB??*/
	public int clobInsert(String sql,String table,String wherestr,String clobfield,String infile)
	{
		int out = ExecuteSQL(sql);//Insert??
		out = clobModify(table,wherestr,clobfield,infile);
		return out;
	}

	/**??CLOB??(???CLOB?????????????)*/
	public int clobModify(String table,String wherestr,String clobfield,String infile)
	{
		try 
		{
			/* ??????? */
			boolean defaultCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);

			/* ???CLOB????? */
			String sqlCommand = "select "+clobfield+" from "+table;
			if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
			sqlCommand += " for update ";
			pstm = conn.prepareStatement(sqlCommand);
			rs = pstm.executeQuery();

			while (rs.next())
			{
				/* ???CLOB?? */
				oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(clobfield);

				/* ??????? */
				BufferedReader in = new BufferedReader(new FileReader(infile));
				BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
				
				int c;
				while ((c=in.read())!=-1)
				{
					out.write(c);
				}

				in.close();
				out.close();
			}
			rs.close();
			pstm.close();

			/* ???? */
			conn.commit();

			/* ??????? */
			conn.setAutoCommit(defaultCommit);
		}
		catch(SQLException sqle)
		{
			System.out.println("??DataBase.clobModify()????:\r\n"+sqle);
			return sqle.getErrorCode();
		}
		catch(IOException iosql){System.out.println("??DataBase.clobModify()????:\r\n"+iosql);}
		return 0;
	} 

	/**??CLOB??(??CLOB????,???????CLOB??)*/
	public int clobReplace(String table,String wherestr,String clobfield,String infile)
	{
		int out = 0;
		try 
		{
			/* ??????? */
			boolean defaultCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);

			/* ???CLOB?? */
			String sqlCommand = "update "+table+" set "+clobfield+"=EMPTY_CLOB()";
			if(!sqlCommand.equals(""))sqlCommand += " where "+wherestr;
			pstm = conn.prepareStatement(sqlCommand);
			pstm.executeUpdate();
			pstm.close();

			/* ???? */
			conn.commit();

			/* ??????? */
			conn.setAutoCommit(defaultCommit);

			out = clobModify(table,wherestr,clobfield,infile);		
		}
		catch(SQLException sqle)
		{
			System.out.println("??DataBase.clobReplace()????:\r\n"+sqle);
			return sqle.getErrorCode();
		}
		return out;
	}

	/**???????????BLOB??*/
	public int blobInsert(String sql,String table,String wherestr,String blobfield,String infile)
	{
		int out = ExecuteSQL(sql);//Insert??
		out = clobModify(table,wherestr,blobfield,infile);
		return out;
	}

	/**??BLOB??(???BLOB?????????????)*/
	public int blobModify(String table,String wherestr,String blobfield,String infile)
	{
		try 
		{
			/* ??????? */
			boolean defaultCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);

			/* ???BLOB????? */
			String sqlCommand = "select "+blobfield+" from "+table;
			if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
			sqlCommand += " for update ";
			pstm = conn.prepareStatement(sqlCommand);
			rs = pstm.executeQuery();

			while (rs.next())
			{
				/* ???BLOB?? */
				oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(blobfield);

				/* ??????? */
				BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));
				BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());	
				
				int c;
				while ((c=in.read())!=-1) 
				{
					out.write(c);
				}

				in.close();
				out.close();
			}
			rs.close();
			pstm.close();

			/* ???? */
			conn.commit();

			/* ??????? */
			conn.setAutoCommit(defaultCommit);
		}
		catch(SQLException sqle)
		{
			System.out.println("??DataBase.blobModify()????:\r\n"+sqle);
			return sqle.getErrorCode();
		}
		catch(IOException iosql){System.out.println("??DataBase.blobModify()????:\r\n"+iosql);}
		return 0;
	} 

	/**??BLOB??(??BLOB????,???????CLOB??)*/
	public int blobReplace(String table,String wherestr,String blobfield,String infile)
	{
		int out = 0;
		try 
		{
			/* ??????? */
			boolean defaultCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);

			/* ???CLOB?? */
			String sqlCommand = "update "+table+" set "+blobfield+"=EMPTY_BLOB()";
			if(!sqlCommand.equals(""))sqlCommand += " where "+wherestr;
			pstm = conn.prepareStatement(sqlCommand);
			pstm.executeUpdate();
			pstm.close();

			/* ???? */
			conn.commit();

			/* ??????? */
			conn.setAutoCommit(defaultCommit);

			out = blobModify(table,wherestr,blobfield,infile);		
		}
		catch(SQLException sqle)
		{
			System.out.println("??DataBase.blobReplace()????:\r\n"+sqle);
			return sqle.getErrorCode();
		}
		return out;
	}	

	/**数据库信息*/
	public Hashtable getDataBaseInfo()
	{
		Hashtable hash = new Hashtable();
		try
		{
			DatabaseMetaData dmd = conn.getMetaData();
			hash.put("1",dmd.getCatalogSeparator());
			hash.put("2",dmd.getCatalogTerm());
			hash.put("数据库类型名称",dmd.getDatabaseProductName());
			hash.put("数据库版本",dmd.getDatabaseProductVersion());
			hash.put("5",dmd.getDefaultTransactionIsolation()+"");
			hash.put("驱动版本(最大)",dmd.getDriverMajorVersion()+"");
			hash.put("驱动版本(最小)",dmd.getDriverMinorVersion()+"");
			hash.put("驱动名",dmd.getDriverName());
			hash.put("驱动版本",dmd.getDriverVersion());
			hash.put("10",dmd.getExtraNameCharacters());
			hash.put("11",dmd.getIdentifierQuoteString());
			hash.put("12",dmd.getMaxBinaryLiteralLength()+"");
			hash.put("最大行限定",dmd.getMaxRowSize()+"");
			hash.put("方案",dmd.getSchemaTerm());
			hash.put("日期函数",dmd.getTimeDateFunctions());
			hash.put("连接地址",dmd.getURL());
			hash.put("用户名",dmd.getUserName());
		}
		catch(SQLException sqle){System.out.println("调用DataBase.getDataBaseInfo()函数错误:\r\n"+sqle);}
		catch(AbstractMethodError e){System.out.println("调用DataBase.getDataBaseInfo()函数错误:\r\n"+e);}
		return hash;
	}

	/**数据表列表*/
	public Vector getTableList()
	{
		Vector vect = new Vector();
		try
		{
			if(ParentBean.DBType.equals("Access"))
			{
				//ExecuteSQL("create table tab(name char)");
				//ExecuteSQL("insert into tab(name) select name from MsysObjects");
				rs = QuerySQL("select name as TABLE_NAME from tab");
				//rs = QuerySQL("SELECT MSysObjects.Name as TABLE_NAME FROM MsysObjects WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name");
			}
			else
			{
				DatabaseMetaData dmd = conn.getMetaData();
				String[] types = new String[1];
				types[0] = "TABLE";
				//types[1] = "VIEW"
				rs = dmd.getTables(null,ParentBean.DBName.toUpperCase(), "%", types);
			}
System.out.println("000"+getResultSetData(rs));
			while(rs.next())
			{
				vect.add((new DealString()).toString(rs.getString("TABLE_NAME")));
			}
			rs.close();
		}
		catch(SQLException sqle){System.out.println("调用DataBase.getTableList()函数错误:\r\n"+sqle+sqle.getErrorCode());}
		catch(AbstractMethodError e){System.out.println("调用DataBase.getTableList()函数错误:\r\n"+e);}
		return vect;
	}

/**数据表的结构*/
	public Vector getTableStruct(String table)
	{
		Vector vect = new Vector();
		try
		{
			/*
			rs = QuerySQL("select * from "+table);
			ResultSetMetaData rmd = rs.getMetaData();
			int cols = rmd.getColumnCount();
			for(int i=1;i<=cols;i++)
			{
				Hashtable hash = new Hashtable();
				//hash.put("目录名",rmd.getCatalogName(i));
				//hash.put("列返回值类型名",rmd.getColumnClassName(i));			
				hash.put("列定义大小",rmd.getColumnDisplaySize(i)+"");
				//hash.put("列标签",rmd.getColumnLabel(i));
				hash.put("字段名",rmd.getColumnName(i));
				hash.put("列类型编号",rmd.getColumnType(i)+"");
				hash.put("列标准类型名",rmd.getColumnTypeName(i));
				hash.put("列精确度",rmd.getPrecision(i)+"");
				//hash.put("10",rmd.getScale(i)+"");
				//hash.put("11",rmd.getSchemaName(i));
				//hash.put("表名",rmd.getTableName(i));
				//hash.put("13",rmd.isAutoIncrement(i)+"");
				//hash.put("大小写敏感",rmd.isCaseSensitive(i)+"");
				//hash.put("是否为金额",rmd.isCurrency(i)+"");
				//hash.put("是否可写",rmd.isDefinitelyWritable(i)+"");
				hash.put("是否可为空",rmd.isNullable(i)+"");
				//hash.put("是否只读",rmd.isReadOnly(i)+"");
				//hash.put("是否可查询",rmd.isSearchable(i)+"");
				hash.put("是否数字",rmd.isSigned(i)+"");
				//hash.put("是否可写",rmd.isWritable(i)+"");
				vect.add(hash);
			}
			*/


			DatabaseMetaData dmd = conn.getMetaData();
			rs = dmd.getColumns(null,ParentBean.DBName.toUpperCase(),table.toUpperCase(),null);
rs=dmd.getColumns(null,"HG","TEST",null);
			ResultSetMetaData rmd = rs.getMetaData();
			int cols = rmd.getColumnCount();
System.out.println(cols+"gggHHH");
System.out.println("resultSET"+getResultSetData(rs));
			while(rs.next())
			{
	System.out.println("TTTTT");
				Hashtable hash = new Hashtable();			
				hash.put("列定义大小",rs.getString("CHAR_OCTET_LENGTH")+"");
				String f = rs.getString("COLUMN_NAME");
				ResultSet r = QuerySQL("select "+f+" from "+table);
				ResultSetMetaData rm = r.getMetaData();

				hash.put("字段名",f+"");
				hash.put("列类型编号",rm.getColumnType(1)+"");
				hash.put("列标准类型名",rm.getColumnTypeName(1)+"");
				
				hash.put("是否可为空",rm.isNullable(1)+"");
				hash.put("是否数字",rm.isSigned(1)+"");
				hash.put("列定义大小",rm.getColumnDisplaySize(1)+"");	
				hash.put("列精确度",rs.getString("NUM_PREC_RADIX")+"");
	
				r.close(); Statement stst = r.getStatement(); if(stst!=null) stst.close();				
				vect.add(hash);
			}
			Statement stmt = rs.getStatement(); 
			rs.close();
			if(stmt!=null)
				stmt.close();
	System.out.println("____"+vect);
		}
		catch(SQLException sqle){System.out.println("调用DataBase.getTableStruct()函数错误:\r\n"+sqle);}
		catch(AbstractMethodError e){System.out.println("调用DataBase.getTableStruct()函数错误:\r\n"+e);}
		return vect;
	}

	/**取得数据集内容*/
	public Vector getResultSetData(ResultSet rs)
	{
		Vector vect = new Vector();
		try
		{
			//取得列数和列名
			ResultSetMetaData rsmd = rs.getMetaData();
			int cols = rsmd.getColumnCount();
			while(rs.next())
			{	
				Hashtable hash = new Hashtable();
				for(int i=1;i<=cols;i++)
				{
					DealString ds = new DealString();
					String field = ds.toString(rsmd.getColumnName(i));
					String value = ds.toString(rs.getString(i));
					hash.put(field,value);
				}
				vect.add(hash);
			}
		}
		catch(SQLException sqle){System.out.println("调用DataBase.getResultSetData()函数错误:\r\n"+sqle);}
		return vect;
	}

	/**创建申明对象*/
	public void prepareStatement(String sql)
	{