List of usage examples for java.sql ResultSetMetaData getColumnType
int getColumnType(int column) throws SQLException;
From source file:edu.ku.brc.specify.toycode.RegPivot.java
/** * @param newTblName// w w w . j a va 2s. c o m * @param tblName * @param keyName */ private void process(final String newTblName, final String tblName, final String keyName, final String defSQL, final String fillSQL, final boolean isRegBuild) { String sql = String.format("SELECT DISTINCT Name FROM %s", tblName); String sql2 = "SELECT MAX(LENGTH(Value)) FROM " + tblName + " WHERE Name = '%s'"; int instCnt = 0; Statement stmt = null; try { stmt = connection.createStatement(); BasicSQLUtils.setDBConnection(connection); boolean doBuild = true; if (doBuild) { StringBuilder tblSQL = new StringBuilder(String .format("CREATE TABLE %s (`%s` INT(11) NOT NULL AUTO_INCREMENT, \n", newTblName, keyName)); Vector<String> dbFieldNames = new Vector<String>(); Vector<Integer> dbFieldTypes = new Vector<Integer>(); if (defSQL != null) { ResultSet rs = stmt.executeQuery(defSQL); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { if (i > 1) tblSQL.append(",\n "); String name = rsmd.getColumnName(i); dbFieldNames.add(rsmd.getColumnName(i)); dbFieldTypes.add(rsmd.getColumnType(i)); switch (rsmd.getColumnType(i)) { case java.sql.Types.INTEGER: tblSQL.append(String.format("`%s` INT(11) DEFAULT NULL", name)); break; case java.sql.Types.VARCHAR: tblSQL.append(String.format("`%s` VARCHAR(%s) DEFAULT NULL", name, 64)); break; case java.sql.Types.TIMESTAMP: tblSQL.append(String.format("`%s` DATETIME DEFAULT NULL", name)); break; default: System.err.println(String.format("No case for %s %d", name, rsmd.getColumnType(i))); break; } } rs.close(); } int secInx = dbFieldNames.size() + 1; System.out.println("secInx: " + secInx + " " + tblSQL.toString()); HashSet<String> nameSet = new HashSet<String>(); int cnt = 0; for (Object nmObj : BasicSQLUtils.querySingleCol(connection, sql)) { String name = nmObj.toString(); if (name.endsWith("ID")) { continue; } name = StringUtils.replace(name, "(", "_"); name = StringUtils.replace(name, ")", "_"); if (nameSet.contains(name)) continue; nameSet.add(name); tblSQL.append(",\n "); if (name.startsWith("num_") || name.startsWith("Usage_")) { tblSQL.append(String.format("`%s` INT(11) DEFAULT NULL", name)); dbFieldNames.add(name); dbFieldTypes.add(java.sql.Types.INTEGER); } else if (name.endsWith("_number")) { tblSQL.append(String.format("`%s` VARCHAR(16) DEFAULT NULL", name)); dbFieldNames.add(name); dbFieldTypes.add(java.sql.Types.VARCHAR); } else { int maxLen = BasicSQLUtils.getCountAsInt(connection, String.format(sql2, name)); tblSQL.append(String.format("`%s` VARCHAR(%s) DEFAULT NULL", name, maxLen + 1)); dbFieldNames.add(name); dbFieldTypes.add(java.sql.Types.VARCHAR); } cnt++; } if (isRegBuild) { tblSQL.append(String.format(",\n`RecordType`INT(11) DEFAULT NULL")); } tblSQL.append(String.format(",\n PRIMARY KEY (`%s`)) ENGINE=InnoDB DEFAULT CHARSET=UTF8", keyName)); System.out.println(tblSQL.toString()); DBMSUserMgr dbMgr = DBMSUserMgr.getInstance(); dbMgr.setConnection(connection); if (dbMgr.doesDBHaveTable(newTblName)) { BasicSQLUtils.update(connection, "DROP TABLE " + newTblName); } BasicSQLUtils.update(connection, tblSQL.toString()); HashMap<Integer, String> inxToName = new HashMap<Integer, String>(); StringBuilder fields = new StringBuilder(); StringBuilder vals = new StringBuilder(); int inx = 0; for (String nm : dbFieldNames) { if (fields.length() > 0) fields.append(","); fields.append(nm); if (vals.length() > 0) vals.append(","); vals.append('?'); inxToName.put(inx, nm); inx++; } if (isRegBuild) { if (fields.length() > 0) fields.append(","); fields.append("RecordType"); if (vals.length() > 0) vals.append(","); vals.append('?'); } String insertSQL = String.format("INSERT INTO %s (%s) VALUES(%s)", newTblName, fields.toString(), vals.toString()); System.out.println(insertSQL); PreparedStatement pStmt = connection.prepareStatement(insertSQL); if (isRegBuild) { fillRegisterTable(newTblName, stmt, pStmt, fillSQL, secInx, dbFieldTypes, dbFieldNames, inxToName); } else { fillTrackTable(newTblName, stmt, pStmt, fillSQL, secInx, dbFieldTypes, dbFieldNames, inxToName); } System.out.println("InstCnt: " + instCnt); pStmt.close(); } boolean doIP = false; if (doIP) { HTTPGetter httpGetter = new HTTPGetter(); sql = "SELECT RegID, IP from reg"; PreparedStatement pStmt = connection.prepareStatement(String .format("UPDATE %s SET lookup=?, Country=?, City=? WHERE %s = ?", newTblName, keyName)); HashMap<String, String> ipHash = new HashMap<String, String>(); HashMap<String, Pair<String, String>> ccHash = new HashMap<String, Pair<String, String>>(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int regId = rs.getInt(1); String ip = rs.getString(2); String hostName = ipHash.get(ip); String country = null; String city = null; if (hostName == null) { String rvStr = new String( httpGetter.doHTTPRequest("http://api.hostip.info/get_html.php?ip=" + ip)); country = parse(rvStr, "Country:"); city = parse(rvStr, "City:"); System.out.println(rvStr + "[" + country + "][" + city + "]"); try { InetAddress addr = InetAddress.getByName(ip); hostName = addr.getHostName(); ipHash.put(ip, hostName); ccHash.put(ip, new Pair<String, String>(country, city)); } catch (UnknownHostException e) { e.printStackTrace(); } } else { Pair<String, String> p = ccHash.get(ip); if (p != null) { country = p.first; city = p.second; } } pStmt.setString(1, hostName); pStmt.setString(2, country); pStmt.setString(3, city); pStmt.setInt(4, regId); pStmt.executeUpdate(); } pStmt.close(); } stmt.close(); colDBConn.close(); } catch (Exception ex) { ex.printStackTrace(); } System.out.println("Done."); }
From source file:org.executequery.gui.resultset.ResultSetTableModel.java
public void createTable(ResultSet resultSet) { if (!isOpenAndValid(resultSet)) { clearData();/*from w ww . java2 s .c om*/ 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.nextep.designer.sqlclient.ui.editors.SQLFullClientGUI.java
/** * @see com.nextep.datadesigner.gui.model.IConnector#refreshConnector() *//* w ww .ja v a 2 s.com*/ @Override public void refreshConnector() { // Clearing current table columns // clearSQLView(); final ISQLScript script = (ISQLScript) getModel(); if (script.getSql() == null || "".equals(script.getSql())) { return; } try { // sqlText.add("select * from " + o.getName()); // sqlText.select(sqlText.getItemCount()-1); // Creating result table final CTabItem sqlItem = new CTabItem(sqlFolder, SWT.NONE); Composite resultPane = new Composite(sqlFolder, SWT.NONE); final Table sqlView = new Table(resultPane, SWT.FULL_SELECTION | SWT.BORDER); final NextepTableEditor editor = NextepTableEditor.handle(sqlView); CoolBar statsBar = new CoolBar(resultPane, SWT.NONE); statsBar.setLayoutData(new GridData(GridData.FILL_HORIZONTAL)); final CoolItem rowsItem = new CoolItem(statsBar, SWT.NONE); rowsItem.setSize(rowsItem.computeSize(100, 20)); final Label rowsCount = new Label(statsBar, SWT.NONE); rowsItem.setControl(rowsCount); final CoolItem timeItem = new CoolItem(statsBar, SWT.NONE); final Label timeLabel = new Label(statsBar, SWT.NONE); timeItem.setControl(timeLabel); timeItem.setSize(timeItem.computeSize(200, 20)); sqlView.setHeaderVisible(true); sqlView.setLinesVisible(true); sqlView.setLayoutData(new GridData(GridData.FILL, GridData.FILL, true, true, 1, 1)); resultPane.setLayoutData(new GridData(GridData.FILL, GridData.FILL, true, true, 1, 1)); GridLayout grid = new GridLayout(); grid.marginBottom = grid.marginHeight = grid.marginLeft = grid.marginRight = grid.marginTop = grid.marginWidth = 0; resultPane.setLayout(grid); sqlItem.setControl(resultPane); final Listener sortListener = new SortListener(sqlView); final String query = formatQuery(script.getSql()); final int queryLen = query.length(); sqlItem.setText(queryLen < 30 ? query : query.substring(0, 30) + "..."); sqlItem.setToolTipText(query); sqlFolder.setSelection(sqlItem); final List<ICommand> bufferedCommands = new ArrayList<ICommand>(); // Initializing lines Job refreshJob = new Job("Fetching SQL data...") { @Override protected IStatus run(IProgressMonitor monitor) { Statement s = null; ResultSet r = null; try { s = connection.createStatement(); final Date startDate = new Date(); final boolean isResultSet = s.execute(query); final Date afterExecDate = new Date(); if (!isResultSet) { final int updates = s.getUpdateCount(); bufferedCommands.add(new ICommand() { @Override public String getName() { return null; } @Override public Object execute(Object... parameters) { if (sqlView != null && !sqlView.isDisposed()) { TableColumn c = new TableColumn(sqlView, SWT.NONE); c.setText(SQLClientMessages.getString("sql.result")); c.setWidth(300); c.addListener(SWT.Selection, sortListener); if (updates > 0) { final TableItem i = new TableItem(sqlView, SWT.NONE); i.setText(MessageFormat.format( SQLClientMessages.getString("sql.updatedRows"), updates)); } else { final TableItem i = new TableItem(sqlView, SWT.NONE); i.setText(SQLClientMessages.getString("sql.queryOk")); } } return null; } }); syncProcessCommands(bufferedCommands); return Status.OK_STATUS; } r = s.getResultSet(); // Initializing columns final ResultSetMetaData md = r.getMetaData(); // Initializing sizing table final int[] colMaxWidth = new int[md.getColumnCount() + 1]; for (int i = 1; i <= md.getColumnCount(); i++) { final int index = i; final String colName = md.getColumnName(index); // final int colPrecision = md.getPrecision(index); final int colType = md.getColumnType(index); final int colIndex = i - 1; bufferedCommands.add(new ICommand() { @Override public String getName() { return null; } @Override public Object execute(Object... parameters) { if (!sqlView.isDisposed()) { TableColumn c = new TableColumn(sqlView, SWT.NONE); c.addListener(SWT.Selection, sortListener); c.setText(colName); c.setWidth(colName.length() * 8); colMaxWidth[colIndex] = c.getWidth(); c.setData(COL_TYPE, colType); TextColumnEditor.handle(editor, colIndex, ChangeEvent.CUSTOM_1, new IEventListener() { @Override public void handleEvent(ChangeEvent event, IObservable source, Object data) { } }); } return null; } }); } final ResultSet rset = r; int rows = 0; final long execTime = afterExecDate.getTime() - startDate.getTime(); bufferedCommands.add(new ICommand() { @Override public String getName() { return null; } @Override public Object execute(Object... parameters) { timeLabel.setText(MessageFormat .format(SQLClientMessages.getString("sql.executionTime"), execTime)); return null; } }); syncProcessCommands(bufferedCommands); while (r.next()) { rows++; // Handling cancellation while fetching SQL lines if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } final String[] colValues = new String[md.getColumnCount()]; final Collection<Integer> nullCols = new ArrayList<Integer>(); for (int i = 1; i <= md.getColumnCount(); i++) { Object val = null; try { val = rset.getObject(i); } catch (SQLException e) { LOGGER.error("Error while fetching column value : " + e.getMessage(), e); val = e.getMessage(); } final String strVal = strVal(val); colValues[i - 1] = strVal; // Building list of null columns if (val == null) { nullCols.add(i - 1); } // Updating max sizes final int colWidth = colMaxWidth[i - 1]; if (strVal.length() * 8 > colWidth) { colMaxWidth[i - 1] = strVal.length() * 8; } } // Adding the row as a command bufferedCommands.add(buildAddRowCommand(colValues, sqlView, nullCols)); // Flushing to display every N lines if (bufferedCommands.size() > MAX_ROWS_BEFORE_REFRESH) { bufferedCommands.add(buildAdjustWidthCommand(sqlView, colMaxWidth)); syncProcessCommands(bufferedCommands); } } // Flushing any left row bufferedCommands.add(buildAdjustWidthCommand(sqlView, colMaxWidth)); final Date afterFetchDate = new Date(); final int nbRows = rows; bufferedCommands.add(new ICommand() { @Override public String getName() { // TODO Auto-generated method stub return null; } @Override public Object execute(Object... parameters) { long fetchTime = afterFetchDate.getTime() - afterExecDate.getTime(); timeLabel.setText( MessageFormat.format(SQLClientMessages.getString("sql.executionFetchTime"), execTime, fetchTime)); rowsCount.setText(MessageFormat .format(SQLClientMessages.getString("sql.fetchedRows"), nbRows)); return null; } }); syncProcessCommands(bufferedCommands); } catch (final SQLException e) { PlatformUI.getWorkbench().getDisplay().syncExec(new Runnable() { @Override public void run() { if (!sqlView.isDisposed()) { sqlView.removeAll(); for (TableColumn c : sqlView.getColumns()) { c.dispose(); } TableColumn c = new TableColumn(sqlView, SWT.NONE); c.setText("SQL Exception " + e.getErrorCode()); c.setWidth(300); TableItem i = new TableItem(sqlView, SWT.NONE); i.setText(e.getMessage()); } } }); // throw new ErrorException(e); } finally { try { if (r != null) {// && !r.isClosed()) { r.close(); } if (s != null) { // && !s.isClosed()) { s.close(); } } catch (SQLException e) { throw new ErrorException(e); } finally { PlatformUI.getWorkbench().getDisplay().asyncExec(new Runnable() { @Override public void run() { // If the user has closed his SQL Query editor, we will // fall here (exception) with a disposed button if (runSQLButton != null && !runSQLButton.isDisposed()) { runSQLButton.setEnabled(true); } } }); } } return Status.OK_STATUS; } }; runSQLButton.setEnabled(false); refreshJob.schedule(); // } catch(SQLException e) { // throw new ErrorException(e); } finally { // try { // if(stmt != null && !stmt.isClosed()) { // stmt.close(); // } // if(rset != null && !rset.isClosed()) { // rset.close(); // } // } catch(SQLException e) { // throw new ErrorException(e); // } } }
From source file:com.flexive.core.storage.GenericDivisionExporter.java
/** * Dump a generic table to XML// ww w . ja v a2 s .c o m * * @param tableName name of the table * @param stmt an open statement * @param out output stream * @param sb an available and valid StringBuilder * @param xmlTag name of the xml tag to write per row * @param idColumn (optional) id column to sort results * @param onlyBinaries process binary fields (else these will be ignored) * @throws SQLException on errors * @throws IOException on errors */ private void dumpTable(String tableName, Statement stmt, OutputStream out, StringBuilder sb, String xmlTag, String idColumn, boolean onlyBinaries) throws SQLException, IOException { ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName + (StringUtils.isEmpty(idColumn) ? "" : " ORDER BY " + idColumn + " ASC")); final ResultSetMetaData md = rs.getMetaData(); String value, att; boolean hasSubTags; while (rs.next()) { hasSubTags = false; if (!onlyBinaries) { sb.setLength(0); sb.append(" <").append(xmlTag); } for (int i = 1; i <= md.getColumnCount(); i++) { value = null; att = md.getColumnName(i).toLowerCase(); switch (md.getColumnType(i)) { case java.sql.Types.DECIMAL: case java.sql.Types.NUMERIC: case java.sql.Types.BIGINT: if (!onlyBinaries) { value = String.valueOf(rs.getBigDecimal(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.INTEGER: case java.sql.Types.SMALLINT: case java.sql.Types.TINYINT: if (!onlyBinaries) { value = String.valueOf(rs.getLong(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: case java.sql.Types.REAL: if (!onlyBinaries) { value = String.valueOf(rs.getDouble(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: if (!onlyBinaries) { final Timestamp ts = rs.getTimestamp(i); if (rs.wasNull()) value = null; else value = FxFormatUtils.getDateTimeFormat().format(ts); } break; case java.sql.Types.BIT: case java.sql.Types.CHAR: case java.sql.Types.BOOLEAN: if (!onlyBinaries) { value = rs.getBoolean(i) ? "1" : "0"; if (rs.wasNull()) value = null; } break; case java.sql.Types.CLOB: case java.sql.Types.BLOB: case java.sql.Types.LONGVARBINARY: case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARBINARY: case java.sql.Types.VARCHAR: case java.sql.Types.BINARY: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: hasSubTags = true; break; default: LOG.warn("Unhandled type [" + md.getColumnType(i) + "] for [" + tableName + "." + att + "]"); } if (value != null && !onlyBinaries) sb.append(' ').append(att).append("=\"").append(value).append("\""); } if (hasSubTags) { if (!onlyBinaries) sb.append(">\n"); for (int i = 1; i <= md.getColumnCount(); i++) { switch (md.getColumnType(i)) { case java.sql.Types.VARBINARY: case java.sql.Types.LONGVARBINARY: case java.sql.Types.BLOB: case java.sql.Types.BINARY: if (idColumn == null) throw new IllegalArgumentException("Id column required to process binaries!"); String binFile = FOLDER_BINARY + "/BIN_" + String.valueOf(rs.getLong(idColumn)) + "_" + i + ".blob"; att = md.getColumnName(i).toLowerCase(); if (onlyBinaries) { if (!(out instanceof ZipOutputStream)) throw new IllegalArgumentException( "out has to be a ZipOutputStream to store binaries!"); ZipOutputStream zip = (ZipOutputStream) out; InputStream in = rs.getBinaryStream(i); if (rs.wasNull()) break; ZipEntry ze = new ZipEntry(binFile); zip.putNextEntry(ze); byte[] buffer = new byte[4096]; int read; while ((read = in.read(buffer)) != -1) zip.write(buffer, 0, read); in.close(); zip.closeEntry(); zip.flush(); } else { InputStream in = rs.getBinaryStream(i); //need to fetch to see if it is empty if (rs.wasNull()) break; in.close(); sb.append(" <").append(att).append(">").append(binFile).append("</").append(att) .append(">\n"); } break; case java.sql.Types.CLOB: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARCHAR: if (!onlyBinaries) { value = rs.getString(i); if (rs.wasNull()) break; att = md.getColumnName(i).toLowerCase(); sb.append(" <").append(att).append('>'); escape(sb, value); sb.append("</").append(att).append(">\n"); } break; } } if (!onlyBinaries) sb.append(" </").append(xmlTag).append(">\n"); } else { if (!onlyBinaries) sb.append("/>\n"); } if (!onlyBinaries) write(out, sb); } }
From source file:com.thinkbiganalytics.hive.service.HiveService.java
public QueryResult query(String query) throws DataAccessException { final DefaultQueryResult queryResult = new DefaultQueryResult(query); final List<QueryResultColumn> columns = new ArrayList<>(); final Map<String, Integer> displayNameMap = new HashMap<>(); if (query != null && !query.toLowerCase().startsWith("show")) { query = safeQuery(query);// ww w . j ava2s. co m } try { // Setting in order to query complex formats like parquet jdbcTemplate.execute("set hive.optimize.index.filter=false"); jdbcTemplate.query(query, new RowMapper<Map<String, Object>>() { @Override public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException { if (columns.isEmpty()) { ResultSetMetaData rsMetaData = rs.getMetaData(); for (int i = 1; i <= rsMetaData.getColumnCount(); i++) { String colName = rsMetaData.getColumnName(i); DefaultQueryResultColumn column = new DefaultQueryResultColumn(); column.setField(rsMetaData.getColumnName(i)); String displayName = rsMetaData.getColumnLabel(i); column.setHiveColumnLabel(displayName); //remove the table name if it exists displayName = StringUtils.substringAfterLast(displayName, "."); Integer count = 0; if (displayNameMap.containsKey(displayName)) { count = displayNameMap.get(displayName); count++; } displayNameMap.put(displayName, count); column.setDisplayName(displayName + "" + (count > 0 ? count : "")); column.setTableName(StringUtils.substringAfterLast(rsMetaData.getColumnName(i), ".")); column.setDataType(ParserHelper.sqlTypeToHiveType(rsMetaData.getColumnType(i))); columns.add(column); } queryResult.setColumns(columns); } Map<String, Object> row = new LinkedHashMap<>(); for (QueryResultColumn column : columns) { row.put(column.getDisplayName(), rs.getObject(column.getHiveColumnLabel())); } queryResult.addRow(row); return row; } }); } catch (DataAccessException dae) { dae.printStackTrace(); throw dae; } return queryResult; }
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 ww w .java 2 s. c om*/ 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; }
From source file:org.seasar.dbflute.logic.sql2entity.cmentity.DfCustomizeEntityMetaExtractor.java
public Map<String, DfColumnMeta> extractColumnMetaInfoMap(ResultSet rs, String sql, DfForcedJavaNativeProvider forcedJavaNativeProvider) throws SQLException { final Map<String, DfColumnMeta> columnMetaInfoMap = StringKeyMap.createAsFlexibleOrdered(); final ResultSetMetaData md = rs.getMetaData(); for (int i = 1; i <= md.getColumnCount(); i++) { final DfColumnMeta metaInfo = new DfColumnMeta(); String sql2EntityRelatedTableName = null; try {/*from w w w .ja v a 2 s.com*/ sql2EntityRelatedTableName = md.getTableName(i); } catch (SQLException ignored) { // Because this table name is not required. This is for classification. String msg = "ResultSetMetaData.getTableName(" + i + ") threw the exception:"; msg = msg + " " + ignored.getMessage(); _log.info(msg); } metaInfo.setSql2EntityRelatedTableName(sql2EntityRelatedTableName); String columnName = md.getColumnLabel(i); final String relatedColumnName = md.getColumnName(i); metaInfo.setSql2EntityRelatedColumnName(relatedColumnName); if (columnName == null || columnName.trim().length() == 0) { columnName = relatedColumnName; } if (columnName == null || columnName.trim().length() == 0) { final String ln = ln(); String msg = "The columnName is invalid: columnName=" + columnName + ln; msg = msg + "ResultSetMetaData returned invalid value." + ln; msg = msg + "sql=" + sql; throw new IllegalStateException(msg); } metaInfo.setColumnName(columnName); final int columnType = md.getColumnType(i); metaInfo.setJdbcDefValue(columnType); final String columnTypeName = md.getColumnTypeName(i); metaInfo.setDbTypeName(columnTypeName); int columnSize = md.getPrecision(i); if (!DfColumnExtractor.isColumnSizeValid(columnSize)) { // ex) sum(COLUMN) columnSize = md.getColumnDisplaySize(i); } metaInfo.setColumnSize(columnSize); final int scale = md.getScale(i); metaInfo.setDecimalDigits(scale); if (forcedJavaNativeProvider != null) { final String sql2entityForcedJavaNative = forcedJavaNativeProvider.provide(columnName); metaInfo.setSql2EntityForcedJavaNative(sql2entityForcedJavaNative); } // column comment is not set here (no comment on meta data) // if select column comment is specified, comment will be set later columnMetaInfoMap.put(columnName, metaInfo); } return columnMetaInfoMap; }
From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java
public void testResultSetMetaData() throws SQLException { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select c1, c2, c3, c4, c5 as a, c6, c7, c8, c9, c10, c11, c12, " + "c1*2, sentences(null, null, null) as b, c17, c18, c20 from " + dataTypeTableName + " limit 1"); ResultSetMetaData meta = res.getMetaData(); ResultSet colRS = con.getMetaData().getColumns(null, null, dataTypeTableName.toLowerCase(), null); assertEquals(17, meta.getColumnCount()); assertTrue(colRS.next());// w w w . j a v a 2 s .c om assertEquals("c1", meta.getColumnName(1)); assertEquals(Types.INTEGER, meta.getColumnType(1)); assertEquals("int", meta.getColumnTypeName(1)); assertEquals(11, meta.getColumnDisplaySize(1)); assertEquals(10, meta.getPrecision(1)); assertEquals(0, meta.getScale(1)); assertEquals("c1", colRS.getString("COLUMN_NAME")); assertEquals(Types.INTEGER, colRS.getInt("DATA_TYPE")); assertEquals("int", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(1), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(1), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c2", meta.getColumnName(2)); assertEquals("boolean", meta.getColumnTypeName(2)); assertEquals(Types.BOOLEAN, meta.getColumnType(2)); assertEquals(1, meta.getColumnDisplaySize(2)); assertEquals(1, meta.getPrecision(2)); assertEquals(0, meta.getScale(2)); assertEquals("c2", colRS.getString("COLUMN_NAME")); assertEquals(Types.BOOLEAN, colRS.getInt("DATA_TYPE")); assertEquals("boolean", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(2), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(2), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c3", meta.getColumnName(3)); assertEquals(Types.DOUBLE, meta.getColumnType(3)); assertEquals("double", meta.getColumnTypeName(3)); assertEquals(25, meta.getColumnDisplaySize(3)); assertEquals(15, meta.getPrecision(3)); assertEquals(15, meta.getScale(3)); assertEquals("c3", colRS.getString("COLUMN_NAME")); assertEquals(Types.DOUBLE, colRS.getInt("DATA_TYPE")); assertEquals("double", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(3), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(3), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c4", meta.getColumnName(4)); assertEquals(Types.VARCHAR, meta.getColumnType(4)); assertEquals("string", meta.getColumnTypeName(4)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(4)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(4)); assertEquals(0, meta.getScale(4)); assertEquals("c4", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("string", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(4), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(4), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("a", meta.getColumnName(5)); assertEquals(Types.VARCHAR, meta.getColumnType(5)); assertEquals("string", meta.getColumnTypeName(5)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(5)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(5)); assertEquals(0, meta.getScale(5)); assertEquals("c5", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("array<int>", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(5), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(5), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c6", meta.getColumnName(6)); assertEquals(Types.VARCHAR, meta.getColumnType(6)); assertEquals("string", meta.getColumnTypeName(6)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(6)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(6)); assertEquals(0, meta.getScale(6)); assertEquals("c6", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("map<int,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(6), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(6), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c7", meta.getColumnName(7)); assertEquals(Types.VARCHAR, meta.getColumnType(7)); assertEquals("string", meta.getColumnTypeName(7)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(7)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(7)); assertEquals(0, meta.getScale(7)); assertEquals("c7", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("map<string,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(7), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(7), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c8", meta.getColumnName(8)); assertEquals(Types.VARCHAR, meta.getColumnType(8)); assertEquals("string", meta.getColumnTypeName(8)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(8)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(8)); assertEquals(0, meta.getScale(8)); assertEquals("c8", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("struct<r:string,s:int,t:double>", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(8), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(8), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c9", meta.getColumnName(9)); assertEquals(Types.TINYINT, meta.getColumnType(9)); assertEquals("tinyint", meta.getColumnTypeName(9)); assertEquals(4, meta.getColumnDisplaySize(9)); assertEquals(3, meta.getPrecision(9)); assertEquals(0, meta.getScale(9)); assertEquals("c9", colRS.getString("COLUMN_NAME")); assertEquals(Types.TINYINT, colRS.getInt("DATA_TYPE")); assertEquals("tinyint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(9), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(9), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c10", meta.getColumnName(10)); assertEquals(Types.SMALLINT, meta.getColumnType(10)); assertEquals("smallint", meta.getColumnTypeName(10)); assertEquals(6, meta.getColumnDisplaySize(10)); assertEquals(5, meta.getPrecision(10)); assertEquals(0, meta.getScale(10)); assertEquals("c10", colRS.getString("COLUMN_NAME")); assertEquals(Types.SMALLINT, colRS.getInt("DATA_TYPE")); assertEquals("smallint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(10), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(10), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c11", meta.getColumnName(11)); assertEquals(Types.FLOAT, meta.getColumnType(11)); assertEquals("float", meta.getColumnTypeName(11)); assertEquals(24, meta.getColumnDisplaySize(11)); assertEquals(7, meta.getPrecision(11)); assertEquals(7, meta.getScale(11)); assertEquals("c11", colRS.getString("COLUMN_NAME")); assertEquals(Types.FLOAT, colRS.getInt("DATA_TYPE")); assertEquals("float", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(11), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(11), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c12", meta.getColumnName(12)); assertEquals(Types.BIGINT, meta.getColumnType(12)); assertEquals("bigint", meta.getColumnTypeName(12)); assertEquals(20, meta.getColumnDisplaySize(12)); assertEquals(19, meta.getPrecision(12)); assertEquals(0, meta.getScale(12)); assertEquals("c12", colRS.getString("COLUMN_NAME")); assertEquals(Types.BIGINT, colRS.getInt("DATA_TYPE")); assertEquals("bigint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(12), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(12), colRS.getInt("DECIMAL_DIGITS")); assertEquals("_c12", meta.getColumnName(13)); assertEquals(Types.INTEGER, meta.getColumnType(13)); assertEquals("int", meta.getColumnTypeName(13)); assertEquals(11, meta.getColumnDisplaySize(13)); assertEquals(10, meta.getPrecision(13)); assertEquals(0, meta.getScale(13)); assertEquals("b", meta.getColumnName(14)); assertEquals(Types.VARCHAR, meta.getColumnType(14)); assertEquals("string", meta.getColumnTypeName(14)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(14)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(14)); assertEquals(0, meta.getScale(14)); assertEquals("c17", meta.getColumnName(15)); assertEquals(Types.TIMESTAMP, meta.getColumnType(15)); assertEquals("timestamp", meta.getColumnTypeName(15)); assertEquals(29, meta.getColumnDisplaySize(15)); assertEquals(29, meta.getPrecision(15)); assertEquals(9, meta.getScale(15)); assertEquals("c18", meta.getColumnName(16)); assertEquals(Types.DECIMAL, meta.getColumnType(16)); assertEquals("decimal", meta.getColumnTypeName(16)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(16)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(16)); assertEquals(Integer.MAX_VALUE, meta.getScale(16)); assertEquals("c20", meta.getColumnName(17)); assertEquals(Types.DATE, meta.getColumnType(17)); assertEquals("date", meta.getColumnTypeName(17)); assertEquals(10, meta.getColumnDisplaySize(17)); assertEquals(10, meta.getPrecision(17)); assertEquals(0, meta.getScale(17)); for (int i = 1; i <= meta.getColumnCount(); i++) { assertFalse(meta.isAutoIncrement(i)); assertFalse(meta.isCurrency(i)); assertEquals(ResultSetMetaData.columnNullable, meta.isNullable(i)); } }
From source file:org.cloudgraph.rdb.service.GraphQuery.java
private List<List<PropertyPair>> findResults(Query query, SelectionCollector collector, PlasmaType type, Connection con) {//from w w w . ja v a2s . c om Object[] params = new Object[0]; RDBDataConverter converter = RDBDataConverter.INSTANCE; AliasMap aliasMap = new AliasMap(type); // construct a filter adding to alias map RDBFilterAssembler filterAssembler = null; Where where = query.findWhereClause(); if (where != null) { filterAssembler = new RDBFilterAssembler(where, type, aliasMap); params = filterAssembler.getParams(); } RDBOrderingAssembler orderingDeclAssembler = null; OrderBy orderby = query.findOrderByClause(); if (orderby != null) orderingDeclAssembler = new RDBOrderingAssembler(orderby, type, aliasMap); RDBGroupingAssembler groupingDeclAssembler = null; GroupBy groupby = query.findGroupByClause(); if (groupby != null) groupingDeclAssembler = new RDBGroupingAssembler(groupby, type, aliasMap); String rootAlias = aliasMap.getAlias(type); StringBuilder sqlQuery = new StringBuilder(); sqlQuery.append("SELECT DISTINCT "); // FIXME: only necessary if // FIXME: determine if any selected column(s) are LOB and don't use // DISTINCT in this case boolean hasLob = false; int i = 0; Set<Property> props = collector.getProperties(type); for (Property prop : props) { if (prop.isMany() && !prop.getType().isDataType()) continue; if (i > 0) sqlQuery.append(", "); sqlQuery.append(rootAlias); sqlQuery.append("."); sqlQuery.append(((PlasmaProperty) prop).getPhysicalName()); i++; } // construct a FROM clause from alias map sqlQuery.append(" FROM "); Iterator<PlasmaType> it = aliasMap.getTypes(); int count = 0; while (it.hasNext()) { PlasmaType aliasType = it.next(); String alias = aliasMap.getAlias(aliasType); if (count > 0) sqlQuery.append(", "); sqlQuery.append(this.statementUtil.getQualifiedPhysicalName(aliasType)); sqlQuery.append(" "); sqlQuery.append(alias); count++; } // append WHERE filter if (filterAssembler != null) { sqlQuery.append(" "); sqlQuery.append(filterAssembler.getFilter()); } if (orderingDeclAssembler != null) { sqlQuery.append(" "); sqlQuery.append(orderingDeclAssembler.getOrderingDeclaration()); } if (groupingDeclAssembler != null) { sqlQuery.append(" "); sqlQuery.append(groupingDeclAssembler.getGroupingDeclaration()); } // set the result range RDBMSVendorName vendor = PlasmaRuntime.getInstance().getRDBMSProviderVendor(DataAccessProviderName.JDBC); switch (vendor) { case ORACLE: if (query.getStartRange() != null && query.getEndRange() != null) { long offset = query.getStartRange() - 1; // inclusive if (offset < 0) offset = 0; long rowcount = query.getEndRange() - offset; StringBuilder buf = new StringBuilder(); // Pagination wrapper making sure ordering occurs before any // ROWNUM selected by using // a nested SELECT. if (offset == 0) { buf.append("SELECT * FROM ("); buf.append(sqlQuery); buf.append(") WHERE ROWNUM <= "); buf.append(rowcount); } else { // For offsets uses limiting condition on ROWNUM itself // as well as a // ROWNUM alias to enable Oracle STOPKEY processing // which helps performance. buf.append("SELECT * FROM (SELECT "); buf.append(PAGE_ALIAS); buf.append(".*, ROWNUM AS "); buf.append(ROWNUM_ALIAS); buf.append(" FROM ("); buf.append(sqlQuery); buf.append(") "); buf.append(PAGE_ALIAS); buf.append(") "); buf.append("WHERE "); buf.append(ROWNUM_ALIAS); buf.append(" >= "); buf.append(query.getStartRange()); buf.append(" AND ROWNUM <= "); buf.append(rowcount); } sqlQuery = buf; } break; case MYSQL: if (query.getStartRange() != null && query.getEndRange() != null) { long offset = query.getStartRange() - 1; // inclusive if (offset < 0) offset = 0; long rowcount = query.getEndRange() - offset; sqlQuery.append(" LIMIT "); // e.g. LIMIT offset,numrows sqlQuery.append(String.valueOf(offset)); sqlQuery.append(","); sqlQuery.append(String.valueOf(rowcount)); } break; default: } List<List<PropertyPair>> rows = new ArrayList<List<PropertyPair>>(); PreparedStatement statement = null; ResultSet rs = null; try { statement = con.prepareStatement(sqlQuery.toString(), ResultSet.TYPE_FORWARD_ONLY, /* * ResultSet * . * TYPE_SCROLL_INSENSITIVE * , */ ResultSet.CONCUR_READ_ONLY); // statement.setFetchSize(32); // log.debug("setting fetch size 32"); // set params // FIXME: params are pre-converted // to string in filter assembly int paramCount = 0; if (filterAssembler != null) { params = filterAssembler.getParams(); if (params != null) { paramCount = params.length; for (i = 0; i < params.length; i++) statement.setObject(i + 1, params[i]); } } // execute long before = System.currentTimeMillis(); statement.execute(); long after = System.currentTimeMillis(); if (log.isDebugEnabled()) { if (params == null || params.length == 0) { log.debug("executed: " + sqlQuery.toString() + " (" + String.valueOf(after - before) + ")"); } else { StringBuilder paramBuf = new StringBuilder(); paramBuf.append(" ["); for (int p = 0; p < params.length; p++) { if (p > 0) paramBuf.append(", "); paramBuf.append(String.valueOf(params[p])); } paramBuf.append("]"); log.debug("executed: " + sqlQuery.toString() + " " + paramBuf.toString() + " (" + String.valueOf(after - before) + ")"); } } // read results before = System.currentTimeMillis(); int numresults = 0; rs = statement.getResultSet(); int numcols = rs.getMetaData().getColumnCount(); ResultSetMetaData rsMeta = rs.getMetaData(); List<PropertyPair> row = null; PropertyPair pair = null; while (rs.next()) { row = new ArrayList<PropertyPair>(); rows.add(row); for (i = 1; i <= numcols; i++) { String columnName = rsMeta.getColumnLabel(i); // mysql 5.5 // returns // original // table col // name for // views if (columnName == null) columnName = rsMeta.getColumnName(i); if (ROWNUM_ALIAS.equals(columnName)) continue; int columnType = rsMeta.getColumnType(i); PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName); PlasmaProperty valueProp = prop; while (!valueProp.getType().isDataType()) { valueProp = this.statementUtil.getOppositePriKeyProperty(valueProp); } Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp); if (value != null) { pair = new PropertyPair(prop, value); pair.setColumn(i); if (!valueProp.equals(prop)) pair.setValueProp(valueProp); row.add(pair); } } numresults++; } after = System.currentTimeMillis(); if (log.isDebugEnabled()) log.debug("read " + numresults + " results (" + String.valueOf(after - before) + ")"); } catch (Throwable t) { StringBuffer buf = this.generateErrorDetail(t, sqlQuery.toString(), filterAssembler); log.error(buf.toString()); throw new DataAccessException(t); } finally { try { if (rs != null) rs.close(); if (statement != null) statement.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } } return rows; }
From source file:es.juntadeandalucia.panelGestion.negocio.utiles.JDBCConnector.java
public List<ColumnVO> getColumnsMetaDataExceptGeom(String sql, String geometryName) throws Exception { Exception error = null;//from w ww.j a v a 2 s.c o m List<ColumnVO> tableColumns = new LinkedList<ColumnVO>(); Connection connection = null; PreparedStatement preparedStmnt = null; try { DataSource dataSource = poolDataSources.get(schemaId); connection = dataSource.getConnection(); connection.setAutoCommit(false); preparedStmnt = connection.prepareStatement(sql); ResultSet rs = preparedStmnt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); String geometryFieldName = geometryName; if (StringUtils.isEmpty(geometryFieldName)) { geometryFieldName = "the_geom"; } for (int i = 1; i <= rsmd.getColumnCount(); i++) { String columnName = rsmd.getColumnName(i); if (!columnName.equals(geometryFieldName)) { String columnType = rsmd.getColumnTypeName(i); int columnSqlType = rsmd.getColumnType(i); int columnLength = rsmd.getColumnDisplaySize(i); int columnPrecision = rsmd.getPrecision(i); ColumnVO column = new ColumnVO(); column.setNameOnTable(columnName); column.setType(columnType); column.setSqlType(columnSqlType); column.setLength(columnLength); column.setPrecision(columnPrecision); column.setInTable(true); tableColumns.add(column); } } } catch (SQLException e) { error = e; } finally { if (preparedStmnt != null) { try { preparedStmnt.close(); } catch (SQLException se2) { log.warn("No se pudo cerrar el statment: ".concat(se2.getLocalizedMessage())); } } if (connection != null) { try { if (error != null) { connection.rollback(); } } catch (SQLException se) { log.warn("Se produjo un error al manejar la conexin: ".concat(se.getLocalizedMessage())); } try { connection.close(); } catch (SQLException se) { log.warn("Se produjo un error al intentar cerrar la conexin: " .concat(se.getLocalizedMessage())); } } } if (error != null) { throw error; } return tableColumns; }