diff options
Diffstat (limited to 'src/connectors/src/data/io/sql')
-rw-r--r-- | src/connectors/src/data/io/sql/SQLConnectionWrapper.java | 136 | ||||
-rw-r--r-- | src/connectors/src/data/io/sql/SQLRelDataReader.java | 173 |
2 files changed, 309 insertions, 0 deletions
diff --git a/src/connectors/src/data/io/sql/SQLConnectionWrapper.java b/src/connectors/src/data/io/sql/SQLConnectionWrapper.java new file mode 100644 index 0000000..2bab2c8 --- /dev/null +++ b/src/connectors/src/data/io/sql/SQLConnectionWrapper.java @@ -0,0 +1,136 @@ +/* + * SSSync, a Simple and Stupid Synchronizer for data with multi-valued attributes + * Copyright (C) 2014 Ludovic Pouzenc <ludovic@pouzenc.fr> + * + * This file is part of SSSync. + * + * SSSync is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * SSSync is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with SSSync. If not, see <http://www.gnu.org/licenses/> + */ + +package data.io.sql; + +import java.io.Closeable; +import java.io.File; +import java.io.IOException; +import java.sql.Connection; +import java.sql.Driver; +import java.sql.DriverManager; +import java.sql.SQLException; + +import data.io.MVDataReader; + +/** + * TODO javadoc + * + * @author lpouzenc + */ +public class SQLConnectionWrapper implements Closeable { + + /** + * Enumeration of supported DBMS. Each use a particular JDBC driver. + */ + public enum DBMSType { oracle, mysql/*, derby*/ } + + private final Connection conn; + + /** + * TODO javadoc + * @param dbms + * @param host + * @param port + * @param ress + * @param user + * @param pass + * @param db + */ + public SQLConnectionWrapper(DBMSType dbms, String host, int port, String ress, String user, String pass, String db) { + + String driverClassName=null; + String url; + + switch ( dbms ) { + case oracle: + driverClassName="oracle.jdbc.driver.OracleDriver"; + url="jdbc:oracle:thin:@" + host + ":" + port + ":" + ress + "/" + db; + break; + case mysql: + driverClassName="com.mysql.jdbc.Driver"; + url="jdbc:mysql://" + host + ":" + port + "/" + db; + break; + /* Could be useful with JUnit tests + case derby: + driverClassName="org.apache.derby.jdbc.EmbeddedDriver"; + url="jdbc:derby:" + db; + break; + */ + default: + throw new IllegalArgumentException("Unsupported DBMSType : " + dbms); + } + + try { + @SuppressWarnings("unchecked") + Class<? extends Driver> clazz = (Class<? extends Driver>) Class.forName(driverClassName); + DriverManager.registerDriver(clazz.newInstance()); + } catch (Exception e) { + throw new RuntimeException("Can't load or register JDBC driver for " + dbms + " (" + driverClassName + ")", e); + } + + try { + conn = DriverManager.getConnection(url, user, pass); + } catch (SQLException e) { + throw new RuntimeException("Can't establish database connection (" + url + ")"); + } + } + + /** + * Builds a new reader against current connection and a File containing a SELECT statement. + * @param name + * @param queryFile + * @return + * @throws IOException + */ + public MVDataReader newReader(String name, File queryFile) throws IOException { + return new SQLRelDataReader(name, conn, queryFile); + } + + /** + * Builds a new reader against current connection and a String containing a SELECT statement. + * @param name + * @param query + * @return + * @throws IOException + */ + public MVDataReader newReader(String name, String query) { + return new SQLRelDataReader(name, conn, query); + } + + /** + * Close the current database connection. + */ + @Override + public void close() throws IOException { + try { + conn.close(); + } catch (SQLException e) { + throw new IOException("Exception occured while trying to close the SQL connection", e); + } + } + + /** + * @return the current database connection (useful for JUnit tests) + */ + public Connection getConn() { + return conn; + } +} diff --git a/src/connectors/src/data/io/sql/SQLRelDataReader.java b/src/connectors/src/data/io/sql/SQLRelDataReader.java new file mode 100644 index 0000000..b6355e9 --- /dev/null +++ b/src/connectors/src/data/io/sql/SQLRelDataReader.java @@ -0,0 +1,173 @@ +/* + * SSSync, a Simple and Stupid Synchronizer for data with multi-valued attributes + * Copyright (C) 2014 Ludovic Pouzenc <ludovic@pouzenc.fr> + * + * This file is part of SSSync. + * + * SSSync is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * SSSync is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with SSSync. If not, see <http://www.gnu.org/licenses/> + */ + +package data.io.sql; + +import java.io.File; +import java.io.FileReader; +import java.io.IOException; +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.Iterator; + +import data.MVDataEntry; +import data.io.AbstractMVDataReader; + +/** + * Stream-oriented reader from a particular RDBMS source. + * + * @author lpouzenc + */ +public class SQLRelDataReader extends AbstractMVDataReader { + + private final Connection conn; + private final String request; + + private transient String columnNames[]; + private transient ResultSet rs; + private transient boolean didNext; + private transient boolean hasNext; + + /** + * Build a new reader from an existing connection and a File containing a SELECT statement. + * @param dataSourceName A short string representing this reader (for logging) + * @param conn A pre-established SQL data connection + * @param queryFile An SQL file containing an SQL SELECT statement + * @throws IOException + */ + public SQLRelDataReader(String dataSourceName, Connection conn, File queryFile) throws IOException { + this.dataSourceName = dataSourceName; + this.conn = conn; + this.request = readEntireFile(queryFile); + } + + /** + * Build a new reader from an existing connection and a String containing a SELECT statement. + * @param dataSourceName A short string representing this reader (for logging) + * @param conn A pre-established SQL data connection + * @param query A String containing an SQL SELECT statement + * @throws IOException + */ + public SQLRelDataReader(String dataSourceName, Connection conn, String query) { + this.dataSourceName = dataSourceName; + this.conn = conn; + this.request = query; + } + + /** + * {@inheritDoc} + * Note : multiple iterators on the same instance are not supported + */ + @Override + public Iterator<MVDataEntry> iterator() { + try { + // Reset iterator-related attributes + hasNext = false; + didNext = false; + + // Close and free any previous request result + if ( rs != null ) { + rs.close(); + } + // (Re-)Execute the SQL request + Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); + rs = stmt.executeQuery(request); + + // Get the column names + ResultSetMetaData rsmd = rs.getMetaData(); + columnNames = new String[rsmd.getColumnCount()]; + for (int i = 0; i < columnNames.length ; i++) { + // Java SQL : all indices starts at 1 (it sucks !) + columnNames[i] = rsmd.getColumnName(i+1); + } + } catch (SQLException e) { + throw new RuntimeException("Could not execute query : " + e.getMessage() + "\n" + request ); + } + + return this; + } + + /** + * {@inheritDoc} + */ + @Override + public boolean hasNext() { + // java.sql.ResultSet don't implement Iterable interface at all + // It's next() don't return anything except hasNext() result but it moves the cursor ! + if (!didNext) { + try { + hasNext = rs.next(); + } catch (SQLException e) { + throw new RuntimeException(e); + } + didNext = true; + } + return hasNext; + } + + /** + * {@inheritDoc} + */ + @Override + public MVDataEntry next() { + MVDataEntry result = null; + try { + if (!didNext) { + rs.next(); + } + didNext = false; + //TODO Instead of always use the first col, user could choose a specific columnName like in LDAP + String key = rs.getString(1); + result = new MVDataEntry(key); + for (int i = 0; i < columnNames.length ; i++) { + // Java SQL : all indices starts at 1 (it sucks !) + result.splitAndPut(columnNames[i], rs.getString(i+1), ";"); // TODO regex should be an option + } + + } catch (SQLException e) { + throw new RuntimeException("Exception while reading next line in SQL resultset", e); + } + + return result; + } + + /** + * Helper function to load and entire file as a String. + * @param file + * @return + * @throws IOException + */ + private static String readEntireFile(File file) throws IOException { + FileReader input = new FileReader(file); + StringBuilder contents = new StringBuilder(); + char[] buffer = new char[4096]; + int read = 0; + do { + contents.append(buffer, 0, read); + read = input.read(buffer); + } while (read >= 0); + input.close(); + + return contents.toString(); + } +} |