The better way to work with a SQL database and a programming language like java is by using a ORM (Object-Relational Mapping) layer such as hibernate or JPA.
An ORM layer basically provides an object oriented interface to a relational database. That means that instead of writing a query to insert or update a record, you assign some properties to an object and call a persist method. Instead of writing queries with joins, you can access related data through properties of an object.
For example, if you have a “post” object that represents a blog post, you can access it’s comments through the property “post.comments”. At first appearance, this might seem convenient.
The problem, however, is that this is horribly inefficient and it teaches developers to ignore how the underlying database works. In fact, it’s so inefficient that it just about mapping relational database systems in ORM system for this reasons were born a lot of new databases nosql like Apache Cassandra, MongoDB and others.
I only studied one of these technologies: Mongodb, because in my opinion it is the most used.
The differences between MySQL and mongodb are the following.
Data Representation
MySQL represents data in tables and rows.
MongoDB represents data as collections of JSON documents.
A JSON document is very much like what you would be working with in your application layer. If you are using javascript, it’s exactly what you’re working with. If you’re using PHP, it’s just like an associative array. If you’re using python, its just like a dictionary object.
Querying
The SQL in MySQL stands for Structured Query Language. That’s because you have to put together a string in this query language that is parsed by the database system. This is what makes SQL injection attacks possible.
MongoDB uses object JSON querying.
Relationships
One of the best things about MySQL and relational databases in general is the almighty JOIN operation. This allows you to perform queries across multiple tables.
MongoDB does not support joins, but it does multi-dimensional data types such as arrays and even other documents. Placing one document inside another is referred to as embedding. For example, if you were to create a blog using MySQL, you would have a table for posts and a table for comments. In MongoDB you might have a single collection of posts, and an array of comments within each post.
Transactions
Another great thing about MySQL is its support for atomic transactions. The ability to contain multiple operations within a transaction and roll back the whole thing as if it were a single operation.
MongoDB does not support transactions, but single operations are atomic.
Schema Definition
MySQL requires you to define your tables and columns before you can store anything, and every row in a table must have the same columns.
One of my favourite things about MongoDB is that you don’t define the schema. You just drop in documents, and two documents within a collection don’t even need to have the same fields.
Performance
MySQL often has poor performance. Well if you are using an ORM, performance will likely increased. If you are using a simple database wrapper and you’ve indexed your data correctly, you’ll get good performance
By sacrificing things like joins and providing excellent tools for performance analysis, MongoDB can perform much better than a relational database. You still need to index your data and the truth is that the vast majority applications out there don’t have enough data to notice the difference.
When should you use MySQL?
If your data structure fits nicely into tables and rows, MySQL will offer you robust and easy interaction with your data. If it’s performance that is your concern, there is a good chance you don’t really need MongoDB. Most likely, you just need to index your data properly. If you require SQL or transactions, you’ll have to stick with MySQL.
When should you use MongoDB?
If your data seems complex to model in a relational database system, or if you find yourself de-normalizing your database schema or coding around performance issues you should consider using MongoDB. If you find yourself trying to store serialized arrays or JSON objects, that’s a good sign that you are better off MongoDB. If you can’t pre-define your schema or you want to store records in the same collection that have different fields, that’s another good reason.
Example of mongodb use
> use mydb
switched to db mydb
> users ={"name": "Michele", "surname":"Rizzi" }
{ "name" : "Michele", "surname" : "Rizzi" }
> db.users.insert(users)
WriteResult({ "nInserted" : 1 })
> departments2={"name": "CS","value": "Computer Science" }
{ "name" : "CS", "value" : "Computer Science" }
> db.department.insert(departments)
WriteResult({ "nInserted" : 1 })
> db.department.insert(departments2)
WriteResult({ "nInserted" : 1 })
> db.users.find()
{ "_id" : ObjectId("53dbe6cc8f9fa523bbaef558"), "name" : "Michele", "surname" : "Rizzi" }
> db.department.find()
{ "_id" : ObjectId("53dbe7128f9fa523bbaef559"), "name" : "TC", "value" : "Telecomunications" }
{ "_id" : ObjectId("53dbe71f8f9fa523bbaef55a"), "name" : "CS", "value" : "Computer Science" }
> db.department.find().sort({name:1})
{ "_id" : ObjectId("53dbe71f8f9fa523bbaef55a"), "name" : "CS", "value" : "Computer Science" }
{ "_id" : ObjectId("53dbe7128f9fa523bbaef559"), "name" : "TC", "value" : "Telecomunications" }
> db.department.find().sort({name:-1})
{ "_id" : ObjectId("53dbe7128f9fa523bbaef559"), "name" : "TC", "value" : "Telecomunications" }
{ "_id" : ObjectId("53dbe71f8f9fa523bbaef55a"), "name" : "CS", "value" : "Computer Science" }
> db.department.update ({name:"TC"},{ $set: {name:"TLC"}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.department.find().sort({name:-1})
{ "_id" : ObjectId("53dbe7128f9fa523bbaef559"), "name" : "TLC", "value" : "Telecomunications" }
{ "_id" : ObjectId("53dbe71f8f9fa523bbaef55a"), "name" : "CS", "value" : "Computer Science" }
> db.department.remove({name: "CS"})
WriteResult({ "nRemoved" : 1 })
> db.department.find().sort({name:-1})
{ "_id" : ObjectId("53dbe7128f9fa523bbaef559"), "name" : "TLC", "value" : "Telecomunications" }
> db.department.update ({name:"TC"}, {$set: {namee:"TLC"}})
WriteResult({ "nMatched" : 0, "nUpserted" : 0, "nModified" : 0 })
> db.department.find().sort({name:-1})
{ "_id" : ObjectId("53dbe7128f9fa523bbaef559"), "name" : "TLC", "value" : "Telecomunications", "namee" : "TLC" }
> ObjectId()
ObjectId("53dbeb448f9fa523bbaef55b")
> ObjectId()
ObjectId("53dbeb468f9fa523bbaef55c")
> ObjectId()
ObjectId("53dbeb478f9fa523bbaef55d")
> ObjectId()
ObjectId("53dbeb4a8f9fa523bbaef55e")
> var id=ObjectId()
> id
ObjectId("53dbeb658f9fa523bbaef55f")
> db.department.update ({name:"TLC"}, {$set: {id: id}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.department.find().sort({name:-1})
{ "_id" : ObjectId("53dbe7128f9fa523bbaef559"), "name" : "TLC", "value" : "Telecomunications", "namee" : "TLC", "id" : ObjectId("53dbeb658f9fa523bbaef55f") }
> db.users
> db.users.update({},{$set: {depId:id}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.users.find()
{ "_id" : ObjectId("53dbe6cc8f9fa523bbaef558"), "name" : "Michele", "surname" : "Rizzi", "depId" : ObjectId("53dbeb658f9fa523bbaef55f") }
> db.users.find({name: $in: ["Michele","bla"]}})
2014-08-01T21:47:34.652+0200 SyntaxError: Unexpected token :
> db.users.find({$and:[{name: {$in: ["Michele","bla"]}}]})
{ "_id" : ObjectId("53dbe6cc8f9fa523bbaef558"), "name" : "Michele", "surname" : "Rizzi", "depId" : ObjectId("53dbeb658f9fa523bbaef55f") }
> db.users.find({$and:[{name: {$in: ["Michele","bla"]}}]},{name:1})
{ "_id" : ObjectId("53dbe6cc8f9fa523bbaef558"), "name" : "Michele" }
> db.users.find({$and:[{name: {$in: ["Michele","bla"]}}]})
{ "_id" : ObjectId("53dbe6cc8f9fa523bbaef558"), "name" : "Michele", "surname" : "Rizzi", "depId" : ObjectId("53dbeb658f9fa523bbaef55f") }
> db.users.find({$and:[{name: {$in: ["Michele","bla"]}}]},{id:1})
{ "_id" : ObjectId("53dbe6cc8f9fa523bbaef558") }
> var names=db.users.find({$and:[{name: {$in: ["Michele","bla"]}}]},{name:1});
> var names_id=names.hasNext()? names.next():null;
> db.users.find({name: names_id.name});
{ "_id" : ObjectId("53dbe6cc8f9fa523bbaef558"), "name" : "Michele", "surname" : "Rizzi", "depId" : ObjectId("53dbeb658f9fa523bbaef55f") }
> var s={ _id: ObjectId("50a8240b927d5d8b5891743c"), cust_id: "abc123", ord_date: new Date("Oct 04, 2012"), status: 'A', price: 25, items: [ { sku: "mmm", qty: 5, price: 2.5 }, { sku: "nnn", qty: 5, price: 2.5 } ]}
> s
{
"_id" : ObjectId("50a8240b927d5d8b5891743c"),
"cust_id" : "abc123",
"ord_date" : ISODate("2012-10-03T22:00:00Z"),
"status" : "A",
"price" : 25,
"items" : [
{
"sku" : "mmm",
"qty" : 5,
"price" : 2.5
},
{
"sku" : "nnn",
"qty" : 5,
"price" : 2.5
}
]
}
> var mapFunction1 = function() { emit(this.cust_id, this.price);};
> var reduceFunction1 = function(keyCustId, valuesPrices) { return Array.sum(valuesPrices);};
> db.users.insert(s)
WriteResult({ "nInserted" : 1 })
> db.users.mapReduce( mapFunction1, reduceFunction1, { out: "map_reduce_example" })
{
"result" : "map_reduce_example",
"timeMillis" : 343,
"counts" : {
"input" : 2,
"emit" : 2,
"reduce" : 0,
"output" : 2
},
"ok" : 1,
}
> var res1=db.users.mapReduce( mapFunction1, reduceFunction1, { out: "map_reduce_example" })
> db [res1.result].find()
{ "_id" : null, "value" : null }
{ "_id" : "abc123", "value" : 25 }
Example of integration with java
//connection
Mongo mongo = new Mongo("localhost", 27017);
DB db = mongo.getDB("database");
DBCollection collection = db.getCollection("collection");
//INSERT
BasicDBObject document = new BasicDBObject();
document.put("user", "Michele");
collection.insert(document);
//UPDATE WITH SET: REPLACE THE VALUE OF ONE FIELD
BasicDBObject updateDocument = new BasicDBObject();
updateDocument.append("$set", new BasicDBObject().append("user", "Ing Michele"));
collection.update(document, updateDocument);
//READ
BasicDBObject documentSearch = new BasicDBObject();
documentSearch.put("user", "Ing Michele");
DBCursor cursorDocJSON = collection.find(documentSearch);
while (cursorDocJSON.hasNext()) {
System.out.println(cursorDocJSON.next());
}
//DELETE
collection.remove(documentSearch);
