List of usage examples for java.sql ResultSetMetaData getPrecision
int getPrecision(int column) throws SQLException;
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/* ww w . ja va 2s .com*/ .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 w w . j ava 2 s .co m 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(); } } } }
From source file:com.p5solutions.core.jpa.orm.EntityUtility.java
/** * Build the database-meta-dta for a given table entity, using an existing * connection./*from w w w. j a v a 2s.c o m*/ * * @param table * annotation * @param detail * {@link EntityDetail} probably provided by the * {@link #cacheEntityDetails} * @param connection * an existing mock or real, database connection. */ protected void buildColumnMetaData(Table table, EntityDetail<?> detail, Connection connection) { Statement stmt = null; ResultSet rs = null; try { String sql = "SELECT * FROM " + table.name() + " WHERE 1=0"; stmt = connection.createStatement(); // set the maximum result set to zero, just in-case!? stmt.setMaxRows(0); rs = stmt.executeQuery(sql); ResultSetMetaData rsMeta = rs.getMetaData(); logger.info("** Building Database MetaData for Table " + table.name()); for (int ic = 1; ic <= rsMeta.getColumnCount(); ic++) { String columnName = rsMeta.getColumnName(ic); ParameterBinder binder = detail.getParameterBinderByAny(columnName); if (binder == null) { if (logger.isErrorEnabled()) { String error = " -- Column " + columnName + " as defined by the table meta-data, cannot be found within the scope of " + detail.getEntityClass(); logger.error(error); } // TODO ?? throw new RuntimeException(new // NoColumnDefinedException(error)); } else { ParameterBinderColumnMetaData columnMetaData = new ParameterBinderColumnMetaData(); // columnMetaData.setColumnIndex(ic); // USELESS, EVERY UNIQUE QUERY // STRING WOULD RESULT IN A DIFFERENT INDEX. EASIER TO CACHE IT BASED // ON UNIQUE QUERY STRINGS. // columnMetaData.setColumnLabel(rsMeta.getColumnLabel(ic)); columnMetaData.setColumnName(columnName); columnMetaData.setLength(rsMeta.getColumnDisplaySize(ic)); columnMetaData.setPrecision(rsMeta.getPrecision(ic)); columnMetaData.setScale(rsMeta.getScale(ic)); columnMetaData.setColumnType(rsMeta.getColumnType(ic)); columnMetaData.setColumnTypeName(rsMeta.getColumnTypeName(ic)); binder.setColumnMetaData(columnMetaData); if (logger.isDebugEnabled()) { logger.debug(" -- [" + columnMetaData.toString() + "]"); } } } } catch (SQLException e) { logger.error(">> *UNABLE* to retrieve meta data for table " + table.name() + ", doesn't exist?"); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { ; } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { ; } stmt = null; } } }
From source file:com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.java
private Map<String, Integer> getTableSchema(SchemaAndTable schemaAndTable) throws SQLException { Map<String, Integer> columns = new HashMap<>(); String query = "SELECT * FROM \"" + schemaAndTable.getSchema() + "\".\"" + schemaAndTable.getTable() + "\" WHERE 1 = 0"; try (Statement schemaStatement = connection.createStatement(); ResultSet rs = schemaStatement.executeQuery(query)) { ResultSetMetaData md = rs.getMetaData(); int colCount = md.getColumnCount(); for (int i = 1; i <= colCount; i++) { int colType = md.getColumnType(i); String colName = md.getColumnName(i); if (!configBean.baseConfigBean.caseSensitive) { colName = colName.toUpperCase(); }//from w w w . j av a2 s .c o m if (colType == Types.DATE || colType == Types.TIME || colType == Types.TIMESTAMP) { dateTimeColumns.computeIfAbsent(schemaAndTable, k -> new HashMap<>()); dateTimeColumns.get(schemaAndTable).put(colName, md.getColumnTypeName(i)); } if (colType == Types.DECIMAL || colType == Types.NUMERIC) { decimalColumns.computeIfAbsent(schemaAndTable, k -> new HashMap<>()); decimalColumns.get(schemaAndTable).put(colName, new PrecisionAndScale(md.getPrecision(i), md.getScale(i))); } columns.put(md.getColumnName(i), md.getColumnType(i)); } } return columns; }
From source file:com.glaf.core.jdbc.QueryHelper.java
/** * @param conn//from www .ja v a 2 s . co m * ? * @param sqlExecutor * ? * @param start * 0 * @param pageSize * ? * @return */ @SuppressWarnings("unchecked") public List<Map<String, Object>> getResultList(Connection conn, SqlExecutor sqlExecutor, int start, int pageSize) { if (!DBUtils.isLegalQuerySql(sqlExecutor.getSql())) { throw new RuntimeException(" SQL statement illegal "); } List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>(); String sql = sqlExecutor.getSql(); PreparedStatement psmt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; boolean supportsPhysicalPage = false; try { Dialect dialect = DBConfiguration.getDatabaseDialect(conn); if (dialect != null && dialect.supportsPhysicalPage()) { supportsPhysicalPage = true; sql = dialect.getLimitString(sql, start, pageSize); logger.debug("sql=" + sqlExecutor.getSql()); logger.debug(">>sql=" + sql); } psmt = conn.prepareStatement(sql); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); logger.debug(">>values=" + 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 i = 1; i <= count; i++) { int sqlType = rsmd.getColumnType(i); ColumnDefinition column = new ColumnDefinition(); column.setIndex(i); 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)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase())); columns.add(column); } if (!supportsPhysicalPage) { logger.debug("---------------------skipRows:" + start); this.skipRows(rs, start, pageSize); } logger.debug("---------------------columns:" + columns.size()); logger.debug("---------------------start:" + start); logger.debug("---------------------pageSize:" + pageSize); // int index = 0; while (rs.next()) { // index++; // logger.debug("---------------------row index:" + index); 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(); if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); if (value != null) { value = value.trim(); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } 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, rowMap.get(columnName)); logger.debug("?:" + num.longValue()); } } else if ("Double".equals(javaType)) { try { Double d = rs.getDouble(column.getIndex()); rowMap.put(columnName, d); rowMap.put(columnLabel, rowMap.get(columnName)); } 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, rowMap.get(columnName)); 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)) { } 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)); } } } 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:com.glaf.core.jdbc.QueryHelper.java
/** * @param conn//www . j av a 2s . c om * ? * @param start * 0 * @param pageSize * ? * @param sql * ? * @param paramMap * ? * @return */ @SuppressWarnings("unchecked") public ResultModel getResultList(Connection conn, String sql, Map<String, Object> paramMap, int start, int pageSize) { if (!DBUtils.isLegalQuerySql(sql)) { throw new RuntimeException(" SQL statement illegal "); } ResultModel resultModel = new ResultModel(); boolean supportsPhysicalPage = false; PreparedStatement psmt = null; ResultSetMetaData rsmd = null; ResultSet rs = null; Dialect dialect = null; try { dialect = DBConfiguration.getDatabaseDialect(conn); if (dialect != null && dialect.supportsPhysicalPage()) { logger.debug("sql=" + sql); supportsPhysicalPage = dialect.supportsPhysicalPage(); sql = dialect.getLimitString(sql, start, pageSize); logger.debug(">>sql=" + sql); } 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); } List<ColumnDefinition> columns = new 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.setIndex(i); 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)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.lower(StringTools.camelStyle(column.getColumnLabel()))); columns.add(column); } resultModel.setHeaders(columns); if (!supportsPhysicalPage) { this.skipRows(rs, start); } int k = 0; while (rs.next() && k++ < pageSize) { int index = 0; RowModel rowModel = new RowModel(); Iterator<ColumnDefinition> iterator = columns.iterator(); while (iterator.hasNext()) { ColumnDefinition column = iterator.next(); ColumnDefinition c = new ColumnDefinition(); c.setColumnName(column.getColumnName()); c.setColumnLabel(column.getColumnLabel()); c.setName(column.getName()); c.setJavaType(column.getJavaType()); c.setPrecision(column.getPrecision()); c.setScale(column.getScale()); String javaType = column.getJavaType(); index = index + 1; if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); c.setValue(value); } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.intValue()); } } else if ("Long".equals(javaType)) { try { Long value = rs.getLong(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.longValue()); } } else if ("Double".equals(javaType)) { try { Double value = rs.getDouble(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.doubleValue()); } } else if ("Boolean".equals(javaType)) { Boolean value = rs.getBoolean(column.getIndex()); c.setValue(value); } else if ("Date".equals(javaType)) { Timestamp value = rs.getTimestamp(column.getIndex()); c.setValue(value); } else { c.setValue(rs.getObject(column.getIndex())); } rowModel.addColumn(c); } resultModel.addRow(rowModel); } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } return resultModel; }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test 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, c21, c22, c23 from " + dataTypeTableName + " limit 1"); ResultSetMetaData meta = res.getMetaData(); ResultSet colRS = con.getMetaData().getColumns(null, null, dataTypeTableName.toLowerCase(), null); assertEquals(20, meta.getColumnCount()); assertTrue(colRS.next());/*www.j av 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.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.ARRAY, meta.getColumnType(5)); assertEquals("array", 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.ARRAY, colRS.getInt("DATA_TYPE")); assertEquals("array<int>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c6", meta.getColumnName(6)); assertEquals(Types.JAVA_OBJECT, meta.getColumnType(6)); assertEquals("map", 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.JAVA_OBJECT, colRS.getInt("DATA_TYPE")); assertEquals("map<int,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c7", meta.getColumnName(7)); assertEquals(Types.JAVA_OBJECT, meta.getColumnType(7)); assertEquals("map", 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.JAVA_OBJECT, colRS.getInt("DATA_TYPE")); assertEquals("map<string,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c8", meta.getColumnName(8)); assertEquals(Types.STRUCT, meta.getColumnType(8)); assertEquals("struct", 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.STRUCT, colRS.getInt("DATA_TYPE")); assertEquals("struct<r:string,s:int,t:double>", colRS.getString("TYPE_NAME").toLowerCase()); 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.ARRAY, meta.getColumnType(14)); assertEquals("array", meta.getColumnTypeName(14)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(14)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(14)); assertEquals(0, meta.getScale(14)); // Move the result of getColumns() forward to match the columns of the query assertTrue(colRS.next()); // c13 assertTrue(colRS.next()); // c14 assertTrue(colRS.next()); // c15 assertTrue(colRS.next()); // c16 assertTrue(colRS.next()); // c17 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("c17", colRS.getString("COLUMN_NAME")); assertEquals(Types.TIMESTAMP, colRS.getInt("DATA_TYPE")); assertEquals("timestamp", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(15), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(15), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c18", meta.getColumnName(16)); assertEquals(Types.DECIMAL, meta.getColumnType(16)); assertEquals("decimal", meta.getColumnTypeName(16)); assertEquals(18, meta.getColumnDisplaySize(16)); assertEquals(16, meta.getPrecision(16)); assertEquals(7, meta.getScale(16)); assertEquals("c18", colRS.getString("COLUMN_NAME")); assertEquals(Types.DECIMAL, colRS.getInt("DATA_TYPE")); assertEquals("decimal", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(16), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(16), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); // skip c19, since not selected by query assertTrue(colRS.next()); 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)); assertEquals("c20", colRS.getString("COLUMN_NAME")); assertEquals(Types.DATE, colRS.getInt("DATA_TYPE")); assertEquals("date", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(17), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(17), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c21", meta.getColumnName(18)); assertEquals(Types.VARCHAR, meta.getColumnType(18)); assertEquals("varchar", meta.getColumnTypeName(18)); // varchar columns should have correct display size/precision assertEquals(20, meta.getColumnDisplaySize(18)); assertEquals(20, meta.getPrecision(18)); assertEquals(0, meta.getScale(18)); assertEquals("c21", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("varchar", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(18), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(18), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c22", meta.getColumnName(19)); assertEquals(Types.CHAR, meta.getColumnType(19)); assertEquals("char", meta.getColumnTypeName(19)); // char columns should have correct display size/precision assertEquals(15, meta.getColumnDisplaySize(19)); assertEquals(15, meta.getPrecision(19)); assertEquals(0, meta.getScale(19)); assertEquals("c23", meta.getColumnName(20)); assertEquals(Types.BINARY, meta.getColumnType(20)); assertEquals("binary", meta.getColumnTypeName(20)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(20)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(20)); assertEquals(0, meta.getScale(20)); assertEquals("c22", colRS.getString("COLUMN_NAME")); assertEquals(Types.CHAR, colRS.getInt("DATA_TYPE")); assertEquals("char", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(19), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(19), colRS.getInt("DECIMAL_DIGITS")); 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:be.dataminded.nifi.plugins.util.JdbcCommon.java
public static long convertToAvroStream(final ResultSet rs, final OutputStream outStream, String recordName, ResultSetRowCallback callback, final int maxRows, boolean convertNames) throws SQLException, IOException { final Schema schema = createSchema(rs, recordName, convertNames); final GenericRecord rec = new GenericData.Record(schema); final DatumWriter<GenericRecord> datumWriter = new GenericDatumWriter<>(schema); try (final DataFileWriter<GenericRecord> dataFileWriter = new DataFileWriter<>(datumWriter)) { dataFileWriter.create(schema, outStream); final ResultSetMetaData meta = rs.getMetaData(); final int nrOfColumns = meta.getColumnCount(); long nrOfRows = 0; while (rs.next()) { if (callback != null) { callback.processRow(rs); }/* w w w. j a va2 s . co m*/ for (int i = 1; i <= nrOfColumns; i++) { final int javaSqlType = meta.getColumnType(i); // Need to handle CLOB and BLOB before getObject() is called, due to ResultSet's maximum portability statement if (javaSqlType == CLOB) { Clob clob = rs.getClob(i); if (clob != null) { long numChars = clob.length(); char[] buffer = new char[(int) numChars]; InputStream is = clob.getAsciiStream(); int index = 0; int c = is.read(); while (c > 0) { buffer[index++] = (char) c; c = is.read(); } rec.put(i - 1, new String(buffer)); clob.free(); } else { rec.put(i - 1, null); } continue; } if (javaSqlType == BLOB) { Blob blob = rs.getBlob(i); if (blob != null) { long numChars = blob.length(); byte[] buffer = new byte[(int) numChars]; InputStream is = blob.getBinaryStream(); int index = 0; int c = is.read(); while (c > 0) { buffer[index++] = (byte) c; c = is.read(); } ByteBuffer bb = ByteBuffer.wrap(buffer); rec.put(i - 1, bb); blob.free(); } else { rec.put(i - 1, null); } continue; } final Object value = rs.getObject(i); if (value == null) { rec.put(i - 1, null); } else if (javaSqlType == BINARY || javaSqlType == VARBINARY || javaSqlType == LONGVARBINARY || javaSqlType == ARRAY) { // bytes requires little bit different handling byte[] bytes = rs.getBytes(i); ByteBuffer bb = ByteBuffer.wrap(bytes); rec.put(i - 1, bb); } else if (value instanceof Byte) { // tinyint(1) type is returned by JDBC driver as java.sql.Types.TINYINT // But value is returned by JDBC as java.lang.Byte // (at least H2 JDBC works this way) // direct put to avro record results: // org.apache.avro.AvroRuntimeException: Unknown datum type java.lang.Byte rec.put(i - 1, ((Byte) value).intValue()); } else if (value instanceof Short) { //MS SQL returns TINYINT as a Java Short, which Avro doesn't understand. rec.put(i - 1, ((Short) value).intValue()); } else if (value instanceof BigDecimal) { // Avro can't handle BigDecimal as a number - it will throw an AvroRuntimeException such as: "Unknown datum type: java.math.BigDecimal: 38" try { int scale = meta.getScale(i); BigDecimal bigDecimal = ((BigDecimal) value); if (scale == 0) { if (meta.getPrecision(i) < 10) { rec.put(i - 1, bigDecimal.intValue()); } else { rec.put(i - 1, bigDecimal.longValue()); } } else { rec.put(i - 1, bigDecimal.doubleValue()); } } catch (Exception e) { rec.put(i - 1, value.toString()); } } else if (value instanceof BigInteger) { // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that. // It the SQL type is BIGINT and the precision is between 0 and 19 (inclusive); if so, the BigInteger is likely a // long (and the schema says it will be), so try to get its value as a long. // Otherwise, Avro can't handle BigInteger as a number - it will throw an AvroRuntimeException // such as: "Unknown datum type: java.math.BigInteger: 38". In this case the schema is expecting a string. if (javaSqlType == BIGINT) { int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { rec.put(i - 1, value.toString()); } else { try { rec.put(i - 1, ((BigInteger) value).longValueExact()); } catch (ArithmeticException ae) { // Since the value won't fit in a long, convert it to a string rec.put(i - 1, value.toString()); } } } else { rec.put(i - 1, value.toString()); } } else if (value instanceof Number || value instanceof Boolean) { if (javaSqlType == BIGINT) { int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { rec.put(i - 1, value.toString()); } else { rec.put(i - 1, value); } } else { rec.put(i - 1, value); } } else { // The different types that we support are numbers (int, long, double, float), // as well as boolean values and Strings. Since Avro doesn't provide // timestamp types, we want to convert those to Strings. So we will cast anything other // than numbers or booleans to strings by using the toString() method. rec.put(i - 1, value.toString()); } } dataFileWriter.append(rec); nrOfRows += 1; if (maxRows > 0 && nrOfRows == maxRows) break; } return nrOfRows; } }
From source file:org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.ResultSetTableModelFactory.java
public static AttributeMap<Object> collectData(final ResultSetMetaData rsmd, final int column, final String name) throws SQLException { AttributeMap<Object> metaData = new AttributeMap<Object>(); metaData.setAttribute(MetaAttributeNames.Core.NAMESPACE, MetaAttributeNames.Core.TYPE, TypeMapper.mapForColumn(rsmd, column)); metaData.setAttribute(MetaAttributeNames.Core.NAMESPACE, MetaAttributeNames.Core.NAME, name); try {/* ww w . ja v a2s . co m*/ if (rsmd.isCurrency(column + 1)) { metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.CURRENCY, Boolean.TRUE); } else { metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.CURRENCY, Boolean.FALSE); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#isCurrency. Driver does not implement the JDBC specs correctly. ", e); } try { if (rsmd.isSigned(column + 1)) { metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SIGNED, Boolean.TRUE); } else { metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SIGNED, Boolean.FALSE); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#isSigned. Driver does not implement the JDBC specs correctly. ", e); } try { final String tableName = rsmd.getTableName(column + 1); if (tableName != null) { metaData.setAttribute(MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.TABLE, tableName); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } try { final String schemaName = rsmd.getSchemaName(column + 1); if (schemaName != null) { metaData.setAttribute(MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.SCHEMA, schemaName); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getSchemaName. Driver does not implement the JDBC specs correctly. ", e); } try { final String catalogName = rsmd.getCatalogName(column + 1); if (catalogName != null) { metaData.setAttribute(MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.CATALOG, catalogName); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } try { final String label = rsmd.getColumnLabel(column + 1); if (label != null) { metaData.setAttribute(MetaAttributeNames.Formatting.NAMESPACE, MetaAttributeNames.Formatting.LABEL, label); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } try { final int displaySize = rsmd.getColumnDisplaySize(column + 1); metaData.setAttribute(MetaAttributeNames.Formatting.NAMESPACE, MetaAttributeNames.Formatting.DISPLAY_SIZE, IntegerCache.getInteger(displaySize)); } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } try { final int precision = rsmd.getPrecision(column + 1); metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.PRECISION, IntegerCache.getInteger(precision)); } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } try { final int scale = rsmd.getScale(column + 1); metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SCALE, IntegerCache.getInteger(scale)); } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } return metaData; }
From source file:org.apache.nifi.processors.standard.util.JdbcCommon.java
public static long convertToAvroStream(final ResultSet rs, final OutputStream outStream, String recordName, ResultSetRowCallback callback, final int maxRows, boolean convertNames) throws SQLException, IOException { final Schema schema = createSchema(rs, recordName, convertNames); final GenericRecord rec = new GenericData.Record(schema); final DatumWriter<GenericRecord> datumWriter = new GenericDatumWriter<>(schema); try (final DataFileWriter<GenericRecord> dataFileWriter = new DataFileWriter<>(datumWriter)) { dataFileWriter.create(schema, outStream); final ResultSetMetaData meta = rs.getMetaData(); final int nrOfColumns = meta.getColumnCount(); long nrOfRows = 0; while (rs.next()) { if (callback != null) { callback.processRow(rs); }/*from w w w . j a v a 2 s . c o m*/ for (int i = 1; i <= nrOfColumns; i++) { final int javaSqlType = meta.getColumnType(i); // Need to handle CLOB and BLOB before getObject() is called, due to ResultSet's maximum portability statement if (javaSqlType == CLOB) { Clob clob = rs.getClob(i); if (clob != null) { long numChars = clob.length(); char[] buffer = new char[(int) numChars]; InputStream is = clob.getAsciiStream(); int index = 0; int c = is.read(); while (c > 0) { buffer[index++] = (char) c; c = is.read(); } rec.put(i - 1, new String(buffer)); clob.free(); } else { rec.put(i - 1, null); } continue; } if (javaSqlType == BLOB) { Blob blob = rs.getBlob(i); if (blob != null) { long numChars = blob.length(); byte[] buffer = new byte[(int) numChars]; InputStream is = blob.getBinaryStream(); int index = 0; int c = is.read(); while (c > 0) { buffer[index++] = (byte) c; c = is.read(); } ByteBuffer bb = ByteBuffer.wrap(buffer); rec.put(i - 1, bb); blob.free(); } else { rec.put(i - 1, null); } continue; } final Object value = rs.getObject(i); if (value == null) { rec.put(i - 1, null); } else if (javaSqlType == BINARY || javaSqlType == VARBINARY || javaSqlType == LONGVARBINARY || javaSqlType == ARRAY) { // bytes requires little bit different handling byte[] bytes = rs.getBytes(i); ByteBuffer bb = ByteBuffer.wrap(bytes); rec.put(i - 1, bb); } else if (value instanceof Byte) { // tinyint(1) type is returned by JDBC driver as java.sql.Types.TINYINT // But value is returned by JDBC as java.lang.Byte // (at least H2 JDBC works this way) // direct put to avro record results: // org.apache.avro.AvroRuntimeException: Unknown datum type java.lang.Byte rec.put(i - 1, ((Byte) value).intValue()); } else if (value instanceof Short) { //MS SQL returns TINYINT as a Java Short, which Avro doesn't understand. rec.put(i - 1, ((Short) value).intValue()); } else if (value instanceof BigDecimal) { // Avro can't handle BigDecimal as a number - it will throw an AvroRuntimeException such as: "Unknown datum type: java.math.BigDecimal: 38" rec.put(i - 1, value.toString()); } else if (value instanceof BigInteger) { // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that. // It the SQL type is BIGINT and the precision is between 0 and 19 (inclusive); if so, the BigInteger is likely a // long (and the schema says it will be), so try to get its value as a long. // Otherwise, Avro can't handle BigInteger as a number - it will throw an AvroRuntimeException // such as: "Unknown datum type: java.math.BigInteger: 38". In this case the schema is expecting a string. if (javaSqlType == BIGINT) { int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { rec.put(i - 1, value.toString()); } else { try { rec.put(i - 1, ((BigInteger) value).longValueExact()); } catch (ArithmeticException ae) { // Since the value won't fit in a long, convert it to a string rec.put(i - 1, value.toString()); } } } else { rec.put(i - 1, value.toString()); } } else if (value instanceof Number || value instanceof Boolean) { if (javaSqlType == BIGINT) { int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { rec.put(i - 1, value.toString()); } else { rec.put(i - 1, value); } } else { rec.put(i - 1, value); } } else { // The different types that we support are numbers (int, long, double, float), // as well as boolean values and Strings. Since Avro doesn't provide // timestamp types, we want to convert those to Strings. So we will cast anything other // than numbers or booleans to strings by using the toString() method. rec.put(i - 1, value.toString()); } } dataFileWriter.append(rec); nrOfRows += 1; if (maxRows > 0 && nrOfRows == maxRows) break; } return nrOfRows; } }