List of usage examples for java.sql ResultSetMetaData getColumnTypeName
String getColumnTypeName(int column) throws SQLException;
From source file:com.openddal.test.BaseTestCase.java
/** * Check if the result set meta data is correct. * * @param rs the result set/* w w w.ja v a 2 s . co m*/ * @param columnCount the expected column count * @param labels the expected column labels * @param datatypes the expected data types * @param precision the expected precisions * @param scale the expected scales */ protected void assertResultSetMeta(ResultSet rs, int columnCount, String[] labels, int[] datatypes, int[] precision, int[] scale) throws SQLException { ResultSetMetaData meta = rs.getMetaData(); int cc = meta.getColumnCount(); if (cc != columnCount) { fail("result set contains " + cc + " columns not " + columnCount); } for (int i = 0; i < columnCount; i++) { if (labels != null) { String l = meta.getColumnLabel(i + 1); if (!labels[i].equals(l)) { fail("column label " + i + " is " + l + " not " + labels[i]); } } if (datatypes != null) { int t = meta.getColumnType(i + 1); if (datatypes[i] != t) { fail("column datatype " + i + " is " + t + " not " + datatypes[i] + " (prec=" + meta.getPrecision(i + 1) + " scale=" + meta.getScale(i + 1) + ")"); } String typeName = meta.getColumnTypeName(i + 1); String className = meta.getColumnClassName(i + 1); switch (t) { case Types.INTEGER: Assert.assertEquals("INTEGER", typeName); Assert.assertEquals("java.lang.Integer", className); break; case Types.VARCHAR: Assert.assertEquals("VARCHAR", typeName); Assert.assertEquals("java.lang.String", className); break; case Types.SMALLINT: Assert.assertEquals("SMALLINT", typeName); Assert.assertEquals("java.lang.Short", className); break; case Types.TIMESTAMP: Assert.assertEquals("TIMESTAMP", typeName); Assert.assertEquals("java.sql.Timestamp", className); break; case Types.DECIMAL: Assert.assertEquals("DECIMAL", typeName); Assert.assertEquals("java.math.BigDecimal", className); break; default: } } if (precision != null) { int p = meta.getPrecision(i + 1); if (precision[i] != p) { fail("column precision " + i + " is " + p + " not " + precision[i]); } } if (scale != null) { int s = meta.getScale(i + 1); if (scale[i] != s) { fail("column scale " + i + " is " + s + " not " + scale[i]); } } } }
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); }//from www .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.jumpmind.db.sql.JdbcSqlTemplate.java
/** * Retrieve a JDBC column value from a ResultSet, using the most appropriate * value type. The returned value should be a detached value object, not * having any ties to the active ResultSet: in particular, it should not be * a Blob or Clob object but rather a byte array respectively String * representation.//from w w w . ja v a2s . c om * <p> * Uses the <code>getObject(index)</code> method, but includes additional * "hacks" to get around Oracle 10g returning a non-standard object for its * TIMESTAMP datatype and a <code>java.sql.Date</code> for DATE columns * leaving out the time portion: These columns will explicitly be extracted * as standard <code>java.sql.Timestamp</code> object. * * @param rs * is the ResultSet holding the data * @param index * is the column index * @param readStringsAsBytes TODO * @return the value object * @throws SQLException * if thrown by the JDBC API * @see java.sql.Blob * @see java.sql.Clob * @see java.sql.Timestamp */ public static Object getResultSetValue(ResultSet rs, int index, boolean readStringsAsBytes) throws SQLException { ResultSetMetaData metaData = rs.getMetaData(); Object obj = null; int jdbcType = metaData.getColumnType(index); if (readStringsAsBytes && TypeMap.isTextType(jdbcType)) { byte[] bytes = rs.getBytes(index); if (bytes != null) { obj = new String(bytes); } } else { obj = rs.getObject(index); } String className = null; if (obj != null) { className = obj.getClass().getName(); } if (obj instanceof Blob) { Blob blob = (Blob) obj; InputStream is = blob.getBinaryStream(); try { obj = IOUtils.toByteArray(is); } catch (IOException e) { throw new SqlException(e); } finally { IOUtils.closeQuietly(is); } } else if (obj instanceof Clob) { Clob clob = (Clob) obj; Reader reader = clob.getCharacterStream(); try { obj = IOUtils.toString(reader); } catch (IOException e) { throw new SqlException(e); } finally { IOUtils.closeQuietly(reader); } } else if (className != null && ("oracle.sql.TIMESTAMP".equals(className))) { obj = rs.getTimestamp(index); } else if (className != null && "oracle.sql.TIMESTAMPTZ".equals(className)) { obj = rs.getString(index); } else if (className != null && "oracle.sql.TIMESTAMPLTZ".equals(className)) { obj = rs.getString(index); } else if (className != null && className.startsWith("oracle.sql.DATE")) { String metaDataClassName = metaData.getColumnClassName(index); if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) { obj = rs.getTimestamp(index); } else { obj = rs.getDate(index); } } else if (obj instanceof java.sql.Date) { String metaDataClassName = metaData.getColumnClassName(index); if ("java.sql.Timestamp".equals(metaDataClassName)) { obj = rs.getTimestamp(index); } } else if (obj instanceof Timestamp) { String typeName = metaData.getColumnTypeName(index); if (typeName != null && typeName.equals("timestamptz")) { obj = rs.getString(index); } } return obj; }
From source file:com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.java
private Map<String, Integer> getTableSchema(SchemaAndTable schemaAndTable) throws SQLException { Map<String, Integer> columns = new HashMap<>(); String query = "SELECT * FROM \"" + schemaAndTable.getSchema() + "\".\"" + schemaAndTable.getTable() + "\" WHERE 1 = 0"; try (Statement schemaStatement = connection.createStatement(); ResultSet rs = schemaStatement.executeQuery(query)) { ResultSetMetaData md = rs.getMetaData(); int colCount = md.getColumnCount(); for (int i = 1; i <= colCount; i++) { int colType = md.getColumnType(i); String colName = md.getColumnName(i); if (!configBean.baseConfigBean.caseSensitive) { colName = colName.toUpperCase(); }/*from www . ja va 2 s. co m*/ if (colType == Types.DATE || colType == Types.TIME || colType == Types.TIMESTAMP) { dateTimeColumns.computeIfAbsent(schemaAndTable, k -> new HashMap<>()); dateTimeColumns.get(schemaAndTable).put(colName, md.getColumnTypeName(i)); } if (colType == Types.DECIMAL || colType == Types.NUMERIC) { decimalColumns.computeIfAbsent(schemaAndTable, k -> new HashMap<>()); decimalColumns.get(schemaAndTable).put(colName, new PrecisionAndScale(md.getPrecision(i), md.getScale(i))); } columns.put(md.getColumnName(i), md.getColumnType(i)); } } return columns; }
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// w w w . jav a2 s . com .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:org.agnitas.web.ImportWizardForm.java
/** * Reads columns from database.//from w ww. j a va 2 s . 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); }
From source file:org.apache.hive.hplsql.Exec.java
/** * Trace values retrived from the database *///from w ww .j a v a 2s . c o m public void trace(ParserRuleContext ctx, Var var, ResultSet rs, ResultSetMetaData rm, int idx) throws SQLException { if (var.type != Var.Type.ROW) { trace(ctx, "COLUMN: " + rm.getColumnName(idx) + ", " + rm.getColumnTypeName(idx)); trace(ctx, "SET " + var.getName() + " = " + var.toString()); } else { Row row = (Row) var.value; int cnt = row.size(); for (int j = 1; j <= cnt; j++) { Var v = row.getValue(j - 1); trace(ctx, "COLUMN: " + rm.getColumnName(j) + ", " + rm.getColumnTypeName(j)); trace(ctx, "SET " + v.getName() + " = " + v.toString()); } } }
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 w w . ja v a 2 s . c o m*/ 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:com.p5solutions.core.jpa.orm.EntityUtility.java
/** * Build the database-meta-dta for a given table entity, using an existing * connection.//w ww. ja va2s . 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.exist.xquery.modules.sql.ExecuteFunction.java
/** * evaluate the call to the XQuery execute() function, it is really the main entry point of this class. * * @param args arguments from the execute() function call * @param contextSequence the Context Sequence to operate on (not used here internally!) * * @return A node representing the SQL result set * * @throws XPathException DOCUMENT ME! * * @see org.exist.xquery.BasicFunction#eval(org.exist.xquery.value.Sequence[], org.exist.xquery.value.Sequence) *///w ww.j a v a2 s . c om @Override public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException { // was a connection and SQL statement specified? if (args[0].isEmpty() || args[1].isEmpty()) { return (Sequence.EMPTY_SEQUENCE); } // get the Connection long connectionUID = ((IntegerValue) args[0].itemAt(0)).getLong(); Connection con = SQLModule.retrieveConnection(context, connectionUID); if (con == null) { return (Sequence.EMPTY_SEQUENCE); } boolean preparedStmt = false; //setup the SQL statement String sql = null; Statement stmt = null; boolean executeResult = false; ResultSet rs = null; try { boolean makeNodeFromColumnName = false; MemTreeBuilder builder = context.getDocumentBuilder(); int iRow = 0; //SQL or PreparedStatement? if (args.length == 3) { // get the SQL statement sql = args[1].getStringValue(); stmt = con.createStatement(); makeNodeFromColumnName = ((BooleanValue) args[2].itemAt(0)).effectiveBooleanValue(); //execute the statement executeResult = stmt.execute(sql); } else if (args.length == 4) { preparedStmt = true; //get the prepared statement long statementUID = ((IntegerValue) args[1].itemAt(0)).getLong(); PreparedStatementWithSQL stmtWithSQL = SQLModule.retrievePreparedStatement(context, statementUID); sql = stmtWithSQL.getSql(); stmt = stmtWithSQL.getStmt(); makeNodeFromColumnName = ((BooleanValue) args[3].itemAt(0)).effectiveBooleanValue(); if (!args[2].isEmpty()) { setParametersOnPreparedStatement(stmt, (Element) args[2].itemAt(0)); } //execute the prepared statement executeResult = ((PreparedStatement) stmt).execute(); } else { //TODO throw exception } // DW: stmt can be null ? // execute the query statement if (executeResult) { /* SQL Query returned results */ // iterate through the result set building an XML document rs = stmt.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); int iColumns = rsmd.getColumnCount(); builder.startDocument(); builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("count", null, null), String.valueOf(-1)); while (rs.next()) { builder.startElement(new QName("row", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("index", null, null), String.valueOf(rs.getRow())); // get each tuple in the row for (int i = 0; i < iColumns; i++) { String columnName = rsmd.getColumnLabel(i + 1); if (columnName != null) { String colElement = "field"; if (makeNodeFromColumnName && columnName.length() > 0) { // use column names as the XML node /** * Spaces in column names are replaced with * underscore's */ colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_')); } builder.startElement(new QName(colElement, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); if (!makeNodeFromColumnName || columnName.length() <= 0) { String name; if (columnName.length() > 0) { name = SQLUtils.escapeXmlAttr(columnName); } else { name = "Column: " + String.valueOf(i + 1); } builder.addAttribute(new QName("name", null, null), name); } builder.addAttribute( new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), rsmd.getColumnTypeName(i + 1)); builder.addAttribute(new QName(TYPE_ATTRIBUTE_NAME, Namespaces.SCHEMA_NS, "xs"), Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1)))); //get the content if (rsmd.getColumnType(i + 1) == Types.SQLXML) { //parse sqlxml value try { final SQLXML sqlXml = rs.getSQLXML(i + 1); if (rs.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true"); } else { SAXParserFactory factory = SAXParserFactory.newInstance(); factory.setNamespaceAware(true); InputSource src = new InputSource(sqlXml.getCharacterStream()); SAXParser parser = factory.newSAXParser(); XMLReader xr = parser.getXMLReader(); SAXAdapter adapter = new AppendingSAXAdapter(builder); xr.setContentHandler(adapter); xr.setProperty(Namespaces.SAX_LEXICAL_HANDLER, adapter); xr.parse(src); } } catch (Exception e) { throw new XPathException( "Could not parse column of type SQLXML: " + e.getMessage(), e); } } else { //otherwise assume string value final String colValue = rs.getString(i + 1); if (rs.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true"); } else { if (colValue != null) { builder.characters(SQLUtils.escapeXmlText(colValue)); } } } builder.endElement(); } } builder.endElement(); iRow++; } builder.endElement(); } else { /* SQL Query performed updates */ builder.startDocument(); builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("updateCount", null, null), String.valueOf(stmt.getUpdateCount())); builder.endElement(); } // Change the root element count attribute to have the correct value NodeValue node = (NodeValue) builder.getDocument().getDocumentElement(); Node count = node.getNode().getAttributes().getNamedItem("count"); if (count != null) { count.setNodeValue(String.valueOf(iRow)); } builder.endDocument(); // return the XML result set return (node); } catch (SQLException sqle) { LOG.error("sql:execute() Caught SQLException \"" + sqle.getMessage() + "\" for SQL: \"" + sql + "\"", sqle); //return details about the SQLException MemTreeBuilder builder = context.getDocumentBuilder(); builder.startDocument(); builder.startElement(new QName("exception", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); boolean recoverable = false; if (sqle instanceof SQLRecoverableException) { recoverable = true; } builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable)); builder.startElement(new QName("state", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.characters(sqle.getSQLState()); builder.endElement(); builder.startElement(new QName("message", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); String state = sqle.getMessage(); if (state != null) { builder.characters(state); } builder.endElement(); builder.startElement(new QName("stack-trace", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream(); sqle.printStackTrace(new PrintStream(bufStackTrace)); builder.characters(new String(bufStackTrace.toByteArray())); builder.endElement(); builder.startElement(new QName("sql", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.characters(SQLUtils.escapeXmlText(sql)); builder.endElement(); if (stmt instanceof PreparedStatement) { Element parametersElement = (Element) args[2].itemAt(0); if (parametersElement.getNamespaceURI().equals(SQLModule.NAMESPACE_URI) && parametersElement.getLocalName().equals(PARAMETERS_ELEMENT_NAME)) { NodeList paramElements = parametersElement.getElementsByTagNameNS(SQLModule.NAMESPACE_URI, PARAM_ELEMENT_NAME); builder.startElement( new QName(PARAMETERS_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); for (int i = 0; i < paramElements.getLength(); i++) { Element param = ((Element) paramElements.item(i)); String value = param.getFirstChild().getNodeValue(); String type = param.getAttributeNS(SQLModule.NAMESPACE_URI, TYPE_ATTRIBUTE_NAME); builder.startElement( new QName(PARAM_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute( new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), type); builder.characters(SQLUtils.escapeXmlText(value)); builder.endElement(); } builder.endElement(); } } builder.startElement(new QName("xquery", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("line", null, null), String.valueOf(getLine())); builder.addAttribute(new QName("column", null, null), String.valueOf(getColumn())); builder.endElement(); builder.endElement(); builder.endDocument(); return ((NodeValue) builder.getDocument().getDocumentElement()); } finally { // close any record set or statement if (rs != null) { try { rs.close(); } catch (SQLException se) { LOG.warn("Unable to cleanup JDBC results", se); } rs = null; } if (!preparedStmt && stmt != null) { try { stmt.close(); } catch (SQLException se) { LOG.warn("Unable to cleanup JDBC results", se); } stmt = null; } } }