database.java

NEWS publish. JSP编写.系统测试用户名随便,直接点确定进入后数据库用户名/密码:随便

			else
			{
				out = t1;

			}
			rs.close();
			pstm.close();
		}
		catch(SQLException sqle)
		{
			System.out.println("调用SQL语句 " + sql + " 时出错;\r\n错误为:" + sqle);
		}
		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())
			{ //有记录
				out = rs.getInt("ID");
			}

			rs.close();
			pstm.close();
		}
		catch(SQLException sqle)
		{
			System.out.println("调用SQL语句 " + sql + " 时出错;\r\n错误为:" + sqle);
		}
		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);
			}
			rs.close();
			pstm.close();
		}
		catch(SQLException sqle)
		{
			System.out.println("调用SQL语句 " + sql + " 时出错;\r\n错误为:" + sqle);
		}
		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));
			}
			rs.close();
			pstm.close();
		}
		catch(SQLException sqle)
		{
			System.out.println("调用SQL语句 " + sql + " 时出错;\r\n错误为:" + sqle);
		}
		return out;
	}

	/**分页时取得一页的数据量*/
	public Vector getOnePage(String sql, int page, int records)
	{
		//第一个为总页数*/
	   //第二...个为Hashtable*/
	  Vector vect = new Vector();
		try
		{
			pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
										 ResultSet.CONCUR_UPDATABLE);
			rs = pstm.executeQuery();
			int rows = 0;
			while(rs.next())
			{
				rows++;
			}
			int sum = rows / records;
			if(rows % records != 0 || rows == 0)
			{
				sum++;
			}
			vect.add("" + rows);
			vect.add("" + sum);

			//移到当前行
			pstm.close();
			rs.close();

			pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
										 ResultSet.CONCUR_UPDATABLE);
			rs = pstm.executeQuery();
			rows = (page - 1) * records;
			rs.absolute(rows+1);

			DealString ds = new DealString();

			//查询当前页
			int j = 0;
			do
			{
				if(rs==null||j == records||rs.getRow()==0)
				{
					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);
			}while(rs.next());
			rs.close();
			pstm.close();
		}
		catch(SQLException sqle)
		{
			System.out.println("执行SQL语句 " + sql + " 分页至第 " + page +
							   " 页时出错;错误为:" +
							   sqle);
		}
		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++;
		   }

		   pstm_t.close();
		   rs.close();
		   pstm.close();
	   }
	   catch(SQLException sqle)
	   {
		   System.out.println("调用MyDataBase.setSort()函数错误:\r\n" + sqle);
	   }
		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();
					}
				}
			}

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

			//out = (new DealString()).Replace(out,"\"","\"\"");

		}
		catch(SQLException sqle)
		{
			System.out.println("调用MyDataBase.QueryCLOB()函数错误:\r\n" + sqle);
		}
		catch(IOException iosql)
		{
			System.out.println("调用MyDataBase.QueryCLOB()函数错误:\r\n" + iosql);
		}
		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();
			}
			rs.close();
			pstm.close();

			/* 正式提交 */
			conn.commit();
			conn.setAutoCommit(defaultCommit);
		}
		catch(SQLException sqle)
		{
			System.out.println("调用MyDataBase.UpdateCLOB()函数错误:\r\n" + sqle);
			return sqle.getErrorCode();
		}
		catch(Exception e)
		{
			System.out.println("Exception:" + e);
		}
		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();
					}
				}
			}

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

			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);
		}
		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);
			}
			rs.close();
			pstm.close();

			/* 正式提交 */
			conn.commit();
			conn.setAutoCommit(defaultCommit);
		}
		catch(SQLException sqle)
		{
			System.out.println("调用MyDataBase.UpdateBLOB()函数错误:\r\n" + sqle);
			return sqle.getErrorCode();
		}
		catch(Exception e)
		{