public void insert() throws Exception { Connection connection = null; try { connection = getConnection(); List<String[]> records = TableDataReader.readRecords(new ByteArrayInputStream(content)); insertRecords(connection, records, tableName); } finally { closeConnection(connection); } }
private boolean exists(List<TableColumn> availableTableColumns, TableColumn tableColumn) { if (getTableName() == null) { return false; } for (TableColumn tableColumn2 : availableTableColumns) { TableColumn tableColumnX = tableColumn2; if (tableColumnX.getName().equals(tableColumn.getName())) { tableColumnX.setType(tableColumn.getType()); return true; } } return false; }
public String getTableData() { String data = ""; if (getTableName() == null) { data = COULD_NOT_RETRIEVE_TABLE_DATA; logger.error(COULD_NOT_RETRIEVE_TABLE_DATA); return data; } try { Connection connection = null; try { connection = dataSource.getConnection(); List<TableColumn> availableTableColumns = TableMetadataHelper.getColumns(connection, getTableName()); setTableColumns(availableTableColumns.toArray(new TableColumn[] {})); data = getDataForTable(); } finally { if (connection != null) { connection.close(); } } } catch (Exception e) { logger.error(ERROR_ON_LOADING_TABLE_COLUMNS_FROM_DATABASE_FOR_TABLE + getTableName(), e); } return data; }
try { connection = dataSource.getConnection(); List<String[]> records = TableDataReader.readRecords(new ByteArrayInputStream(fileContent)); for (String[] record : records) { if (record.length > 0) { TableImporter tableDataInserter = new TableImporter(dataSource, buff.toString().getBytes(), tableName); tableDataInserter.insert();
int columnsCount = records.get(0).length; PreparedStatement insertStatement = connection .prepareStatement(INSERT_INTO + tableName + VALUES + generateQM(columnsCount) + CLOSE); List<TableColumn> availableTableColumns = TableMetadataHelper.getColumns(connection, tableName); logger.debug(" {}: {}", tableColumn.getName(), tableColumn.getType()); switch (tableColumn.getType()) { case Types.INTEGER: insertStatement.setInt(i + 1, Integer.parseInt(record[i]));
ResultSet resultSet = null; StringBuilder sb = new StringBuilder(); String tableName = getTableName(); TableColumn[] columns = getTableColumns(); Connection connection = null; sb.append(resultSet.getString(column.getName())); sb.append(DATA_DELIMETER);
/** * Process the data rows in the 'replace' mode * * @param model * the model * @throws Exception * in case of database error */ public void executeReplaceUpdate(DataStructureDataReplaceModel model) throws Exception { logger.info("Processing rows in mode 'replace': " + model.getLocation()); String tableName = model.getName(); deleteAllDataFromTable(tableName); byte[] content = model.getContent().getBytes(); if (content.length != 0) { TableImporter tableDataInserter = new TableImporter(dataSource, content, tableName); tableDataInserter.insert(); moveSequence(tableName); // move the sequence just in case } }
public static List<String[]> readRecords(InputStream csvFile) throws FileNotFoundException, IOException, InvalidNumberOfElementsException { BufferedReader reader = new BufferedReader(new InputStreamReader(csvFile, StandardCharsets.UTF_8)); List<String[]> data = new ArrayList<String[]>(); int item_count = -1; int line_number = 0; while (true) { String line = reader.readLine(); line_number++; if (line == null) { break; } String[] items = getStringItems(line); if (item_count == -1) { item_count = items.length; } else if (item_count != items.length) { throw new InvalidNumberOfElementsException( String.format(INVALID_NUMBER_D_OF_ELEMENTS_AT_LINE_D_INITIAL_COLUMNS_NUMBER_D, items.length, line_number, item_count)); } data.add(items); } reader.close(); return data; }
private void deleteRowsDataFromTable(String tableName, String primaryKey, byte[] fileContent) throws Exception { Connection connection = null; try { connection = dataSource.getConnection(); List<String[]> records = TableDataReader.readRecords(new ByteArrayInputStream(fileContent)); for (String[] record : records) { if (record.length > 0) { String sql = SqlFactory.getNative(connection).delete().from(tableName).where(primaryKey + " = ?").build(); PreparedStatement deleteStatement = connection.prepareStatement(sql); deleteStatement.setObject(1, record[0]); deleteStatement.execute(); } else { logger.error(String.format("Skipping deletion of an empty data row for table: %s", tableName)); } } } finally { if (connection != null) { connection.close(); } } }
private static List<TableColumn> populateColumns(ResultSet columns) throws SQLException { List<TableColumn> availableTableColumns = new ArrayList<TableColumn>(); while (columns.next()) { // columns String columnName = columns.getString(COLUMN_NAME); int columnType = columns.getInt(DATA_TYPE); TableColumn tableColumn = new TableColumn(columnName, columnType, false, true); availableTableColumns.add(tableColumn); } return availableTableColumns; }
/** * Returns the columns result set * * @param connection the connection * @param name the table name * @return the result set with the columns metadata * @throws SQLException in case of an error */ public static List<TableColumn> getColumns(Connection connection, String name) throws SQLException { DatabaseMetaData meta = connection.getMetaData(); if (name == null) { throw new SQLException("Error on getting columns of table: null"); } ResultSet columns = meta.getColumns(null, null, name, null); if (columns.next()) { return populateColumns(meta.getColumns(null, null, name, null)); } columns = meta.getColumns(null, null, name.toLowerCase(), null); if (columns.next()) { return populateColumns(meta.getColumns(null, null, name.toLowerCase(), null)); } columns = meta.getColumns(null, null, name.toUpperCase(), null); return populateColumns(columns); }
private String getPrimaryKey(String tableName) throws Exception { String result = null; Connection connection = null; try { connection = this.dataSource.getConnection(); ResultSet primaryKeys = TableMetadataHelper.getPrimaryKeys(connection, tableName); List<String> primaryKeysList = new ArrayList<String>(); while (primaryKeys.next()) { String columnName = primaryKeys.getString(COLUMN_NAME); primaryKeysList.add(columnName); } if (primaryKeysList.size() == 0) { throw new Exception(String.format("Trying to manipulate data records for a table without a primary key: %s", tableName)); } if (primaryKeysList.size() > 1) { throw new Exception( String.format("Trying to manipulate data records for a table with more than one columns in the primary key: %s", tableName)); } result = primaryKeysList.get(0); } finally { if (connection != null) { connection.close(); } } return result; }
/** * Process the data rows in the 'append' mode * * @param model * the model * @throws Exception * in case of database error */ public void executeAppendUpdate(DataStructureDataAppendModel model) throws Exception { logger.info("Processing rows in mode 'append': " + model.getLocation()); String tableName = model.getName(); int tableRowsCount = getTableRowsCount(tableName); if (tableRowsCount == 0) { byte[] content = model.getContent().getBytes(); if (content.length != 0) { TableImporter tableDataInserter = new TableImporter(dataSource, content, tableName); tableDataInserter.insert(); moveSequence(tableName); // move the sequence, to be able to add more records after the initial import } } }