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. The Type 4/5 JDBC driver has the highest performance when querying MongoDB.

NEW: A free version of the MongoDB JDBC driver has been released for client applications where only one connection is required. The free version has all the features and may be used and distributed for all non-server applications including open source projects.

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:mongodb://<serverName>/<databaseName>
	Alternate URL format:	 jdbc:mongo://<serverName>/<databaseName>
	mongodb+srv format:	 jdbc:mongodb+srv://<serverName>/<databaseName>
    The mongodb and mongodb+srv formats are directly from MongoDB. 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.
    Connector Argument Details
    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. Examples:

    JDBC URL using mongodb format (sent directly to Mongo Java library): 

    jdbc:mongodb://testserver/testdatabase

    JDBC URL using mongo format (driver interpreted with specified schema location): 

    jdbc:mongo://testserver/testdatabase?rebuildschema=true&schema=/temp/schemaloc.xml

    JDBC URL using mongodb+srv format (for cloud MongoDB Atlas): 

    jdbc:mongodb+srv://mongoatlascluster-yourserver.mongodb.net/database?authSource=admin&replicaSet=MongoAtlasCluster-shard-0&readPreference=primary&ssl=true

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");
  2. Connect to the URL. The last part is the database name (tpch in this case).
    3. 	String url="jdbc:mongo://server/tpch";
	con = DriverManager.getConnection(url, "user", "password");
  3. Create a statement and submit a query. See the features and SQL examples supported by the JDBC driver for MongoDB.
    4. 	stmt = con.createStatement();
	String sql = "SELECT * FROM nation WHERE n_name >= 'C';";
	rst = stmt.executeQuery(sql);
  4. Print out your results.
    5. 	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();
	}
  5. Close the statement and connection.
    6. 	rst.close();
	stmt.close();
	con.close();

    Mongo and MongoDB are trademarks of 10gen, Inc.

Setup Instructions

Code Examples