Examples with DmlStatement org.jumpmind.db.sql.DmlStatement used on opensource projects

Example 11 with DmlStatement

use of org.jumpmind.db.sql.DmlStatement in project symmetric-ds by JumpMind.

the class DbExportImportTest method createAndFillTimestampWithTimeZoneTable.

protected boolean createAndFillTimestampWithTimeZoneTable() {
    ISymmetricEngine engine = getSymmetricEngine();
    IDatabasePlatform platform = engine.getDatabasePlatform();
    String dbName = platform.getName();
    if (dbName.equals(DatabaseNamesConstants.ORACLE) || dbName.equals(DatabaseNamesConstants.POSTGRESQL)) {
        ISqlTemplate template = engine.getSqlTemplate();
        try {
            template.update(String.format("drop table \"%s\"", TEST_TS_W_TZ));
        } catch (Exception ex) {
        }
        String createSql = String.format("create table \"%s\" (\"id\" integer, \"tz\" timestamp with time zone, primary key (\"id\"))", TEST_TS_W_TZ);
        template.update(createSql);
        DmlStatement statement = platform.createDmlStatement(DmlType.INSERT, platform.getTableFromCache(TEST_TS_W_TZ, true), null);
        template.update(statement.getSql(), statement.getValueArray(new Object[] { 1, "1973-06-08 07:00:00.000 -04:00" }, new Object[] { 1 }));
        return true;
    } else {
        return false;
    }
}

Example 12 with DmlStatement

use of org.jumpmind.db.sql.DmlStatement in project symmetric-ds by JumpMind.

the class JdbcPersistenceManager method find.

@Override
public <T> List<T> find(Class<T> clazz, Map<String, Object> conditions, String catalogName, String schemaName, String tableName) {
    if (conditions == null || conditions.size() == 0) {
        return find(clazz, catalogName, schemaName, tableName);
    } else {
        try {
            Table table = findTable(catalogName, schemaName, tableName);
            T object = clazz.newInstance();
            LinkedHashMap<String, Column> objectToTableMapping = mapObjectToTable(object, table);
            LinkedHashMap<String, Object> objectValuesByColumnName = new LinkedHashMap<String, Object>();
            Column[] keys = new Column[conditions.size()];
            Set<String> keyPropertyNames = conditions.keySet();
            boolean[] nullKeyValues = new boolean[conditions.size()];
            int index = 0;
            for (String propertyName : keyPropertyNames) {
                Column column = objectToTableMapping.get(propertyName);
                if (column != null) {
                    keys[index] = column;
                    nullKeyValues[index] = conditions.get(propertyName) == null;
                    objectValuesByColumnName.put(column.getName(), conditions.get(propertyName));
                    index++;
                } else {
                    throw new IllegalStateException("Could not find a database column that maps to the " + propertyName + " property on " + clazz.getName() + ".  Make sure the property is defined on the class and " + "the matching column is defined in the database table");
                }
            }
            Column[] columns = objectToTableMapping.values().toArray(new Column[objectToTableMapping.size()]);
            DmlStatement statement = databasePlatform.createDmlStatement(DmlType.SELECT, table.getCatalog(), table.getSchema(), table.getName(), keys, columns, nullKeyValues, null);
            String sql = statement.getSql();
            Object[] values = statement.getValueArray(objectValuesByColumnName);
            int[] types = statement.getTypes();
            List<Row> rows = databasePlatform.getSqlTemplate().query(sql, values, types);
            List<T> objects = new ArrayList<T>();
            for (Row row : rows) {
                object = clazz.newInstance();
                Set<String> propertyNames = objectToTableMapping.keySet();
                for (String propertyName : propertyNames) {
                    Object value = row.get(objectToTableMapping.get(propertyName).getName());
                    BeanUtils.copyProperty(object, propertyName, value);
                }
                objects.add(object);
            }
            return objects;
        } catch (Exception e) {
            throw toRuntimeException(e);
        }
    }
}

Example 13 with DmlStatement

use of org.jumpmind.db.sql.DmlStatement in project symmetric-ds by JumpMind.

the class DefaultDatabaseWriterConflictResolver method isTimestampNewer.

protected boolean isTimestampNewer(Conflict conflict, AbstractDatabaseWriter writer, CsvData data) {
    DefaultDatabaseWriter databaseWriter = (DefaultDatabaseWriter) writer;
    IDatabasePlatform platform = databaseWriter.getPlatform();
    String columnName = conflict.getDetectExpression();
    Table targetTable = writer.getTargetTable();
    Table sourceTable = writer.getSourceTable();
    String[] pkData = data.getPkData(targetTable);
    Object[] objectValues = databaseWriter.getPlatform().getObjectValues(writer.getBatch().getBinaryEncoding(), pkData, targetTable.getPrimaryKeyColumns());
    DmlStatement stmt = databaseWriter.getPlatform().createDmlStatement(DmlType.FROM, targetTable, writer.getWriterSettings().getTextColumnExpression());
    Column column = targetTable.getColumnWithName(columnName);
    if (column == null) {
        throw new RuntimeException(String.format("Could not find a timestamp column with a name of %s on the table %s.  " + "Please check your conflict resolution configuration", columnName, targetTable.getQualifiedTableName()));
    }
    String sql = stmt.getColumnsSql(new Column[] { column });
    Map<String, String> newData = data.toColumnNameValuePairs(sourceTable.getColumnNames(), CsvData.ROW_DATA);
    String loadingStr = newData.get(columnName);
    Date loadingTs = null;
    Date existingTs = null;
    if (column.isTimestampWithTimezone()) {
        // Get the existingTs with timezone
        String existingStr = databaseWriter.getTransaction().queryForObject(sql, String.class, objectValues);
        // because the row doesn't exist, then existing simply needs to be null
        if (existingStr != null) {
            int split = existingStr.lastIndexOf(" ");
            existingTs = FormatUtils.parseDate(existingStr.substring(0, split).trim(), FormatUtils.TIMESTAMP_PATTERNS, TimeZone.getTimeZone(existingStr.substring(split).trim()));
        }
        // Get the loadingTs with timezone
        int split = loadingStr.lastIndexOf(" ");
        loadingTs = FormatUtils.parseDate(loadingStr.substring(0, split).trim(), FormatUtils.TIMESTAMP_PATTERNS, TimeZone.getTimeZone(loadingStr.substring(split).trim()));
    } else {
        // Get the existingTs
        existingTs = databaseWriter.getTransaction().queryForObject(sql, Timestamp.class, objectValues);
        // Get the loadingTs
        Object[] values = platform.getObjectValues(writer.getBatch().getBinaryEncoding(), new String[] { loadingStr }, new Column[] { column });
        if (values[0] instanceof Date) {
            loadingTs = (Date) values[0];
        } else if (values[0] instanceof String && column.getJdbcTypeName().equalsIgnoreCase(TypeMap.DATETIME2)) {
            // SQL Server DateTime2 type is treated as a string internally.
            loadingTs = databaseWriter.getPlatform().parseDate(Types.VARCHAR, (String) values[0], false);
        } else {
            throw new ParseException("Could not parse " + columnName + " with a value of " + loadingStr + " for purposes of conflict detection");
        }
    }
    return existingTs == null || loadingTs.compareTo(existingTs) > 0;
}

Example 14 with DmlStatement

use of org.jumpmind.db.sql.DmlStatement in project symmetric-ds by JumpMind.

the class DbCompareDiffWriter method writeDelete.

public void writeDelete(DbCompareRow targetCompareRow) {
    if (stream == null) {
        return;
    }
    Table table = targetCompareRow.getTable();
    DmlStatement statement = targetEngine.getDatabasePlatform().createDmlStatement(DmlType.DELETE, table.getCatalog(), table.getSchema(), table.getName(), table.getPrimaryKeyColumns(), null, null, null);
    Row row = new Row(targetCompareRow.getTable().getPrimaryKeyColumnCount());
    for (int i = 0; i < targetCompareRow.getTable().getPrimaryKeyColumnCount(); i++) {
        row.put(table.getColumn(i).getName(), targetCompareRow.getRowValues().get(targetCompareRow.getTable().getColumn(i).getName()));
    }
    String sql = statement.buildDynamicDeleteSql(BinaryEncoding.HEX, row, false, true);
    writeLine(sql);
}

Example 15 with DmlStatement

use of org.jumpmind.db.sql.DmlStatement in project symmetric-ds by JumpMind.

the class DbCompareDiffWriter method writeUpdate.

public void writeUpdate(DbCompareRow targetCompareRow, Map<Column, String> deltas) {
    if (stream == null) {
        return;
    }
    Table table = targetCompareRow.getTable();
    Column[] changedColumns = deltas.keySet().toArray(new Column[deltas.keySet().size()]);
    DmlStatement statement = targetEngine.getDatabasePlatform().createDmlStatement(DmlType.UPDATE, table.getCatalog(), table.getSchema(), table.getName(), table.getPrimaryKeyColumns(), changedColumns, null, null);
    Row row = new Row(changedColumns.length + table.getPrimaryKeyColumnCount());
    for (Column changedColumn : deltas.keySet()) {
        String value = deltas.get(changedColumn);
        row.put(changedColumn.getName(), value);
    }
    for (String pkColumnName : table.getPrimaryKeyColumnNames()) {
        String value = targetCompareRow.getRow().getString(pkColumnName);
        row.put(pkColumnName, value);
    }
    String sql = statement.buildDynamicSql(BinaryEncoding.HEX, row, false, true);
    writeLine(sql);
}