Java tutorial
/*********************************************************************************************************************** * * Copyright (C) 2010-2013 by the Stratosphere project (http://stratosphere.eu) * * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with * the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the * specific language governing permissions and limitations under the License. * **********************************************************************************************************************/ package eu.stratosphere.api.java.io.jdbc; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import eu.stratosphere.api.common.io.InputFormat; import eu.stratosphere.api.common.io.statistics.BaseStatistics; import eu.stratosphere.api.java.tuple.Tuple; import eu.stratosphere.configuration.Configuration; import eu.stratosphere.core.io.GenericInputSplit; import eu.stratosphere.core.io.InputSplit; import eu.stratosphere.types.NullValue; /** * InputFormat to read data from a database and generate tuples. * The InputFormat has to be configured using the supplied InputFormatBuilder. * * @param <OUT> * @see Tuple * @see DriverManager */ public class JDBCInputFormat<OUT extends Tuple> implements InputFormat<OUT, InputSplit> { private static final long serialVersionUID = 1L; @SuppressWarnings("unused") private static final Log LOG = LogFactory.getLog(JDBCInputFormat.class); private String username; private String password; private String drivername; private String dbURL; private String query; private transient Connection dbConn; private transient Statement statement; private transient ResultSet resultSet; private int[] columnTypes = null; public JDBCInputFormat() { } @Override public void configure(Configuration parameters) { } /** * Connects to the source database and executes the query. * * @param ignored * @throws IOException */ @Override public void open(InputSplit ignored) throws IOException { try { establishConnection(); statement = dbConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); resultSet = statement.executeQuery(query); } catch (SQLException se) { close(); throw new IllegalArgumentException("open() failed." + se.getMessage(), se); } catch (ClassNotFoundException cnfe) { throw new IllegalArgumentException("JDBC-Class not found. - " + cnfe.getMessage(), cnfe); } } private void establishConnection() throws SQLException, ClassNotFoundException { Class.forName(drivername); if (username == null) { dbConn = DriverManager.getConnection(dbURL); } else { dbConn = DriverManager.getConnection(dbURL, username, password); } } /** * Closes all resources used. * * @throws IOException Indicates that a resource could not be closed. */ @Override public void close() throws IOException { try { resultSet.close(); } catch (SQLException se) { LOG.info("Inputformat couldn't be closed - " + se.getMessage()); } catch (NullPointerException npe) { } try { statement.close(); } catch (SQLException se) { LOG.info("Inputformat couldn't be closed - " + se.getMessage()); } catch (NullPointerException npe) { } try { dbConn.close(); } catch (SQLException se) { LOG.info("Inputformat couldn't be closed - " + se.getMessage()); } catch (NullPointerException npe) { } } /** * Checks whether all data has been read. * * @return boolean value indication whether all data has been read. * @throws IOException */ @Override public boolean reachedEnd() throws IOException { try { if (resultSet.isLast()) { close(); return true; } return false; } catch (SQLException se) { throw new IOException("Couldn't evaluate reachedEnd() - " + se.getMessage(), se); } } /** * Stores the next resultSet row in a tuple * * @param tuple * @return tuple containing next row * @throws java.io.IOException */ @Override public OUT nextRecord(OUT tuple) throws IOException { try { resultSet.next(); if (columnTypes == null) { extractTypes(tuple); } addValue(tuple); return tuple; } catch (SQLException se) { close(); throw new IOException("Couldn't read data - " + se.getMessage(), se); } catch (NullPointerException npe) { close(); throw new IOException("Couldn't access resultSet", npe); } } private void extractTypes(OUT tuple) throws SQLException, IOException { ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); columnTypes = new int[resultSetMetaData.getColumnCount()]; if (tuple.getArity() != columnTypes.length) { close(); throw new IOException("Tuple size does not match columncount"); } for (int pos = 0; pos < columnTypes.length; pos++) { columnTypes[pos] = resultSetMetaData.getColumnType(pos + 1); } } /** * Enters data value from the current resultSet into a Record. * * @param pos Tuple position to be set. * @param type SQL type of the resultSet value. * @param reuse Target Record. */ private void addValue(OUT reuse) throws SQLException { for (int pos = 0; pos < columnTypes.length; pos++) { switch (columnTypes[pos]) { case java.sql.Types.NULL: reuse.setField(NullValue.getInstance(), pos); break; case java.sql.Types.BOOLEAN: reuse.setField(resultSet.getBoolean(pos + 1), pos); break; case java.sql.Types.BIT: reuse.setField(resultSet.getBoolean(pos + 1), pos); break; case java.sql.Types.CHAR: reuse.setField(resultSet.getString(pos + 1), pos); break; case java.sql.Types.NCHAR: reuse.setField(resultSet.getString(pos + 1), pos); break; case java.sql.Types.VARCHAR: reuse.setField(resultSet.getString(pos + 1), pos); break; case java.sql.Types.LONGVARCHAR: reuse.setField(resultSet.getString(pos + 1), pos); break; case java.sql.Types.LONGNVARCHAR: reuse.setField(resultSet.getString(pos + 1), pos); break; case java.sql.Types.TINYINT: reuse.setField(resultSet.getShort(pos + 1), pos); break; case java.sql.Types.SMALLINT: reuse.setField(resultSet.getShort(pos + 1), pos); break; case java.sql.Types.BIGINT: reuse.setField(resultSet.getLong(pos + 1), pos); break; case java.sql.Types.INTEGER: reuse.setField(resultSet.getInt(pos + 1), pos); break; case java.sql.Types.FLOAT: reuse.setField(resultSet.getDouble(pos + 1), pos); break; case java.sql.Types.REAL: reuse.setField(resultSet.getFloat(pos + 1), pos); break; case java.sql.Types.DOUBLE: reuse.setField(resultSet.getDouble(pos + 1), pos); break; case java.sql.Types.DECIMAL: reuse.setField(resultSet.getBigDecimal(pos + 1).doubleValue(), pos); break; case java.sql.Types.NUMERIC: reuse.setField(resultSet.getBigDecimal(pos + 1).doubleValue(), pos); break; case java.sql.Types.DATE: reuse.setField(resultSet.getDate(pos + 1).toString(), pos); break; case java.sql.Types.TIME: reuse.setField(resultSet.getTime(pos + 1).getTime(), pos); break; case java.sql.Types.TIMESTAMP: reuse.setField(resultSet.getTimestamp(pos + 1).toString(), pos); break; case java.sql.Types.SQLXML: reuse.setField(resultSet.getSQLXML(pos + 1).toString(), pos); break; default: throw new SQLException("Unsupported sql-type [" + columnTypes[pos] + "] on column [" + pos + "]"); // case java.sql.Types.BINARY: // case java.sql.Types.VARBINARY: // case java.sql.Types.LONGVARBINARY: // case java.sql.Types.ARRAY: // case java.sql.Types.JAVA_OBJECT: // case java.sql.Types.BLOB: // case java.sql.Types.CLOB: // case java.sql.Types.NCLOB: // case java.sql.Types.DATALINK: // case java.sql.Types.DISTINCT: // case java.sql.Types.OTHER: // case java.sql.Types.REF: // case java.sql.Types.ROWID: // case java.sql.Types.STRUCT: } } } @Override public BaseStatistics getStatistics(BaseStatistics cachedStatistics) throws IOException { return cachedStatistics; } @Override public InputSplit[] createInputSplits(int minNumSplits) throws IOException { GenericInputSplit[] split = { new GenericInputSplit(0, 1) }; return split; } @Override public Class<? extends InputSplit> getInputSplitType() { return GenericInputSplit.class; } /** * A builder used to set parameters to the output format's configuration in a fluent way. * @return builder */ public static JDBCInputFormatBuilder buildJDBCInputFormat() { return new JDBCInputFormatBuilder(); } public static class JDBCInputFormatBuilder { private final JDBCInputFormat format; public JDBCInputFormatBuilder() { this.format = new JDBCInputFormat(); } public JDBCInputFormatBuilder setUsername(String username) { format.username = username; return this; } public JDBCInputFormatBuilder setPassword(String password) { format.password = password; return this; } public JDBCInputFormatBuilder setDrivername(String drivername) { format.drivername = drivername; return this; } public JDBCInputFormatBuilder setDBUrl(String dbURL) { format.dbURL = dbURL; return this; } public JDBCInputFormatBuilder setQuery(String query) { format.query = query; return this; } public JDBCInputFormat finish() { if (format.username == null) { LOG.info("Username was not supplied separately."); } if (format.password == null) { LOG.info("Password was not supplied separately."); } if (format.dbURL == null) { throw new IllegalArgumentException("No dababase URL supplied."); } if (format.query == null) { throw new IllegalArgumentException("No query suplied"); } if (format.drivername == null) { throw new IllegalArgumentException("No driver supplied"); } return format; } } }