KickJava   Java API By Example, From Geeks To Geeks.

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


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 import java.sql.*;
22
23
24 import java.util.*;
25
26 import org.openharmonise.commons.dsi.*;
27 import org.openharmonise.commons.dsi.ddl.*;
28 import org.openharmonise.commons.dsi.dml.*;
29
30
31 /**
32   * Class providing interface to a MySQL database.
33   *
34   * @author Michael Bell
35   *
36   * @see java.sql
37   */

38 public class DataStoreInterfaceMySQL extends AbstractDataStoreInterface {
39     
40     //static initialiser block
41
static {
42         DB_DATEFORMAT = "yyyy-MM-dd HH:mm:ss";
43     }
44
45     /**
46       * Constructs a MySQL data store interface with no DB settings.
47       */

48     public DataStoreInterfaceMySQL() {
49         super();
50     }
51
52    /**
53     * Constructs a MySQL 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 DataStoreInterfaceMySQL(int nConnectionType)
60                              throws DataStoreException {
61         super(nConnectionType);
62     }
63
64
65     /* (non-Javadoc)
66      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceNextValue(java.lang.String)
67      */

68     public int getSequenceNextValue(String JavaDoc sSeqName) throws DataStoreException,
69                                                             SQLException {
70         Connection conn = null;
71         ResultSet rs = null;
72         Statement stmt = null;
73         String JavaDoc sSql = null;
74         int nSeq = -1;
75
76         conn = getConnection();
77
78
79         stmt = conn.createStatement();
80
81         sSql = "UPDATE oh_seq SET id = id +1 WHERE seq_name='" + sSeqName +
82                "'";
83         stmt.executeUpdate(sSql);
84
85         sSql = "SELECT id from oh_seq WHERE seq_name='" + sSeqName + "'";
86         rs = stmt.executeQuery(sSql);
87
88         if (rs.next()) {
89             nSeq = rs.getInt(1);
90         } else {
91             throw new DataStoreException("Sequence [" + sSeqName +
92                                          "] not found.");
93         }
94
95         if (rs != null) {
96             rs.close();
97         }
98
99         if (stmt != null) {
100             stmt.close();
101         }
102
103         if (isPooledConnection() && (conn != null)) {
104             this.closeConnection(conn);
105         }
106
107         return nSeq;
108     }
109
110
111
112     /* (non-Javadoc)
113      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#insertClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
114      */

115     public void insertClob(String JavaDoc sTable, String JavaDoc sColumn, String JavaDoc sClob,
116                            String JavaDoc sCondition) throws DataStoreException {
117       Connection conn = null;
118       Statement stmt = null;
119       ResultSet rs = null;
120
121       if ((sCondition == null) || (sCondition.length() == 0)) {
122           throw new DataStoreException("Missing CLOB condition");
123       }
124
125       StringBuffer JavaDoc sSql = new StringBuffer JavaDoc();
126
127       try {
128           conn = getConnection();
129           stmt = conn.createStatement();
130
131           sSql.append("update ");
132           sSql.append(sTable);
133           sSql.append(" set ");
134           sSql.append(sColumn);
135           sSql.append(" = '");
136           sSql.append(addEscapeChars(sClob) );
137           sSql.append("' where ");
138           sSql.append(sCondition);
139
140           stmt.execute(sSql.toString());
141
142           if (isPooledConnection() && (conn != null)) {
143               this.closeConnection(conn);
144           }
145       } catch (SQLException e) {
146           throw new DataStoreException("SQLException: " + e.getMessage());
147         }
148     }
149
150
151     /* (non-Javadoc)
152      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#updateClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
153      */

154     public void updateClob(String JavaDoc sTable, String JavaDoc sColumn, String JavaDoc sClob,
155                            String JavaDoc sCondition) throws DataStoreException {
156       Connection conn = null;
157       Statement stmt = null;
158       ResultSet rs = null;
159
160       if ((sCondition == null) || (sCondition.length() == 0)) {
161           throw new DataStoreException("Missing CLOB condition");
162       }
163
164       StringBuffer JavaDoc sSql = new StringBuffer JavaDoc();
165
166       try {
167           conn = getConnection();
168           stmt = conn.createStatement();
169
170           sSql.append("update ");
171           sSql.append(sTable);
172           sSql.append(" set ");
173           sSql.append(sColumn);
174           sSql.append(" = '");
175           sSql.append(this.addEscapeChars(sClob));
176           sSql.append("' where ");
177           sSql.append(sCondition);
178
179           stmt.execute(sSql.toString());
180
181           if (stmt != null) {
182               stmt.close();
183           }
184
185           if (rs != null) {
186               rs.close();
187           }
188
189           if (isPooledConnection() && (conn != null)) {
190               this.closeConnection(conn);
191           }
192       } catch (SQLException e) {
193           throw new DataStoreException("SQLException: " + e.getMessage());
194         }
195     }
196
197     /* (non-Javadoc)
198      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getClob(java.lang.String, java.lang.String, java.lang.String)
199      */

200     public String JavaDoc getClob(String JavaDoc sTable, String JavaDoc sColumn, String JavaDoc sCondition)
201                    throws DataStoreException {
202       Connection conn = null;
203       Statement stmt = null;
204       ResultSet rs = null;
205       String JavaDoc sReturn = null;
206
207       if ((sCondition == null) || (sCondition.length() == 0)) {
208           throw new DataStoreException("Missing CLOB condition");
209       }
210
211       StringBuffer JavaDoc sSql = new StringBuffer JavaDoc();
212
213       try {
214           conn = getConnection();
215
216
217           stmt = conn.createStatement();
218
219           sSql.append("select ");
220           sSql.append(sColumn);
221           sSql.append(" from ");
222           sSql.append(sTable);
223           sSql.append(" where ");
224           sSql.append(sCondition);
225
226           try {
227               rs = stmt.executeQuery(sSql.toString());
228           } catch (SQLException e) {
229               throw new DataStoreException(sSql.toString() + " : " +
230                                            e.getMessage());
231           }
232
233           if (rs.next()) {
234               sReturn = rs.getString(1);
235           }
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         return sReturn;
253     }
254
255   
256     /* (non-Javadoc)
257      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#addEscapeChars(java.lang.String)
258      */

259     protected String JavaDoc addEscapeChars(String JavaDoc sOldString) {
260         int marker = -1;
261         int lastmarker = 0;
262         int quotemarker = -1;
263
264         if (sOldString == null) {
265             return "";
266         }
267
268         StringTokenizer tokenizer = new StringTokenizer(sOldString,"'");
269
270         StringBuffer JavaDoc sBuf = new StringBuffer JavaDoc();
271
272         while(tokenizer.hasMoreTokens() == true) {
273           String JavaDoc token = tokenizer.nextToken();
274
275           sBuf.append(token);
276           if(tokenizer.hasMoreTokens() == true) {
277             sBuf.append("''");
278           }
279         }
280
281         tokenizer = new StringTokenizer(sBuf.toString(),"\\");
282         sBuf.setLength(0);
283         while(tokenizer.hasMoreTokens() == true) {
284           String JavaDoc token = tokenizer.nextToken();
285
286           sBuf.append(token);
287           if(tokenizer.hasMoreTokens() == true) {
288             sBuf.append("\\\\");
289           }
290         }
291
292         return sBuf.toString();
293     }
294
295     /* (non-Javadoc)
296      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateDataType()
297      */

298     public String JavaDoc getDateDataType() {
299         return "DATETIME";
300     }
301
302     /* (non-Javadoc)
303      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getCLOBDataType()
304      */

305     public String JavaDoc getCLOBDataType() {
306         return "longtext";
307     }
308
309     /* (non-Javadoc)
310      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getBooleanDataType()
311      */

312     public String JavaDoc getBooleanDataType() {
313         return "SMALLINT";
314     }
315
316     /* (non-Javadoc)
317      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getTableList()
318      */

319     public List getTableList() throws DataStoreException {
320         Vector tables = new Vector();
321
322         ResultSet rs = null;
323
324         try {
325             rs = executeQuery("show tables");
326             
327             while (rs.next()) {
328                 tables.add(rs.getString(1).trim());
329             }
330             
331             rs.close();
332         } catch (SQLException e) {
333             throw new DataStoreException(e);
334         }
335
336
337         return tables;
338     }
339
340     /* (non-Javadoc)
341      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceList()
342      */

343     public List getSequenceList() throws DataStoreException {
344         Vector seqs = new Vector();
345         ResultSet rs = null;
346
347         try {
348             rs = executeQuery("select seq_name from oh_seq order by seq_name");
349             
350             while (rs.next()) {
351                 seqs.add(rs.getString(1).trim());
352             }
353             
354             rs.close();
355         } catch (SQLException e) {
356             throw new DataStoreException(e);
357         }
358
359         return seqs;
360     }
361
362     
363     /* (non-Javadoc)
364      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateAsSQL(java.lang.String)
365      */

366     protected String JavaDoc getDateAsSQL(String JavaDoc date) {
367         // localise this, as it is different for different DB implementations,
368
// SQL Server
369
StringBuffer JavaDoc sSql = new StringBuffer JavaDoc();
370
371         sSql.append("'").append(date).append("'");
372
373         return sSql.toString();
374
375     }
376
377     /* (non-Javadoc)
378      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getJoinCondition(org.openharmonise.commons.dsi.ColumnRef, org.openharmonise.commons.dsi.ColumnRef, boolean)
379      */

380     public String JavaDoc getJoinCondition(ColumnRef ref1, ColumnRef ref2,
381                                    boolean bIsOuter) {
382         StringBuffer JavaDoc sSql = new StringBuffer JavaDoc();
383
384         sSql.append(ref1.getFullRef());
385
386         //TODO handle outer joins
387

388         sSql.append("=");
389
390         sSql.append(ref2.getFullRef());
391
392         return sSql.toString();
393     }
394
395     /* (non-Javadoc)
396      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getFunction(org.openharmonise.commons.dsi.dml.Function)
397      */

398     protected String JavaDoc getFunction(Function func) throws DataStoreException {
399         throw new UnsupportedOperationException JavaDoc("Method not implemented");
400     }
401
402     /* (non-Javadoc)
403      * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#createTable(org.openharmonise.commons.dsi.ddl.TableDefinition)
404      */

405     public void createTable(TableDefinition tblDef) throws DataStoreException {
406         throw new UnsupportedOperationException JavaDoc("Method not implemented");
407     }
408 }
Popular Tags