Java tutorial
/*! ****************************************************************************** * * Pentaho Data Integration * * Copyright (C) 2002-2013 by Pentaho : http://www.pentaho.com * ******************************************************************************* * * 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 org.pentaho.di.trans.steps.accessoutput; import java.io.File; import java.io.IOException; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.apache.commons.vfs.FileObject; import org.pentaho.di.core.CheckResult; import org.pentaho.di.core.CheckResultInterface; import org.pentaho.di.core.Const; import org.pentaho.di.core.database.DatabaseMeta; import org.pentaho.di.core.exception.KettleException; import org.pentaho.di.core.exception.KettleValueException; import org.pentaho.di.core.exception.KettleXMLException; import org.pentaho.di.core.row.RowMeta; import org.pentaho.di.core.row.RowMetaInterface; import org.pentaho.di.core.row.ValueMeta; import org.pentaho.di.core.row.ValueMetaInterface; import org.pentaho.di.core.variables.VariableSpace; import org.pentaho.di.core.vfs.KettleVFS; import org.pentaho.di.core.xml.XMLHandler; import org.pentaho.di.i18n.BaseMessages; import org.pentaho.di.repository.ObjectId; import org.pentaho.di.repository.Repository; import org.pentaho.di.resource.ResourceDefinition; import org.pentaho.di.resource.ResourceNamingInterface; import org.pentaho.di.trans.Trans; import org.pentaho.di.trans.TransMeta; import org.pentaho.di.trans.step.BaseStepMeta; import org.pentaho.di.trans.step.StepDataInterface; import org.pentaho.di.trans.step.StepInterface; import org.pentaho.di.trans.step.StepMeta; import org.pentaho.di.trans.step.StepMetaInterface; import org.pentaho.metastore.api.IMetaStore; import org.w3c.dom.Node; import com.healthmarketscience.jackcess.Column; import com.healthmarketscience.jackcess.DataType; import com.healthmarketscience.jackcess.Database; import com.healthmarketscience.jackcess.Table; /* * Created on 2-jun-2003 * */ public class AccessOutputMeta extends BaseStepMeta implements StepMetaInterface { private static Class<?> PKG = AccessOutputMeta.class; // for i18n purposes, needed by Translator2!! private String filename; private boolean fileCreated; private String tablename; private boolean tableCreated; private boolean tableTruncated; private int commitSize; /** Flag: add the filenames to result filenames */ private boolean addToResultFilenames; /** Flag : Do not open new file when transformation start */ private boolean doNotOpeNnewFileInit; public AccessOutputMeta() { super(); // allocate BaseStepMeta } public void loadXML(Node stepnode, List<DatabaseMeta> databases, IMetaStore metaStore) throws KettleXMLException { readData(stepnode, databases); } public Object clone() { AccessOutputMeta retval = (AccessOutputMeta) super.clone(); return retval; } /** * @return Returns the tablename. */ public String getTablename() { return tablename; } /** * @param tablename * The tablename to set. */ public void setTablename(String tablename) { this.tablename = tablename; } /** * @return Returns the truncate table flag. */ public boolean truncateTable() { return tableTruncated; } /** * @param truncateTable * The truncate table flag to set. */ public void setTableTruncated(boolean truncateTable) { this.tableTruncated = truncateTable; } private void readData(Node stepnode, List<DatabaseMeta> databases) throws KettleXMLException { try { filename = XMLHandler.getTagValue(stepnode, "filename"); tablename = XMLHandler.getTagValue(stepnode, "table"); tableTruncated = "Y".equalsIgnoreCase(XMLHandler.getTagValue(stepnode, "truncate")); fileCreated = "Y".equalsIgnoreCase(XMLHandler.getTagValue(stepnode, "create_file")); tableCreated = "Y".equalsIgnoreCase(XMLHandler.getTagValue(stepnode, "create_table")); commitSize = Const.toInt(XMLHandler.getTagValue(stepnode, "commit_size"), AccessOutput.COMMIT_SIZE); String addToResultFiles = XMLHandler.getTagValue(stepnode, "add_to_result_filenames"); if (Const.isEmpty(addToResultFiles)) { addToResultFilenames = true; } else { addToResultFilenames = "Y".equalsIgnoreCase(addToResultFiles); } doNotOpeNnewFileInit = "Y" .equalsIgnoreCase(XMLHandler.getTagValue(stepnode, "do_not_open_newfile_init")); } catch (Exception e) { throw new KettleXMLException("Unable to load step info from XML", e); } } public void setDefault() { fileCreated = true; tableCreated = true; tableTruncated = false; commitSize = AccessOutput.COMMIT_SIZE; doNotOpeNnewFileInit = false; addToResultFilenames = true; } public String getXML() { StringBuffer retval = new StringBuffer(300); retval.append(" ").append(XMLHandler.addTagValue("filename", filename)); retval.append(" ").append(XMLHandler.addTagValue("table", tablename)); retval.append(" ").append(XMLHandler.addTagValue("truncate", tableTruncated)); retval.append(" ").append(XMLHandler.addTagValue("create_file", fileCreated)); retval.append(" ").append(XMLHandler.addTagValue("create_table", tableCreated)); retval.append(" ").append(XMLHandler.addTagValue("commit_size", commitSize)); retval.append(" ").append(XMLHandler.addTagValue("add_to_result_filenames", addToResultFilenames)); retval.append(" ").append(XMLHandler.addTagValue("do_not_open_newfile_init", doNotOpeNnewFileInit)); return retval.toString(); } public void readRep(Repository rep, IMetaStore metaStore, ObjectId id_step, List<DatabaseMeta> databases) throws KettleException { try { filename = rep.getStepAttributeString(id_step, "filename"); tablename = rep.getStepAttributeString(id_step, "table"); tableTruncated = rep.getStepAttributeBoolean(id_step, "truncate"); fileCreated = rep.getStepAttributeBoolean(id_step, "create_file"); tableCreated = rep.getStepAttributeBoolean(id_step, "create_table"); commitSize = (int) rep.getStepAttributeInteger(id_step, "commit_size"); String addToResultFiles = rep.getStepAttributeString(id_step, "add_to_result_filenames"); if (Const.isEmpty(addToResultFiles)) { addToResultFilenames = true; } else { addToResultFilenames = rep.getStepAttributeBoolean(id_step, "add_to_result_filenames"); } doNotOpeNnewFileInit = rep.getStepAttributeBoolean(id_step, "do_not_open_newfile_init"); } catch (Exception e) { throw new KettleException("Unexpected error reading step information from the repository", e); } } public void saveRep(Repository rep, IMetaStore metaStore, ObjectId id_transformation, ObjectId id_step) throws KettleException { try { rep.saveStepAttribute(id_transformation, id_step, "filename", filename); rep.saveStepAttribute(id_transformation, id_step, "table", tablename); rep.saveStepAttribute(id_transformation, id_step, "truncate", tableTruncated); rep.saveStepAttribute(id_transformation, id_step, "create_file", fileCreated); rep.saveStepAttribute(id_transformation, id_step, "create_table", tableCreated); rep.saveStepAttribute(id_transformation, id_step, "commit_size", commitSize); rep.saveStepAttribute(id_transformation, id_step, "add_to_result_filenames", addToResultFilenames); rep.saveStepAttribute(id_transformation, id_step, "do_not_open_newfile_init", doNotOpeNnewFileInit); } catch (Exception e) { throw new KettleException("Unable to save step information to the repository for id_step=" + id_step, e); } } public void check(List<CheckResultInterface> remarks, TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev, String[] input, String[] output, RowMetaInterface info, VariableSpace space, Repository repository, IMetaStore metaStore) { // TODO: add file checking in case we don't create a table. // See if we have input streams leading to this step! if (input.length > 0) { CheckResult cr = new CheckResult(CheckResult.TYPE_RESULT_OK, BaseMessages.getString(PKG, "AccessOutputMeta.CheckResult.ExpectedInputOk"), stepMeta); remarks.add(cr); } else { CheckResult cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, BaseMessages.getString(PKG, "AccessOutputMeta.CheckResult.ExpectedInputError"), stepMeta); remarks.add(cr); } } public StepInterface getStep(StepMeta stepMeta, StepDataInterface stepDataInterface, int cnr, TransMeta transMeta, Trans trans) { return new AccessOutput(stepMeta, stepDataInterface, cnr, transMeta, trans); } public StepDataInterface getStepData() { return new AccessOutputData(); } public RowMetaInterface getRequiredFields(VariableSpace space) throws KettleException { String realFilename = space.environmentSubstitute(filename); File file = new File(realFilename); Database db = null; try { if (!file.exists() || !file.isFile()) { throw new KettleException( BaseMessages.getString(PKG, "AccessOutputMeta.Exception.FileDoesNotExist", realFilename)); } // open the database and get the table db = Database.open(file); String realTablename = space.environmentSubstitute(tablename); Table table = db.getTable(realTablename); if (table == null) { throw new KettleException( BaseMessages.getString(PKG, "AccessOutputMeta.Exception.TableDoesNotExist", realTablename)); } RowMetaInterface layout = getLayout(table); return layout; } catch (Exception e) { throw new KettleException(BaseMessages.getString(PKG, "AccessOutputMeta.Exception.ErrorGettingFields"), e); } finally { try { if (db != null) { db.close(); } } catch (IOException e) { throw new KettleException( BaseMessages.getString(PKG, "AccessOutputMeta.Exception.ErrorClosingDatabase"), e); } } } public static final RowMetaInterface getLayout(Table table) throws SQLException { RowMetaInterface row = new RowMeta(); List<Column> columns = table.getColumns(); for (int i = 0; i < columns.size(); i++) { Column column = columns.get(i); int valtype = ValueMetaInterface.TYPE_STRING; int length = -1; int precision = -1; int type = column.getType().getSQLType(); switch (type) { case java.sql.Types.CHAR: case java.sql.Types.VARCHAR: case java.sql.Types.LONGVARCHAR: // Character Large Object valtype = ValueMetaInterface.TYPE_STRING; length = column.getLength(); break; case java.sql.Types.CLOB: valtype = ValueMetaInterface.TYPE_STRING; length = DatabaseMeta.CLOB_LENGTH; break; case java.sql.Types.BIGINT: valtype = ValueMetaInterface.TYPE_INTEGER; precision = 0; // Max 9.223.372.036.854.775.807 length = 15; break; case java.sql.Types.INTEGER: valtype = ValueMetaInterface.TYPE_INTEGER; precision = 0; // Max 2.147.483.647 length = 9; break; case java.sql.Types.SMALLINT: valtype = ValueMetaInterface.TYPE_INTEGER; precision = 0; // Max 32.767 length = 4; break; case java.sql.Types.TINYINT: valtype = ValueMetaInterface.TYPE_INTEGER; precision = 0; // Max 127 length = 2; break; case java.sql.Types.DECIMAL: case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: case java.sql.Types.REAL: case java.sql.Types.NUMERIC: valtype = ValueMetaInterface.TYPE_NUMBER; length = column.getLength(); precision = column.getPrecision(); if (length >= 126) { length = -1; } if (precision >= 126) { precision = -1; } if (type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT || type == java.sql.Types.REAL) { if (precision == 0) { precision = -1; // precision is obviously incorrect if the type if Double/Float/Real } } else { if (precision == 0 && length < 18 && length > 0) // Among others Oracle is affected here. { valtype = ValueMetaInterface.TYPE_INTEGER; } } if (length > 18 || precision > 18) { valtype = ValueMetaInterface.TYPE_BIGNUMBER; } break; case java.sql.Types.DATE: case java.sql.Types.TIME: case java.sql.Types.TIMESTAMP: valtype = ValueMetaInterface.TYPE_DATE; break; case java.sql.Types.BOOLEAN: case java.sql.Types.BIT: valtype = ValueMetaInterface.TYPE_BOOLEAN; break; case java.sql.Types.BINARY: case java.sql.Types.BLOB: case java.sql.Types.VARBINARY: case java.sql.Types.LONGVARBINARY: valtype = ValueMetaInterface.TYPE_BINARY; break; default: valtype = ValueMetaInterface.TYPE_STRING; length = column.getLength(); break; } ValueMetaInterface v = new ValueMeta(column.getName(), valtype); v.setLength(length, precision); row.addValueMeta(v); } return row; } public static final List<Column> getColumns(RowMetaInterface row) { List<Column> list = new ArrayList<Column>(); for (int i = 0; i < row.size(); i++) { ValueMetaInterface value = row.getValueMeta(i); Column column = new Column(); column.setName(value.getName()); int length = value.getLength(); switch (value.getType()) { case ValueMetaInterface.TYPE_INTEGER: if (length < 3) { column.setType(DataType.BYTE); length = DataType.BYTE.getFixedSize(); } else { if (length < 5) { column.setType(DataType.INT); length = DataType.INT.getFixedSize(); } else { column.setType(DataType.LONG); length = DataType.LONG.getFixedSize(); } } break; case ValueMetaInterface.TYPE_NUMBER: column.setType(DataType.DOUBLE); length = DataType.DOUBLE.getFixedSize(); break; case ValueMetaInterface.TYPE_DATE: column.setType(DataType.SHORT_DATE_TIME); length = DataType.SHORT_DATE_TIME.getFixedSize(); break; case ValueMetaInterface.TYPE_STRING: if (length < 255) { column.setType(DataType.TEXT); length *= DataType.TEXT.getUnitSize(); } else { column.setType(DataType.MEMO); length *= DataType.MEMO.getUnitSize(); } break; case ValueMetaInterface.TYPE_BINARY: column.setType(DataType.BINARY); break; case ValueMetaInterface.TYPE_BOOLEAN: column.setType(DataType.BOOLEAN); length = DataType.BOOLEAN.getFixedSize(); break; case ValueMetaInterface.TYPE_BIGNUMBER: column.setType(DataType.NUMERIC); length = DataType.NUMERIC.getFixedSize(); break; default: break; } if (length >= 0) { column.setLength((short) length); } if (value.getPrecision() >= 1 && value.getPrecision() <= 28) { column.setPrecision((byte) value.getPrecision()); } list.add(column); } return list; } public static Object[] createObjectsForRow(RowMetaInterface rowMeta, Object[] rowData) throws KettleValueException { Object[] values = new Object[rowMeta.size()]; for (int i = 0; i < rowMeta.size(); i++) { ValueMetaInterface valueMeta = rowMeta.getValueMeta(i); Object valueData = rowData[i]; // Prevent a NullPointerException below if (valueData == null || valueMeta == null) { values[i] = null; continue; } int length = valueMeta.getLength(); switch (valueMeta.getType()) { case ValueMetaInterface.TYPE_INTEGER: if (length < 3) { values[i] = new Byte(valueMeta.getInteger(valueData).byteValue()); } else { if (length < 5) { values[i] = new Short(valueMeta.getInteger(valueData).shortValue()); } else { values[i] = valueMeta.getInteger(valueData); } } break; case ValueMetaInterface.TYPE_NUMBER: values[i] = valueMeta.getNumber(valueData); break; case ValueMetaInterface.TYPE_DATE: values[i] = valueMeta.getDate(valueData); break; case ValueMetaInterface.TYPE_STRING: values[i] = valueMeta.getString(valueData); break; case ValueMetaInterface.TYPE_BINARY: values[i] = valueMeta.getBinary(valueData); break; case ValueMetaInterface.TYPE_BOOLEAN: values[i] = valueMeta.getBoolean(valueData); break; case ValueMetaInterface.TYPE_BIGNUMBER: values[i] = valueMeta.getNumber(valueData); break; default: break; } } return values; } /** * @return the fileCreated */ public boolean isFileCreated() { return fileCreated; } /** * @param fileCreated * the fileCreated to set */ public void setFileCreated(boolean fileCreated) { this.fileCreated = fileCreated; } /** * @return the filename */ public String getFilename() { return filename; } /** * @param filename * the filename to set */ public void setFilename(String filename) { this.filename = filename; } /** * @return the tableCreated */ public boolean isTableCreated() { return tableCreated; } /** * @param tableCreated * the tableCreated to set */ public void setTableCreated(boolean tableCreated) { this.tableCreated = tableCreated; } /** * @return the tableTruncated */ public boolean isTableTruncated() { return tableTruncated; } /** * @return the commitSize */ public int getCommitSize() { return commitSize; } /** * @param commitSize * the commitSize to set */ public void setCommitSize(int commitSize) { this.commitSize = commitSize; } /** * @return Returns the add to result filesname. */ public boolean isAddToResultFiles() { return addToResultFilenames; } /** * @param addtoresultfilenamesin * The addtoresultfilenames to set. */ public void setAddToResultFiles(boolean addtoresultfilenamesin) { this.addToResultFilenames = addtoresultfilenamesin; } /** * @return Returns the "do not open new file init" flag */ public boolean isDoNotOpenNewFileInit() { return doNotOpeNnewFileInit; } /** * @param doNotOpenNewFileInit * The "do not open new file init" flag to set. */ public void setDoNotOpenNewFileInit(boolean doNotOpenNewFileInit) { this.doNotOpeNnewFileInit = doNotOpenNewFileInit; } public String[] getUsedLibraries() { return new String[] { "jackcess-1.1.13.jar", "commons-collections-3.1.jar", "commons-logging.jar", "commons-lang-2.2.jar", "commons-dbcp-1.2.1.jar", "commons-pool-1.3.jar", }; } /** * @param space * the variable space to use * @param definitions * @param resourceNamingInterface * @param repository * The repository to optionally load other resources from (to be converted to XML) * @param metaStore * the metaStore in which non-kettle metadata could reside. * * @return the filename of the exported resource */ public String exportResources(VariableSpace space, Map<String, ResourceDefinition> definitions, ResourceNamingInterface resourceNamingInterface, Repository repository, IMetaStore metaStore) throws KettleException { try { // The object that we're modifying here is a copy of the original! // So let's change the filename from relative to absolute by grabbing the file object... // if (!Const.isEmpty(filename)) { FileObject fileObject = KettleVFS.getFileObject(space.environmentSubstitute(filename), space); filename = resourceNamingInterface.nameResource(fileObject, space, true); } return null; } catch (Exception e) { throw new KettleException(e); } } }