@Test public void testCreateSqlStringValue() throws ProcessException, SQLException, JsonGenerationException, JsonMappingException, IOException, InitializationException { final TestRunner putSqlRunner = TestRunners.newTestRunner(PutSQL.class); final AtomicInteger id = new AtomicInteger(20); putSqlRunner.addControllerService("dbcp", service); putSqlRunner.enableControllerService(service); putSqlRunner.setProperty(PutSQL.OBTAIN_GENERATED_KEYS, "false"); putSqlRunner.setProperty(PutSQL.CONNECTION_POOL, "dbcp"); String tableName = "DIFTYPES"; ObjectMapper mapper = new ObjectMapper(); ResultSet colrs = null; try (final Connection conn = service.getConnection()) { try (final Statement stmt = conn.createStatement()) { stmt.executeUpdate(createDifferentTypes); } colrs = conn.getMetaData().getColumns(null, null, tableName, "%"); while (colrs.next()) { final int sqlType = colrs.getInt("DATA_TYPE"); final int colSize = colrs.getInt("COLUMN_SIZE"); switch (sqlType) { case Types.BOOLEAN: String json = mapper.writeValueAsString("true"); JsonNode fieldNode = mapper.readTree(json); String booleanString = ConvertJSONToSQL.createSqlStringValue(fieldNode, colSize, sqlType); assertEquals("true",booleanString); Map<String, String> attributes = new HashMap<>(); attributes.put("sql.args.1.type", String.valueOf(sqlType)); attributes.put("sql.args.1.value", booleanString); byte[] data = ("INSERT INTO DIFTYPES (ID, B) VALUES (" + id.incrementAndGet() + ", ?)").getBytes(); putSqlRunner.enqueue(data, attributes); putSqlRunner.run(); List<MockFlowFile> failed = putSqlRunner.getFlowFilesForRelationship(PutSQL.REL_FAILURE); putSqlRunner.assertTransferCount(PutSQL.REL_SUCCESS, 1); putSqlRunner.assertTransferCount(PutSQL.REL_FAILURE, 0); putSqlRunner.clearTransferState(); break; case Types.FLOAT: case Types.DOUBLE: json = mapper.writeValueAsString("78895654.6575"); fieldNode = mapper.readTree(json); String numberString = ConvertJSONToSQL.createSqlStringValue(fieldNode, colSize, sqlType); assertEquals("78895654.6575",numberString); attributes = new HashMap<>(); attributes.put("sql.args.1.type", String.valueOf(sqlType)); attributes.put("sql.args.1.value", numberString); data = ("INSERT INTO DIFTYPES (ID, dbl) VALUES (" + id.incrementAndGet() + ", ?)").getBytes(); putSqlRunner.enqueue(data, attributes); putSqlRunner.run(); failed = putSqlRunner.getFlowFilesForRelationship(PutSQL.REL_FAILURE); putSqlRunner.assertTransferCount(PutSQL.REL_SUCCESS, 1); putSqlRunner.assertTransferCount(PutSQL.REL_FAILURE, 0); putSqlRunner.clearTransferState(); break; default: break; } } } }
protected static String createSqlStringValue(final JsonNode fieldNode, final Integer colSize, final int sqlType) { String fieldValue = fieldNode.asText(); switch (sqlType) { case Types.BOOLEAN: fieldValue = Boolean.valueOf(fieldValue).toString(); break; case Types.BIT: case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.REAL: case Types.FLOAT: case Types.DOUBLE: case Types.DECIMAL: case Types.NUMERIC: if (fieldNode.isBoolean()) { fieldValue = fieldNode.asBoolean() ? "1" : "0"; } break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: break; case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: case Types.NCHAR: case Types.NVARCHAR: case Types.LONGNVARCHAR: if (colSize != null && fieldValue.length() > colSize) { fieldValue = fieldValue.substring(0, colSize); } break; } return fieldValue; }
ConvertJSONToSQL extends AbstractProcessor { protected static String createSqlStringValue(final JsonNode fieldNode, final Integer colSize, final int sqlType) { String fieldValue = fieldNode.asText(); switch (sqlType) { case Types.BOOLEAN: fieldValue = Boolean.valueOf(fieldValue).toString(); break; case Types.BIT: case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.REAL: case Types.FLOAT: case Types.DOUBLE: case Types.DECIMAL: case Types.NUMERIC: if (fieldNode.isBoolean()) { fieldValue = fieldNode.asBoolean() ? "1" : "0"; } break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: break; case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: case Types.NCHAR: case Types.NVARCHAR: case Types.LONGNVARCHAR: if (colSize != null && fieldValue.length() > colSize) { fieldValue = fieldValue.substring(0, colSize); } break; } return fieldValue; } }
ConvertJSONToSQL extends AbstractProcessor { protected static String createSqlStringValue(final JsonNode fieldNode, final Integer colSize, final int sqlType) { String fieldValue = fieldNode.asText(); switch (sqlType) { case Types.BOOLEAN: fieldValue = Boolean.valueOf(fieldValue).toString(); break; case Types.BIT: case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.REAL: case Types.FLOAT: case Types.DOUBLE: case Types.DECIMAL: case Types.NUMERIC: if (fieldNode.isBoolean()) { fieldValue = fieldNode.asBoolean() ? "1" : "0"; } break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: break; case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: case Types.NCHAR: case Types.NVARCHAR: case Types.LONGNVARCHAR: if (colSize != null && fieldValue.length() > colSize) { fieldValue = fieldValue.substring(0, colSize); } break; } return fieldValue; }  }
ConvertJSONToSQL extends AbstractProcessor { protected static String createSqlStringValue(final JsonNode fieldNode, final Integer colSize, final int sqlType) { String fieldValue = fieldNode.asText(); switch (sqlType) { case Types.BOOLEAN: fieldValue = Boolean.valueOf(fieldValue).toString(); break; case Types.BIT: case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.REAL: case Types.FLOAT: case Types.DOUBLE: case Types.DECIMAL: case Types.NUMERIC: if (fieldNode.isBoolean()) { fieldValue = fieldNode.asBoolean() ? "1" : "0"; } break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: break; case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: case Types.NCHAR: case Types.NVARCHAR: case Types.LONGNVARCHAR: if (colSize != null && fieldValue.length() > colSize) { fieldValue = fieldValue.substring(0, colSize); } break; } return fieldValue; }  @Override Set<Relationship> getRelationships(); @OnScheduled void onScheduled(final ProcessContext context); @Override void onTrigger(final ProcessContext context, final ProcessSession session); }
ConvertJSONToSQL extends AbstractProcessor { protected static String createSqlStringValue(final JsonNode fieldNode, final Integer colSize, final int sqlType) { String fieldValue = fieldNode.asText(); switch (sqlType) { case Types.BOOLEAN: fieldValue = Boolean.valueOf(fieldValue).toString(); break; case Types.BIT: case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.REAL: case Types.FLOAT: case Types.DOUBLE: case Types.DECIMAL: case Types.NUMERIC: if (fieldNode.isBoolean()) { fieldValue = fieldNode.asBoolean() ? "1" : "0"; } break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: break; case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: case Types.NCHAR: case Types.NVARCHAR: case Types.LONGNVARCHAR: if (colSize != null && fieldValue.length() > colSize) { fieldValue = fieldValue.substring(0, colSize); } break; } return fieldValue; }  @Override Set<Relationship> getRelationships(); @OnScheduled void onScheduled(final ProcessContext context); @Override void onTrigger(final ProcessContext context, final ProcessSession session);  }