Java tutorial
/* * Copyright (C) 2010-2101 Alibaba Group Holding Limited. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.alibaba.otter.manager.biz.utils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import javax.sql.DataSource; import org.apache.commons.lang.StringUtils; import org.apache.ddlutils.model.Table; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.jdbc.core.JdbcTemplate; import com.alibaba.otter.manager.biz.common.DataSourceCreator; import com.alibaba.otter.manager.biz.config.datamediasource.DataMediaSourceService; import com.alibaba.otter.shared.common.model.config.ConfigHelper; import com.alibaba.otter.shared.common.model.config.ModeValueFilter; import com.alibaba.otter.shared.common.model.config.data.DataMedia.ModeValue; import com.alibaba.otter.shared.common.model.config.data.DataMediaSource; import com.alibaba.otter.shared.common.model.config.data.DataMediaType; import com.alibaba.otter.shared.common.model.config.data.db.DbMediaSource; import com.alibaba.otter.shared.common.utils.meta.DdlSchemaFilter; import com.alibaba.otter.shared.common.utils.meta.DdlTableNameFilter; import com.alibaba.otter.shared.common.utils.meta.DdlUtils; /** * @author simon 2011-11-25 ?04:57:55 */ public class DataSourceChecker { private static final Logger logger = LoggerFactory.getLogger(DataSourceChecker.class); private DataMediaSourceService dataMediaSourceService; private DataSourceCreator dataSourceCreator; // private static final String MYSQL_FLAG = "mysql"; // private static final String ORACLE_FLAG = "oracle"; // ?jdbc-url?? // private static final String DBTYPE_CONFLICT = // "\u9009\u62e9\u7684\u6570\u636e\u5e93\u7c7b\u578b\u548cjdbc-url\u4e0d\u5339\u914d"; // ?,??! private static final String DATABASE_SUCCESS = "\u606d\u559c,\u6570\u636e\u5e93\u901a\u8fc7\u9a8c\u8bc1!"; // ,??,?! private static final String DATABASE_FAIL = "\u62b1\u6b49,\u6570\u636e\u5e93\u672a\u901a\u8fc7\u9a8c\u8bc1,\u8bf7\u68c0\u67e5\u76f8\u5173\u914d\u7f6e!"; // ?,select??,??! private static final String TABLE_SUCCESS = "\u606d\u559c,select\u64cd\u4f5c\u6210\u529f,\u6743\u9650\u6b63\u5e38!"; // select?,???! private static final String TABLE_FAIL = "\u62b1\u6b49,\u64cd\u4f5c\u62a5\u9519,\u8bf7\u68c0\u67e5\u6743\u9650\u914d\u7f6e!"; // ?,??! private static final String ENCODE_QUERY_ERROR = "\u6267\u884cSQL\u51fa\u9519,\u8bf7\u68c0\u67e5\u6570\u636e\u5e93\u7c7b\u578b\u662f\u5426\u9009\u62e9\u6b63\u786e!"; // ,??,?: private static final String ENCODE_FAIL = "\u62b1\u6b49,\u5b57\u7b26\u96c6\u4e0d\u5339\u914d,\u5b9e\u9645\u6570\u636e\u5e93\u9ed8\u8ba4\u5b57\u7b26\u96c6\u4e3a:"; // SELECT? private static final String SELECT_FAIL = "SELECT\u672a\u6210\u529f"; // DELETE? // private static final String DELETE_FAIL = "DELETE\u672a\u6210\u529f"; // INSERT? // private static final String INSERT_FAIL = "INSERT\u672a\u6210\u529f"; // ??? // private static final String SPLIT_INDEX_FAIL = // "\u5206\u5e93\u524d\u7f00\u5fc5\u987b\u5927\u4e8e\u540e\u7f00\uff0c\u4e14\u4e0d\u80fd\u4e3a\u8d1f\u6570"; private void closeConnection(Connection conn) { closeConnection(conn, null, null); } private void closeConnection(Connection conn, Statement st) { closeConnection(conn, st, null); } private void closeConnection(Connection conn, Statement st, ResultSet rs) { try { if (null != rs) { rs.close(); } if (null != st) { st.close(); } if (null != conn) { conn.close(); } } catch (SQLException e) { logger.error("", e); } } @SuppressWarnings("resource") public String check(String url, String username, String password, String encode, String sourceType) { Connection conn = null; Statement stmt = null; ResultSet rs = null; // boolean typeConflict = true; // if ((sourceType.toLowerCase().equals(MYSQL_FLAG) && // url.toLowerCase().contains(MYSQL_FLAG)) // || sourceType.toLowerCase().equals(ORACLE_FLAG) && // url.toLowerCase().contains(ORACLE_FLAG)) { // typeConflict = false; // } // // if (typeConflict) { // return DBTYPE_CONFLICT; // } DataSource dataSource = null; try { DbMediaSource dbMediaSource = new DbMediaSource(); dbMediaSource.setUrl(url); dbMediaSource.setUsername(username); dbMediaSource.setPassword(password); dbMediaSource.setEncode(encode); if (sourceType.equalsIgnoreCase("MYSQL")) { dbMediaSource.setType(DataMediaType.MYSQL); dbMediaSource.setDriver("com.mysql.jdbc.Driver"); } else if (sourceType.equalsIgnoreCase("ORACLE")) { dbMediaSource.setType(DataMediaType.ORACLE); dbMediaSource.setDriver("oracle.jdbc.driver.OracleDriver"); } dataSource = dataSourceCreator.createDataSource(dbMediaSource); try { conn = dataSource.getConnection(); } catch (Exception e) { logger.error("check error!", e); } if (null == conn) { return DATABASE_FAIL; } stmt = conn.createStatement(); String sql = null; if (sourceType.equals("MYSQL")) { sql = "SHOW VARIABLES LIKE 'character_set_database'"; } else if (sourceType.equals("ORACLE")) { // sql // ="select * from V$NLS_PARAMETERS where parameter in('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET')"; sql = "select * from V$NLS_PARAMETERS where parameter in('NLS_CHARACTERSET')"; } rs = stmt.executeQuery(sql); while (rs.next()) { String defaultEncode = null; if (sourceType.equals("MYSQL")) { defaultEncode = ((String) rs.getObject(2)).toLowerCase(); defaultEncode = defaultEncode.equals("iso-8859-1") ? "latin1" : defaultEncode; if (!encode.toLowerCase().equals(defaultEncode)) { return ENCODE_FAIL + defaultEncode; } } else if (sourceType.equals("ORACLE")) { // ORACLE????? defaultEncode = ((String) rs.getObject(2)).toLowerCase(); defaultEncode = defaultEncode.equalsIgnoreCase("zhs16gbk") ? "gbk" : defaultEncode; defaultEncode = defaultEncode.equalsIgnoreCase("us7ascii") ? "iso-8859-1" : defaultEncode; if (!encode.toLowerCase().equals(defaultEncode)) { return ENCODE_FAIL + defaultEncode; } } } } catch (SQLException se) { logger.error("check error!", se); return ENCODE_QUERY_ERROR; } catch (Exception e) { logger.error("check error!", e); return DATABASE_FAIL; } finally { closeConnection(conn); dataSourceCreator.destroyDataSource(dataSource); } return DATABASE_SUCCESS; } public String checkMap(String namespace, String name, Long dataSourceId) { Connection conn = null; Statement stmt = null; DataMediaSource source = dataMediaSourceService.findById(dataSourceId); DataSource dataSource = null; try { DbMediaSource dbMediaSource = (DbMediaSource) source; dataSource = dataSourceCreator.createDataSource(dbMediaSource); // conn = dataSource.getConnection(); // if (null == conn) { // return DATABASE_FAIL; // } ModeValue namespaceValue = ConfigHelper.parseMode(namespace); ModeValue nameValue = ConfigHelper.parseMode(name); String tempNamespace = namespaceValue.getSingleValue(); String tempName = nameValue.getSingleValue(); // String descSql = "desc " + tempNamespace + "." + tempName; // stmt = conn.createStatement(); try { Table table = DdlUtils.findTable(new JdbcTemplate(dataSource), tempNamespace, tempNamespace, tempName); if (table == null) { return SELECT_FAIL; } } catch (SQLException se) { logger.error("check error!", se); return SELECT_FAIL; } catch (Exception e) { logger.error("check error!", e); return SELECT_FAIL; } // String selectSql = "SELECT * from " + tempNamespace + "." + // tempName + " where 1 = 0"; // String insertSql = "INSERT INTO " + tempNamespace + "." + // tempName + " select * from "; // insertSql += "( SELECT * from " + tempNamespace + "." + tempName // + ") table2 where 1 = 0"; // String deleteSql = "DELETE from " + tempNamespace + "." + // tempName + " where 1 = 0"; // // stmt = conn.createStatement(); // // try { // stmt.executeQuery(selectSql); // } catch (SQLException se) { // return SELECT_FAIL; // } // // try { // stmt.execute(insertSql); // } catch (SQLException se) { // return INSERT_FAIL; // } // // try { // stmt.execute(deleteSql); // } catch (SQLException se) { // return DELETE_FAIL; // } } finally { closeConnection(conn, stmt); dataSourceCreator.destroyDataSource(dataSource); } return TABLE_SUCCESS; } public String checkNamespaceTables(final String namespace, final String name, final Long dataSourceId) { DataSource dataSource = null; try { DataMediaSource source = dataMediaSourceService.findById(dataSourceId); DbMediaSource dbMediaSource = (DbMediaSource) source; dataSource = dataSourceCreator.createDataSource(dbMediaSource); JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); List<String> schemaList; { ModeValue mode = ConfigHelper.parseMode(namespace); String schemaPattern = ConfigHelper.makeSQLPattern(mode, namespace); final ModeValueFilter modeValueFilter = ConfigHelper.makeModeValueFilter(mode, namespace); if (source.getType().isOracle()) { schemaList = Arrays.asList(namespace); } else { schemaList = DdlUtils.findSchemas(jdbcTemplate, schemaPattern, new DdlSchemaFilter() { @Override public boolean accept(String schemaName) { return modeValueFilter.accept(schemaName); } }); } } final List<String> matchSchemaTables = new ArrayList<String>(); matchSchemaTables.add("Find schema and tables:"); if (schemaList != null) { ModeValue mode = ConfigHelper.parseMode(name); String tableNamePattern = ConfigHelper.makeSQLPattern(mode, name); final ModeValueFilter modeValueFilter = ConfigHelper.makeModeValueFilter(mode, name); for (String schema : schemaList) { DdlUtils.findTables(jdbcTemplate, schema, schema, tableNamePattern, null, new DdlTableNameFilter() { @Override public boolean accept(String catalogName, String schemaName, String tableName) { if (modeValueFilter.accept(tableName)) { matchSchemaTables.add(schemaName + "." + tableName); } return false; } }); } } if (matchSchemaTables.size() == 1) { return TABLE_FAIL; } return StringUtils.join(matchSchemaTables, "<br>\n"); } catch (Exception e) { logger.error("check error!", e); return TABLE_FAIL; } finally { dataSourceCreator.destroyDataSource(dataSource); } } public void setDataMediaSourceService(DataMediaSourceService dataMediaSourceService) { this.dataMediaSourceService = dataMediaSourceService; } public void setDataSourceCreator(DataSourceCreator dataSourceCreator) { this.dataSourceCreator = dataSourceCreator; } }