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 }