mytables.sql
要用StringBuffer来存放匿名块文件的代码,读取每一行的时候,记得加上回车符号。
然后:
PreparedStatement st = conn.prepareStatement(sb.toString());
st.execute();
调用匿名块是运行参数的,具体看API
declare
num int:=0;
begin
dbms_output.PUT_LINE('--------------------start pro[OracleTools]-----------------------');
--getStrBeforeLine
select count(*) into num from mytables where dict_id=10;
if num>0 then
update mytables set STATE='001',DESCRIPTION='',DISPLAY_INDEX=1,EDIT_USER=NULL,LAST_OPER_ID=NULL,CREATE_USER='admin',EDIT_DATE=NULL,DICT_NAME='文本',DICT_CODE='001',CREATE_DATE=NULL,DICT_TYPE_CODE='BSC_ANALYSIS_ITEM_TYPE',VERSION_NUM=NULL where dict_id=10;
else
insert into mytables(STATE,DESCRIPTION,DISPLAY_INDEX,EDIT_USER,LAST_OPER_ID,CREATE_USER,EDIT_DATE,DICT_NAME,DICT_CODE,CREATE_DATE,DICT_TYPE_CODE,DICT_ID,VERSION_NUM) values('001','',1,NULL,NULL,'admin',NULL,'文本','001',NULL,'BSC_ANALYSIS_ITEM_TYPE',10,NULL);
end if;
--getStrAfterLine
--getStrBeforeLine
select count(*) into num from mytables where dict_id=30;
if num>0 then
update mytables set STATE='001',DESCRIPTION='',DISPLAY_INDEX=2,EDIT_USER=NULL,LAST_OPER_ID=NULL,CREATE_USER='admin',EDIT_DATE=NULL,DICT_NAME='日期',DICT_CODE='002',CREATE_DATE=NULL,DICT_TYPE_CODE='BSC_ANALYSIS_ITEM_TYPE',VERSION_NUM=NULL where dict_id=30;
else
insert into mytables(STATE,DESCRIPTION,DISPLAY_INDEX,EDIT_USER,LAST_OPER_ID,CREATE_USER,EDIT_DATE,DICT_NAME,DICT_CODE,CREATE_DATE,DICT_TYPE_CODE,DICT_ID,VERSION_NUM) values('001','',2,NULL,NULL,'admin',NULL,'日期','002',NULL,'BSC_ANALYSIS_ITEM_TYPE',30,NULL);
end if;
--getStrAfterLine
......
......
......
dbms_output.PUT_LINE('--------------------end pro[OracleTools]-----------------------');
commit;
exception WHEN others THEN
dbms_output.PUT_LINE('--------------------exception-----------------------');
dbms_output.PUT_LINE(sqlerrm);
rollback;
end;
要用StringBuffer来存放匿名块文件的代码,读取每一行的时候,记得加上回车符号。
然后:
PreparedStatement st = conn.prepareStatement(sb.toString());
st.execute();
调用匿名块是运行参数的,具体看API
package com.geosun.main;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import com.geosun.conf.DBUtils;
public class ProcessUnNameSQL {
public static StringBuffer readFile(String filePath){
BufferedReader reader = null;
StringBuffer sb = new StringBuffer();
try {
reader = new BufferedReader(new FileReader(filePath));
String line = reader.readLine();
while(line!=null){
sb.append(line+"\n");
line = reader.readLine();
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
System.out.println(sb.toString());
return sb;
}
public static void process(Connection conn, String filePath){
StringBuffer sb = readFile(filePath);
try {
conn.setAutoCommit(false);
PreparedStatement st = conn.prepareStatement(sb.toString());
System.out.println("Start Process");
st.execute();
conn.commit();
System.out.println("Process success.");
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
throw new RuntimeException(e);
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Connection conn = DBUtils.getNewConn("jdbc:oracle:thin:@localhost:1521:orcl", "aaa", "aaa", "oracle.jdbc.driver.OracleDriver");
String filePath="D:\mytables.sql";
process(conn, filePath);
conn.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
}