001: /*
002: * The contents of this file are subject to the
003: * Mozilla Public License Version 1.1 (the "License");
004: * you may not use this file except in compliance with the License.
005: * You may obtain a copy of the License at http://www.mozilla.org/MPL/
006: *
007: * Software distributed under the License is distributed on an "AS IS"
008: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.
009: * See the License for the specific language governing rights and
010: * limitations under the License.
011: *
012: * The Initial Developer of the Original Code is Simulacra Media Ltd.
013: * Portions created by Simulacra Media Ltd are Copyright (C) Simulacra Media Ltd, 2004.
014: *
015: * All Rights Reserved.
016: *
017: * Contributor(s):
018: */
019: package org.openharmonise.commons.dsi.impl;
020:
021: import java.sql.*;
022:
023: import java.util.*;
024:
025: import org.openharmonise.commons.dsi.*;
026: import org.openharmonise.commons.dsi.ddl.*;
027: import org.openharmonise.commons.dsi.dml.*;
028:
029: /**
030: * Class providing interface to a MySQL database.
031: *
032: * @author Michael Bell
033: *
034: * @see java.sql
035: */
036: public class DataStoreInterfaceMySQL extends AbstractDataStoreInterface {
037:
038: //static initialiser block
039: static {
040: DB_DATEFORMAT = "yyyy-MM-dd HH:mm:ss";
041: }
042:
043: /**
044: * Constructs a MySQL data store interface with no DB settings.
045: */
046: public DataStoreInterfaceMySQL() {
047: super ();
048: }
049:
050: /**
051: * Constructs a MySQL data store interface with the given connection
052: * type.
053: *
054: * @param nConnectionType the connection type
055: * @throws DataStoreException if any errors occur
056: */
057: public DataStoreInterfaceMySQL(int nConnectionType)
058: throws DataStoreException {
059: super (nConnectionType);
060: }
061:
062: /* (non-Javadoc)
063: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceNextValue(java.lang.String)
064: */
065: public int getSequenceNextValue(String sSeqName)
066: throws DataStoreException, SQLException {
067: Connection conn = null;
068: ResultSet rs = null;
069: Statement stmt = null;
070: String sSql = null;
071: int nSeq = -1;
072:
073: conn = getConnection();
074:
075: stmt = conn.createStatement();
076:
077: sSql = "UPDATE oh_seq SET id = id +1 WHERE seq_name='"
078: + sSeqName + "'";
079: stmt.executeUpdate(sSql);
080:
081: sSql = "SELECT id from oh_seq WHERE seq_name='" + sSeqName
082: + "'";
083: rs = stmt.executeQuery(sSql);
084:
085: if (rs.next()) {
086: nSeq = rs.getInt(1);
087: } else {
088: throw new DataStoreException("Sequence [" + sSeqName
089: + "] not found.");
090: }
091:
092: if (rs != null) {
093: rs.close();
094: }
095:
096: if (stmt != null) {
097: stmt.close();
098: }
099:
100: if (isPooledConnection() && (conn != null)) {
101: this .closeConnection(conn);
102: }
103:
104: return nSeq;
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 sTable, String sColumn, String sClob,
111: String 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 sSql = new StringBuffer();
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(addEscapeChars(sClob));
132: sSql.append("' where ");
133: sSql.append(sCondition);
134:
135: stmt.execute(sSql.toString());
136:
137: if (isPooledConnection() && (conn != null)) {
138: this .closeConnection(conn);
139: }
140: } catch (SQLException e) {
141: throw new DataStoreException("SQLException: "
142: + e.getMessage());
143: }
144: }
145:
146: /* (non-Javadoc)
147: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#updateClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
148: */
149: public void updateClob(String sTable, String sColumn, String sClob,
150: String sCondition) throws DataStoreException {
151: Connection conn = null;
152: Statement stmt = null;
153: ResultSet rs = null;
154:
155: if ((sCondition == null) || (sCondition.length() == 0)) {
156: throw new DataStoreException("Missing CLOB condition");
157: }
158:
159: StringBuffer sSql = new StringBuffer();
160:
161: try {
162: conn = getConnection();
163: stmt = conn.createStatement();
164:
165: sSql.append("update ");
166: sSql.append(sTable);
167: sSql.append(" set ");
168: sSql.append(sColumn);
169: sSql.append(" = '");
170: sSql.append(this .addEscapeChars(sClob));
171: sSql.append("' where ");
172: sSql.append(sCondition);
173:
174: stmt.execute(sSql.toString());
175:
176: if (stmt != null) {
177: stmt.close();
178: }
179:
180: if (rs != null) {
181: rs.close();
182: }
183:
184: if (isPooledConnection() && (conn != null)) {
185: this .closeConnection(conn);
186: }
187: } catch (SQLException e) {
188: throw new DataStoreException("SQLException: "
189: + e.getMessage());
190: }
191: }
192:
193: /* (non-Javadoc)
194: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getClob(java.lang.String, java.lang.String, java.lang.String)
195: */
196: public String getClob(String sTable, String sColumn,
197: String sCondition) throws DataStoreException {
198: Connection conn = null;
199: Statement stmt = null;
200: ResultSet rs = null;
201: String sReturn = null;
202:
203: if ((sCondition == null) || (sCondition.length() == 0)) {
204: throw new DataStoreException("Missing CLOB condition");
205: }
206:
207: StringBuffer sSql = new StringBuffer();
208:
209: try {
210: conn = getConnection();
211:
212: stmt = conn.createStatement();
213:
214: sSql.append("select ");
215: sSql.append(sColumn);
216: sSql.append(" from ");
217: sSql.append(sTable);
218: sSql.append(" where ");
219: sSql.append(sCondition);
220:
221: try {
222: rs = stmt.executeQuery(sSql.toString());
223: } catch (SQLException e) {
224: throw new DataStoreException(sSql.toString() + " : "
225: + e.getMessage());
226: }
227:
228: if (rs.next()) {
229: sReturn = rs.getString(1);
230: }
231:
232: if (stmt != null) {
233: stmt.close();
234: }
235:
236: if (rs != null) {
237: rs.close();
238: }
239:
240: if (isPooledConnection() && (conn != null)) {
241: this .closeConnection(conn);
242: }
243: } catch (SQLException e) {
244: throw new DataStoreException("SQLException: "
245: + e.getMessage());
246: }
247:
248: return sReturn;
249: }
250:
251: /* (non-Javadoc)
252: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#addEscapeChars(java.lang.String)
253: */
254: protected String addEscapeChars(String sOldString) {
255: int marker = -1;
256: int lastmarker = 0;
257: int quotemarker = -1;
258:
259: if (sOldString == null) {
260: return "";
261: }
262:
263: StringTokenizer tokenizer = new StringTokenizer(sOldString, "'");
264:
265: StringBuffer sBuf = new StringBuffer();
266:
267: while (tokenizer.hasMoreTokens() == true) {
268: String token = tokenizer.nextToken();
269:
270: sBuf.append(token);
271: if (tokenizer.hasMoreTokens() == true) {
272: sBuf.append("''");
273: }
274: }
275:
276: tokenizer = new StringTokenizer(sBuf.toString(), "\\");
277: sBuf.setLength(0);
278: while (tokenizer.hasMoreTokens() == true) {
279: String token = tokenizer.nextToken();
280:
281: sBuf.append(token);
282: if (tokenizer.hasMoreTokens() == true) {
283: sBuf.append("\\\\");
284: }
285: }
286:
287: return sBuf.toString();
288: }
289:
290: /* (non-Javadoc)
291: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateDataType()
292: */
293: public String getDateDataType() {
294: return "DATETIME";
295: }
296:
297: /* (non-Javadoc)
298: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getCLOBDataType()
299: */
300: public String getCLOBDataType() {
301: return "longtext";
302: }
303:
304: /* (non-Javadoc)
305: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getBooleanDataType()
306: */
307: public String getBooleanDataType() {
308: return "SMALLINT";
309: }
310:
311: /* (non-Javadoc)
312: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getTableList()
313: */
314: public List getTableList() throws DataStoreException {
315: Vector tables = new Vector();
316:
317: ResultSet rs = null;
318:
319: try {
320: rs = executeQuery("show tables");
321:
322: while (rs.next()) {
323: tables.add(rs.getString(1).trim());
324: }
325:
326: rs.close();
327: } catch (SQLException e) {
328: throw new DataStoreException(e);
329: }
330:
331: return tables;
332: }
333:
334: /* (non-Javadoc)
335: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceList()
336: */
337: public List getSequenceList() throws DataStoreException {
338: Vector seqs = new Vector();
339: ResultSet rs = null;
340:
341: try {
342: rs = executeQuery("select seq_name from oh_seq order by seq_name");
343:
344: while (rs.next()) {
345: seqs.add(rs.getString(1).trim());
346: }
347:
348: rs.close();
349: } catch (SQLException e) {
350: throw new DataStoreException(e);
351: }
352:
353: return seqs;
354: }
355:
356: /* (non-Javadoc)
357: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateAsSQL(java.lang.String)
358: */
359: protected String getDateAsSQL(String date) {
360: // localise this, as it is different for different DB implementations,
361: // SQL Server
362: StringBuffer sSql = new StringBuffer();
363:
364: sSql.append("'").append(date).append("'");
365:
366: return sSql.toString();
367:
368: }
369:
370: /* (non-Javadoc)
371: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getJoinCondition(org.openharmonise.commons.dsi.ColumnRef, org.openharmonise.commons.dsi.ColumnRef, boolean)
372: */
373: public String getJoinCondition(ColumnRef ref1, ColumnRef ref2,
374: boolean bIsOuter) {
375: StringBuffer sSql = new StringBuffer();
376:
377: sSql.append(ref1.getFullRef());
378:
379: //TODO handle outer joins
380:
381: sSql.append("=");
382:
383: sSql.append(ref2.getFullRef());
384:
385: return sSql.toString();
386: }
387:
388: /* (non-Javadoc)
389: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getFunction(org.openharmonise.commons.dsi.dml.Function)
390: */
391: protected String getFunction(Function func)
392: throws DataStoreException {
393: throw new UnsupportedOperationException(
394: "Method not implemented");
395: }
396:
397: /* (non-Javadoc)
398: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#createTable(org.openharmonise.commons.dsi.ddl.TableDefinition)
399: */
400: public void createTable(TableDefinition tblDef)
401: throws DataStoreException {
402: throw new UnsupportedOperationException(
403: "Method not implemented");
404: }
405: }
|