Examples of java.sql.ResultSet

java.sql.ResultSet
esultSet will be both scrollable and updateable
The ResultSet interface provides a series of methods for retrieving data from columns in the current row, such as getDate, getFloat. The columns are identified either by their index number (starting at 1) or by their name - there are separate methods for both techniques of column addressing. The column names are case insensitive. If several columns have the same name, then the getter methods use the first matching column. This means that if column names are used, it is not possible to guarantee that the name will retrieve data from the intended column - for certainty it is better to use column indexes. Ideally the columns should be read left-to-right and read once only, since not all * databases are optimised to handle other techniques of reading the data.
When reading data, the JDBC driver maps the SQL data retrieved from the database to the Java type implied by the method invoked by the application. The JDBC specification has a table of allowable mappings from SQL types to Java types.
There are also methods for writing data into the ResultSet, such as updateInt, updateString. The update methods can be used either to modify the data of an existing row or to insert new data rows into the ResultSet. Modification of existing data involves moving the Cursor to the row which needs modification and then using the update methods to modify the data, followed by calling the ResultSet.updateRow method. For insertion of new rows, the cursor is first moved to a special row called the Insert Row, data is added using the update methods, followed by calling the ResultSet.insertRow method.
A ResultSet is closed if the Statement object which generated it closed, executed again or is used to retrieve the next result from a sequence of results.

        t2.start();
        Thread.sleep(20);
        conn1.commit();
        t2.join(1000);
        t1.join(1000);
        ResultSet rs = s1.executeQuery("SELECT * FROM TEST1 ORDER BY ID");
        rs.next();
        assertEquals(1, rs.getInt(1));
        rs.next();
        assertEquals(5, rs.getInt(1));
        assertFalse(rs.next());
        conn1.close();
        conn2.close();
        conn3.close();
    }

View Full Code Here

        c1.close();
        c2.close();


        if (!config.memory) {
            Connection conn = getConnection("multiConn");
            ResultSet rs;
            rs = conn.createStatement().executeQuery("SELECT * FROM MULTI_A ORDER BY ID");
            rs.next();
            assertEquals("0-insert-A", rs.getString("NAME"));
            assertFalse(rs.next());
            rs = conn.createStatement().executeQuery("SELECT * FROM MULTI_B ORDER BY ID");
            rs.next();
            assertEquals("1-insert-D", rs.getString("NAME"));
            assertFalse(rs.next());
            conn.close();
        }


    }

View Full Code Here

  }
  
  private Map<String, TableInfo> getTables(MetadataFactory metadataFactory,
      DatabaseMetaData metadata) throws SQLException, TranslatorException {
    LogManager.logDetail(LogConstants.CTX_CONNECTOR, "JDBCMetadataProcessor - Importing tables"); //$NON-NLS-1$
    ResultSet tables = metadata.getTables(catalog, schemaPattern, tableNamePattern, tableTypes);
    Map<String, TableInfo> tableMap = new HashMap<String, TableInfo>();
    while (tables.next()) {
      String tableCatalog = tables.getString(1);
      String tableSchema = tables.getString(2);
      String tableName = tables.getString(3);
      String fullName = getFullyQualifiedName(tableCatalog, tableSchema, tableName);
      Table table = metadataFactory.addTable(useFullSchemaName?fullName:tableName);
      table.setNameInSource(getFullyQualifiedName(tableCatalog, tableSchema, tableName, true));
      table.setSupportsUpdate(true);
      String remarks = tables.getString(5);
      table.setAnnotation(remarks);
      tableMap.put(fullName, new TableInfo(tableCatalog, tableSchema, tableName, table));
      tableMap.put(tableName, new TableInfo(tableCatalog, tableSchema, tableName, table));
    }
    tables.close();
    
    getColumns(metadataFactory, metadata, tableMap);
    return tableMap;
  }

View Full Code Here


  private void getColumns(MetadataFactory metadataFactory,
      DatabaseMetaData metadata, Map<String, TableInfo> tableMap)
      throws SQLException, TranslatorException {
    LogManager.logDetail(LogConstants.CTX_CONNECTOR, "JDBCMetadataProcessor - Importing columns"); //$NON-NLS-1$
    ResultSet columns = metadata.getColumns(catalog, schemaPattern, tableNamePattern, null);
    int rsColumns = columns.getMetaData().getColumnCount();
    while (columns.next()) {
      String tableCatalog = columns.getString(1);
      String tableSchema = columns.getString(2);
      String tableName = columns.getString(3);
      String fullTableName = getFullyQualifiedName(tableCatalog, tableSchema, tableName);
      TableInfo tableInfo = tableMap.get(fullTableName);
      if (tableInfo == null) {
        tableInfo = tableMap.get(tableName);
        if (tableInfo == null) {
          continue;
        }
      }
      String columnName = columns.getString(4);
      int type = columns.getInt(5);
      String typeName = columns.getString(6);
      int columnSize = columns.getInt(7);
      String runtimeType = getRuntimeType(type, typeName, columnSize);
      //note that the resultset is already ordered by position, so we can rely on just adding columns in order
      Column column = metadataFactory.addColumn(columnName, runtimeType, tableInfo.table);
      column.setNameInSource(quoteName(columnName));
      column.setPrecision(columnSize);
      column.setLength(columnSize);
      column.setNativeType(typeName);
      column.setRadix(columns.getInt(10));
      column.setNullType(NullType.values()[columns.getShort(11)]);
      column.setUpdatable(true);
      String remarks = columns.getString(12);
      column.setAnnotation(remarks);
      String defaultValue = columns.getString(13);
      column.setDefaultValue(defaultValue);
      if (defaultValue != null && type == Types.BIT && TypeFacility.RUNTIME_NAMES.BOOLEAN.equals(runtimeType)) {
        //try to determine a usable boolean value
                if(defaultValue.length() == 1) {
                    int charIntVal = defaultValue.charAt(0);
                    // Set boolean FALse for incoming 0, TRUE for 1
                    if(charIntVal==0) {
                        column.setDefaultValue(Boolean.FALSE.toString());
                    } else if(charIntVal==1) {
                        column.setDefaultValue(Boolean.TRUE.toString());
                    }
        } else { //SQLServer quotes bit values
                    String trimedDefault = defaultValue.trim();
                    if (defaultValue.startsWith("(") && defaultValue.endsWith(")")) { //$NON-NLS-1$ //$NON-NLS-2$
                        trimedDefault = defaultValue.substring(1, defaultValue.length() - 1);
                    }
                    column.setDefaultValue(trimedDefault);
                }
      }
      column.setCharOctetLength(columns.getInt(16));
      if (rsColumns >= 23) {
        column.setAutoIncremented("YES".equalsIgnoreCase(columns.getString(23))); //$NON-NLS-1$
      }
    }
    columns.close();
  }

View Full Code Here

  private void getPrimaryKeys(MetadataFactory metadataFactory,
      DatabaseMetaData metadata, Map<String, TableInfo> tableMap)
      throws SQLException, TranslatorException {
    LogManager.logDetail(LogConstants.CTX_CONNECTOR, "JDBCMetadataProcessor - Importing primary keys"); //$NON-NLS-1$
    for (TableInfo tableInfo : tableMap.values()) {
      ResultSet pks = metadata.getPrimaryKeys(tableInfo.catalog, tableInfo.schema, tableInfo.name);
      TreeMap<Short, String> keyColumns = null;
      String pkName = null;
      while (pks.next()) {
        String columnName = pks.getString(4);
        short seqNum = pks.getShort(5);
        if (keyColumns == null) {
          keyColumns = new TreeMap<Short, String>();
        }
        keyColumns.put(seqNum, columnName);
        if (pkName == null) {
          pkName = pks.getString(6);
          if (pkName == null) {
            pkName = "PK_" + tableInfo.table.getName().toUpperCase(); //$NON-NLS-1$
          }
        }
      }
      if (keyColumns != null) {
        metadataFactory.addPrimaryKey(pkName, new ArrayList<String>(keyColumns.values()), tableInfo.table);
      }
      pks.close();
    }
  }

View Full Code Here

  
  private void getForeignKeys(MetadataFactory metadataFactory,
      DatabaseMetaData metadata, Map<String, TableInfo> tableMap) throws SQLException, TranslatorException {
    LogManager.logDetail(LogConstants.CTX_CONNECTOR, "JDBCMetadataProcessor - Importing foreign keys"); //$NON-NLS-1$
    for (TableInfo tableInfo : tableMap.values()) {
      ResultSet fks = metadata.getImportedKeys(tableInfo.catalog, tableInfo.schema, tableInfo.name);
      TreeMap<Short, String> keyColumns = null;
      String fkName = null;
      TableInfo pkTable = null;
      short savedSeqNum = Short.MAX_VALUE;
      while (fks.next()) {
        String columnName = fks.getString(8);
        short seqNum = fks.getShort(9);
        if (seqNum <= savedSeqNum) {
          if (keyColumns != null) {
            metadataFactory.addForiegnKey(fkName, new ArrayList<String>(keyColumns.values()), pkTable.table, tableInfo.table);
          }
          keyColumns = new TreeMap<Short, String>();
          fkName = null;
        }
        savedSeqNum = seqNum;
        keyColumns.put(seqNum, columnName);
        if (fkName == null) {
          String tableCatalog = fks.getString(1);
          String tableSchema = fks.getString(2);
          String tableName = fks.getString(3);
          String fullTableName = getFullyQualifiedName(tableCatalog, tableSchema, tableName);
          pkTable = tableMap.get(fullTableName);
          if (pkTable == null) {
            //throw new TranslatorException(JDBCPlugin.Util.getString("JDBCMetadataProcessor.cannot_find_primary", fullTableName)); //$NON-NLS-1$
            continue; //just drop the foreign key, the user probably didn't import the other table
          }
          fkName = fks.getString(12);
          if (fkName == null) {
            fkName = "FK_" + tableInfo.table.getName().toUpperCase(); //$NON-NLS-1$
          }
        } 
      }
      if (keyColumns != null) {
        metadataFactory.addForiegnKey(fkName, new ArrayList<String>(keyColumns.values()), pkTable.table, tableInfo.table);
      }
      fks.close();
    }
  }

View Full Code Here


  private void getIndexes(MetadataFactory metadataFactory,
      DatabaseMetaData metadata, Map<String, TableInfo> tableMap) throws SQLException, TranslatorException {
    LogManager.logDetail(LogConstants.CTX_CONNECTOR, "JDBCMetadataProcessor - Importing index info"); //$NON-NLS-1$
    for (TableInfo tableInfo : tableMap.values()) {
      ResultSet indexInfo = metadata.getIndexInfo(tableInfo.catalog, tableInfo.schema, tableInfo.name, false, importApproximateIndexes);
      TreeMap<Short, String> indexColumns = null;
      String indexName = null;
      short savedOrdinalPosition = Short.MAX_VALUE;
      boolean nonUnique = false;
      while (indexInfo.next()) {
        short type = indexInfo.getShort(7);
        if (type == DatabaseMetaData.tableIndexStatistic) {
          tableInfo.table.setCardinality(indexInfo.getInt(11));
          continue;
        }
        short ordinalPosition = indexInfo.getShort(8);
        if (ordinalPosition <= savedOrdinalPosition) {
          if (indexColumns != null) {
            metadataFactory.addIndex(indexName, nonUnique, new ArrayList<String>(indexColumns.values()), tableInfo.table);
          }
          indexColumns = new TreeMap<Short, String>();
          indexName = null;
        }
        savedOrdinalPosition = ordinalPosition;
        String columnName = indexInfo.getString(9);
        nonUnique = indexInfo.getBoolean(4);
        indexColumns.put(ordinalPosition, columnName);
        if (indexName == null) {
          indexName = indexInfo.getString(6);
          if (indexName == null) {
            indexName = "NDX_" + tableInfo.table.getName().toUpperCase(); //$NON-NLS-1$
          }
        }
      }
      if (indexColumns != null) {
        metadataFactory.addIndex(indexName, nonUnique, new ArrayList<String>(indexColumns.values()), tableInfo.table);
      }
      indexInfo.close();
    }
  }

View Full Code Here

            return;
        }
        deleteDb("manyObjects");
        Connection conn = getConnection("manyObjects");
        DatabaseMetaData meta = conn.getMetaData();
        ResultSet rsTables = meta.getColumns(null, null, null, null);
        while (rsTables.next()) {
            meta.getExportedKeys(null, null, null);
            meta.getImportedKeys(null, null, null);
        }
        conn.close();
    }

View Full Code Here

        deleteDb("callableStatement");
    }


    private void testCallWithResultSet(Connection conn) throws SQLException {
        CallableStatement call;
        ResultSet rs;
        call = conn.prepareCall("select 10");


        call.execute();
        rs = call.getResultSet();
        rs.next();
        assertEquals(10, rs.getInt(1));


        call.executeUpdate();
        rs = call.getResultSet();
        rs.next();
        assertEquals(10, rs.getInt(1));


    }

View Full Code Here

    }


    private void testPrepare(Connection conn) throws SQLException {
        Statement stat = conn.createStatement();
        CallableStatement call;
        ResultSet rs;
        stat.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR)");
        call = conn.prepareCall("INSERT INTO TEST VALUES(?, ?)");
        call.setInt(1, 1);
        call.setString(2, "Hello");
        call.execute();
        call = conn.prepareCall("SELECT * FROM TEST",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        rs = call.executeQuery();
        rs.next();
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());
        call = conn.prepareCall("SELECT * FROM TEST",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY,
                ResultSet.HOLD_CURSORS_OVER_COMMIT);
        rs = call.executeQuery();
        rs.next();
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());
        stat.execute("CREATE ALIAS testCall FOR \"" + getClass().getName() + ".testCall\"");
        call = conn.prepareCall("{CALL testCall(?,?,?)}");
        call.setInt("A", 100);
        call.setString(2, "abc");
        long t = System.currentTimeMillis();

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of java.sql.ResultSet

co.nubetech.hiho.job.TestExportToDB

co.nubetech.hiho.job.TestExportToMySQLDB

com.alibaba.wasp.jdbc.TestJdbcBase

com.alibaba.wasp.jdbc.TestJdbcResultSet

com.alibaba.wasp.jdbc.TestPreparedStatement

com.emc.plants.service.impl.ReportGeneratorBean

com.github.zathrus_writer.commandsex.CommandsEX

com.github.zathrus_writer.commandsex.helpers.Bans

com.github.zathrus_writer.commandsex.helpers.Econ

com.github.zathrus_writer.commandsex.helpers.Home

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.