Example usage for java.sql ResultSetMetaData getScale

List of usage examples for java.sql ResultSetMetaData getScale

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSetMetaData
  6. getScale

Introduction

In this page you can find the example usage for java.sql ResultSetMetaData getScale.

Prototype

int getScale(int column) throws SQLException;

Source Link

Document

Gets the designated column's number of digits to right of the decimal point.

Usage

From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java

public void testResultSetMetaData() throws SQLException {
    Statement stmt = con.createStatement();

    ResultSet res = stmt.executeQuery("select c1, c2, c3, c4, c5 as a, c6, c7, c8, c9, c10, c11, c12, "
            + "c1*2, sentences(null, null, null) as b, c17, c18, c20 from " + dataTypeTableName + " limit 1");
    ResultSetMetaData meta = res.getMetaData();

    ResultSet colRS = con.getMetaData().getColumns(null, null, dataTypeTableName.toLowerCase(), null);

    assertEquals(17, meta.getColumnCount());

    assertTrue(colRS.next());/*from  w w w  .jav a  2 s  .  co  m*/

    assertEquals("c1", meta.getColumnName(1));
    assertEquals(Types.INTEGER, meta.getColumnType(1));
    assertEquals("int", meta.getColumnTypeName(1));
    assertEquals(11, meta.getColumnDisplaySize(1));
    assertEquals(10, meta.getPrecision(1));
    assertEquals(0, meta.getScale(1));

    assertEquals("c1", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.INTEGER, colRS.getInt("DATA_TYPE"));
    assertEquals("int", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(1), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(1), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c2", meta.getColumnName(2));
    assertEquals("boolean", meta.getColumnTypeName(2));
    assertEquals(Types.BOOLEAN, meta.getColumnType(2));
    assertEquals(1, meta.getColumnDisplaySize(2));
    assertEquals(1, meta.getPrecision(2));
    assertEquals(0, meta.getScale(2));

    assertEquals("c2", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.BOOLEAN, colRS.getInt("DATA_TYPE"));
    assertEquals("boolean", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(2), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(2), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c3", meta.getColumnName(3));
    assertEquals(Types.DOUBLE, meta.getColumnType(3));
    assertEquals("double", meta.getColumnTypeName(3));
    assertEquals(25, meta.getColumnDisplaySize(3));
    assertEquals(15, meta.getPrecision(3));
    assertEquals(15, meta.getScale(3));

    assertEquals("c3", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.DOUBLE, colRS.getInt("DATA_TYPE"));
    assertEquals("double", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(3), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(3), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c4", meta.getColumnName(4));
    assertEquals(Types.VARCHAR, meta.getColumnType(4));
    assertEquals("string", meta.getColumnTypeName(4));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(4));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(4));
    assertEquals(0, meta.getScale(4));

    assertEquals("c4", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("string", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(4), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(4), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("a", meta.getColumnName(5));
    assertEquals(Types.VARCHAR, meta.getColumnType(5));
    assertEquals("string", meta.getColumnTypeName(5));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(5));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(5));
    assertEquals(0, meta.getScale(5));

    assertEquals("c5", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("array<int>", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(5), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(5), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c6", meta.getColumnName(6));
    assertEquals(Types.VARCHAR, meta.getColumnType(6));
    assertEquals("string", meta.getColumnTypeName(6));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(6));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(6));
    assertEquals(0, meta.getScale(6));

    assertEquals("c6", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("map<int,string>", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(6), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(6), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c7", meta.getColumnName(7));
    assertEquals(Types.VARCHAR, meta.getColumnType(7));
    assertEquals("string", meta.getColumnTypeName(7));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(7));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(7));
    assertEquals(0, meta.getScale(7));

    assertEquals("c7", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("map<string,string>", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(7), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(7), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c8", meta.getColumnName(8));
    assertEquals(Types.VARCHAR, meta.getColumnType(8));
    assertEquals("string", meta.getColumnTypeName(8));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(8));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(8));
    assertEquals(0, meta.getScale(8));

    assertEquals("c8", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE"));
    assertEquals("struct<r:string,s:int,t:double>", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(8), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(8), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c9", meta.getColumnName(9));
    assertEquals(Types.TINYINT, meta.getColumnType(9));
    assertEquals("tinyint", meta.getColumnTypeName(9));
    assertEquals(4, meta.getColumnDisplaySize(9));
    assertEquals(3, meta.getPrecision(9));
    assertEquals(0, meta.getScale(9));

    assertEquals("c9", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.TINYINT, colRS.getInt("DATA_TYPE"));
    assertEquals("tinyint", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(9), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(9), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c10", meta.getColumnName(10));
    assertEquals(Types.SMALLINT, meta.getColumnType(10));
    assertEquals("smallint", meta.getColumnTypeName(10));
    assertEquals(6, meta.getColumnDisplaySize(10));
    assertEquals(5, meta.getPrecision(10));
    assertEquals(0, meta.getScale(10));

    assertEquals("c10", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.SMALLINT, colRS.getInt("DATA_TYPE"));
    assertEquals("smallint", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(10), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(10), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c11", meta.getColumnName(11));
    assertEquals(Types.FLOAT, meta.getColumnType(11));
    assertEquals("float", meta.getColumnTypeName(11));
    assertEquals(24, meta.getColumnDisplaySize(11));
    assertEquals(7, meta.getPrecision(11));
    assertEquals(7, meta.getScale(11));

    assertEquals("c11", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.FLOAT, colRS.getInt("DATA_TYPE"));
    assertEquals("float", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(11), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(11), colRS.getInt("DECIMAL_DIGITS"));

    assertTrue(colRS.next());

    assertEquals("c12", meta.getColumnName(12));
    assertEquals(Types.BIGINT, meta.getColumnType(12));
    assertEquals("bigint", meta.getColumnTypeName(12));
    assertEquals(20, meta.getColumnDisplaySize(12));
    assertEquals(19, meta.getPrecision(12));
    assertEquals(0, meta.getScale(12));

    assertEquals("c12", colRS.getString("COLUMN_NAME"));
    assertEquals(Types.BIGINT, colRS.getInt("DATA_TYPE"));
    assertEquals("bigint", colRS.getString("TYPE_NAME").toLowerCase());
    assertEquals(meta.getPrecision(12), colRS.getInt("COLUMN_SIZE"));
    assertEquals(meta.getScale(12), colRS.getInt("DECIMAL_DIGITS"));

    assertEquals("_c12", meta.getColumnName(13));
    assertEquals(Types.INTEGER, meta.getColumnType(13));
    assertEquals("int", meta.getColumnTypeName(13));
    assertEquals(11, meta.getColumnDisplaySize(13));
    assertEquals(10, meta.getPrecision(13));
    assertEquals(0, meta.getScale(13));

    assertEquals("b", meta.getColumnName(14));
    assertEquals(Types.VARCHAR, meta.getColumnType(14));
    assertEquals("string", meta.getColumnTypeName(14));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(14));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(14));
    assertEquals(0, meta.getScale(14));

    assertEquals("c17", meta.getColumnName(15));
    assertEquals(Types.TIMESTAMP, meta.getColumnType(15));
    assertEquals("timestamp", meta.getColumnTypeName(15));
    assertEquals(29, meta.getColumnDisplaySize(15));
    assertEquals(29, meta.getPrecision(15));
    assertEquals(9, meta.getScale(15));

    assertEquals("c18", meta.getColumnName(16));
    assertEquals(Types.DECIMAL, meta.getColumnType(16));
    assertEquals("decimal", meta.getColumnTypeName(16));
    assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(16));
    assertEquals(Integer.MAX_VALUE, meta.getPrecision(16));
    assertEquals(Integer.MAX_VALUE, meta.getScale(16));

    assertEquals("c20", meta.getColumnName(17));
    assertEquals(Types.DATE, meta.getColumnType(17));
    assertEquals("date", meta.getColumnTypeName(17));
    assertEquals(10, meta.getColumnDisplaySize(17));
    assertEquals(10, meta.getPrecision(17));
    assertEquals(0, meta.getScale(17));

    for (int i = 1; i <= meta.getColumnCount(); i++) {
        assertFalse(meta.isAutoIncrement(i));
        assertFalse(meta.isCurrency(i));
        assertEquals(ResultSetMetaData.columnNullable, meta.isNullable(i));
    }
}

From source file:com.openddal.test.BaseTestCase.java

/**
 * Check if the result set meta data is correct.
 *
 * @param rs the result set//from   w  w  w.  jav  a 2 s .  c om
 * @param columnCount the expected column count
 * @param labels the expected column labels
 * @param datatypes the expected data types
 * @param precision the expected precisions
 * @param scale the expected scales
 */
protected void assertResultSetMeta(ResultSet rs, int columnCount, String[] labels, int[] datatypes,
        int[] precision, int[] scale) throws SQLException {
    ResultSetMetaData meta = rs.getMetaData();
    int cc = meta.getColumnCount();
    if (cc != columnCount) {
        fail("result set contains " + cc + " columns not " + columnCount);
    }
    for (int i = 0; i < columnCount; i++) {
        if (labels != null) {
            String l = meta.getColumnLabel(i + 1);
            if (!labels[i].equals(l)) {
                fail("column label " + i + " is " + l + " not " + labels[i]);
            }
        }
        if (datatypes != null) {
            int t = meta.getColumnType(i + 1);
            if (datatypes[i] != t) {
                fail("column datatype " + i + " is " + t + " not " + datatypes[i] + " (prec="
                        + meta.getPrecision(i + 1) + " scale=" + meta.getScale(i + 1) + ")");
            }
            String typeName = meta.getColumnTypeName(i + 1);
            String className = meta.getColumnClassName(i + 1);
            switch (t) {
            case Types.INTEGER:
                Assert.assertEquals("INTEGER", typeName);
                Assert.assertEquals("java.lang.Integer", className);
                break;
            case Types.VARCHAR:
                Assert.assertEquals("VARCHAR", typeName);
                Assert.assertEquals("java.lang.String", className);
                break;
            case Types.SMALLINT:
                Assert.assertEquals("SMALLINT", typeName);
                Assert.assertEquals("java.lang.Short", className);
                break;
            case Types.TIMESTAMP:
                Assert.assertEquals("TIMESTAMP", typeName);
                Assert.assertEquals("java.sql.Timestamp", className);
                break;
            case Types.DECIMAL:
                Assert.assertEquals("DECIMAL", typeName);
                Assert.assertEquals("java.math.BigDecimal", className);
                break;
            default:
            }
        }
        if (precision != null) {
            int p = meta.getPrecision(i + 1);
            if (precision[i] != p) {
                fail("column precision " + i + " is " + p + " not " + precision[i]);
            }
        }
        if (scale != null) {
            int s = meta.getScale(i + 1);
            if (scale[i] != s) {
                fail("column scale " + i + " is " + s + " not " + scale[i]);
            }
        }

    }
}

From source file:org.seasar.dbflute.logic.sql2entity.cmentity.DfCustomizeEntityMetaExtractor.java

public Map<String, DfColumnMeta> extractColumnMetaInfoMap(ResultSet rs, String sql,
        DfForcedJavaNativeProvider forcedJavaNativeProvider) throws SQLException {
    final Map<String, DfColumnMeta> columnMetaInfoMap = StringKeyMap.createAsFlexibleOrdered();
    final ResultSetMetaData md = rs.getMetaData();
    for (int i = 1; i <= md.getColumnCount(); i++) {
        final DfColumnMeta metaInfo = new DfColumnMeta();

        String sql2EntityRelatedTableName = null;
        try {/*from www  .  j av  a  2 s .c  om*/
            sql2EntityRelatedTableName = md.getTableName(i);
        } catch (SQLException ignored) {
            // Because this table name is not required. This is for classification.
            String msg = "ResultSetMetaData.getTableName(" + i + ") threw the exception:";
            msg = msg + " " + ignored.getMessage();
            _log.info(msg);
        }
        metaInfo.setSql2EntityRelatedTableName(sql2EntityRelatedTableName);

        String columnName = md.getColumnLabel(i);
        final String relatedColumnName = md.getColumnName(i);
        metaInfo.setSql2EntityRelatedColumnName(relatedColumnName);
        if (columnName == null || columnName.trim().length() == 0) {
            columnName = relatedColumnName;
        }
        if (columnName == null || columnName.trim().length() == 0) {
            final String ln = ln();
            String msg = "The columnName is invalid: columnName=" + columnName + ln;
            msg = msg + "ResultSetMetaData returned invalid value." + ln;
            msg = msg + "sql=" + sql;
            throw new IllegalStateException(msg);
        }
        metaInfo.setColumnName(columnName);

        final int columnType = md.getColumnType(i);
        metaInfo.setJdbcDefValue(columnType);

        final String columnTypeName = md.getColumnTypeName(i);
        metaInfo.setDbTypeName(columnTypeName);

        int columnSize = md.getPrecision(i);
        if (!DfColumnExtractor.isColumnSizeValid(columnSize)) {
            // ex) sum(COLUMN)
            columnSize = md.getColumnDisplaySize(i);
        }
        metaInfo.setColumnSize(columnSize);

        final int scale = md.getScale(i);
        metaInfo.setDecimalDigits(scale);

        if (forcedJavaNativeProvider != null) {
            final String sql2entityForcedJavaNative = forcedJavaNativeProvider.provide(columnName);
            metaInfo.setSql2EntityForcedJavaNative(sql2entityForcedJavaNative);
        }

        // column comment is not set here (no comment on meta data)
        // if select column comment is specified, comment will be set later

        columnMetaInfoMap.put(columnName, metaInfo);
    }
    return columnMetaInfoMap;
}

From source file:org.nuclos.server.dblayer.impl.standard.StandardSqlDBAccess.java

@Override
public ResultVO executePlainQueryAsResultVO(String sql, int maxRows) throws DbException {
    return executePlainQuery(sql, maxRows, new ResultSetRunner<ResultVO>() {
        @Override//from ww  w  . j  a  v a 2 s. c om
        public ResultVO perform(ResultSet rs) throws SQLException {
            ResultVO result = new ResultVO();
            ResultSetMetaData metadata = rs.getMetaData();

            Class<?>[] javaTypes = new Class<?>[metadata.getColumnCount()];
            for (int i = 0; i < metadata.getColumnCount(); i++) {
                ResultColumnVO column = new ResultColumnVO();
                column.setColumnLabel(metadata.getColumnLabel(i + 1));

                DbGenericType type = getDbGenericType(metadata.getColumnType(i + 1),
                        metadata.getColumnTypeName(i + 1));
                if (type != null) {
                    Class<?> javaType = type.getPreferredJavaType();
                    // override java type here @todo this is not the right place.
                    if (type == DbGenericType.NUMERIC) {
                        if (metadata.getScale(i + 1) == 0)
                            javaType = Integer.class;
                        else
                            javaType = Double.class;
                    }
                    column.setColumnClassName(javaType.getName());
                    javaTypes[i] = javaType;
                } else {
                    column.setColumnClassName(metadata.getColumnClassName(i + 1));
                    javaTypes[i] = Object.class;
                }
                result.addColumn(column);
            }
            while (rs.next()) {
                final Object[] values = new Object[javaTypes.length];
                for (int i = 0; i < values.length; i++) {
                    values[i] = getResultSetValue(rs, i + 1, javaTypes[i]);
                }
                result.addRow(values);
            }
            return result;
        }
    });
}

From source file:org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.ResultSetTableModelFactory.java

/**
 * No longer used./*from w  w  w . j ava2s.  c om*/
 *
 * @param rsmd
 * @param metaData
 * @param column
 */
@Deprecated
public static void updateMetaData(final ResultSetMetaData rsmd, final DefaultTableMetaData metaData,
        final int column) {
    try {
        if (rsmd.isCurrency(column + 1)) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                    MetaAttributeNames.Numeric.CURRENCY, Boolean.TRUE);
        } else {
            metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                    MetaAttributeNames.Numeric.CURRENCY, Boolean.FALSE);
        }

        if (rsmd.isSigned(column + 1)) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                    MetaAttributeNames.Numeric.SIGNED, Boolean.TRUE);
        } else {
            metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                    MetaAttributeNames.Numeric.SIGNED, Boolean.FALSE);
        }

        final String tableName = rsmd.getTableName(column + 1);
        if (tableName != null) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Database.NAMESPACE,
                    MetaAttributeNames.Database.TABLE, tableName);
        }
        final String schemaName = rsmd.getSchemaName(column + 1);
        if (schemaName != null) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Database.NAMESPACE,
                    MetaAttributeNames.Database.SCHEMA, schemaName);
        }
        final String catalogName = rsmd.getCatalogName(column + 1);
        if (catalogName != null) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Database.NAMESPACE,
                    MetaAttributeNames.Database.CATALOG, catalogName);
        }
        final String label = rsmd.getColumnLabel(column + 1);
        if (label != null) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Formatting.NAMESPACE,
                    MetaAttributeNames.Formatting.LABEL, label);
        }
        final int displaySize = rsmd.getColumnDisplaySize(column + 1);
        metaData.setColumnAttribute(column, MetaAttributeNames.Formatting.NAMESPACE,
                MetaAttributeNames.Formatting.DISPLAY_SIZE, IntegerCache.getInteger(displaySize));

        final int precision = rsmd.getPrecision(column + 1);
        metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                MetaAttributeNames.Numeric.PRECISION, IntegerCache.getInteger(precision));
        final int scale = rsmd.getScale(column + 1);
        metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                MetaAttributeNames.Numeric.SCALE, IntegerCache.getInteger(scale));
    } catch (SQLException sqle) {
        // It is non-fatal if the meta-data cannot be read from the result set. Drivers are
        // buggy all the time ..
    }
}

From source file:com.glaf.core.jdbc.QueryHelper.java

@SuppressWarnings("unchecked")
public List<ColumnDefinition> getColumnDefinitions(String systemName, String sql, Map<String, Object> params) {
    SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, params);
    Connection conn = null;//from w ww. j  a  v a 2s  . c om
    PreparedStatement psmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    try {
        conn = DBConnectionFactory.getConnection(systemName);
        psmt = conn.prepareStatement(sqlExecutor.getSql());
        if (sqlExecutor.getParameter() != null) {
            List<Object> values = (List<Object>) sqlExecutor.getParameter();
            JdbcUtils.fillStatement(psmt, values);
        }
        rs = psmt.executeQuery();
        rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();
        for (int index = 1; index <= count; index++) {
            int sqlType = rsmd.getColumnType(index);
            ColumnDefinition column = new ColumnDefinition();
            column.setIndex(index);
            column.setColumnName(rsmd.getColumnName(index));
            column.setColumnLabel(rsmd.getColumnLabel(index));
            column.setJavaType(FieldType.getJavaType(sqlType));
            column.setPrecision(rsmd.getPrecision(index));
            column.setScale(rsmd.getScale(index));
            if (column.getScale() == 0 && sqlType == Types.NUMERIC) {
                column.setJavaType("Long");
            }
            column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase()));
            columns.add(column);
        }
        return columns;
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(psmt);
        JdbcUtils.close(conn);
    }
}

From source file:com.glaf.core.jdbc.QueryHelper.java

@SuppressWarnings("unchecked")
public List<ColumnDefinition> getColumns(Connection conn, String sql, Map<String, Object> paramMap) {
    if (!DBUtils.isLegalQuerySql(sql)) {
        throw new RuntimeException(" SQL statement illegal ");
    }/*from  w w  w  .j  a  v a  2 s  . co m*/
    List<ColumnDefinition> columns = new java.util.ArrayList<ColumnDefinition>();
    PreparedStatement psmt = null;
    ResultSetMetaData rsmd = null;
    ResultSet rs = null;
    try {
        List<Object> values = null;
        if (paramMap != null) {
            SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap);
            sql = sqlExecutor.getSql();
            values = (List<Object>) sqlExecutor.getParameter();
        }

        logger.debug("sql:\n" + sql);
        logger.debug("values:" + values);

        psmt = conn.prepareStatement(sql);

        if (values != null && !values.isEmpty()) {
            JdbcUtils.fillStatement(psmt, values);
        }

        rs = psmt.executeQuery();
        rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        for (int i = 1; i <= count; i++) {
            int sqlType = rsmd.getColumnType(i);
            ColumnDefinition column = new ColumnDefinition();
            column.setColumnLabel(rsmd.getColumnLabel(i));
            column.setColumnName(rsmd.getColumnName(i));
            column.setJavaType(FieldType.getJavaType(sqlType));
            column.setPrecision(rsmd.getPrecision(i));
            column.setScale(rsmd.getScale(i));
            column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase()));
            if (column.getScale() == 0 && sqlType == Types.NUMERIC) {
                column.setJavaType("Long");
            }
            if (!columns.contains(column)) {
                columns.add(column);
            }
            logger.debug(column.getColumnName() + " sqlType:" + sqlType + " precision:" + column.getPrecision()
                    + " scale:" + column.getScale());
        }

    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(psmt);
        JdbcUtils.close(rs);
    }
    return columns;
}

From source file:com.glaf.core.jdbc.QueryHelper.java

/**
 * @param conn//from   w  w w. j  a  va2s.  c o  m
 *            ?
 * @param sqlExecutor
 *            
 * @return
 */
@SuppressWarnings("unchecked")
public List<Map<String, Object>> getResultList(Connection conn, SqlExecutor sqlExecutor) {
    if (!DBUtils.isLegalQuerySql(sqlExecutor.getSql())) {
        throw new RuntimeException(" SQL statement illegal ");
    }
    List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
    PreparedStatement psmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    try {
        psmt = conn.prepareStatement(sqlExecutor.getSql());
        if (sqlExecutor.getParameter() != null) {
            List<Object> values = (List<Object>) sqlExecutor.getParameter();
            JdbcUtils.fillStatement(psmt, values);
        }

        rs = psmt.executeQuery();

        if (conf.getBoolean("useMyBatisResultHandler", false)) {

            resultList = this.getResults(rs);

        } else {

            rsmd = rs.getMetaData();

            int count = rsmd.getColumnCount();
            List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();

            for (int index = 1; index <= count; index++) {
                int sqlType = rsmd.getColumnType(index);
                ColumnDefinition column = new ColumnDefinition();
                column.setIndex(index);
                column.setColumnName(rsmd.getColumnName(index));
                column.setColumnLabel(rsmd.getColumnLabel(index));
                column.setJavaType(FieldType.getJavaType(sqlType));
                column.setPrecision(rsmd.getPrecision(index));
                column.setScale(rsmd.getScale(index));
                if (column.getScale() == 0 && sqlType == Types.NUMERIC) {
                    column.setJavaType("Long");
                }
                column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase()));
                columns.add(column);
            }
            int startIndex = 1;
            while (rs.next() && startIndex <= 50000) {
                int index = 0;
                startIndex++;
                Map<String, Object> rowMap = new HashMap<String, Object>();
                Iterator<ColumnDefinition> iterator = columns.iterator();
                while (iterator.hasNext()) {
                    ColumnDefinition column = iterator.next();
                    String columnLabel = column.getColumnLabel();
                    String columnName = column.getColumnName();
                    if (StringUtils.isEmpty(columnName)) {
                        columnName = column.getColumnLabel();
                    }
                    columnName = columnName.toLowerCase();
                    String javaType = column.getJavaType();
                    index = index + 1;
                    if ("String".equals(javaType)) {
                        String value = rs.getString(column.getIndex());
                        if (value != null) {
                            value = value.trim();
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, value);
                        }
                    } else if ("Integer".equals(javaType)) {
                        try {
                            Integer value = rs.getInt(column.getIndex());
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, value);
                        } catch (Exception e) {
                            String str = rs.getString(column.getIndex());
                            logger.error("integer:" + str);
                            str = StringTools.replace(str, "$", "");
                            str = StringTools.replace(str, "", "");
                            str = StringTools.replace(str, ",", "");
                            NumberFormat fmt = NumberFormat.getInstance();
                            Number num = fmt.parse(str);
                            rowMap.put(columnName, num.intValue());
                            rowMap.put(columnLabel, rowMap.get(columnName));
                            logger.debug("?:" + num.intValue());
                        }
                    } else if ("Long".equals(javaType)) {
                        try {
                            Long value = rs.getLong(column.getIndex());
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, rowMap.get(columnName));
                        } catch (Exception e) {
                            String str = rs.getString(column.getIndex());
                            logger.error("long:" + str);
                            str = StringTools.replace(str, "$", "");
                            str = StringTools.replace(str, "", "");
                            str = StringTools.replace(str, ",", "");
                            NumberFormat fmt = NumberFormat.getInstance();
                            Number num = fmt.parse(str);
                            rowMap.put(columnName, num.longValue());
                            rowMap.put(columnLabel, num.longValue());
                            logger.debug("?:" + num.longValue());
                        }
                    } else if ("Double".equals(javaType)) {
                        try {
                            Double d = rs.getDouble(column.getIndex());
                            rowMap.put(columnName, d);
                            rowMap.put(columnLabel, d);
                        } catch (Exception e) {
                            String str = rs.getString(column.getIndex());
                            logger.error("double:" + str);
                            str = StringTools.replace(str, "$", "");
                            str = StringTools.replace(str, "", "");
                            str = StringTools.replace(str, ",", "");
                            NumberFormat fmt = NumberFormat.getInstance();
                            Number num = fmt.parse(str);
                            rowMap.put(columnName, num.doubleValue());
                            rowMap.put(columnLabel, num.doubleValue());
                            logger.debug("?:" + num.doubleValue());
                        }
                    } else if ("Boolean".equals(javaType)) {
                        rowMap.put(columnName, rs.getBoolean(column.getIndex()));
                        rowMap.put(columnLabel, rowMap.get(columnName));
                    } else if ("Date".equals(javaType)) {
                        rowMap.put(columnName, rs.getTimestamp(column.getIndex()));
                        rowMap.put(columnLabel, rowMap.get(columnName));
                    } else if ("Blob".equals(javaType)) {
                        // ignore
                    } else {
                        Object value = rs.getObject(column.getIndex());
                        if (value != null) {
                            if (value instanceof String) {
                                value = (String) value.toString().trim();
                            }
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, rowMap.get(columnName));
                        }
                    }
                }
                rowMap.put("startIndex", startIndex);
                resultList.add(rowMap);
            }
        }

        logger.debug(">resultList size=" + resultList.size());
        return resultList;
    } catch (Exception ex) {
        logger.error(ex);
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(psmt);
        JdbcUtils.close(rs);
    }
}

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

@Test
public void testIntervalTypes() throws Exception {
    Statement stmt = con.createStatement();

    // Since interval types not currently supported as table columns, need to create them
    // as expressions.
    ResultSet res = stmt//from  ww  w.  j  a v a 2  s  .c  o m
            .executeQuery("select case when c17 is null then null else interval '1' year end as col1,"
                    + " c17 -  c17 as col2 from " + dataTypeTableName + " order by col1");
    ResultSetMetaData meta = res.getMetaData();

    assertEquals("col1", meta.getColumnLabel(1));
    assertEquals(java.sql.Types.OTHER, meta.getColumnType(1));
    assertEquals("interval_year_month", meta.getColumnTypeName(1));
    assertEquals(11, meta.getColumnDisplaySize(1));
    assertEquals(11, meta.getPrecision(1));
    assertEquals(0, meta.getScale(1));
    assertEquals(HiveIntervalYearMonth.class.getName(), meta.getColumnClassName(1));

    assertEquals("col2", meta.getColumnLabel(2));
    assertEquals(java.sql.Types.OTHER, meta.getColumnType(2));
    assertEquals("interval_day_time", meta.getColumnTypeName(2));
    assertEquals(29, meta.getColumnDisplaySize(2));
    assertEquals(29, meta.getPrecision(2));
    assertEquals(0, meta.getScale(2));
    assertEquals(HiveIntervalDayTime.class.getName(), meta.getColumnClassName(2));

    // row 1 - results should be null
    assertTrue(res.next());
    // skip the last (partitioning) column since it is always non-null
    for (int i = 1; i < meta.getColumnCount(); i++) {
        assertNull("Column " + i + " should be null", res.getObject(i));
    }

    // row 2 - results should be null
    assertTrue(res.next());
    for (int i = 1; i < meta.getColumnCount(); i++) {
        assertNull("Column " + i + " should be null", res.getObject(i));
    }

    // row 3
    assertTrue(res.next());
    assertEquals("1-0", res.getString(1));
    assertEquals(1, ((HiveIntervalYearMonth) res.getObject(1)).getYears());
    assertEquals("0 00:00:00.000000000", res.getString(2));
    assertEquals(0, ((HiveIntervalDayTime) res.getObject(2)).getDays());
}

From source file:com.glaf.dts.transform.MxTransformThread.java

@SuppressWarnings("unchecked")
public void run() {
    logger.debug(taskId + "----------------execution-----------------");
    TransformTask task = transformTaskService.getTransformTask(taskId);
    if (task != null) {
        if (task.getStatus() == 9 || task.getRetryTimes() > 3) {
            return;
        }// w  ww  .j  a  v  a  2  s.c  om
        task.setStartTime(new java.util.Date());
        task.setRetryTimes(task.getRetryTimes() + 1);
        task.setStatus(1);
        transformTaskService.save(task);
    }

    List<TableModel> resultList = new java.util.ArrayList<TableModel>();
    Map<String, Object> singleDataMap = new HashMap<String, Object>();
    Connection conn = null;
    PreparedStatement psmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    boolean success = true;
    long start = System.currentTimeMillis();
    logger.debug("start:" + DateUtils.getDateTime(new java.util.Date()));
    try {
        Database database = getDatabaseService().getDatabaseById(queryDefinition.getDatabaseId());
        if (database != null) {
            conn = DBConnectionFactory.getConnection(database.getName());
        } else {
            conn = DBConnectionFactory.getConnection();
        }

        logger.debug("conn:" + conn.toString());

        String sql = queryDefinition.getSql();
        sql = QueryUtils.replaceSQLVars(sql);
        List<Object> values = null;
        if (paramMap != null) {
            SqlExecutor sqlExecutor = JdbcUtils.rebuildSQL(sql, paramMap);
            sql = sqlExecutor.getSql();
            values = (List<Object>) sqlExecutor.getParameter();
        }

        logger.debug("--------------execute query----------------------");
        logger.debug(queryDefinition.getTitle());

        logger.debug("::sql::" + sql);
        psmt = conn.prepareStatement(sql);

        if (values != null && !values.isEmpty()) {
            JdbcUtils.fillStatement(psmt, values);
            logger.debug("::values::" + values);
        }

        List<ColumnDefinition> columns = new java.util.ArrayList<ColumnDefinition>();

        rs = psmt.executeQuery();
        rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        for (int i = 1; i <= count; i++) {
            int sqlType = rsmd.getColumnType(i);
            ColumnDefinition column = new ColumnDefinition();
            column.setColumnName(rsmd.getColumnName(i));
            column.setColumnLabel(rsmd.getColumnLabel(i));
            column.setJavaType(FieldType.getJavaType(sqlType));
            column.setPrecision(rsmd.getPrecision(i));
            column.setScale(rsmd.getScale(i));
            columns.add(column);
        }

        Set<String> cols = new HashSet<String>();

        while (rs.next()) {
            int index = 0;
            TableModel rowModel = new TableModel();

            ColumnModel cell01 = new ColumnModel();
            cell01.setColumnName("ID");
            cell01.setType("String");
            rowModel.addColumn(cell01);
            rowModel.setIdColumn(cell01);
            cols.add(cell01.getColumnName());

            ColumnModel cell04 = new ColumnModel();
            cell04.setColumnName("AGGREGATIONKEY");
            cell04.setType("String");
            rowModel.addColumn(cell04);
            cols.add(cell04.getColumnName());

            Iterator<ColumnDefinition> iterator = columns.iterator();
            while (iterator.hasNext()) {
                ColumnDefinition column = iterator.next();
                /**
                 * ????
                 */
                if (cols.contains(column.getColumnName())) {
                    continue;
                }
                ColumnModel cell = new ColumnModel();
                String columnName = column.getColumnName();
                String javaType = column.getJavaType();
                cell.setColumnName(columnName);
                cell.setType(javaType);
                index = index + 1;
                if ("String".equals(javaType)) {
                    String value = rs.getString(columnName);
                    cell.setStringValue(value);
                    cell.setValue(value);
                } else if ("Integer".equals(javaType)) {
                    try {
                        Integer value = rs.getInt(columnName);
                        cell.setIntValue(value);
                        cell.setValue(value);
                    } catch (Exception e) {
                        String str = rs.getString(columnName);
                        logger.error("integer:" + str);
                        str = StringTools.replace(str, "$", "");
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        cell.setIntValue(num.intValue());
                        cell.setValue(cell.getIntValue());
                        logger.debug("?:" + num.intValue());
                    }
                } else if ("Long".equals(javaType)) {
                    try {
                        Long value = rs.getLong(columnName);
                        cell.setLongValue(value);
                        cell.setValue(value);
                    } catch (Exception e) {
                        String str = rs.getString(columnName);
                        logger.error("long:" + str);
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        cell.setLongValue(num.longValue());
                        cell.setValue(cell.getLongValue());
                        logger.debug("?:" + num.longValue());
                    }
                } else if ("Double".equals(javaType)) {
                    try {
                        Double d = rs.getDouble(columnName);
                        cell.setDoubleValue(d);
                        cell.setValue(d);
                    } catch (Exception e) {
                        String str = rs.getString(columnName);
                        logger.error("double:" + str);
                        str = StringTools.replace(str, "", "");
                        str = StringTools.replace(str, ",", "");
                        NumberFormat fmt = NumberFormat.getInstance();
                        Number num = fmt.parse(str);
                        cell.setDoubleValue(num.doubleValue());
                        cell.setValue(cell.getDoubleValue());
                        logger.debug("?:" + num.doubleValue());
                    }
                } else if ("Boolean".equals(javaType)) {
                    Boolean value = rs.getBoolean(columnName);
                    cell.setBooleanValue(value);
                    cell.setValue(value);
                } else if ("Date".equals(javaType)) {
                    Date value = rs.getTimestamp(columnName);
                    cell.setDateValue(value);
                    cell.setValue(value);
                } else {
                    String value = rs.getString(columnName);
                    cell.setStringValue(value);
                    cell.setValue(value);
                }
                rowModel.addColumn(cell);
                if (resultList.isEmpty()) {
                    singleDataMap.put(column.getColumnLabel(), cell.getValue());
                }
            }
            resultList.add(rowModel);
        }

        logger.debug("--------------------resultList size:" + resultList.size());

    } catch (Exception ex) {
        success = false;
        ex.printStackTrace();
        logger.error(ex);
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(psmt);
        JdbcUtils.close(conn);
        if (!success) {
            if (task != null) {
                task.setStatus(2);
                transformTaskService.save(task);
            }
        }
    }

    logger.debug("--------------execute mybatis save----------------------");

    try {

        if (!StringUtils.equalsIgnoreCase(queryDefinition.getRotatingFlag(), "R2C")) {
            TransformTable tbl = new TransformTable();
            tbl.createOrAlterTable(tableDefinition);
        }

        List<ColumnDefinition> columns = DBUtils.getColumnDefinitions(tableDefinition.getTableName());
        if (columns != null && !columns.isEmpty()) {
            tableDefinition.setColumns(columns);
        }

        if (resultList != null && !resultList.isEmpty() && tableDefinition.getTableName() != null
                && tableDefinition.getAggregationKeys() != null) {
            logger.debug("RotatingFlag:" + queryDefinition.getRotatingFlag());
            logger.debug("RotatingColumn:" + queryDefinition.getRotatingColumn());
            /**
             * ????
             */
            if (StringUtils.equalsIgnoreCase(queryDefinition.getRotatingFlag(), "R2C")
                    && StringUtils.isNotEmpty(queryDefinition.getRotatingColumn()) && resultList.size() == 1) {

                logger.debug("?dataMap?:" + singleDataMap);
                logger.debug("AggregationKeys:" + tableDefinition.getAggregationKeys());
                ColumnDefinition idField = columnMap.get(tableDefinition.getAggregationKeys().toLowerCase());
                ColumnDefinition field = columnMap.get(queryDefinition.getRotatingColumn().toLowerCase());
                logger.debug("idField:" + idField);
                logger.debug("field:" + field);
                if (idField != null && field != null) {
                    String javaType = field.getJavaType();
                    List<TableModel> list = new ArrayList<TableModel>();
                    Set<Entry<String, Object>> entrySet = singleDataMap.entrySet();
                    for (Entry<String, Object> entry : entrySet) {
                        String key = entry.getKey();
                        Object value = entry.getValue();
                        if (key == null || value == null) {
                            continue;
                        }
                        TableModel tableModel = new TableModel();
                        tableModel.setTableName(queryDefinition.getTargetTableName());
                        ColumnModel cell = new ColumnModel();
                        cell.setColumnName(queryDefinition.getRotatingColumn());
                        cell.setType(javaType);

                        // logger.debug(cell.getColumnName()+"->"+javaType);

                        if ("String".equals(javaType)) {
                            cell.setStringValue(ParamUtils.getString(singleDataMap, key));
                            cell.setValue(cell.getStringValue());
                        } else if ("Integer".equals(javaType)) {
                            cell.setIntValue(ParamUtils.getInt(singleDataMap, key));
                            cell.setValue(cell.getIntValue());
                        } else if ("Long".equals(javaType)) {
                            cell.setLongValue(ParamUtils.getLong(singleDataMap, key));
                            cell.setValue(cell.getLongValue());
                        } else if ("Double".equals(javaType)) {
                            cell.setDoubleValue(ParamUtils.getDouble(singleDataMap, key));
                            cell.setValue(cell.getDoubleValue());
                        } else if ("Date".equals(javaType)) {
                            cell.setDateValue(ParamUtils.getDate(singleDataMap, key));
                            cell.setValue(cell.getDateValue());
                        } else {
                            cell.setValue(value);
                        }

                        tableModel.addColumn(cell);

                        ColumnModel idColumn = new ColumnModel();
                        idColumn.setColumnName(tableDefinition.getAggregationKeys());
                        idColumn.setJavaType(idField.getJavaType());
                        idColumn.setValue(key);
                        tableModel.setIdColumn(idColumn);
                        list.add(tableModel);
                    }
                    logger.debug("update datalist:" + list);
                    tableDataService.updateTableData(list);
                }
            } else {
                tableDataService.saveAll(tableDefinition, null, resultList);
            }
        }

        resultList.clear();
        resultList = null;

        long time = System.currentTimeMillis() - start;

        if (task != null) {
            task.setEndTime(new java.util.Date());
            task.setStatus(9);
            task.setDuration(time);
        }
        logger.debug("execute time(ms)--------------------------" + time);
    } catch (Exception ex) {
        if (task != null) {
            task.setStatus(2);
        }
        ex.printStackTrace();
        logger.error(ex);
        throw new RuntimeException(ex);
    } finally {
        if (task != null) {
            transformTaskService.save(task);
            if (task.getStatus() != 9) {
                this.run();
            }
        }
    }
}