Java JdbcUtils类代码示例

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
public  void setTableStructure() throws SQLException{
	DataNode dn = this.getOldDataNodes().get(0);
	Connection con = null;
	try {
		con = DataMigratorUtil.getMysqlConnection(dn);
		List<Map<String, Object>> list = DataMigratorUtil.executeQuery(con, "show create table "+tableName);
		Map<String, Object> m  = list.get(0);
		String str = m.get("Create Table").toString();
		str = str.replaceAll("CREATE TABLE", "Create Table if not exists");
		setTableStructure(str);
	} catch (SQLException e) {
		throw e;
	}finally {
		JdbcUtils.close(con);
	}
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
public DynamicDataSource(MybatisNodeProperties druidNode, DruidProperties defaultDruidProperties, String dataSourceName) throws SQLException {
	this.dataSourceName=dataSourceName;
	DruidProperties master = druidNode.getMaster();
	if (master == null)
		master = new DruidProperties();
	master.merge(defaultDruidProperties).defaultEmpty().setDefaultReadOnly(false);
	this.masterDataSource = master.createDataSource();
	this.masterDataSource.setName(dataSourceName + "-Master");
	List<DruidProperties> slaves = druidNode.getSlaves();
	if (slaves != null && !slaves.isEmpty()) {
		for (int i = 0; i < slaves.size(); i++) {
			DruidProperties slave = slaves.get(i);
			if (slave == null)
				continue;
			slave.merge(defaultDruidProperties).defaultEmpty().setDefaultReadOnly(true);
			String slaveDatasourceName = dataSourceName + "-Slave-" + i;
			this.slavesDataSourceNames.add(slaveDatasourceName);
			DruidDataSource datasourc = slave.createDataSource();
			datasourc.setName(slaveDatasourceName);
			this.slaveDataSources.put(slaveDatasourceName, datasourc);
		}
	}
	String rawUrl=master.getUrl();
	String dbType = JdbcUtils.getDbType(rawUrl,null);
	this.dialect=Dialect.valoueOfName(dbType);
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
public static PublicKey getPublicKeyByX509(String x509File) {
	if (x509File == null || x509File.length() == 0) {
		return ConfigTools.getPublicKey(null);
	}

	FileInputStream in = null;
	try {
		in = new FileInputStream(x509File);

		CertificateFactory factory = CertificateFactory
				.getInstance("X.509");
		Certificate cer = factory.generateCertificate(in);
		return cer.getPublicKey();
	} catch (Exception e) {
		throw new IllegalArgumentException("Failed to get public key", e);
	} finally {
		JdbcUtils.close(in);
	}
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
public static PublicKey getPublicKeyByPublicKeyFile(String publicKeyFile) {
	if (publicKeyFile == null || publicKeyFile.length() == 0) {
		return ConfigTools.getPublicKey(null);
	}

	FileInputStream in = null;
	try {
		in = new FileInputStream(publicKeyFile);
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		int len = 0;
		byte[] b = new byte[512 / 8];
		while ((len = in.read(b)) != -1) {
			out.write(b, 0, len);
		}

		byte[] publicKeyBytes = out.toByteArray();
		X509EncodedKeySpec spec = new X509EncodedKeySpec(publicKeyBytes);
		KeyFactory factory = KeyFactory.getInstance("RSA");
		return factory.generatePublic(spec);
	} catch (Exception e) {
		throw new IllegalArgumentException("Failed to get public key", e);
	} finally {
		JdbcUtils.close(in);
	}
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
@Override
public void run() {
	String data = "";
	long offset = 0;
	Connection con = null;
	try {
		long start = System.currentTimeMillis();
		con = DataMigratorUtil.getMysqlConnection(srcDn);
		if(tableInfo.isExpantion()){
			deleteDataDependFile(data, offset, con);
		}else{
			//缩容，移除的节点直接truncate删除数据，非移除的节点按照临时文件的中值进行删除操作
			List<DataNode> list = tableInfo.getRemovedDataNodes();
			boolean isRemovedDn = false;
			for(DataNode dn:list){
				if(srcDn.equals(dn)){
					isRemovedDn = true;
				}
			}
			if(isRemovedDn){
				String sql = "truncate "+tableInfo.getTableName();
				JdbcUtils.execute(con, sql, new ArrayList<>());
			}else{
				deleteDataDependFile(data, offset, con);
			}
		}
		long end = System.currentTimeMillis();
		System.out.println(tableInfo.getSchemaAndTableName()+" clean dataNode "+srcDn.getName()+" completed in "+(end-start)+"ms");
		
	} catch (Exception e) {
		String errMessage = srcDn.toString()+":"+"clean data error!";
		LOGGER.error(errMessage, e);
		tableInfo.setError(true);
		tableInfo.getErrMessage().append(errMessage+"\n");
	} finally{
		JdbcUtils.close(con);
	}
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
private void deleteDataDependFile(String data,long offset,Connection con) throws IOException, SQLException{
	while((data=DataMigratorUtil.readData(tempFile,offset,DataMigrator.margs.getQueryPageSize())).length()>0){
		offset += data.getBytes().length;
		if(data.startsWith(",")){
			data = data.substring(1, data.length());
		}
		if(data.endsWith(",")){
			data = data.substring(0,data.length()-1);
		}
		String sql = "delete from "+tableInfo.getTableName()+" where "+tableInfo.getColumn()+" in ("+data+")";
		JdbcUtils.execute(con, sql, new ArrayList<>());
	}
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
public static long querySize(DataNode dn,String tableName) throws SQLException{
	List<Map<String, Object>> list=null;
	long size = 0L;
	Connection con = null;
	try {
		con =  getMysqlConnection(dn);
		list = executeQuery(con, "select count(1) size from "+tableName);
		size = (long) list.get(0).get("size");
	} catch (SQLException e) {
		throw e;
	}finally{
		JdbcUtils.close(con);
	}
	return size;
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
public static void createTable(DataNode dn,String table) throws SQLException{
	Connection con = null;
	try {
		con =  getMysqlConnection(dn);
		JdbcUtils.execute(con, table, new ArrayList<>());
	} catch (SQLException e) {
		throw e;
	}finally{
		JdbcUtils.close(con);
	}
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
protected String getDbType(DruidProperties nodeProperties, DruidProperties defaultProperties) {
	String rawUrl = nodeProperties.getUrl();
	if (StringUtils.isEmpty(nodeProperties.getUrl())) {
		rawUrl = defaultProperties.getUrl();
	}
	return JdbcUtils.getDbType(rawUrl, null);
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
/**
* 根据数据源获取数据库类型 <br/>
* @author jingma
* @param dataSource
* @return
*/
public static String getDbtypeByDatasource(DataSource dataSource) {
    String dbType = null;
    if(dataSource instanceof DruidDataSource){
        dbType = JdbcUtils.getDbType(((DruidDataSource)dataSource).getUrl(), null);
    }else if(dataSource instanceof SJDataSource){
        dbType = JdbcUtils.getDbType(
                ((SJDataSource)dataSource).toString().split("::::")[1],
                null);
    }
    return dbType;
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
/**
* 根据数据源获取数据库类型 <br/>
* @author jingma
* @param dataSource
* @return
*/
public static String getDbtypeByDatasource(DataSource dataSource) {
    String dbType = null;
    if(dataSource instanceof DruidDataSource){
        dbType = ((DruidDataSource)dataSource).getDbType();
    }else if(dataSource instanceof SJDataSource){
        dbType = JdbcUtils.getDbType(
                ((SJDataSource)dataSource).toString().split("::::")[1],
                null);
    }
    return dbType;
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
public static List<SQLStatement> parse(String sql) {
    /**
     SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcUtils.MYSQL);
     return parser.parseStatementList();
     */
    return SQLUtils.toStatementList(sql, JdbcUtils.MYSQL);
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
public static TableBindPlugin tableBindPlugin(
        String configName,
        DruidPlugin druidPlugin,
        Properties dbProp
) {
    String dbUrl = dbProp.getProperty(GojaPropConst.DBURL);
    if (!Strings.isNullOrEmpty(dbUrl)) {
        String dbtype = JdbcUtils.getDbType(dbUrl, StringUtils.EMPTY);
        //  setting db table name like 'dev_info'
        final TableBindPlugin atbp = new TableBindPlugin(configName, druidPlugin);

        if (!StringUtils.equals(dbtype, JdbcConstants.MYSQL)) {
            if (StringUtils.equals(dbtype, JdbcConstants.ORACLE)) {
                atbp.setDialect(new OracleDialect());
                atbp.setContainerFactory(new CaseInsensitiveContainerFactory(true));
            } else if (StringUtils.equals(dbtype, JdbcConstants.POSTGRESQL)) {
                atbp.setDialect(new PostgreSqlDialect());
                atbp.setContainerFactory(new CaseInsensitiveContainerFactory(true));
            } else if (StringUtils.equals(dbtype, JdbcConstants.H2)) {
                atbp.setDialect(new AnsiSqlDialect());
                atbp.setContainerFactory(new CaseInsensitiveContainerFactory(true));
            } else if (StringUtils.equals(dbtype, "sqlite")) {
                atbp.setDialect(new Sqlite3Dialect());
            } else if (StringUtils.equals(dbtype, JdbcConstants.JTDS)) {
                atbp.setDialect(new SqlServerDialect());
            } else {
                System.err.println("database type is use mysql.");
            }
        }
        atbp.setShowSql(GojaConfig.getApplicationMode().isDev());
        return atbp;
    }
    return null;
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
@Override
public void run() {
	if(tableInfo.isError()) {
		return;
	}
	
	long[] count = new long[newDnSize];
   	int page=0;
   	List<Map<String, Object>> list=null;
	
   	Connection con = null;
	try {
		con = DataMigratorUtil.getMysqlConnection(srcDn);
		//创建空的中间临时文件
		createTempFiles();
		
		//暂时只实现mysql的分页查询
		list = DataMigratorUtil.executeQuery(con, "select " 
		        + column+ " from " + tableName + " limit ?,?", page++ * pageSize,
		        pageSize);
		int total = 0; //该节点表总数据量
		
		while (!CollectionUtil.isEmpty(list)) {
			if(tableInfo.isError()) {
				return;
			}
			flushData(false);
   			for(int i=0,l=list.size();i<l;i++){
   				Map<String, Object> sf=list.get(i);
   				String filedVal = sf.get(column).toString();
   				Integer newIndex=alg.calculate(filedVal);
   				total++;
   				DataNode newDn = newDnList.get(newIndex);
   				if(!srcDn.equals(newDn)){
   					count[newIndex]++;
   					map.get(newDn).append(filedVal+",");
   				}
   			}
   			list = DataMigratorUtil.executeQuery(con, "select "
                       + column + " from " + tableName + " limit ?,?", page++ * pageSize,
                       pageSize);
   		}
		flushData(true);
		statisticalData(total,count);
	} catch (Exception e) {
		//发生错误，终止此拆分表所有节点线程任务，记录错误信息，退出此拆分表迁移任务
		String message = "["+tableInfo.getSchemaName()+":"+tableName+"]  src dataNode: "+srcDn.getUrl()+
				" prepare temp files is failed! this table's migrator will exit! "+e.getMessage();
		tableInfo.setError(true);
		tableInfo.setErrMessage(message);
		System.out.println(message);
		LOGGER.error(message, e);
	}finally{
		JdbcUtils.close(con);
	}
}
 

import com.alibaba.druid.util.JdbcUtils; //导入依赖的package包/类
public static List<Map<String, Object>> executeQuery(Connection conn, String sql,Object... parameters) throws SQLException{
	return JdbcUtils.executeQuery(conn, sql, Arrays.asList(parameters));
}