database.java

这是一个很好用的软件

package oa.main;

import java.util.*;
import java.sql.*;
import java.io.*;

public class DataBase extends Object {
	Connection conn = null;

	Statement stm = null;

	PreparedStatement pstm = null;

	ResultSet rs = null;

	boolean connected = false;

	/** 构造函数 */
	public DataBase() {
	}

	/** 生成 SQLServer连接 */
	public void createConn(String drv, String url, String usr, String pwd) {
		try {
			if (connected) {
				throw new SQLException("数据库已连接，无须重连！");
			}
			Class.forName(drv).newInstance();
			conn = DriverManager.getConnection(url, usr, pwd);
			connected = true;

		} catch (ClassNotFoundException ec) {
			System.out.println("从自身建立数据库连接时出错1;\r\n错误为:" + ec);
			ec.printStackTrace();
		} catch (SQLException e) {
			System.out.println("从自身建立数据库连接时出错2;\r\n错误为:" + e);
		} catch (Exception et) {
			System.out.println("从自身建立数据库连接时出错3;\r\n错误为:" + et);
		}
	}

	/** 释放数据库连接 */
	public void releaseConn() {
		try {
			if (!connected) {
				//throw new SQLException("数据库未连接！");
			}
			if (conn != null) {
				conn.close();
				connected = false;

				/** 测试连接计数 */
			}
		} catch (SQLException e) {
			System.out.println("关闭数据库连接时出错;\r\n错误为:" + e);
		}
	}

	/** 查询记录 */
	public ResultSet QuerySQL(String sql) {
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
		} catch (SQLException sqle) {
			System.out.println("执行DataBase::QuerySQL(String)调用SQL语句 " + sql
					+ " 时出错;\r\n错误为:" + sqle);
			if (pstm != null)
				try {
					pstm.close();
				} catch (Exception e) {
					System.out
							.println("执行DataBase::QuerySQL(String)试图关闭错误的声明时出错;\r\n错误为："
									+ e);
				}
		}
		return rs;
	}

	/** 执行增删改的语句 */
	public int ExecuteSQL(String sql) {
		try {
			pstm = conn.prepareStatement(sql);
			pstm.executeUpdate();
			conn.commit();
		} catch (SQLException sqle) {
			System.out.println("执行DataBase::ExecuteSQL(String)调用SQL语句 " + sql
					+ " 时出错;\r\n错误为:" + sqle);
			return sqle.getErrorCode();
		} finally {
			try {
				pstm.close();
			} catch (SQLException sqle) {
				System.out.println("执行DataBase::ExecuteSQL(String)调用SQL语句 "
						+ sql + " 时出错;\r\n错误为:" + sqle);
			}
		}
		return 0;
	}

	/** 产生唯一编号 */
	public int makeID(String table, String field1, String field2,
			String value1, boolean type1) {
		int out = -1;
		String sql = "";
		try {
			//只有唯一主键field1
			sql = "select " + field1 + " as ID from news." + table + " order by "
					+ field1;
			//有两个主键field1、field2
			if (!value1.equals(""))//当第一个字段不空时，作为条件查询第二个字段
			{
				sql = "select " + field2 + " as ID from news." + table + " where "
						+ field1 + "=" + value1 + " order by " + field2;
				if (!type1) //是字符串时 将type1设为false
					sql = "select " + field2 + " as ID from news." + table
							+ " where " + field1 + "='" + value1
							+ "' order by " + field2;
			}
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			int t1 = 1;
			int t2 = 2;
			if (rs.next()) //有记录
			{
				t1 = rs.getInt("ID");
				out = t1;
				boolean bool = false;
				while (rs.next()) //不止一条纪录
				{
					bool = true;
					t2 = rs.getInt("ID");
					if ((t2 - t1) > 1)
						break; //如果t2与t1相差大于1,则跳出去,新编号为t1++（见后面**）
					t1 = t2; //否则将t2赋给t1
				}
				if (!bool) //如果只有一条纪录
				{
					if (t1 > 1)
						t1 = 1; //如果已有纪录的ID号大于1，则新编号设为1
					else
						t1++;
				} else
					t1++; //**
			}
			if (out > 1)
				out = 1;
			else
				out = t1;

		} catch (SQLException sqle) {
			System.out
					.println("执行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "
							+ sql + " 时出错;\r\n错误为:" + sqle);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					System.out
							.println("执行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "
									+ sql + " 时出错;\r\n错误为:" + e);
				}
			}
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					System.out
							.println("执行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "
									+ sql + " 时出错;\r\n错误为:" + e);
				}
			}
		}
		return out;
	}

	/** 产生唯一编号 */
	public int makeID_Add1(String table, String field1, String field2,
			String value1, boolean type1) {
		int out = -1;
		String sql = "";
		try {
			//只有唯一主键field1
			sql = "select max(" + field1 + ")+1 as ID from " + table
					+ " order by " + field1;
			//有两个主键field1、field2
			if (!value1.equals(""))//当第一个字段不空时，作为条件查询第二个字段
			{
				sql = "select (" + field2 + ")+1 as ID from " + table
						+ " where " + field1 + "=" + value1 + " order by "
						+ field2;
				if (!type1) //是字符串时 将type1设为false
					sql = "select (" + field2 + ")+1 as ID from " + table
							+ " where " + field1 + "='" + value1
							+ "' order by " + field2;
			}
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			if (rs.next()) //有记录
			{
				out = rs.getInt(1);
			}
		} catch (SQLException sqle) {
			System.out
					.println("执行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "
							+ sql + " 时出错;\r\n错误为:" + sqle);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					System.out
							.println("执行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "
									+ sql + " 时出错;\r\n错误为:" + e);
				}
			}
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					System.out
							.println("执行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "
									+ sql + " 时出错;\r\n错误为:" + e);
				}
			}
		}
		return out;
	}

	public int makeID(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 " + field1 + " as ID from " + table + " order by "
					+ field1;
			//有两个主键field1、field2
			if (!value1.equals(""))//当第一个字段不空时，作为条件查询第二个字段
			{
				sql = "select " + field2 + " as ID from " + table + " where "
						+ field1 + "=" + value1 + " order by " + field2;
				if (!type1) //是字符串时 将type1设为false
					sql = "select " + field2 + " as ID from " + table
							+ " where " + field1 + "='" + value1
							+ "' order by " + field2;
			}
			if (!value2.equals(""))//当第二个字段不空时，作为条件查询第三个字段
			{
				sql = "select " + field3 + " as ID from " + table + " where "
						+ field1 + "=" + value1 + " and " + field2 + "="
						+ value2 + " order by " + field3;
				if (!type2) //是字符串时 将type1设为false
					sql = "select " + field3 + " as ID from " + table
							+ " where " + field1 + "='" + value1 + "' and "
							+ field2 + "='" + value2 + "' order by " + field3;
			}
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			int t1 = 1;
			int t2 = 2;
			if (rs.next()) //有记录
			{
				t1 = rs.getInt("ID");
				out = t1;
				boolean bool = false;
				while (rs.next()) //不止一条纪录
				{
					bool = true;
					t2 = rs.getInt("ID");
					if ((t2 - t1) > 1)
						break; //如果t2与t1相差大于1,则跳出去,新编号为t1++（见后面**）
					t1 = t2; //否则将t2赋给t1
				}
				if (!bool) //如果只有一条纪录
				{
					if (t1 > 1)
						t1 = 1; //如果已有纪录的ID号大于1，则新编号设为1
					else
						t1++;
				} else
					t1++; //**
			}
			if (out > 1)
				out = 1;
			else
				out = t1;
		} catch (SQLException sqle) {
			System.out
					.println("执行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "
							+ sql + " 时出错;\r\n错误为:" + sqle);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					System.out
							.println("执行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "
									+ sql + " 时出错;\r\n错误为:" + e);
				}
			}
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					System.out
							.println("执行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "
									+ sql + " 时出错;\r\n错误为:" + e);
				}
			}
		}
		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 news." + table
					+ " order by " + field1;
			//有两个主键field1、field2
			if (!value1.equals(""))//当第一个字段不空时，作为条件查询第二个字段
			{
				sql = "select max(" + field2 + ") as ID from news." + table
						+ " where " + field1 + "=" + value1 + " order by "
						+ field2;
				if (!type1) //是字符串时 将type1设为false
					sql = "select max(" + field2 + ") as ID from news." + table
							+ " where " + field1 + "='" + value1
							+ "' order by " + field2;
			}
			if (!value2.equals(""))//当第二个字段不空时，作为条件查询第三个字段
			{
				sql = "select max(" + field3 + ") as ID from news." + table
						+ " where " + field1 + "=" + value1 + " and " + field2
						+ "=" + value2 + " order by " + field3;
				if (!type2) //是字符串时 将type1设为false
					sql = "select max(" + field3 + ") as ID from news." + table
							+ " where " + field1 + "='" + value1 + "' and "
							+ field2 + "='" + value2 + "' order by " + field3;
			}
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			if (rs.next()) //有记录
			{
				out = rs.getInt("ID");
			}
		} catch (SQLException sqle) {
			System.out
					.println("执行DataBase::makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "
							+ sql + " 时出错;\r\n错误为:" + sqle);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					System.out
							.println("执行DataBase::makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "
									+ sql + " 时出错;\r\n错误为:" + e);
				}
			}
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					System.out
							.println("执行DataBase::makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "
									+ sql + " 时出错;\r\n错误为:" + e);
				}
			}
		}
		return out;
	}

	/** 将名称转换为编号 */
	public int toID(String table, String field1, String field2, String value1) {
		int out = -1;
		String sql = "";
		try {
			sql = "select " + field2 + " from news." + 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;
	}

	/** 将编号转换为名称 */