Java Code Examples for java.sql.Statement#setFetchSize()

private void applyHints(Statement statement, Hints hints) throws SQLException {
	Integer fetchSize = (Integer)hints.get(HintEnum.fetchSize);
	
	if(fetchSize != null && fetchSize > 0) {
           statement.setFetchSize(fetchSize);
       }

	Integer maxRows = (Integer)hints.get(HintEnum.maxRows);
	if (maxRows != null && maxRows > 0) {
           statement.setMaxRows(maxRows);
       }

       Integer timeout = (Integer)hints.get(HintEnum.timeout);
       if (timeout == null || timeout < 0) {
           timeout = StatusManager.getTimeoutMarkdown().getTimeoutThreshold();
       }

	statement.setQueryTimeout(timeout);
}
 

private void writeDump(OutputStream out, BackupFile backup) throws Exception {
    DataOutputStream dout = new DataOutputStream(out);
    for (TableBackupInfo table:backup.tables) {
        System.err.print("saving " + table.getFullName() + " ... ");
        dout.writeUTF(table.getFullName());
        String sql = String.format("SELECT SQL_NO_CACHE * FROM `%s`.`%s`", table.catalog, table.table);
        if (this.limit != -1) {
            sql += " LIMIT " + this.limit;
        }
        long count = 0;
        Statement stmt = this.conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        try (ResultSet rs = stmt.executeQuery(sql)) {
            ResultSetMetaData rsmeta = rs.getMetaData();
            while (rs.next()) {
                writeRow(dout, rsmeta, rs);
                count++;
            }
        }
        dout.writeInt(0);
        this.nRows += count;
        System.err.println(count + " rows");
    }
    dout.flush();
}
 

protected void setFetchSize(Statement stmt, int fetchSize) throws SQLException {
    // do not set fetch size when using the cursor connection
    if (_connection instanceof CursorConnection) return;

    // check if the statement was called with a specific fetch size, if not grab the default from the datasource
    if (fetchSize < 0) {
        Datasource ds = EntityConfig.getDatasource(this.helperInfo.getHelperBaseName());
        if (ds != null) {
            fetchSize = ds.getResultFetchSize();
        } else {
            Debug.logWarning("Datasource is null, not setting fetch size!", module);
        }
    }

    // otherwise only set if the size is > -1 (0 is sometimes used to note ALL rows)
    if (fetchSize > -1) {
        stmt.setFetchSize(fetchSize);
    }
}
 

public void testCompression() throws Exception {
    Connection compressedConn = getConnectionWithProps("useCompression=true,maxAllowedPacket=33554432");
    Statement compressedStmt = compressedConn.createStatement();
    compressedStmt.setFetchSize(Integer.MIN_VALUE);
    this.rs = compressedStmt.executeQuery("select repeat('a', 256 * 256 * 256 - 5)");
    this.rs.next();
    String str = this.rs.getString(1);

    assertEquals((256 * 256 * 256 - 5), str.length());

    for (int i = 0; i < str.length(); i++) {
        if (str.charAt(i) != 'a') {
            fail();
        }
    }
}
 

@Ignore("Not working for setFetchSize")
@Test
public void testFetchSize() throws SQLException
{
  Connection connection = getConnection();
  Statement statement = connection.createStatement();
  assertEquals(50, statement.getFetchSize());
  statement.setFetchSize(1);
  ResultSet rs = statement.executeQuery("select * from JDBC_STATEMENT");
  assertEquals(1, getSizeOfResultSet(rs));

  statement.close();
  connection.close();
}
 

@Override
public List<Fact> getFacts(String relation) throws IOException {
	List<Fact> triples = new LinkedList<>();
	Statement stmt;
	ResultSet rs;

	try {
		Connection con = EntityLinkingManager.getConnectionForDatabase(EntityLinkingManager.DB_YAGO);
		con.setAutoCommit(false);

		stmt = con.createStatement();
		stmt.setFetchSize(10000000);
		String sql = "SELECT id, subject, object " + "FROM " + tableName + " WHERE predicate='" + relation + "'";
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			String id = rs.getString("id");
			String subject = rs.getString("subject");
			String object = rs.getString("object");
			triples.add(new Fact(id, subject, relation, object));
		}
		rs.close();
		con.setAutoCommit(true);

		EntityLinkingManager.releaseConnection(con);
	} catch (SQLException e) {
		throw new IOException(e);
	}

	return triples;
}
 

protected void applyStatementOptions(Statement statement) throws SQLException {
  if (options != null) {
    if (options.getQueryTimeout() > 0) {
      statement.setQueryTimeout(options.getQueryTimeout());
    }
    if (options.getFetchDirection() != null) {
      statement.setFetchDirection(options.getFetchDirection().getType());
    }
    if (options.getFetchSize() > 0) {
      statement.setFetchSize(options.getFetchSize());
    }
  }
}
 

public  ResultList executeQuery(DBConnectionWrapper conn, String sql, Map<String, String> params, int maxCount)throws SQLException
{
	PreparedStatement pstmt = null;
	Statement stmt = null;
	ResultSet rs = null;
	String actualSql = null;
	try
	{
		String sqlText = sql;
		if(params != null && params.size() > 0)
		{				
			actualSql = sqlText;
			for(Map.Entry<String, String> param: params.entrySet())
			{
				//note our parameters are prefixed with &
				actualSql = actualSql.replace("&"+param.getKey(), SqlParameter.escapeSingleQuote(param.getValue()));
			}
		}else
			actualSql = sqlText;
		
		stmt = conn.getConnection().createStatement();
		stmt.setQueryTimeout(this.frameworkContext.getQueryTimeout());
		stmt.setFetchSize(maxCount>0&&maxCount<this.frameworkContext.getQueryFetchSize()?maxCount:this.frameworkContext.getQueryFetchSize());
		conn.setCurrentStatement(stmt);
		rs = stmt.executeQuery(actualSql);
		return ResultListUtil.fromSqlResultSet(rs,maxCount>=0?maxCount:1000);
	}
	catch(SQLException sqlEx)
	{
		logger.info("Failed to execute "+actualSql);
		throw sqlEx;
	}finally
	{
		close(rs);
		close(stmt);
		close(pstmt);
		conn.setCurrentStatement(null);
	}
}
 

@Override
protected Statement optimizeStatement(Statement statement) throws SQLException {
	super.optimizeStatement(statement);
	
	switch (operationType) {
	case READ:
		statement.setFetchSize(Integer.MIN_VALUE);
		break;
	}

	return statement;
}
 

/**对Statement的属性进行设置。设置 JDBC Statement 对象的 fetchSize、maxRows、Timeout等参数。*/
protected void applyStatementSettings(final Statement stmt) throws SQLException {
    int fetchSize = this.getFetchSize();
    if (fetchSize > 0) {
        stmt.setFetchSize(fetchSize);
    }
    int maxRows = this.getMaxRows();
    if (maxRows > 0) {
        stmt.setMaxRows(maxRows);
    }
    int timeout = this.getQueryTimeout();
    if (timeout > 0) {
        stmt.setQueryTimeout(timeout);
    }
}
 

/**
 * This method performs searches against the search attribute value tables (see classes implementing
 * {@link org.kuali.rice.kew.docsearch.SearchableAttributeValue}) to get data to fill in search attribute values on the given resultBuilder parameter
 *
 * @param resultBuilder - document search result object getting search attributes added to it
 * @param searchAttributeStatement - statement being used to call the database for queries
 * @throws SQLException
 */
public void populateDocumentAttributesValues(DocumentSearchResult.Builder resultBuilder, Statement searchAttributeStatement) throws SQLException {
    searchAttributeStatement.setFetchSize(50);
    String documentId = resultBuilder.getDocument().getDocumentId();
    List<SearchableAttributeValue> attributeValues = DocumentSearchInternalUtils
            .getSearchableAttributeValueObjectTypes();
    PerformanceLogger perfLog = new PerformanceLogger(documentId);
    for (SearchableAttributeValue searchAttValue : attributeValues) {
        String attributeSql = "select KEY_CD, VAL from " + searchAttValue.getAttributeTableName() + " where DOC_HDR_ID = '" + documentId + "'";
        ResultSet attributeResultSet = null;
        try {
            attributeResultSet = searchAttributeStatement.executeQuery(attributeSql);
            while (attributeResultSet.next()) {
                searchAttValue.setSearchableAttributeKey(attributeResultSet.getString("KEY_CD"));
                searchAttValue.setupAttributeValue(attributeResultSet, "VAL");
                if ( (!org.apache.commons.lang.StringUtils.isEmpty(searchAttValue.getSearchableAttributeKey())) && (searchAttValue.getSearchableAttributeValue() != null) ) {
                    DocumentAttribute documentAttribute = searchAttValue.toDocumentAttribute();
                    resultBuilder.getDocumentAttributes().add(DocumentAttributeFactory.loadContractIntoBuilder(
                            documentAttribute));
                }
            }
        } finally {
            if (attributeResultSet != null) {
                try {
                    attributeResultSet.close();
                } catch (Exception e) {
                    LOG.warn("Could not close searchable attribute result set for class " + searchAttValue.getClass().getName(),e);
                }
            }
        }
    }
    perfLog.log("Time to execute doc search search attribute queries.", true);
}
 

/**
 * Testable template method to create simple statement
 */
protected StatementWrapper create(final String sql) throws SQLException {
    Statement statement = connection.createStatement();
    if (fetchSize != 0) {
        statement.setFetchSize(fetchSize);
    }
    return new StatementWrapper.Simple(statement, sql, converter);
}
 

@Test
public void assertSetFetchSize() throws SQLException {
    for (Statement each : statements.values()) {
        each.setFetchSize(4);
        each.executeQuery(sql);
        assertFetchSize((ShardingSphereStatement) each, 4);
        each.setFetchSize(100);
        assertFetchSize((ShardingSphereStatement) each, 100);
    }
}
 

@Test
public void testRoundRobinAfterTableSplit() throws Exception {
    String tableName = generateUniqueName();
    byte[] tableNameBytes = Bytes.toBytes(tableName);
    int numRows = setupTableForSplit(tableName);
    Connection conn = getConnection();
    ConnectionQueryServices services = conn.unwrap(PhoenixConnection.class).getQueryServices();
    int nRegions = services.getAllTableRegions(tableNameBytes).size();
    int nRegionsBeforeSplit = nRegions;
    Admin admin = services.getAdmin();
    try {
        // Split is an async operation. So hoping 10 seconds is long enough time.
        // If the test tends to flap, then you might want to increase the wait time
        admin.split(TableName.valueOf(tableName));
        CountDownLatch latch = new CountDownLatch(1);
        int nTries = 0;
        long waitTimeMillis = 2000;
        while (nRegions == nRegionsBeforeSplit && nTries < 10) {
            latch.await(waitTimeMillis, TimeUnit.MILLISECONDS);
            nRegions = services.getAllTableRegions(tableNameBytes).size();
            nTries++;
        }
        
        String query = "SELECT * FROM " + tableName;
        Statement stmt = conn.createStatement();
        stmt.setFetchSize(10); // this makes scanner caches to be replenished in parallel.
        ResultSet rs = stmt.executeQuery(query);
        int numRowsRead = 0;
        while (rs.next()) {
            numRowsRead++;
        }
        nRegions = services.getAllTableRegions(tableNameBytes).size();
        // Region cache has been updated, as there are more regions now
        assertNotEquals(nRegions, nRegionsBeforeSplit);
        assertEquals(numRows, numRowsRead);
    } finally {
        admin.close();
    }

}
 

void setStatementState(Statement oldStatement, Statement newStatement) throws SQLException {
	if (cursorName != null)
		newStatement.setCursorName(cursorName);
	if (escapeProcessing != null)
		newStatement.setEscapeProcessing(escapeProcessing);

	newStatement.setFetchDirection(oldStatement.getFetchDirection());
	newStatement.setFetchSize(oldStatement.getFetchSize());
	newStatement.setMaxFieldSize(oldStatement.getMaxFieldSize());
	newStatement.setMaxRows(oldStatement.getMaxRows());
	newStatement.setQueryTimeout(oldStatement.getQueryTimeout());
}
 

/**
Create a statement with modified State.
 */
private Statement createFloatStatementForStateChecking(
    int[] StatementExpectedValues, Connection conn)
throws SQLException {
    Statement s = internalCreateFloatStatementForStateChecking(conn);
    s.setCursorName("StokeNewington");
    s.setFetchDirection(ResultSet.FETCH_REVERSE);
    s.setFetchSize(444);
    s.setMaxFieldSize(713);
    s.setMaxRows(19);

    // Create
    assertStatementState(null, StatementExpectedValues, s);
    return s;
}
 

/**
 * Connects to the database
 */
private void connectToDatabase() {
    try {
        // Reset the error counter
        errCnt = 0;

        logger.debug("mySQL: Attempting to connect to database {}", url);
        Class.forName(driverClass).newInstance();
        connection = DriverManager.getConnection(url, user, password);
        logger.debug("mySQL: Connected to database {}", url);

        Statement st = connection.createStatement();
        int result = st.executeUpdate("SHOW TABLES LIKE 'Items'");
        st.close();

        if (waitTimeout != -1) {
            logger.debug("mySQL: Setting wait_timeout to {} seconds.", waitTimeout);
            st = connection.createStatement();
            st.executeUpdate("SET SESSION wait_timeout=" + waitTimeout);
            st.close();
        }
        if (result == 0) {
            st = connection.createStatement();
            st.executeUpdate(
                    "CREATE TABLE Items (ItemId INT NOT NULL AUTO_INCREMENT,ItemName VARCHAR(200) NOT NULL,PRIMARY KEY (ItemId));",
                    Statement.RETURN_GENERATED_KEYS);
            st.close();
        }

        // Retrieve the table array
        st = connection.createStatement();

        // Turn use of the cursor on.
        st.setFetchSize(50);
        ResultSet rs = st.executeQuery("SELECT ItemId, ItemName FROM Items");
        while (rs.next()) {
            sqlTables.put(rs.getString(2), "Item" + rs.getInt(1));
        }
        rs.close();
        st.close();
    } catch (Exception e) {
        logger.error(
                "mySQL: Failed connecting to the SQL database using: driverClass={}, url={}, user={}",
                driverClass, url, user, e);
    }
}
 

private void fillSuperdocKeyphraseCounts(TIntObjectHashMap<int[]> linkTo, TIntObjectHashMap<TIntIntHashMap> superdocKeyphraseCounts)
    throws SQLException {
  Connection con = EntityLinkingManager.getConnectionForDatabase(EntityLinkingManager.DB_AIDA);

  con.setAutoCommit(false);
  Statement stmt = con.createStatement();
  stmt.setFetchSize(fetchSize);

  String sql = "SELECT entity,keyphrase FROM " + DataAccessSQL.ENTITY_KEYPHRASES;
  ResultSet rs = stmt.executeQuery(sql);

  int reportFreq = 1000000;
  int rowCount = 0;
  long totalCount = 0;
  long totalLinksSinceLast = 0;
  long startTime = System.currentTimeMillis();

  while (rs.next()) {
    // write message every 1,000,000 rows
    if ((++rowCount % reportFreq) == 0) {
      long duration = System.currentTimeMillis() - startTime;
      double avgLinks = (double) totalLinksSinceLast / (double) reportFreq;
      double linksPerMs = (double) totalLinksSinceLast / duration;
      logger.info(
          "Read " + rowCount / 1000000 + " mio e/kp ... " + totalCount + " kp-counts adjusted." + " Average number of links was: " + avgLinks + " ("
              + linksPerMs + " links/ms).");
      startTime = System.currentTimeMillis();
      totalLinksSinceLast = 0;
    }

    // get ids
    int eid = rs.getInt("entity");
    int kid = rs.getInt("keyphrase");

    // add keyphrase to entity itself
    TIntIntHashMap keyphraseCount = superdocKeyphraseCounts.get(eid);
    boolean adjusted = keyphraseCount.adjustValue(kid, 1);
    // Just bookkeeping.
    if (adjusted) ++totalCount;

    // add keyphrase to entities this entity links to
    int[] links = linkTo.get(eid);
    if (links != null) {
      totalLinksSinceLast += links.length;
      for (int linkedEid : links) {
        keyphraseCount = superdocKeyphraseCounts.get(linkedEid);

        if (keyphraseCount != null) {
          adjusted = keyphraseCount.adjustValue(kid, 1);
          // Just bookkeeping.
          if (adjusted) ++totalCount;
        } else {
          logger.warn("No dictionary for entity '" + eid + "'");
        }
      }
    }
  }

  rs.close();
  logger.info(totalCount + " kp-counts adjusted");

  con.setAutoCommit(true);
  EntityLinkingManager.releaseConnection(con);
}
 

@Test
public void testReverseRangeScan() throws Exception {
    String tenantId = getOrganizationId();
    String tableName = initATableValues(tenantId, getSplitsAtRowKeys(tenantId), getUrl());
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String query = "SELECT entity_id FROM " + tableName + " WHERE entity_id >= '" + ROW3 + "' ORDER BY organization_id DESC, entity_id DESC";
    try {
        Statement stmt = conn.createStatement();
        stmt.setFetchSize(2);
        ResultSet rs = stmt.executeQuery(query);

        assertTrue (rs.next());
        assertEquals(ROW9,rs.getString(1));
        assertTrue (rs.next());
        assertEquals(ROW8,rs.getString(1));
        assertTrue (rs.next());
        assertEquals(ROW7,rs.getString(1));
        assertTrue (rs.next());
        assertEquals(ROW6,rs.getString(1));
        assertTrue (rs.next());
        assertEquals(ROW5,rs.getString(1));
        assertTrue (rs.next());
        assertEquals(ROW4,rs.getString(1));
        assertTrue (rs.next());
        assertEquals(ROW3,rs.getString(1));

        assertFalse(rs.next());
        
        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
        assertEquals(
                "CLIENT PARALLEL 1-WAY REVERSE FULL SCAN OVER " + tableName + "\n" +
                "    SERVER FILTER BY FIRST KEY ONLY AND ENTITY_ID >= '00A323122312312'",
                QueryUtil.getExplainPlan(rs));
        
        PreparedStatement statement = conn.prepareStatement("SELECT entity_id FROM " + tableName + " WHERE organization_id = ? AND entity_id >= ? ORDER BY organization_id DESC, entity_id DESC");
        statement.setString(1, tenantId);
        statement.setString(2, ROW7);
        rs = statement.executeQuery();

        assertTrue (rs.next());
        assertEquals(ROW9,rs.getString(1));
        assertTrue (rs.next());
        assertEquals(ROW8,rs.getString(1));
        assertTrue (rs.next());
        assertEquals(ROW7,rs.getString(1));

        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Override
public CloseableIterator<E> createIterator(int skip, int take) {
    Statement statement = null;
    try {
        // connection held by the iterator if statement not reused
        BoundParameters parameters = createQuery(skip, take);
        statement = createStatement(!parameters.isEmpty());
        statement.setFetchSize(limit == null ? 0 : limit);

        StatementListener listener = configuration.getStatementListener();
        listener.beforeExecuteQuery(statement, sql, parameters);

        ResultSet results;
        if (parameters.isEmpty()) {
            results = statement.executeQuery(sql);
        } else {
            PreparedStatement preparedStatement = (PreparedStatement) statement;
            Mapping mapping = configuration.getMapping();
            for (int i = 0; i < parameters.count(); i++) {
                Expression expression = parameters.expressionAt(i);
                Object value = parameters.valueAt(i);
                if (expression instanceof Attribute) {
                    // extract foreign key reference
                    Attribute attribute = (Attribute) expression;
                    if (attribute.isAssociation() &&
                        (attribute.isForeignKey() || attribute.isKey())) {
                        // get the referenced value
                        if (value != null &&
                            ((Expression<?>)expression).getClassType()
                                .isAssignableFrom(value.getClass())) {
                            value = Attributes.replaceKeyReference(value, attribute);
                        }
                    }
                }
                mapping.write(expression, preparedStatement, i + 1, value);
            }
            results = preparedStatement.executeQuery();
        }
        listener.afterExecuteQuery(statement);

        return new ResultSetIterator<>(reader, results, selection, true, closeConnection);
    } catch (Exception e) {
        throw StatementExecutionException.closing(statement, e, sql);
    }
}