JDBC执行匿名块
mytables.sql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Sql代码 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
Java代码 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(); } } } |
