List of usage examples for java.sql ResultSetMetaData getColumnDisplaySize
int getColumnDisplaySize(int column) throws SQLException;
From source file:org.agnitas.web.ImportWizardForm.java
/** * Reads columns from database./*www .j av a2 s .co 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); }
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); }// ww w. j a v a 2 s. 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.hive.jdbc.TestJdbcDriver2.java
/** * Validate the Metadata for the result set of a metadata getColumns call. *///ww w . j a va 2s. co m @Test public void testMetaDataGetColumnsMetaData() throws SQLException { ResultSet rs = con.getMetaData().getColumns(null, null, "testhivejdbcdriver\\_table", null); ResultSetMetaData rsmd = rs.getMetaData(); assertEquals("TABLE_CAT", rsmd.getColumnName(1)); assertEquals(Types.VARCHAR, rsmd.getColumnType(1)); assertEquals(Integer.MAX_VALUE, rsmd.getColumnDisplaySize(1)); assertEquals("ORDINAL_POSITION", rsmd.getColumnName(17)); assertEquals(Types.INTEGER, rsmd.getColumnType(17)); assertEquals(11, rsmd.getColumnDisplaySize(17)); }
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 ww w. j a v a2s. 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.hive.jdbc.TestJdbcDriver2.java
@Test public void testIntervalTypes() throws Exception { Statement stmt = con.createStatement(); // Since interval types not currently supported as table columns, need to create them // as expressions. ResultSet res = stmt/*from w w w. j ava 2 s .co m*/ .executeQuery("select case when c17 is null then null else interval '1' year end as col1," + " c17 - c17 as col2 from " + dataTypeTableName + " order by col1"); ResultSetMetaData meta = res.getMetaData(); assertEquals("col1", meta.getColumnLabel(1)); assertEquals(java.sql.Types.OTHER, meta.getColumnType(1)); assertEquals("interval_year_month", meta.getColumnTypeName(1)); assertEquals(11, meta.getColumnDisplaySize(1)); assertEquals(11, meta.getPrecision(1)); assertEquals(0, meta.getScale(1)); assertEquals(HiveIntervalYearMonth.class.getName(), meta.getColumnClassName(1)); assertEquals("col2", meta.getColumnLabel(2)); assertEquals(java.sql.Types.OTHER, meta.getColumnType(2)); assertEquals("interval_day_time", meta.getColumnTypeName(2)); assertEquals(29, meta.getColumnDisplaySize(2)); assertEquals(29, meta.getPrecision(2)); assertEquals(0, meta.getScale(2)); assertEquals(HiveIntervalDayTime.class.getName(), meta.getColumnClassName(2)); // row 1 - results should be null assertTrue(res.next()); // skip the last (partitioning) column since it is always non-null for (int i = 1; i < meta.getColumnCount(); i++) { assertNull("Column " + i + " should be null", res.getObject(i)); } // row 2 - results should be null assertTrue(res.next()); for (int i = 1; i < meta.getColumnCount(); i++) { assertNull("Column " + i + " should be null", res.getObject(i)); } // row 3 assertTrue(res.next()); assertEquals("1-0", res.getString(1)); assertEquals(1, ((HiveIntervalYearMonth) res.getObject(1)).getYears()); assertEquals("0 00:00:00.000000000", res.getString(2)); assertEquals(0, ((HiveIntervalDayTime) res.getObject(2)).getDays()); }
From source file:com.p5solutions.core.jpa.orm.EntityUtility.java
/** * Build the database-meta-dta for a given table entity, using an existing * connection./* ww w. j ava2s .c o 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.apache.hive.jdbc.TestJdbcDriver2.java
@Test 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, c21, c22, c23 from " + dataTypeTableName + " limit 1"); ResultSetMetaData meta = res.getMetaData(); ResultSet colRS = con.getMetaData().getColumns(null, null, dataTypeTableName.toLowerCase(), null); assertEquals(20, meta.getColumnCount()); assertTrue(colRS.next());/*from w ww . jav a2s .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.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.ARRAY, meta.getColumnType(5)); assertEquals("array", 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.ARRAY, colRS.getInt("DATA_TYPE")); assertEquals("array<int>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c6", meta.getColumnName(6)); assertEquals(Types.JAVA_OBJECT, meta.getColumnType(6)); assertEquals("map", 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.JAVA_OBJECT, colRS.getInt("DATA_TYPE")); assertEquals("map<int,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c7", meta.getColumnName(7)); assertEquals(Types.JAVA_OBJECT, meta.getColumnType(7)); assertEquals("map", 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.JAVA_OBJECT, colRS.getInt("DATA_TYPE")); assertEquals("map<string,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c8", meta.getColumnName(8)); assertEquals(Types.STRUCT, meta.getColumnType(8)); assertEquals("struct", 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.STRUCT, colRS.getInt("DATA_TYPE")); assertEquals("struct<r:string,s:int,t:double>", colRS.getString("TYPE_NAME").toLowerCase()); 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.ARRAY, meta.getColumnType(14)); assertEquals("array", meta.getColumnTypeName(14)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(14)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(14)); assertEquals(0, meta.getScale(14)); // Move the result of getColumns() forward to match the columns of the query assertTrue(colRS.next()); // c13 assertTrue(colRS.next()); // c14 assertTrue(colRS.next()); // c15 assertTrue(colRS.next()); // c16 assertTrue(colRS.next()); // c17 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("c17", colRS.getString("COLUMN_NAME")); assertEquals(Types.TIMESTAMP, colRS.getInt("DATA_TYPE")); assertEquals("timestamp", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(15), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(15), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c18", meta.getColumnName(16)); assertEquals(Types.DECIMAL, meta.getColumnType(16)); assertEquals("decimal", meta.getColumnTypeName(16)); assertEquals(18, meta.getColumnDisplaySize(16)); assertEquals(16, meta.getPrecision(16)); assertEquals(7, meta.getScale(16)); assertEquals("c18", colRS.getString("COLUMN_NAME")); assertEquals(Types.DECIMAL, colRS.getInt("DATA_TYPE")); assertEquals("decimal", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(16), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(16), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); // skip c19, since not selected by query assertTrue(colRS.next()); 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)); assertEquals("c20", colRS.getString("COLUMN_NAME")); assertEquals(Types.DATE, colRS.getInt("DATA_TYPE")); assertEquals("date", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(17), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(17), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c21", meta.getColumnName(18)); assertEquals(Types.VARCHAR, meta.getColumnType(18)); assertEquals("varchar", meta.getColumnTypeName(18)); // varchar columns should have correct display size/precision assertEquals(20, meta.getColumnDisplaySize(18)); assertEquals(20, meta.getPrecision(18)); assertEquals(0, meta.getScale(18)); assertEquals("c21", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("varchar", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(18), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(18), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c22", meta.getColumnName(19)); assertEquals(Types.CHAR, meta.getColumnType(19)); assertEquals("char", meta.getColumnTypeName(19)); // char columns should have correct display size/precision assertEquals(15, meta.getColumnDisplaySize(19)); assertEquals(15, meta.getPrecision(19)); assertEquals(0, meta.getScale(19)); assertEquals("c23", meta.getColumnName(20)); assertEquals(Types.BINARY, meta.getColumnType(20)); assertEquals("binary", meta.getColumnTypeName(20)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(20)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(20)); assertEquals(0, meta.getScale(20)); assertEquals("c22", colRS.getString("COLUMN_NAME")); assertEquals(Types.CHAR, colRS.getInt("DATA_TYPE")); assertEquals("char", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(19), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(19), colRS.getInt("DECIMAL_DIGITS")); 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.sakaiproject.util.foorm.Foorm.java
/** * /*from w w w . j a v a2s .co m*/ * @param table * @param formDefinition * @param vendor * @param md * @return */ public String[] formAdjustTable(String table, String[] formDefinition, String vendor, ResultSetMetaData md) { ArrayList<String> rv = new ArrayList<String>(); for (String formField : formDefinition) { Properties info = parseFormString(formField); String field = info.getProperty("field", null); String type = info.getProperty("type", null); if ("header".equals(type)) continue; String maxs = adjustMax(info.getProperty("maxlength", null)); int maxlength = 0; if (maxs != null) maxlength = (new Integer(maxs)).intValue(); if (maxlength < 1) maxlength = 80; String sqlType = null; boolean autoIncrement = false; int sqlLength = -1; boolean isNullable = false; try { for (int i = 1; i <= md.getColumnCount(); i++) { if (field.equalsIgnoreCase(md.getColumnLabel(i))) { sqlLength = md.getColumnDisplaySize(i); autoIncrement = md.isAutoIncrement(i); sqlType = getSuperType(md.getColumnClassName(i)); isNullable = (md.isNullable(i) == ResultSetMetaData.columnNullable); break; } } } catch (Exception e) { // ignore } logger.fine(field + " (" + maxlength + ") type=" + type); logger.fine(field + " (" + sqlLength + ") auto=" + autoIncrement + " type=" + sqlType + " null=" + isNullable); // If the field is not there... if (sqlType == null) { if ("oracle".equals(vendor)) { rv.add("ALTER TABLE " + table + " ADD ( " + formSql(formField, vendor) + " )"); } else if ("mysql".equals(vendor)) { rv.add("ALTER TABLE " + table + " ADD " + formSql(formField, vendor)); } else { rv.add("ALTER TABLE " + table + " ADD COLUMN " + formSql(formField, vendor)); } continue; } String ff = formSql(formField, vendor); // BLTI-220, BLTI-238 - Required will be enforced in software - not the DB boolean shouldAlter = false; if ("key".equals(type)) { if (!NUMBER_TYPE.equals(sqlType)) logger.severe(field + " must be Integer and Auto Increment"); } else if ("autodate".equals(type)) { } else if ("url".equals(type) || "text".equals(type) || "textarea".equals(type)) { if ("oracle.sql.CLOB".equals(sqlType) || "oracle.jdbc.OracleClob".equals(sqlType)) continue; // CLOBS large enough :) if (!STRING_TYPE.equals(sqlType)) { logger.severe(field + " must be String field"); continue; } if (sqlLength < maxlength) shouldAlter = true; if (!isNullable) shouldAlter = true; // BLTI-220, BLTI-238 } else if ("radio".equals(type) || "checkbox".equals(type) || "integer".equals(type)) { if (NUMBER_TYPE.equals(sqlType)) continue; logger.severe(field + " must be Integer field"); } if (shouldAlter) { if ("oracle".equals(vendor)) { rv.add("ALTER TABLE " + table + " MODIFY ( " + ff + " )"); } else if ("mysql".equals(vendor)) { rv.add("ALTER TABLE " + table + " MODIFY " + ff); } else { rv.add("ALTER TABLE " + table + " ALTER COLUMN " + ff); } } } return rv.toArray(new String[rv.size()]); }
From source file:org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.ResultSetTableModelFactory.java
public static AttributeMap<Object> collectData(final ResultSetMetaData rsmd, final int column, final String name) throws SQLException { AttributeMap<Object> metaData = new AttributeMap<Object>(); metaData.setAttribute(MetaAttributeNames.Core.NAMESPACE, MetaAttributeNames.Core.TYPE, TypeMapper.mapForColumn(rsmd, column)); metaData.setAttribute(MetaAttributeNames.Core.NAMESPACE, MetaAttributeNames.Core.NAME, name); try {//ww w .ja v a2 s .co m if (rsmd.isCurrency(column + 1)) { metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.CURRENCY, Boolean.TRUE); } else { metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.CURRENCY, Boolean.FALSE); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#isCurrency. Driver does not implement the JDBC specs correctly. ", e); } try { if (rsmd.isSigned(column + 1)) { metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SIGNED, Boolean.TRUE); } else { metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SIGNED, Boolean.FALSE); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#isSigned. Driver does not implement the JDBC specs correctly. ", e); } try { final String tableName = rsmd.getTableName(column + 1); if (tableName != null) { metaData.setAttribute(MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.TABLE, tableName); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } try { final String schemaName = rsmd.getSchemaName(column + 1); if (schemaName != null) { metaData.setAttribute(MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.SCHEMA, schemaName); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getSchemaName. Driver does not implement the JDBC specs correctly. ", e); } try { final String catalogName = rsmd.getCatalogName(column + 1); if (catalogName != null) { metaData.setAttribute(MetaAttributeNames.Database.NAMESPACE, MetaAttributeNames.Database.CATALOG, catalogName); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } try { final String label = rsmd.getColumnLabel(column + 1); if (label != null) { metaData.setAttribute(MetaAttributeNames.Formatting.NAMESPACE, MetaAttributeNames.Formatting.LABEL, label); } } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } try { final int displaySize = rsmd.getColumnDisplaySize(column + 1); metaData.setAttribute(MetaAttributeNames.Formatting.NAMESPACE, MetaAttributeNames.Formatting.DISPLAY_SIZE, IntegerCache.getInteger(displaySize)); } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } try { final int precision = rsmd.getPrecision(column + 1); metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.PRECISION, IntegerCache.getInteger(precision)); } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } try { final int scale = rsmd.getScale(column + 1); metaData.setAttribute(MetaAttributeNames.Numeric.NAMESPACE, MetaAttributeNames.Numeric.SCALE, IntegerCache.getInteger(scale)); } catch (SQLException e) { logger.debug( "Error on ResultSetMetaData#getTableName. Driver does not implement the JDBC specs correctly. ", e); } return metaData; }
From source file:uk.ac.ed.epcc.webapp.model.data.Repository.java
/** * Use a ResultSet to populate the MetaData information * /* ww w . j a va 2 s . c o m*/ * @param rs * @throws SQLException * @throws ConsistencyError */ private void setMetaData(ResultSet rs) throws SQLException, ConsistencyError { assert (fields == null); fields = new LinkedHashMap<String, FieldInfo>(); ResultSetMetaData meta_data = rs.getMetaData(); int md_columns = meta_data.getColumnCount(); boolean seen_key = false; // Logger log = ctx.getLogger(getClass()); for (int i = 1; i <= md_columns; i++) { String returned_name = meta_data.getTableName(i); if (returned_name.length() > 0 && !returned_name.equalsIgnoreCase(table_name)) { throw new ConsistencyError("Table names do not match " + getTag() + "!=" + returned_name); } // if we don't know for sure assume no nulls boolean can_null = (meta_data.isNullable(i) == ResultSetMetaData.columnNullable); String name = meta_data.getColumnName(i); if (!seen_key && meta_data.isAutoIncrement(i)) { seen_key = true; id_name = name; } else { // log.debug("Metadata "+name+" "+meta_data.getColumnType(i)); int columnType = meta_data.getColumnType(i); int columnDisplaySize = meta_data.getColumnDisplaySize(i); fields.put(dbFieldtoTag(name), new FieldInfo(name, columnType, columnDisplaySize, can_null)); } } if (use_id && !seen_key) { // Note we need an up-to-date mysql driver for the isAutoIncrement // method to work properly. otherwise default to first col and hope id_name = meta_data.getColumnName(1); fields.remove(dbFieldtoTag(id_name)); } // cache the qualified form as this is used frequently StringBuilder sb = new StringBuilder(); sb.append(alias_name); sb.append("."); sb.append(id_name); qualified_id_name = sb.toString(); }