database.java

“JSP数据库项目案例导航”一书从第一章到第十一章各章实例的源程序文件以及数据库文件。 注意： 1. 本书中的案例提供的数据库环境不同

				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++;							
			}
			
			if(pstm_t!=null)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);
		            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对象 */