Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD); PreparedStatement statement = connection.prepareStatement(JDBC_SELECT); ResultSet rs = statement.executeQuery(); PrintStream out = System.out; if (rs != null) { while (rs.next()) { ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { if (i > 1) { out.print(","); } int type = rsmd.getColumnType(i); if (type == Types.VARCHAR || type == Types.CHAR) { out.print(rs.getString(i)); } else { out.print(rs.getLong(i)); } } out.println(); } }
private HashMap<Long, HashMap<String, Object>> buildSchemaMap(Connection conn) throws SQLException { HashMap<Long, HashMap<String, Object>> schemas = new HashMap<>(); PreparedStatement p = conn.prepareStatement("SELECT * from `schemas`"); ResultSet rs = p.executeQuery(); ResultSetMetaData md = rs.getMetaData(); while ( rs.next() ) { HashMap<String, Object> row = new HashMap<>(); for ( int i = 1; i <= md.getColumnCount(); i++ ) row.put(md.getColumnName(i), rs.getObject(i)); schemas.put(rs.getLong("id"), row); } rs.close(); return schemas; }
public static List<Map<String, Object>> executeQuery(Connection conn, String sql, List<Object> parameters) throws SQLException { List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>(); PreparedStatement stmt = null; ResultSet rs = null; try { stmt = conn.prepareStatement(sql); setParameters(stmt, parameters); rs = stmt.executeQuery(); ResultSetMetaData rsMeta = rs.getMetaData(); while (rs.next()) { Map<String, Object> row = new LinkedHashMap<String, Object>(); for (int i = 0, size = rsMeta.getColumnCount(); i < size; ++i) { String columName = rsMeta.getColumnLabel(i + 1); Object value = rs.getObject(i + 1); row.put(columName, value); } rows.add(row); } } finally { JdbcUtils.close(rs); JdbcUtils.close(stmt); } return rows; }
/** * Determine the column name to use. The column name is determined based on a * lookup using ResultSetMetaData. * <p>This method implementation takes into account recent clarifications * expressed in the JDBC 4.0 specification: * <p><i>columnLabel - the label for the column specified with the SQL AS clause. * If the SQL AS clause was not specified, then the label is the name of the column</i>. * @param resultSetMetaData the current meta-data to use * @param columnIndex the index of the column for the look up * @return the column name to use * @throws SQLException in case of lookup failure */ public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) throws SQLException { String name = resultSetMetaData.getColumnLabel(columnIndex); if (!StringUtils.hasLength(name)) { name = resultSetMetaData.getColumnName(columnIndex); } return name; }
public ColumnDefinition41Packet(final int sequenceId, final ResultSetMetaData resultSetMetaData, final int columnIndex) throws SQLException { this(sequenceId, resultSetMetaData.getSchemaName(columnIndex), resultSetMetaData.getTableName(columnIndex), resultSetMetaData.getTableName(columnIndex), resultSetMetaData.getColumnLabel(columnIndex), resultSetMetaData.getColumnName(columnIndex), resultSetMetaData.getColumnDisplaySize(columnIndex), ColumnType.valueOfJDBCType(resultSetMetaData.getColumnType(columnIndex)), resultSetMetaData.getScale(columnIndex)); }
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2"); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); // The column count starts from 1 for (int i = 1; i <= columnCount; i++ ) { String name = rsmd.getColumnName(i); // Do stuff with name }
/** * @throws Exception If failed. */ @Test public void testResultSetMetaData() throws Exception { Statement stmt = DriverManager.getConnection(URL).createStatement(); ResultSet rs = stmt.executeQuery( "select p.name, o.id as orgId from \"pers\".Person p, \"org\".Organization o where p.orgId = o.id"); assert rs != null; ResultSetMetaData meta = rs.getMetaData(); assert meta != null; assert meta.getColumnCount() == 2; assert "Person".equalsIgnoreCase(meta.getTableName(1)); assert "name".equalsIgnoreCase(meta.getColumnName(1)); assert "name".equalsIgnoreCase(meta.getColumnLabel(1)); assert meta.getColumnType(1) == VARCHAR; assert "VARCHAR".equals(meta.getColumnTypeName(1)); assert "java.lang.String".equals(meta.getColumnClassName(1)); assert "Organization".equalsIgnoreCase(meta.getTableName(2)); assert "orgId".equalsIgnoreCase(meta.getColumnName(2)); assert "orgId".equalsIgnoreCase(meta.getColumnLabel(2)); assert meta.getColumnType(2) == INTEGER; assert "INTEGER".equals(meta.getColumnTypeName(2)); assert "java.lang.Integer".equals(meta.getColumnClassName(2)); }
@Test public void testEmptyJsonFileDoesntSuppressNetSchema2() throws Exception { Statement stmt = connection.createStatement(); ResultSet results = stmt.executeQuery( "SELECT a FROM cp.`empty.json`" ); // Result set should still have columns even though there are no rows: ResultSetMetaData metadata = results.getMetaData(); assertThat( "ResultSetMetaData.getColumnCount() should have been 1", metadata.getColumnCount(), equalTo( 1 ) ); assertThat( "Unexpected non-empty results. Test rot?", false, equalTo( results.next() ) ); }
@Test public void testPreparedStatementWithMockData() throws SQLException { Driver driver = new DummyDriver(); Connection conn = driver.connect("jdbc:kylin://test_url/test_db", null); PreparedStatement state = conn.prepareStatement("select * from test_table where id=?"); state.setInt(1, 10); ResultSet resultSet = state.executeQuery(); ResultSetMetaData metadata = resultSet.getMetaData(); assertEquals(12, metadata.getColumnType(1)); assertEquals("varchar", metadata.getColumnTypeName(1)); assertEquals(1, metadata.isNullable(1)); while (resultSet.next()) { assertEquals("foo", resultSet.getString(1)); assertEquals("bar", resultSet.getString(2)); assertEquals("tool", resultSet.getString(3)); } resultSet.close(); state.close(); conn.close(); }
try (Connection conn = DriverManager.getConnection(URL)) { conn.setSchema("pers"); DatabaseMetaData meta = conn.getMetaData(); ResultSetMetaData rsMeta = rs.getMetaData(); assert rsMeta.getColumnCount() == 24 : "Invalid columns count: " + rsMeta.getColumnCount(); while (rs.next()) { String name = rs.getString("COLUMN_NAME");
private static void runQuery(String sqlStmt) throws Exception { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery(sqlStmt); ResultSetMetaData meta = res.getMetaData(); System.out.println("Resultset has " + meta.getColumnCount() + " columns"); for (int i = 1; i <= meta.getColumnCount(); i++) { System.out.println("Column #" + i + " Name: " + meta.getColumnName(i) + " Type: " + meta.getColumnType(i)); } while (res.next()) { for (int i = 1; i <= meta.getColumnCount(); i++) { System.out.println("Column #" + i + ": " + res.getString(i)); } } res.close(); stmt.close(); }
@SuppressWarnings("unchecked") public Mock(MockType type) throws Exception { connection = mock(Connection.class); statement = mock(Statement.class); resultSet = mock(ResultSet.class); resultSetMetaData = mock(ResultSetMetaData.class); given(connection.createStatement()).willReturn(statement); given(statement.executeQuery(anyString())).willReturn(resultSet); given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getString(1)).willReturn("Bubba"); given(resultSet.getLong(2)).willReturn(22L); given(resultSet.getTimestamp(3)).willReturn(new Timestamp(1221222L)); given(resultSet.getObject(anyInt(), any(Class.class))).willThrow(new SQLFeatureNotSupportedException()); given(resultSet.getDate(3)).willReturn(new java.sql.Date(1221222L)); given(resultSet.getBigDecimal(4)).willReturn(new BigDecimal("1234.56")); given(resultSet.wasNull()).willReturn(type == MockType.TWO); given(resultSetMetaData.getColumnCount()).willReturn(4); given(resultSetMetaData.getColumnLabel(1)).willReturn( type == MockType.THREE ? "Last Name" : "name"); given(resultSetMetaData.getColumnLabel(2)).willReturn("age"); given(resultSetMetaData.getColumnLabel(3)).willReturn("birth_date"); given(resultSetMetaData.getColumnLabel(4)).willReturn("balance"); jdbcTemplate = new JdbcTemplate(); jdbcTemplate.setDataSource(new SingleConnectionDataSource(connection, false)); jdbcTemplate.setExceptionTranslator(new SQLStateSQLExceptionTranslator()); jdbcTemplate.afterPropertiesSet(); }
try (Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1")) { try (ResultSet colsRs = conn.getMetaData().getColumns(null, QueryUtils.DFLT_SCHEMA, "T", ".*")) { while (colsRs.next()) resCols.put(colsRs.getString("COLUMN_NAME"), DataType.getTypeClassName(DataType.convertSQLTypeToValueType(colsRs .getShort("DATA_TYPE")))); try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM T")) { try (ResultSet dataRs = ps.executeQuery()) { assertTrue(dataRs.next()); for (int i = 0; i < dataRs.getMetaData().getColumnCount(); i++) resData.add(dataRs.getObject(i + 1));
@Test public void testConnectionWithCatalogAndSchema() throws Exception { try (Connection connection = createConnection(TEST_CATALOG, "information_schema")) { try (Statement statement = connection.createStatement()) { try (ResultSet rs = statement.executeQuery("" + "SELECT table_catalog, table_schema " + "FROM tables " + "WHERE table_schema = 'information_schema' " + " AND table_name = 'tables'")) { ResultSetMetaData metadata = rs.getMetaData(); assertEquals(metadata.getColumnCount(), 2); assertEquals(metadata.getColumnLabel(1), "table_catalog"); assertEquals(metadata.getColumnLabel(2), "table_schema"); assertTrue(rs.next()); assertEquals(rs.getString("table_catalog"), TEST_CATALOG); } } } }
/** Tests that basic executeQuery() (with query statement) works. */ @Test public void testExecuteQueryBasicCaseWorks() throws SQLException { try (PreparedStatement stmt = connection.prepareStatement( "VALUES 11" )) { try(ResultSet rs = stmt.executeQuery()) { assertThat("Unexpected column count", rs.getMetaData().getColumnCount(), equalTo(1) ); assertTrue("No expected first row", rs.next()); assertThat(rs.getInt(1), equalTo(11)); assertFalse("Unexpected second row", rs.next()); } } }
public List<Map> selectByParams(String sql, List params) throws SQLException { List<Map> list = new ArrayList<> (); int index = 1; pstmt = conn.prepareStatement(sql); if (null != params && !params.isEmpty()) { for (int i = 0; i < params.size(); i ++) { pstmt.setObject(index++, params.get(i)); } } rs = pstmt.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int colsLen = metaData.getColumnCount(); while (rs.next()) { Map map = new HashMap(colsLen); for (int i = 0; i < colsLen; i ++) { String columnName = metaData.getColumnName(i + 1); Object columnValue = rs.getObject(columnName); if (null == columnValue) { columnValue = ""; } map.put(columnName, columnValue); } list.add(map); } return list; }
@Test public void testUpdateAndGeneratedKeys() throws SQLException { given(resultSetMetaData.getColumnCount()).willReturn(1); given(resultSetMetaData.getColumnLabel(1)).willReturn("1"); given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getObject(1)).willReturn(11); given(preparedStatement.executeUpdate()).willReturn(1); given(preparedStatement.getGeneratedKeys()).willReturn(resultSet); given(connection.prepareStatement(INSERT_GENERATE_KEYS, PreparedStatement.RETURN_GENERATED_KEYS) ).willReturn(preparedStatement); GeneratedKeysUpdater pc = new GeneratedKeysUpdater(); KeyHolder generatedKeyHolder = new GeneratedKeyHolder(); int rowsAffected = pc.run("rod", generatedKeyHolder); assertEquals(1, rowsAffected); assertEquals(1, generatedKeyHolder.getKeyList().size()); assertEquals(11, generatedKeyHolder.getKey().intValue()); verify(preparedStatement).setString(1, "rod"); verify(resultSet).close(); }
/** * Check that values of {@code field1} match what we expect. * @param vals Expected values. */ private void assertColumnValues(int... vals) throws SQLException { try (Statement stmt = conn.createStatement()) { try (ResultSet rs = stmt.executeQuery("SELECT age FROM Person ORDER BY id desc")) { assertEquals(1, rs.getMetaData().getColumnCount()); for (int i = 0; i < vals.length; i++) { assertTrue("Result set must have " + vals.length + " rows, got " + i, rs.next()); assertEquals(vals[i], rs.getInt(1)); } assertFalse("Result set must have exactly " + vals.length + " rows", rs.next()); } } }
@SuppressWarnings({"unused"}) @Test public void testDefaults() throws Exception { try (Connection conn = DriverManager.getConnection(URL)) { conn.setSchema('"' + CACHE_NAME + '"'); try (PreparedStatement st = conn.prepareStatement("SELECT * FROM VALUE")) { ResultSet rs = st.executeQuery(); int cols = rs.getMetaData().getColumnCount(); while(rs.next()) { StringBuilder sb = new StringBuilder(); sb.append(rs.getObject(1).toString()); for (int i = 1; i < cols; ++i) sb.append(", ").append(rs.getObject(i + 1).toString()); System.out.println(sb.toString()); } } } }
/** * @throws Exception If failed. */ @Test public void testMetadataResultSetClose() throws Exception { try (Connection conn = DriverManager.getConnection(BASE_URL); ResultSet tbls = conn.getMetaData().getTables(null, null, "%", null)) { int colCnt = tbls.getMetaData().getColumnCount(); while (tbls.next()) { for (int i = 0; i < colCnt; i++) tbls.getObject(i + 1); } } catch (Exception ignored) { fail(); } }