package com.walker.connector.db;
|
|
import com.walker.connector.support.DatabaseConnector;
|
import com.walker.db.DatabaseType;
|
import com.walker.db.TableInfo;
|
import com.walker.jdbc.util.StringSqlUtils;
|
import org.springframework.jdbc.core.RowMapper;
|
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
|
|
import javax.sql.DataSource;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* MySQL数据库连接器实现
|
* @author shikeying
|
* @date 2015年12月15日
|
*
|
*/
|
public class MySqlConnector extends DatabaseConnector {
|
|
// private static final String SQL_GET_TBL_COUNT = "SELECT COUNT(*) cnt FROM information_schema.`TABLES`"
|
// + " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";
|
public static final String SQL_TABLES_ROW = "SELECT TABLE_NAME tname, TABLE_COMMENT summary, TABLE_ROWS trows"
|
+ " FROM information_schema.`TABLES` "
|
+ " WHERE TABLE_SCHEMA = :schema AND TABLE_TYPE = 'BASE TABLE'"
|
+ " AND TABLE_NAME in (:ids)";
|
// + " AND TABLE_NAME in ('base_area_44','rec_main_consume_856')";
|
|
@Override
|
protected DatabaseType getDatabaseType() {
|
return DatabaseType.MYSQL;
|
}
|
|
public MySqlConnector(){}
|
|
public MySqlConnector(DataSource dataSource){
|
super(dataSource);
|
}
|
|
public static void main(String[] args){
|
DatabaseConnector dbConnector = new MySqlConnector();
|
// DatabaseConnector dbConnector = new OracleConnector();
|
// dbConnector.setUrl("192.168.0.142");
|
dbConnector.setUrl("127.0.0.1");
|
dbConnector.setPort(3307);
|
// dbConnector.setServiceName("orcl");
|
dbConnector.setServiceName("ecard_dist_1?characterEncoding=UTF-8");
|
// 不设置数据库名字，可以连接数据库服务，并创建数据库
|
// dbConnector.setManageMode();
|
|
Map<String, String> parameters = new HashMap<String, String>(5);
|
parameters.put(DatabaseConnector.OPTION_USER, "root");
|
parameters.put(DatabaseConnector.OPTION_PASSWORD, "123456");
|
// parameters.put(DatabaseConnector.OPTION_USER, "ecard_856");
|
// parameters.put(DatabaseConnector.OPTION_PASSWORD, "ecard_856");
|
parameters.put(DatabaseConnector.OPTION_MAX_ACTIVE, "5");
|
parameters.put(DatabaseConnector.OPTION_MAX_IDLE, "2");
|
parameters.put(DatabaseConnector.OPTION_INIT_SIZE, "2");
|
|
dbConnector.setParameters(parameters);
|
dbConnector.initialize();
|
|
// String sql = "select * from s_log limit 10";
|
// String sql = "select * from base_area where VER > 0 and ROWNUM <= 10";
|
try {
|
// List<Map> list = (List<Map>)dbConnector.invoke(sql, new Object[]{});
|
// if(list != null){
|
// for(Map log : list){
|
// System.out.println(log);
|
// }
|
// }
|
TableInfoMapper tableInfoMapper = new TableInfoMapper();
|
|
List<String> tables = new ArrayList<String>();
|
tables.add("base_area_44");
|
tables.add("rec_main_consume_856");
|
|
String repoName = StringSqlUtils.getMySQLSchemaName(dbConnector.getServiceName());
|
System.out.println("serviceName: " + repoName);
|
|
MapSqlParameterSource nameSet = new MapSqlParameterSource();
|
nameSet.addValue("schema", repoName);
|
nameSet.addValue("ids", tables);
|
|
List<TableInfo> list = (List<TableInfo>)dbConnector.queryForRowMapper(SQL_TABLES_ROW
|
, tableInfoMapper
|
, nameSet);
|
|
for(TableInfo ti : list){
|
System.out.println(ti);
|
}
|
System.out.println("=================");
|
// dbConnector.execute("CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;");
|
// dbConnector.execute("CREATE DATABASE test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;");
|
// dbConnector.exeCreateTable("CREATE TABLE IF NOT EXISTS `test_tb` (`id` int(11) NOT NULL AUTO_INCREMENT,`pid` int(11) NOT NULL,`url` varchar(100) CHARACTER SET utf8 NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");
|
} catch (Exception e) {
|
if(e.getCause() instanceof SQLException){
|
System.out.println("数据库已经存在，或者没有权限访问");
|
}
|
e.printStackTrace();
|
}
|
// int result = dbConnector.queryTableSize("walkersoft-share-marks");
|
// System.out.println("表数量：" + result);
|
}
|
|
private static class TableInfoMapper implements RowMapper<TableInfo>{
|
@Override
|
public TableInfo mapRow(ResultSet rs, int arg1) throws SQLException {
|
TableInfo ti = new TableInfo();
|
ti.setName(rs.getString("tname"));
|
ti.setSummary(rs.getString("summary"));
|
ti.setRows(rs.getLong("trows"));
|
return ti;
|
}
|
|
}
|
}
|
