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;
		}
		
	}
}