数据库工具java部分

之前花两个星期写了一个简易的数据库工具，还有好多功能没有做，这是界面部分，左上角的下拉框来选择数据库，目前支持的数据库是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 blocke.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 blocke.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 blocke1.printStackTrace();}List<ColumnsInfo> columnsInfoList = new ArrayList<ColumnsInfo>();try {columnsCount = rs.getMetaData().getColumnCount();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.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 blocke.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 blocke.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 blocke.printStackTrace();}}if(ps!=null){try {ps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blockqueryData.put("error", e.getMessage());influenceCount = -1;e.printStackTrace();}if (ps != null) {try {influenceCount = ps.executeUpdate(sql);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();queryData.put("error", e.getMessage());influenceCount = -1;}finally{try {ps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.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 blocke.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 blocke.printStackTrace();}}if(ps!=null){try {ps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.printStackTrace();}this.userName = userName;}// 启动检测连接是否超时的timerpublic 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 blocke.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 blocke1.printStackTrace();}try {wb.write(out);} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {try {out.close();} catch (IOException e) {// TODO Auto-generated catch blocke.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();}}}