?? schedule.java
字號:
att = att + "," + "?";
}
att = att.substring(1);
String insert = "insert into " + newtk.getDSchema() + "." + newtk.getDTab() + "(" + newfield + ") values (" + att +")" ;
PreparedStatement inpstm = null;
System.out.println(insert);
String updatefield = "";
String update = "update " + newtk.getDSchema() + "." + newtk.getDTab() ;
PreparedStatement upstm = null;
int insertnum = 0;
int updatenum = 0;
try {
while(rs.next()){
//*******************************判斷目的表中是否存在該項數據*****************************************************
int keyword = rs.getInt(newtk.getD()[0]);
String existitem = "select " + newtk.getD()[0] + " from " + newtk.getDSchema() + "." + newtk.getDTab()+
" where " + newtk.getD()[0] + " = " + keyword;
System.out.println(existitem);
PreparedStatement existitemps = newconn.prepareStatement(existitem);
ResultSet existitemrs = existitemps.executeQuery();
boolean isexistitem = existitemrs.next();
System.out.println(isexistitem);
existitemps.close();
//***********************************不存在執行insert******************************************************
if(!isexistitem){
inpstm = newconn.prepareStatement(insert);
int attnum = 1;
inpstm.setInt(attnum,keyword);
for(int i = 1; i < newtk.getD().length; i++){
System.out.println(newtk.getD()[i]);
attnum++;
if(newtk.getDAttrb()[i].equalsIgnoreCase("number"))
inpstm.setInt(attnum,rs.getInt(newtk.getS()[i]));
if(newtk.getDAttrb()[i].equalsIgnoreCase("varchar2"))
inpstm.setString(attnum,rs.getString(newtk.getS()[i]));
if(newtk.getDAttrb()[i].equalsIgnoreCase("Date"))
inpstm.setDate(attnum,rs.getDate(newtk.getS()[i]));
if(newtk.getDAttrb()[i].equalsIgnoreCase("float"))
inpstm.setFloat(attnum,rs.getFloat(newtk.getS()[i]));
if(newtk.getDAttrb()[i].equalsIgnoreCase("double"))
inpstm.setDouble(attnum,rs.getDouble(newtk.getS()[i]));
}
insertnum += inpstm.executeUpdate();
inpstm.close();
//System.out.println("插入了一項數據！");
}
//************************************存在執行update**************************************************
else{
updatefield = updatefield + "," + newtk.getD()[0] + " = " + keyword;
for(int i = 2; i < newtk.getD().length; i++){
if(newtk.getDAttrb()[i].equalsIgnoreCase("number"))
updatefield = updatefield + "," + newtk.getD()[i] + " = " + rs.getInt(newtk.getS()[i]);
if(newtk.getDAttrb()[i].equalsIgnoreCase("varchar2")&& rs.getString(newtk.getS()[i])!=null)
updatefield = updatefield + "," + newtk.getD()[i] + " = '" + rs.getString(newtk.getS()[i])+"'";
if(newtk.getDAttrb()[i].equalsIgnoreCase("Date")&& rs.getDate(newtk.getS()[i])!=null)
updatefield = updatefield + "," + newtk.getD()[i] + " =to_date( '" + rs.getDate(newtk.getS()[i])+"'" + ",'yyyy-mm-dd')";
if(newtk.getDAttrb()[i].equalsIgnoreCase("float"))
updatefield = updatefield + "," + newtk.getD()[i] + " = " + rs.getFloat(newtk.getS()[i]);
if(newtk.getDAttrb()[i].equalsIgnoreCase("double"))
updatefield = updatefield + "," + newtk.getD()[i] + " = " + rs.getDouble(newtk.getS()[i]);
}
updatefield = updatefield.substring(1);
//System.out.println(updatefield);
update = update + " set " + updatefield + " where " + newtk.getD()[0] + " = " + keyword;
System.out.println(update);
upstm = newconn.prepareStatement(update);
updatenum += upstm.executeUpdate();
upstm.close();
update = "update " + newtk.getDSchema() + "." + newtk.getDTab();
updatefield = "";
}
}
} catch (SQLException e) {
error += sdf.format(new Date()) + " 方案:" + newtk.getTaskName()+ " 向目的庫數據庫中導入數據失??！";
log.Log("" + root + "/src/Log/" + logerrorname, error);
System.out.println("向目的庫數據庫中導入數據失??！");
e.printStackTrace();
}
sql = sdf.format(new Date()) + " 方案:" + newtk.getTaskName()+" "+ insert;
log.Log("" + root + "/src/Log/" + logsqlname, sql);
sql = sdf.format(new Date()) + " 方案:" + newtk.getTaskName()+ update + " "+updatefield;
log.Log("" + root + "/src/Log/" + logsqlname, sql);
sql = "\n" + sdf.format(new Date()) + " 方案:" +" "+ newtk.getTaskName()+ "：增量模式導數據結果：" + "插入了 " + insertnum + " 項數據！\n" + "更新了 " + updatenum + " 項數據！\n";
log.Log("" + root + "/src/Log/" + logsqlname, sql);
System.out.println("插入了 " + insertnum + " 項數據！");
System.out.println("更新了 " + updatenum + " 項數據！");
// ******************************關閉與源數據庫、目的數據庫的連接*************************************
try {
if(insertnum!=0)
inpstm.close();
newconn.close();
rs.close();
pstm.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("增量模式導數據執行完畢！");
}
/**
* 更新導數據模式
*
*/
public void renewal(){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss ");
System.out.println(newtk.getDATE());
String logerrorname = null;
String logsqlname = null;
logerrorname = "log.txt";
logsqlname = "log.txt";
String error = "";
String sql = "";
boolean bFile;
bFile = false;
File dirFile;
dirFile = new File("" + root + "/src/Log");
bFile = dirFile.exists();
if (bFile == true) {
System.out.println("Log文件夾存在。");
} else {
System.out.println("Log文件夾不存在" + "\n" +"創建Log文件夾"+ "\n");
bFile = dirFile.mkdir();
if (bFile == true) {
System.out.println("Log文件夾 Createsuccessfully!");
} else {
System.out.println("Disable to make the folder,please check the disk is full or not.");
System.exit(1);
}
}
System.out.println("開始執行 更新模式導數據！………………");
// *****************************與源庫建立連接，從源庫取得數據***************************************
try {
System.out.println("------->"+newtk.getSDriver().toString()+"------>"+newtk.getSURL());
Class.forName(newtk.getSDriver());
} catch (ClassNotFoundException e) {
error += sdf.format(new Date()) + " 方案:" + newtk.getTaskName()+ " 無法完成源數據庫驅動！\n";
log.Log("" + root + "/src/Log/" + logerrorname, error);
System.out.println("無法完成源數據庫驅動！");
e.printStackTrace();
}
Connection conn = null;
try {
conn = DriverManager.getConnection(newtk.getSURL(),newtk.getSuser(),newtk.getSpassword());
} catch (SQLException e) {
error += sdf.format(new Date())+" 方案:" + newtk.getTaskName()+ " 無法與源數據庫建立連接！";
log.Log("" + root + "/src/Log/" + logerrorname, error);
System.out.println("無法與源數據庫建立連接！");
e.printStackTrace();
}
String field = "";
for(int i = 0; i < newtk.getS().length; i++){
field =field + "," + newtk.getS()[i];
}
field = field.substring(1);
String sschema = null;
if(newtk.getSSchema() == null){
sschema = "";
}
else{
sschema = newtk.getSSchema();
}
String stable = null;
if(newtk.getSKind().equalsIgnoreCase("excel")){
stable = "[Sheet1$]";
}
else{
stable = newtk.getSTab();
}
String query = " select " + field + " from " +sschema + "." + stable;
System.out.println(query);
PreparedStatement pstm = null;
ResultSet rs = null;
try {
pstm = conn.prepareStatement(query);
rs = pstm.executeQuery();
} catch (SQLException e) {
error += sdf.format(new Date()) +" 方案:" + newtk.getTaskName()+ " 無法從源數據庫中取得數據！";
log.Log("" + root + "/src/Log/" + logerrorname, error);
System.out.println("無法從源數據庫中取得數據！");
e.printStackTrace();
}
// *****************************與目的庫建立連接，向目的庫數據*************************************************
try {
Class.forName(newtk.getDDriver());
} catch (ClassNotFoundException e) {
error += sdf.format(new Date()) + " 方案:" + newtk.getTaskName()+ " 無法完成目的數據庫驅動！";
log.Log("" + root + "/src/Log/" + logerrorname, error);
System.out.println("無法完成目的數據庫驅動！");
e.printStackTrace();
}
Connection newconn = null;
try {
//DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
newconn = DriverManager.getConnection(newtk.getDURL(),newtk.getDuser(),newtk.getDpassword());
} catch (SQLException e) {
error += sdf.format(new Date()) + " 方案:" + newtk.getTaskName()+ " 無法與目的數據庫建立連接！";
log.Log("" + root + "/src/Log/" + logerrorname, error);
System.out.println("無法與目的數據庫建立連接！");
e.printStackTrace();
}
// *******************導數據之前，先刪除目的表中原有的數據，然后在導入新的數據，以實現更新****************************
String delete = "delete from " + newtk.getDSchema() + "." + newtk.getDTab();
System.out.println(delete);
sql = sdf.format(new Date()) + " 方案:" +" "+ newtk.getTaskName()+ delete;
log.Log("" + root + "/src/Log/" + logsqlname, sql);
PreparedStatement delstm = null;
try {
delstm = newconn.prepareStatement(delete);
delstm.executeUpdate();
} catch (SQLException e1) {
error += sdf.format(new Date()) + " 方案:" + newtk.getTaskName()+ " 無法進行更新！";
log.Log("" + root + "/src/Log/" + logerrorname, error);
System.out.println("無法進行更新！");
try {
newconn.rollback();//回滾事物
} catch (SQLException e) {
e.printStackTrace();
}
e1.printStackTrace();
}
String newfield = "";
for(int i = 0; i < newtk.getD().length; i++){
newfield = newfield + "," + newtk.getD()[i];
}
newfield = newfield.substring(1);
String att = "";
for(int i = 0;i < newtk.getD().length; i++){
att = att + "," + "?";
}
att = att.substring(1);
String insert = "insert into " + newtk.getDSchema() + "." + newtk.getDTab() + "(" + newfield + ") values (" + att +")" ;
PreparedStatement inpstm = null;
System.out.println(insert);
int num = 0;
try {
while(rs.next()){
inpstm = newconn.prepareStatement(insert);
int attnum = 1;
for(int i = 0; i < newtk.getS().length; i++){
if(newtk.getDAttrb()[i].equalsIgnoreCase("number")){
inpstm.setInt(attnum,rs.getInt(newtk.getS()[i]));
}
if(newtk.getDAttrb()[i].equalsIgnoreCase("varchar2")){
inpstm.setString(attnum,rs.getString(newtk.getS()[i]));
}
if(newtk.getDAttrb()[i].equalsIgnoreCase("Date")){
inpstm.setDate(attnum,rs.getDate(newtk.getS()[i]));
}
if(newtk.getDAttrb()[i].equalsIgnoreCase("float")){
inpstm.setFloat(attnum,rs.getFloat(newtk.getS()[i]));
}
if(newtk.getDAttrb()[i].equalsIgnoreCase("double")){
inpstm.setDouble(attnum,rs.getDouble(newtk.getS()[i]));
}
attnum++;
}
System.out.println(sql);
num += inpstm.executeUpdate();
inpstm.close();
System.out.println("導入第 " + num + "項數據！");
}
} catch (SQLException e) {
error += sdf.format(new Date()) + " 方案:" + newtk.getTaskName()+ " 向目的庫數據庫中導入數據失??！";
log.Log("" + root + "/src/Log/" + logerrorname, error);
System.out.println("向目的庫數據庫中導入數據失敗！");
e.printStackTrace();
}
try {
inpstm.close();
newconn.close();
rs.close();
pstm.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
sql = sdf.format(new Date()) + " 方案:" + newtk.getTaskName()+ " "+insert;
log.Log("" + root + "/src/Log/" + logsqlname, sql);
sql = "\n" + sdf.format(new Date()) + " 方案: " + newtk.getTaskName()+ " 更新模式導數據結果：" + "導入了 " + num + " 項數據！\n" ;
log.Log("" + root + "/src/Log/" + logsqlname, sql);
System.out.println("導入了 " + num + " 項數據！");
System.out.println("更新模式導數據執行完畢！");
}
/**
* 創建導數據模式
*
*/
public void establish(){
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -