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.sql.Connection;
027    import java.sql.DriverManager;
028    import java.sql.ResultSet;
029    import java.sql.ResultSetMetaData;
030    import java.sql.Statement;
031    
032    import org.ujmp.core.MatrixFactory;
033    import org.ujmp.core.enums.DB;
034    import org.ujmp.core.enums.ValueType;
035    import org.ujmp.core.exceptions.MatrixException;
036    import org.ujmp.core.objectmatrix.ObjectMatrix2D;
037    
038    public class ImportMatrixJDBC {
039    
040            public static ObjectMatrix2D fromDatabase(String url, String sqlStatement,
041                            String username, String password) throws Exception {
042                    if (url.startsWith("jdbc:mysql://")) {
043                            Class.forName("com.mysql.jdbc.Driver");
044                    } else if (url.startsWith("jdbc:postgresql://")) {
045                            Class.forName("org.postgresql.Driver");
046                    } else {
047                            throw new MatrixException("Database format not supported: " + url);
048                    }
049    
050                    Connection connection = DriverManager.getConnection(url, username,
051                                    password);
052                    Statement statement = connection.createStatement();
053                    ResultSet resultSet = statement.executeQuery(sqlStatement);
054                    ResultSetMetaData rsMetaData = resultSet.getMetaData();
055                    long columnCount = rsMetaData.getColumnCount();
056                    resultSet.last();
057                    long rowCount = resultSet.getRow();
058                    resultSet.first();
059                    ObjectMatrix2D m = (ObjectMatrix2D) MatrixFactory.zeros(
060                                    ValueType.OBJECT, rowCount, columnCount);
061    
062                    for (int c = 0; c < columnCount; c++) {
063                            m.setColumnLabel(c, rsMetaData.getColumnLabel(c + 1));
064                    }
065    
066                    for (int r = 0; r < rowCount; r++) {
067                            for (int c = 0; c < columnCount; c++) {
068                                    m.setObject(resultSet.getObject(c + 1), r, c);
069                            }
070                            resultSet.next();
071                    }
072    
073                    resultSet.close();
074                    statement.close();
075                    connection.close();
076                    return m;
077            }
078    
079            public static ObjectMatrix2D fromDatabase(DB type, String host, int port,
080                            String databasename, String sqlStatement, String username,
081                            String password) throws Exception {
082                    switch (type) {
083                    case MySQL:
084                            return fromDatabase("jdbc:mysql://" + host + ":" + port + "/"
085                                            + databasename, sqlStatement, username, password);
086                    default:
087                            throw new MatrixException("not supported: " + type);
088                    }
089            }
090    }