List of usage examples for java.sql ResultSetMetaData getColumnName
String getColumnName(int column) throws SQLException;
From source file:org.codehaus.mojo.sql.SqlExecMojo.java
/** * print any results in the result set.//from w ww . j a v a2 s . c om * @param rs the resultset to print information about * @param out the place to print results * @throws SQLException on SQL problems. */ private void printResultSet(ResultSet rs, PrintStream out) throws SQLException { if (rs != null) { getLog().debug("Processing new result set."); ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); StringBuffer line = new StringBuffer(); if (showheaders) { boolean first = true; for (int col = 1; col <= columnCount; col++) { String columnValue = md.getColumnName(col); if (columnValue != null) { columnValue = columnValue.trim(); if (",".equals(outputDelimiter)) { columnValue = StringEscapeUtils.escapeCsv(columnValue); } } if (first) { first = false; } else { line.append(outputDelimiter); } line.append(columnValue); } out.println(line); line = new StringBuffer(); } while (rs.next()) { boolean first = true; for (int col = 1; col <= columnCount; col++) { String columnValue = rs.getString(col); if (columnValue != null) { columnValue = columnValue.trim(); if (",".equals(outputDelimiter)) { columnValue = StringEscapeUtils.escapeCsv(columnValue); } } if (first) { first = false; } else { line.append(outputDelimiter); } line.append(columnValue); } out.println(line); line = new StringBuffer(); } } out.println(); }
From source file:org.apache.ctakes.ytex.uima.mapper.DocumentMapperServiceImpl.java
public void initDocKeyMapping() { AbstractEntityPersister cm = (AbstractEntityPersister) this.sessionFactory.getClassMetadata(Document.class); // figure out which columns are already mapped String[] propNames = cm.getPropertyNames(); Set<String> mappedCols = new TreeSet<String>(String.CASE_INSENSITIVE_ORDER); for (String prop : propNames) { String cols[] = cm.getPropertyColumnNames(prop); mappedCols.addAll(Arrays.asList(cols)); }// w w w.j av a2s . c o m // this.formattedTableName = DBUtil.formatTableName(cm.getTableName()); this.formattedTableName = cm.getTableName(); log.info("document table name = " + formattedTableName); final String query = "select * from " + formattedTableName + " where 1=2"; Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(query); ResultSetMetaData rsmd = rs.getMetaData(); int nCols = rsmd.getColumnCount(); for (int i = 1; i <= nCols; i++) { String colName = rsmd.getColumnName(i); if (!mappedCols.contains(colName)) { log.info("document candidate foreign key column: " + colName); docTableCols.put(colName, rsmd.getColumnType(i)); } } if (log.isDebugEnabled()) { log.debug("docTableCols: " + docTableCols); } } catch (SQLException e) { log.error("problem determining document table fields", e); throw new RuntimeException(e); } finally { try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } try { if (stmt != null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); } try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
From source file:com.egt.core.db.xdp.RecursoCachedRowSet.java
private String findColumn(int columnIndex) { try {/*from w ww .j av a2 s . c o m*/ ResultSetMetaData rsmd = getMetaData(); return rsmd.getColumnName(columnIndex); } catch (SQLException ex) { TLC.getBitacora().fatal(ex); } return null; }
From source file:org.agnitas.dao.impl.RecipientDaoImpl.java
@Override public CaseInsensitiveMap<CsvColInfo> readDBColumns(int companyID) { String sqlGetTblStruct = "SELECT * FROM customer_" + companyID + "_tbl WHERE 1=0"; CsvColInfo aCol = null;/*from w w w. j a va 2s.c o m*/ int colType; CaseInsensitiveMap<CsvColInfo> dbAllColumns = new CaseInsensitiveMap<CsvColInfo>(); DataSource ds = (DataSource) this.applicationContext.getBean("dataSource"); Connection con = DataSourceUtils.getConnection(ds); try { Statement stmt = con.createStatement(); ResultSet rset = stmt.executeQuery(sqlGetTblStruct); ResultSetMetaData meta = rset.getMetaData(); for (int i = 1; i <= meta.getColumnCount(); i++) { if (!meta.getColumnName(i).equals("change_date") && !meta.getColumnName(i).equals("creation_date") && !meta.getColumnName(i).equals("datasource_id")) { // if (meta.getColumnName(i).equals("customer_id")) { // if (status == null) { // initStatus(getWebApplicationContext()); // } // if (!( mode == ImportWizardServiceImpleImpl.MODE_ONLY_UPDATE && status.getKeycolumn().equals("customer_id"))) { // continue; // } // } aCol = new CsvColInfo(); aCol.setName(meta.getColumnName(i)); aCol.setLength(meta.getColumnDisplaySize(i)); aCol.setType(CsvColInfo.TYPE_UNKNOWN); aCol.setActive(false); aCol.setNullable(meta.isNullable(i) != 0); colType = meta.getColumnType(i); aCol.setType(dbTypeToCsvType(colType)); dbAllColumns.put(meta.getColumnName(i), aCol); } } rset.close(); stmt.close(); } catch (Exception e) { logger.error("readDBColumns (companyID: " + companyID + ")", e); } DataSourceUtils.releaseConnection(con, ds); return dbAllColumns; }
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 . j av a2 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.alinous.plugin.derby.DerbyDataSource.java
private List<Record> executeSelectSQL(Object connectionHandle, String sql, LimitOffsetClause limit, PostContext context, VariableRepository provider, AdjustWhere adjWhere, TypeHelper helper) throws DataSourceException, ExecutionException { Connection con = (Connection) connectionHandle; Statement stmt = null;/* w w w .j ava 2 s . c om*/ List<Record> retList = new LinkedList<Record>(); try { stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.execute(sql); ResultSet rs = stmt.getResultSet(); ResultSetMetaData metaData = rs.getMetaData(); if (limit != null && limit.isReady(context, provider, adjWhere)) { fetchWithOffset(rs, metaData, retList, limit, context, provider, adjWhere, helper); } else { while (rs.next()) { int cnt = metaData.getColumnCount(); Record rec = new Record(); for (int i = 0; i < cnt; i++) { String colName = metaData.getColumnName(i + 1).toUpperCase(); String value = rs.getString(i + 1); int colType = metaData.getColumnType(i + 1); rec.addFieldValue(colName, value, colType); } retList.add(rec); } } } catch (SQLException e) { throw new DataSourceException(e); } finally { try { stmt.close(); } catch (SQLException ignore) { } } return retList; }
From source file:net.certifi.audittablegen.GenericDMR.java
/** * Get List of ColumnDef objects for all tables * in the targeted database/schema/*from w ww. j a v a 2 s . c om*/ * * @param tableName * @return ArrayList of ColumnDef objects or an empty list if none are found. */ @Override public List getColumns(String tableName) { //getDataTypes will initialize the map if it isn't already loaded Map<String, DataTypeDef> dtds = getDataTypes(); List columns = new ArrayList<>(); try { Connection conn = dataSource.getConnection(); DatabaseMetaData dmd = conn.getMetaData(); ResultSet rs = dmd.getColumns(null, verifiedSchema, tableName, null); //load all of the metadata in the result set into a map for each column ResultSetMetaData rsmd = rs.getMetaData(); int metaDataColumnCount = rsmd.getColumnCount(); if (!rs.isBeforeFirst()) { throw new RuntimeException( "No results for DatabaseMetaData.getColumns(" + verifiedSchema + "." + tableName + ")"); } while (rs.next()) { ColumnDef columnDef = new ColumnDef(); Map columnMetaData = new CaseInsensitiveMap(); for (int i = 1; i <= metaDataColumnCount; i++) { columnMetaData.put(rsmd.getColumnName(i), rs.getString(i)); } columnDef.setName(rs.getString("COLUMN_NAME")); columnDef.setTypeName(rs.getString("TYPE_NAME")); columnDef.setSqlType(rs.getInt("DATA_TYPE")); columnDef.setSize(rs.getInt("COLUMN_SIZE")); columnDef.setDecimalSize(rs.getInt("DECIMAL_DIGITS")); columnDef.setSourceMeta(columnMetaData); if (dtds.containsKey(columnDef.getTypeName())) { columnDef.setDataTypeDef(dtds.get(columnDef.getTypeName())); } else { throw new RuntimeException( "Missing DATA_TYPE definition for data type " + columnDef.getTypeName()); } columns.add(columnDef); } } catch (SQLException e) { throw Throwables.propagate(e); } return columns; }
From source file:org.agnitas.util.AgnUtils.java
/** * Getter for property bshInterpreter./*from w w w . j ava2 s . c o m*/ * * @return Value of property bshInterpreter. */ public static Interpreter getBshInterpreter(int cID, int customerID, ApplicationContext con) { DataSource ds = (DataSource) con.getBean("dataSource"); Interpreter aBsh = new Interpreter(); NameSpace aNameSpace = aBsh.getNameSpace(); aNameSpace.importClass("org.agnitas.util.AgnUtils"); String sqlStatement = "select * from customer_" + cID + "_tbl cust where cust.customer_id=" + customerID; Connection dbCon = DataSourceUtils.getConnection(ds); try { Statement stmt = dbCon.createStatement(); ResultSet rset = stmt.executeQuery(sqlStatement); ResultSetMetaData aMeta = rset.getMetaData(); if (rset.next()) { for (int i = 1; i <= aMeta.getColumnCount(); i++) { switch (aMeta.getColumnType(i)) { case java.sql.Types.BIGINT: case java.sql.Types.INTEGER: case java.sql.Types.NUMERIC: case java.sql.Types.SMALLINT: case java.sql.Types.TINYINT: if (rset.getObject(i) != null) { aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.lang.Integer.class, new Integer(rset.getInt(i)), null); } else { aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.lang.Integer.class, null, null); } break; case java.sql.Types.DECIMAL: case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: if (rset.getObject(i) != null) { aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.lang.Double.class, new Double(rset.getDouble(i)), null); } else { aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.lang.Double.class, null, null); } break; case java.sql.Types.CHAR: case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARCHAR: aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.lang.String.class, rset.getString(i), null); break; case java.sql.Types.DATE: case java.sql.Types.TIME: case java.sql.Types.TIMESTAMP: aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.util.Date.class, rset.getTimestamp(i), null); break; default: logger.error("Ignoring: " + aMeta.getColumnName(i)); } } } rset.close(); stmt.close(); // add virtual column "sysdate" aNameSpace.setTypedVariable(AgnUtils.getHibernateDialect().getCurrentTimestampSQLFunctionName(), Date.class, new Date(), null); } catch (Exception e) { sendExceptionMail("Sql: " + sqlStatement, e); logger.error("getBshInterpreter: " + e.getMessage()); aBsh = null; } DataSourceUtils.releaseConnection(dbCon, ds); return aBsh; }
From source file:org.agnitas.dao.impl.RecipientDaoImpl.java
/** * Load complete Subscriber-Data from DB. customerID must be set first for this method. * * @return Map with Key/Value-Pairs of customer data *//*w w w. java 2s. c om*/ @Override public CaseInsensitiveMap<Object> getCustomerDataFromDb(int companyID, int customerID) { String aName = null; String aValue = null; int a; java.sql.Timestamp aTime = null; Recipient cust = (Recipient) applicationContext.getBean("Recipient"); if (cust.getCustParameters() == null) { cust.setCustParameters(new CaseInsensitiveMap<Object>()); } String getCust = "SELECT * FROM customer_" + companyID + "_tbl WHERE customer_id=" + customerID; if (cust.getCustDBStructure() == null) { cust.loadCustDBStructure(); } DataSource ds = (DataSource) this.applicationContext.getBean("dataSource"); Connection con = DataSourceUtils.getConnection(ds); try { Statement stmt = con.createStatement(); ResultSet rset = stmt.executeQuery(getCust); if (logger.isInfoEnabled()) { logger.info("getCustomerDataFromDb: " + getCust); } if (rset.next()) { ResultSetMetaData aMeta = rset.getMetaData(); for (a = 1; a <= aMeta.getColumnCount(); a++) { aValue = null; aName = aMeta.getColumnName(a).toLowerCase(); switch (aMeta.getColumnType(a)) { case java.sql.Types.TIMESTAMP: case java.sql.Types.TIME: case java.sql.Types.DATE: try { aTime = rset.getTimestamp(a); } catch (Exception e) { aTime = null; } if (aTime == null) { cust.getCustParameters().put(aName + "_DAY_DATE", ""); cust.getCustParameters().put(aName + "_MONTH_DATE", ""); cust.getCustParameters().put(aName + "_YEAR_DATE", ""); cust.getCustParameters().put(aName + "_HOUR_DATE", ""); cust.getCustParameters().put(aName + "_MINUTE_DATE", ""); cust.getCustParameters().put(aName + "_SECOND_DATE", ""); cust.getCustParameters().put(aName, ""); } else { GregorianCalendar aCal = new GregorianCalendar(); aCal.setTime(aTime); cust.getCustParameters().put(aName + "_DAY_DATE", Integer.toString(aCal.get(GregorianCalendar.DAY_OF_MONTH))); cust.getCustParameters().put(aName + "_MONTH_DATE", Integer.toString(aCal.get(GregorianCalendar.MONTH) + 1)); cust.getCustParameters().put(aName + "_YEAR_DATE", Integer.toString(aCal.get(GregorianCalendar.YEAR))); cust.getCustParameters().put(aName + "_HOUR_DATE", Integer.toString(aCal.get(GregorianCalendar.HOUR_OF_DAY))); cust.getCustParameters().put(aName + "_MINUTE_DATE", Integer.toString(aCal.get(GregorianCalendar.MINUTE))); cust.getCustParameters().put(aName + "_SECOND_DATE", Integer.toString(aCal.get(GregorianCalendar.SECOND))); SimpleDateFormat bdfmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cust.getCustParameters().put(aName, bdfmt.format(aCal.getTime())); } break; default: aValue = rset.getString(a); if (aValue == null) { aValue = ""; } cust.getCustParameters().put(aName, aValue); break; } } } rset.close(); stmt.close(); } catch (Exception e) { logger.error("getCustomerDataFromDb: " + getCust, e); AgnUtils.sendExceptionMail("sql:" + getCust, e); } DataSourceUtils.releaseConnection(con, ds); cust.setChangeFlag(false); Map<String, Object> result = cust.getCustParameters(); if (result instanceof CaseInsensitiveMap) { return (CaseInsensitiveMap<Object>) result; } else { return new CaseInsensitiveMap<Object>(result); } }
From source file:com.glaf.dts.transform.MxTransformManager.java
protected List<Map<String, Object>> prepare(QueryDefinition query) { logger.debug("-------------------------1 start------------------------"); List<Map<String, Object>> resultList = new java.util.ArrayList<Map<String, Object>>(); Connection conn = null;//from w w w . j a va 2 s. co m PreparedStatement psmt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; try { Database database = databaseService.getDatabaseById(query.getDatabaseId()); if (database != null) { conn = DBConnectionFactory.getConnection(database.getName()); } else { conn = DBConnectionFactory.getConnection(); } logger.debug("-------------------------1 connection------------------------"); String sql = QueryUtils.replaceSQLVars(query.getSql()); logger.debug(">sql=" + query.getSql()); psmt = conn.prepareStatement(sql); rs = psmt.executeQuery(); rsmd = rs.getMetaData(); logger.debug("-------------------------1 executeQuery------------------------"); int count = rsmd.getColumnCount(); while (rs.next()) { Map<String, Object> rowMap = new java.util.HashMap<String, Object>(); for (int i = 1; i <= count; i++) { String columnName = rsmd.getColumnLabel(i); if (null == columnName || 0 == columnName.length()) { columnName = rsmd.getColumnName(i); } try { rowMap.put(columnName, rs.getObject(i)); } catch (SQLException ex) { rowMap.put(columnName, rs.getString(i)); } } resultList.add(rowMap); } query.setResultList(resultList); // logger.debug(">resultList=" + resultList); return resultList; } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(rs); JdbcUtils.close(psmt); JdbcUtils.close(conn); logger.debug("-------------------------1 start------------------------"); } }