davisusanibar commented on code in PR #229: URL: https://github.com/apache/arrow-cookbook/pull/229#discussion_r921738140
########## java/source/io.rst: ########## @@ -444,6 +444,176 @@ Reading Parquet File Please check :doc:`Dataset <./dataset>` +Reading JDBC ResultSets +*********************** + +The `Arrow Java JDBC module <https://arrow.apache.org/docs/java/jdbc.html>`_ +help us to convert JDBC ResultSets objects into columnar arrow format objects +through Arrow VectorSchemaRoots. + +ResultSet to VectorSchemaRoot Conversion +---------------------------------------- + +The main class to help us to convert ResultSet to VectorSchemaRoot is +`JdbcToArrow <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html>`_ + +.. testcode:: + + import org.apache.arrow.adapter.jdbc.ArrowVectorIterator; + import org.apache.arrow.adapter.jdbc.JdbcToArrow; + import org.apache.arrow.memory.BufferAllocator; + import org.apache.arrow.memory.RootAllocator; + import org.apache.arrow.vector.VectorSchemaRoot; + import org.h2.jdbcx.JdbcConnectionPool; + + import java.io.IOException; + import java.sql.ResultSet; + import java.sql.SQLException; + + try (BufferAllocator allocator = new RootAllocator()) { + JdbcConnectionPool pool = JdbcConnectionPool.create( + "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test", + "sa", ""); + ResultSet resultSet = pool.getConnection().createStatement().executeQuery( + "SELECT int_field1, bool_field2, double_field7 FROM TABLE1"); + try (ArrowVectorIterator iterator = JdbcToArrow.sqlToArrowVectorIterator( + resultSet, allocator)) { + while (iterator.hasNext()) { + VectorSchemaRoot root = iterator.next(); + System.out.print(root.contentToTSVString()); + } + } + } catch (SQLException throwables) { + throwables.printStackTrace(); + } catch (IOException e) { + e.printStackTrace(); + } + +.. testoutput:: + + INT_FIELD1 BOOL_FIELD2 DOUBLE_FIELD7 + 101 true 5.6478356785345E10 + 102 true 5.6478356785345E10 + 103 true 5.6478356785345E10 + +JdbcToArrow could be created also with a custom configurations needed with the +support of `JdbcToArrowConfig <https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html>`_ +, it is useful at the moment to work with array columns. + +.. testcode:: + + import org.apache.arrow.adapter.jdbc.ArrowVectorIterator; + import org.apache.arrow.adapter.jdbc.JdbcFieldInfo; + import org.apache.arrow.adapter.jdbc.JdbcToArrow; + import org.apache.arrow.adapter.jdbc.JdbcToArrowConfig; + import org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder; + import org.apache.arrow.adapter.jdbc.JdbcToArrowUtils; + import org.apache.arrow.memory.BufferAllocator; + import org.apache.arrow.memory.RootAllocator; + import org.apache.arrow.vector.VectorSchemaRoot; + import org.h2.jdbcx.JdbcConnectionPool; + + import java.io.IOException; + import java.sql.ResultSet; + import java.sql.SQLException; + import java.sql.Types; + import java.util.HashMap; + + try (BufferAllocator allocator = new RootAllocator()) { + JdbcConnectionPool pool = JdbcConnectionPool.create( + "jdbc:h2:zip:./thirdpartydeps/database/jdbc-cookbook.zip!/test", Review Comment: Hi @lidavidm in the migration from H2 to Derby database I saw that `Array` support is enabled for H2 but not for Apache Derby ([ML](https://lists.apache.org/thread/xw37phkcrhvjccm94jnb8t9qz40f0cbc) / Jira [ticket](https://issues.apache.org/jira/browse/IOTDB-86)) How do we could proceed? - One option will be to only showcase example for int/boolean/char data types - Another option will be to maintain current H2 database -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
