JDBC Driver for MongoDB®

The JDBC driver for MongoDB runs SQL queries against MongoDB for any Java application that supports JDBC. SQL support includes functions, expressions, aggregation, and joins. See the features and SQL examples supported by the JDBC driver for MongoDB. The JDBC driver also supports the TokuMX MongoDB distribution.


Free 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 and ORDER BY.
  • 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 JDBC Driver for MongoDB Package which contains the JDBC Driver for MongoDB, the MongoDB Java Driver version 2.12.2 distributed by MongoDB, and example Java programs demonstrating JDBC queries, metadata, arrays and nested objects, inserts, updates, and deletes, PreparedStatements (SELECT), PreparedStatements (INSERT/UPDATE/DELETE), translating SQL to Mongo queries, and DataSources, XADataSource, connection pooling using the JDBC driver. There are also sample Mongo code for JSP and JSTL for use on Java web containers such as Tomcat. The sample programs can be run directly against a live database.
  2. Configure the connection. The JDBC Driver for MongoDB can be used with any query or reporting software that supports JDBC. 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. ReadPreference Java API

    readparam JSON encoded string specifying ReadPreference parameters. If a String encoded JSON parameter string is specified, it is used to build a DBObject to pass to ReadPreference constructors. For example,
    readpref=primarypref
    &readparam={"name":"test"}
    will build a DBObject that is passed to primaryPreferred constructor.

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

    writeparam String tag name If specified, uses the given string as the parameter in the WriteConcern(String w) constructor.

    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.
  3. Connect to a database and run your queries. To help you take the JDBC Driver for MongoDB for a test drive, two MongoDB instances are provided for you at MongoLab (www.mongolab.com). Both instances are TPC-H databases.
    • 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. This instance is automatically refreshed every day.

    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

    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 number of rows returned by the UnityJDBC driver for MongoDB is unrestricted if the query can be directly translated and run natively by the driver. If the query is promoted to UnityJDBC, it will use the trial version of UnityJDBC. 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 UnityJDBC license here for access to the complete result set.

  4. 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>

Code Walk through

  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