JDBC Driver for MongoDB®

The JDBC driver for MongoDB allows SQL queries on MongoDB for any software that supports JDBC. SQL support includes functions, expressions, aggregation, and joins including for collections with nested objects and arrays. See the features and SQL examples supported by the JDBC driver for MongoDB. The JDBC driver works with on-premise and cloud hosted MongoDB including MongoDB Atlas.


Simple Licensing

Terms and Agreement

Download

MongoDB JDBC driver

Getting Started

Get started in 5 minutes



Features

  • Access data in MongoDB collections via SQL including WHERE filters, GROUP BY, and ORDER BY.
  • Works with all reporting and query software that supports JDBC including Tableau, Splunk, Tibco JasperReports, SAP Lumira, QlikView, and hundreds of others.
  • Manipulate and control data using standard SQL functions not natively supported by MongoDB.
  • Perform SQL joins across MongoDB collections and databases.
  • Full support for nested documents (subdocuments) and arrays including filters and expressions.

How it works

  • The SQL query is validated and translated into a MongoDB query and executed using the MongoDB Java library.
  • The generated MongoDB query can be output in text form which provides a SQL to MongoDB translation tool and service.
  • Metadata is exposed to relational systems through JDBC by building a representative schema by sampling the datastore and fitting the least-general type that will represent the data.
  • SQL features not natively supported by MongoDB and the JDBC driver are executed using the UnityJDBC virtualization engine.


Getting Started

Start using the JDBC Driver for MongoDB in 5 minutes:

  1. Download the UnityJDBC installation package which contains the JDBC Driver for MongoDB and example Java programs.
  2. After installation, put the mongodb_unityjdbc_full.jar into your application or development environment. There are installation instructions for common applications such as Tableau, Splunk, and SAP Lumira. The JDBC Driver for MongoDB can be used with any query or reporting software that supports JDBC.
  3. Configure the connection. The connection information is:
    	JDBC Driver class name:  mongodb.jdbc.MongoDriver
    	URL format:              jdbc:mongo://<serverName>/<databaseName>
    As part of the connection string, a series of connection arguments can be passed into the driver that will affect its behavior. For sharded or cluster setup, the URL should contain the server names (and optional ports) in a comma-separated list.
    AttributeValuesDescription
    debug true, false The debug property will cause the driver to print out debug information to the console during its operation.

    user <username> User name for connection.

    password <password> Password for connection.

    encoding utf-8 Character encoding used.

    dbname <database> Database name to use.

    validation strict
    flex
    none
    Schema validation performed. Strict validation ensures all identifiers are in the schema. Flex validation will perform best effort validation against a schema (if present) but attempt to execute the query in all cases. None will never generate or use any schema information.

    schema <file_location> Location of schema. Either a file URI or location in MongoDB. If the MongoDB collection is read-only, a schema can be stored locally. A schema is required for query promotion to UnityJDBC. Default schema location is _schema in the current MongoDB database (requires write permissions to database).

    rebuildschema true, false If true, rebuilds schema for connection. If false, uses existing cached schema if available. Uses location provided in schema property.

    samplesize Between 0 and 1. During schema building fraction of documents in a collection to use. Valid range is between 0 and 1. Default is 0.001.

    readpref primary (default), primarypref, secondary, secondarypref, nearest Specifies the MongoDB ReadPreference to use such as primary or secondary. More info.


    writeconcern ack (default), unack, replicaAck, journaled Specifies the MongoDB WriteConcern to use. More info.

    ssl false (default), true If true connect using SSL. For details on SSL setup and use, see MongoDB SSL Setup.

    log <log file name> Log debug notifications to given log file if debug is on.
  4. Connect to a database and run your queries. Example URL for testing:
    jdbc:mongo://ds029847.mongolab.com:29847/tpch

Sample Databases

To help test the JDBC Driver for MongoDB, two sample MongoDB databases are available.

  • The first instance is named tpch and is a read-only database for testing SELECT queries.
  • The second instance is named tpch_writeable and allows for both read and write operations.
  • tpch_writeable can be fully modified and can be used with SELECT, UPDATE, INSERT and DELETE queries.

The connection information for each database is
	tpch URL:            jdbc:mongo://ds029847.mongolab.com:29847/tpch
	tpch_writeable URL:  jdbc:mongo://ds035438.mongolab.com:35438/tpch_writeable

Sample connection parameter usage:

// Specify debug mode, rebuild schema on every connection, and set location to cache schema
jdbc:mongo://ds035438.mongolab.com:35438/tpch_writeable?debug=true&rebuildschema=true&schema=mongo:ds035438.mongolab.com:35438/tpch_writeable/myschema

Sample connection parameter usage for cluster or sharded servers:

jdbc:mongo://<server1>:<port1>,<server2>:<port2>/<database>?<parameters>

Query Execution

If the submitted query cannot be natively handled by the JDBC Driver for MongoDB (such as for queries containing JOIN, GROUP BY or HAVING clauses), the query will be promoted to UnityJDBC for processing. UnityJDBC will parse the query into subqueries that will then be run on the specific MongoDB collection and process the intermediate results to produce the final result.

The 30-day trial version of the UnityJDBC driver for MongoDB has no row or feature limitations. After 30 days, the trial version has no expiration date and is fully functioning except that it is limited to returning up to 100 results. If your query produces more than 100 results, upgrade your MongoDB JDBC license here.


Using the Driver in Java Code

  1. Create a new instance of the JDBC Driver for MongoDB and make a connection.
  2. 	Class.forName("mongodb.jdbc.MongoDriver");
  3. Connect to the URL. The last part is the database name (tpch in this case).
  4. 	String url="jdbc:mongo://ds029847.mongolab.com:29847/tpch";
    	con = DriverManager.getConnection(url, "dbuser", "dbuser");
  5. Create a statement and submit a query. See the features and SQL examples supported by the JDBC driver for MongoDB.
  6. 	stmt = con.createStatement();
    	String sql = "SELECT * FROM nation WHERE n_name >= 'C';";
    	rst = stmt.executeQuery(sql);
  7. Print out your results.
  8. 	ResultSetMetaData meta = rst.getMetaData();
    	int numColumns = meta.getColumnCount();
    	System.out.print(meta.getcolumnName(1));
    	for (int j = 2; j <= meta.getColumnCount(); j++)
    		System.out.print(", " + meta.getColumnName(j));
    	System.out.println();
    	while (rst.next())
    	{
    		System.out.print(rst.getObject(1));
    		for (int j = 2; j <= numColumns; j++)
    			System.out.print(", " + rst.getObjects(j));
    		System.out.println();
    	}
  9. Close the statement and connection.
  10. 	rst.close();
    	stmt.close();
    	con.close();

    Mongo and MongoDB are trademarks of 10gen, Inc.

Setup Instructions

Code Examples