java（抽取所有数据库的dba_segments信息，进行统计分析）

JDBCTool jdbc数据库连接类

package com.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCTool {
	private final String DRIVER = "oracle.jdbc.driver.OracleDriver"; 

	private String URL ;

	private final String USERNAME ="SYSTEM";

	private String PASSWORD;

	public String getURL() {
		return URL;
	}

	public void setURL(String uRL) {
		URL = uRL;
	}

	public String getPASSWORD() {
		return PASSWORD;
	}

	public void setPASSWORD(String pASSWORD) {
		PASSWORD = pASSWORD;
	}

	private Connection conn = null;

	public JDBCTool() {
		try {
			Class.forName(DRIVER).newInstance(); // 加载数据库驱动
		} catch (Exception ex) {
			System.out.println("加载数据库驱动失败!");
		}
	}

	public boolean creatConnection() {// 用来创建数据库连接
		boolean isCreated = true;// 默认创建成功
		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);// 创建数据库连接
			
			
		} catch (SQLException e) {
			isCreated = false;// 创建失败
			System.out.print(e);
		
		}
		return isCreated;// 返回创建情况
	}

	public boolean executeUpdate(String sql) {// 用来插入、修改和删除记录
		boolean isUpdate = true;// 默认执行成功
		if (conn == null)
			creatConnection();
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			isUpdate = false;// 执行失败
		}
		return isUpdate;// 返回执行情况
	}

	public ResultSet executeQuery(String sql) {// 用来查询记录
		ResultSet rs = null;
		if (conn == null)
			creatConnection();
		try {
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;// 返回查询结果集
	}

	public void closeConnection() {// 用来关闭数据库连接
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				conn = null;
			}
		}
	}


}

c3p0连接池配置

db.properties文件

jdbc.jdbcUrl=jdbc:oracle:thin:@192.xx.xx.xx:21521/xxxx
jdbc.driverClass=oracle.jdbc.driver.OracleDriver
jdbc.user=xxxx
jdbc.password=xxxxxx

bean.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-4.3.xsd">
    <!-- 配置数据源    -->
    <context:property-placeholder location="classpath:db.properties"/>
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass}"></property>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="minPoolSize" value="3"></property>
        <property name="maxPoolSize" value="8"></property>
        <property name="acquireRetryAttempts" value="3"></property>
        <property name="testConnectionOnCheckin" value="true"></property>
        <property name="checkoutTimeout" value="10000"></property>
        <property name="idleConnectionTestPeriod" value="30"></property>
        <property name="breakAfterAcquireFailure" value="false"></property>
        
    </bean>
    <!-- 配置jdbctemplate实例 -->
    <bean id="jdbctemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

　　

main方法

db_info表保存了数据库的tsn连接串信息

db_user表保存每个数据库对应的system账户信息

package com.itsm;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.dao.DataBaseDao;
import com.tools.JDBCTool;

public class sync_segment {

	public static void main(String[] args) throws SQLException {
		   String xmlpath = "bean.xml";
	       ApplicationContext context = new ClassPathXmlApplicationContext(xmlpath);
	       JdbcTemplate j = (JdbcTemplate) context.getBean("jdbctemplate");
	       String sql="select f_decrypt_number(PASSWORD) password,'jdbc:oracle:thin:@'||replace(a.scanip,' ','') url,a.db_id from db_info a,db_users b where a.db_id=b.db_id and b.username='SYSTEM'"+
		           " and a.status_code='1' and a.database_type='ORACLE' and A.DB_REGION='生产' and a.status_code='1'  order by a.database_name ";
	        RowMapper<DataBaseDao> row= new BeanPropertyRowMapper<>(DataBaseDao.class);    
	        List<DataBaseDao> database=(List<DataBaseDao>) j.query(sql,row);
	        List<Object[]> segment = new ArrayList<Object[]>();
	        for(int i=0;i<database.size();i++){
	        	JDBCTool jdbc=new JDBCTool();
				jdbc.setPASSWORD(database.get(i).getPassword());
				jdbc.setURL(database.get(i).getUrl());
			    boolean sign=jdbc.creatConnection();
			    if (sign==false){
			    System.out.println(database.get(i).getDb_id()+':'+database.get(i).getUrl()+" FALSE");	
			    continue;
			    }  
			    System.out.println(database.get(i).getDb_id()+':'+database.get(i).getUrl());	
			    ResultSet rs = jdbc.executeQuery(
			    		"select /*+parallel 4*/ OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,"
			    		+ "TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments"
			    		);
			    int commit=0;
			    segment=new ArrayList<Object[]>();
			    String insert_sql="insert into issue.db_segment_detial (db_id,OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,"
		        		+ "BYTES,BLOCKS,EXTENTS)values(?,?,?,?,?,?,?,?,?)";
			    while(rs.next()){
			    	segment.add(new Object[]{database.get(i).getDb_id(),rs.getString(1),rs.getString(2),rs.getString(3)
			    			,rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8)
			    	});
			    	commit++;
			    	if(commit==100000){
			    		j.batchUpdate(insert_sql,segment); 
			    		commit=0;
			    		segment=new ArrayList<Object[]>();
			    	}
			    }
			    if(commit!=0){
			    	j.batchUpdate(insert_sql,segment);
			    	
			    }
　　　　　　　　　　　　jdbc.closeConnection();

} } }