List of usage examples for java.sql ResultSetMetaData getColumnType
int getColumnType(int column) throws SQLException;
From source file:org.apache.nifi.processors.standard.GenerateTableFetch.java
@Override public void onTrigger(final ProcessContext context, final ProcessSessionFactory sessionFactory) throws ProcessException { ProcessSession session = sessionFactory.createSession(); FlowFile fileToProcess = null;/* ww w. j a v a2 s.c om*/ if (context.hasIncomingConnection()) { fileToProcess = session.get(); if (fileToProcess == null) { // Incoming connection with no flow file available, do no work (see capability description) return; } } final ComponentLog logger = getLogger(); final DBCPService dbcpService = context.getProperty(DBCP_SERVICE).asControllerService(DBCPService.class); final DatabaseAdapter dbAdapter = dbAdapters.get(context.getProperty(DB_TYPE).getValue()); final String tableName = context.getProperty(TABLE_NAME).evaluateAttributeExpressions(fileToProcess) .getValue(); final String columnNames = context.getProperty(COLUMN_NAMES).evaluateAttributeExpressions(fileToProcess) .getValue(); final String maxValueColumnNames = context.getProperty(MAX_VALUE_COLUMN_NAMES) .evaluateAttributeExpressions(fileToProcess).getValue(); final int partitionSize = context.getProperty(PARTITION_SIZE).evaluateAttributeExpressions(fileToProcess) .asInteger(); final StateManager stateManager = context.getStateManager(); final StateMap stateMap; try { stateMap = stateManager.getState(Scope.CLUSTER); } catch (final IOException ioe) { logger.error("Failed to retrieve observed maximum values from the State Manager. Will not perform " + "query until this is accomplished.", ioe); context.yield(); return; } try { // Make a mutable copy of the current state property map. This will be updated by the result row callback, and eventually // set as the current state map (after the session has been committed) final Map<String, String> statePropertyMap = new HashMap<>(stateMap.toMap()); // Build a WHERE clause with maximum-value columns (if they exist), and a list of column names that will contain MAX(<column>) aliases. The // executed SQL query will retrieve the count of all records after the filter(s) have been applied, as well as the new maximum values for the // specified columns. This allows the processor to generate the correctly partitioned SQL statements as well as to update the state with the // latest observed maximum values. String whereClause = null; List<String> maxValueColumnNameList = StringUtils.isEmpty(maxValueColumnNames) ? new ArrayList<>(0) : Arrays.asList(maxValueColumnNames.split("\\s*,\\s*")); List<String> maxValueClauses = new ArrayList<>(maxValueColumnNameList.size()); String columnsClause = null; List<String> maxValueSelectColumns = new ArrayList<>(maxValueColumnNameList.size() + 1); maxValueSelectColumns.add("COUNT(*)"); // For each maximum-value column, get a WHERE filter and a MAX(column) alias IntStream.range(0, maxValueColumnNameList.size()).forEach((index) -> { String colName = maxValueColumnNameList.get(index); maxValueSelectColumns.add("MAX(" + colName + ") " + colName); final String fullyQualifiedStateKey = getStateKey(tableName, colName); String maxValue = statePropertyMap.get(fullyQualifiedStateKey); if (StringUtils.isEmpty(maxValue) && !isDynamicTableName) { // If the table name is static and the fully-qualified key was not found, try just the column name maxValue = statePropertyMap.get(getStateKey(null, colName)); } if (!StringUtils.isEmpty(maxValue)) { Integer type = columnTypeMap.get(fullyQualifiedStateKey); if (type == null && !isDynamicTableName) { // If the table name is static and the fully-qualified key was not found, try just the column name type = columnTypeMap.get(getStateKey(null, colName)); } if (type == null) { // This shouldn't happen as we are populating columnTypeMap when the processor is scheduled or when the first maximum is observed throw new IllegalArgumentException("No column type found for: " + colName); } // Add a condition for the WHERE clause maxValueClauses.add(colName + (index == 0 ? " > " : " >= ") + getLiteralByType(type, maxValue, dbAdapter.getName())); } }); whereClause = StringUtils.join(maxValueClauses, " AND "); columnsClause = StringUtils.join(maxValueSelectColumns, ", "); // Build a SELECT query with maximum-value columns (if present) final String selectQuery = dbAdapter.getSelectStatement(tableName, columnsClause, whereClause, null, null, null); long rowCount = 0; try (final Connection con = dbcpService.getConnection(); final Statement st = con.createStatement()) { final Integer queryTimeout = context.getProperty(QUERY_TIMEOUT) .evaluateAttributeExpressions(fileToProcess).asTimePeriod(TimeUnit.SECONDS).intValue(); st.setQueryTimeout(queryTimeout); // timeout in seconds logger.debug("Executing {}", new Object[] { selectQuery }); ResultSet resultSet; resultSet = st.executeQuery(selectQuery); if (resultSet.next()) { // Total row count is in the first column rowCount = resultSet.getLong(1); // Update the state map with the newly-observed maximum values ResultSetMetaData rsmd = resultSet.getMetaData(); for (int i = 2; i <= rsmd.getColumnCount(); i++) { //Some JDBC drivers consider the columns name and label to be very different things. // Since this column has been aliased lets check the label first, // if there is no label we'll use the column name. String resultColumnName = (StringUtils.isNotEmpty(rsmd.getColumnLabel(i)) ? rsmd.getColumnLabel(i) : rsmd.getColumnName(i)).toLowerCase(); String fullyQualifiedStateKey = getStateKey(tableName, resultColumnName); String resultColumnCurrentMax = statePropertyMap.get(fullyQualifiedStateKey); if (StringUtils.isEmpty(resultColumnCurrentMax) && !isDynamicTableName) { // If we can't find the value at the fully-qualified key name and the table name is static, it is possible (under a previous scheme) // the value has been stored under a key that is only the column name. Fall back to check the column name; either way, when a new // maximum value is observed, it will be stored under the fully-qualified key from then on. resultColumnCurrentMax = statePropertyMap.get(resultColumnName); } int type = rsmd.getColumnType(i); if (isDynamicTableName) { // We haven't pre-populated the column type map if the table name is dynamic, so do it here columnTypeMap.put(fullyQualifiedStateKey, type); } try { String newMaxValue = getMaxValueFromRow(resultSet, i, type, resultColumnCurrentMax, dbAdapter.getName()); if (newMaxValue != null) { statePropertyMap.put(fullyQualifiedStateKey, newMaxValue); } } catch (ParseException | IOException pie) { // Fail the whole thing here before we start creating flow files and such throw new ProcessException(pie); } } } else { // Something is very wrong here, one row (even if count is zero) should be returned throw new SQLException("No rows returned from metadata query: " + selectQuery); } final long numberOfFetches = (partitionSize == 0) ? rowCount : (rowCount / partitionSize) + (rowCount % partitionSize == 0 ? 0 : 1); // Generate SQL statements to read "pages" of data for (long i = 0; i < numberOfFetches; i++) { long limit = partitionSize == 0 ? null : partitionSize; long offset = partitionSize == 0 ? null : i * partitionSize; final String query = dbAdapter.getSelectStatement(tableName, columnNames, whereClause, StringUtils.join(maxValueColumnNameList, ", "), limit, offset); FlowFile sqlFlowFile = (fileToProcess == null) ? session.create() : session.create(fileToProcess); sqlFlowFile = session.write(sqlFlowFile, out -> out.write(query.getBytes())); session.transfer(sqlFlowFile, REL_SUCCESS); } if (fileToProcess != null) { session.remove(fileToProcess); } } catch (SQLException e) { if (fileToProcess != null) { logger.error("Unable to execute SQL select query {} due to {}, routing {} to failure", new Object[] { selectQuery, e, fileToProcess }); fileToProcess = session.putAttribute(fileToProcess, "generatetablefetch.sql.error", e.getMessage()); session.transfer(fileToProcess, REL_FAILURE); } else { logger.error("Unable to execute SQL select query {} due to {}", new Object[] { selectQuery, e }); throw new ProcessException(e); } } session.commit(); try { // Update the state stateManager.setState(statePropertyMap, Scope.CLUSTER); } catch (IOException ioe) { logger.error( "{} failed to update State Manager, observed maximum values will not be recorded. " + "Also, any generated SQL statements may be duplicated.", new Object[] { this, ioe }); } } catch (final ProcessException pe) { // Log the cause of the ProcessException if it is available Throwable t = (pe.getCause() == null ? pe : pe.getCause()); logger.error("Error during processing: {}", new Object[] { t.getMessage() }, t); session.rollback(); context.yield(); } }
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. ja va2s . 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
@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 ww w . jav a2 s. com 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.feedzai.commons.sql.abstraction.engine.AbstractDatabaseEngine.java
/** * Gets the query metadata.//from w ww . j a v a 2s. co m * * @param query The query to retrieve the metadata from. * @return A representation of the table columns and types. * @throws DatabaseEngineException If something occurs getting the metadata. */ @Override public Map<String, DbColumnType> getQueryMetadata(String query) throws DatabaseEngineException { final Map<String, DbColumnType> metaMap = new LinkedHashMap<String, DbColumnType>(); ResultSet rs = null; Statement stmt = null; try { getConnection(); stmt = conn.createStatement(); long start = System.currentTimeMillis(); rs = stmt.executeQuery(query); logger.trace("[{} ms] {}", (System.currentTimeMillis() - start), query); ResultSetMetaData meta = rs.getMetaData(); for (int i = 1; i <= meta.getColumnCount(); i++) { metaMap.put(meta.getColumnName(i), toPdbType(meta.getColumnType(i))); } stmt.close(); return metaMap; } catch (final Exception e) { throw new DatabaseEngineException("Error querying", e); } finally { try { if (rs != null) { rs.close(); } } catch (Exception e) { logger.trace("Error closing result set.", e); } try { if (stmt != null) { stmt.close(); } } catch (Exception e) { logger.trace("Error closing statement.", e); } } }
From source file:com.glaf.core.jdbc.QueryHelper.java
/** * @param conn// w w w. ja v a 2 s . c om * ? * @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.cloudgraph.rdb.filter.RDBStatementExecutor.java
@Override public List<PropertyPair> executeInsertWithGeneratedKeys(PlasmaType type, StringBuilder sql, Map<String, PropertyPair> values) { List<PropertyPair> resultKeys = new ArrayList<PropertyPair>(); PreparedStatement statement = null; List<InputStream> streams = null; ResultSet generatedKeys = null; try {//from w ww. ja va 2 s.co m if (log.isDebugEnabled()) { log.debug("execute: " + sql.toString()); StringBuilder paramBuf = createParamDebug(values); log.debug("params: " + paramBuf.toString()); } statement = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS); for (PropertyPair pair : values.values()) { PlasmaProperty valueProp = pair.getProp(); if (pair.getValueProp() != null) valueProp = pair.getValueProp(); int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue()); Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue()); if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) { statement.setObject(pair.getColumn(), jdbcValue, jdbcType); } else { byte[] bytes = (byte[]) jdbcValue; long len = bytes.length; ByteArrayInputStream is = new ByteArrayInputStream(bytes); statement.setBinaryStream(pair.getColumn(), is, len); if (streams == null) streams = new ArrayList<InputStream>(); streams.add(is); } } statement.execute(); generatedKeys = statement.getGeneratedKeys(); ResultSetMetaData rsMeta = generatedKeys.getMetaData(); int numcols = rsMeta.getColumnCount(); if (log.isDebugEnabled()) log.debug("returned " + numcols + " keys"); if (generatedKeys.next()) { // FIXME; without metadata describing which properties // are actually a sequence, there is guess work // involved in matching the values returned // automatically from PreparedStatment as they // are anonymous in terms of the column names // making it impossible to match them to a metadata // property. List<Property> pkPropList = type.findProperties(KeyType.primary); if (pkPropList == null || pkPropList.size() == 0) throw new DataAccessException("no pri-key properties found for type '" + type.getName() + "'"); if (pkPropList.size() > 1) throw new DataAccessException("multiple pri-key properties found for type '" + type.getName() + "' - cannot map to generated keys"); PlasmaProperty prop = (PlasmaProperty) pkPropList.get(0); // FIXME: need to find properties per column by physical name // alias // in case where multiple generated pri-keys for (int i = 1; i <= numcols; i++) { String columnName = rsMeta.getColumnName(i); if (log.isDebugEnabled()) log.debug("returned key column '" + columnName + "'"); int columnType = rsMeta.getColumnType(i); Object value = converter.fromJDBCDataType(generatedKeys, i, columnType, prop); PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value); resultKeys.add(pair); } } } catch (Throwable t) { throw new DataAccessException(t); } finally { try { if (statement != null) statement.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } if (streams != null) try { for (InputStream stream : streams) stream.close(); } catch (IOException e) { log.error(e.getMessage(), e); } } return resultKeys; }
From source file:org.apache.hadoop.chukwa.extraction.Consolidator.java
public void run() { ResultSet rs = null;//from www . j a v a 2s .c o m String[] columns; int[] columnsType; String groupBy = ""; for (int interval : intervals) { // Start reducing from beginning of time; Calendar aYearAgo = Calendar.getInstance(); aYearAgo.set(2008, 12, 30, 0, 0, 0); long start = aYearAgo.getTimeInMillis(); //starting from 2008/01/01 long end = start + (interval * 60000); log.debug("start time: " + start); log.debug("end time: " + end); Calendar now = Calendar.getInstance(); DatabaseWriter db = new DatabaseWriter(); String fields = null; String dateclause = null; boolean emptyPrimeKey = false; log.debug("Consolidate for " + interval + " minutes interval."); String table = this.table + "_" + interval; // Find the most recent entry try { String query = "select * from " + table + " order by timestamp desc limit 1"; log.debug("Query: " + query); rs = db.query(query); if (rs == null) { throw new SQLException("Table undefined."); } ResultSetMetaData rmeta = rs.getMetaData(); boolean empty = true; if (rs.next()) { for (int i = 1; i <= rmeta.getColumnCount(); i++) { if (rmeta.getColumnName(i).toLowerCase().equals("timestamp")) { start = rs.getTimestamp(i).getTime(); } } empty = false; } if (empty) { throw new SQLException("Table is empty."); } end = start + (interval * 60000); } catch (SQLException ex) { try { String query = "select * from " + this.table + " order by timestamp limit 1"; log.debug("Query: " + query); rs = db.query(query); if (rs.next()) { ResultSetMetaData rmeta = rs.getMetaData(); for (int i = 1; i <= rmeta.getColumnCount(); i++) { if (rmeta.getColumnName(i).toLowerCase().equals("timestamp")) { start = rs.getTimestamp(i).getTime(); } } } end = start + (interval * 60000); } catch (SQLException ex2) { log.error("Unable to determine starting point in table: " + this.table); log.error("SQL Error:" + ExceptionUtil.getStackTrace(ex2)); return; } } try { ResultSetMetaData rmeta = rs.getMetaData(); int col = rmeta.getColumnCount(); columns = new String[col]; columnsType = new int[col]; for (int i = 1; i <= col; i++) { columns[i - 1] = rmeta.getColumnName(i); columnsType[i - 1] = rmeta.getColumnType(i); } for (int i = 0; i < columns.length; i++) { if (i == 0) { fields = columns[i]; if (columnsType[i] == java.sql.Types.VARCHAR) { groupBy = " group by " + columns[i]; } } else { if (columnsType[i] == java.sql.Types.VARCHAR || columnsType[i] == java.sql.Types.TIMESTAMP) { fields = fields + "," + columns[i]; if (columnsType[i] == java.sql.Types.VARCHAR) { groupBy = " group by " + columns[i]; } } else { fields = fields + ",AVG(" + columns[i] + ") as " + columns[i]; } } } } catch (SQLException ex) { log.error("SQL Error:" + ExceptionUtil.getStackTrace(ex)); return; } if (groupBy.equals("")) { emptyPrimeKey = true; } long previousStart = start; while (end < now.getTimeInMillis() - (interval * 2 * 60000)) { // Select new data sample for the given intervals if (interval == 5) { table = this.table; } else if (interval == 30) { table = this.table + "_5"; } else if (interval == 120) { table = this.table + "_30"; } SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String startS = formatter.format(start); String endS = formatter.format(end); dateclause = "Timestamp >= '" + startS + "' and Timestamp <= '" + endS + "'"; if (emptyPrimeKey) { groupBy = "group by " + dateclause; } String query = "insert ignore into " + this.table + "_" + interval + " (select " + fields + " from " + table + " where " + dateclause + groupBy + ")"; log.debug(query); db.execute(query); db.close(); if (previousStart == start) { start = start + (interval * 60000); end = start + (interval * 60000); previousStart = start; } } } }
From source file:org.plasma.sdo.jdbc.service.JDBCSupport.java
protected List<PropertyPair> executeInsert(PlasmaType type, StringBuilder sql, Map<String, PropertyPair> values, Connection con) {// www . j ava 2 s .co m List<PropertyPair> resultKeys = new ArrayList<PropertyPair>(); PreparedStatement statement = null; ResultSet generatedKeys = null; try { statement = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS); StringBuilder paramBuf = null; if (log.isDebugEnabled()) { log.debug("execute: " + sql.toString()); paramBuf = new StringBuilder(); paramBuf.append("["); } int i = 1; for (PropertyPair pair : values.values()) { int jdbcType = converter.toJDBCDataType(pair.getProp(), pair.getValue()); Object jdbcValue = converter.toJDBCDataValue(pair.getProp(), pair.getValue()); statement.setObject(pair.getColumn(), jdbcValue, jdbcType); if (log.isDebugEnabled()) { if (i > 1) { paramBuf.append(", "); } paramBuf.append("("); paramBuf.append(jdbcValue.getClass().getSimpleName()); paramBuf.append("/"); paramBuf.append(converter.getJdbcTypeName(jdbcType)); paramBuf.append(")"); paramBuf.append(String.valueOf(jdbcValue)); } i++; } if (log.isDebugEnabled()) { paramBuf.append("]"); log.debug("params: " + paramBuf.toString()); } statement.execute(); generatedKeys = statement.getGeneratedKeys(); //if (generatedKeys.next()) { // resultKeys.add(generatedKeys.getObject(1)); //} ResultSetMetaData rsMeta = generatedKeys.getMetaData(); int numcols = rsMeta.getColumnCount(); if (log.isDebugEnabled()) log.debug("returned " + numcols + " keys"); if (generatedKeys.next()) { // FIXME; without metadata describing which properties // are actually a sequence, there us guess work // involved in matching the values returned // automatically from PreparedStatment as they // are anonymous in terms of the column names // making it impossible to match them to a metadata // property. List<Property> pkPropList = type.findProperties(KeyType.primary); if (pkPropList == null || pkPropList.size() == 0) throw new DataAccessException("no pri-key properties found for type '" + type.getName() + "'"); if (pkPropList.size() > 1) throw new DataAccessException("multiple pri-key properties found for type '" + type.getName() + "' - cannot map to generated keys"); PlasmaProperty prop = (PlasmaProperty) pkPropList.get(0); for (i = 1; i <= numcols; i++) { String columnName = rsMeta.getColumnName(i); if (log.isDebugEnabled()) log.debug("returned key column '" + columnName + "'"); int columnType = rsMeta.getColumnType(i); Object value = converter.fromJDBCDataType(generatedKeys, i, columnType, prop); PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value); resultKeys.add(pair); } } } catch (Throwable t) { throw new DataAccessException(t); } finally { try { if (statement != null) statement.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } } return resultKeys; }
From source file:com.rapid.actions.Database.java
public JSONObject doQuery(RapidRequest rapidRequest, JSONObject jsonAction, Application application, DataFactory df) throws Exception { // place holder for the object we're going to return JSONObject jsonData = null;/* w w w . j a va 2 s. co m*/ // retrieve the sql String sql = _query.getSQL(); // only if there is some sql is it worth going further if (sql != null) { // get any json inputs JSONObject jsonInputData = jsonAction.optJSONObject("data"); // initialise the parameters list ArrayList<Parameters> parametersList = new ArrayList<Parameters>(); // populate the parameters from the inputs collection (we do this first as we use them as the cache key due to getting values from the session) if (_query.getInputs() == null) { // just add an empty parameters member if no inputs parametersList.add(new Parameters()); } else { // if there is input data if (jsonInputData != null) { // get any input fields JSONArray jsonFields = jsonInputData.optJSONArray("fields"); // get any input rows JSONArray jsonRows = jsonInputData.optJSONArray("rows"); // if we have fields and rows if (jsonFields != null && jsonRows != null) { // loop the input rows (only the top row if not multirow) for (int i = 0; i < jsonRows.length() && (_query.getMultiRow() || i == 0); i++) { // get this jsonRow JSONArray jsonRow = jsonRows.getJSONArray(i); // make the parameters for this row Parameters parameters = new Parameters(); // loop the query inputs for (Parameter input : _query.getInputs()) { // get the input id String id = input.getItemId(); // get the input field String field = input.getField(); // add field to id if present if (field != null && !"".equals(field)) id += "." + field; // retain the value String value = null; // if it looks like a control, or a system value (bit of extra safety checking) if ("P".equals(id.substring(0, 1)) && id.indexOf("_C") > 0 || id.indexOf("System.") == 0) { // loop the json inputs looking for the value if (jsonInputData != null) { for (int j = 0; j < jsonFields.length(); j++) { // get the id from the fields String jsonId = jsonFields.optString(j); // if the id we want matches this one if (id.toLowerCase().equals(jsonId.toLowerCase())) { // get the value value = jsonRow.optString(j, null); // no need to keep looking break; } } } } // if still null try the session if (value == null) value = (String) rapidRequest.getSessionAttribute(input.getItemId()); // add the parameter parameters.add(value); } // add the parameters to the list parametersList.add(parameters); } // row loop } // input fields and rows check } // input data check } // query inputs check // placeholder for the action cache ActionCache actionCache = rapidRequest.getRapidServlet().getActionCache(); // if an action cache was found if (actionCache != null) { // log that we found action cache _logger.debug("Database action cache found"); // attempt to fetch data from the cache jsonData = actionCache.get(application.getId(), getId(), parametersList.toString()); } // if there isn't a cache or no data was retrieved if (jsonData == null) { try { // instantiate jsonData jsonData = new JSONObject(); // fields collection JSONArray jsonFields = new JSONArray(); // rows collection can start initialised JSONArray jsonRows = new JSONArray(); // trim the sql sql = sql.trim(); // check the verb if (sql.toLowerCase().startsWith("select") || sql.toLowerCase().startsWith("with")) { // set readonly to true (makes for faster querying) df.setReadOnly(true); // loop the parameterList getting a result set for each parameters (input row) for (Parameters parameters : parametersList) { // get the result set! ResultSet rs = df.getPreparedResultSet(rapidRequest, sql, parameters); // get it's meta data for the field names ResultSetMetaData rsmd = rs.getMetaData(); // got fields indicator boolean gotFields = false; // loop the result set while (rs.next()) { // initialise the row JSONArray jsonRow = new JSONArray(); // loop the columns for (int i = 0; i < rsmd.getColumnCount(); i++) { // add the field name to the fields collection if not done yet if (!gotFields) jsonFields.put(rsmd.getColumnLabel(i + 1)); // get the column type int columnType = rsmd.getColumnType(i + 1); // add the data to the row according to it's type switch (columnType) { case (Types.NUMERIC): jsonRow.put(rs.getDouble(i + 1)); break; case (Types.INTEGER): jsonRow.put(rs.getInt(i + 1)); break; case (Types.BIGINT): jsonRow.put(rs.getLong(i + 1)); break; case (Types.FLOAT): jsonRow.put(rs.getFloat(i + 1)); break; case (Types.DOUBLE): jsonRow.put(rs.getDouble(i + 1)); break; default: jsonRow.put(rs.getString(i + 1)); } } // add the row to the rows collection jsonRows.put(jsonRow); // remember we now have our fields gotFields = true; } // close the record set rs.close(); } } else { // assume rows affected is 0 int rows = 0; // sql check if (sql.length() > 0) { // perform update for all incoming parameters (one parameters collection for each row) for (Parameters parameters : parametersList) { rows += df.getPreparedUpdate(rapidRequest, sql, parameters); } // add a psuedo field jsonFields.put("rows"); // create a row array JSONArray jsonRow = new JSONArray(); // add the rows updated jsonRow.put(rows); // add the row we just made jsonRows.put(jsonRow); } } // add the fields to the data object jsonData.put("fields", jsonFields); // add the rows to the data object jsonData.put("rows", jsonRows); // check for any child database actions if (_childDatabaseActions != null) { // if there really are some if (_childDatabaseActions.size() > 0) { // get any child data JSONArray jsonChildQueries = jsonAction.optJSONArray("childQueries"); // if there was some if (jsonChildQueries != null) { // loop for (int i = 0; i < jsonChildQueries.length(); i++) { // fetch the data JSONObject jsonChildAction = jsonChildQueries.getJSONObject(i); // read the index (the position of the child this related to int index = jsonChildAction.getInt("index"); // get the relevant child action Database childDatabaseAction = _childDatabaseActions.get(index); // get the resultant child data JSONObject jsonChildData = childDatabaseAction.doQuery(rapidRequest, jsonChildAction, application, df); // a map for indexes of matching fields between our parent and child Map<Integer, Integer> fieldsMap = new HashMap<Integer, Integer>(); // the child fields JSONArray jsonChildFields = jsonChildData.getJSONArray("fields"); if (jsonChildFields != null) { // loop the parent fields for (int j = 0; j < jsonFields.length(); j++) { // loop the child fields for (int k = 0; k < jsonChildFields.length(); k++) { // get parent field String field = jsonFields.getString(j); // get child field String childField = jsonChildFields.getString(k); // if both not null if (field != null && childField != null) { // check for match if (field.toLowerCase().equals(childField.toLowerCase())) fieldsMap.put(j, k); } } } } // add a field for the results of this child action jsonFields.put("childAction" + (i + 1)); // if matching fields if (fieldsMap.size() > 0) { // an object with a null value for when there is no match Object nullObject = null; // get the child rows JSONArray jsonChildRows = jsonChildData.getJSONArray("rows"); // if we had some if (jsonChildRows != null) { // loop the parent rows for (int j = 0; j < jsonRows.length(); j++) { // get the parent row JSONArray jsonRow = jsonRows.getJSONArray(j); // make a new rows collection for the child subset JSONArray jsonChildRowsSubset = new JSONArray(); // loop the child rows for (int k = 0; k < jsonChildRows.length(); k++) { // get the child row JSONArray jsonChildRow = jsonChildRows.getJSONArray(k); // assume no matches int matches = 0; // loop the fields map for (Integer l : fieldsMap.keySet()) { // parent value Object parentValue = null; // get the value if there are enough if (jsonRow.length() > l) parentValue = jsonRow.get(l); // child value Object childValue = null; if (jsonChildRow.length() > l) childValue = jsonChildRow.get(fieldsMap.get(l)); // non null check if (parentValue != null && childValue != null) { // a string we will concert the child value to String parentString = null; // check the parent value type if (parentValue.getClass() == String.class) { parentString = (String) parentValue; } else if (parentValue.getClass() == Integer.class) { parentString = Integer .toString((Integer) parentValue); } else if (parentValue.getClass() == Long.class) { parentString = Long.toString((Long) parentValue); } else if (parentValue.getClass() == Double.class) { parentString = Double .toString((Double) parentValue); } else if (parentValue.getClass() == Boolean.class) { parentString = Boolean .toString((Boolean) parentValue); } // a string we will convert the child value to String childString = null; // check the parent value type if (childValue.getClass() == String.class) { childString = (String) childValue; } else if (childValue.getClass() == Integer.class) { childString = Integer .toString((Integer) childValue); } else if (childValue.getClass() == Long.class) { childString = Long.toString((Long) childValue); } else if (childValue.getClass() == Double.class) { childString = Double.toString((Double) childValue); } else if (childValue.getClass() == Boolean.class) { childString = Boolean .toString((Boolean) childValue); } // non null check if (parentString != null && childString != null) { // do the match! if (parentString.equals(childString)) matches++; } } // values non null } // field map loop // if we got some matches for all the fields add this row to the subset if (matches == fieldsMap.size()) jsonChildRowsSubset.put(jsonChildRow); } // child row loop // if our child subset has rows in it if (jsonChildRowsSubset.length() > 0) { // create a new childSubset object JSONObject jsonChildDataSubset = new JSONObject(); // add the fields jsonChildDataSubset.put("fields", jsonChildFields); // add the subset of rows jsonChildDataSubset.put("rows", jsonChildRowsSubset); // add the child database action data subset jsonRow.put(jsonChildDataSubset); } else { // add an empty cell jsonRow.put(nullObject); } } // parent row loop } // jsonChildRows null check } else { // loop the parent rows for (int j = 0; j < jsonRows.length(); j++) { // get the row JSONArray jsonRow = jsonRows.getJSONArray(j); // add the child database action data jsonRow.put(jsonChildData); } } // matching fields check } // jsonChildQueries loop } // jsonChildQueries null check } // _childDatabaseActions size > 0 } // _childDatabaseActions not null // cache if in use if (actionCache != null) actionCache.put(application.getId(), getId(), parametersList.toString(), jsonData); } catch (Exception ex) { // log the error _logger.error(ex); // close the data factory and silently fail try { df.close(); } catch (Exception ex2) { } // only throw if no action cache if (actionCache == null) { throw ex; } else { _logger.debug("Error not shown to user due to cache : " + ex.getMessage()); } } // jsonData not null } // jsonData == null } // got sql return jsonData; }
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; }//from w w w . ja v a 2 s. c o 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(); } } } }