Free JDBC Driver for MongoDB®

The free 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.

The free version of the MongoDB JDBC driver is for client applications where only one connection is required. The free version has all the features of the licensed version. It may be used and distributed for all non-server applications including open source projects. The version is suitable for desktop query software including Tableau, DBeaver, SQuirreL, Aqua Data Studio, and others. For questions regarding server applications (e.g. Splunk DBConnect, Tableau Server, ETL, web servers) contact [email protected].

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 mongodb_unityjdbc_free.jar.
2. Install the mongodb_unityjdbc_free.jar into your application or development environment. 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

   Attribute	Values	Description
   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 free version of the UnityJDBC driver for MongoDB has no row or feature limitations. It is limited to one active connection at a time. If a server application is required or more concurrent connections, 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.

	Class.forName("mongodb.jdbc.MongoDriver");

2. Connect to the URL. The last part is the database name (tpch in this case).

	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.

	stmt = con.createStatement();
	String sql = "SELECT * FROM nation WHERE n_name >= 'C';";
	rst = stmt.executeQuery(sql);

4. Print out your results.

	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.

	rst.close();
	stmt.close();
	con.close();

Mongo and MongoDB are trademarks of 10gen, Inc.