1 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 38 public class DataStoreInterfaceMySQL extends AbstractDataStoreInterface { 39 40 static { 42 DB_DATEFORMAT = "yyyy-MM-dd HH:mm:ss"; 43 } 44 45 48 public DataStoreInterfaceMySQL() { 49 super(); 50 } 51 52 59 public DataStoreInterfaceMySQL(int nConnectionType) 60 throws DataStoreException { 61 super(nConnectionType); 62 } 63 64 65 68 public int getSequenceNextValue(String sSeqName) throws DataStoreException, 69 SQLException { 70 Connection conn = null; 71 ResultSet rs = null; 72 Statement stmt = null; 73 String 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 115 public void insertClob(String sTable, String sColumn, String sClob, 116 String 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 sSql = new StringBuffer (); 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 154 public void updateClob(String sTable, String sColumn, String sClob, 155 String 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 sSql = new StringBuffer (); 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 200 public String getClob(String sTable, String sColumn, String sCondition) 201 throws DataStoreException { 202 Connection conn = null; 203 Statement stmt = null; 204 ResultSet rs = null; 205 String sReturn = null; 206 207 if ((sCondition == null) || (sCondition.length() == 0)) { 208 throw new DataStoreException("Missing CLOB condition"); 209 } 210 211 StringBuffer sSql = new StringBuffer (); 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 259 protected String addEscapeChars(String 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 sBuf = new StringBuffer (); 271 272 while(tokenizer.hasMoreTokens() == true) { 273 String 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 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 298 public String getDateDataType() { 299 return "DATETIME"; 300 } 301 302 305 public String getCLOBDataType() { 306 return "longtext"; 307 } 308 309 312 public String getBooleanDataType() { 313 return "SMALLINT"; 314 } 315 316 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 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 366 protected String getDateAsSQL(String date) { 367 StringBuffer sSql = new StringBuffer (); 370 371 sSql.append("'").append(date).append("'"); 372 373 return sSql.toString(); 374 375 } 376 377 380 public String getJoinCondition(ColumnRef ref1, ColumnRef ref2, 381 boolean bIsOuter) { 382 StringBuffer sSql = new StringBuffer (); 383 384 sSql.append(ref1.getFullRef()); 385 386 388 sSql.append("="); 389 390 sSql.append(ref2.getFullRef()); 391 392 return sSql.toString(); 393 } 394 395 398 protected String getFunction(Function func) throws DataStoreException { 399 throw new UnsupportedOperationException ("Method not implemented"); 400 } 401 402 405 public void createTable(TableDefinition tblDef) throws DataStoreException { 406 throw new UnsupportedOperationException ("Method not implemented"); 407 } 408 } | Popular Tags |