数据库分年度统计数据量

不得不说还有半年就要毕业了，还有写审计数据分析报告。

正题，分年度统计数据量，思路：养老保险领域数据库有14个每个有几百张表，有的表有时间属性，有的没有时间属性，

第一步，根据数据库自带函数统计每张表数据量

第二步，人工挑选每张表时间属性字段并配置表格HELP1

第三步，计算当前年份记录数在整张表的百分比，统计即可。

数据库 DB2

语言 Java

贴代码

package com.Audit.test;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;










public class test1 {


/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub


String domain = "606";
String object[] = {"shengzhi","haerbin","qiqihaer","jixi","hegang","shuangyashan","daqing","yichun","jiamusi","qitaihe","mudanjiang","hehei","suihua","daxinganling"};
int pubdata = 16640;
String SchemaName = "administrator";
String objectDBName[] = {"GB_DB1","GB_DB5","yl2302","yl2303","yl2304","yl2305","yl2306","yl2307","yl2308","yl2309","yl2310","yl2311","yl2312","yl2327"};
test1 t1 = new test1();
for(int i = 0;i<object.length;i++){
t1.execute(domain, object[i], pubdata, objectDBName[i],SchemaName);
}








}

public void execute (String domain,String object,int pubdata,String objectDBName,String SchemaName){
List<List<String>> queryResultList1 = new ArrayList<List<String>>();


//conn1
Connection conn1 = test1.getConn1();
Statement stmt1 = test1.getStatement(conn1);
// String sql = "Select c.c1, c.cc ,c.c2*d.d2"
// +"From ( Select a.a1 c1, a.aa  cc,cast(a.a2 as float)/cast(b.b2 as float) c2"
// +"From  ( SELECT  'AA02' as a1 , AA02. AAE001 aa,count(*) a2 FROM  AA02  Group by  AA02. AAE001"
// +") a inner join ("
// +"SELECT  'AA02' as b1,count(*) b2 FROM  AA02 ) b on a.a1=b.b1 ) c inner join "
// +"(select TABNAME d1,SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+XML_OBJECT_P_SIZE)  d2  "
// +"FROM SYSIBMADM.ADMINTABINFO T  WHERE TABSCHEMA='ADMINISTRATOR' group by TABNAME) d on c.c1 = d.d1;";

// String sql =""+
// "Select c.c1, c.cc ,c.c2*d.d2 "+
// "From ( Select a.a1 c1, a.aa  cc,cast(a.a2 as float)/cast(b.b2 as float) c2 "+
// "From  ( SELECT  '"+tableName+"' as a1 , "+tableName+"."+timeName+" aa,count(*) a2 FROM  "+tableName+"  Group by  "+tableName+"."+timeName+" "+
// ") a inner join ( "+
// "SELECT  '"+tableName+"' as b1,count(*) b2 FROM  "+tableName+" ) b on a.a1=b.b1 ) c inner join "+
// "(select TABNAME d1,SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+XML_OBJECT_P_SIZE)  d2  "+
// "FROM SYSIBMADM.ADMINTABINFO T  WHERE TABSCHEMA='ADMINISTRATOR' group by TABNAME) d on c.c1 = d.d1 ";
String sql1 = "select * from HELP1 where domain = '"+domain+"' and object = '"+object+"'";
ResultSet rs1 = test1.getResultSet(stmt1, sql1);
try {



ResultSetMetaData metaData = rs1.getMetaData();
int columnCount = metaData.getColumnCount();


while (rs1.next()) {
List<String> tupleList = new ArrayList<String>();
for (int index = 1; index <= columnCount; index++) {
tupleList.add(rs1.getString(index));
}
queryResultList1.add(tupleList);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
test1.close(rs1);
test1.close(stmt1);
test1.close(conn1);
}


for(int i = 0;i<queryResultList1.size();i++){
for(int j = 0;j<queryResultList1.get(i).size();j++){
System.out.print(queryResultList1.get(i).get(j)+" ");
}
System.out.println();
}



int count = queryResultList1.size();//表示多少张表

Map<String,String> map = new LinkedHashMap<String,String>(); 


//进入循环处理表
for(int i= 0;i<count;i++){
List<List<String>> queryResultList = new ArrayList<List<String>>();

String tableName = queryResultList1.get(i).get(0);
String timeName = "";
if(queryResultList1.get(i).get(2).equals("DECIMAL")){
timeName = queryResultList1.get(i).get(1);
}else if(queryResultList1.get(i).get(2).equals("VARCHAR")){
timeName = "Substr("+queryResultList1.get(i).get(1)+",1,4)";
}else if(queryResultList1.get(i).get(2).equals("DATE")){
timeName = "year("+queryResultList1.get(i).get(1)+")";
}else if(queryResultList1.get(i).get(2).equals("TIMESTAMP")){
timeName = "Substr(char("+queryResultList1.get(i).get(1)+"),1,4)";

}


// String timeName = "Substr(AAE002,1,4)";//VARCHAR
// String timeName = "year(AAE030)";//DATE




//conn2
String sql2 =""+
"Select c.c1, c.cc ,round(c.c2*d.d2,2) "+
"From ( Select a.a1 c1, a.aa  cc,cast(a.a2 as float)/cast(b.b2 as float) c2 "+
"From  ( SELECT  '"+tableName+"' as a1 , "+timeName+" aa,count(*) a2 FROM  "+tableName+"  Group by  "+timeName+" "+
") a inner join ( "+
"SELECT  '"+tableName+"' as b1,count(*) b2 FROM  "+tableName+" ) b on a.a1=b.b1 ) c inner join "+
"(select TABNAME d1,SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+XML_OBJECT_P_SIZE)  d2  "+
"FROM SYSIBMADM.ADMINTABINFO T  WHERE TABSCHEMA='ADMINISTRATOR' group by TABNAME) d on c.c1 = d.d1 ";




Connection conn2 = test1.getConn(objectDBName,SchemaName);//领域数据库
Statement stmt2 = test1.getStatement(conn2);

ResultSet rs2 = test1.getResultSet(stmt2, sql2);
try {



ResultSetMetaData metaData = rs2.getMetaData();
int columnCount = metaData.getColumnCount();


while (rs2.next()) {
List<String> tupleList = new ArrayList<String>();
for (int index = 1; index <= columnCount; index++) {
tupleList.add(rs2.getString(index));
}
queryResultList.add(tupleList);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
test1.close(rs2);
test1.close(stmt2);
test1.close(conn2);
}



for(int x = 0;x<queryResultList.size();x++){
for(int j = 0;j<queryResultList.get(x).size();j++){
System.out.print(queryResultList.get(x).get(j)+" ");
}
System.out.println();
}

for(int x = 0;x<queryResultList.size();x++){
if(!map.containsKey(queryResultList.get(x).get(1))){
map.put(queryResultList.get(x).get(1), String.valueOf(Double.parseDouble(queryResultList.get(x).get(2))+pubdata));//pubdata所有年度公用数据量（KB）
}else {
map.put(queryResultList.get(x).get(1), String.valueOf(Double.parseDouble(map.get(queryResultList.get(x).get(1)))+Double.parseDouble(queryResultList.get(x).get(2))));
}
}



}//for循环结束




// System.out.println(queryResultList.toString());
// for(int i = 0;i<queryResultList.size();i++){
// for(int j = 0;j<queryResultList.get(i).size();j++){
// System.out.print(queryResultList.get(i).get(j)+" ");
// }
// System.out.println();
// }
System.out.println(map.toString());



//conn3
Connection conn3 = test1.getConn1();//yearspli
Statement stmt3 = test1.getStatement(conn3);

for(String i : map.keySet()){
String sql3 = "insert into YEAR_DATAVAL(YEAR,DATAVAL,DOMAIN,OBJECT) values ('"+i+"','"+map.get(i)+"','"+domain+"','"+object+"') ";

try {
int  ps = stmt3.executeUpdate(sql3);
} catch (SQLException e) {
e.printStackTrace();
}
}


test1.close(stmt3);
test1.close(conn3);
System.out.println("划分成功");

}


public static Connection getConn(String objectDBName,String SchemaName) {
Connection conn = null;
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");//
conn = DriverManager.getConnection("jdbc:db2://10.1.1.228:50000/"+objectDBName+":currentSchema="+SchemaName+";","ADMINISTRATOR","password");// 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟? 
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}

return conn;
}

public static Connection getConn1() {
Connection conn = null;
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");//
conn = DriverManager.getConnection("jdbc:db2://10.1.1.225:50000/YEARSPLI","ADMINISTRATOR","password");// 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟? 
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}

return conn;
}


public static Statement getStatement(Connection conn) {
Statement stmt = null; 
try {
if(conn != null) {
//stmt = conn.createStatement();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
}
} catch (SQLException e) {
e.printStackTrace();
}
return stmt;
}

public static ResultSet getResultSet(Statement stmt, String sql) {
ResultSet rs = null;
try {
if(stmt != null) {
rs = stmt.executeQuery(sql);
}
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}

public static void close(Connection conn) {
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}

public static void close(Statement stmt) {
try {
if(stmt != null) {
stmt.close();
stmt = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}

public static void close(ResultSet rs) {
try {
if(rs != null) {
rs.close();
rs = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}




}