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.IOException;
028    import java.sql.Connection;
029    import java.sql.DriverManager;
030    import java.sql.PreparedStatement;
031    import java.sql.ResultSet;
032    import java.sql.ResultSetMetaData;
033    import java.sql.SQLException;
034    import java.util.HashMap;
035    import java.util.Map;
036    
037    import org.ujmp.core.collections.SoftHashMap;
038    import org.ujmp.core.exceptions.MatrixException;
039    import org.ujmp.core.objectmatrix.stub.AbstractDenseObjectMatrix2D;
040    
041    public abstract class AbstractDenseJDBCMatrix2D extends
042                    AbstractDenseObjectMatrix2D implements Closeable {
043            private static final long serialVersionUID = -9077208839474846706L;
044    
045            private final Map<Integer, Connection> connections = new HashMap<Integer, Connection>();
046    
047            private String url = null;
048    
049            private String username = "sa";
050    
051            private String password = "";
052    
053            private final Map<Integer, PreparedStatement> selectStatements = new HashMap<Integer, PreparedStatement>();
054    
055            private final Map<Integer, ResultSet> resultSets = new SoftHashMap<Integer, ResultSet>();
056    
057            private String sqlStatement = null;
058    
059            private long[] size = null;
060    
061            private final int resultSize = Integer.MAX_VALUE;
062    
063            private final int connectionCount = 1;
064    
065            private int statementId = 0;
066    
067            public AbstractDenseJDBCMatrix2D(String url, String sqlStatement,
068                            String username, String password) {
069                    this.url = url;
070                    this.username = username;
071                    this.password = password;
072                    this.sqlStatement = sqlStatement;
073            }
074    
075            public String getSQLStatement() {
076                    return sqlStatement;
077            }
078    
079            public synchronized Object getObject(long row, long column) {
080                    return getObject((int) row, (int) column);
081            }
082    
083            public synchronized Object getObject(int row, int column) {
084                    try {
085                            ResultSet rs = getResultSet(row);
086                            return rs.getObject(column + 1);
087                    } catch (SQLException e) {
088                            if ("S1009".equals(e.getSQLState())) {
089                                    // ignore Value '0000-00-00' can not be represented
090                                    return null;
091                            }
092                            throw new MatrixException(e);
093                    }
094            }
095    
096            public final String getSelectString() {
097                    return sqlStatement;
098            }
099    
100            public synchronized void setObject(Object value, long row, long column) {
101            }
102    
103            public synchronized void setObject(Object value, int row, int column) {
104            }
105    
106            public synchronized long[] getSize() {
107                    try {
108                            if (size == null) {
109                                    ResultSet rs = getResultSet(1);
110                                    ResultSetMetaData rsMetaData = rs.getMetaData();
111                                    long columnCount = rsMetaData.getColumnCount();
112                                    rs.last();
113                                    long rowCount = rs.getRow();
114                                    size = new long[] { rowCount, columnCount };
115                            }
116                            return size;
117                    } catch (SQLException e) {
118                            throw new MatrixException(e);
119                    }
120            }
121    
122            public synchronized void close() throws IOException {
123                    try {
124                            for (Connection connection : connections.values()) {
125                                    if (connection != null) {
126                                            connection.close();
127                                    }
128                            }
129                    } catch (SQLException e) {
130                            throw new IOException(e.toString());
131                    }
132            }
133    
134            public synchronized ResultSet getResultSet(long row) throws SQLException {
135                    int pos = (int) row / resultSize;
136                    int offset = pos * resultSize;
137                    int remain = (int) row - offset;
138                    ResultSet resultSet = resultSets.get(pos);
139                    if (resultSet == null) {
140                            PreparedStatement ps = getSelectStatement();
141                            // ps.setInt(1, resultSize);
142                            // ps.setInt(2, offset);
143                            resultSet = ps.executeQuery();
144                            resultSets.put(pos, resultSet);
145                            if (getMatrixAnnotation() == null) {
146                                    setMatrixAnnotation(getUrl() + " " + getSelectString());
147                                    ResultSetMetaData rsm = resultSet.getMetaData();
148                                    for (int c = 0; c < rsm.getColumnCount(); c++) {
149                                            setColumnLabel(c, rsm.getColumnLabel(c + 1));
150                                    }
151                            }
152                    }
153                    resultSet.absolute(remain + 1);
154                    // resultSet.next();
155                    return resultSet;
156            }
157    
158            public synchronized PreparedStatement getSelectStatement()
159                            throws SQLException {
160                    PreparedStatement selectStatement = selectStatements.get(statementId);
161                    if (selectStatement == null) {
162                            selectStatement = getConnection(statementId).prepareStatement(
163                                            getSelectString(), ResultSet.TYPE_SCROLL_SENSITIVE,
164                                            ResultSet.CONCUR_READ_ONLY);
165                    }
166                    statementId = ++statementId > connectionCount ? 0 : statementId;
167                    return selectStatement;
168            }
169    
170            public synchronized Connection getConnection(int id) throws SQLException {
171                    Connection connection = connections.get(id);
172                    if (connection == null) {
173                            connection = DriverManager.getConnection(getUrl(), getUsername(),
174                                            getPassword());
175                            // DatabaseMetaData dbm = connection.getMetaData();
176                            // dbm = null;
177                            // ResultSet rs = dbm.getTables(null, null, "%", null);
178    
179                            // rs = meta.getPrimaryKeys(null, null, "table");
180                            // while (rs.next()) {
181                            // String columnName = rs.getString("COLUMN_NAME");
182                            // System.out
183                            // .println("getPrimaryKeys(): columnName=" + columnName);
184                            // }
185    
186                            connections.put(id, connection);
187                    }
188                    id = ++id >= connectionCount ? 0 : id;
189                    return connection;
190            }
191    
192            public String getUrl() {
193                    return url;
194            }
195    
196            public String getUsername() {
197                    return username;
198            }
199    
200            public String getPassword() {
201                    return password;
202            }
203    
204    }