database.java

这是一个很好用的软件

	public String toName(String table, String field1, String field2,
			String value1) {
		String out = "";
		String sql = "";
		try {
			sql = "select " + field2 + " from news." + 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, false);
	}

	/** 分页时取得一页的数据量 */
	public Vector getOnePage(String sql, int page, int records, boolean useDic) {
		//第一个为总页数*/
		//第二...个为Hashtable*/
		Vector vect = new Vector();
		int zdrecords = records;
		try {
			if (useDic) {
				String strsql = "select XMMC from news.CODE_ZDB where 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 news." + 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 news." + table + " set " + field1 + "=" + i
							+ " where " + field2 + "=" + rs.getString(1);
				} else //为field2字符串
				{
					sql = "update news." + 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;
	}

	/** 数据库信息 */
	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 void prepareStatement(String sql) {
		try {
			pstm = conn.prepareStatement(sql);
		} catch (SQLException sqle) {
			System.out.println("调用DataBase.prepareStatement()函数错误:\r\n" + sqle);
		}
	}

	/** 执行查询 */
	public void executeQuery() {
		try {
			rs = pstm.executeQuery();
		} catch (SQLException sqle) {
			System.out.println("调用DataBase.executeQuery()函数错误:\r\n" + sqle);
		}
	}

	/** 转向下一条 */
	public boolean next() {
		try {
			return rs.next();
		} catch (SQLException sqle) {
			System.out.println("调用DataBase.next()函数错误:\r\n" + sqle);
		}
		return true;
	}

	/** 取得数据并根据数据类型转化为字符串 */

	/** 根据数据类型保存到数据库 */
		/** 执行更新 */
	public void executeUpdate() {
		try {
			pstm.executeUpdate();
		} catch (SQLException sqle) {
			System.out.println("调用DataBase.executeUpdate()函数错误:\r\n" + sqle);
		}
	}

	/** 关闭申明对象 */
	public void closePstm() {
		if (pstm != null)
			try {
				pstm.close();
			} catch (SQLException sqle) {
				System.out.println("调用DataBase.closePstm()函数错误:\r\n" + sqle);
			}
	}

	/** 关闭游标 */
	public void closeRs() {
		if (rs != null)
			try {
				rs.close();
			} catch (SQLException sqle) {
				System.out.println("调用DataBase.closeRs()函数错误:\r\n" + sqle);
			}
	}

	public void setBinaryStream(int index, InputStream is, int t)
			throws Exception {
		pstm.setBinaryStream(index, is, t);
	}

	public void setAsciiStream(int index, InputStream is, int t)
			throws Exception {
		pstm.setBinaryStream(index, is, t);
	}

	public boolean getAutoCommit() {
		try {
			return conn.getAutoCommit();
		} catch (SQLException e) {
		}
		return true;
	}

	public void closeAutoCommit() {
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
		}
	}

	public void commit() {
		try {
			conn.commit();
		} catch (SQLException e) {
		}
	}

	public void rollback() {
		try {
			conn.rollback();
		} catch (SQLException e) {
		}
	}

	public void openAutoCommit() {
		try {
			conn.setAutoCommit(true);
		} catch (SQLException e) {
		}
	}

	public void createStatement() {
		try {
			stm = conn.createStatement();
		} catch (SQLException e) {
		}
	}

	public void closeStm()//专门使用
	{
		if (stm != null)
			try {
				stm.close();
			} catch (SQLException e) {
			}
	}

	public void closeAll() {
		this.closeRs();
		this.closePstm();
		this.closeStm();
		this.releaseConn();//下一次再使用要重新连接
	}

	public static void main(String[] args) throws Exception {
		/*
		 * 必须执行的代码 stm = rs.getStatement(); rs.close(); stm.close();
		 */
		System.out.println("begin\r\n\r\n");
		DataBase db = new DataBase();
		//db.connected=true;
		db.createConn("com.microsoft.jdbc.sqlserver.SQLServerDriver",
				"jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=news",
				"news", "news");

		if (db.conn != null) {
			System.out.println("数据库建立成功!");
		}
		Vector v = db.getData("select * from news.menu");
		if (v.size() > 5) {
			System.out.println("执行查询成功!");
		}
		ResultSet r = db.QuerySQL("select * from news.menu");
		if (r.next())
			System.out.println("查询结果有:" + (String) r.getString(6));

		Hashtable ht = db.getDataBaseInfo();
		System.out.println("数据库的用户名为:" + ht.get("用户名"));
		db.closeAll();
		System.out.println("\r\n\r\nend");
	}
}