📄 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 + -