Examples with TJDBCInputDefinition org.talend.components.jdbc.tjdbcinput.TJDBCInputDefinition used on opensource projects

Example 26 with TJDBCInputDefinition

use of org.talend.components.jdbc.tjdbcinput.TJDBCInputDefinition in project components by Talend.

the class JDBCInputTestIT method testReaderAllTypesString.

/**
 * Checks {@link JDBCInputReader} outputs {@link IndexedRecord} which contains nullable String type data for every SQL/JDBC
 * type
 */
@Test
public void testReaderAllTypesString() throws IOException {
    TJDBCInputDefinition definition = new TJDBCInputDefinition();
    TJDBCInputProperties properties = DBTestUtils.createCommonJDBCInputProperties(allSetting, definition);
    properties.main.schema.setValue(DBTestUtils.createAllTypesSchema(tablename_all_type));
    properties.tableSelection.tablename.setValue(tablename_all_type);
    properties.sql.setValue(DBTestUtils.getSQL(tablename_all_type));
    Reader reader = DBTestUtils.createCommonJDBCInputReader(properties);
    reader.start();
    IndexedRecord record = (IndexedRecord) reader.getCurrent();
    Short col0 = (Short) record.get(0);
    Integer col1 = (Integer) record.get(1);
    Long col2 = (Long) record.get(2);
    Float col3 = (Float) record.get(3);
    Double col4 = (Double) record.get(4);
    BigDecimal col5 = (BigDecimal) record.get(5);
    String col6 = (String) record.get(6);
    String col7 = (String) record.get(7);
    String col8 = (String) record.get(8);
    String col9 = (String) record.get(9);
    Long col10 = (Long) record.get(10);
    Long col11 = (Long) record.get(11);
    Long col12 = (Long) record.get(12);
    Boolean col13 = (Boolean) record.get(13);
    assertEquals(32767, col0.shortValue());
    assertEquals(2147483647, col1.intValue());
    assertEquals(9223372036854775807l, col2.longValue());
    assertTrue(col3 > 1);
    assertTrue(col4 > 2);
    assertEquals(new BigDecimal("1234567890.1234567890"), col5);
    assertEquals("abcd", col6);
    assertEquals("abcdefg", col7);
    assertEquals("00010203040506070809", col8);
    assertEquals("abcdefg", col9);
    assertEquals("2016-12-28", new SimpleDateFormat("yyyy-MM-dd").format(new Date(col10)));
    assertEquals("14:30:33", new SimpleDateFormat("HH:mm:ss").format(new Date(col11)));
    assertEquals("2016-12-28 14:31:56.123", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date(col12)));
    assertEquals(true, col13);
    Schema actualSchema = record.getSchema();
    List<Field> actualFields = actualSchema.getFields();
    assertEquals(14, actualFields.size());
    reader.close();
}

Example 27 with TJDBCInputDefinition

use of org.talend.components.jdbc.tjdbcinput.TJDBCInputDefinition in project components by Talend.

the class JDBCRowTestIT method commonAssert.

private void commonAssert(TJDBCRowProperties properties, Schema schema) throws IOException {
    JDBCRowSink sink = new JDBCRowSink();
    sink.initialize(null, properties);
    ValidationResult result = sink.validate(null);
    Assert.assertTrue(result.getStatus() == ValidationResult.Result.OK);
    WriteOperation operation = sink.createWriteOperation();
    JDBCRowWriter writer = (JDBCRowWriter) operation.createWriter(null);
    try {
        writer.open("wid");
        IndexedRecord r1 = new GenericData.Record(properties.main.schema.getValue());
        r1.put(0, 4);
        r1.put(1, "xiaoming");
        writer.write(r1);
        DBTestUtils.assertSuccessRecord(writer, r1);
        IndexedRecord r2 = new GenericData.Record(properties.main.schema.getValue());
        r2.put(0, 5);
        r2.put(1, "xiaobai");
        writer.write(r2);
        DBTestUtils.assertSuccessRecord(writer, r2);
        writer.close();
    } finally {
        writer.close();
    }
    TJDBCInputDefinition definition1 = new TJDBCInputDefinition();
    TJDBCInputProperties properties1 = DBTestUtils.createCommonJDBCInputProperties(allSetting, definition1);
    List<IndexedRecord> records = DBTestUtils.fetchDataByReaderFromTable(tablename, schema, definition1, properties1);
    assertThat(records, hasSize(5));
    Assert.assertEquals(new Integer(4), records.get(3).get(0));
    Assert.assertEquals("momo", records.get(3).get(1));
    Assert.assertEquals(new Integer(4), records.get(4).get(0));
    Assert.assertEquals("momo", records.get(4).get(1));
}

Example 28 with TJDBCInputDefinition

use of org.talend.components.jdbc.tjdbcinput.TJDBCInputDefinition in project components by Talend.

the class JDBCRowTestIT method test_use_preparedstatement_no_connector.

@Test
public void test_use_preparedstatement_no_connector() throws Exception {
    TJDBCRowDefinition definition = new TJDBCRowDefinition();
    TJDBCRowProperties properties = DBTestUtils.createCommonJDBCRowProperties(allSetting, definition);
    properties.tableSelection.tablename.setValue(tablename);
    properties.sql.setValue("insert into " + tablename + " values(?, ?)");
    properties.dieOnError.setValue(true);
    randomCommit(properties);
    properties.usePreparedStatement.setValue(true);
    properties.preparedStatementTable.indexs.setValue(Arrays.asList(1, 2));
    properties.preparedStatementTable.types.setValue(Arrays.asList(PreparedStatementTable.Type.Int.name(), PreparedStatementTable.Type.String.name()));
    properties.preparedStatementTable.values.setValue(Arrays.<Object>asList(4, "momo"));
    JDBCRowSourceOrSink sourceOrSink = new JDBCRowSourceOrSink();
    sourceOrSink.initialize(null, properties);
    ValidationResult result = sourceOrSink.validate(null);
    Assert.assertTrue(result.getStatus() == ValidationResult.Result.OK);
    TJDBCInputDefinition definition1 = new TJDBCInputDefinition();
    TJDBCInputProperties properties1 = DBTestUtils.createCommonJDBCInputProperties(allSetting, definition1);
    List<IndexedRecord> records = DBTestUtils.fetchDataByReaderFromTable(tablename, DBTestUtils.createTestSchema(tablename), definition1, properties1);
    assertThat(records, hasSize(4));
    Assert.assertEquals(new Integer(4), records.get(3).get(0));
    Assert.assertEquals("momo", records.get(3).get(1));
}

Example 29 with TJDBCInputDefinition

use of org.talend.components.jdbc.tjdbcinput.TJDBCInputDefinition in project components by Talend.

the class JDBCOutputTestIT method testUpdate.

@Test
public void testUpdate() throws Exception {
    TJDBCOutputDefinition definition = new TJDBCOutputDefinition();
    TJDBCOutputProperties properties = DBTestUtils.createCommonJDBCOutputProperties(allSetting, definition);
    Schema schema = DBTestUtils.createTestSchema2(tablename);
    properties.main.schema.setValue(schema);
    properties.updateOutputSchemas();
    properties.tableSelection.tablename.setValue(tablename);
    properties.dataAction.setValue(DataAction.UPDATE);
    properties.dieOnError.setValue(true);
    String randomInfo = randomBatchAndCommit(properties);
    JDBCOutputWriter writer = DBTestUtils.createCommonJDBCOutputWriter(definition, properties);
    try {
        writer.open("wid");
        IndexedRecord r1 = new GenericData.Record(properties.main.schema.getValue());
        r1.put(0, 1);
        r1.put(1, "wangwei1");
        writer.write(r1);
        DBTestUtils.assertSuccessRecord(writer, r1);
        IndexedRecord r2 = new GenericData.Record(properties.main.schema.getValue());
        r2.put(0, 2);
        r2.put(1, "gaoyan1");
        writer.write(r2);
        DBTestUtils.assertSuccessRecord(writer, r2);
        writer.close();
    } finally {
        writer.close();
    }
    TJDBCInputDefinition definition1 = new TJDBCInputDefinition();
    TJDBCInputProperties properties1 = DBTestUtils.createCommonJDBCInputProperties(allSetting, definition1);
    List<IndexedRecord> records = DBTestUtils.fetchDataByReaderFromTable(tablename, schema, definition1, properties1);
    assertThat(records, hasSize(3));
    Assert.assertEquals(new Integer(1), records.get(0).get(0));
    Assert.assertEquals("wangwei1", records.get(0).get(1));
    Assert.assertEquals(new Integer(2), records.get(1).get(0));
    Assert.assertEquals(randomInfo, "gaoyan1", records.get(1).get(1));
    Assert.assertEquals(new Integer(3), records.get(2).get(0));
    Assert.assertEquals("dabao", records.get(2).get(1));
}

Example 30 with TJDBCInputDefinition

use of org.talend.components.jdbc.tjdbcinput.TJDBCInputDefinition in project components by Talend.

the class JDBCOutputTestIT method testDelete.

@Test
public void testDelete() throws Exception {
    TJDBCOutputDefinition definition = new TJDBCOutputDefinition();
    TJDBCOutputProperties properties = DBTestUtils.createCommonJDBCOutputProperties(allSetting, definition);
    Schema schema = DBTestUtils.createTestSchema2(tablename);
    properties.main.schema.setValue(schema);
    properties.updateOutputSchemas();
    properties.tableSelection.tablename.setValue(tablename);
    properties.dataAction.setValue(DataAction.DELETE);
    properties.dieOnError.setValue(true);
    randomBatchAndCommit(properties);
    JDBCOutputWriter writer = DBTestUtils.createCommonJDBCOutputWriter(definition, properties);
    try {
        writer.open("wid");
        IndexedRecord r1 = new GenericData.Record(properties.main.schema.getValue());
        r1.put(0, 1);
        writer.write(r1);
        DBTestUtils.assertSuccessRecord(writer, r1);
        IndexedRecord r2 = new GenericData.Record(properties.main.schema.getValue());
        r2.put(0, 2);
        writer.write(r2);
        DBTestUtils.assertSuccessRecord(writer, r2);
        writer.close();
    } finally {
        writer.close();
    }
    TJDBCInputDefinition definition1 = new TJDBCInputDefinition();
    TJDBCInputProperties properties1 = DBTestUtils.createCommonJDBCInputProperties(allSetting, definition1);
    List<IndexedRecord> records = DBTestUtils.fetchDataByReaderFromTable(tablename, schema, definition1, properties1);
    assertThat(records, hasSize(1));
    Assert.assertEquals(new Integer(3), records.get(0).get(0));
    Assert.assertEquals("dabao", records.get(0).get(1));
}