Skip to content

Instantly share code, notes, and snippets.

@cflove
Last active January 9, 2020 23:12
Show Gist options
  • Save cflove/5752795 to your computer and use it in GitHub Desktop.
Save cflove/5752795 to your computer and use it in GitHub Desktop.
Experiment talking to MongoDB java driver directly in ColdFusion
<cfset Mongo = CreateObject("java","com.mongodb.MongoClient")>
<cffunction name="m" returntype="any">
<cfargument name="value" type="any">
<cfif IsJSON(arguments.value)>
<cfset local.retrun = CreateObject("java","com.mongodb.util.JSON").parse(arguments.value)>
<cfelse>
<cfset local.retrun = CreateObject("java","com.mongodb.util.JSON").parse( SerializeJSON(arguments.value) )>
</cfif>
<cfreturn local.retrun>
</cffunction>
<!--- Get/Create Database --->
<cfset db = Mongo.getDB('alexandria')>
<!---- *********************************************** --->
<!--- Authentication. Start MongoD with --auth option --->
<!---- *********************************************** --->
<!--- server authentication --->
<cfset admin = Mongo.getDB('admin')>
<cfset pw = createobject("java","java.lang.String").init(javacast("string","admin123")).toCharArray()>
<cfset admin.addUser( "admin",pw )>
<cfset admin.authenticate( "admin",pw )>
<cfset results = admin.isAuthenticated()>
<!--- database authentication --->
<cfset db.addUser( "admin",pw )>
<cfset db.authenticate( "admin",pw )>
<cfset results = db.isAuthenticated()>
<!--- List Database --->
<cfset results = Mongo.getDatabaseNames()>
<!--- Drop Database --->
<cfset Mongo.dropDatabase('TestDB')>
<!--- List Collection --->
<cfset results = "#db.getCollectionNames().toArray()#">
<!--- Get/Create Collection --->
<cfset people = db.getCollection('people')>
<!--- Remove Collection --->
<cfset people.remove( m("{}") )>
<!--- Drop Collection --->
<cfset db.getCollection('people').drop()>
<!--- ************************************************ --->
<!--- insert into [people] --->
<!--- ************************************************ --->
<cfset doc = {
"Name" = "Marc",
"Spouse"= "Heather",
"Fruit" = "Mango",
"Kids" = [
{"Name"="Alexis", "Age"=7, "Hair"="blonde", "Description"="crazy" },
{"Name"="Sidney", "Age"=2, "Hair"="dirty blonde", "Description"="ornery" }
],
"Bike" = "Felt",
"LoveSQL" = true,
"TS" = now(),
"Counter" = 1
}>
<cfset doc = SerializeJSON(doc)>
<cfset doc = m(doc)>
<cfset people.save( doc )>
<!--- insert multiple rocords as a array --->
<cfset records = ArrayNew(1)>
<!--- create set of records for insert --->
<cfloop from="1" to="10" index="i">
<cfset record = {
"Name" = "Cool Person #i#",
"Spouse" = "Cool Spouse #i#",
"Kids" = [
{"Name"="Alexis#i#", "Age"=7, "Hair"="blonde", "Description"="Witty" },
{"Name"="Sidney#i#", "Age"=2, "Hair"="dirty blonde", "Description"="Smart" }
],
"Bike" = "Specialized",
"LoveSQL" = YesNoFormat(BitAnd(i,1)),
"TS" = now(),
"Counter" = val(i+1)
}>
<cfset arrayAppend( records, record )>
</cfloop>
<!--- covert data format and insert --->
<cfset records = m(SerializeJSON(records))>
<cfset people.insert( records )>
<!--- ************************************************ --->
<!--- Select Records --->
<!--- ************************************************ --->
<!--- select count(*) from people --->
<cfset results = "#db.getCollection('people').count()#">
<!--- Select * from table --->
<cfset results = people.find().toArray() >
<!--- select * from people where name = "Marc" --->
<cfset results = people.find( m('{"Name":"Marc"}') ).toArray()>
<!--- Select Name,Spouse,Kids from table --->
<!--- where Name= 'Marc' --->
<cfset results = people.find( m( '{"Name":"Marc"}' ) ,m('{"Name":1,"Spouse":1,"Kids":1}')).toArray()>
<!--- select * from people where counter > 3 --->
<cfset results = people.find( m( '{"Counter":{"$gt":3}}' ) ).toArray()>
<!--- select * from people where --->
<!--- counter >= 2 and counter < 5 --->
<cfset results = people.find( m( '{"Counter":{"$gte":2,"$lt":5}}' ) ).toArray()>
<!--- select * from people where --->
<!--- counter >= 2 and counter <= 5 and LoveSQL = 0 --->
<!--- Order by Name Desc --->
<cfset results = people.find( m(' {"Counter":{"$gte":2,"$lte":5},"LoveSQL":false}' ) ).sort( m('{"Name":-1} ') ).toArray()>
<!--- select * from people where --->
<!--- Kids.Description = 'crazy' --->
<cfset results = people.find( m( '{"Kids.Description":"crazy"}' ) ).toArray() >
<!--- Get _ID out of each records --->
<cfset ID = results[1]['_id'].toString()>
<!--- select top 2 * from people where --->
<!--- counter >= 2 and counter < 7 --->
<cfset results = "#people.find( m(' {"Counter":{"$gte":2,"$lt":5}} ') ).limit(2).toArray()#">
<!--- select * from people where --->
<!--- counter >= 2 and counter < 7 Limit 2 --->
<cfset results = "#people.find( m(' {"$query":{"Counter":{"$gte":2,"$lt":5}},"$maxScan":2 } ') ).toArray() #">
<!--- Get Record by ID Object --->
<cfset results = "#people.findOne( m( '{"Counter":{"$gte":2,"$lt":5}}' ) )#">
<cfset results = people.findOne( results['_id'] ) >
<!--- Get Record by ID string --->
<cfset ObjectID = CreateObject("java","org.bson.types.ObjectId")>
<cfset results = people.findOne( ObjectID.init( ID ) ) >
<!--- Pull date out of a record --->
<cfset results = "#people.find().toArray()#">
<cfset results = results[1]['_id'].getTime()>
<cfset results = CreateObject("java","java.util.Date").init(results)>
<!--- select * from people where Column [Fruit] exists --->
<cfset results = "#people.find( m('{ "Fruit": { "$exists": true }}') ).toArray()#">
<!--- ************************************************ --->
<!--- Update people set Name= 'Genna', Spouse= 'Rose' --->
<!--- where Name = 'Marc' --->
<!--- ************************************************ --->
<cfset record = {"$set" ={
"Name" = "Genna",
"Spouse" = "Rose"
}}>
<cfset results = people.findAndModify( m( '{"Spouse":"Cool Spouse 2"}' ), m( SerializeJSON(record)), m( '{ "N":1 }' ) )>
<!--- create index [idx_name] on [people] [Name] --->
<cfset people.ensureIndex( m('{"Name":1}'),m(' {"name":"idx_name"} ') ) >
<!--- Get Index list --->
<cfset results = people.getIndexInfo()>
<!--- drop index [idx_name] --->
<cfset people.dropIndex('idx_name')>
<!--- ************************************************ --->
<!--- delete from [people] where Name = 'Mark' --->
<!--- ************************************************ --->
<cfset people.remove( m('{"Name":"Marc"}') )>
<!--- delete top 1 from [people] where --->
<!--- Specialized = 'bike' --->
<cfset results = people.findAndRemove( m('{"Bike":"Specialized"}'))>
<!--- Delete [Kids] array from all records --->
<cfset people.update( m('{}'), m(' {"$pop": { "Kids": 1 }}') )>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment