Skip to content

ObdalibPluginJDBC

Jump to bottom Edit New page
elemguzel edited this page Sep 22, 2015 · 14 revisions

Table of Contents

JDBC Connections and Drivers

In Java, database connections are established using the Java DataBase Connectivity Framework, (JDBC). Quest and ontop use JDBC connections to connect to data sources, and so, they require JDBC parameters.

In particular, for each data source you will need to define 4 parameters: Driver class, JDBC URL, username and password. The last two are self explanatory, we now elaborate on the first two.

Driver class

The Driver Class is a string that indicates which JDBC Driver to use when establishing a JDBC connection. JDBC Driver's are software implemented by third parties (often the same developers of the database system) that handle interaction with the DB in their own proprietary protocols.

Note that there often are be many drivers suitable for any DB, e.g., there are official and open source drivers for DB2.

The following is the list of drivers that we have used, and for which we provide support in -ontop- and Quest:

Database Driver name Version Class Download link Notes
MySQL Connector/J 5.1.20 com.mysql.jdbc.Driver download
PostgreSQL PostgreSQL JDBC4 Driver 9.1-902 org.postgresql.Driver download
H2 H2 JDBC Driver 1.3.166 org.h2.Driver download
DB2 DB2 Universal Driver com.ibm.db2.jcc.DB2Driver download
Microsoft SQL Server MS JDBC Driver for SQL Server 4.0.2206.100 com.microsoft.sqlserver.jdbc.SQLServerDriver download
Oracle Thin JDBC Driver for Java 6 11.2.0.3 oracle.jdbc.driver.OracleDriver download File: ojdbc6.jar
Teiid Teiid JDBC Driver 7.4.0 org.teiid.jdbc.TeiidDriver download JBOSS_PATH/server/default/lib/teiid-7.4.0.Final-client.jar
MonetDB MonetDB JDBC Driver 11.19.9 nl.cwi.monetdb.jdbc.MonetDriver

Note: You may use other drivers to connect to your database but in that case we won't be able to offer your support.

JDBC URL

The JDBC URL is a URI that determines the type, location and name of the database. Additionally, it is possible to pass connection parameters to the JDBC driver by means of the JDBC URL. Connection parameters might be important when creating OBDA applications, e.g., to define a default schema, default fetch size and query timeouts.

Now we provide a quick overview of common JDBC URLs for the supported DBs. Please read the documentation provided by your DB vendor for further information. Items in square brackets ([,]) being optional.

MySQL

The JDBC URL format for MySQL Connector/J is as follows, 

jdbc:mysql://<host>[:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

Examples: 

jdbc:mysql://localhost:3306/sakila
jdbc:mysql://localhost:3306/sakila?profileSQL=true
jdbc:mysql://192.168.174.1/mydb?useCursorFetch=true&defaultFetchSize=150
jdbc:mysql://10.7.20.39/si_test?sessionVariables=sql_mode='ANSI'

ANSI MODE, QUOTEs and MySQL

MySQL uses quotes and double quotes in a non-standard way which can give problems to ontop. Please connect ONLY using ANSI mode, to do this, just use the following connection string (replace with your server, port and database) 

jdbc:mysql://myserver:port/mydatabase?sessionVariables=sql_mode='ANSI'

More information, including all possible connection properties, can be found here.

PostgreSQL

The JDBC URL format for the PostgreSQL driver is as follows, 

jdbc:postgresql://<host>[:port]/<database>[?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

Examples: 

jdbc:postgresql://localhost/mydb
jdbc:postgresql://localhost:5432/mydb?ssl=true
jdbc:postgresql://localhost:5432/mydb?ssl=true&loginTimeout=15

More information, including all possible connection properties, can be found here.

H2 (in-memory)

The JDBC URL format for the H2 in in-memory mode (volatile DB) is as follows, 

jdbc:h2:mem:<databaseName>

Examples: 

jdbc:h2:mem:test_mem

H2 (server)

The JDBC URL format for the H2 working as a stand alone server (persistent DB) is as follows, 

# For TCP/IP connections (normal)
jdbc:h2:tcp://<server>[:<port>]/[<path>]<databaseName>

# For SSL/TLS connections (secure)
jdbc:h2:ssl://<server>[:<port>]/<databaseName>

Examples: 

jdbc:h2:tcp://localhost/~/test;DATABASE_TO_UPPER=FALSE
jdbc:h2:tcp://dbserv:8084/~/sample;DATABASE_TO_UPPER=FALSE
jdbc:h2:tcp://localhost/mem:test;DATABASE_TO_UPPER=FALSE
jdbc:h2:ssl://localhost:8085/~/sample;DATABASE_TO_UPPER=FALSE

More information and connection parameters can be found here.

Note that by default H2 is not case sensitive. However, Quest and -ontop- are case sensitive. To avoid incompatibilities you need to use turn on case sensitivity in H2 using the ;DATABASE_TO_UPPER=FALSE sufix in your connection string.

DB2

The JDBC URL format for DB2 using the Universal Driver is as follows, 

jdbc:db2://<host>[:<port>]/<database_name>

Examples: 

jdbc:db2://obdalin.inf.unibz.it/helloworld

More information here and here. See notes about the driver's license.

Microsoft SQL Server

The JDBC URL format for SQL Server is as follows, 

jdbc:sqlserver://<host>[:port];databaseName=<database>

Examples: 

jdbc:sqlserver://obdawin.inf.unibz.it;databaseName=helloworld

Oracle

The JDBC URL format for Oracle is as follows, 

jdbc:oracle:thin:@//<host>[:port]/<service>

Examples: 

jdbc:oracle:thin:@//obdalin.inf.unibz.it:1521/xe

Teiid

The JDBC URL format for Teiid is as follows, 

jdbc:teiid:<database>@mm://<host>[:port]

Examples: 

jdbc:teiid:bookselling@mm://localhost:31000

MonetDB

The JDBC URL format for the MonetDB driver is as follows, 

jdbc:monetdb://<host>[:<port>]/<database>

Examples: 

jdbc:monetdb://localhost/database

About the supported databases

Using PostgreSQL (recommended)

Quest works properly, and offers great performance, with any version and any driver for PostgreSQL 8 and 9 (recommended). An example of the JDBC connection parameters for postgres are: 

JDBC URL: jdbc:postgresql://localhost/stockclient1
Username: user
Password: '''''' 
Driver: org.postgresql.Driver

Using MySQL

Using MySQL in Virtual Mode is not recommended. The query planner of MySQL is very simple and often has problems making good query plans for the queries generated by the system. In particular, if there are nested-subqueries. Because of this, use cases with large volumes of data and MySQL will perform poorly in virtual mode.

Another reason to not use MySQL is that it lacks proper support for server side cursors. MySQL by default sends all results to the client, or, if cursors are enable, it creates temporary tables to hold the results. This can slow down query answering considerably. See ObdalibPluginTroubleshooting for more information.

An example of the JDBC connection parameters for mysql are: 

JDBC URL: jdbc:mysql://localhost/stockclient1
Username: user
Password: '''''' 
Driver: com.mysql.jdbc.Driver

Using H2

The following is a sample connection for H2: 

JDBC URL: jdbc:h2:mem:stockclient1
Username: sa
Password: 
Driver: org.h2.Driver

Using DB2 (recommended among commercial DBs)

We tested DB2 using the DB2 Universal Driver, which can be downloaded from here. The performance we get with DB2 is in many cases superior to the performance we get with PostgreSQL or MySQL, even the free version of DB2 Express-C.

The following is an example of the connection parameters for this driver: 

JDBC URL: jdbc:db2://192.168.8.130:50000/SAMPLE:currentSchema=DB2INST1;
Username: user
Password: '''''' *
Driver: com.ibm.db2.jcc.DB2Driver

Using SQL Server 

JDBC URL: jdbc:sqlserver://obdawin.inf.unibz.it;databaseName=helloworld
Username: user
Password: password
Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

Using Oracle

We have tested Oracle using the thin driver. However, the current version of Quest is has not been properly tested with Oracle and errors have been reported. The following is an example of the connection parameters for this driver: 

JDBC URL: jdbc:oracle:thin:@192.168.8.131:1521
Username: user
Password: password
Driver: oracle.jdbc.driver.OracleDriver

Copyright (c) 2009 -- 2016, Free University of Bozen-Bolzano

Clone this wiki locally