database.java

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

			}
		}
		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){System.out.println("执行DataBase::toID(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+sqle);}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){System.out.println("执行DataBase::toID(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){System.out.println("执行DataBase::toID(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
			}
		}
		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){System.out.println("执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+sqle);}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){System.out.println("执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){System.out.println("执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
			}
		}
		return out;
	}	

	/**将编号转换为名称*/
	public String toName2(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){System.out.println("执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+sqle);}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){System.out.println("执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){System.out.println("执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
			}
		}
		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 b)
	{
		/**第一个为总页数
		*  第二...个为Hashtable
		*/
		Vector vect = new Vector();
		try
		{
			pstm = conn.prepareStatement(sql);
			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);
			int temp = rows;

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

			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			rows = (page - 1) * records;
			rows++;
			while(rows>0)
			{
				rs.next();
				rows--;
			}

			DealString ds = new DealString();

			//查询当前页
			int j = 0;

			do
			{
				if(rs==null||j == records||temp==0||page>sum)
				{
					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());
		}catch(SQLException sqle)
		{
			System.out.println("执行SQL语句 " + sql + " 分页至第 " + page +
							   " 页时出错;错误为:" +
							   sqle);
		}
		finally
		{
			closeRs();
			closePstm();
		}
		return vect;
	}


	/**分页时取得一页的数据量*/
	public Vector getOnePage1(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){System.out.println("DataBase::getOnePage(String,int,int)执行SQL语句 "+sql+" 分页至第 "+page+" 页时出错;错误为:"+sqle);}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){System.out.println("DataBase::getOnePage(String,int,int)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){System.out.println("DataBase::getOnePage(String,int,int)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
			}
		}
		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){System.out.println("执行DataBase::getData(String)执行SQL语句 "+sql+" 时出错;错误为:"+sqle);}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){System.out.println("执行DataBase::getData(String)试图释放rs时出错;\r\n错误为:"+e);}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){System.out.println("执行DataBase::getData(String)试图释放pstm时出错;\r\n错误为:"+e);}
			}
		}
		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();	
		}catch(SQLException sqle){System.out.println("调用MyDataBase.setSort()函数错误:\r\n"+sqle);}
		finally{
			if(rs!=null){
				try{
				rs.close();
				}catch(SQLException e){System.out.println("调用MyDataBase.setSort()试图释放rs时出错;\r\n错误为:"+e);}
			}
			if(pstm!=null){
				try{
				pstm.close();
				}catch(SQLException e){System.out.println("调用MyDataBase.setSort()试图释放pstm时出错;\r\n错误为:"+e);}
			}
		}
		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){System.out.println("??MyDataBase.QueryCLOB()????:\r\n"+sqle);}
		catch(IOException iosql){System.out.println("??MyDataBase.QueryCLOB()????:\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;
	}

	/**??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)
		{
			System.out.println("??MyDataBase.UpdateCLOB()????:\r\n"+sqle);
			return sqle.getErrorCode();
		}
		catch(Exception e){System.out.println("Exception:"+e);}
		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 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);