项目作者: datadotworld

项目描述 :
JDBC driver for data.world
高级语言: Java
项目地址: git://github.com/datadotworld/dw-jdbc.git
创建时间: 2016-10-19T19:56:33Z
项目社区:https://github.com/datadotworld/dw-jdbc

开源协议:Apache License 2.0

下载


dw-jdbc

dw-jdbc is a JDBC driver for connecting to datasets hosted on data.world.
It can be used to provide read-only access to any dataset provided by data.world
from any JVM language. dw-jdbc supports query access both in dwSQL
(data.world’s SQL dialect) and in SPARQL 1.1, the native query language
for semantic web data sources.

JDBC URLs

JDBC connects to data source based on a provided JDBC url. data.world
JDBC urls have the form

  1. jdbc:data:world:[language]:[user id]:[dataset id]

where:

  • [language] is either sql or sparql
  • [user id] is the data.world id of the dataset owner
  • [dataset id] is the data.world id of the dataset

You can extract these ids from the dataset home page url: https://data.world/[user id]/[dataset id].

Sample code (Java 8)

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.ResultSetMetaData;
  6. final String QUERY = "select * from HallOfFame where playerID = ? order by yearid, playerID limit 10";
  7. final String URL = "jdbc:data:world:sql:dave:lahman-sabremetrics-dataset";
  8. try (final Connection connection = // get a connection to the database, which will automatically be closed when done
  9. DriverManager.getConnection(URL, "<your user name>", "<your API token>");
  10. final PreparedStatement statement = // get a connection to the database, which will automatically be closed when done
  11. connection.prepareStatement(QUERY)) {
  12. statement.setString(1, "alexape01"); //bind a query parameter
  13. try (final ResultSet resultSet = statement.executeQuery()) { //execute the query
  14. ResultSetMetaData rsmd = resultSet.getMetaData(); //print out the column headers
  15. int columnsNumber = rsmd.getColumnCount();
  16. for (int i = 1; i <= columnsNumber; i++) {
  17. if (i > 1) System.out.print(", ");
  18. System.out.print(rsmd.getColumnName(i));
  19. }
  20. System.out.println("");
  21. while (resultSet.next()) { //loop through the query results
  22. for (int i = 1; i <= columnsNumber; i++) { //print out the column headers
  23. if (i > 1) System.out.print(", ");
  24. String columnValue = resultSet.getString(i);
  25. System.out.print(columnValue);
  26. }
  27. System.out.println("");
  28. // Note: when calling ResultSet.getObject() prefer the version that takes an explicit Class argument:
  29. // Integer n = resultSet.getObject(param, Integer.class);
  30. }
  31. }
  32. }

Using dw-jdbc in your project

If using Maven, you can use dw-jdbc by just including the following in your pom.xml file:

  1. <dependency>
  2. <groupId>world.data</groupId>
  3. <artifactId>dw-jdbc</artifactId>
  4. <version>0.4.1</version>
  5. </dependency>

See this link at Maven Central to find the latest version
number for the JDBC driver.

For some database tools it’s easier to install the jdbc driver if it’s a single jar. For this reason we also
provide dw-jdbc bundled with all its dependencies under the following:

  1. <dependency>
  2. <groupId>world.data</groupId>
  3. <artifactId>dw-jdbc</artifactId>
  4. <classifier>shaded</classifier>
  5. <version>0.4.1</version>
  6. </dependency>

Finding your Token

  1. Visit https://data.world
  2. Visit your user settings, and click the advanced tab.
  3. Copy your token.

Features

  • JDBC 4.2

  • The driver only supports read-only queries. It does not support INSERT/UPDATE/DELETE, DDL, or transactions.

  • Queries can be written in SPARQL 1.1 or in the SQL dialect described at https://docs.data.world/tutorials/dwsql/.

  • [SQL-only] Table and column metadata via java.sql.DatabaseMetaData.

  • [SQL-only] Support for positional parameters via java.sql.PreparedStatement.

  • [SPARQL-only] Support for named parameters via java.sql.CallableStatement.

    • For example, CallableStatement.setString("name", "value") will bind the string value to ?name within the query.
  • The DataWorldStatement.setJdbcCompatibilityLevel(JdbcCompatibility) method can be used to adjust how the JDBC driver maps query results to Java objects in java.sql.ResultSetMetaData. This is particularly relevant to SPARQL queries where result types in a column can vary from row to row.

    • JdbcCompatibility.LOW - No assumptions are made about types. ResultSetMetaData.getColumnType() returns java.sql.Types.OTHER and ResultSet.getObject() returns world.data.jdbc.model.Node.

    • JdbcCompatibility.MEDIUM - [SPARQL default] All columns are typed as string. ResultSetMetaData.getColumnType() returns java.sql.Types.NVARCHAR and ResultSet.getObject() returns java.lang.String.

    • JdbcCompatibility.HIGH - [SQL default] Columns are typed based on the underlying data, either using table metadata (SQL) or by inspecting the first row of the response (SPARQL).