KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > openharmonise > commons > dsi > impl > DataStoreInterfaceOracle


1 /*
2  * The contents of this file are subject to the
3  * Mozilla Public License Version 1.1 (the "License");
4  * you may not use this file except in compliance with the License.
5  * You may obtain a copy of the License at http://www.mozilla.org/MPL/
6  *
7  * Software distributed under the License is distributed on an "AS IS"
8  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.
9  * See the License for the specific language governing rights and
10  * limitations under the License.
11  *
12  * The Initial Developer of the Original Code is Simulacra Media Ltd.
13  * Portions created by Simulacra Media Ltd are Copyright (C) Simulacra Media Ltd, 2004.
14  *
15  * All Rights Reserved.
16  *
17  * Contributor(s):
18  */
19 package org.openharmonise.commons.dsi.impl;
20
21
22 import java.io.*;
23
24 import java.sql.*;
25
26 import java.util.*;
27
28 import org.openharmonise.commons.dsi.*;
29 import org.openharmonise.commons.dsi.ddl.*;
30 import org.openharmonise.commons.dsi.dml.*;
31
32
33 /**
34  * Class providing interface to an Oracle database.
35  *
36  * @author Michael Bell
37  * @see java.sql
38  */
39 public class DataStoreInterfaceOracle extends AbstractDataStoreInterface {
40     
41     //static initialiser block
42 static {
43         DB_DATEFORMAT = "MM-dd-yyyy HH:mm:ss";
44     }
45     
46     /**
47      * Constructs a Oracle data store interface with no DB settings
48      */
49     public DataStoreInterfaceOracle() {
50     }
51     
52     /**
53      * Constructs a Oracle data store interface with the given connection
54      * type
55      *
56      * @param nConnectionType the connection type
57      * @throws DataStoreException if any errors occur
58      */
59     public DataStoreInterfaceOracle(int nConnectionType)
60     throws DataStoreException {
61         super(nConnectionType);
62     }
63     
64     
65     
66     /* (non-Javadoc)
67      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceNextValue(java.lang.String)
68      */
69     public int getSequenceNextValue(String JavaDoc sSeqName) throws DataStoreException,
70     SQLException {
71         Connection conn = null;
72         ResultSet rs = null;
73         Statement stmt = null;
74         String JavaDoc sSql = null;
75         int nSeq = -1;
76         
77         conn = getConnection();
78         stmt = conn.createStatement();
79         
80         sSql = "select " + sSeqName + ".NEXTVAL from dual";
81         
82         rs = stmt.executeQuery(sSql);
83         
84         if (rs.next()) {
85             nSeq = rs.getInt(1);
86         } else {
87             throw new DataStoreException("Sequence [" + sSeqName +
88             "] not found.");
89         }
90         
91         if (rs != null) {
92             rs.close();
93         }
94         
95         if (stmt != null) {
96             stmt.close();
97         }
98         
99         if (isPooledConnection() && (conn != null)) {
100             this.closeConnection(conn);
101         }
102         
103         return nSeq;
104     }
105     
106     
107     /* (non-Javadoc)
108      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#insertClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
109      */
110     public void insertClob(String JavaDoc sTable, String JavaDoc sColumn, String JavaDoc sClob,
111             String JavaDoc sCondition) throws DataStoreException {
112         Connection conn = null;
113         Statement stmt = null;
114         ResultSet rs = null;
115         
116         if ((sCondition == null) || (sCondition.length() == 0)) {
117             throw new DataStoreException("Missing CLOB condition");
118         }
119         
120         StringBuffer JavaDoc sSql = new StringBuffer JavaDoc();
121         
122         try {
123             conn = getConnection();
124             stmt = conn.createStatement();
125             
126             sSql.append("update ");
127             sSql.append(sTable);
128             sSql.append(" set ");
129             sSql.append(sColumn);
130             sSql.append("=");
131             sSql.append("");
132             sSql.append("empty_clob()");
133             sSql.append(" where ");
134             sSql.append(sCondition);
135             
136             stmt.executeUpdate(sSql.toString());
137             
138             sSql.setLength(0);
139             
140             conn.setAutoCommit(false);
141             
142             
143             //get CLOB locator
144 sSql.append("select ");
145             sSql.append(sColumn);
146             sSql.append(" from ");
147             sSql.append(sTable);
148             sSql.append(" where ");
149             sSql.append(sCondition);
150             sSql.append(" for update");
151             
152             rs = stmt.executeQuery(sSql.toString());
153             
154             Clob clob = null;
155             
156             if (rs.next()) {
157                 clob = rs.getClob(1);
158             }
159             
160             //create input stream
161 Writer outstream = clob.setCharacterStream(0);
162             
163             try {
164                 outstream.write(addEscapeChars(sClob));
165                 outstream.close();
166             } catch (IOException e) {
167                 throw new DataStoreException("Error writing CLOB to database: " +
168                         e.getMessage());
169             }
170             
171             conn.commit();
172             conn.setAutoCommit(true);
173             
174             if (isPooledConnection() && (conn != null)) {
175                 this.closeConnection(conn);
176             }
177         } catch (SQLException e) {
178             throw new DataStoreException("SQLException: " + e.getMessage());
179         }
180     }
181     
182     
183     /* (non-Javadoc)
184      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#updateClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
185      */
186     public void updateClob(String JavaDoc sTable, String JavaDoc sColumn, String JavaDoc sClob,
187             String JavaDoc sCondition) throws DataStoreException {
188         Connection conn = null;
189         Statement stmt = null;
190         ResultSet rs = null;
191         
192         if ((sCondition == null) || (sCondition.length() == 0)) {
193             throw new DataStoreException("Missing CLOB condition");
194         }
195         
196         StringBuffer JavaDoc sSql = new StringBuffer JavaDoc();
197         
198         try {
199             conn = getConnection();
200             stmt = conn.createStatement();
201             
202             conn.setAutoCommit(false);
203             
204             
205             //get CLOB locator
206 sSql.append("select ");
207             sSql.append(sColumn);
208             sSql.append(" from ");
209             sSql.append(sTable);
210             sSql.append(" where ");
211             sSql.append(sCondition);
212             
213             sSql.append(" for update");
214             
215             rs = stmt.executeQuery(sSql.toString());
216             
217             Clob clob = null;
218             
219             if (rs.next()) {
220                 clob = rs.getClob(1);
221             }
222             
223             //create input stream
224 Writer outstream = clob.setCharacterStream(0);
225             
226             try {
227                 outstream.write(addEscapeChars(sClob));
228                 outstream.close();
229             } catch (IOException e) {
230                 throw new DataStoreException("Error writing CLOB to database: " +
231                         e.getMessage());
232             }
233             
234             conn.commit();
235             conn.setAutoCommit(true);
236             
237             if (stmt != null) {
238                 stmt.close();
239             }
240             
241             if (rs != null) {
242                 rs.close();
243             }
244             
245             if (isPooledConnection() && (conn != null)) {
246                 this.closeConnection(conn);
247             }
248         } catch (SQLException e) {
249             throw new DataStoreException("SQLException: " + e.getMessage());
250         }
251     }
252     
253     /* (non-Javadoc)
254      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getClob(java.lang.String, java.lang.String, java.lang.String)
255      */
256     public String JavaDoc getClob(String JavaDoc sTable, String JavaDoc sColumn, String JavaDoc sCondition)
257     throws DataStoreException {
258         Connection conn = null;
259         Statement stmt = null;
260         ResultSet rs = null;
261         String JavaDoc sReturn = null;
262         
263         if ((sCondition == null) || (sCondition.length() == 0)) {
264             throw new DataStoreException("Missing CLOB condition");
265         }
266         
267         StringBuffer JavaDoc sSql = new StringBuffer JavaDoc();
268         
269         try {
270             conn = getConnection();
271             
272             conn.setAutoCommit(false);
273             
274             stmt = conn.createStatement();
275             
276             sSql.append("select ");
277             sSql.append(sColumn);
278             sSql.append(" from ");
279             sSql.append(sTable);
280             sSql.append(" where ");
281             sSql.append(sCondition);
282             
283             rs = stmt.executeQuery(sSql.toString());
284             
285             if (rs.next()) {
286                 
287                 Clob clob = rs.getClob(1);
288                 
289                 try {
290                     sReturn = Clob2String(clob);
291                 } catch (Exception JavaDoc e) {
292                     throw new DataStoreException(
293                             "Error converting CLOB to a String: " +
294                             e.getMessage());
295                 }
296             }
297             
298             conn.commit();
299             conn.setAutoCommit(true);
300             
301             if (stmt != null) {
302                 stmt.close();
303             }
304             
305             if (rs != null) {
306                 rs.close();
307             }
308             
309             if (isPooledConnection() && (conn != null)) {
310                 this.closeConnection(conn);
311             }
312         } catch (SQLException e) {
313             throw new DataStoreException("SQLException: " + e.getMessage());
314         }
315         
316         return sReturn;
317     }
318     
319     
320     /**
321      * Converts and Oracle Clob object to a Java String object.
322      *
323      * @param clob the CLOB to convert
324      * @return the content as string
325      * @throws DataStoreException if an error occurs
326      */
327     private String JavaDoc Clob2String(Clob clob) throws DataStoreException {
328         StringBuffer JavaDoc strbuf = new StringBuffer JavaDoc();
329         String JavaDoc sMattString = "";
330         
331         try {
332             // get character stream to retrieve clob data
333 char[] buffer = new char[(int) clob.length()];
334             
335             long mattLong = 1;
336             
337             sMattString = clob.getSubString(mattLong, (int) clob.length());
338             
339             
340         } catch (SQLException e) {
341             throw new DataStoreException("SQLException: " + e.getMessage());
342         }
343         
344         return sMattString;
345     }
346     
347     
348     /* (non-Javadoc)
349      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#addEscapeChars(java.lang.String)
350      */
351     protected String JavaDoc addEscapeChars(String JavaDoc sOldString) {
352         int marker = -1;
353         int lastmarker = 0;
354         int quotemarker = -1;
355         
356         if (sOldString == null) {
357             return "";
358         }
359         
360         StringBuffer JavaDoc sBuf = new StringBuffer JavaDoc();
361         
362         quotemarker = sOldString.indexOf("'");
363         
364         if (quotemarker >= 0) {
365             marker = quotemarker;
366         }
367         
368         if (marker < 0) {
369             return sOldString;
370         } else {
371             while (marker >= 0) {
372                 //append to stringbuffer
373 sBuf.append(sOldString.substring(lastmarker, marker));
374                 sBuf.append("'");
375                 
376                 
377                 //reset markers
378 quotemarker = -1;
379                 lastmarker = marker;
380                 
381                 quotemarker = sOldString.indexOf("'", marker + 1);
382                 
383                 if (quotemarker >= 0) {
384                     marker = quotemarker;
385                 } else {
386                     marker = -1;
387                 }
388             }
389             
390             sBuf.append(sOldString.substring(lastmarker));
391             
392             return (sBuf.toString());
393         }
394     }
395     
396     /* (non-Javadoc)
397      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateDataType()
398      */
399     public String JavaDoc getDateDataType() {
400         return "DATE";
401     }
402     
403     /* (non-Javadoc)
404      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getCLOBDataType()
405      */
406     public String JavaDoc getCLOBDataType() {
407         return "CLOB";
408     }
409     
410     /* (non-Javadoc)
411      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getBooleanDataType()
412      */
413     public String JavaDoc getBooleanDataType() {
414         return "SMALLINT";
415     }
416     
417     /* (non-Javadoc)
418      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getTableList()
419      */
420     public List getTableList() throws DataStoreException {
421         Vector tables = new Vector();
422         ResultSet rs = null;
423         
424         try {
425             rs = executeQuery(
426             "select table_name from user_tables where table_name not like '%$%' order by table_name");
427             
428             while (rs.next()) {
429                 tables.add(rs.getString(1).trim());
430             }
431             
432             rs.close();
433         } catch (SQLException e) {
434             throw new DataStoreException(e);
435         }
436         
437         return tables;
438     }
439     
440     
441     /* (non-Javadoc)
442      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceList()
443      */
444     public List getSequenceList() throws DataStoreException {
445         Vector seqs = new Vector();
446         ResultSet rs = null;
447         
448         try {
449             rs = executeQuery(
450             "select sequence_name from seq order by sequence_name");
451             
452             while (rs.next()) {
453                 seqs.add(rs.getString(1).trim());
454             }
455             
456             rs.close();
457         } catch (SQLException e) {
458             throw new DataStoreException(e);
459         }
460         
461         return seqs;
462     }
463     
464     
465     /* (non-Javadoc)
466      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateAsSQL(java.lang.String)
467      */
468     protected String JavaDoc getDateAsSQL(String JavaDoc date) {
469         // localise this, as it is different for different DB implementations,
470 // SQL Server
471 StringBuffer JavaDoc sSql = new StringBuffer JavaDoc();
472         
473         sSql.append(" TO_DATE('");
474         sSql.append(date);
475         sSql.append("','MM-DD-YYYY HH24:MI:SS' )");
476         
477         return sSql.toString();
478     }
479     
480     /* (non-Javadoc)
481      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getJoinCondition(org.openharmonise.commons.dsi.ColumnRef, org.openharmonise.commons.dsi.ColumnRef, boolean)
482      */
483     public String JavaDoc getJoinCondition(ColumnRef ref1, ColumnRef ref2,
484             boolean bIsOuter) {
485         StringBuffer JavaDoc sSql = new StringBuffer JavaDoc();
486         
487         sSql.append(ref1.getFullRef());
488         
489         if (bIsOuter == true) {
490             sSql.append("(+)");
491         }
492         
493         sSql.append("=");
494         
495         sSql.append(ref2.getFullRef());
496         
497         return sSql.toString();
498     }
499     
500     /* (non-Javadoc)
501      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getFunction(org.openharmonise.commons.dsi.dml.Function)
502      */
503     protected String JavaDoc getFunction(Function func) throws DataStoreException {
504         throw new DataStoreException("Not implemented");
505     }
506     
507     /* (non-Javadoc)
508      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#createTable(org.openharmonise.commons.dsi.ddl.TableDefinition)
509      */
510     public void createTable(TableDefinition tblDef) throws DataStoreException {
511         throw new UnsupportedOperationException JavaDoc("Method not implemented");
512     }
513 }
Popular Tags