de.alpharogroup.file.csv.CsvToSqlExtensions Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of file-worker Show documentation
Show all versions of file-worker Show documentation
Project that holds utility class for file operations.
/**
* The MIT License
*
* Copyright (C) 2007 Asterios Raptis
*
* Permission is hereby granted, free of charge, to any person obtaining
* a copy of this software and associated documentation files (the
* "Software"), to deal in the Software without restriction, including
* without limitation the rights to use, copy, modify, merge, publish,
* distribute, sublicense, and/or sell copies of the Software, and to
* permit persons to whom the Software is furnished to do so, subject to
* the following conditions:
*
* The above copyright notice and this permission notice shall be
* included in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
* NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
* LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
* OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
* WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
*/
package de.alpharogroup.file.csv;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* The class {@link CsvToSqlExtensions}.
*/
public final class CsvToSqlExtensions
{
/**
* Extract sql columns.
*
* @param headers
* the headers
* @return the string
*/
public static String extractSqlColumns(final String[] headers)
{
final StringBuffer sqlColumns = new StringBuffer();
sqlColumns.append("");
for (int i = 0; i < headers.length; i++)
{
sqlColumns.append(headers[i]);
if (i < headers.length - 1)
{
sqlColumns.append(", ");
}
else
{
sqlColumns.append("");
}
}
return sqlColumns.toString();
}
/**
* Gets the csv file as sql insert script.
*
* @param tableName
* the table name
* @param csvBean
* the csv bean
* @return the csv file as sql insert script
*/
public static String getCsvFileAsSqlInsertScript(final String tableName, final CsvBean csvBean)
{
return getCsvFileAsSqlInsertScript(tableName, csvBean, true, true);
}
/**
* Gets the csv file as sql insert script.
*
* @param tableName
* the table name
* @param csvBean
* the csv bean
* @param withHeader
* the with header
* @param withEndSemicolon
* the with end semicolon
* @return the csv file as sql insert script
*/
public static String getCsvFileAsSqlInsertScript(final String tableName, final CsvBean csvBean,
final boolean withHeader, final boolean withEndSemicolon)
{
final StringBuffer sb = new StringBuffer();
if (withHeader)
{
final String sqlColumns = extractSqlColumns(csvBean.getHeaders());
sb.append("INSERT INTO " + tableName + " ( " + sqlColumns + ") VALUES \n");
}
final String[] columnTypesEdit = csvBean.getColumnTypesEdit();
if (columnTypesEdit != null)
{
final StringBuffer sqlData = getSqlData(csvBean.getHeaders(), csvBean.getColumnTypes(),
columnTypesEdit, csvBean.getLineOrder(), csvBean.getLines(), withEndSemicolon);
sb.append(sqlData.toString());
}
else
{
final StringBuffer sqlData = getSqlData(csvBean.getHeaders(), csvBean.getColumnTypes(),
null, null, csvBean.getLines(), true);
sb.append(sqlData.toString());
}
return sb.toString();
}
/**
* Gets the csv file as sql insert script.
*
* @param tableName
* the table name
* @param headers
* the headers
* @param columnTypes
* the column types
* @param lines
* the lines
* @return the csv file as sql insert script
*/
public static String getCsvFileAsSqlInsertScript(final String tableName, final String[] headers,
final String[] columnTypes, final List lines)
{
return getCsvFileAsSqlInsertScript(tableName, new CsvBean(headers, columnTypes, lines));
}
/**
* Gets the csv file as sql insert script.
*
* @param tableName
* the table name
* @param headers
* the headers
* @param columnTypes
* the column types
* @param columnTypesEdit
* the column types edit
* @param lines
* the lines
* @return the csv file as sql insert script
*/
public static String getCsvFileAsSqlInsertScript(final String tableName, final String[] headers,
final String[] columnTypes, final String[] columnTypesEdit, final List lines)
{
return getCsvFileAsSqlInsertScript(tableName,
new CsvBean(headers, columnTypes, columnTypesEdit, lines));
}
/**
* Gets the data from line.
*
* @param line
* the line
* @param seperator
* the seperator
* @return the data from line
*/
public static String[] getDataFromLine(final String line, final String seperator)
{
final String[] splittedLine = line.split(seperator);
return splittedLine;
}
/**
* Gets the sql data.
*
* @param columns
* the columns
* @param columnTypes
* the column types
* @param columnTypesEdit
* the column types edit
* @param lineOrder
* the line order
* @param lines
* the lines
* @param withEndSemicolon
* the with end semicolon
* @return the sql data
*/
public static StringBuffer getSqlData(final String[] columns, final String[] columnTypes,
final String[] columnTypesEdit, final Map lineOrder,
final List lines, final boolean withEndSemicolon)
{
final StringBuffer sb = new StringBuffer();
int autoincrement = 0;
for (final Iterator iterator = lines.iterator(); iterator.hasNext();)
{
String[] line;
if (lineOrder != null)
{
final String[] trueLine = iterator.next();
final String newLine[] = new String[columnTypes.length];
for (final Integer index : lineOrder.keySet())
{
newLine[lineOrder.get(index)] = trueLine[index];
}
line = newLine;
}
else
{
line = iterator.next();
}
sb.append("(");
for (int i = 0; i < line.length; i++)
{
String lineItem = line[i];
final String columTypeEdit = columnTypesEdit[i];
if (columTypeEdit != null)
{
final String[] editTypeData = columTypeEdit.split(",");
final String editType = editTypeData[0];
if (editType.equals("edit"))
{
lineItem = lineItem.replace(editTypeData[1], editTypeData[2]);
if (2 < editTypeData.length)
{
final Boolean lc = new Boolean(editTypeData[3]);
if (lc)
{
final String tlc = lineItem.toLowerCase();
sb.append("\"" + tlc + "\"");
}
else
{
sb.append("\"" + lineItem + "\"");
}
}
else
{
sb.append("\"" + lineItem + "\"");
}
}
else if (editType.equals("autoincrement"))
{
final int startCount = Integer.parseInt(editTypeData[1]);
if (i == 0 && autoincrement == 0)
{
autoincrement = startCount;
}
sb.append(autoincrement);
autoincrement++;
}
else if (editType.equals("constant"))
{
final String type = editTypeData[1];
if (type.equals("text"))
{
sb.append("\"" + editTypeData[2] + "\"");
}
else
{
sb.append(editTypeData[2]);
}
}
}
else
{
if (lineItem != null)
{
if (columnTypes[i].endsWith("text"))
{
sb.append("\"" + lineItem + "\"");
}
else
{
sb.append(lineItem);
}
}
else
{
sb.append(lineItem);
}
}
if (i < columns.length - 1)
{
sb.append(", ");
}
}
sb.append(")");
if (iterator.hasNext())
{
sb.append(",\n");
}
else
{
if (withEndSemicolon)
{
sb.append(";\n");
}
else
{
sb.append(",\n");
}
}
}
return sb;
}
/**
* Private constructor.
*/
private CsvToSqlExtensions()
{
super();
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy