private void handleResultSet(List<Map<String, Object>> list, ResultSet resultSet) { try { if (resultSet != null && resultSet.last()) { resultSet.beforeFirst(); ResultSetMetaData rsmd = resultSet.getMetaData(); while (resultSet.next()) { Map<String, Object> map = Maps.newHashMap(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { map.put(rsmd.getColumnName(i), resultSet.getString(i)); } list.add(map); } resultSet.beforeFirst(); resultSet.close(); } } catch (SQLException e) { e.printStackTrace(); } }
public Object[] viewCloudMetaData() { return jdbcTemplate.query( "select * from CLOUDFILEDATA order by FILEDATAID", new ResultSetExtractor<Object[]>() { public Object[] extractData(ResultSet rs) throws SQLException, DataAccessException { ResultSetMetaData resultSetMetaData = rs.getMetaData(); String tablemetadata[] = new String[] { resultSetMetaData.getColumnName(1), resultSetMetaData.getColumnName(2), resultSetMetaData.getColumnName(3), resultSetMetaData.getColumnName(4), resultSetMetaData.getColumnName(5), resultSetMetaData.getColumnName(6), resultSetMetaData.getColumnName(9), resultSetMetaData.getColumnName(10), resultSetMetaData.getColumnName(11), }; return tablemetadata; } }); }
private List<Object[]> readResultSet(ResultSet rs) throws SQLException { if (rs == null) { return null; } List<Object[]> array = new ArrayList<Object[]>(); ResultSetMetaData metadata = rs.getMetaData(); int nColumn = metadata.getColumnCount(); //��ӱ�ͷ Object[] columnNames = new Object[nColumn]; for (int i = 1; i <= nColumn; i++) { columnNames[i - 1] = metadata.getColumnName(i); } array.add(columnNames); //��ȡ���� while (rs.next()) { Object[] row = new Object[nColumn]; for (int i = 0; i < nColumn; i++) { row[i] = rs.getObject(i + 1); } array.add(row); } return array; }
/** * Checks if parametrised query execution is working correctly. */ @Test public void shouldExecuteParametrisedQuery() { SqlScriptExecutor executor = sqlScriptExecutorProvider.get(new LoggingSqlScriptVisitor()); SelectStatement testSelect = select(field("alfaDate1"), field("alfaDate2"), literal(123)) .from(tableRef("DateTable")).where(eq(field("alfaDate1"), parameter("firstDateParam").type(DataType.BIG_INTEGER)));; Iterable<SqlParameter> parameterMetadata = ImmutableList.of(parameter(column("firstDateParam", DataType.STRING))); RecordBuilder parameterData = DataSetUtils.record().setString("firstDateParam", "20040609"); ResultSetProcessor<List<List<String>>> resultSetProcessor = new ResultSetProcessor<List<List<String>>>() { /** * Takes all rows and puts into two-dimension String array. */ @Override public List<List<String>> process(ResultSet resultSet) throws SQLException { Builder<List<String>> builder = ImmutableList.<List<String>>builder(); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); while (resultSet.next()) { List<String> rowBuilder = new LinkedList<>(); for (int columnNumber = 1; columnNumber < columnCount + 1; columnNumber++) { String stringifiezedCell = resultSet.getString(columnNumber); rowBuilder.add(stringifiezedCell); } builder.add(rowBuilder); } return builder.build(); } }; List<List<String>> result = executor.executeQuery(testSelect, parameterMetadata, parameterData, connection, resultSetProcessor); assertEquals(ImmutableList.of(ImmutableList.of("20040609","20040813", "123"), ImmutableList.of("20040609","20040609", "123") , ImmutableList.of("20040609","20040610", "123")), result); }
/** * 将'ResultSet'结果集转换为'Map的List集合'的方法. * @param rs ResultSet实例 * @return Map的List集合 */ @Override public List<Map<String, Object>> transform(ResultSet rs) { if (rs == null) { return null; } // 遍历Resultset和元数据,将每一行各列的数据存到Map中,然后将各行数据add到List集合中 List<Map<String, Object>> maps = new ArrayList<Map<String, Object>>(); try { ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { Map<String, Object> map = new HashMap<String, Object>(); for (int i = 0, cols = rsmd.getColumnCount(); i < cols; i++) { map.put(JdbcHelper.getColumn(rsmd, i + 1), rs.getObject(i + 1)); } maps.add(map); } } catch (Exception e) { throw new ResultsTransformException("将'ResultSet'结果集转换为'map的List集合'出错!", e); } return maps; }
/** Creates new form SelectParameters */ public SelectParametersJDialog(ResultSetMetaData rs) throws SQLException { initComponents(); this.setModal(true); int cols = rs.getColumnCount(); for (int i = 0; i < cols; i++) { jComboBox_lat.insertItemAt(rs.getColumnName(i + 1), i); if (rs.getColumnName(i + 1).equalsIgnoreCase("lat") || rs.getColumnName(i + 1).equalsIgnoreCase("latitude")) { jComboBox_lat.setSelectedItem(rs.getColumnName(i + 1)); } jComboBox_lon.insertItemAt(rs.getColumnName(i + 1), i); if (rs.getColumnName(i + 1).equalsIgnoreCase("lon") || rs.getColumnName(i + 1).equalsIgnoreCase("longitude")) { jComboBox_lon.setSelectedItem(rs.getColumnName(i + 1)); } jComboBox_time.insertItemAt(rs.getColumnName(i + 1), i); if (rs.getColumnName(i + 1).equalsIgnoreCase("time") || rs.getColumnName(i + 1).equalsIgnoreCase("date")) { jComboBox_time.setSelectedItem(rs.getColumnName(i + 1)); } } jComboBox_time.insertItemAt("", cols); }
/** * Tests BUG13601 (which doesn't seem to be present in 3.1.11, but we'll * leave it in here for regression's-sake). * * @throws Exception * if the test fails. */ public void testBug13601() throws Exception { if (versionMeetsMinimum(5, 0)) { createTable("testBug13601", "(field1 BIGINT NOT NULL, field2 BIT default 0 NOT NULL) ENGINE=MyISAM"); this.rs = this.stmt.executeQuery("SELECT field1, field2 FROM testBug13601 WHERE 1=-1"); ResultSetMetaData rsmd = this.rs.getMetaData(); assertEquals(Types.BIT, rsmd.getColumnType(2)); assertEquals(Boolean.class.getName(), rsmd.getColumnClassName(2)); this.rs = this.conn.prepareStatement("SELECT field1, field2 FROM testBug13601 WHERE 1=-1").executeQuery(); rsmd = this.rs.getMetaData(); assertEquals(Types.BIT, rsmd.getColumnType(2)); assertEquals(Boolean.class.getName(), rsmd.getColumnClassName(2)); } }
public <T> List<T> toBeanList(ResultSet rs, Class<T> type) throws SQLException { List<T> results = new ArrayList(); if (!rs.next()) { return results; } PropertyDescriptor[] props = propertyDescriptors(type); ResultSetMetaData rsmd = rs.getMetaData(); int[] columnToProperty = mapColumnsToProperties(rsmd, props); do { results.add(createBean(rs, type, props, columnToProperty)); } while (rs.next()); return results; }
/** * Tests for types being returned correctly * * @throws Exception * if an error occurs. */ public void testTypes() throws Exception { try { this.stmt.execute("DROP TABLE IF EXISTS typesRegressTest"); this.stmt.execute("CREATE TABLE typesRegressTest (varcharField VARCHAR(32), charField CHAR(2), enumField ENUM('1','2')," + "setField SET('1','2','3'), tinyblobField TINYBLOB, mediumBlobField MEDIUMBLOB, longblobField LONGBLOB, blobField BLOB)"); this.rs = this.stmt.executeQuery("SELECT * from typesRegressTest"); ResultSetMetaData rsmd = this.rs.getMetaData(); int numCols = rsmd.getColumnCount(); for (int i = 0; i < numCols; i++) { String columnName = rsmd.getColumnName(i + 1); String columnTypeName = rsmd.getColumnTypeName(i + 1); System.out.println(columnName + " -> " + columnTypeName); } } finally { this.stmt.execute("DROP TABLE IF EXISTS typesRegressTest"); } }
public static void dump(ResultSet rs) throws SQLException { // the order of the rows in a cursor // are implementation dependent unless you use the SQL ORDER statement ResultSetMetaData meta = rs.getMetaData(); int colmax = meta.getColumnCount(); int i; Object o = null; // the result set is a cursor into the data. You can only // point to one row at a time // assume we are pointing to BEFORE the first row // rs.next() points to next row and returns true // or false if there is no next row, which breaks the loop for (; rs.next(); ) { for (i = 0; i < colmax; ++i) { o = rs.getObject(i + 1); // Is SQL the first column is indexed // with 1 not 0 System.out.print(o.toString() + " "); } System.out.println(" "); } }
/** * Tests fix for BUG#2852, where RSMD is not returning correct (or matching) * types for TINYINT and SMALLINT. * * @throws Exception * if the test fails. */ public void testBug2852() throws Exception { try { this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2852"); this.stmt.executeUpdate("CREATE TABLE testBug2852 (field1 TINYINT, field2 SMALLINT)"); this.stmt.executeUpdate("INSERT INTO testBug2852 VALUES (1,1)"); this.rs = this.stmt.executeQuery("SELECT * from testBug2852"); assertTrue(this.rs.next()); ResultSetMetaData rsmd = this.rs.getMetaData(); assertTrue(rsmd.getColumnClassName(1).equals(this.rs.getObject(1).getClass().getName())); assertTrue("java.lang.Integer".equals(rsmd.getColumnClassName(1))); assertTrue(rsmd.getColumnClassName(2).equals(this.rs.getObject(2).getClass().getName())); assertTrue("java.lang.Integer".equals(rsmd.getColumnClassName(2))); } finally { this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2852"); } }
/** * Tests fix for BUG#2855, where RSMD is not returning correct (or matching) * types for FLOAT. * * @throws Exception * if the test fails. */ public void testBug2855() throws Exception { try { this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2855"); this.stmt.executeUpdate("CREATE TABLE testBug2855 (field1 FLOAT)"); this.stmt.executeUpdate("INSERT INTO testBug2855 VALUES (1)"); this.rs = this.stmt.executeQuery("SELECT * from testBug2855"); assertTrue(this.rs.next()); ResultSetMetaData rsmd = this.rs.getMetaData(); assertTrue(rsmd.getColumnClassName(1).equals(this.rs.getObject(1).getClass().getName())); assertTrue("java.lang.Float".equals(rsmd.getColumnClassName(1))); } finally { this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2855"); } }
@RequestMapping("/commit") public List<Map<String, String>> select() { List<Map<String, String>> results = new ArrayList<>(); try (Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT * FROM INFORMATION_SCHEMA.COLUMNS")) { ResultSetMetaData metaData = resultSet.getMetaData(); while (resultSet.next()) { Map<String, String> result = new HashMap<>(); for (int i = 0; i < metaData.getColumnCount(); i++) { String columnName = metaData.getColumnName(i + 1); result.put(columnName, resultSet.getString(columnName)); } results.add(result); } connection.commit(); } catch (Exception e) { throw new IllegalStateException(e); } return results; }
/** * bean生成模块抽取 * @param rs * @param meta * @param type * @return */ private T createBean(ResultSet rs, ResultSetMetaData meta,Class<T> type) { Object object = ReflectUtils.newInstance(type); try { int count = meta.getColumnCount(); for (int i = 0; i < count; i++) { Object value = rs.getObject(i + 1); String propertyName = fieldBinder.getProperty(type, meta.getColumnName(i + 1)); if (StringUtils.isEmpty(propertyName)){ ReflectUtils.setFieldValue(object,meta.getColumnName(i + 1), value); }else{ ReflectUtils.setFieldValue(object,propertyName, value); } } return (T) object; } catch (SQLException e) { e.printStackTrace(); return null; } }
/** * ensures two tables (or views, that is) have the same content */ private void ensureEqualContent(String tableNameLHS, String tableNameRHS) throws SQLException { ResultSet lhs = m_statement.executeQuery("SELECT * FROM \"" + tableNameLHS + "\""); ResultSet rhs = m_statement.executeQuery("SELECT * FROM \"" + tableNameRHS + "\""); ResultSetMetaData meta = lhs.getMetaData(); while (lhs.next() && rhs.next()) { for (int col = 1; col <= meta.getColumnCount(); ++col) { assertEquals("table content does not match: cp. " + tableNameLHS + "-" + tableNameRHS + ", row " + lhs.getRow() + ", col " + col, lhs.getObject(col), rhs.getObject(col)); } } // lhs should be after last, rhs still on last assertTrue("row count does not match: " + tableNameLHS + "-" + tableNameRHS, lhs.isAfterLast() && rhs.isLast()); }
/** * Tests fix for BUG#6399, ResultSetMetaData.getDisplaySize() is wrong for * multi-byte charsets. * * @throws Exception * if the test fails */ public void testBug6399() throws Exception { if (versionMeetsMinimum(4, 1)) { try { this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6399"); this.stmt.executeUpdate( "CREATE TABLE testBug6399 (field1 CHAR(3) CHARACTER SET UTF8, field2 CHAR(3) CHARACTER SET LATIN1, field3 CHAR(3) CHARACTER SET SJIS)"); this.stmt.executeUpdate("INSERT INTO testBug6399 VALUES ('a', 'a', 'a')"); this.rs = this.stmt.executeQuery("SELECT field1, field2, field3 FROM testBug6399"); ResultSetMetaData rsmd = this.rs.getMetaData(); assertEquals(3, rsmd.getColumnDisplaySize(1)); assertEquals(3, rsmd.getColumnDisplaySize(2)); assertEquals(3, rsmd.getColumnDisplaySize(3)); } finally { this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6399"); } } }
/** (Worked before.) */ @Test public void testInfoSchemaTablesZeroRowsByWhereFalseWorks() throws Exception { Statement stmt = connection.createStatement(); ResultSet results = stmt.executeQuery( "SELECT * FROM INFORMATION_SCHEMA.`TABLES` WHERE FALSE" ); // Result set should still have columns even though there are no rows: ResultSetMetaData metadata = results.getMetaData(); assertThat( "ResultSetMetaData.getColumnCount() should have been > 0", metadata.getColumnCount(), not( equalTo( 0 ) ) ); assertThat( "Unexpected non-empty results. Test rot?", false, equalTo( results.next() ) ); }
/** * Create a table from a {@link ResultSet}. */ public static String fromResultSet(ResultSet resultSet) throws SQLException { if (resultSet == null) throw new NullPointerException("resultSet == null"); if (!resultSet.isBeforeFirst()) throw new IllegalStateException("Result set not at first."); List<String> headers = new ArrayList<>(); ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); int columnCount = resultSetMetaData.getColumnCount(); for (int column = 0; column < columnCount; column++) { headers.add(resultSetMetaData.getColumnName(column + 1)); } List<String[]> data = new ArrayList<>(); while (resultSet.next()) { String[] rowData = new String[columnCount]; for (int column = 0; column < columnCount; column++) { rowData[column] = resultSet.getString(column + 1); } data.add(rowData); } String[] headerArray = headers.toArray(new String[headers.size()]); String[][] dataArray = data.toArray(new String[data.size()][]); return FlipTable.of(headerArray, dataArray); }
/** * Extract a value for the single column in the current row. * <p>Validates that there is only one column selected, * then delegates to {@code getColumnValue()} and also * {@code convertValueToRequiredType}, if necessary. * @see java.sql.ResultSetMetaData#getColumnCount() * @see #getColumnValue(java.sql.ResultSet, int, Class) * @see #convertValueToRequiredType(Object, Class) */ @Override @SuppressWarnings("unchecked") public T mapRow(ResultSet rs, int rowNum) throws SQLException { // Validate column count. ResultSetMetaData rsmd = rs.getMetaData(); int nrOfColumns = rsmd.getColumnCount(); if (nrOfColumns != 1) { throw new IncorrectResultSetColumnCountException(1, nrOfColumns); } // Extract column value from JDBC ResultSet. Object result = getColumnValue(rs, 1, this.requiredType); if (result != null && this.requiredType != null && !this.requiredType.isInstance(result)) { // Extracted value does not match already: try to convert it. try { return (T) convertValueToRequiredType(result, this.requiredType); } catch (IllegalArgumentException ex) { throw new TypeMismatchDataAccessException( "Type mismatch affecting row number " + rowNum + " and column type '" + rsmd.getColumnTypeName(1) + "': " + ex.getMessage()); } } return (T) result; }
/** * Tests fix for BUG#27867 - Schema objects with identifiers other than the * connection character aren't retrieved correctly in ResultSetMetadata. * * @throws Exception * if the test fails. */ public void testBug27867() throws Exception { if (!versionMeetsMinimum(4, 1)) { return; } String gbkColumnName = "\u00e4\u00b8\u00ad\u00e6\u2013\u2021\u00e6\u00b5\u2039\u00e8\u00af\u2022"; createTable("ColumnNameEncoding", "(`" + gbkColumnName + "` varchar(1) default NULL, `ASCIIColumn` varchar(1) default NULL" + ")ENGINE=MyISAM DEFAULT CHARSET=utf8"); this.rs = this.stmt.executeQuery("SELECT * FROM ColumnNameEncoding"); java.sql.ResultSetMetaData tblMD = this.rs.getMetaData(); assertEquals(gbkColumnName, tblMD.getColumnName(1)); assertEquals("ASCIIColumn", tblMD.getColumnName(2)); }
private List<Map<String, Object>> executeQuery(String sql, Object... params) { List<Map<String, Object>> list = null; try { try(Connection connection = dataSource.getConnection(); PreparedStatement ps= connection.prepareStatement(sql)){ if (params != null) { for (int i = 0; i < params.length; i++) { ps.setObject((i + 1), params[i]); } } ResultSet rs = ps.executeQuery(); ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); list = new ArrayList<>(); while (rs.next()) { Map<String, Object> rowData = Maps.newHashMap(); for (int i = 1; i <= columnCount; i++) { rowData.put(md.getColumnName(i), rs.getObject(i)); } list.add(rowData); } } } catch (SQLException e) { e.printStackTrace(); LOGGER.error("executeQuery->" + e.getMessage()); } return list; }
private void compareMetaData(ResultSetMetaData rsmd, ResultSetMetaData rsmd1) throws SQLException { assertEquals(rsmd1.getColumnCount(), rsmd.getColumnCount()); int cols = rsmd.getColumnCount(); for (int i = 1; i <= cols; i++) { assertTrue(rsmd1.getCatalogName(i).equals(rsmd.getCatalogName(i))); assertTrue(rsmd1.getColumnClassName(i).equals(rsmd.getColumnClassName(i))); assertTrue(rsmd1.getColumnDisplaySize(i) == rsmd.getColumnDisplaySize(i)); assertTrue(rsmd1.getColumnLabel(i).equals(rsmd.getColumnLabel(i))); assertTrue(rsmd1.getColumnName(i).equals(rsmd.getColumnName(i))); assertTrue(rsmd1.getColumnType(i) == rsmd.getColumnType(i)); assertTrue(rsmd1.getPrecision(i) == rsmd.getPrecision(i)); assertTrue(rsmd1.getScale(i) == rsmd.getScale(i)); assertTrue(rsmd1.getSchemaName(i).equals(rsmd.getSchemaName(i))); assertTrue(rsmd1.getTableName(i).equals(rsmd.getTableName(i))); assertTrue(rsmd1.isAutoIncrement(i) == rsmd.isAutoIncrement(i)); assertTrue(rsmd1.isCaseSensitive(i) == rsmd.isCaseSensitive(i)); assertTrue(rsmd1.isCurrency(i) == rsmd.isCurrency(i)); assertTrue(rsmd1.isDefinitelyWritable(i) == rsmd.isDefinitelyWritable(i)); assertTrue(rsmd1.isNullable(i) == rsmd.isNullable(i)); assertTrue(rsmd1.isReadOnly(i) == rsmd.isReadOnly(i)); assertTrue(rsmd1.isSearchable(i) == rsmd.isSearchable(i)); assertTrue(rsmd1.isSigned(i) == rsmd.isSigned(i)); assertTrue(rsmd1.isWritable(i) == rsmd.isWritable(i)); } }
/** * Tests fix for BUG#4880 - RSMD.getPrecision() returns '0' for non-numeric * types. * * Why-oh-why is this not in the spec, nor the api-docs, but in some * 'optional' book, _and_ it is a variance from both ODBC and the ANSI SQL * standard :p * * (from the CTS testsuite).... * * The getPrecision(int colindex) method returns an integer value * representing the number of decimal digits for number types,maximum length * in characters for character types,maximum length in bytes for JDBC binary * datatypes. * * (See Section 27.3 of JDBC 2.0 API Reference & Tutorial 2nd edition) * * @throws Exception * if the test fails. */ public void testBug4880() throws Exception { try { this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4880"); this.stmt.executeUpdate("CREATE TABLE testBug4880 (field1 VARCHAR(80), field2 TINYBLOB, field3 BLOB, field4 MEDIUMBLOB, field5 LONGBLOB)"); this.rs = this.stmt.executeQuery("SELECT field1, field2, field3, field4, field5 FROM testBug4880"); ResultSetMetaData rsmd = this.rs.getMetaData(); assertEquals(80, rsmd.getPrecision(1)); assertEquals(Types.VARCHAR, rsmd.getColumnType(1)); assertEquals(80, rsmd.getColumnDisplaySize(1)); assertEquals(255, rsmd.getPrecision(2)); assertEquals(Types.VARBINARY, rsmd.getColumnType(2)); assertTrue("TINYBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(2))); assertEquals(255, rsmd.getColumnDisplaySize(2)); assertEquals(65535, rsmd.getPrecision(3)); assertEquals(Types.LONGVARBINARY, rsmd.getColumnType(3)); assertTrue("BLOB".equalsIgnoreCase(rsmd.getColumnTypeName(3))); assertEquals(65535, rsmd.getColumnDisplaySize(3)); assertEquals(16777215, rsmd.getPrecision(4)); assertEquals(Types.LONGVARBINARY, rsmd.getColumnType(4)); assertTrue("MEDIUMBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(4))); assertEquals(16777215, rsmd.getColumnDisplaySize(4)); if (versionMeetsMinimum(4, 1)) { // Server doesn't send us enough information to detect LONGBLOB // type assertEquals(Integer.MAX_VALUE, rsmd.getPrecision(5)); assertEquals(Types.LONGVARBINARY, rsmd.getColumnType(5)); assertTrue("LONGBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(5))); assertEquals(Integer.MAX_VALUE, rsmd.getColumnDisplaySize(5)); } } finally { this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4880"); } }
public static void main(String args[]) { // connect to database books and query database try (JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet()) { // specify JdbcRowSet properties rowSet.setUrl(DATABASE_URL); rowSet.setUsername(USERNAME); rowSet.setPassword(PASSWORD); rowSet.setCommand("SELECT * FROM authors"); // set query rowSet.execute(); // execute query // process query results ResultSetMetaData metaData = rowSet.getMetaData(); int numberOfColumns = metaData.getColumnCount(); System.out.println("Authors Table of Books Database:\n"); // display rowset header for (int i = 1; i <= numberOfColumns; i++) System.out.printf("%-8s\t", metaData.getColumnName(i)); System.out.println(); // display each row while (rowSet.next()) { for (int i = 1; i <= numberOfColumns; i++) System.out.printf("%-8s\t", rowSet.getObject(i)); System.out.println(); } } catch (SQLException sqlException) { sqlException.printStackTrace(); System.exit(1); } }
/** * Tests detection of read-only fields when the server is 4.1.0 or newer. * * @throws Exception * if the test fails. */ public void testRSMDIsReadOnly() throws Exception { try { this.rs = this.stmt.executeQuery("SELECT 1"); ResultSetMetaData rsmd = this.rs.getMetaData(); if (versionMeetsMinimum(4, 1)) { assertTrue(rsmd.isReadOnly(1)); try { createTable("testRSMDIsReadOnly", "(field1 INT)"); this.stmt.executeUpdate("INSERT INTO testRSMDIsReadOnly VALUES (1)"); this.rs = this.stmt.executeQuery("SELECT 1, field1 + 1, field1 FROM testRSMDIsReadOnly"); rsmd = this.rs.getMetaData(); assertTrue(rsmd.isReadOnly(1)); assertTrue(rsmd.isReadOnly(2)); assertTrue(!rsmd.isReadOnly(3)); } finally { } } else { assertTrue(rsmd.isReadOnly(1) == false); } } finally { if (this.rs != null) { this.rs.close(); } } }
/** * Test for CallableStatement.executeLargeUpdate(). * Validate update count returned and generated keys. */ public void testCallStmtExecuteLargeUpdate() throws Exception { createTable("testExecuteLargeUpdate", "(id BIGINT AUTO_INCREMENT PRIMARY KEY, n INT)"); createProcedure("testExecuteLargeUpdateProc", "(IN n1 INT, IN n2 INT, IN n3 INT, IN n4 INT, IN n5 INT) BEGIN " + "INSERT INTO testExecuteLargeUpdate (n) VALUES (n1), (n2), (n3), (n4), (n5); END"); CallableStatement testCstmt = this.conn.prepareCall("{CALL testExecuteLargeUpdateProc(?, ?, ?, ?, ?)}"); testCstmt.setInt(1, 1); testCstmt.setInt(2, 2); testCstmt.setInt(3, 3); testCstmt.setInt(4, 4); testCstmt.setInt(5, 5); long count = testCstmt.executeLargeUpdate(); assertEquals(5, count); assertEquals(5, testCstmt.getLargeUpdateCount()); this.rs = testCstmt.getGeneratedKeys(); // Although not requested, CallableStatements makes gerenated keys always available. ResultSetMetaData rsmd = this.rs.getMetaData(); assertEquals(1, rsmd.getColumnCount()); assertEquals(JDBCType.BIGINT.getVendorTypeNumber().intValue(), rsmd.getColumnType(1)); assertEquals(20, rsmd.getColumnDisplaySize(1)); // We can't check the generated keys as they are not returned correctly in this case (last_insert_id is missing from OK_PACKET when executing inserts // within a stored procedure - Bug#21792359). // long generatedKey = 0; // while (this.rs.next()) { // assertEquals(++generatedKey, this.rs.getLong(1)); // } // assertEquals(5, generatedKey); this.rs.close(); }
public boolean checkExist(String table) { try { // String sql = "SELECT COUNT(*) AS c FROM sqlite_master WHERE type ='table' AND name ='" + table + "' "; String sql = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='" + table + "';"; Statement statement = mConnection.createStatement(); ResultSet rs = statement.executeQuery(sql); int count = 0; if (rs != null && rs.next()) { ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); if (columnCount > 0) { count = rs.getInt(1); } } statement.close(); rs.close(); return count > 0; } catch (Exception e) { e.printStackTrace(); } return false; }
@Test public void testTables() throws Exception { final Connection connection = getMockConnection(); final ResultSet resultSet = connection.getMetaData().getTables(null, null, null, new String[0]); assertFalse(resultSet.next()); final ResultSetMetaData metaData = resultSet.getMetaData(); assertTrue(metaData.getColumnCount() >= 3); assertEquals("TABLE_CAT", metaData.getColumnName(1)); assertEquals("TABLE_SCHEM", metaData.getColumnName(2)); assertEquals("TABLE_NAME", metaData.getColumnName(3)); resultSet.close(); connection.close(); }
@Override public ResultSetMetaData getMetaData() throws SQLException { return new MongoResultSetMetaData(select,fieldtype,this._schema,this._table); /* if(_cur !=null){ return new MongoResultSetMetaData(_cur.keySet(),this._schema); } else{ return new MongoResultSetMetaData(select,this._schema); } */ }
@Override public DremioResultSetImpl newResultSet(AvaticaStatement statement, QueryState state, Meta.Signature signature, TimeZone timeZone, Meta.Frame firstFrame) { final ResultSetMetaData metaData = newResultSetMetaData(statement, signature); return new DremioResultSetImpl(statement, state, signature, metaData, timeZone, firstFrame); }
/** returns true if the table for pi was created by Jason */ protected boolean isCreatedByJason(PredicateIndicator pi) throws SQLException { ResultSetMetaData meta = belsDB.get(pi); if (meta != null) { int cols = meta.getColumnCount(); return cols >= extraCols && meta.getColumnName((cols - extraCols) + 1).equalsIgnoreCase(COL_NEG) && meta.getColumnName((cols - extraCols) + 2).equalsIgnoreCase(COL_ANNOT); } return false; }
public void testBug44508() throws Exception { DatabaseMetaData dbmd = this.conn.getMetaData(); this.rs = dbmd.getSuperTypes("", "", ""); ResultSetMetaData rsmd = this.rs.getMetaData(); assertEquals("TYPE_CAT", rsmd.getColumnName(1)); // Gives TABLE_CAT assertEquals("TYPE_SCHEM", rsmd.getColumnName(2)); // Gives TABLE_SCHEM }
String fixupColumnDefRead(String aTableName, ResultSetMetaData meta, String columnType, ResultSet columnDesc, int columnIndex) throws SQLException { String SeqName = new String("_" + columnDesc.getString(4) + "_seq"); int spaceleft = 31 - SeqName.length(); if (aTableName.length() > spaceleft) { SeqName = aTableName.substring(0, spaceleft) + SeqName; } else { SeqName = aTableName + SeqName; } String CompareString = "nextval(\'\"" + SeqName + "\"\'"; if (columnType.indexOf(CompareString) >= 0) { // We just found a increment columnType = "SERIAL"; } for (int Idx = 0; Idx < Funcs.length; Idx++) { String ORACLE_func = Funcs[Idx][ORACLE]; int iStartPos = columnType.indexOf(ORACLE_func); if (iStartPos >= 0) { String NewColumnType = columnType.substring(0, iStartPos); NewColumnType += Funcs[Idx][HSQLDB]; NewColumnType += columnType.substring(iStartPos + ORACLE_func.length()); columnType = NewColumnType; } } return (columnType); }
/** * 数据库查询操作,返回单条记录 * @param sql语句 * @param 传入的占位符 * @return 返回Map集合类型,包含查询的结果 * @throws SQLException */ public Map<String,Object> returnSimpleResult(String sql,List<Object>params) { Map<String, Object> map = new HashMap<String, Object>(); int index = 1;//从1开始设置占位符 try { pStatement = connection.prepareStatement(sql); if(params != null && !params.isEmpty()) /*判断参数是否为空*/ { for(int i = 0;i<params.size();i++) /*循环填充占位符*/ { pStatement.setObject(index++, params.get(i)); } } // System.out.println(pStatement.toString()); resultset = pStatement.executeQuery(sql); /* 将查询结果封装到map集合*/ ResultSetMetaData metaDate = resultset.getMetaData();//获取resultSet列的信息 int columnLength = metaDate.getColumnCount();//获得列的长度 while(resultset.next()) { for(int i = 0;i<columnLength;i++) { String metaDateKey = metaDate.getColumnName(i+1);//获得列名 Object resultsetValue = resultset.getObject(metaDateKey);//通过列名获得值 if(resultsetValue == null) { resultsetValue = "";//转成String类型 } map.put(metaDateKey, resultsetValue);//添加到map集合(以上代码是为了将从数据库返回的值转换成map的key和value) } } } catch (SQLException e) { e.printStackTrace(); } return map; }
/** returns the where clausule for a select for literal l */ protected String getWhere(Literal l) throws SQLException { ResultSetMetaData meta = belsDB.get(l.getPredicateIndicator()); StringBuilder q = new StringBuilder(" where "); String and = ""; // for all ground terms of l for (int i = 0; i < l.getArity(); i++) { Term t = l.getTerm(i); if (t.isGround()) { q.append(and); String ts; if (t.isString()) { ts = "'" + ((StringTerm) t).getString() + "'"; } else if (t.isNumeric()) { ts = t.toString(); } else { ts = "'" + t.toString() + "'"; } q.append(meta.getColumnName(i + 1) + " = " + ts); and = " and "; } } if (isCreatedByJason(l.getPredicateIndicator())) { q.append(and + COL_NEG + " = " + l.negated()); } //System.out.println(q.toString()); if (and.length() > 0) // add nothing in the clausule return q.toString(); else return ""; }
/** * Tests fix for Bug#23212347, ALL API CALLS ON RESULTSET METADATA RESULTS IN NPE WHEN USESERVERPREPSTMTS=TRUE. */ public void testBug23212347() throws Exception { boolean useSPS = false; do { String testCase = String.format("Case [SPS: %s]", useSPS ? "Y" : "N"); createTable("testBug23212347", "(id INT)"); Properties props = new Properties(); props.setProperty("useServerPrepStmts", Boolean.toString(useSPS)); Connection testConn = getConnectionWithProps(props); Statement testStmt = testConn.createStatement(); testStmt.execute("INSERT INTO testBug23212347 VALUES (1)"); this.pstmt = testConn.prepareStatement("SELECT * FROM testBug23212347 WHERE id = 1"); this.rs = this.pstmt.executeQuery(); assertTrue(testCase, this.rs.next()); assertEquals(testCase, 1, this.rs.getInt(1)); assertFalse(testCase, this.rs.next()); ResultSetMetaData rsmd = this.pstmt.getMetaData(); assertEquals(testCase, "id", rsmd.getColumnName(1)); this.pstmt = testConn.prepareStatement("SELECT * FROM testBug23212347 WHERE id = ?"); this.pstmt.setInt(1, 1); this.rs = this.pstmt.executeQuery(); assertTrue(testCase, this.rs.next()); assertEquals(testCase, 1, this.rs.getInt(1)); assertFalse(this.rs.next()); rsmd = this.pstmt.getMetaData(); assertEquals(testCase, "id", rsmd.getColumnName(1)); } while (useSPS = !useSPS); }