List of usage examples for java.sql ResultSetMetaData getColumnLabel
String getColumnLabel(int column) throws SQLException;
From source file:org.geoserver.taskmanager.tasks.CopyTableTaskTypeImpl.java
@Override public TaskResult run(TaskContext ctx) throws TaskException { // TODO: check for ctx.isInterruptMe() in loops and cancel task final DbSource sourcedb = (DbSource) ctx.getParameterValues().get(PARAM_SOURCE_DB_NAME); final DbSource targetdb = (DbSource) ctx.getParameterValues().get(PARAM_TARGET_DB_NAME); final DbTable table = (DbTable) ctx.getBatchContext().get(ctx.getParameterValues().get(PARAM_TABLE_NAME)); final DbTable targetTable = ctx.getParameterValues().containsKey(PARAM_TARGET_TABLE_NAME) ? (DbTable) ctx.getParameterValues().get(PARAM_TARGET_TABLE_NAME) : new DbTableImpl(targetdb, table.getTableName()); final String tempTableName = SqlUtil.qualified(SqlUtil.schema(targetTable.getTableName()), "_temp_" + UUID.randomUUID().toString().replace('-', '_')); ctx.getBatchContext().put(targetTable, new DbTableImpl(targetdb, tempTableName)); try (Connection sourceConn = sourcedb.getDataSource().getConnection()) { sourceConn.setAutoCommit(false); try (Connection destConn = targetdb.getDataSource().getConnection()) { try (Statement stmt = sourceConn.createStatement()) { stmt.setFetchSize(BATCH_SIZE); try (ResultSet rs = stmt .executeQuery("SELECT * FROM " + sourcedb.getDialect().quote(table.getTableName()))) { ResultSetMetaData rsmd = rs.getMetaData(); String tempSchema = SqlUtil.schema(tempTableName); String sqlCreateSchemaIfNotExists = tempSchema == null ? "" : targetdb.getDialect().createSchema(destConn, targetdb.getDialect().quote(tempSchema)); // create the temp table structure StringBuilder sb = new StringBuilder(sqlCreateSchemaIfNotExists); sb.append("CREATE TABLE ").append(targetdb.getDialect().quote(tempTableName)).append(" ( "); int columnCount = rsmd.getColumnCount(); for (int i = 1; i <= columnCount; i++) { String columnName = targetdb.getDialect().quote(rsmd.getColumnLabel(i)); String typeName = rsmd.getColumnTypeName(i); sb.append(columnName).append(" ").append(typeName); if (("char".equals(typeName) || "varchar".equals(typeName)) && rsmd.getColumnDisplaySize(i) > 0 && rsmd.getColumnDisplaySize(i) < Integer.MAX_VALUE) { sb.append(" (").append(rsmd.getColumnDisplaySize(i)).append(" ) "); }// www .ja va2 s . co m switch (sourcedb.getDialect().isNullable(rsmd.isNullable(i))) { case ResultSetMetaData.columnNoNulls: sb.append(" NOT NULL"); break; case ResultSetMetaData.columnNullable: sb.append(" NULL"); break; } sb.append(", "); } String primaryKey = getPrimaryKey(sourceConn, table.getTableName()); boolean hasPrimaryKeyColumn = !primaryKey.isEmpty(); if (!hasPrimaryKeyColumn) { // create a Primary key column if none exist. sb.append(GENERATE_ID_COLUMN_NAME + " int PRIMARY KEY, "); columnCount++; } sb.setLength(sb.length() - 2); sb.append(" ); "); // creating indexes Map<String, Set<String>> indexAndColumnMap = getIndexesColumns(sourceConn, table.getTableName()); Set<String> uniqueIndexes = getUniqueIndexes(sourceConn, table.getTableName()); Set<String> spatialColumns = sourcedb.getDialect().getSpatialColumns(sourceConn, table.getTableName(), sourcedb.getSchema()); for (String indexName : indexAndColumnMap.keySet()) { Set<String> columnNames = indexAndColumnMap.get(indexName); boolean isSpatialIndex = columnNames.size() == 1 && spatialColumns.contains(columnNames.iterator().next()); sb.append(targetdb.getDialect().createIndex(tempTableName, columnNames, isSpatialIndex, uniqueIndexes.contains(indexName))); } // we are copying a view and need to create the spatial index. if (indexAndColumnMap.isEmpty() && !spatialColumns.isEmpty()) { sb.append( targetdb.getDialect().createIndex(tempTableName, spatialColumns, true, false)); } String dump = sb.toString(); LOGGER.log(Level.FINE, "creating temporary table: " + dump); try (Statement stmt2 = destConn.createStatement()) { stmt2.executeUpdate(dump); } // copy the data sb = new StringBuilder("INSERT INTO ").append(targetdb.getDialect().quote(tempTableName)) .append(" VALUES ("); for (int i = 0; i < columnCount; i++) { if (i > 0) { sb.append(","); } sb.append("?"); } sb.append(")"); LOGGER.log(Level.FINE, "inserting records: " + sb.toString()); try (PreparedStatement pstmt = destConn.prepareStatement(sb.toString())) { int batchSize = 0; int primaryKeyValue = 0; while (rs.next()) { for (int i = 1; i <= rsmd.getColumnCount(); i++) { pstmt.setObject(i, rs.getObject(i)); } // generate the primary key value if (!hasPrimaryKeyColumn) { pstmt.setObject(columnCount, primaryKeyValue); } pstmt.addBatch(); batchSize++; if (batchSize >= BATCH_SIZE) { pstmt.executeBatch(); batchSize = 0; } primaryKeyValue++; } if (batchSize > 0) { pstmt.executeBatch(); } } } } } } catch (SQLException e) { // clean-up if necessary try (Connection conn = targetdb.getDataSource().getConnection()) { try (Statement stmt = conn.createStatement()) { stmt.executeUpdate("DROP TABLE IF EXISTS " + targetdb.getDialect().quote(tempTableName)); } } catch (SQLException e2) { } throw new TaskException(e); } return new TaskResult() { @Override public void commit() throws TaskException { try (Connection conn = targetdb.getDataSource().getConnection()) { try (Statement stmt = conn.createStatement()) { stmt.executeUpdate( "DROP TABLE IF EXISTS " + targetdb.getDialect().quote(targetTable.getTableName())); stmt.executeUpdate("ALTER TABLE " + targetdb.getDialect().quote(tempTableName) + " RENAME TO " + targetdb.getDialect().quote(SqlUtil.notQualified(targetTable.getTableName()))); } ctx.getBatchContext().delete(targetTable); } catch (SQLException e) { throw new TaskException(e); } } @Override public void rollback() throws TaskException { try (Connection conn = targetdb.getDataSource().getConnection()) { try (Statement stmt = conn.createStatement()) { stmt.executeUpdate("DROP TABLE " + targetdb.getDialect().quote(tempTableName) + ""); } } catch (SQLException e) { throw new TaskException(e); } } }; }
From source file:net.hydromatic.optiq.test.JdbcTest.java
/** Tests driver's implementation of {@link DatabaseMetaData#getColumns}. */ @Test//from w w w . java 2 s . com public void testResultSetMetaData() throws ClassNotFoundException, SQLException { Connection connection = getConnection("hr", "foodmart"); Statement statement = connection.createStatement(); ResultSet resultSet = statement .executeQuery("select \"empid\", \"deptno\" as x, 1 as y\n" + "from \"hr\".\"emps\""); ResultSetMetaData metaData = resultSet.getMetaData(); assertEquals(3, metaData.getColumnCount()); assertEquals("empid", metaData.getColumnLabel(1)); assertEquals("empid", metaData.getColumnName(1)); assertEquals("emps", metaData.getTableName(1)); assertEquals("X", metaData.getColumnLabel(2)); assertEquals("deptno", metaData.getColumnName(2)); assertEquals("emps", metaData.getTableName(2)); assertEquals("Y", metaData.getColumnLabel(3)); assertEquals("Y", metaData.getColumnName(3)); assertEquals(null, metaData.getTableName(3)); resultSet.close(); connection.close(); }
From source file:org.executequery.gui.resultset.ResultSetTableModel.java
public void createTable(ResultSet resultSet) { if (!isOpenAndValid(resultSet)) { clearData();/*from www . j a va 2s . c o m*/ return; } try { resetMetaData(); ResultSetMetaData rsmd = resultSet.getMetaData(); columnHeaders.clear(); visibleColumnHeaders.clear(); tableData.clear(); int zeroBaseIndex = 0; int count = rsmd.getColumnCount(); for (int i = 1; i <= count; i++) { zeroBaseIndex = i - 1; columnHeaders.add(new ResultSetColumnHeader(zeroBaseIndex, rsmd.getColumnLabel(i), rsmd.getColumnName(i), rsmd.getColumnType(i), rsmd.getColumnTypeName(i))); } int recordCount = 0; interrupted = false; if (holdMetaData) { setMetaDataVectors(rsmd); } List<RecordDataItem> rowData; long time = System.currentTimeMillis(); while (resultSet.next()) { if (interrupted || Thread.interrupted()) { throw new InterruptedException(); } recordCount++; rowData = new ArrayList<RecordDataItem>(count); for (int i = 1; i <= count; i++) { zeroBaseIndex = i - 1; ResultSetColumnHeader header = columnHeaders.get(zeroBaseIndex); RecordDataItem value = recordDataItemFactory.create(header); try { int dataType = header.getDataType(); switch (dataType) { // some drivers (informix for example) // was noticed to return the hashcode from // getObject for -1 data types (eg. longvarchar). // force string for these - others stick with // getObject() for default value formatting case Types.CHAR: case Types.VARCHAR: value.setValue(resultSet.getString(i)); break; case Types.DATE: value.setValue(resultSet.getDate(i)); break; case Types.TIME: value.setValue(resultSet.getTime(i)); break; case Types.TIMESTAMP: value.setValue(resultSet.getTimestamp(i)); break; case Types.LONGVARCHAR: case Types.CLOB: value.setValue(resultSet.getClob(i)); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BINARY: value.setValue(resultSet.getBytes(i)); break; case Types.BLOB: value.setValue(resultSet.getBlob(i)); break; case Types.BIT: case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.FLOAT: case Types.REAL: case Types.DOUBLE: case Types.NUMERIC: case Types.DECIMAL: case Types.NULL: case Types.OTHER: case Types.JAVA_OBJECT: case Types.DISTINCT: case Types.STRUCT: case Types.ARRAY: case Types.REF: case Types.DATALINK: case Types.BOOLEAN: case Types.ROWID: case Types.NCHAR: case Types.NVARCHAR: case Types.LONGNVARCHAR: case Types.NCLOB: case Types.SQLXML: // use getObject for all other known types value.setValue(resultSet.getObject(i)); break; default: // otherwise try as string asStringOrObject(value, resultSet, i); break; } } catch (Exception e) { try { // ... and on dump, resort to string value.setValue(resultSet.getString(i)); } catch (SQLException sqlException) { // catch-all SQLException - yes, this is hideous // noticed with invalid date formatted values in mysql value.setValue("<Error - " + sqlException.getMessage() + ">"); } } if (resultSet.wasNull()) { value.setNull(); } rowData.add(value); } tableData.add(rowData); if (recordCount == maxRecords) { break; } } if (Log.isTraceEnabled()) { Log.trace("Finished populating table model - " + recordCount + " rows - [ " + MiscUtils.formatDuration(System.currentTimeMillis() - time) + "]"); } fireTableStructureChanged(); } catch (SQLException e) { System.err.println("SQL error populating table model at: " + e.getMessage()); Log.debug("Table model error - " + e.getMessage(), e); } catch (Exception e) { if (e instanceof InterruptedException) { Log.debug("ResultSet generation interrupted.", e); } else { String message = e.getMessage(); if (StringUtils.isBlank(message)) { System.err.println("Exception populating table model."); } else { System.err.println("Exception populating table model at: " + message); } Log.debug("Table model error - ", e); } } finally { if (resultSet != null) { try { resultSet.close(); Statement statement = resultSet.getStatement(); if (statement != null) { statement.close(); } } catch (SQLException e) { } } } }
From source file:com.glaf.core.jdbc.QueryHelper.java
public Map<String, Object> toMap(ResultSet rs) throws SQLException { Map<String, Object> result = new CaseInsensitiveHashMap(); ResultSetMetaData rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); for (int i = 1; i <= count; i++) { String columnName = rsmd.getColumnLabel(i); if (StringUtils.isEmpty(columnName)) { columnName = rsmd.getColumnName(i); }//w w w. j a v a 2 s.c o m Object object = rs.getObject(i); columnName = columnName.toLowerCase(); String name = StringTools.camelStyle(columnName); result.put(name, object); result.put(columnName, object); } return result; }
From source file:org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.ResultSetTableModelFactory.java
/** * No longer used.//from ww w . j a v a 2s . c o m * * @param rsmd * @param metaData * @param column */ @Deprecated public static void updateMetaData(final ResultSetMetaData rsmd, final DefaultTableMetaData metaData, final int column) { try { if (rsmd.isCurrency(column + 1)) { metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.CURRENCY, Boolean.TRUE); } else { metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.CURRENCY, Boolean.FALSE); } if (rsmd.isSigned(column + 1)) { metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SIGNED, Boolean.TRUE); } else { metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SIGNED, Boolean.FALSE); } final String tableName = rsmd.getTableName(column + 1); if (tableName != null) { metaData.setColumnAttribute(column, MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.TABLE, tableName); } final String schemaName = rsmd.getSchemaName(column + 1); if (schemaName != null) { metaData.setColumnAttribute(column, MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.SCHEMA, schemaName); } final String catalogName = rsmd.getCatalogName(column + 1); if (catalogName != null) { metaData.setColumnAttribute(column, MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.CATALOG, catalogName); } final String label = rsmd.getColumnLabel(column + 1); if (label != null) { metaData.setColumnAttribute(column, MetaAttributeNames.Formatting.NAMESPACE, MetaAttributeNames.Formatting.LABEL, label); } final int displaySize = rsmd.getColumnDisplaySize(column + 1); metaData.setColumnAttribute(column, MetaAttributeNames.Formatting.NAMESPACE, MetaAttributeNames.Formatting.DISPLAY_SIZE, IntegerCache.getInteger(displaySize)); final int precision = rsmd.getPrecision(column + 1); metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.PRECISION, IntegerCache.getInteger(precision)); final int scale = rsmd.getScale(column + 1); metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SCALE, IntegerCache.getInteger(scale)); } catch (SQLException sqle) { // It is non-fatal if the meta-data cannot be read from the result set. Drivers are // buggy all the time .. } }
From source file:com.glaf.core.jdbc.QueryHelper.java
public List<Map<String, Object>> getResults(ResultSet rs) { logger.debug("--------------use mybatis results----------------"); try {// ww w .jav a 2 s . c om List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); List<String> columns = new ArrayList<String>(); List<TypeHandler<?>> typeHandlers = new ArrayList<TypeHandler<?>>(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) { columns.add(rsmd.getColumnLabel(i + 1)); try { Class<?> type = Resources.classForName(rsmd.getColumnClassName(i + 1)); TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(type); if (typeHandler == null) { typeHandler = typeHandlerRegistry.getTypeHandler(Object.class); } typeHandlers.add(typeHandler); } catch (Exception ex) { ex.printStackTrace(); typeHandlers.add(typeHandlerRegistry.getTypeHandler(Object.class)); } } while (rs.next()) { Map<String, Object> row = new HashMap<String, Object>(); for (int i = 0, n = columns.size(); i < n; i++) { String name = columns.get(i); TypeHandler<?> handler = typeHandlers.get(i); Object value = handler.getResult(rs, name); row.put(name, value); if (value != null && value instanceof java.util.Date) { java.util.Date date = (java.util.Date) value; row.put(name + "_date", DateUtils.getDate(date)); row.put(name + "_datetime", DateUtils.getDateTime(date)); } } list.add(row); } return list; } catch (SQLException ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { try { if (rs != null) { rs.close(); rs = null; } } catch (SQLException e) { } } }
From source file:org.apache.sqoop.manager.SqlManager.java
/** * Get column types for a query statement that we do not modify further. *///from w w w . j a v a 2 s . com protected Map<String, Integer> getColumnTypesForRawQuery(String stmt) { ResultSet results; try { results = execute(stmt); } catch (SQLException sqlE) { LOG.error("Error executing statement: " + sqlE.toString(), sqlE); release(); return null; } try { Map<String, Integer> colTypes = new SqlTypeMap<String, Integer>(); int cols = results.getMetaData().getColumnCount(); ResultSetMetaData metadata = results.getMetaData(); for (int i = 1; i < cols + 1; i++) { int typeId = metadata.getColumnType(i); // If we have an unsigned int we need to make extra room by // plopping it into a bigint if (typeId == Types.INTEGER && !metadata.isSigned(i)) { typeId = Types.BIGINT; } String colName = metadata.getColumnName(i); if (colName == null || colName.equals("")) { colName = metadata.getColumnLabel(i); } colTypes.put(colName, Integer.valueOf(typeId)); } return colTypes; } catch (SQLException sqlException) { LOG.error("Error reading from database: " + sqlException.toString()); return null; } finally { try { results.close(); getConnection().commit(); } catch (SQLException sqlE) { LOG.warn("SQLException closing ResultSet: " + sqlE.toString()); } release(); } }
From source file:com.netspective.axiom.sql.QueryResultSet.java
public void fillReportFromMetaData(TabularReport report) throws SQLException { ResultSetMetaData rsmd = resultSet.getMetaData(); int numColumns = rsmd.getColumnCount(); TabularReportColumns columns = report.getColumns(); columns.clear();// w w w .j av a 2 s.c o m for (int c = 1; c <= numColumns; c++) { TabularReportColumn column = null; int dataType = rsmd.getColumnType(c); switch (dataType) { case Types.INTEGER: case Types.SMALLINT: case Types.BIGINT: case Types.TINYINT: case Types.BIT: column = new NumericColumn(); break; case Types.FLOAT: case Types.REAL: column = new DecimalColumn(); break; case Types.NUMERIC: case Types.DECIMAL: if (rsmd.getScale(c) > 0) column = new DecimalColumn(); else column = new NumericColumn(); break; default: column = new GeneralColumn(); break; } column.setColIndex(c - 1); column.setHeading(new StaticValueSource( TextUtils.getInstance().sqlIdentifierToText(rsmd.getColumnLabel(c), true))); column.setDataType(dataType); column.setWidth(rsmd.getColumnDisplaySize(c)); columns.add(column); } report.finalizeContents(); }
From source file:org.exist.xquery.modules.sql.ExecuteFunction.java
/** * evaluate the call to the XQuery execute() function, it is really the main entry point of this class. * * @param args arguments from the execute() function call * @param contextSequence the Context Sequence to operate on (not used here internally!) * * @return A node representing the SQL result set * * @throws XPathException DOCUMENT ME! * * @see org.exist.xquery.BasicFunction#eval(org.exist.xquery.value.Sequence[], org.exist.xquery.value.Sequence) */// w w w . j a v a2 s .c o m @Override public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException { // was a connection and SQL statement specified? if (args[0].isEmpty() || args[1].isEmpty()) { return (Sequence.EMPTY_SEQUENCE); } // get the Connection long connectionUID = ((IntegerValue) args[0].itemAt(0)).getLong(); Connection con = SQLModule.retrieveConnection(context, connectionUID); if (con == null) { return (Sequence.EMPTY_SEQUENCE); } boolean preparedStmt = false; //setup the SQL statement String sql = null; Statement stmt = null; boolean executeResult = false; ResultSet rs = null; try { boolean makeNodeFromColumnName = false; MemTreeBuilder builder = context.getDocumentBuilder(); int iRow = 0; //SQL or PreparedStatement? if (args.length == 3) { // get the SQL statement sql = args[1].getStringValue(); stmt = con.createStatement(); makeNodeFromColumnName = ((BooleanValue) args[2].itemAt(0)).effectiveBooleanValue(); //execute the statement executeResult = stmt.execute(sql); } else if (args.length == 4) { preparedStmt = true; //get the prepared statement long statementUID = ((IntegerValue) args[1].itemAt(0)).getLong(); PreparedStatementWithSQL stmtWithSQL = SQLModule.retrievePreparedStatement(context, statementUID); sql = stmtWithSQL.getSql(); stmt = stmtWithSQL.getStmt(); makeNodeFromColumnName = ((BooleanValue) args[3].itemAt(0)).effectiveBooleanValue(); if (!args[2].isEmpty()) { setParametersOnPreparedStatement(stmt, (Element) args[2].itemAt(0)); } //execute the prepared statement executeResult = ((PreparedStatement) stmt).execute(); } else { //TODO throw exception } // DW: stmt can be null ? // execute the query statement if (executeResult) { /* SQL Query returned results */ // iterate through the result set building an XML document rs = stmt.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); int iColumns = rsmd.getColumnCount(); builder.startDocument(); builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("count", null, null), String.valueOf(-1)); while (rs.next()) { builder.startElement(new QName("row", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("index", null, null), String.valueOf(rs.getRow())); // get each tuple in the row for (int i = 0; i < iColumns; i++) { String columnName = rsmd.getColumnLabel(i + 1); if (columnName != null) { String colElement = "field"; if (makeNodeFromColumnName && columnName.length() > 0) { // use column names as the XML node /** * Spaces in column names are replaced with * underscore's */ colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_')); } builder.startElement(new QName(colElement, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); if (!makeNodeFromColumnName || columnName.length() <= 0) { String name; if (columnName.length() > 0) { name = SQLUtils.escapeXmlAttr(columnName); } else { name = "Column: " + String.valueOf(i + 1); } builder.addAttribute(new QName("name", null, null), name); } builder.addAttribute( new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), rsmd.getColumnTypeName(i + 1)); builder.addAttribute(new QName(TYPE_ATTRIBUTE_NAME, Namespaces.SCHEMA_NS, "xs"), Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1)))); //get the content if (rsmd.getColumnType(i + 1) == Types.SQLXML) { //parse sqlxml value try { final SQLXML sqlXml = rs.getSQLXML(i + 1); if (rs.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true"); } else { SAXParserFactory factory = SAXParserFactory.newInstance(); factory.setNamespaceAware(true); InputSource src = new InputSource(sqlXml.getCharacterStream()); SAXParser parser = factory.newSAXParser(); XMLReader xr = parser.getXMLReader(); SAXAdapter adapter = new AppendingSAXAdapter(builder); xr.setContentHandler(adapter); xr.setProperty(Namespaces.SAX_LEXICAL_HANDLER, adapter); xr.parse(src); } } catch (Exception e) { throw new XPathException( "Could not parse column of type SQLXML: " + e.getMessage(), e); } } else { //otherwise assume string value final String colValue = rs.getString(i + 1); if (rs.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true"); } else { if (colValue != null) { builder.characters(SQLUtils.escapeXmlText(colValue)); } } } builder.endElement(); } } builder.endElement(); iRow++; } builder.endElement(); } else { /* SQL Query performed updates */ builder.startDocument(); builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("updateCount", null, null), String.valueOf(stmt.getUpdateCount())); builder.endElement(); } // Change the root element count attribute to have the correct value NodeValue node = (NodeValue) builder.getDocument().getDocumentElement(); Node count = node.getNode().getAttributes().getNamedItem("count"); if (count != null) { count.setNodeValue(String.valueOf(iRow)); } builder.endDocument(); // return the XML result set return (node); } catch (SQLException sqle) { LOG.error("sql:execute() Caught SQLException \"" + sqle.getMessage() + "\" for SQL: \"" + sql + "\"", sqle); //return details about the SQLException MemTreeBuilder builder = context.getDocumentBuilder(); builder.startDocument(); builder.startElement(new QName("exception", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); boolean recoverable = false; if (sqle instanceof SQLRecoverableException) { recoverable = true; } builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable)); builder.startElement(new QName("state", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.characters(sqle.getSQLState()); builder.endElement(); builder.startElement(new QName("message", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); String state = sqle.getMessage(); if (state != null) { builder.characters(state); } builder.endElement(); builder.startElement(new QName("stack-trace", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream(); sqle.printStackTrace(new PrintStream(bufStackTrace)); builder.characters(new String(bufStackTrace.toByteArray())); builder.endElement(); builder.startElement(new QName("sql", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.characters(SQLUtils.escapeXmlText(sql)); builder.endElement(); if (stmt instanceof PreparedStatement) { Element parametersElement = (Element) args[2].itemAt(0); if (parametersElement.getNamespaceURI().equals(SQLModule.NAMESPACE_URI) && parametersElement.getLocalName().equals(PARAMETERS_ELEMENT_NAME)) { NodeList paramElements = parametersElement.getElementsByTagNameNS(SQLModule.NAMESPACE_URI, PARAM_ELEMENT_NAME); builder.startElement( new QName(PARAMETERS_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); for (int i = 0; i < paramElements.getLength(); i++) { Element param = ((Element) paramElements.item(i)); String value = param.getFirstChild().getNodeValue(); String type = param.getAttributeNS(SQLModule.NAMESPACE_URI, TYPE_ATTRIBUTE_NAME); builder.startElement( new QName(PARAM_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute( new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), type); builder.characters(SQLUtils.escapeXmlText(value)); builder.endElement(); } builder.endElement(); } } builder.startElement(new QName("xquery", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("line", null, null), String.valueOf(getLine())); builder.addAttribute(new QName("column", null, null), String.valueOf(getColumn())); builder.endElement(); builder.endElement(); builder.endDocument(); return ((NodeValue) builder.getDocument().getDocumentElement()); } finally { // close any record set or statement if (rs != null) { try { rs.close(); } catch (SQLException se) { LOG.warn("Unable to cleanup JDBC results", se); } rs = null; } if (!preparedStmt && stmt != null) { try { stmt.close(); } catch (SQLException se) { LOG.warn("Unable to cleanup JDBC results", se); } stmt = null; } } }
From source file:org.apache.nifi.cdc.mysql.processors.CaptureChangeMySQL.java
/** * Retrieves the column information for the specified database and table. The column information can be used to enrich CDC events coming from the RDBMS. * * @param key A TableInfoCacheKey reference, which contains the database and table names * @return A TableInfo instance with the ColumnDefinitions provided (if retrieved successfully from the database) *///from w w w . j ava 2 s .c o m protected TableInfo loadTableInfo(TableInfoCacheKey key) throws SQLException { TableInfo tableInfo = null; if (jdbcConnection != null) { try (Statement s = jdbcConnection.createStatement()) { s.execute("USE " + key.getDatabaseName()); ResultSet rs = s.executeQuery("SELECT * FROM " + key.getTableName() + " LIMIT 0"); ResultSetMetaData rsmd = rs.getMetaData(); int numCols = rsmd.getColumnCount(); List<ColumnDefinition> columnDefinitions = new ArrayList<>(); for (int i = 1; i <= numCols; i++) { // Use the column label if it exists, otherwise use the column name. We're not doing aliasing here, but it's better practice. String columnLabel = rsmd.getColumnLabel(i); columnDefinitions.add(new ColumnDefinition(rsmd.getColumnType(i), columnLabel != null ? columnLabel : rsmd.getColumnName(i))); } tableInfo = new TableInfo(key.getDatabaseName(), key.getTableName(), key.getTableId(), columnDefinitions); } } return tableInfo; }