package com.exedosoft.plat.util;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
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.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.exedosoft.plat.bo.BOInstance;
import com.exedosoft.plat.bo.DOBO;
import com.exedosoft.plat.bo.SQLTypes;
import com.exedosoft.plat.bo.DODataSource;
/**
* 类型就是收敛到一定的程度，比如就是现在的4个类型。 但是还要支持扩展的类型。
*
* @author anolesoft
*
*/
public class DBTransUtil {
private static Log log = LogFactory.getLog(DBTransUtil.class);
/**
*
* @param rs
* @param isOracle
* @return
* @throws SQLException
*/
public static void makInsertSql(String aTargetTable, String aHistoryTable) {
List<String> targetCols = getTableCols(aTargetTable);
log.info("Target Table Cols:::" + targetCols);
List<String> hisCols = getTableCols(aHistoryTable);
log.info("History Table Cols:::" + hisCols);
StringBuffer insertSql = new StringBuffer("insert into ").append(
aTargetTable).append("(");
for (Iterator<String> it = targetCols.iterator(); it.hasNext();) {
String aTargetCol = it.next();
insertSql.append(aTargetCol);
if (it.hasNext()) {
insertSql.append(",");
}
}
insertSql.append(") ");
insertSql.append(" select ");
for (Iterator<String> it = targetCols.iterator(); it.hasNext();) {
String targetCol = it.next();
if (hisCols.contains(targetCol)) {
insertSql.append(targetCol);
} else {
insertSql.append("#rep#");
}
if (it.hasNext()) {
insertSql.append(",");
}
}
insertSql.append(" from ").append(aHistoryTable);
// /************************12.学生报班表
// 更新折扣************************************************************************/
// update tbstudentclass set fdifagio = fdifagio*0.01 where fdifagio is
// not null
log.info("Insert Sql:::" + insertSql);
// ///////////////////target table cols not in src table cols
StringBuffer notInCols = new StringBuffer("");
for (Iterator<String> it = hisCols.iterator(); it.hasNext();) {
String hisCol = it.next();
if (!targetCols.contains(hisCol)) {
notInCols.append(hisCol);
} else {
continue;
}
if (it.hasNext()) {
notInCols.append(",");
}
}
log.info("Target notInCols:::" + notInCols);
// /*******1.员工表数据导入********修改省份************/
// update tbemployee set fdprovince=(select OBJUID from tbprovince WHERE
// tbemployee.fdprovince=fdtemppro)
//
// /*******1.员工表数据导入********修改城市，未完全修改不规范城市信息************/
// update tbemployee set fdcity=(select OBJUID from tbcity WHERE
// tbemployee.fdcity=replace(tbcity.fdcity,'市',''))
// /*******11.学生表*数据导入********修改学校************/
// update tbstudent set fdschool=(select OBJUID from tbschool WHERE
// tbstudent.fdschool=tbschool.fdschool and
// tbstudent.fdxuebu=tbschool.fdxuebu)
// where exists (select 1 from tbschool where
// tbstudent.fdschool=tbschool.fdschool and
// tbstudent.fdxuebu=tbschool.fdxuebu)
if (targetCols.contains("fdprovince")) {
StringBuffer buffer = new StringBuffer(" update ").append(
aTargetTable).append(
" set fdprovince=(select OBJUID from tbprovince WHERE ")
.append(aTargetTable).append(".fdprovince=fdtemppro)");
log.info("Replace fdprovince::" + buffer);
}
if (targetCols.contains("fdcity")) {
StringBuffer buffer = new StringBuffer(" update ").append(
aTargetTable).append(
" set fdcity=(select OBJUID from tbcity WHERE ").append(
aTargetTable).append(
".fdcity=replace(tbcity.fdcity,'市',''))");
log.info("Replace fdcity::" + buffer);
}
if (targetCols.contains("fdaddprovince")) {
StringBuffer buffer = new StringBuffer(" update ")
.append(aTargetTable)
.append(
" set fdaddprovince=(select OBJUID from tbprovince WHERE ")
.append(aTargetTable).append(".fdaddprovince=fdtemppro)");
log.info("Replace fdprovince::" + buffer);
}
if (targetCols.contains("fdaddcity")) {
StringBuffer buffer = new StringBuffer(" update ").append(
aTargetTable).append(
" set fdaddcity=(select OBJUID from tbcity WHERE ")
.append(aTargetTable).append(
".fdaddcity=replace(tbcity.fdcity,'市',''))");
log.info("Replace fdcity::" + buffer);
}
}
private static List<String> getTableCols(String aTableName) {
List<String> list = new ArrayList<String>();
try {
Connection con = DODataSource.getDefaultCon_Busi();
PreparedStatement pstmt = con.prepareStatement("select * from "
+ aTableName);
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData rsMeta = rs.getMetaData();
for (int col = 1; col <= rsMeta.getColumnCount(); col++) {
String metaName = rsMeta.getColumnName(col).toLowerCase()
.trim();
list.add(metaName.toLowerCase());
}
pstmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
//
public static StringBuffer changeBitType() {
StringBuffer buffer = new StringBuffer();
Connection con = DODataSource.getDefaultCon();
try {
DatabaseMetaData meta = con.getMetaData();
String[] tblTypes = new String[] { "TABLE" };
/*对oracle的schema过滤 by whutmen@gmail.com begin */
DOBO bo = DOBO.getDOBOByName("do_datasource");
DODataSource dss = DODataSource.getDataSourceByL10n(bo
.getCorrInstance().getValue("l10n"));
String schema = null;
if (dss.isOracle()) {
schema = dss.getUserName().trim().toUpperCase();
}
ResultSet rsDB = meta.getTables(null, schema, null, tblTypes);
/*by whutmen@gmail.com end */
//ResultSet rsDB = meta.getTables(null, null, null, tblTypes);
while (rsDB.next()) {
String tableName = rsDB.getString("TABLE_NAME").toLowerCase();
// ////////////增强更新功能
// //////首先要跟现有的tableName比较
PreparedStatement pstmt = con
.prepareStatement("select * from C192391203."
+ tableName);
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData rsMeta = rs.getMetaData();
for (int col = 1; col <= rsMeta.getColumnCount(); col++) {
if (rsMeta.getColumnType(col) == Types.BIT) {
buffer.append(" deldefault '").append(tableName)
.append("','")
.append(rsMeta.getColumnName(col)).append(
"';\n");
buffer.append(" alter table C192391203.").append(
tableName).append(" alter column ").append(
rsMeta.getColumnName(col)).append(" int;\n");
}
}
pstmt.close();
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
try {
if (!con.isClosed()) {
con.close();
}
} catch (SQLException ex1) {
ex1.printStackTrace();
}
}
return buffer;
}
public static void checkDecimal() {
Connection con = DODataSource.getDefaultCon_Busi();
try {
DatabaseMetaData meta = con.getMetaData();
String[] tblTypes = new String[] { "TABLE" };
/*对oracle的schema过滤 by whutmen@gmail.com begin */
DOBO bo = DOBO.getDOBOByName("do_datasource");
DODataSource dss = DODataSource.getDataSourceByL10n(bo
.getCorrInstance().getValue("l10n"));
String schema = null;
if (dss.isOracle()) {
schema = dss.getUserName().trim().toUpperCase();
}
ResultSet rsDB = meta.getTables(null, schema, null, tblTypes);
/*by whutmen@gmail.com end */
//ResultSet rsDB = meta.getTables(null, null, null, tblTypes);
while (rsDB.next()) {
String tableName = rsDB.getString("TABLE_NAME");
// ////////////增强更新功能
// //////首先要跟现有的tableName比较
System.out.println("TableName::::::::::" + tableName);
if (!tableName.toUpperCase().equals(tableName)) {
continue;
}
PreparedStatement pstmt = con.prepareStatement("select * from "
+ tableName);
ResultSet rs = null;
try {
rs = pstmt.executeQuery();
} catch (Exception e) {
continue;
}
if (rs == null) {
continue;
}
ResultSetMetaData rsMeta = rs.getMetaData();
for (int col = 1; col <= rsMeta.getColumnCount(); col++) {
if (SQLTypes.isDouble(rsMeta.getColumnType(col))
&& (rsMeta.getScale(col) != 2 && rsMeta
.getScale(col) != 0)) {
String metaName = rsMeta.getColumnName(col)
.toLowerCase().trim();
System.out.println("This::::::::::" + tableName
+ "-----" + metaName);
}
}
pstmt.close();
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
try {
if (!con.isClosed()) {
con.close();
}
} catch (SQLException ex1) {
ex1.printStackTrace();
}
}
}
public static StringBuffer changeTextType() {
StringBuffer buffer = new StringBuffer();
Connection con = DODataSource.getDefaultCon();
try {
DatabaseMetaData meta = con.getMetaData();
String[] tblTypes = new String[] { "TABLE" };
/*对oracle的schema过滤 by whutmen@gmail.com begin */
DOBO bo = DOBO.getDOBOByName("do_datasource");
DODataSource dss = DODataSource.getDataSourceByL10n(bo
.getCorrInstance().getValue("l10n"));
String schema = null;
if (dss.isOracle()) {
schema = dss.getUserName().trim().toUpperCase();
}
ResultSet rsDB = meta.getTables(null, schema, null, tblTypes);
/*by whutmen@gmail.com end */
//ResultSet rsDB = meta.getTables(null, null, null, tblTypes);
while (rsDB.next()) {
String tableName = rsDB.getString("TABLE_NAME").toLowerCase();
// ////////////增强更新功能
// //////首先要跟现有的tableName比较
PreparedStatement pstmt = con
.prepareStatement("select * from C192391203."
+ tableName);
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData rsMeta = rs.getMetaData();
for (int col = 1; col <= rsMeta.getColumnCount(); col++) {
// / alter table do_ui_formmodel2 add note1 varchar(4000);
//
// update do_ui_formmodel2 set note1=note;
//
// alter table do_ui_formmodel2 drop column note;
//
// sp_rename 'do_ui_formmodel2.[note1]', 'note', 'COLUMN'
if (rsMeta.getColumnType(col) == Types.CLOB) {
buffer.append(" alter table C192391203.").append(
tableName).append(" add ").append(
rsMeta.getColumnName(col)).append(
"1 varchar(4000);\n");
buffer.append(" update C192391203.").append(tableName)
.append(" set ").append(
rsMeta.getColumnName(col))
.append("1 =")
.append(rsMeta.getColumnName(col))
.append(";\n");
buffer.append(" alter table C192391203.").append(
tableName).append(" drop column ").append(
rsMeta.getColumnName(col)).append(";\n");
buffer.append(" sp_rename 'C192391203.").append(
tableName).append(".[").append(
rsMeta.getColumnName(col)).append("1]','")
.append(rsMeta.getColumnName(col)).append(
"','COLUMN';\n\n");
}
}
pstmt.close();
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
try {
if (!con.isClosed()) {
con.close();
}
} catch (SQLException ex1) {
ex1.printStackTrace();
}
}
return buffer;
}
public static void test() {
try {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn
.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "smartypants");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
// DBTransUtil.makInsertSql("SEA_CLASSIFY", "\"ArticleClass\"");
DBTransUtil.test();
// System.out.print(DBTransUtil.changeBitType());
// System.out.print(DBTransUtil.changeTextType());
}
}