Example usage for org.springframework.jdbc.support.rowset SqlRowSetMetaData getColumnTypeName

List of usage examples for org.springframework.jdbc.support.rowset SqlRowSetMetaData getColumnTypeName

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.support.rowset.*
  5. SqlRowSetMetaData
  6. getColumnTypeName

Introduction

In this page you can find the example usage for org.springframework.jdbc.support.rowset SqlRowSetMetaData getColumnTypeName.

Prototype

String getColumnTypeName(int columnIndex) throws InvalidResultSetAccessException;

Source Link

Document

Retrieve the DBMS-specific type name for the indicated column.

Usage

From source file:com.simplymeasured.prognosticator.HiveQueryCursorImpl.java

@Override
public Map<String, Object> get() {
    Map<String, Object> result = Maps.newHashMap();

    final SqlRowSetMetaData metadata = rowSet.getMetaData();

    for (int i = 1; i <= metadata.getColumnCount(); i++) {
        String columnTypeName = metadata.getColumnTypeName(i);

        final Object value;

        if ("array".equalsIgnoreCase(columnTypeName)) {
            value = parseJson(rowSet.getString(i), List.class);
        } else if ("map".equalsIgnoreCase(columnTypeName) || "struct".equalsIgnoreCase(columnTypeName)) {
            value = parseJson(rowSet.getString(i), Map.class);
        } else if ("string".equalsIgnoreCase(columnTypeName)) {
            value = HiveUtils.unescapeString(rowSet.getString(i));
        } else {//from   w  w w .  ja  v  a2 s  .c o  m
            value = rowSet.getObject(i);
        }

        result.put(metadata.getColumnName(i), value);
    }

    return result;
}

From source file:com.simplymeasured.prognosticator.HiveQueryCursorImplTest.java

License:asdf

@Test
public void testGet() {
    // build up a result inside the mock, test it.

    // column 1 = string, named STR
    // column 2 = long, named LNG
    // column 3 = Map, named MAP
    // column 4 = Array, named LIST
    // column 5 = Struct (serialized to a Java Map), named STRUCT

    SqlRowSetMetaData metadata = mock(SqlRowSetMetaData.class);

    when(metadata.getColumnCount()).thenReturn(5);

    when(metadata.getColumnName(1)).thenReturn("STR");
    when(metadata.getColumnTypeName(1)).thenReturn("STRING");

    when(metadata.getColumnName(2)).thenReturn("LNG");
    when(metadata.getColumnTypeName(2)).thenReturn("BIGINT");

    when(metadata.getColumnName(3)).thenReturn("MAP");
    when(metadata.getColumnTypeName(3)).thenReturn("MAP");

    when(metadata.getColumnName(4)).thenReturn("LIST");
    when(metadata.getColumnTypeName(4)).thenReturn("ARRAY");

    when(metadata.getColumnName(5)).thenReturn("STRUCT");
    when(metadata.getColumnTypeName(5)).thenReturn("STRUCT");

    when(rowSet.getMetaData()).thenReturn(metadata);

    when(rowSet.getString(1)).thenReturn("My string");
    when(rowSet.getObject(2)).thenReturn(123L);
    when(rowSet.getString(3)).thenReturn("{\"foo\":\"str1\", \"bar\":\"str2\"}");
    when(rowSet.getString(4)).thenReturn("[1, 2, 3]");
    when(rowSet.getString(5)).thenReturn("{\"struct1\": \"asdf\", \"struct2\": 1234 }");

    Map<String, Object> result = cursor.get();

    Assert.assertNotNull(result);//  www  .j a v a2 s  .  c  o  m
    Assert.assertFalse(result.isEmpty());

    Map<String, Object> expected = new HashMap<String, Object>() {
        {
            put("STR", "My string");
            put("LNG", 123L);
            put("MAP", new HashMap<String, String>() {
                {
                    put("foo", "str1");
                    put("bar", "str2");
                }
            });
            put("LIST", new ArrayList<Integer>() {
                {
                    add(1);
                    add(2);
                    add(3);
                }
            });
            put("STRUCT", new HashMap<String, Object>() {
                {
                    put("struct1", "asdf");
                    put("struct2", 1234);
                }
            });
        }
    };

    Assert.assertEquals(expected, result);
}

From source file:org.restsql.core.impl.AbstractSqlResourceMetaData.java

/**
 * Builds table and column meta data.//from   w w w.java 2 s.co m
 * 
 * @throws SqlResourceException
 */
@SuppressWarnings("fallthrough")
private void buildTablesAndColumns(final SqlRowSet resultSet) throws SqlResourceException {
    final SqlRowSetMetaData resultSetMetaData = resultSet.getMetaData();
    final int columnCount = resultSetMetaData.getColumnCount();

    allReadColumns = new ArrayList<ColumnMetaData>(columnCount);
    parentReadColumns = new ArrayList<ColumnMetaData>(columnCount);
    childReadColumns = new ArrayList<ColumnMetaData>(columnCount);
    tableMap = new HashMap<String, TableMetaData>(DEFAULT_NUMBER_TABLES);
    tables = new ArrayList<TableMetaData>(DEFAULT_NUMBER_TABLES);
    childPlusExtTables = new ArrayList<TableMetaData>(DEFAULT_NUMBER_TABLES);
    parentPlusExtTables = new ArrayList<TableMetaData>(DEFAULT_NUMBER_TABLES);
    final HashSet<String> databases = new HashSet<String>(DEFAULT_NUMBER_DATABASES);

    for (int colNumber = 1; colNumber <= columnCount; colNumber++) {
        final String databaseName, qualifiedTableName, tableName;
        // boolean readOnly = isColumnReadOnly(resultSetMetaData,
        // colNumber);
        // if (readOnly) {
        databaseName = SqlResourceDefinitionUtils.getDefaultDatabase(definition);
        tableName = SqlResourceDefinitionUtils.getTable(definition, TableRole.Parent).getName();
        qualifiedTableName = getQualifiedTableName(databaseName, tableName);

        final ColumnMetaDataImpl column = new ColumnMetaDataImpl(colNumber, databaseName, qualifiedTableName,
                tableName, getColumnName(definition, resultSetMetaData, colNumber),
                resultSetMetaData.getColumnLabel(colNumber), resultSetMetaData.getColumnTypeName(colNumber),
                resultSetMetaData.getColumnType(colNumber), true, this);

        TableMetaDataImpl table = (TableMetaDataImpl) tableMap.get(column.getQualifiedTableName());
        if (table == null) {
            // Create table metadata object and add to special references
            final Table tableDef = SqlResourceDefinitionUtils.getTable(definition, column);
            if (tableDef == null) {
                throw new SqlResourceException("Definition requires table element for " + column.getTableName()
                        + ", referenced by column " + column.getColumnLabel());
            }
            table = new TableMetaDataImpl(tableName, qualifiedTableName, databaseName,
                    TableRole.valueOf(tableDef.getRole()));
            tableMap.put(column.getQualifiedTableName(), table);
            tables.add(table);

            switch (table.getTableRole()) {
            case Parent:
                parentTable = table;
                if (tableDef.getAlias() != null) {
                    table.setTableAlias(tableDef.getAlias());
                }
                // fall through
            case ParentExtension:
                parentPlusExtTables.add(table);
                break;
            case Child:
                childTable = table;
                if (tableDef.getAlias() != null) {
                    table.setTableAlias(tableDef.getAlias());
                }
                // fall through
            case ChildExtension:
                childPlusExtTables.add(table);
                break;
            case Join: // unlikely to be in the select columns, but just in
                       // case
                joinTable = table;
                joinList = new ArrayList<TableMetaData>(1);
                joinList.add(joinTable);
                break;
            default: // Unknown
            }
        }

        // Add column to the table
        table.addColumn(column);
        column.setTableRole(table.getTableRole());

        // Add column to special column lists
        allReadColumns.add(column);
        switch (table.getTableRole()) {
        case Parent:
        case ParentExtension:
            parentReadColumns.add(column);
            break;
        case Child:
        case ChildExtension:
            childReadColumns.add(column);
            break;
        default: // Unknown
        }
    }

    // Determine number of databases
    multipleDatabases = databases.size() > 1;
}

From source file:org.apache.fineract.infrastructure.dataexport.helper.CsvFileHelper.java

/**
 * Creates a new CSV file//ww w.j a va 2 s . c  o  m
 * 
 * @param sqlRowSet
 * @param file
 */
public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {
        // create a new CSVWriter object
        final CSVWriter csvWriter = new CSVWriter(
                new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), ENCODING)), SEPARATOR,
                QUOTE_CHARACTER, ESCAPE_CHARACTER, DataExportApiConstants.WINDOWS_END_OF_LINE_CHARACTER);
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();
        final String[] headers = new String[columnCount];
        final List<String[]> data = new ArrayList<>();

        int columnIndex = 0;

        for (int i = 1; i <= columnCount; i++) {
            // get the column label of the dataset
            String columnLabel = WordUtils.capitalize(sqlRowSetMetaData.getColumnLabel(i));

            // add column label to headers array
            headers[columnIndex++] = columnLabel;
        }

        while (sqlRowSet.next()) {
            // create a new empty string array of length "columnCount"
            final String[] rowData = new String[columnCount];

            int rowIndex = 0;

            for (int i = 1; i <= columnCount; i++) {
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                rowData[rowIndex++] = StringEscapeUtils.escapeCsv(columnValue);
            }

            // add the row data to the array list of row data
            data.add(rowData);
        }

        // write file headers to file
        csvWriter.writeNext(headers);

        // write file data to file
        csvWriter.writeAll(data);

        // close stream writer
        csvWriter.close();
    }

    catch (Exception exception) {
        logger.error(exception.getMessage(), exception);
    }
}

From source file:org.apache.fineract.infrastructure.dataexport.helper.XlsFileHelper.java

public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {/*from  w  w  w.  j  ava 2  s.  c om*/
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();

        // Create a new spreadsheet workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Create a blank sheet for the workbook
        XSSFSheet sheet = workbook.createSheet();
        // create a new cell style object
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // create a new data format object 
        XSSFDataFormat dataFormat = workbook.createDataFormat();

        int rowIndex = 0;
        int columnIndex = 0;
        Row row = sheet.createRow(rowIndex++);

        for (int i = 1; i <= columnCount; i++) {
            // create a new cell for each columns for the header row
            Cell cell = row.createCell(columnIndex++);
            // get the column label of the dataset
            String columnLabel = DataExportUtils
                    .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable);
            // set the value of the cell
            cell.setCellValue(WordUtils.capitalize(columnLabel));
        }

        while (sqlRowSet.next()) {
            columnIndex = 0;
            row = sheet.createRow(rowIndex++);

            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(columnIndex++);
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                if (columnValue != null) {
                    switch (mysqlDataType.getCategory()) {
                    case NUMERIC:
                        // TINYINT(1) is also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit
                        // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212
                        if (mysqlDataType.equals(MysqlDataType.TINYINT)
                                && sqlRowSetMetaData.getPrecision(i) == 1
                                && (columnValue.equals("true") || columnValue.equals("false"))) {
                            // Handle the cell as string, it is already a casted boolean:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(columnValue);

                        } else {
                            double numberAsDouble = Double.parseDouble(columnValue);

                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(numberAsDouble);
                        }
                        break;

                    case DATE_TIME:
                        DateFormat dateFormat;
                        Date date;

                        switch (mysqlDataType) {
                        case DATE:
                        case DATETIME:
                            String mysqlDateFormat = "yyyy-MM-dd";
                            String excelDateFormat = "MM/DD/YYYY";

                            if (mysqlDataType.equals(MysqlDataType.DATETIME)) {
                                mysqlDateFormat = "yyyy-MM-dd HH:mm:ss";
                                excelDateFormat = "MM/DD/YYYY HH:MM:SS";
                            }

                            dateFormat = new SimpleDateFormat(mysqlDateFormat);
                            date = dateFormat.parse(columnValue);

                            cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat));

                            cell.setCellValue(date);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellStyle(cellStyle);
                            break;

                        default:
                            cell.setCellValue(columnValue);
                            break;
                        }
                        break;

                    default:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(columnValue);
                        break;
                    }

                } else {
                    cell.setCellValue(columnValue);
                }
            }
        }

        //Write the workbook in file system
        FileOutputStream fileOutputStream = new FileOutputStream(file);

        workbook.write(fileOutputStream);
        fileOutputStream.close();

    } catch (Exception exception) {
        exception.printStackTrace();
    }
}

From source file:org.mifosplatform.infrastructure.dataexport.helper.XlsFileHelper.java

public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {//  www  .  j a va2 s  . c o  m
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();

        // Create a new spreadsheet workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Create a blank sheet for the workbook
        XSSFSheet sheet = workbook.createSheet();
        // create a new cell style object
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // create a new data format object 
        XSSFDataFormat dataFormat = workbook.createDataFormat();

        int rowIndex = 0;
        int columnIndex = 0;
        Row row = sheet.createRow(rowIndex++);

        for (int i = 1; i <= columnCount; i++) {
            // create a new cell for each columns for the header row
            Cell cell = row.createCell(columnIndex++);
            // get the column label of the dataset
            String columnLabel = DataExportUtils
                    .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable);
            // set the value of the cell
            cell.setCellValue(WordUtils.capitalize(columnLabel));
        }

        while (sqlRowSet.next()) {
            columnIndex = 0;
            row = sheet.createRow(rowIndex++);

            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(columnIndex++);
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                if (columnValue != null) {
                    switch (mysqlDataType.getCategory()) {
                    case NUMERIC:

                        // TINYINT(1), BIT(1), etc are also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit
                        // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212
                        if (sqlRowSetMetaData.getPrecision(i) == 1
                                && (columnValue.equals("true") || columnValue.equals("false"))) {
                            // Handle the cell as string, it is already a casted boolean:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(columnValue);

                        } else {
                            double numberAsDouble = Double.parseDouble(columnValue);

                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(numberAsDouble);
                        }
                        break;

                    case DATE_TIME:
                        DateFormat dateFormat;
                        Date date;

                        switch (mysqlDataType) {
                        case DATE:
                        case DATETIME:
                            String mysqlDateFormat = "yyyy-MM-dd";
                            String excelDateFormat = "MM/DD/YYYY";

                            if (mysqlDataType.equals(MysqlDataType.DATETIME)) {
                                mysqlDateFormat = "yyyy-MM-dd HH:mm:ss";
                                excelDateFormat = "MM/DD/YYYY HH:MM:SS";
                            }

                            dateFormat = new SimpleDateFormat(mysqlDateFormat);
                            date = dateFormat.parse(columnValue);

                            cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat));

                            cell.setCellValue(date);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellStyle(cellStyle);
                            break;

                        default:
                            cell.setCellValue(columnValue);
                            break;
                        }
                        break;

                    default:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(columnValue);
                        break;
                    }

                } else {
                    cell.setCellValue(columnValue);
                }
            }
        }

        //Write the workbook in file system
        FileOutputStream fileOutputStream = new FileOutputStream(file);

        workbook.write(fileOutputStream);
        fileOutputStream.close();

    } catch (Exception exception) {
        exception.printStackTrace();
    }
}