Java tutorial
/******************************************************************************* * Copyright (c) 2010-2011 VIVO Harvester Team. For full list of contributors, please see the AUTHORS file provided. * All rights reserved. * This program and the accompanying materials are made available under the terms of the new BSD license which accompanies this distribution, and is available at http://www.opensource.org/licenses/bsd-license.html ******************************************************************************/ package org.vivoweb.harvester.util; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; 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 java.util.ArrayList; import java.util.List; import org.apache.commons.vfs.FileSystemException; import org.h2.tools.Csv; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.vivoweb.harvester.util.args.ArgDef; import org.vivoweb.harvester.util.args.ArgList; import org.vivoweb.harvester.util.args.ArgParser; import org.vivoweb.harvester.util.args.UsageException; /** * This Class takes the data from a csv file and places it into a database * @author James Pence jrpence@ufl.edu */ public class CSVtoJDBC { /** * SLF4J Logger */ private static Logger log = LoggerFactory.getLogger(CSVtoJDBC.class); /** * CSV to read from */ private InputStream csvStream; /** * DBconnection into which to output */ private Connection output; /** * Table name into which to output */ private String tableName; /** * Field names into which to output */ private List<String> fieldNames; /** * Library style initialyzer * @param input CSV inputStream to read from * @param output The database connection for the output * @param tableName table name into which to output */ public CSVtoJDBC(InputStream input, Connection output, String tableName) { this.csvStream = input; this.output = output; this.tableName = tableName; this.fieldNames = new ArrayList<String>(); } /** * Library style Constructor * @param filename CSV to read from * @param output The database connection for the output * @param tableName table name into which to output * @throws IOException error establishing connection to file */ public CSVtoJDBC(String filename, Connection output, String tableName) throws IOException { this(FileAide.getInputStream(filename), output, tableName); } /** * Library style Constructor * @param filename CSV to read from * @param jdbcDriverClass jdbc driver class * @param connLine the jdbc connection line * @param username username with which to connect * @param password password with which to connect * @param tableName table name into which to output * @throws IOException error establishing connection to database or file */ public CSVtoJDBC(String filename, String jdbcDriverClass, String connLine, String username, String password, String tableName) throws IOException { this(filename, getConnection(jdbcDriverClass, connLine, username, password), tableName); } /** * Library style Constructor * @param input CSV inputStream to read from * @param jdbcDriverClass jdbc driver class * @param connLine the jdbc connection line * @param username username with which to connect * @param password password with which to connect * @param tableName table name into which to output * @throws IOException error establishing connection to database or file */ public CSVtoJDBC(InputStream input, String jdbcDriverClass, String connLine, String username, String password, String tableName) throws IOException { this(input, getConnection(jdbcDriverClass, connLine, username, password), tableName); } /** * Command line Constructor * @param args command line arguments * @throws IOException error establishing connection to database or file * @throws UsageException user requested usage message */ private CSVtoJDBC(String[] args) throws IOException, UsageException { this(getParser().parse(args)); } /** * ArgList Constructor * @param argList option set of parsed args * @throws IOException error establishing connection to database or file */ private CSVtoJDBC(ArgList argList) throws IOException { this(argList.get("i"), argList.get("d"), argList.get("c"), argList.get("u"), argList.get("p"), argList.get("t")); } /** * Get a connection * @param jdbcDriverClass the driver * @param connLine the connection string * @param username the username * @param password the password * @return the connection * @throws IOException error connecting */ private static Connection getConnection(String jdbcDriverClass, String connLine, String username, String password) throws IOException { try { Class.forName(jdbcDriverClass); return DriverManager.getConnection(connLine, username, password); } catch (ClassNotFoundException e) { throw new IllegalArgumentException(e); } catch (SQLException e) { throw new IOException(e); } } /** * Move CSV data into a recordHandler * @throws IOException error reading from database or file */ public void execute() throws IOException { try { ResultSet rs = Csv.getInstance().read(new InputStreamReader(this.csvStream), null); ResultSetMetaData meta = rs.getMetaData(); Statement cursor = this.output.createStatement(); int rowID = 0; StringBuilder createTable = new StringBuilder("CREATE TABLE "); createTable.append(this.tableName); createTable.append("( ROWID int NOT NULL, "); this.fieldNames.add("ROWID"); StringBuilder columnNames = new StringBuilder("( ROWID, "); for (int i = 0; i < meta.getColumnCount(); i++) { String colLbl = meta.getColumnLabel(i + 1); createTable.append("\n"); createTable.append(colLbl); this.fieldNames.add(colLbl); createTable.append((i == (meta.getColumnCount() - 1)) ? " TEXT )" : " TEXT ,"); columnNames.append(colLbl); columnNames.append((i == (meta.getColumnCount() - 1)) ? " )" : ", "); } log.debug("Create table command: \n" + createTable.toString()); cursor.execute(createTable.toString()); cursor.execute("ALTER TABLE " + this.tableName + " ADD PRIMARY KEY (ROWID)"); while (rs.next()) { StringBuilder insertCommand = new StringBuilder("INSERT INTO "); insertCommand.append(this.tableName); insertCommand.append(" "); insertCommand.append(columnNames.toString()); insertCommand.append("\nVALUES ("); insertCommand.append(rowID); insertCommand.append(", '"); for (int i = 0; i < meta.getColumnCount(); i++) { insertCommand.append(rs.getString(i + 1)); insertCommand.append((i == (meta.getColumnCount() - 1)) ? "')" : "', '"); } log.debug("Insert command: \n" + insertCommand.toString()); cursor.executeUpdate(insertCommand.toString()); rowID++; } } catch (FileSystemException e) { throw new IOException(e); } catch (SQLException e) { throw new IOException(e); } } /** * Returns the list of fields from the recent CSV * @return the list of fields generated from recent CSV */ public List<String> getFields() { return this.fieldNames; } /** * Get the ArgParser for this task * @return the ArgParser */ private static ArgParser getParser() { ArgParser parser = new ArgParser("CSVtoJDBC"); parser.addArgument(new ArgDef().setShortOption('i').setLongOpt("inputFile").withParameter(true, "FILENAME") .setDescription("csv file to be read into the database").setRequired(true)); parser.addArgument(new ArgDef().setShortOption('d').setLongOpt("driver").withParameter(true, "JDBC_DRIVER") .setDescription("jdbc driver class for output database").setRequired(true)); parser.addArgument( new ArgDef().setShortOption('c').setLongOpt("connection").withParameter(true, "JDBC_CONN") .setDescription("jdbc connection string for output database").setRequired(true)); parser.addArgument(new ArgDef().setShortOption('u').setLongOpt("username").withParameter(true, "USERNAME") .setDescription("database username for output database").setRequired(true)); parser.addArgument(new ArgDef().setShortOption('p').setLongOpt("password").withParameter(true, "PASSWORD") .setDescription("database password for output database").setRequired(true)); parser.addArgument( new ArgDef().setShortOption('t').setLongOpt("tableName").withParameter(true, "TABLE_NAME") .setDescription("a single database table name").setRequired(true)); return parser; } /** * Main method * @param args commandline arguments */ public static void main(String... args) { Exception error = null; try { InitLog.initLogger(args, getParser()); log.info(getParser().getAppName() + ": Start"); new CSVtoJDBC(args).execute(); } catch (IllegalArgumentException e) { log.error(e.getMessage()); log.debug("Stacktrace:", e); System.out.println(getParser().getUsage()); error = e; } catch (UsageException e) { log.info("Printing Usage:"); System.out.println(getParser().getUsage()); error = e; } catch (Exception e) { log.error(e.getMessage()); log.debug("Stacktrace:", e); error = e; } finally { log.info(getParser().getAppName() + ": End"); if (error != null) { System.exit(1); } } } }