java 实现抽取数据库_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文件

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">

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 row= new BeanPropertyRowMapper<>(DataBaseDao.class);

List database=(List) j.query(sql,row);

List segment = new ArrayList();

for(int i=0;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();

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();

}

}

if(commit!=0){

j.batchUpdate(insert_sql,segment);

}

jdbc.closeConnection();

} } }