使用clojure.java.jdbc从MySQL流式传输 [英] Streaming from MySQL with clojure.java.jdbc

问题描述

根据的发行说明MySQL JDBC驱动程序，并且仅当使用具有并发只读，前向结果且访存大小恰好为Integer/MIN_VALUE的连接时，它才应流式传输结果.

According to the release notes for the MySQL JDBC driver, it should stream results if and only if using a connection with concurrency read-only, forward-only results, and a fetch-size of exactly Integer/MIN_VALUE.

但是，当我尝试完全生成这些条件时(针对[mysql/mysql-connector-java "5.1.21"])，我的SQL查询仍将永远运行(或者直到它耗尽JVM的内存并开始运行为止).

However, when I attempt to generate exactly these conditions (against [mysql/mysql-connector-java "5.1.21"]), my SQL query still runs forever (or, rather, until it exhausts the JVM's memory and goes boom).

(let [query (query-only (fetch-all big-table))]
  (clojure.java.jdbc/with-connection (get-connection (:db query))
    (clojure.java.jdbc/with-query-results rows
      (into [{:fetch-size Integer/MIN_VALUE
              :concurrency :read-only
              :result-type :forward-only} (:sql-str query)]
            (:params query))
      (throw (Exception. (str "retrieved a row: " (pr-str (first rows)))))))))

推荐答案

此答案是针对postgresql而不是MySQL，但应同时应用于两者.

This answer refers to postgresql instead of MySQL, but should apply to both.

用(clojure.java.jdbc/transaction)包装with-query-results函数，所以:

Wrap your with-query-results function with (clojure.java.jdbc/transaction), so:

(let [query (query-only (fetch-all big-table))]
  (clojure.java.jdbc/with-connection (get-connection (:db query))
    (clojure.java.jdbc/transaction        
      (clojure.java.jdbc/with-query-results rows
        (into [{:fetch-size Integer/MIN_VALUE
                :concurrency :read-only
                :result-type :forward-only} (:sql-str query)]
              (:params query))
        (throw (Exception. (str "retrieved a row: " (pr-str (first rows))))))))))

PostgreSQL文档为启用流式传输:连接不得处于自动提交模式."默认情况下，连接是在自动提交启用的情况下创建的，但是使用(clojure.java.jdbc/transaction)进行换行将在自动提交关闭的情况下运行内部代码.您也可以自己在连接上调用.setAutoCommit.

The postgresql docs specify one more requirement for enabling streaming: "The Connection must not be in autocommit mode." By default the connection is created with autocommit on, but wrapping with (clojure.java.jdbc/transaction) will run the inner code with autocommit off. You could also call .setAutoCommit on the connection yourself.

这篇关于使用clojure.java.jdbc从MySQL流式传输的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！