List of usage examples for java.sql ResultSetMetaData getColumnName
String getColumnName(int column) throws SQLException;
From source file:com.p5solutions.core.jpa.orm.EntityUtility.java
/** * Build the database-meta-dta for a given table entity, using an existing * connection.//from w ww. j av a 2 s .co m * * @param table * annotation * @param detail * {@link EntityDetail} probably provided by the * {@link #cacheEntityDetails} * @param connection * an existing mock or real, database connection. */ protected void buildColumnMetaData(Table table, EntityDetail<?> detail, Connection connection) { Statement stmt = null; ResultSet rs = null; try { String sql = "SELECT * FROM " + table.name() + " WHERE 1=0"; stmt = connection.createStatement(); // set the maximum result set to zero, just in-case!? stmt.setMaxRows(0); rs = stmt.executeQuery(sql); ResultSetMetaData rsMeta = rs.getMetaData(); logger.info("** Building Database MetaData for Table " + table.name()); for (int ic = 1; ic <= rsMeta.getColumnCount(); ic++) { String columnName = rsMeta.getColumnName(ic); ParameterBinder binder = detail.getParameterBinderByAny(columnName); if (binder == null) { if (logger.isErrorEnabled()) { String error = " -- Column " + columnName + " as defined by the table meta-data, cannot be found within the scope of " + detail.getEntityClass(); logger.error(error); } // TODO ?? throw new RuntimeException(new // NoColumnDefinedException(error)); } else { ParameterBinderColumnMetaData columnMetaData = new ParameterBinderColumnMetaData(); // columnMetaData.setColumnIndex(ic); // USELESS, EVERY UNIQUE QUERY // STRING WOULD RESULT IN A DIFFERENT INDEX. EASIER TO CACHE IT BASED // ON UNIQUE QUERY STRINGS. // columnMetaData.setColumnLabel(rsMeta.getColumnLabel(ic)); columnMetaData.setColumnName(columnName); columnMetaData.setLength(rsMeta.getColumnDisplaySize(ic)); columnMetaData.setPrecision(rsMeta.getPrecision(ic)); columnMetaData.setScale(rsMeta.getScale(ic)); columnMetaData.setColumnType(rsMeta.getColumnType(ic)); columnMetaData.setColumnTypeName(rsMeta.getColumnTypeName(ic)); binder.setColumnMetaData(columnMetaData); if (logger.isDebugEnabled()) { logger.debug(" -- [" + columnMetaData.toString() + "]"); } } } } catch (SQLException e) { logger.error(">> *UNABLE* to retrieve meta data for table " + table.name() + ", doesn't exist?"); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { ; } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { ; } stmt = null; } } }
From source file:org.cloudgraph.rdb.service.GraphQuery.java
private List<List<PropertyPair>> findResults(Query query, SelectionCollector collector, PlasmaType type, Connection con) {/*from w w w . j a v a 2s.co m*/ 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:com.glaf.dts.transform.MxTransformManager.java
@SuppressWarnings("unchecked") public TableDefinition toTableDefinition(QueryDefinition query, String currentSql) { if (query.getId() != null && query.getParentId() != null) { query = this.fill(query.getId(), currentSql); }/* w w w . j a v a 2s. c o m*/ if (query.getParentId() != null) { QueryDefinition parent = this.fill(query.getParentId(), null); if (parent != null) { logger.debug("parent:" + parent.getTitle()); logger.debug("resultList:" + parent.getResultList()); query.setParent(parent); } } String sql = currentSql; List<Object> values = null; logger.debug("currentSql:" + currentSql); if (query.getParentId() != null) { if (query.getParent() != null && query.getParent().getResultList() != null && !query.getParent().getResultList().isEmpty()) { for (Map<String, Object> paramMap : query.getParent().getResultList()) { SqlExecutor sqlExecutor = JdbcUtils.rebuildSQL(sql, paramMap); sql = sqlExecutor.getSql(); sql = QueryUtils.replaceSQLVars(sql); values = (List<Object>) sqlExecutor.getParameter(); break; } } } else { if (sql != null && sql.indexOf("${") != -1) { sql = QueryUtils.replaceSQLVars(sql); SqlExecutor sqlExecutor = JdbcUtils.rebuildSQL(sql, new java.util.HashMap<String, Object>()); if (sqlExecutor != null) { sql = sqlExecutor.getSql(); sql = QueryUtils.replaceSQLVars(sql); values = (List<Object>) sqlExecutor.getParameter(); } } } logger.debug("sql:" + sql); logger.debug("values:" + values); TableDefinition table = new TableDefinition(); Connection conn = null; 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(); } sql = QueryUtils.replaceSQLVars(sql); 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.setColumnName(rsmd.getColumnName(i)); column.setColumnLabel(rsmd.getColumnLabel(i)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(i)); column.setScale(rsmd.getScale(i)); table.addColumn(column); logger.debug("----------------------------------------"); logger.debug("sqlType:" + sqlType); logger.debug("javaType:" + FieldType.getJavaType(sqlType)); logger.debug("columnName:" + rsmd.getColumnName(i)); logger.debug("columnTypeName:" + rsmd.getColumnTypeName(i)); logger.debug("columnClassName:" + rsmd.getColumnClassName(i)); logger.debug("columnLabel:" + rsmd.getColumnLabel(i)); logger.debug("columnDisplaySize:" + rsmd.getColumnDisplaySize(i)); logger.debug("precision:" + rsmd.getPrecision(i)); logger.debug("scale:" + rsmd.getScale(i)); } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(rs); JdbcUtils.close(psmt); JdbcUtils.close(conn); } return table; }
From source file:org.apache.hadoop.chukwa.hicc.DatasetMapper.java
public void execute(String query, boolean groupBySecondColumn) { dataset.clear();// w w w . j a va 2 s.c om try { // The newInstance() call is a work around for some // broken Java implementations Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception ex) { // handle the error } Connection conn = null; Statement stmt = null; ResultSet rs = null; int counter = 0; int size = 0; labels.clear(); double max = 0.0; int labelsCount = 0; try { conn = DriverManager.getConnection(jdbc); stmt = conn.createStatement(); //rs = stmt.executeQuery(query); if (stmt.execute(query)) { rs = stmt.getResultSet(); ResultSetMetaData rmeta = rs.getMetaData(); int col = rmeta.getColumnCount(); int i = 0; java.util.ArrayList<Double> data = null; HashMap<String, Integer> xAxisMap = new HashMap<String, Integer>(); while (rs.next()) { String label = rs.getString(1); if (!xAxisMap.containsKey(label)) { xAxisMap.put(label, i); labels.add(label); i++; } if (groupBySecondColumn) { String item = rs.getString(2); // Get the data from the row using the series column double current = rs.getDouble(3); if (current > max) { max = current; } data = dataset.get(item); if (data == null) { data = new java.util.ArrayList<Double>(); } data.add(rs.getDouble(3)); dataset.put(item, data); } else { for (int j = 2; j <= col; j++) { String item = rmeta.getColumnName(j); // Get the data from the row using the column name double current = rs.getDouble(j); if (current > max) { max = current; } data = dataset.get(item); if (data == null) { data = new java.util.ArrayList<Double>(); } data.add(rs.getDouble(j)); dataset.put(item, data); } } } labelsCount = i; } else { log.error("query is not executed."); } // Now do something with the ResultSet .... } catch (SQLException ex) { // handle any errors log.error("SQLException: " + ex.getMessage()); log.error("SQLState: " + ex.getSQLState()); log.error("VendorError: " + ex.getErrorCode()); } catch (Exception ex) { } finally { // it is a good idea to release // resources in a finally{} block // in reverse-order of their creation // if they are no-longer needed if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { // ignore } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { // ignore } stmt = null; } if (conn != null) { try { conn.close(); } catch (SQLException sqlEx) { // ignore } conn = null; } } }
From source file:com.simplymeasured.prognosticator.ThreadedQueryRunnable.java
@Override public void run() { NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource); try {/*from w w w . j a v a 2s. c o m*/ template.query(query, parameters, new RowCallbackHandler() { @Override public void processRow(ResultSet resultSet) throws SQLException { try { Map<String, Object> result = Maps.newHashMap(); final ResultSetMetaData metadata = resultSet.getMetaData(); for (int i = 1; i <= metadata.getColumnCount(); i++) { String columnTypeName = metadata.getColumnTypeName(i); final Object value; if ("array".equalsIgnoreCase(columnTypeName)) { String stringValue = resultSet.getString(i); if (stringValue != null) { value = objectMapper.readValue(stringValue, List.class); } else { value = null; } } else if ("map".equalsIgnoreCase(columnTypeName) || "struct".equalsIgnoreCase(columnTypeName)) { String stringValue = resultSet.getString(i); if (stringValue != null) { value = objectMapper.readValue(stringValue, Map.class); } else { value = null; } } else { value = resultSet.getObject(i); } result.put(metadata.getColumnName(i), value); } resultQueue.put(result); } catch (SQLException se) { LOG.warn("Database error!", se); throw new RuntimeException("Database error!", se); } catch (InterruptedException ie) { LOG.warn("Query killed!", ie); throw new RuntimeException("Query killed!", ie); } catch (Exception ex) { LOG.warn("Unable to parse row!", ex); throw new RuntimeException("Unable to parse row!", ex); } } }); resultQueue.put(Collections.<String, Object>emptyMap()); } catch (DataAccessException dae) { try { resultQueue.put(Collections.<String, Object>emptyMap()); } catch (InterruptedException ie) { LOG.warn("Queue is dead!", ie); } LOG.warn("Unable to execute query - attempting to clean up", dae); } catch (InterruptedException ie) { LOG.warn("Queue is dead!", ie); } }
From source file:com.cnd.greencube.server.dao.jdbc.JdbcDAO.java
@SuppressWarnings({ "rawtypes", "unchecked" })
private Object fetchRowObject(ResultSet rs, Class clazz) throws SQLException {
ResultSetMetaData meta = rs.getMetaData();
Object obj = null;/* www . ja va 2 s. c om*/
try {
obj = ConstructorUtils.invokeConstructor(clazz, null);
} catch (Exception e) {
e.printStackTrace();
return null;
}
if (obj == null) {
throw new SQLException("Cannot instance object; " + clazz.getName());
}
int columnCount = meta.getColumnCount();
String columnName, propertyName;
Method m;
PropertyDescriptor pd;
Object value;
List<Column2Property> setterColumnsNames = getColumnsFromObj(obj, null);
for (int i = 1; i <= columnCount; i++) {
propertyName = null;
value = null;
columnName = meta.getColumnName(i);
for (Column2Property c : setterColumnsNames) {
if (c.columnName.equals(columnName)) {
propertyName = c.propertyName;
}
}
if (propertyName == null)
continue;
try {
pd = new PropertyDescriptor(propertyName, clazz);
} catch (Exception e) {
e.printStackTrace();
continue;
}
if (pd != null) {
m = pd.getWriteMethod();
Class[] classes = m.getParameterTypes();
Class c = classes[0];
try {
value = getColumnValue(rs, meta, i, c);
} catch (Exception e) {
e.printStackTrace();
continue;
}
if (null != value) {
try {
m.invoke(obj, value);
} catch (Exception e) {
e.printStackTrace();
continue;
}
}
}
}
return obj;
}
From source file:org.apache.kylin.rest.service.QueryService.java
/** * @param correctedSql//ww w . j a v a 2s . c om * @param sqlRequest * @return * @throws Exception */ private SQLResponse execute(String correctedSql, SQLRequest sqlRequest) throws Exception { Connection conn = null; Statement stat = null; ResultSet resultSet = null; Boolean isPushDown = false; List<List<String>> results = Lists.newArrayList(); List<SelectedColumnMeta> columnMetas = Lists.newArrayList(); try { conn = cacheService.getOLAPDataSource(sqlRequest.getProject()).getConnection(); // special case for prepare query. if (BackdoorToggles.getPrepareOnly()) { return getPrepareOnlySqlResponse(correctedSql, conn, isPushDown, results, columnMetas); } stat = conn.createStatement(); processStatementAttr(stat, sqlRequest); resultSet = stat.executeQuery(correctedSql); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); // Fill in selected column meta for (int i = 1; i <= columnCount; ++i) { columnMetas.add(new SelectedColumnMeta(metaData.isAutoIncrement(i), metaData.isCaseSensitive(i), metaData.isSearchable(i), metaData.isCurrency(i), metaData.isNullable(i), metaData.isSigned(i), metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i), metaData.getSchemaName(i), metaData.getCatalogName(i), metaData.getTableName(i), metaData.getPrecision(i), metaData.getScale(i), metaData.getColumnType(i), metaData.getColumnTypeName(i), metaData.isReadOnly(i), metaData.isWritable(i), metaData.isDefinitelyWritable(i))); } // fill in results while (resultSet.next()) { List<String> oneRow = Lists.newArrayListWithCapacity(columnCount); for (int i = 0; i < columnCount; i++) { oneRow.add((resultSet.getString(i + 1))); } results.add(oneRow); } } catch (SQLException sqlException) { isPushDown = PushDownUtil.doPushDownQuery(sqlRequest.getProject(), correctedSql, results, columnMetas, sqlException); } finally { close(resultSet, stat, conn); } return getSqlResponse(isPushDown, results, columnMetas); }
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());//from ww w . j a va 2s .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.apache.sqoop.repository.derby.DerbyTestCase.java
/** *Printout one single table.//from w ww . java2s . c o m * *@param table * Table name *@throws Exception */ protected void generateTableState(String table) throws Exception { PreparedStatement ps = null; ResultSet rs = null; ResultSetMetaData rsmt = null; try { ps = getDerbyDatabaseConnection().prepareStatement("SELECT * FROM " + table); rs = ps.executeQuery(); rsmt = rs.getMetaData(); StringBuilder sb = new StringBuilder(); System.out.println("Table " + table + ":"); for (int i = 1; i <= rsmt.getColumnCount(); i++) { sb.append("| ").append(rsmt.getColumnName(i)).append(" "); } sb.append("|"); System.out.println(sb.toString()); while (rs.next()) { sb = new StringBuilder(); for (int i = 1; i <= rsmt.getColumnCount(); i++) { sb.append("| ").append(rs.getString(i)).append(" "); } sb.append("|"); System.out.println(sb.toString()); } System.out.println(""); } finally { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } } }
From source file:org.agnitas.web.ImportWizardForm.java
/** * Reads columns from database.//www. j a v a2s .c o m */ protected void readDBColumns(int companyID, DataSource ds) { String sqlGetTblStruct = "SELECT * FROM customer_" + companyID + "_tbl WHERE 1=0"; CsvColInfo aCol = null; String colType = null; dbAllColumns = new CaseInsensitiveMap(); 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 (!(this.mode == ImportWizardForm.MODE_ONLY_UPDATE && this.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); colType = meta.getColumnTypeName(i); if (colType.startsWith("VARCHAR")) { aCol.setType(CsvColInfo.TYPE_CHAR); } else if (colType.startsWith("CHAR")) { aCol.setType(CsvColInfo.TYPE_CHAR); } else if (colType.startsWith("NUM")) { aCol.setType(CsvColInfo.TYPE_NUMERIC); } else if (colType.startsWith("INTEGER")) { aCol.setType(CsvColInfo.TYPE_NUMERIC); } else if (colType.startsWith("DOUBLE")) { aCol.setType(CsvColInfo.TYPE_NUMERIC); } else if (colType.startsWith("TIME")) { aCol.setType(CsvColInfo.TYPE_DATE); } else if (colType.startsWith("DATE")) { aCol.setType(CsvColInfo.TYPE_DATE); } this.dbAllColumns.put(meta.getColumnName(i), aCol); } } rset.close(); stmt.close(); } catch (Exception e) { AgnUtils.logger().error("readDBColumns: " + e); } DataSourceUtils.releaseConnection(con, ds); }