(java oracle)以bean和array为参数的存储过程及dao部分代码

浏览数：50 / 时间：2015年06月12日

一、数据库部分

1.创建bean对象

 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 )

2.创建array对象

CREATE OR REPLACE TYPE "QUARTZJOBARRAY"  is table of QUARTZJOBBEAN

3.存储过程PACKAGE部分

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

4.存储过程BODY部分

 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     values
15       (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_TRIGGERS
25       (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     values
38       (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‘ then
52       insert into QRTZ_CRON_TRIGGERS
53         (TRIGGER_NAME, TRIGGER_GROUP, CRON_EXPRESSION, TIME_ZONE_ID)
54       values
55         (v_bean.trigger_name,
56          v_bean.trigger_group,
57          v_bean.t1,
58          ‘Asia/Shanghai‘);
59     elsif v_bean.trigger_type = ‘SIMPLE‘ then
60       insert into QRTZ_SIMPLE_TRIGGERS
61         (TRIGGER_NAME,
62          TRIGGER_GROUP,
63          REPEAT_COUNT,
64          REPEAT_INTERVAL,
65          TIMES_TRIGGERED)
66       values
67         (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) IS
78     v_bean QUARTZJOBBEAN;
79   BEGIN
80     for i in v_array.first .. v_array.last loop
81       v_bean := v_array(i);
82       update QRTZ_TRIGGERS
83          set TRIGGER_STATE = ‘PAUSED‘
84        where trigger_name = v_bean.trigger_name
85          and trigger_group = v_bean.trigger_group;
86       commit;
87     end loop;
88   END;
89   
90 END;

二、dao部分

1.创建bean对象

  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 }

2.Dao

 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 }

3.DaoImplements

  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 }