1 CREATE OR REPLACE TYPE "QUARTZJOBBEAN" as object 2 ( 3 -- Author  : Duwc 4 -- Purpose : for QuartzJobBean 5   job_name          varchar2(200), 6   job_group         varchar2(200), 7   job_class_name    varchar2(250), 8   trigger_name      varchar2(200), 9   trigger_group     varchar2(200),10   trigger_state     varchar2(16),11   trigger_type      varchar2(8),12   t1                varchar2(200),13   t2                varchar2(200),14   t3                varchar2(200),15   is_durable        varchar2(1),16   is_volatile       varchar2(1),17   is_stateful       varchar2(1),18   requests_recovery varchar2(1),19   priority          number(13),20   start_time        number(13),21   end_time          number(13),22   calendar_name     varchar2(200),23   misfire_instr     number(2)24 )

CREATE OR REPLACE TYPE "QUARTZJOBARRAY"  is table of QUARTZJOBBEAN

1 CREATE OR REPLACE PACKAGE PKG_MODULES_DM_QUARTZ AS2 3   /*插入定时任务表*/4   PROCEDURE INSERT_QUARTZJOB(v_bean QUARTZJOBBEAN);5 6   /*暂停定时任务表*/7   PROCEDURE PAUSE_QUARTZJOB(v_array QUARTZJOBARRAY);8 END;

 1 CREATE OR REPLACE PACKAGE BODY PKG_MODULES_DM_QUARTZ AS 2   /*插入定时任务表*/ 3   PROCEDURE INSERT_QUARTZJOB(v_bean QUARTZJOBBEAN) IS 4   BEGIN 5     insert into QRTZ_JOB_DETAILS 6       (JOB_NAME, 7        JOB_GROUP, 8        DESCRIPTION, 9        JOB_CLASS_NAME,10        IS_DURABLE,11        IS_VOLATILE,12        IS_STATEFUL,13        REQUESTS_RECOVERY)14     values15       (v_bean.job_name,16        v_bean.job_group,17        v_bean.job_name,18        v_bean.job_class_name,19        v_bean.is_durable,20        v_bean.is_volatile,21        v_bean.is_stateful,22        v_bean.requests_recovery);23   24     insert into QRTZ_TRIGGERS25       (TRIGGER_NAME,26        TRIGGER_GROUP,27        JOB_NAME,28        JOB_GROUP,29        IS_VOLATILE,30        PRIORITY,31        TRIGGER_STATE,32        TRIGGER_TYPE,33        START_TIME,34        END_TIME,35        CALENDAR_NAME,36        MISFIRE_INSTR)37     values38       (v_bean.trigger_name,39        v_bean.trigger_group,40        v_bean.job_name,41        v_bean.job_group,42        v_bean.is_volatile,43        v_bean.priority,44        v_bean.trigger_state,45        v_bean.trigger_type,46        v_bean.start_time,47        v_bean.end_time,48        v_bean.calendar_name,49        v_bean.misfire_instr);50   51     if v_bean.trigger_type = ‘CRON‘ then52       insert into QRTZ_CRON_TRIGGERS53         (TRIGGER_NAME, TRIGGER_GROUP, CRON_EXPRESSION, TIME_ZONE_ID)54       values55         (v_bean.trigger_name,56          v_bean.trigger_group,57          v_bean.t1,58          ‘Asia/Shanghai‘);59     elsif v_bean.trigger_type = ‘SIMPLE‘ then60       insert into QRTZ_SIMPLE_TRIGGERS61         (TRIGGER_NAME,62          TRIGGER_GROUP,63          REPEAT_COUNT,64          REPEAT_INTERVAL,65          TIMES_TRIGGERED)66       values67         (v_bean.trigger_name,68          v_bean.trigger_group,69          to_number(v_bean.t2),70          to_number(v_bean.t3),71          0);72     end if;73     commit;74   END;75 76   /*暂停定时任务表*/77   PROCEDURE PAUSE_QUARTZJOB(v_array QUARTZJOBARRAY) IS78     v_bean QUARTZJOBBEAN;79   BEGIN80     for i in v_array.first .. v_array.last loop81       v_bean := v_array(i);82       update QRTZ_TRIGGERS83          set TRIGGER_STATE = ‘PAUSED‘84        where trigger_name = v_bean.trigger_name85          and trigger_group = v_bean.trigger_group;86       commit;87     end loop;88   END;89   90 END;

  1 package com.ecnt.gnop.modules.dm.quartz.bean;  2   3 public class QuartzJobBean {  4   5     private String job_name;  6       7     private String job_group;  8       9     private String job_class_name; 10      11     private String trigger_name; 12      13     private String trigger_group; 14      15     private String trigger_state; 16      17     private String trigger_type; 18      19     private String t1; 20      21     private String t2; 22      23     private String t3; 24      25     private String is_durable; 26      27     private String is_volatile; 28      29     private String is_stateful; 30      31     private String requests_recovery; 32      33     private int priority; 34      35     private int start_time; 36      37     private int end_time; 38      39     private String calendar_name; 40      41     private String misfire_instr; 42      43     public Object[] toArray() { 44         Object[] obj = new Object[19]; 45         obj[0] = job_name; 46         obj[1] = job_group; 47         obj[2] = job_class_name; 48         obj[3] = trigger_name; 49         obj[4] = trigger_group; 50         obj[5] = trigger_state; 51         obj[6] = trigger_type; 52         obj[7] = t1; 53         obj[8] = t2; 54         obj[9] = t3; 55         obj[10] = is_durable; 56         obj[11] = is_volatile; 57         obj[12] = is_stateful; 58         obj[13] = requests_recovery; 59         obj[14] = priority; 60         obj[15] = start_time; 61         obj[16] = end_time; 62         obj[17] = calendar_name; 63         obj[18] = misfire_instr; 64         return obj; 65     } 66      67     public String getCalendar_name() { 68         return calendar_name; 69     } 70  71     public void setCalendar_name(String calendar_name) { 72         this.calendar_name = calendar_name; 73     } 74  75     public int getEnd_time() { 76         return end_time; 77     } 78  79     public void setEnd_time(int end_time) { 80         this.end_time = end_time; 81     } 82  83     public String getIs_durable() { 84         return is_durable; 85     } 86  87     public void setIs_durable(String is_durable) { 88         this.is_durable = is_durable; 89     } 90  91     public String getIs_stateful() { 92         return is_stateful; 93     } 94  95     public void setIs_stateful(String is_stateful) { 96         this.is_stateful = is_stateful; 97     } 98  99     public String getIs_volatile() {100         return is_volatile;101     }102 103     public void setIs_volatile(String is_volatile) {104         this.is_volatile = is_volatile;105     }106 107     public String getMisfire_instr() {108         return misfire_instr;109     }110 111     public void setMisfire_instr(String misfire_instr) {112         this.misfire_instr = misfire_instr;113     }114 115     public int getPriority() {116         return priority;117     }118 119     public void setPriority(int priority) {120         this.priority = priority;121     }122 123     public String getRequests_recovery() {124         return requests_recovery;125     }126 127     public void setRequests_recovery(String requests_recovery) {128         this.requests_recovery = requests_recovery;129     }130 131     public int getStart_time() {132         return start_time;133     }134 135     public void setStart_time(int start_time) {136         this.start_time = start_time;137     }138 139     public String getJob_class_name() {140         return job_class_name;141     }142 143     public void setJob_class_name(String job_class_name) {144         this.job_class_name = job_class_name;145     }146 147     public String getJob_group() {148         return job_group;149     }150 151     public void setJob_group(String job_group) {152         this.job_group = job_group;153     }154 155     public String getJob_name() {156         return job_name;157     }158 159     public void setJob_name(String job_name) {160         this.job_name = job_name;161     }162 163     public String getT1() {164         return t1;165     }166 167     public void setT1(String t1) {168         this.t1 = t1;169     }170 171     public String getT2() {172         return t2;173     }174 175     public void setT2(String t2) {176         this.t2 = t2;177     }178 179     public String getT3() {180         return t3;181     }182 183     public void setT3(String t3) {184         this.t3 = t3;185     }186 187     public String getTrigger_group() {188         return trigger_group;189     }190 191     public void setTrigger_group(String trigger_group) {192         this.trigger_group = trigger_group;193     }194 195     public String getTrigger_name() {196         return trigger_name;197     }198 199     public void setTrigger_name(String trigger_name) {200         this.trigger_name = trigger_name;201     }202 203     public String getTrigger_state() {204         return trigger_state;205     }206 207     public void setTrigger_state(String trigger_state) {208         this.trigger_state = trigger_state;209     }210 211     public String getTrigger_type() {212         return trigger_type;213     }214 215     public void setTrigger_type(String trigger_type) {216         this.trigger_type = trigger_type;217     }218 }

 1 package com.ecnt.gnop.modules.dm.quartz.dao; 2  3 import java.sql.SQLException; 4 import java.util.List; 5  6 import com.ecnt.gnop.modules.dm.quartz.bean.QuartzJobBean; 7  8 public interface QuartzJobDao { 9 10     public void insertQuartzJob(QuartzJobBean bean) throws SQLException;11         12     public void pauseQuartzJob(List<QuartzJobBean> list) throws SQLException;13 }

  1 package com.ecnt.gnop.modules.dm.quartz.dao.impl;  2   3 import java.sql.CallableStatement;  4 import java.sql.Connection;  5 import java.sql.DriverManager;  6 import java.sql.SQLException;  7 import java.util.ArrayList;  8 import java.util.List;  9  10 import oracle.sql.ARRAY; 11 import oracle.sql.ArrayDescriptor; 12 import oracle.sql.STRUCT; 13 import oracle.sql.StructDescriptor; 14  15 import org.apache.commons.dbcp.DelegatingConnection; 16 import org.apache.log4j.Logger; 17  18 import com.ecnt.gnop.modules.dm.quartz.bean.QuartzJobBean; 19 import com.ecnt.gnop.modules.dm.quartz.dao.QuartzJobDao; 20  21 public class QuartzJobDaoImpl implements QuartzJobDao { 22  23     private Logger log = Logger.getLogger(this.getClass().getName()); 24  25     private static Connection getConn() { 26         String driver = "oracle.jdbc.driver.OracleDriver"; 27         String url = "jdbc:oracle:thin:@192.168.97.201:1521:fznop"; 28         String username = "bi_swxt"; 29         String password = "swxt2013"; 30         Connection conn = null; 31         try { 32             Class.forName(driver); 33             // new oracle.jdbc.driver.OracleDriver(); 34             conn = DriverManager.getConnection(url, username, password); 35         } catch (ClassNotFoundException e) { 36             e.printStackTrace(); 37         } catch (SQLException e) { 38             e.printStackTrace(); 39         } 40  41         return conn; 42     } 43  44     /** 45      * TOMCAT dbcp Connection --> Oracle Connection 46      *  47      * @param con 48      * @return 49      * @throws SQLException 50      */ 51     public static Connection getNativeConnection(Connection con) throws SQLException { 52         if (con instanceof DelegatingConnection) { 53             Connection nativeCon = ((DelegatingConnection) con).getInnermostDelegate(); 54             return (nativeCon != null ? nativeCon : con.getMetaData().getConnection()); 55         } 56         return con; 57     } 58  59     public void insertQuartzJob(QuartzJobBean bean) throws SQLException { 60         Connection conn = null; 61         Connection oracleConn = null; 62         CallableStatement stmt = null; 63         String sql = "{ CALL PKG_MODULES_DM_QUARTZ.INSERT_QUARTZJOB(?) }"; 64         try { 65             conn = getConn(); 66             oracleConn = getNativeConnection(conn); 67             stmt = oracleConn.prepareCall(sql); 68             StructDescriptor structDescriptor = StructDescriptor.createDescriptor("QUARTZJOBBEAN", oracleConn); 69             Object[] objects = bean.toArray(); 70             STRUCT struct = new STRUCT(structDescriptor, oracleConn, objects); 71             stmt.setObject(1, struct); 72             stmt.execute(); 73         } catch (SQLException e) { 74             log.error(e.getMessage(), e); 75             throw e; 76         } finally { 77             if (stmt != null) { 78                 try { 79                     stmt.close(); 80                 } catch (SQLException e) { 81                     e.printStackTrace(); 82                 } 83             } 84             if (conn != null) { 85                 try { 86                     conn.close(); 87                 } catch (SQLException e) { 88                     e.printStackTrace(); 89                 } 90             } 91         } 92     } 93  94     public void pauseQuartzJob(List<QuartzJobBean> list) throws SQLException { 95         Connection conn = null; 96         Connection oracleConn = null; 97         CallableStatement stmt = null; 98         String sql = "{ CALL PKG_MODULES_DM_QUARTZ.PAUSE_QUARTZJOB(?) }"; 99         try {100             conn = getConn();101             oracleConn = getNativeConnection(conn);102             stmt = oracleConn.prepareCall(sql);103             StructDescriptor structDescriptor = StructDescriptor.createDescriptor("QUARTZJOBBEAN", oracleConn);104             ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("QUARTZJOBARRAY", oracleConn);105             ArrayList<STRUCT> structList = new ArrayList<STRUCT>();106             for (QuartzJobBean bean : list) {107                 STRUCT struct = new STRUCT(structDescriptor, oracleConn, bean.toArray());108                 structList.add(struct);109             }110             ARRAY array = new ARRAY(arrayDescriptor, oracleConn, structList.toArray());111             stmt.setArray(1, array);112             stmt.execute();113         } catch (SQLException e) {114             log.error(e.getMessage(), e);115             throw e;116         } finally {117             if (stmt != null) {118                 try {119                     stmt.close();120                 } catch (SQLException e) {121                     e.printStackTrace();122                 }123             }124             if (conn != null) {125                 try {126                     conn.close();127                 } catch (SQLException e) {128                     e.printStackTrace();129                 }130             }131         }132     }133 }