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    }