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

JDBCTool jdbc数据库连接类

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

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;// 返回执行情况     }<br><br>

    public ResultSet executeQuery(String sql) {
        ResultSet rs = null;
        if (this.conn == null) {
            this.creatConnection();
        }
        try {
            Statement stmt = this.conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(1024);
            rs = stmt.executeQuery(sql);
        } catch (SQLException var4) {
            log.info("SQL查询失败!");
            log.info(var4.getMessage());
        }
        return rs;
    }

    public void closeConnection() {
        if (this.conn != null) {
            try {
                this.conn.close();
            } catch (SQLException var5) {
                var5.printStackTrace();
            } finally {
                this.conn = null;
            }
        }
    }
}

 

c3p0连接池配置

db.properties文件

?

1 2 3 4

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文件

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

<?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账户信息

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

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);                                       }<br>　　　　　　　　　　　　jdbc.closeConnection();} } }