数据库工具java部分

最新推荐文章于 2023-03-07 16:15:55 发布

原创最新推荐文章于 2023-03-07 16:15:55 发布 · 777 阅读

0 ·

CC 4.0 BY-SA版权

这篇博客介绍了使用Java开发数据库应用的经验，重点在于连接控制和数据获取。代码结构简洁，仅包含12个类，适合初学者。作者采用了Java+Flex的组合，并提供了数据库数据获取的关键代码，但出于安全考虑未包含修改数据的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

之前花两个星期写了一个简易的数据库工具，还有好多功能没有做，这是界面部分，左上角的下拉框来选择数据库，目前支持的数据库是mysql,oracle,sqlserver，树上显示的是之前保存的数据库连接，点击后，会弹出请输入密码的输入框，输入密码后，就可以打开树，然后点击数据库，打开相应的数据库，点击先关的表，就可以查看表数据，表数据可以用excel导出。点击查询查询后，就可以打开查询面板，在查询面板中就可以输入要执行的sql语句。界面部分用flex完成，因为flex实现快，且不用考虑浏览器兼容性。展开后的面板如下所示。

java部分代码结构很简单就12个类，界面也很简单。使用java+flex的模式开发应用非常适合像我这样的菜鸟。

其中的关键在于对连接的控制和数据库数据的获取。我分别将其操作放入到两个类中。

数据库数据的获取代码如下，因为安全因素，没哟添加修改表数据和字段功能，大家可以单个小练习看看

package itims.typNew.tool.dbtool.operator;

import itims.typNew.tool.dbtool.service.DbDataAction;
import itims.typNew.tool.dbtool.util.SqlGenerator;
import itims.typNew.tool.dbtool.bean.ColumnsInfo;
import itims.typNew.tool.dbtool.util.UtilTool;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
 * 
 * @author chenshanqun
 *连接上数据库以后，对数据库的所有操作方法
 */
public class DataControl {
	private Log logger = LogFactory.getLog(DataControl.class);
	private Connection conn;

	private String dbType = null;

	public DataControl(Connection conn) {
		this.conn = conn;
	}

	public void setConnection(Connection conn) {
		this.conn = conn;
	}

	public Connection getConnection() {
		return this.conn;
	}
	/**
	 * 获取数据库名称
	 * @return
	 */
	public String getDatabaseName() {
		try {
			String name = conn.getMetaData().getDatabaseProductName();
			name = name.replaceAll("/", "-");
			return name;
		} catch (SQLException e) {
			// System.out.println("meta.getDatabaseProductName.ERROR" +
			// e.getMessage());
			return "--ERROR--";
		}
	}

	/**
	 * 获取所有的数据库列表   （有的数据库不能根据连接获取到和他同级的其他数据库，如oracle)
	 * @return
	 */
	public List<String> getCatalogs() {
		List<String> list = new ArrayList<String>();
		try {

			ResultSet rs;

			rs = conn.getMetaData().getCatalogs();
			while (rs.next()) {
				// System.out.println("getcatalogs+++++++++++++++++++++++++++++++++++++++++++++");
				String schema = rs.getString("TABLE_CAT");
				list.add(schema);
			}

			rs.close();
		} catch (SQLException e) {
			// System.out.println("meta.getSchemas" + e.getMessage());
		}
		return list;
	}

	/**
	 * 获取所有的表空间
	 * @return
	 */
	public List<String> getSchemas() {

		List<String> list = new ArrayList<String>();
		try {

			ResultSet rs = conn.getMetaData().getSchemas();
			while (rs.next()) {
				// System.out.println("get tableschem++++++++++++++++++++++++++++++++++++++++++++++++");
				String schema = rs.getString("TABLE_SCHEM");
				list.add(schema);
			}
			rs.close();

		} catch (SQLException e) {
			// System.out.println("meta.getSchemas" + e.getMessage());
		}
		return list;
	}
	/**
	 * 获取所有表的类型（如视图，表等）
	 * @return
	 */
	public List<String> getElementTypes() {
		List<String> list = new ArrayList<String>();
		try {
			ResultSet rs = conn.getMetaData().getTableTypes();
			while (rs.next()) {
				String type = rs.getString("TABLE_TYPE");
				list.add(type);
			}
			rs.close();
		} catch (SQLException e) {
			// System.out.println("meta.getTableTypes" + e.getMessage());
		}
		return list;
	}

	/**
	 * 
	 * @param schema 表空间或者数据库名（因为有的数据库没有表空间，如mysql）
	 * @param types 表的类型列表
	 * @param mode 数据库结构的类型，0有表空间，1没有表空间，有数据库名
	 * @return 获取所有的数据表（表名列表）
	 */
	public List<String> getElements(String schema, List types, int mode) {
		// mode 0 means by schema;
		// mode 1 means by catalog;
		String[] newTypes;
		List<String> list = new ArrayList<String>();
		if (types.size() == 0) {
			newTypes = new String[] { "table" };
		}
		try {
			ResultSet rs = null;
			newTypes = UtilTool.changeListToStrings(types);
			System.out.println(newTypes[0]);
			if (mode == 0) {
				rs = conn.getMetaData().getTables(null, schema, null, newTypes);
			} else {
				rs = conn.getMetaData().getTables(schema, null, null, newTypes);
			}

			while (rs.next()) {
				String name = rs.getString("TABLE_NAME");

				if (name.indexOf('/') > -1 || name.indexOf('$') > -1) {
					continue;
				}
				list.add(name);
			}
			rs.close();
		} catch (SQLException e) {
			// System.out.println("meta.getTableNames" + e.getMessage());
		}
		return list;
	}
	/**
	 * 
	 * @param tableName
	 * @return 获取一个数据表，所有的字段列表（列表里面的为字段对象）
	 */
	public List getColumns(String tableName) {
		ResultSet resultSet = null;
		List<ColumnsInfo> columnsInfoList = new ArrayList<ColumnsInfo>();
		try {
			resultSet = conn.getMetaData().getColumns(null, null, tableName,
					null);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (resultSet != null) {
			try {
				while (resultSet.next()) {
					// 获得字段名称
					String name = resultSet.getString("COLUMN_NAME");
					// 获得字段类型名称
					String type = resultSet.getString("TYPE_NAME");
					// 获得字段大小
					int size = resultSet.getInt("COLUMN_SIZE");
					// 获得字段备注
					String remark = resultSet.getString("REMARKS");
					ColumnsInfo info = new ColumnsInfo();
					info.setImportedKey(false);
					info.setParmaryKey(false);
					info.setName(name);
					info.setSize(size);
					info.setType(type);
					info.setRemark(remark);
					columnsInfoList.add(info);
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return columnsInfoList;
	}
	/**
	 * 根据一个数据集，获取这个数据集所有的字段列表
	 * @param rs
	 * @return
	 */
	public List getColumns(ResultSet rs) {
		int columnsCount = 0;
		java.sql.ResultSetMetaData rsm = null;
		try {
			rsm = rs.getMetaData();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<ColumnsInfo> columnsInfoList = new ArrayList<ColumnsInfo>();
		try {
			columnsCount = rs.getMetaData().getColumnCount();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		for (int i = 0; i < columnsCount; i++) {
			// 获得字段名称
			ColumnsInfo info = new ColumnsInfo();
			String name = null;
			try {
				System.out.println("dsfafed      " + i + 1);
				info.setType(rsm.getColumnTypeName(i + 1));
				System.out.println("column name is" + rsm.getColumnName(i + 1));

				info.setName(rsm.getColumnName(i + 1));
				info.setSize(rsm.getColumnDisplaySize(i + 1));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			// 获得字段类型名称

			columnsInfoList.add(info);
		}

		return columnsInfoList;
	}
	/**
	 * 
	 * @param sql
	 * @return 执行sql查询语句，获取数据
	 */
	public Map executeQuery(String sql) {
		HashMap queryData = new HashMap();
		ArrayList al = new ArrayList();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			System.out.println("sql" + sql);
			logger.debug(sql);
			ps = conn.prepareStatement(sql);

		} catch (SQLException e) {
			queryData.put("error", e.getMessage());
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// System.out.println("ps"+ps);

		if (ps != null) {
			try {
				rs = ps.executeQuery();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				// System.out.println(e.getMessage());
				queryData.put("error", e.getMessage());
				e.printStackTrace();
			}
			// System.out.println("rs"+rs);
			if (rs != null) {
				List columns = getColumns(rs);
				/**
				 * 将字段名的list放入到返回数据中
				 */
				queryData.put("columns", UtilTool.getColumnNamesList(columns));
				int k = 0;
				// System.out.println("rsKKKK    "+k);
				try {
					while (rs.next()) {
						// System.out.println("rsJJJJ   "+k);
						k++;
						HashMap<String, String> dataMap = new HashMap<String, String>();
						if (columns != null) {
							if (columns.size() > 0) {
								/**
								 * 从resultSet中，读取出每个属性的数据，作为map的形式，出入到list中
								 */
								for (int i = 0, j = columns.size(); i < j; i++) {
									ColumnsInfo info = (ColumnsInfo) columns
											.get(i);
									// System.out.println("info type"+info.getType());
									String type = UtilTool.getType(info
											.getType());
									/**
									 * 遍历每个属性的数据类型，根据属性类型，选择不同的读取方法
									 */
									String infoName=info.getName();
									
									String value=UtilTool.getValueByDataType(infoName, type, rs);
									dataMap.put(info.getName(), value);
								}
							}
						}
						al.add(dataMap);
					}
					rs.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					queryData.put("error", e.getMessage());
					// System.out.println(e.getMessage());
					al = null;
				}

			}
		}
		queryData.put("data", al);
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return queryData;
	}
	/**
	 * 执行更新的sql语句
	 * @param sql
	 * @return
	 */
	public Map executeUpdate(String sql) {
		HashMap queryData = new HashMap();
		int influenceCount = 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			queryData.put("error", e.getMessage());
			influenceCount = -1;
			e.printStackTrace();
		}
		if (ps != null) {
			try {
				influenceCount = ps.executeUpdate(sql);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				queryData.put("error", e.getMessage());
				influenceCount = -1;
			}finally{
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			queryData.put("data", influenceCount);
		}
		return queryData;
	}
	/**
	 * 执行sql，根据sql语句中，是否有select语句，分选操作方法
	 * @param sql
	 * @return
	 */
	public Map executeSql(String sql) {
		Map dataMap = new HashMap();

		if ((sql.trim().startsWith("select") || sql.trim().startsWith("SELECT"))) {
			dataMap.put("select", executeQuery(sql));
		} else {
			dataMap.put("update", executeUpdate(sql));
		}

		return dataMap;
	}
	/**
	 * 得到数据集的大小
	 * @param tableName
	 * @return
	 */
	public int getAllNumCount(String tableName) {
		int numCount = 0;
		String sql = SqlGenerator.getAllCount(tableName);
		Map data = executeQuery(sql);
		// System.out.println("+++"+JSONObject.fromObject(data));
		List list = (List) data.get("data");
		if (list == null) {
			return -1;
		}
		if (list.size() == 0) {
			return -1;
		}
		Map countData = (Map) list.get(0);
		if (countData == null) {
			return -1;
		}
		System.out.println("" + dbType);
		numCount = Integer.parseInt("" + countData.get("N"));
		return numCount;
	}
	/**
	 * 根据数据库名sql语句限制数据集，并返回数据集
	 * @param tableName
	 * @param limitNum
	 * @return
	 */
	public Map getRecordDataWithSqlLimit(String tableName, int limitNum) {

		dbType = getDatabaseName();
		// System.out.println(dbType);
		String sql = SqlGenerator.getLimit(dbType, tableName, limitNum * 1000,
				(limitNum + 1) * 1000);
		return executeQuery(sql);
	}
	/**
	 * 查询数据库的所有数据
	 * @param tableName
	 * @return
	 */
	public Map getRecordDataWithoutLimit(String tableName) {

		dbType = getDatabaseName();
		// System.out.println(dbType);
		String sql = SqlGenerator.getAllData(dbType, tableName);
		return executeQuery(sql);
	}
	/**
	 * 由于有的数据库不能用sql语句来限制数据集（如sqlserver），所以对限制数据集的查询进行分选方法
	 * @param tableName
	 * @param limitNum
	 * @return
	 */
	public Map getRecordDataWithLimit(String tableName, int limitNum) {
		dbType = getDatabaseName();
		if (dbType.equalsIgnoreCase("Microsoft SQL Server")) {
			/**
			 * 用jdbc进行数据集限制
			 */
			return getRecordDataWithJDBCLimit(tableName, limitNum);
		} else {
			/**
			 * 用sql语句进行数据集限制
			 */
			return getRecordDataWithSqlLimit(tableName, limitNum);
		}
	}
	/**
	 * 用jdbc限制数据集
	 * @param tableName
	 * @param limitNum
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map getRecordDataWithJDBCLimit(String tableName, int limitNum) {
		String sql = "select * from " + tableName;
		HashMap queryData = new HashMap();
		ArrayList al = new ArrayList();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			System.out.println("sql" + sql);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			// ps.setFetchSize(2);

		} catch (SQLException e) {
			queryData.put("error", e.getMessage());
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// System.out.println("ps"+ps);
		if (ps != null) {
			try {
				ps.setMaxRows((limitNum + 1) * 1000);
				rs = ps.executeQuery();
				// rs.relative(2);
				rs.absolute(limitNum * 1000);
				// rs.setFetchSize(6);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				// System.out.println(e.getMessage());
				queryData.put("error", e.getMessage());
				e.printStackTrace();
			}
			// System.out.println("rs"+rs);
			if (rs != null) {
				List columns = getColumns(rs);
				queryData.put("columns", UtilTool.getColumnNamesList(columns));
				int k = 0;
				System.out.println("序号    " + k);
				try {
					while (rs.next()) {
						System.out.println("序号   " + k);
						k++;
						HashMap<String, String> dataMap = new HashMap<String, String>();
						if (columns != null) {
							if (columns.size() > 0) {
								for (int i = 0, j = columns.size(); i < j; i++) {
									ColumnsInfo info = (ColumnsInfo) columns
											.get(i);
									System.out.println("info type"
											+ info.getType());
									String type = UtilTool.getType(info
											.getType());
									String infoName=info.getName();
									
									String value=UtilTool.getValueByDataType(infoName, type, rs);
									dataMap.put(info.getName(), value);

								}
							}
						}
						al.add(dataMap);
					}

				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					queryData.put("error", e.getMessage());
					System.out.println(e.getMessage());
					al = null;
				}

			}
		}
		// System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++");
		// System.out.println("al size "+al.size());
		queryData.put("data", al);
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return queryData;

	}
}

连接控制的代码如下

package itims.typNew.tool.dbtool.core;

import itims.typNew.tool.dbtool.bean.linkmanger.ConnectionInfo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 
 * @author chenshanqun
 *对数据库进行连接的操作类
 */
public class Connector {
	/**
	 * lastAccessMap 保存数据库最后一次连接的时间，用来检测超时
	 */
	public static HashMap<String, Long> lastAccessMap = new HashMap<String, Long>();
	/**
	 * connectionMap 保存数据库连接，对于同一个用户，对于相同数据库的连接，直接从数据库中获取，或者生成新的连接，放入到该map中
	 */
	public static HashMap<String, Connection> connectionMap = new HashMap<String, Connection>();
	/**
	 * connectionErrorMap 保存连接的错误信息
	 */
	public static HashMap<String, String> connectionErrorMap = new HashMap<String, String>();
	/**
	 * 保存连接信息，用来检测这次是否是和上次相同的连接
	 */
	public static HashMap<String, ConnectionInfo> connectionInfoMap = new HashMap<String, ConnectionInfo>();

	private static final Log log = LogFactory.getLog(Connector.class);
	/**
	 * 更新连接时间的静态方法，一个用户，只能存在一个连接，在开启一个新连接的时候，就会关闭旧连接
	 */
	public static void updateLastAccessTime(String userId) {

		lastAccessMap.put(userId, new Date().getTime());

	}
	/**
	 * 检测连接是否相同的标志位。true表示连接是第一次或者更新的，false表示和上次连接相同
	 * connInfo 存储的连接信息；
	 * 其他变量 ：这次连接的相关连接信息；
	 */
	public static boolean getChgFlag(String userId, ConnectionInfo connInfo,
			String type, String url, String password, String userName) {
		if (connInfo == null) {

			return true;
		}
		if (connInfo.getDbType().equals(type)
				&& connInfo.getUserName().equals(userName)
				&& connInfo.getDburl().equals(url)
				&& connInfo.getPassword().equals(password)) {

			return false;
		} else {

			return true;
		}

	}
	/**
	 * 进行连接的方法；先判断连接是否存在，存在返回原有的，否则生成新的连接返回，并存入到map中；如果生成新的连接失败，将错误信息存入到errorMap中
	 */
	public static Connection getConnection(String userId, String type,
			String url, String password, String userName) {
		ConnectionInfo connInfo = connectionInfoMap.get(userId);
		log.debug(userId + "," + type + "," + url + "," + password + ","
				+ userName);
		if (Connector.getChgFlag(userId, connInfo, type, url, password,
				userName) == true) {

			try {
				Class.forName(getDriver(type));
			} catch (ClassNotFoundException e) {
				log.error("Load JDBC Driver Class" + getDriver(type));
				// System.out.println("Load JDBC Driver Class:" +
				// getDriver(type));

			}

			Connection conn = null;
			try {
				//
				log.debug("usrid is " + userId);
				Connection beforConnection = connectionMap.get(userId);
				if (beforConnection != null) {
					Connector.disconnect(userId);
				}
				conn = DriverManager.getConnection(url, userName, password);

			} catch (SQLException e) {
				String msg = e.getMessage();
				// System.out.println(msg);
				log.error(msg);
				connectionErrorMap.put(userId, msg);
			}
			if (conn != null) {
				connectionMap.put(userId, conn);
				connInfo = new ConnectionInfo();
				connInfo.setDbType(type);
				connInfo.setDburl(url);
				connInfo.setPassword(password);
				connInfo.setUserName(userName);
				connectionInfoMap.put(userId, connInfo);
				log.debug("return conn");
			}
			return conn;

		} else {
			log.debug(connectionMap.get(userId));
			return connectionMap.get(userId);
		}

	}
	/**
	 * 
	 * @param userId 用户名
	 * @return 返回该用户名保存的连接错误信息
	 */
	public static String getErrorMsg(String userId) {
		return connectionErrorMap.get(userId);
	}

	/**
	 * 
	 * @param type 数据库类型
	 * @return 根据数据库类型，返回驱动字符串
	 */
	public static String getDriver(String type) {
		if (type.equalsIgnoreCase("mysql")) {

			return "com.mysql.jdbc.Driver";
		} else if (type.equalsIgnoreCase("oracle10g")) {
			System.out.println("ddd");
			return "oracle.jdbc.driver.OracleDriver";
		} else if (type.equalsIgnoreCase("sqlserver")) {
			return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		} else {
			return "";
		}
	}

	/**
	 * 
	 * @param userId
	 * 关闭该用户下保存的连接,并清空已经存储的所有记录
	 */
	public static void disconnect(String userId) {
		Connection conn = connectionMap.get(userId);
		try {
			if (conn != null) {
				conn.close();
				connectionInfoMap.remove(userId);
				connectionErrorMap.remove(userId);
				connectionMap.remove(userId);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

为了防止用户不关闭网页而造成连接不释放，在用户第一次连接后我定义了timer来开启一个线程，检测最近一次数据库连接离现在的时间。如果时间大于我设置的时间，则关

闭这个连接。同时释放掉这个线程和timer。代码如下

package itims.typNew.tool.dbtool.timer;

import itims.typNew.tool.dbtool.task.ConnectCheckTask;

import java.util.Timer;
import java.util.TimerTask;

public class ConnectCheckTimer {
	private static Timer timer;
	/**
	 * 启动timer
	 */
	public static void starTimer() {
		if (timer == null) {
			timer = new Timer();
			TimerTask task = new ConnectCheckTask();
			timer.schedule(task, 15 * 60 * 1000, 5 * 60 * 1000);
		}

	}
	/**
	 * 停止timer
	 */
	public static void stopTimer() {
		if (timer != null) {
			timer.cancel();
			timer = null;
		}
	}
}

控制timer的类

package itims.typNew.tool.dbtool.task;

import itims.typNew.tool.dbtool.core.Connector;
import itims.typNew.tool.dbtool.timer.ConnectCheckTimer;
import java.sql.Connection;
import java.util.Date;
import java.util.Iterator;
import java.util.Set;
import java.util.TimerTask;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
 * 检测连接是否超时的任务
 * @author chenshanqun
 *
 */
public class ConnectCheckTask extends TimerTask {
	private static final Log log = LogFactory.getLog(ConnectCheckTask.class);
	long closeTimeOut = 30 * 60 * 1000;

	@Override
	/**
	 * 遍历每一个connection，查看其是否已经超时，超时则中断连接，并在map中移除这个连接，如果已经不存在连接，则停止定时器
	 */
	public void run() {
		Set keyset = Connector.connectionMap.keySet();
		Iterator<String> it = keyset.iterator();
		while (it.hasNext()) {
			checkLastTime(it.next());
		}
		if (keyset.size() == 0) {
			ConnectCheckTimer.stopTimer();
		}
		// TODO Auto-generated method stub

	}
	/**
	 * 检测该用户对应的连接是否超时，超时，则终止这个连接，并移除该连接,移除连接在Connector.disconnect中执行
	 * @param userId
	 */
	
	public void checkLastTime(String userId) {
		
		
		long latestTime = getLatestTime(userId);
		long nowTime = new Date().getTime();
		if ((nowTime - latestTime) > closeTimeOut) {
			
			Connector.disconnect(userId);

		}
	}
	/**
	 * 得到最近连接的时间
	 * @param userId
	 * @return
	 */
	public long getLatestTime(String userId) {
		return Connector.lastAccessMap.get(userId);

	}
	/**
	 * 得到连接
	 * @param userId
	 * @return
	 */
	public Connection getConnection(String userId) {
		return Connector.connectionMap.get(userId);
	}

}

提供的对外的接口action

package itims.typNew.tool.dbtool.service;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import itims.typNew.base.action.BaseAction;
import itims.typNew.tool.dbtool.core.Connector;
import itims.typNew.tool.dbtool.operator.DataControl;
import itims.typNew.tool.dbtool.operator.excel.ExcelOperator;
import itims.typNew.tool.dbtool.timer.ConnectCheckTimer;
/**
 * 
 * @author chenshanqun
 *	执行数据库操作的action
 */
public class DbDataAction extends BaseAction {

	/**
	 * 
	 */
	private Log logger = LogFactory.getLog(DbDataAction.class);
	private String schema;
	private String dburl;
	private String password;
	private String userName;
	private String dbType;
	private String randomer;
	private String tableName;
	private String limitNum;
	private String sql;
	private String exportState;
	private String exportCount;
	private String catalog;

	public String getCatalog() {
		
		return catalog;
	}

	public void setCatalog(String catalog) {
		try {
			URLDecoder.decode(tableName,"utf-8");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.catalog = catalog;
	}

	public String getExportState() {
		return exportState;
	}

	public void setExportState(String exportState) {
		this.exportState = exportState;
	}

	public String getExportCount() {
		return exportCount;
	}

	public void setExportCount(String exportCount) {
		this.exportCount = exportCount;
	}

	public String getSql() {
		
		return sql;
	}

	public void setSql(String sql) {
		try {
			URLDecoder.decode(sql,"utf-8");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		logger.debug(sql);
		this.sql = sql;
	}

	public String getLimitNum() {
		return limitNum;
	}

	public void setLimitNum(String limitNum) {
		this.limitNum = limitNum;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		try {
			URLDecoder.decode(tableName,"utf-8");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		logger.debug(tableName);
		this.tableName = tableName;
	}

	public String getRandomer() {
		return randomer;
	}

	public void setRandomer(String randomer) {
		this.randomer = randomer;
	}

	public void setSchema(String schema) {
		try {
			URLDecoder.decode(schema,"utf-8");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.schema = schema;
	}

	public String getSchema() {
		return schema;
	}

	public String getDbType() {
		return dbType;
	}

	public void setDbType(String dbType) {
		this.dbType = dbType;
	}

	public String getDburl() {
		return dburl;
	}

	public void setDburl(String dburl) {
		try {
			URLDecoder.decode(dburl,"utf-8");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.dburl = dburl;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		try {
			URLDecoder.decode(password,"utf-8");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.password = password;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		try {
			URLDecoder.decode(userName,"utf-8");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.userName = userName;
	}

	// 启动检测连接是否超时的timer
	public void startTimer() {
		ConnectCheckTimer.starTimer();
	}
	/**
	 * 测试连接是否成功
	 */
	public void connectDbTest() {
		String userId = this.getUser().getUserID();
		Connection conn = Connector.getConnection(userId, dbType, dburl,
				password, userName);
		getResponse().setContentType("text/html;charset=UTF-8");
		if (conn != null) {

			try {
				getResponse().getWriter().write("连接成功");
			} catch (IOException e) {
				throw new RuntimeException(e.getMessage(), e);
			}
			Connector.disconnect(userId);
		} else {
			try {
				getResponse().getWriter().write(
						"连接失败;错误是：" + Connector.getErrorMsg(userId));
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}
	/**
	 * 关闭连接 貌似已经废弃了，改用closeLink()
	 * @param userId
	 */
	public void disconnect(String userId) {
		Connector.disconnect(userId);
	}
	/**
	 * 得到表空间列表
	 */
	public void getSchemas() {
		startTimer();
		String userId = this.getUser().getUserID();
		Connector.updateLastAccessTime(userId);
		Connection conn = Connector.getConnection(userId, dbType, dburl,
				password, userName);
		DataControl dataControl = new DataControl(conn);
		JSONObject alljo = new JSONObject();
		if (conn != null) {
			List schemas = dataControl.getSchemas();
			JSONArray ja = new JSONArray();
			for (int i = 0; i < schemas.size(); i++) {
				JSONObject jo = new JSONObject();
				jo.put("type", "schema");
				jo.put("name", schemas.get(i));
				jo.put("catalog", catalog + "");
				ja.add(jo);
			}

			alljo.put("randomer", randomer);
			alljo.put("array", ja);
			this.sendJSON(alljo.toString());

		} else {

			alljo.put("error", "连接失败;错误是：" + Connector.getErrorMsg(userId));
			this.sendJSON(alljo.toString());

		}

	}
	/**
	 * 得到数据库列表
	 */
	public void getCatalogs() {
		startTimer();
		String userId = this.getUser().getUserID();
		Connector.updateLastAccessTime(userId);
		Connection conn = Connector.getConnection(userId, dbType, dburl,
				password, userName);
		DataControl dataControl = new DataControl(conn);
		JSONObject alljo = new JSONObject();
		if (conn != null) {
			List schemas = dataControl.getCatalogs();
			JSONArray ja = new JSONArray();
			for (int i = 0; i < schemas.size(); i++) {
				JSONObject jo = new JSONObject();
				jo.put("type", "catalog");
				jo.put("name", schemas.get(i));

				ja.add(jo);
			}

			alljo.put("randomer", randomer);
			alljo.put("array", ja);
			this.sendJSON(alljo.toString());

		} else {
			alljo.put("error", "连接失败;错误是：" + Connector.getErrorMsg(userId));
			this.sendJSON(alljo.toString());
		}

	}
	/**
	 * 得到有限制的数据集
	 */
	public void getRecordDataWidthLimit() {
		startTimer();
		String userId = this.getUser().getUserID();
		Connector.updateLastAccessTime(userId);
		Connection conn = Connector.getConnection(userId, dbType, dburl,
				password, userName);
		DataControl dataControl = new DataControl(conn);
		if (conn != null) {
			Map recordDatas = dataControl.getRecordDataWithLimit(tableName,
					Integer.parseInt(limitNum));
			this.sendJSON(JSONObject.fromObject(recordDatas).toString());

		}

	}
	/**
	 * 得到有限制和数据条数的数据集
	 */
	public void getRecordDataWidthLimitAndCount() {
		startTimer();
		Map allData = new HashMap();
		String userId = this.getUser().getUserID();
		Connector.updateLastAccessTime(userId);
		Connection conn = Connector.getConnection(userId, dbType, dburl,
				password, userName);
		DataControl dataControl = new DataControl(conn);
		if (conn != null) {
			logger.debug("tableName"+tableName);
			Map recordDatas = dataControl.getRecordDataWithLimit(tableName,
					Integer.parseInt(limitNum));
			allData.put("tableData", recordDatas);
			int count = dataControl.getAllNumCount(tableName);
			allData.put("count", count);
			this.sendJSON(JSONObject.fromObject(allData).toString());

		}

	}
	/**
	 * 执行sql语句
	 */
	public void executeSql() {
		startTimer();
		String userId = this.getUser().getUserID();
		Connector.updateLastAccessTime(userId);
		Connection conn = Connector.getConnection(userId, dbType, dburl,
				password, userName);
		DataControl dataControl = new DataControl(conn);
		if (conn != null) {
			Map recordDatas = dataControl.executeSql(sql);
			this.sendJSON(JSONObject.fromObject(recordDatas).toString());

		}

	}
	/**
	 * 将excel表格作为输出流的形式返回，用于下载
	 */
	public void exportExcel() {
		startTimer();
		String userId = this.getUser().getUserID();
		Connector.updateLastAccessTime(userId);
		Connection conn = Connector.getConnection(userId, dbType, dburl,
				password, userName);
		DataControl dataControl = new DataControl(conn);
		Map data = null;
		if (conn != null) {
			if (exportState.equalsIgnoreCase("fenye")) {
				int expCount = Integer.parseInt(exportCount);
				if (expCount != -1) {
					data = dataControl.getRecordDataWithLimit(tableName,
							Integer.parseInt(exportCount));
				} else {
					data = dataControl.getRecordDataWithoutLimit(tableName);
				}
			} else if (exportState.equalsIgnoreCase("sql")) {
				data = dataControl.executeQuery(sql);
			}
			ExcelOperator excelOperator = new ExcelOperator();
			if (data != null) {
				HSSFWorkbook wb = excelOperator.getWbByList(
						(List) data.get("data"), (List) data.get("columns"));
				HttpServletResponse response = getResponse();
				response.setContentType("text/html;charset=UTF-8");
				response.setContentType("application/x-msdownload");
				response.setHeader("Content-Disposition",
						"attachment; filename=test.xls");
				ServletOutputStream out = null;
				try {
					out = response.getOutputStream();
				} catch (IOException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}

				try {
					wb.write(out);
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} finally {
					try {
						out.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}

			}

		}
	}
	/**
	 * 得到表的类型，已经该类型所有的数据表
	 */
	public void getElementTypesAndElements() {
		startTimer();
		String userId = this.getUser().getUserID();
		Connector.updateLastAccessTime(userId);
		Connection conn = Connector.getConnection(userId, dbType, dburl,
				password, userName);
		DataControl dataControl = new DataControl(conn);
		JSONObject alljo = new JSONObject();
		if (conn != null) {
			List elementTypes = dataControl.getElementTypes();
			JSONArray jsa = new JSONArray();

			for (int i = 0; i < elementTypes.size(); i++) {
				JSONObject jo = new JSONObject();
				jo.put("type", "elementType");
				jo.put("name", elementTypes.get(i));
				logger.debug("catalog is " + catalog);
				jo.put("catalog", catalog + "");
				ArrayList<String> al = new ArrayList<String>();
				al.add((String) elementTypes.get(i));
				List dg;
				if (schema != null) {
					logger.debug("function 1 ");
					dg = dataControl.getElements(schema, al, 0);
				} else {
					logger.debug("function 2 ");
					dg = dataControl.getElements(catalog, al, 1);
				}
				JSONArray jsa2 = new JSONArray();
				for (int j = 0; j < dg.size(); j++) {
					JSONObject jo2 = new JSONObject();
					jo2.put("type", elementTypes.get(i) + "element");
					jo2.put("name", dg.get(j));
					logger.debug("catalog is " + catalog + "");
					jo2.put("catalog", catalog);
					jsa2.add(jo2);
				}
				if (jsa2.size() > 0) {
					jo.put("children", jsa2);
				}
				jsa.add(jo);
			}

			alljo.put("randomer", randomer);
			alljo.put("array", jsa);
			this.sendJSON(alljo.toString());

		} else {
			alljo.put("error", "连接失败;错误是：" + Connector.getErrorMsg(userId));
			this.sendJSON(alljo.toString());
		}

	}
/**
 * 关闭连接，并移除存储的连接，如果Map中已经没有任何连接，则关闭检测连接超时的timer
 */
	public void closeLink() {
		Set keyset = Connector.connectionMap.keySet();
		logger.debug(keyset.size());
		String userId = this.getUser().getUserID();
		Connection conn = Connector.connectionMap.get(userId);
		if (conn != null) {
			Connector.disconnect(userId);
		}
		// logger.debug("first size"+keyset.size());
		// keyset=Connector.connectionMap.keySet();
		logger.debug(keyset.size());
		if (keyset.size() == 0) {
			logger.debug("stopTimerle");
			ConnectCheckTimer.stopTimer();
		}

	}
}