database.java

JSP+Mysql+Tomcat的新闻发部系统,最好下一个最新MYSQL驱动

			System.out.println("Exception:" + 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 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){System.out.println("执行DataBase.getData(String)执行SQL语句 "+sql+" 时出错;错误为:"+sqle);}
		finally
		{
			closeRs();
			closePstm();
		}
		return vect;
	}

	/**数据库信息*/
	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);
			}
			rs.close();
			Statement stmt = rs.getStatement();
			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);
		}