001 /* 002 * Copyright (C) 2008-2010 by Holger Arndt 003 * 004 * This file is part of the Universal Java Matrix Package (UJMP). 005 * See the NOTICE file distributed with this work for additional 006 * information regarding copyright ownership and licensing. 007 * 008 * UJMP is free software; you can redistribute it and/or modify 009 * it under the terms of the GNU Lesser General Public License as 010 * published by the Free Software Foundation; either version 2 011 * of the License, or (at your option) any later version. 012 * 013 * UJMP is distributed in the hope that it will be useful, 014 * but WITHOUT ANY WARRANTY; without even the implied warranty of 015 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 016 * GNU Lesser General Public License for more details. 017 * 018 * You should have received a copy of the GNU Lesser General Public 019 * License along with UJMP; if not, write to the 020 * Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, 021 * Boston, MA 02110-1301 USA 022 */ 023 024 package org.ujmp.jdbc; 025 026 import java.io.Closeable; 027 import java.io.File; 028 import java.io.IOException; 029 import java.sql.Connection; 030 import java.sql.DriverManager; 031 import java.sql.PreparedStatement; 032 import java.sql.ResultSet; 033 import java.sql.SQLException; 034 import java.sql.Statement; 035 036 import org.ujmp.core.exceptions.MatrixException; 037 import org.ujmp.core.interfaces.Erasable; 038 import org.ujmp.core.objectmatrix.stub.AbstractSparseObjectMatrix; 039 import org.ujmp.core.util.MathUtil; 040 041 public class JDBCSparseObjectMatrix extends AbstractSparseObjectMatrix 042 implements Closeable, Erasable { 043 private static final long serialVersionUID = -5801269687893136766L; 044 045 private boolean useExtendedSQL = false; 046 047 private transient Connection connection = null; 048 049 private transient PreparedStatement getEntryStatement = null; 050 051 private transient PreparedStatement insertEntryStatement = null; 052 053 private transient PreparedStatement deleteEntryStatement = null; 054 055 private transient PreparedStatement selectAllStatement = null; 056 057 private final String url; 058 059 private final String username; 060 061 private String password = null; 062 063 private final String tableName; 064 065 private final String[] columnsForCoordinates; 066 067 private final String columnForValue; 068 069 private final long[] size; 070 071 public JDBCSparseObjectMatrix(long... size) throws ClassNotFoundException, 072 IOException, SQLException { 073 this(size, "jdbc:hsqldb:" 074 + File.createTempFile("hsqldbtemp", "").getAbsolutePath() 075 + ";shutdown=true", "SA", "", "matrixTable", "valueColumn", 076 createColumnNames(size)); 077 } 078 079 private static String[] createColumnNames(long... size) { 080 String[] cols = new String[size.length]; 081 for (int c = size.length; --c != -1;) { 082 cols[c] = "column" + c; 083 } 084 return cols; 085 } 086 087 public JDBCSparseObjectMatrix(long[] size, String url, String username, 088 String password, String tableName, String columnForValue, 089 String... columnsForCoordinates) throws ClassNotFoundException, 090 SQLException { 091 this.url = url; 092 this.size = size; 093 this.username = username; 094 this.password = password; 095 this.tableName = tableName; 096 this.columnForValue = columnForValue; 097 this.columnsForCoordinates = columnsForCoordinates; 098 099 if (url.startsWith("jdbc:hsqldb:")) { 100 Class.forName("org.hsqldb.jdbcDriver"); 101 } else if (url.startsWith("jdbc:mysql:")) { 102 Class.forName("org.mysql.jdbc.Driver"); 103 } else if (url.startsWith("jdbc:postgresql:")) { 104 Class.forName("org.postgresql.Driver"); 105 } else if (url.startsWith("jdbc:derby:")) { 106 Class.forName("org.apache.derby.jdbc.Driver40"); 107 } 108 109 createTableIfNotExists(); 110 } 111 112 public JDBCSparseObjectMatrix(long[] size, Connection connection, 113 String tableName, String columnForValue, 114 String... columnsForCoordinates) throws SQLException { 115 this.size = size; 116 this.connection = connection; 117 this.username = connection.getMetaData().getUserName(); 118 this.url = connection.getMetaData().getURL(); 119 this.tableName = tableName; 120 this.columnForValue = columnForValue; 121 this.columnsForCoordinates = columnsForCoordinates; 122 createTableIfNotExists(); 123 } 124 125 private void createTableIfNotExists() throws SQLException { 126 // TODO: check if exists 127 // TODO: add primary key 128 Statement statement = getConnection().createStatement(); 129 StringBuilder sb = new StringBuilder(); 130 131 sb.append("CREATE TABLE ").append(tableName); 132 sb.append(" (valueColumn float"); 133 for (String c : columnsForCoordinates) { 134 sb.append(", ").append(c).append(" int"); 135 } 136 sb.append(")"); 137 statement.execute(sb.toString()); 138 } 139 140 private PreparedStatement getSelectAllStatement() throws SQLException { 141 if (selectAllStatement == null) { 142 StringBuilder s = new StringBuilder(); 143 s.append("select "); 144 s.append(columnForValue); 145 s.append(", "); 146 for (int i = 0; i < columnsForCoordinates.length; i++) { 147 s.append(columnsForCoordinates[i]); 148 if (i < columnsForCoordinates.length - 1) { 149 s.append(", "); 150 } 151 } 152 s.append(" from "); 153 s.append(tableName); 154 selectAllStatement = getConnection().prepareStatement(s.toString()); 155 } 156 return selectAllStatement; 157 } 158 159 private PreparedStatement getGetEntryStatement() throws SQLException { 160 if (getEntryStatement == null) { 161 StringBuilder s = new StringBuilder(); 162 s.append("select "); 163 s.append(columnForValue); 164 s.append(" from "); 165 s.append(tableName); 166 s.append(" where "); 167 for (int i = 0; i < columnsForCoordinates.length; i++) { 168 s.append(columnsForCoordinates[i]); 169 s.append("=?"); 170 if (i < columnsForCoordinates.length - 1) { 171 s.append(" and "); 172 } 173 } 174 getEntryStatement = getConnection().prepareStatement(s.toString()); 175 } 176 return getEntryStatement; 177 } 178 179 private PreparedStatement getInsertEntryStatement() throws SQLException { 180 if (insertEntryStatement == null) { 181 StringBuilder s = new StringBuilder(); 182 s.append("insert into "); 183 s.append(tableName); 184 s.append(" ("); 185 s.append(columnForValue); 186 s.append(", "); 187 for (int i = 0; i < columnsForCoordinates.length; i++) { 188 s.append(columnsForCoordinates[i]); 189 if (i < columnsForCoordinates.length - 1) { 190 s.append(", "); 191 } 192 } 193 s.append(") values (?, "); 194 for (int i = 0; i < columnsForCoordinates.length; i++) { 195 s.append("?"); 196 if (i < columnsForCoordinates.length - 1) { 197 s.append(", "); 198 } 199 } 200 s.append(")"); 201 202 if (useExtendedSQL) { 203 s.append(" on duplicate key update "); 204 s.append(columnForValue); 205 s.append("=?"); 206 } 207 208 insertEntryStatement = getConnection().prepareStatement( 209 s.toString()); 210 } 211 return insertEntryStatement; 212 } 213 214 private PreparedStatement getDeleteEntryStatement() throws SQLException { 215 if (deleteEntryStatement == null) { 216 StringBuilder s = new StringBuilder(); 217 s.append("delete from "); 218 s.append(tableName); 219 s.append(" where "); 220 for (int i = 0; i < columnsForCoordinates.length; i++) { 221 s.append(columnsForCoordinates[i]); 222 s.append("=?"); 223 if (i < columnsForCoordinates.length - 1) { 224 s.append(" and "); 225 } 226 } 227 deleteEntryStatement = getConnection().prepareStatement( 228 s.toString()); 229 } 230 return deleteEntryStatement; 231 } 232 233 public synchronized Object getObject(long... coordinates) { 234 try { 235 PreparedStatement ps = getGetEntryStatement(); 236 for (int i = 0; i < coordinates.length; i++) { 237 ps.setLong(i + 1, coordinates[i]); 238 } 239 ResultSet rs = ps.executeQuery(); 240 Object o = null; 241 if (rs.next()) { 242 o = rs.getObject(1); 243 } 244 rs.close(); 245 return o; 246 } catch (Exception e) { 247 throw new MatrixException(e); 248 } 249 } 250 251 private void deleteObject(long... coordinates) throws SQLException { 252 PreparedStatement ps = getDeleteEntryStatement(); 253 for (int i = 0; i < coordinates.length; i++) { 254 ps.setLong(i + 1, coordinates[i]); 255 } 256 ps.execute(); 257 } 258 259 public synchronized void setObject(Object value, long... coordinates) { 260 try { 261 if (MathUtil.getDouble(value) == 0.0) { 262 deleteObject(coordinates); 263 } else { 264 265 if (!useExtendedSQL) { 266 deleteObject(coordinates); 267 } 268 269 PreparedStatement ps = getInsertEntryStatement(); 270 271 ps.setObject(1, value); 272 for (int i = 0; i < coordinates.length; i++) { 273 ps.setLong(i + 2, coordinates[i]); 274 } 275 276 if (useExtendedSQL) { 277 ps.setObject(coordinates.length + 2, value); 278 } 279 280 ps.executeUpdate(); 281 } 282 } catch (Exception e) { 283 throw new MatrixException(e); 284 } 285 } 286 287 public synchronized long[] getSize() { 288 return size; 289 } 290 291 public synchronized void close() throws IOException { 292 try { 293 if (connection != null) { 294 connection.close(); 295 } 296 } catch (SQLException e) { 297 throw new IOException(e.toString()); 298 } 299 } 300 301 public synchronized Connection getConnection() throws SQLException { 302 if (connection == null || connection.isClosed()) { 303 connection = DriverManager.getConnection(getUrl(), getUsername(), 304 getPassword()); 305 // DatabaseMetaData dbm = connection.getMetaData(); 306 // dbm = null; 307 // ResultSet rs = dbm.getTables(null, null, "%", null); 308 309 // rs = meta.getPrimaryKeys(null, null, "table"); 310 // while (rs.next()) { 311 // String columnName = rs.getString("COLUMN_NAME"); 312 // System.out 313 // .println("getPrimaryKeys(): columnName=" + columnName); 314 // } 315 } 316 return connection; 317 } 318 319 public String getUrl() { 320 return url; 321 } 322 323 public String getUsername() { 324 return username; 325 } 326 327 public String getPassword() { 328 return password; 329 } 330 331 public boolean contains(long... coordinates) throws MatrixException { 332 return getObject(coordinates) != null; 333 } 334 335 @Override 336 protected void finalize() throws Throwable { 337 super.finalize(); 338 if (connection != null) { 339 if (!connection.isClosed()) { 340 connection.close(); 341 } 342 connection = null; 343 } 344 } 345 346 public void erase() throws IOException { 347 try { 348 Statement st = getConnection().createStatement(); 349 st.execute("drop table " + tableName); 350 } catch (SQLException e) { 351 throw new IOException(e); 352 } 353 354 close(); 355 356 // maybe it was just a temporary database? 357 if (url.contains("hsqldbtemp")) { 358 String[] s = url.split(":"); 359 if (s.length > 2) { 360 String file = ""; 361 for (int i = 2; i < s.length; i++) { 362 file += s[i]; 363 if (i < s.length - 1) { 364 file += ":"; 365 } 366 } 367 s = file.split(";"); 368 file = s[0]; 369 370 File file1 = new File(file); 371 if (file1.exists()) { 372 file1.delete(); 373 } 374 File file2 = new File(file + ".log"); 375 if (file2.exists()) { 376 file2.delete(); 377 } 378 File file3 = new File(file + ".properties"); 379 if (file3.exists()) { 380 file3.delete(); 381 } 382 File file4 = new File(file + ".script"); 383 if (file4.exists()) { 384 file4.delete(); 385 } 386 } 387 } 388 } 389 390 }