NoSQL databases are pretty hot these days.
NoSQL databases are actually many different types of databases only sharing the fact that they are not relational and do not use SQL as interface language.
This article will look at the family of document store NoSQL databases.
For other NoSQL database families see:
A document store basically stores structured documents but without enforcing any specific structure.
Question: How is a document store different from a key value store?
Answer: A key value store consider data a big opaque BLOB. A document store understands the data, which allows advanced queries based on the data. Or to look at in a another way: a document store provides extra functionality on top of an underlying key value store.
Comparison:
Database type | Structured | Fixed schema |
---|---|---|
relational | yes | yes |
key value store | no | no |
document store | yes | no |
Schema-less structured data are sometimes called semi-structured data.
The documents are typical stored in JSON format, but a few databases use XML format.
A key featured in a document store is the ability to query data within the documents stored. To support that efficiently the document stores need to implement some type of indexing capability.
MongoDB is by far the most widely used document store NoSQL database. MongoDB users include companies like Barclays, HSBC and Uber.
It is open source and was first released in 2009. It was created by a company 10gen that in 2013 changed its name to MongoDB Inc.
The license situation is a bit tricky. Servers up to and including 4.03 and 4.1.4 are under AGPL license. Servers from 4.0.4 and 4.1.5 and newer are under SSPL license (which is not an OSI approved open source license). Client libraries are under Apache 2.0 license. Please check carefully before usage.
MongoDB is a server with client libraries for many languages.
Data model:
Supported platforms | Linux, Windows, MacOS X, Solaris, FreeBSD |
Supported languages | C, C++, Java, C#/VB.NET, Python, PHP, node.js, Go, Perl, Ruby, Swift |
Features | distributed CP database supported by most technologies |
Missing features |
MongoDB can be used in many different ways. Three examples follow.
Let us see an example where MongoDB is really just used as a key value store - aka not fully utilizing the document store aspect.
#include <string>
#include <iostream>
using std::cout;
using std::endl;
using std::string;
using std::to_string;
#include <bsoncxx/json.hpp>
#include <mongocxx/client.hpp>
#include <mongocxx/stdx.hpp>
#include <mongocxx/uri.hpp>
#include <mongocxx/instance.hpp>
using mongocxx::instance;
using mongocxx::uri;
using mongocxx::client;
using mongocxx::database;
using mongocxx::collection;
using mongocxx::cursor;
using bsoncxx::document::value;
using bsoncxx::document::view;
using bsoncxx::builder::basic::kvp;
using bsoncxx::builder::basic::make_document;
using bsoncxx::to_json;
struct Data
{
string id;
int iv;
double xv;
string sv;
};
static value serialize(Data *o)
{
return make_document(kvp("id", o->id), kvp("iv", o->iv), kvp("xv", o->xv), kvp("sv", o->sv));
}
static Data deserialize(view d)
{
Data o;
o.id = d["id"].get_utf8().value.to_string();
o.iv = d["iv"].get_int32();
o.xv = d["xv"].get_double();
o.sv = d["sv"].get_utf8().value.to_string();
return o;
}
static void dump(collection col, view doc)
{
cout << "dumping " << to_json(doc) << endl;
bool foundsome = false;
for(view d : col.find(doc))
{
//cout << to_json(d) << endl;
Data o = deserialize(d);
cout << "(" << o.iv << "," << o.xv << "," << o.sv << ")" << endl;
foundsome = true;
}
if(!foundsome)
{
cout << "Not found" << endl;
}
}
static const int NREC = 1000;
static const int CPP_OFFSET = 1 * NREC;
int main()
{
instance instance{};
// open
uri uri("mongodb://localhost:27017");
client client(uri);
database db = client["TestDB"];
collection col = db["data"];
// put data
for(int i = 0; i < NREC; i++)
{
Data o;
o.id = "Doc#" + to_string(CPP_OFFSET + i + 1);
o.iv = i + 1;
o.xv = i + 1.0;
o.sv = "This is value " + to_string(i + 1);
value d = serialize(&o);
col.insert_one(d.view());
}
//
value doc77 = make_document(kvp("id", "Doc#" + to_string(CPP_OFFSET + 77)));
// get
dump(col, doc77.view());
// delete
col.delete_one(doc77.view());
// get non-existing
dump(col, doc77.view());
// get and update
value doc88 = make_document(kvp("id", "Doc#" + to_string(CPP_OFFSET + 88)));
dump(col, doc88.view());
Data o = deserialize(*col.find_one(doc88.view()));
o.iv = o.iv + 1;
o.xv = o.xv + 0.1;
o.sv = o.sv + " updated";
col.find_one_and_replace(doc88.view(), serialize(&o));
dump(col, doc88.view());
// list all
cursor it = col.find(make_document());
int n = 0;
for(view itd : it)
{
Data ito = deserialize(itd);
if(ito.id.rfind("Doc#", 0) != 0)
{
cout << "Unexpected document: " << ito.id << endl;
}
if(ito.iv < 1 || NREC < ito.iv)
{
cout << "Unexpected value: (" << ito.iv << "," << ito.xv << "," << ito.sv << ")" << endl;
}
n++;
}
cout << n << endl;
cursor it2 = col.find(make_document(kvp("id", make_document(kvp("$gte", "Doc#" + to_string(CPP_OFFSET + 75)), kvp("$lt", "Doc#" + to_string(CPP_OFFSET + 85))))));
int n2 = 0;
for(view it2d : it2)
{
Data it2o = deserialize(it2d);
if(it2o.id.rfind("Doc#", 0) != 0)
{
cout << "Unexpected document: " << it2o.id << endl;
}
n2++;
}
cout << n2 << endl;
return 0;
}
Build on Windows with MSVC++:
cl /EHsc /MD /DMONGOCXX_STATIC /DBSONCXX_STATIC /DMONGOC_STATIC /DBSON_STATIC /DBSONCXX_POLY_USE_BOOST=1 /I%MONGODB_DIR%\include /I%BOOST_DIR%\include Test.cpp %MONGODB_DIR%\lib\mongocxx.lib %MONGODB_DIR%\lib\bsoncxx.lib
package nosql.mongodb;
import java.io.Serializable;
public class Data implements Serializable {
private static final long serialVersionUID = 1L;
private String id;
private int iv;
private double xv;
private String sv;
public Data() {
this("", 0, 0.0, "");
}
public Data(String id, int iv, double xv, String sv) {
this.id = id;
this.iv = iv;
this.xv = xv;
this.sv = sv;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public int getIv() {
return iv;
}
public void setIv(int iv) {
this.iv = iv;
}
public double getXv() {
return xv;
}
public void setXv(double xv) {
this.xv = xv;
}
public String getSv() {
return sv;
}
public void setSv(String sv) {
this.sv = sv;
}
@Override
public String toString() {
return String.format("{iv: %d, xv: %f, sv: %s}", iv, xv, sv);
}
}
package nosql.mongodb;
import java.util.HashMap;
import java.util.Map;
import org.bson.Document;
import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
public class Test {
private static Document serialize(Data o) {
Document res = new Document();
res.put("id", o.getId());
res.put("iv", o.getIv());
res.put("xv", o.getXv());
res.put("sv", o.getSv());
return res;
}
private static Data deserialize(Document doc) {
Data res = new Data();
res.setId(doc.getString("id"));
res.setIv(doc.getInteger("iv"));
res.setXv(doc.getDouble("xv"));
res.setSv(doc.getString("sv"));
return res;
}
private static void dump(MongoCollection<Document> col, Document doc) {
System.out.println("dumping " + doc.toJson());
boolean foundsome = false;
for(Document d : col.find(doc)) {
//System.out.println(d.toJson());
Data o = deserialize(d);
System.out.println(o);
foundsome = true;
}
if(!foundsome) {
System.out.println("Not found");
}
}
private static final int NREC = 1000;
private static final int JAVA_OFFSET = 2 * NREC;
public static void main(String[] args) {
// open
MongoClient client = new MongoClient("localhost", 27017);
MongoDatabase db = client.getDatabase("TestDB");
MongoCollection<Document> col = db.getCollection("data");
// put data
for(int i = 0; i < NREC; i++) {
Data o = new Data("Doc#" + (JAVA_OFFSET + i + 1), i + 1, i + 1.0, String.format("This is value %d", i + 1));
col.insertOne(serialize(o));
}
//
Document doc = new Document();
doc.put("id", "Doc#" + (JAVA_OFFSET + 77));
// get
dump(col, doc);
// delete
col.deleteMany(doc);
// get non existing
dump(col, doc);
// update and get
doc.put("id", "Doc#" + (JAVA_OFFSET + 88));
dump(col, doc);
Data o = deserialize(col.find(doc).first());
o.setIv(o.getIv() + 1);
o.setXv(o.getXv() + 0.1);
o.setSv(o.getSv() + " updated");
col.findOneAndReplace(doc, serialize(o));
dump(col, doc);
// list all
int n = 0;
for(Document itd : col.find()) {
Data ito = deserialize(itd);
if(!ito.getId().startsWith("Doc#")) {
System.out.println("Unexpected document id: " + ito.getId());
}
if(ito. getIv() < 1 || NREC < ito.getIv()) {
System.out.println("Unexpected value :" + ito);
}
n++;
}
System.out.println(n);
// list documents where "Doc#n075" <= id < "Doc#n085"
int n2 = 0;
Map<String,Object> range = new HashMap<>();
range.put("$gte", "Doc#" + (JAVA_OFFSET + 75));
range.put("$lt", "Doc#" + (JAVA_OFFSET + 85));
doc.put("id", range);
for(Document it2d : col.find(doc)) {
Data it2o = deserialize(it2d);
if(!it2o.getId().startsWith("Doc#")) {
System.out.println("Unexpected document id: " + it2o.getId());
}
n2++;
}
System.out.println(n2);
// close
client.close();
}
}
There is an upcoming Java EE standard for NoSQL databases: Jakarta NoSQL. The reference implementation is Eclipse JNoSQL.
The reference implementation comes with driver for MongoDB.
The example is tested with B4 release.
package nosql.mongodb.jnosql;
import java.io.Serializable;
public class Data implements Serializable {
private static final long serialVersionUID = 1L;
private String id;
private int iv;
private double xv;
private String sv;
public Data() {
this("", 0, 0.0, "");
}
public Data(String id, int iv, double xv, String sv) {
this.id = id;
this.iv = iv;
this.xv = xv;
this.sv = sv;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public int getIv() {
return iv;
}
public void setIv(int iv) {
this.iv = iv;
}
public double getXv() {
return xv;
}
public void setXv(double xv) {
this.xv = xv;
}
public String getSv() {
return sv;
}
public void setSv(String sv) {
this.sv = sv;
}
@Override
public String toString() {
return String.format("{iv: %d, xv: %f, sv: %s}", iv, xv, sv);
}
}
package nosql.mongodb.jnosql;
import java.util.HashMap;
import java.util.Map;
import java.util.stream.Collectors;
import org.eclipse.jnosql.communication.mongodb.document.MongoDBDocumentConfiguration;
import jakarta.nosql.Settings;
import jakarta.nosql.document.DocumentCollectionManager;
import jakarta.nosql.document.DocumentCollectionManagerFactory;
import jakarta.nosql.document.DocumentConfiguration;
import jakarta.nosql.document.DocumentDeleteQuery;
import jakarta.nosql.document.DocumentEntity;
import jakarta.nosql.document.DocumentQuery;
public class Test {
private static void serialize(Data o, DocumentEntity de) {
de.add("id", o.getId());
de.add("iv", o.getIv());
de.add("xv", o.getXv());
de.add("sv", o.getSv());
}
private static DocumentEntity serialize(Data o) {
DocumentEntity res = DocumentEntity.of("data");
serialize(o, res);
return res;
}
private static Data deserialize(DocumentEntity de) {
Data res = new Data();
Map<String,Object> dem = de.toMap();
res.setId((String)dem.get("id"));
res.setIv((Integer)dem.get("iv"));
res.setXv((Double)dem.get("xv"));
res.setSv((String)dem.get("sv"));
return res;
}
private static void dump(DocumentCollectionManager dcm, DocumentQuery dq) {
System.out.println("dumping " + dq);
boolean foundsome = false;
for(DocumentEntity de : dcm.select(dq).collect(Collectors.toList())) {
//System.out.println(de);
Data o = deserialize(de);
System.out.println(o);
foundsome = true;
}
if(!foundsome) {
System.out.println("Not found");
}
}
private static final int NREC = 1000;
private static final int JNOSQL_OFFSET = 10 * NREC;
public static void main(String[] args) {
// open
DocumentConfiguration cfg = new MongoDBDocumentConfiguration();
Map<String, Object> props = new HashMap<>();
props.put("mongodb.host", "localhost:27017");
DocumentCollectionManagerFactory dcmf = cfg.get(Settings.of(props));
DocumentCollectionManager dcm = dcmf.get("data");
// put data
for(int i = 0; i < NREC; i++) {
Data o = new Data("Doc#" + (JNOSQL_OFFSET + i + 1), i + 1, i + 1.0, String.format("This is value %d", i + 1));
dcm.insert(serialize(o));
}
//
DocumentQuery dq;
dq = DocumentQuery.select().from("data").where("id").eq("Doc#" + (JNOSQL_OFFSET + 77)).build();
// get
dump(dcm, dq);
// delete
dcm.delete(DocumentDeleteQuery.delete().from("data").where("id").eq("Doc#" + (JNOSQL_OFFSET + 77)).build());
// get non existing
dump(dcm, dq);
// update and get
dq = DocumentQuery.select().from("data").where("id").eq("Doc#" + (JNOSQL_OFFSET + 88)).build();
dump(dcm, dq);
DocumentEntity de = dcm.select(dq).findFirst().get();
Data o = deserialize(de);
o.setIv(o.getIv() + 1);
o.setXv(o.getXv() + 0.1);
o.setSv(o.getSv() + " updated");
serialize(o, de);
dcm.update(de);
dump(dcm, dq);
// list all
int n = 0;
dq = DocumentQuery.select().from("data").build();
for(DocumentEntity itd : dcm.select(dq).collect(Collectors.toList())) {
Data ito = deserialize(itd);
if(!ito.getId().startsWith("Doc#")) {
System.out.println("Unexpected document id: " + ito.getId());
}
if(ito. getIv() < 1 || NREC < ito.getIv()) {
System.out.println("Unexpected value :" + ito);
}
n++;
}
System.out.println(n);
// list documents where "Doc#n075" <= id < "Doc#n085"
int n2 = 0;
dq = DocumentQuery.select().from("data").where("id").gte("Doc#" + (JNOSQL_OFFSET + 75)).and("id").lt("Doc#" + (JNOSQL_OFFSET + 85)).build();
for(DocumentEntity it2d : dcm.select(dq).collect(Collectors.toList())) {
Data it2o = deserialize(it2d);
if(!it2o.getId().startsWith("Doc#")) {
System.out.println("Unexpected document id: " + it2o.getId());
}
n2++;
}
System.out.println(n2);
// close
dcm.close();
dcmf.close();
}
}
using System;
using System.Collections.Generic;
using MongoDB.Driver;
using MongoDB.Bson;
namespace NoSQL.MongoDB
{
public class Data
{
public string Id { get; set; }
public int Iv { get; set; }
public double Xv { get; set; }
public string Sv { get; set; }
public override string ToString()
{
return string.Format("(iv: {0}, xv: {1}, sv: {2})", Iv, Xv, Sv);
}
}
public class Program
{
private static BsonDocument Serialize(Data o)
{
BsonDocument res = new BsonDocument();
res.Add("id", o.Id);
res.Add("iv", o.Iv);
res.Add("xv", o.Xv);
res.Add("sv", o.Sv);
return res;
}
private static Data Deserialize(BsonDocument doc)
{
Data res = new Data();
res.Id = doc.GetValue("id").AsString;
res.Iv = doc.GetValue("iv").AsInt32;
res.Xv = doc.GetValue("xv").AsDouble;
res.Sv = doc.GetValue("sv").AsString;
return res;
}
private static void Dump(IMongoCollection<BsonDocument> col, BsonDocument doc)
{
Console.WriteLine("dumping " + doc.ToJson());
bool foundsome = false;
foreach (BsonDocument d in col.Find(doc).ToEnumerable())
{
//Console.WriteLine(d.ToJson());
Data o = Deserialize(d);
Console.WriteLine(o);
foundsome = true;
}
if (!foundsome)
{
Console.WriteLine("Not found");
}
}
private const int NREC = 1000;
private const int DN_OFFSET = 3 * NREC;
public static void Main(string[] args)
{
// open
MongoClient client = new MongoClient("mongodb://localhost:27017");
IMongoDatabase db = client.GetDatabase("TestDB");
IMongoCollection<BsonDocument> col = db.GetCollection<BsonDocument>("data");
// put data
for (int i = 0; i < NREC; i++)
{
Data onew = new Data { Id = "Doc#" + (DN_OFFSET + i + 1), Iv = i + 1, Xv = i + 1.0, Sv = string.Format("This is value {0}", i + 1) };
col.InsertOne(Serialize(onew));
}
BsonDocument doc = new BsonDocument();
doc["id"] = "Doc#" + (DN_OFFSET + 77);
// get
Dump(col, doc);
// delete
col.DeleteMany(doc);
// get non existing
Dump(col, doc);
// update and get
doc["id"] = "Doc#" + (DN_OFFSET + 88);
Dump(col, doc);
Data o = Deserialize(col.Find(doc).First());
o.Iv = o.Iv + 1;
o.Xv = o.Xv + 0.1;
o.Sv = o.Sv + " updated";
col.FindOneAndReplace(doc, Serialize(o));
Dump(col, doc);
// list all
int n = 0;
foreach (BsonDocument itd in col.Find(new BsonDocument()).ToEnumerable())
{
Data ito = Deserialize(itd);
if (!ito.Id.StartsWith("Doc#"))
{
Console.WriteLine("Unexpected document id: " + ito.Id);
}
if (ito.Iv < 1 || NREC < ito.Iv)
{
Console.WriteLine("Unexpected value :" + ito);
}
n++;
}
Console.WriteLine(n);
// list documents where "Doc#n075" <= id < "Doc#n085"
int n2 = 0;
IDictionary<String,Object> range = new Dictionary<String,Object>();
range.Add("$gte", "Doc#" + (DN_OFFSET + 75));
range.Add("$lt", "Doc#" + (DN_OFFSET + 85));
doc["id"] = new BsonDocument(range);
foreach (BsonDocument it2d in col.Find(doc).ToEnumerable())
{
Data it2o = Deserialize(it2d);
if (!it2o.Id.StartsWith("Doc#"))
{
Console.WriteLine("Unexpected document id: " + it2o.Id);
}
n2++;
}
Console.WriteLine(n2);
}
}
}
from pymongo import *
class Data(object):
def __init__(self, _id = '', _iv = 0, _xv = 0.0, _sv = ''):
self.id = _id
self.iv = _iv
self.xv = _xv
self.sv = _sv
def __str__(self):
return '(%d, %f, %s)' % (self.iv, self.xv, self.sv)
def serialize(o):
return { 'id': o.id, 'iv': o.iv, 'xv': o.xv, 'sv': o.sv }
def deserialize(d):
return Data(d['id'], d['iv'], d['xv'], d['sv'])
def dump(col, doc):
print('dumping ' + str(doc))
o = col.find_one(doc)
if not o is None:
# print(o)
d = deserialize(o)
print(d)
else:
print('Not found')
NREC = 1000
PY_OFFSET = 4 * NREC
# open
client = MongoClient('localhost', 27017)
db = client.TestDB
col = db.data
# put data
for i in range(NREC):
o = Data('Doc#' + str(PY_OFFSET + i + 1), i + 1, i + 1.0, 'This is value %d' % (i + 1))
col.insert_one(serialize(o))
#get
doc = { 'id': 'Doc#' + str(PY_OFFSET + 77) }
# get
dump(col, doc)
# delete
col.delete_many(doc)
# get non existing
dump(col, doc)
# update and get
doc = { 'id': 'Doc#' + str(PY_OFFSET + 88) }
dump(col, doc)
o = deserialize(col.find_one(doc))
o.iv = o.iv + 1
o.xv = o.xv + 0.1
o.sv = o.sv + ' updated'
col.find_one_and_replace(doc, serialize(o));
dump(col, doc)
# list all
n = 0
for itd in col.find():
ito = deserialize(itd)
if not ito.id.startswith('Doc#'):
print('Unexpected document: ' + ito.id)
if ito.iv < 1 or NREC < ito.iv:
print('Unexpected value: ' + ito)
n = n + 1
print(n)
# list documents where "Doc#n075" <= id < "Doc#n085"
n2 = 0
doc = { 'id': {'$gte': 'Doc#' + str(PY_OFFSET + 75), '$lt': 'Doc#' + str(PY_OFFSET + 85) } }
for it2d in col.find(doc):
it2o = deserialize(it2d)
if not it2o.id.startswith('Doc#'):
print('Unexpected document: ' + it2o.id)
n2 = n2 + 1
print(n2)
<?php
spl_autoload_register(function ($clznam) {
include $clznam . '.php';
});
require 'MongoDB/functions.php';
use MongoDB\Client;
class Data {
private $id;
private $iv;
private $xv;
private $sv;
public function __construct($id = '', $iv = 0, $xv = 0.0, $sv = '') {
$this->id = $id;
$this->iv = $iv;
$this->xv = $xv;
$this->sv = $sv;
}
public function getId()
{
return $this->id;
}
public function getIv()
{
return $this->iv;
}
public function getXv()
{
return $this->xv;
}
public function getSv()
{
return $this->sv;
}
public function setId($id)
{
$this->id = $id;
}
public function setIv($iv)
{
$this->iv = $iv;
}
public function setXv($xv)
{
$this->xv = $xv;
}
public function setSv($sv)
{
$this->sv = $sv;
}
public function __toString() {
return sprintf('(%d, %f, %s)', $this->iv, $this->xv, $this->sv);
}
}
function myserialize($o) {
return array('id' => $o->getId(), 'iv' => $o->getIv(), 'xv' => $o->getXv(), 'sv' => $o->getSv());
}
function mydeserialize($d) {
return new Data($d['id'], $d['iv'], $d['xv'], $d['sv']);
}
function dump($col, $doc) {
echo 'dumping ' . json_encode($doc) . "\r\n";
$da = $col->find($doc)->toArray();
if(count($da) > 0) {
//echo json_encode($da[0]);
$o = mydeserialize($da[0]);
echo "$o\r\n";
} else {
echo "Not found\r\n";
}
}
define('NREC', 1000);
define('PHP_OFFSET', 5 * 1000);
// open
$client = new Client('mongodb://localhost:27017');
$db = $client->TestDB;
$col = $db->data;
// put all
for($i = 0; $i < NREC; $i++) {
$o = new Data('Doc#' . (PHP_OFFSET + $i + 1), $i + 1, $i + 1.0, 'This is value ' . ($i + 1));
$col->insertOne(myserialize($o));
}
//
$doc = array('id' => 'Doc#' . (PHP_OFFSET + 77));
// get
dump($col, $doc);
// delete
$col->deleteOne($doc);
// get non-existing
dump($col, $doc);
// update and get
$doc = array('id' => 'Doc#' . (PHP_OFFSET + 88));
dump($col, $doc);
$o = mydeserialize($col->findOne($doc));
$o->setIv($o->getIv() + 1);
$o->setXv($o->getXv() + 0.1);
$o->setSv($o->getSv() . ' updated');
$col->findOneAndReplace($doc, myserialize($o));
dump($col, $doc);
// list all
$n = 0;
foreach($col->find() as $itd) {
$ito = mydeserialize($itd);
if(strpos($ito->getId(), 'Doc#') != 0) {
echo 'Unexcpected document: ' . $ito->getId() . "\r\n";
}
if($ito->getIv() < 1 || NREC < $ito->getIv()) {
echo 'Unexcpected value: ' . $ito . "\r\n";
}
$n = $n + 1;
}
echo "$n\r\n";
// list documents where "Doc#n075" <= id < "Doc#n085"
$n2 = 0;
$doc = array( 'id' => array( '$gte' => 'Doc#' . (PHP_OFFSET + 75), '$lt' => 'Doc#' . (PHP_OFFSET + 85) ));
foreach($col->find($doc) as $it2d) {
$it2o = mydeserialize($it2d);
if(strpos($it2o->getId(), 'Doc#') != 0) {
echo 'Unexcpected document: ' . $it2o->getId() . "\r\n";
}
$n2 = $n2 + 1;
}
echo "$n2\r\n";
?>
Let us see an example where MongoDB is used to store document using a fixed schema (just enforced by the application instead of the database) and standard queries against it.
package nosql.mongodb;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public String getItemName() {
return itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public int getQuantity() {
return quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
}
package nosql.mongodb;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public String getCustomer() {
return customer;
}
public List<OrderLine> getItems() {
return items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
}
package nosql.mongodb;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.bson.Document;
import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
public class Test2 {
public static Document serialize(OrderLine ol) {
Document d = new Document();
d.put("itemNo", ol.getItemNo());
d.put("itemName", ol.getItemName());
d.put("itemPrice", ol.getItemPrice().toString());
d.put("quantity", ol.getQuantity());
return d;
}
public static Document serialize(Order o) {
Document d = new Document();
d.put("orderId", o.getOrderId());
d.put("customer", o.getCustomer());
List<Document> a = new ArrayList<>();
for(OrderLine ol : o.getItems()) {
a.add(serialize(ol));
}
d.put("items", a);
return d;
}
public static OrderLine deserializeOrderLine(Document d) {
return new OrderLine(d.getInteger("itemNo"), d.getString("itemName"), new BigDecimal(d.getString("itemPrice")), d.getInteger("quantity"));
}
public static Order deserializeOrder(Document d) {
Order res = new Order(d.getInteger("orderId"), d.getString("customer"));
List<Document> a = d.getList("items", Document.class);
for(Document a1 : a) {
res.getItems().add(deserializeOrderLine(a1));
}
return res;
}
private static void dump(MongoCollection<Document> col, Document criteria) {
System.out.println("dumping " + criteria.toJson());
for(Document d : col.find(criteria)) {
//System.out.println(d.toJson());
Order o = deserializeOrder(d);
System.out.printf("Id : %d\n", o.getOrderId());
System.out.printf("Customer : %s\n", o.getCustomer());
System.out.printf("Price : %s\n", o.getPrice());
for(OrderLine ol : o.getItems()) {
System.out.printf(" %2d %-30s %10s %4d %10s\n", ol.getItemNo(), ol.getItemName(), ol.getItemPrice(), ol.getQuantity(), ol.getPrice());
}
}
}
public static void main(String[] args) {
// open
MongoClient client = new MongoClient("localhost", 27017);
MongoDatabase db = client.getDatabase("TestDB");
MongoCollection<Document> col = db.getCollection("java_order");
// setup
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
col.insertOne(serialize(o1));
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
col.insertOne(serialize(o2));
// query
Document d;
d = new Document(); // all
dump(col, d);
d = new Document("orderId", 2); // where id=2
dump(col, d);
d = new Document("customer", "A A"); // where customer='A A'
dump(col, d);
d = new Document("customer", new Document("$regex", "A.*"));// where customer like 'A%'
dump(col, d);
d = new Document("items.itemName", "i5 CPU"); // where items.name='i5 CPU'
dump(col, d);
d = new Document("items.itemName", new Document("$regex", ".*Java.*")); // where items.name like '%Java%'
dump(col, d);
d = new Document("orderId", new Document("$in", Arrays.asList(1, 2))); // where id in (1,2)
dump(col, d);
// close
client.close();
}
}
package nosql.mongodb;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public String getItemName() {
return itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public int getQuantity() {
return quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
}
package nosql.mongodb;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public String getCustomer() {
return customer;
}
public List<OrderLine> getItems() {
return items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
}
package nosql.mongodb;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import org.bson.Document;
import org.bson.conversions.Bson;
import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import static com.mongodb.client.model.Filters.*;
public class Test2Alt {
public static Document serialize(OrderLine ol) {
Document d = new Document();
d.put("itemNo", ol.getItemNo());
d.put("itemName", ol.getItemName());
d.put("itemPrice", ol.getItemPrice().toString());
d.put("quantity", ol.getQuantity());
return d;
}
public static Document serialize(Order o) {
Document d = new Document();
d.put("orderId", o.getOrderId());
d.put("customer", o.getCustomer());
List<Document> a = new ArrayList<>();
for(OrderLine ol : o.getItems()) {
a.add(serialize(ol));
}
d.put("items", a);
return d;
}
public static OrderLine deserializeOrderLine(Document d) {
return new OrderLine(d.getInteger("itemNo"), d.getString("itemName"), new BigDecimal(d.getString("itemPrice")), d.getInteger("quantity"));
}
public static Order deserializeOrder(Document d) {
Order res = new Order(d.getInteger("orderId"), d.getString("customer"));
List<Document> a = d.getList("items", Document.class);
for(Document a1 : a) {
res.getItems().add(deserializeOrderLine(a1));
}
return res;
}
private static void dump(MongoCollection<Document> col, Bson criteria) {
System.out.println("dumping " + criteria);
for(Document d : col.find(criteria)) {
//System.out.println(d.toJson());
Order o = deserializeOrder(d);
System.out.printf("Id : %d\n", o.getOrderId());
System.out.printf("Customer : %s\n", o.getCustomer());
System.out.printf("Price : %s\n", o.getPrice());
for(OrderLine ol : o.getItems()) {
System.out.printf(" %2d %-30s %10s %4d %10s\n", ol.getItemNo(), ol.getItemName(), ol.getItemPrice(), ol.getQuantity(), ol.getPrice());
}
}
}
public static void main(String[] args) {
// open
MongoClient client = new MongoClient("localhost", 27017);
MongoDatabase db = client.getDatabase("TestDB");
MongoCollection<Document> col = db.getCollection("java_order");
// setup
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
col.insertOne(serialize(o1));
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
col.insertOne(serialize(o2));
// query
Bson d;
d = gte("orderId", 0); // all (simulated with id >= 0)
dump(col, d);
d = eq("orderId", 2); // where id=2
dump(col, d);
d = eq("customer", "A A"); // where customer='A A'
dump(col, d);
d = regex("customer", "A.*");// where customer like 'A%'
dump(col, d);
d = eq("items.itemName", "i5 CPU"); // where items.name='i5 CPU'
dump(col, d);
d = regex("items.itemName", ".*Java.*"); // where items.name like '%Java%'
dump(col, d);
d = in("orderId", 1, 2); // where id in (1,2)
dump(col, d);
// close
client.close();
}
}
Java client library comes with an async API besides the normal sync API.
Java async is deprecated since version 3.9. Use with caution.
package nosql.mongodb;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public String getItemName() {
return itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public int getQuantity() {
return quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
}
package nosql.mongodb;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public String getCustomer() {
return customer;
}
public List<OrderLine> getItems() {
return items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
}
package nosql.mongodb;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import org.bson.Document;
import com.mongodb.Block;
import com.mongodb.async.SingleResultCallback;
import com.mongodb.async.client.MongoClient;
import com.mongodb.async.client.MongoClients;
import com.mongodb.async.client.MongoCollection;
import com.mongodb.async.client.MongoDatabase;
@SuppressWarnings("deprecation")
public class Test2Async {
public static Document serialize(OrderLine ol) {
Document d = new Document();
d.put("itemNo", ol.getItemNo());
d.put("itemName", ol.getItemName());
d.put("itemPrice", ol.getItemPrice().toString());
d.put("quantity", ol.getQuantity());
return d;
}
public static Document serialize(Order o) {
Document d = new Document();
d.put("orderId", o.getOrderId());
d.put("customer", o.getCustomer());
List<Document> a = new ArrayList<>();
for(OrderLine ol : o.getItems()) {
a.add(serialize(ol));
}
d.put("items", a);
return d;
}
public static OrderLine deserializeOrderLine(Document d) {
return new OrderLine(d.getInteger("itemNo"), d.getString("itemName"), new BigDecimal(d.getString("itemPrice")), d.getInteger("quantity"));
}
public static Order deserializeOrder(Document d) {
Order res = new Order(d.getInteger("orderId"), d.getString("customer"));
List<Document> a = d.getList("items", Document.class);
for(Document a1 : a) {
res.getItems().add(deserializeOrderLine(a1));
}
return res;
}
private static CountDownLatch cdl;
private static void dump(MongoCollection<Document> col, Document criteria) {
System.out.println("iteration staring for " + criteria.toJson());
col.find(criteria).forEach(new Block<Document>() {
@Override
public void apply(Document d) {
Order o = deserializeOrder(d);
synchronized(System.out) {
//System.out.println(d.toJson());
System.out.println("document for " + criteria.toJson());
System.out.printf("Id : %d\n", o.getOrderId());
System.out.printf("Customer : %s\n", o.getCustomer());
System.out.printf("Price : %s\n", o.getPrice());
for(OrderLine ol : o.getItems()) {
System.out.printf(" %2d %-30s %10s %4d %10s\n", ol.getItemNo(), ol.getItemName(), ol.getItemPrice(), ol.getQuantity(), ol.getPrice());
}
}
}
},
new SingleResultCallback<Void>() {
@Override
public void onResult(Void result, Throwable t) {
System.out.println("iteration done for " + criteria.toJson());
cdl.countDown();
}
});
}
public static void main(String[] args) throws InterruptedException {
// open
MongoClient client = MongoClients.create("mongodb://localhost:27017");
MongoDatabase db = client.getDatabase("TestDB");
MongoCollection<Document> col = db.getCollection("java_order");
// setup
cdl = new CountDownLatch(2);
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
col.insertOne(serialize(o1), new SingleResultCallback<Void>() {
@Override
public void onResult(Void result, Throwable t) {
System.out.println("insert 1 done");
cdl.countDown();
}
});
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
col.insertOne(serialize(o2), new SingleResultCallback<Void>() {
@Override
public void onResult(Void result, Throwable t) {
System.out.println("insert 2 done");
cdl.countDown();;
}
});
cdl.await(); // wait until 2 inserts complete
// query
cdl = new CountDownLatch(7);
Document d;
d = new Document(); // all
dump(col, d);
d = new Document("orderId", 2); // where id=2
dump(col, d);
d = new Document("customer", "A A"); // where customer='A A'
dump(col, d);
d = new Document("customer", new Document("$regex", "A.*"));// where customer like 'A%'
dump(col, d);
d = new Document("items.itemName", "i5 CPU"); // where items.name='i5 CPU'
dump(col, d);
d = new Document("items.itemName", new Document("$regex", ".*Java.*")); // where items.name like '%Java%'
dump(col, d);
d = new Document("orderId", new Document("$in", Arrays.asList(1, 2))); // where id in (1,2)
dump(col, d);
cdl.await(); // wait until 7 queries complete
// close
client.close();
}
}
This example does not make much sense from performance perspective as it does not do anything while waiting for database operations to complete, but it should show the async API.
There is an upcoming Java EE standard for NoSQL databases: Jakarta NoSQL. The reference implementation is Eclipse JNoSQL.
The reference implementation comes with driver for MongoDB.
The example is tested with B4 release.
package nosql.mongodb.jnosql;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public String getItemName() {
return itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public int getQuantity() {
return quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
}
package nosql.mongodb.jnosql;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import jakarta.nosql.mapping.Column;
import jakarta.nosql.mapping.Entity;
import jakarta.nosql.mapping.Id;
@Entity
public class Order {
@Id
private int orderId;
@Column
private String customer;
@Column
private List<OrderLine> items;
public Order() {
this(0, "");
}
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public String getCustomer() {
return customer;
}
public List<OrderLine> getItems() {
return items;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public void setCustomer(String customer) {
this.customer = customer;
}
public void setItems(List<OrderLine> items) {
this.items = items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
}
package nosql.mongodb.jnosql;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import org.eclipse.jnosql.communication.mongodb.document.MongoDBDocumentConfiguration;
import jakarta.nosql.Settings;
import jakarta.nosql.TypeReference;
import jakarta.nosql.document.DocumentCollectionManager;
import jakarta.nosql.document.DocumentCollectionManagerFactory;
import jakarta.nosql.document.DocumentConfiguration;
import jakarta.nosql.document.DocumentEntity;
import jakarta.nosql.document.DocumentQuery;
public class Test2 {
private static void dump(DocumentCollectionManager dcm, DocumentQuery dq) {
System.out.println("dumping " + dq);
for(DocumentEntity de : dcm.select(dq).collect(Collectors.toList())) {
Order o = new Order();
Util.autoMapTo(de, o, new TypeReference<List<OrderLine>>() { });
System.out.printf("Id : %d\n", o.getOrderId());
System.out.printf("Customer : %s\n", o.getCustomer());
System.out.printf("Price : %s\n", o.getPrice());
for(OrderLine ol : o.getItems()) {
System.out.printf(" %2d %-30s %10s %4d %10s\n", ol.getItemNo(), ol.getItemName(), ol.getItemPrice(), ol.getQuantity(), ol.getPrice());
}
}
}
public static void main(String[] args) {
// open
DocumentConfiguration cfg = new MongoDBDocumentConfiguration();
Map<String, Object> props = new HashMap<>();
props.put("mongodb.host", "localhost:27017");
DocumentCollectionManagerFactory dcmf = cfg.get(Settings.of(props));
DocumentCollectionManager dcm = dcmf.get("java_order");
// setup
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
DocumentEntity de1 = DocumentEntity.of("java_order");
Util.autoMapFrom(o1, de1);
dcm.insert(de1);
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
DocumentEntity de2 = DocumentEntity.of("java_order");
Util.autoMapFrom(o2, de2);
dcm.insert(de2);
// query
DocumentQuery dq;
dq = DocumentQuery.select().from("java_order").build(); // all
dump(dcm, dq);
dq = DocumentQuery.select().from("java_order").where("orderId").eq(2).build(); // where id=2
dump(dcm, dq);
dq = DocumentQuery.select().from("java_order").where("customer").eq("A A").build(); // where customer='A A'
dump(dcm, dq);
dq = DocumentQuery.select().from("java_order").where("customer").like("A").build(); // where customer like 'A%'
dump(dcm, dq);
// where items.name='i5 CPU'
// NOT SUPPORTED
// where items.name like '%Java%'
// NOT SUPPORTED
List<Integer> lst = new ArrayList<>();
lst.add(1);
lst.add(2);
dq = DocumentQuery.select().from("java_order").where("orderId").in(lst).build();
dump(dcm, dq);
// close
dcm.close();
dcmf.close();
}
}
package nosql.mongodb.jnosql;
import com.google.gson.Gson;
import jakarta.nosql.ValueReader;
public class OrderLineValueReader implements ValueReader {
@Override
public <T> T read(Class<T> clz, Object value) {
Gson json = new Gson();
return json.fromJson(value.toString(), clz);
}
@Override
public boolean test(Class<?> clz) {
return clz.equals(OrderLine.class);
}
}
package nosql.mongodb.jnosql;
import com.google.gson.Gson;
import jakarta.nosql.ValueWriter;
public class OrderLineValueWriter implements ValueWriter<OrderLine, String> {
@Override
public String write(OrderLine o) {
Gson json = new Gson();
return json.toJson(o);
}
@Override
public boolean test(Class> clz) {
return clz.equals(OrderLine.class);
}
}
package nosql.mongodb.jnosql;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.List;
import jakarta.nosql.TypeReference;
import jakarta.nosql.document.DocumentEntity;
//++++
//
// I think there should be an easy way to convert between DocumentEntity and a custom class but I cannot find it,
// so I came up with this hack.
//
//++++
public class Util {
public static <T> void autoMapFrom(T o, DocumentEntity de) {
try {
for(PropertyDescriptor pd : Introspector.getBeanInfo(o.getClass()).getPropertyDescriptors()) {
if(pd.getName().equals("class")) {
// ignore Object getClass
} else {
de.add(pd.getName(), pd.getReadMethod().invoke(o));
}
}
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public static <T, U> void autoMapTo(DocumentEntity de, T o, TypeReference<List<U>> tr) {
try {
for(PropertyDescriptor pd : Introspector.getBeanInfo(o.getClass()).getPropertyDescriptors()) {
if(pd.getName().equals("class")) {
// ignore Object getClass
} else if(pd.getPropertyType().isPrimitive()
|| pd.getPropertyType().equals(BigInteger.class)
|| pd.getPropertyType().equals(BigDecimal.class)
|| pd.getPropertyType().equals(String.class)
|| !pd.getPropertyType().getName().startsWith("java")) {
if(pd.getWriteMethod() != null) {
pd.getWriteMethod().invoke(o, de.find(pd.getName()).get().get(pd.getPropertyType()));
}
} else if(pd.getPropertyType().equals(List.class)) {
List from = de.find(pd.getName()).get().get(tr);
List to = (List)pd.getReadMethod().invoke(o);
for(Object so : from) {
to.add(so);
}
} else {
throw new RuntimeException("Unsupported type " + pd.getPropertyType() + " for property " + pd.getName());
}
}
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
META-INF/services/jakarta.nosql.ValueReader:
nosql.mongodb.jnosql.OrderLineValueReader
META-INF/services/jakarta.nosql.ValueWriter:
nosql.mongodb.jnosql.OrderLineValueWriter
using System;
using System.Collections.Generic;
using System.Linq;
using MongoDB.Driver;
using MongoDB.Bson;
namespace NoSQL.MongoDB
{
public class OrderLine
{
public int ItemNo { get; set; }
public string ItemName { get; set; }
public decimal ItemPrice { get; set; }
public int Quantity { get; set; }
public decimal Price
{
get
{
return ItemPrice * Quantity;
}
}
}
public class Order
{
public int OrderId { get; set; }
public string Customer { get; set; }
public IList<OrderLine> Items { get; set; }
public decimal Price
{
get
{
return Items.Sum(ol => ol.Price);
}
}
}
public class Program
{
private static BsonDocument Serialize(OrderLine ol)
{
BsonDocument d = new BsonDocument();
d.Add("itemNo", ol.ItemNo);
d.Add("itemName", ol.ItemName);
d.Add("itemPrice", ol.ItemPrice);
d.Add("quantity", ol.Quantity);
return d;
}
private static BsonDocument Serialize(Order o)
{
BsonDocument d = new BsonDocument();
d.Add("orderId", o.OrderId);
d.Add("customer", o.Customer);
BsonArray a = new BsonArray();
foreach (OrderLine ol in o.Items)
{
a.Add(Serialize(ol));
}
d.Add("items", a);
return d;
}
private static OrderLine DeserializeOrderLine(BsonDocument d)
{
return new OrderLine { ItemNo = d.GetValue("itemNo").AsInt32,
ItemName = d.GetValue("itemName").AsString,
ItemPrice = d.GetValue("itemPrice").AsDecimal,
Quantity = d.GetValue("quantity").AsInt32 };
}
private static Order DeserializeOrder(BsonDocument d)
{
Order res = new Order { OrderId = d.GetValue("orderId").AsInt32, Customer = d.GetValue("customer").AsString, Items = new List<OrderLine>() };
BsonArray a = d.GetValue("items").AsBsonArray;
foreach (BsonDocument a1 in a)
{
res.Items.Add(DeserializeOrderLine(a1));
}
return res;
}
private static void Dump(IMongoCollection<BsonDocument> col, BsonDocument criteria)
{
Console.WriteLine("dumping " + criteria.ToJson());
foreach (BsonDocument d in col.Find(criteria).ToEnumerable())
{
//Console.WriteLine(d.ToJson());
Order o = DeserializeOrder(d);
Console.WriteLine("Id : {0}", o.OrderId);
Console.WriteLine("Customer : {0}", o.Customer);
Console.WriteLine("Price : {0}", o.Price);
foreach (OrderLine ol in o.Items)
{
Console.WriteLine(" {0,2} {1,-30} {2,10:F2} {3,4} {4,10:F2}", ol.ItemNo, ol.ItemName, ol.ItemPrice, ol.Quantity, ol.Price);
}
}
}
public static void Main(string[] args)
{
// open
MongoClient client = new MongoClient("mongodb://localhost:27017");
IMongoDatabase db = client.GetDatabase("TestDB");
IMongoCollection<BsonDocument> col = db.GetCollection<BsonDocument>("dn_order");
// setup
Order o1 = new Order { OrderId = 1, Customer = "A A", Items = new List<OrderLine>() };
o1.Items.Add(new OrderLine { ItemNo = 1, ItemName = "A good C# book", ItemPrice = 19.95m, Quantity = 1 });
col.InsertOne(Serialize(o1));
Order o2 = new Order { OrderId = 2, Customer = "B B", Items = new List<OrderLine>() };
o2.Items.Add(new OrderLine { ItemNo = 1, ItemName = "ASUS MB", ItemPrice = 249.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 2, ItemName = "i5 CPU", ItemPrice = 299.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 3, ItemName = "4 GB kit", ItemPrice = 29.00m, Quantity = 4 });
col.InsertOne(Serialize(o2));
// query
BsonDocument d;
d = new BsonDocument(); // all
Dump(col, d);
d = new BsonDocument("orderId", 2); // where id=2
Dump(col, d);
d = new BsonDocument("customer", "A A"); // where customer='A A'
Dump(col, d);
d = new BsonDocument("customer", new BsonDocument("$regex", "A.*"));// where customer like 'A%'
Dump(col, d);
d = new BsonDocument("items.itemName", "i5 CPU"); // where items.name='i5 CPU'
Dump(col, d);
d = new BsonDocument("items.itemName", new BsonDocument("$regex", ".*C#.*")); // where items.name like '%C#%'
Dump(col, d);
d = new BsonDocument("orderId", new BsonDocument("$in", new BsonArray(new int[] { 1, 2 }))); // where id in (1,2)
Dump(col, d);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using MongoDB.Driver;
using MongoDB.Bson;
namespace NoSQL.MongoDB
{
public class OrderLine
{
public int ItemNo { get; set; }
public string ItemName { get; set; }
public decimal ItemPrice { get; set; }
public int Quantity { get; set; }
public decimal Price
{
get
{
return ItemPrice * Quantity;
}
}
}
public class Order
{
public int OrderId { get; set; }
public string Customer { get; set; }
public IList<OrderLIne> Items { get; set; }
public decimal Price
{
get
{
return Items.Sum(ol => ol.Price);
}
}
}
public class Program
{
private static BsonDocument Serialize(OrderLine ol)
{
BsonDocument d = new BsonDocument();
d.Add("itemNo", ol.ItemNo);
d.Add("itemName", ol.ItemName);
d.Add("itemPrice", ol.ItemPrice);
d.Add("quantity", ol.Quantity);
return d;
}
private static BsonDocument Serialize(Order o)
{
BsonDocument d = new BsonDocument();
d.Add("orderId", o.OrderId);
d.Add("customer", o.Customer);
BsonArray a = new BsonArray();
foreach (OrderLine ol in o.Items)
{
a.Add(Serialize(ol));
}
d.Add("items", a);
return d;
}
private static OrderLine DeserializeOrderLine(BsonDocument d)
{
return new OrderLine
{
ItemNo = d.GetValue("itemNo").AsInt32,
ItemName = d.GetValue("itemName").AsString,
ItemPrice = d.GetValue("itemPrice").AsDecimal,
Quantity = d.GetValue("quantity").AsInt32
};
}
private static Order DeserializeOrder(BsonDocument d)
{
Order res = new Order { OrderId = d.GetValue("orderId").AsInt32, Customer = d.GetValue("customer").AsString, Items = new List<OrderLIne>() };
BsonArray a = d.GetValue("items").AsBsonArray;
foreach (BsonDocument a1 in a)
{
res.Items.Add(DeserializeOrderLine(a1));
}
return res;
}
private static void Dump(IMongoCollection<BsonDocument> col, FilterDefinition<BsonDocument> filter)
{
Console.WriteLine("dumping " + filter);
foreach (BsonDocument d in col.Find(filter).ToEnumerable())
{
//Console.WriteLine(d.ToJson());
Order o = DeserializeOrder(d);
Console.WriteLine("Id : {0}", o.OrderId);
Console.WriteLine("Customer : {0}", o.Customer);
Console.WriteLine("Price : {0}", o.Price);
foreach (OrderLine ol in o.Items)
{
Console.WriteLine(" {0,2} {1,-30} {2,10:F2} {3,4} {4,10:F2}", ol.ItemNo, ol.ItemName, ol.ItemPrice, ol.Quantity, ol.Price);
}
}
}
public static void Main(string[] args)
{
// open
MongoClient client = new MongoClient("mongodb://localhost:27017");
IMongoDatabase db = client.GetDatabase("TestDB");
IMongoCollection<BsonDocument> col = db.GetCollection<BsonDocument>("dn_order");
// setup
Order o1 = new Order { OrderId = 1, Customer = "A A", Items = new List<OrderLIne>() };
o1.Items.Add(new OrderLine { ItemNo = 1, ItemName = "A good C# book", ItemPrice = 19.95m, Quantity = 1 });
col.InsertOne(Serialize(o1));
Order o2 = new Order { OrderId = 2, Customer = "B B", Items = new List<OrderLIne>() };
o2.Items.Add(new OrderLine { ItemNo = 1, ItemName = "ASUS MB", ItemPrice = 249.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 2, ItemName = "i5 CPU", ItemPrice = 299.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 3, ItemName = "4 GB kit", ItemPrice = 29.00m, Quantity = 4 });
col.InsertOne(Serialize(o2));
// query
FilterDefinitionBuilder<BsonDocument> fdb = new FilterDefinitionBuilder<BsonDocument>();
FilterDefinition<BsonDocument> filter;
filter = fdb.Gte("orderId", 0); // all (simulated with orderId >= 0)
Dump(col, filter);
filter = fdb.Eq("orderId", 2); // where id=2
Dump(col, filter);
filter = fdb.Eq("customer", "A A"); // where customer='A A'
Dump(col, filter);
filter = fdb.Regex("customer", "A.*");// where customer like 'A%'
Dump(col, filter);
filter = fdb.Eq("items.itemName", "i5 CPU"); // where items.name='i5 CPU'
Dump(col, filter);
filter = fdb.Regex("items.itemName", ".*C#.*"); // where items.name like '%C#%'
Dump(col, filter);
filter = fdb.In("orderId", new int[] { 1, 2 }); // where id in (1,2)
Dump(col, filter);
}
}
}
.NET client library comes with an async API besides the normal sync API.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using MongoDB.Driver;
using MongoDB.Bson;
namespace NoSQL.MongoDB
{
public class OrderLine
{
public int ItemNo { get; set; }
public string ItemName { get; set; }
public decimal ItemPrice { get; set; }
public int Quantity { get; set; }
public decimal Price
{
get
{
return ItemPrice * Quantity;
}
}
}
public class Order
{
public int OrderId { get; set; }
public string Customer { get; set; }
public IList<OrderLine> Items { get; set; }
public decimal Price
{
get
{
return Items.Sum(ol => ol.Price);
}
}
}
public class Program
{
private static BsonDocument Serialize(OrderLine ol)
{
BsonDocument d = new BsonDocument();
d.Add("itemNo", ol.ItemNo);
d.Add("itemName", ol.ItemName);
d.Add("itemPrice", ol.ItemPrice);
d.Add("quantity", ol.Quantity);
return d;
}
private static BsonDocument Serialize(Order o)
{
BsonDocument d = new BsonDocument();
d.Add("orderId", o.OrderId);
d.Add("customer", o.Customer);
BsonArray a = new BsonArray();
foreach (OrderLine ol in o.Items)
{
a.Add(Serialize(ol));
}
d.Add("items", a);
return d;
}
private static OrderLine DeserializeOrderLine(BsonDocument d)
{
return new OrderLine
{
ItemNo = d.GetValue("itemNo").AsInt32,
ItemName = d.GetValue("itemName").AsString,
ItemPrice = d.GetValue("itemPrice").AsDecimal,
Quantity = d.GetValue("quantity").AsInt32
};
}
private static Order DeserializeOrder(BsonDocument d)
{
Order res = new Order { OrderId = d.GetValue("orderId").AsInt32, Customer = d.GetValue("customer").AsString, Items = new List<OrderLine>() };
BsonArray a = d.GetValue("items").AsBsonArray;
foreach (BsonDocument a1 in a)
{
res.Items.Add(DeserializeOrderLine(a1));
}
return res;
}
private static CountdownEvent cde;
private static void Dump(IMongoCollection<BsonDocument> col, BsonDocument criteria)
{
Task<IAsyncCursor<BsonDocument>> t = col.FindAsync(criteria);
t.ContinueWith((Task < IAsyncCursor < BsonDocument >> t) => {
IAsyncCursor<BsonDocument> cursor = t.Result;
while(cursor.MoveNext())
{
foreach(BsonDocument d in cursor.Current)
{
lock(Console.Out)
{
Console.WriteLine("document for " + criteria.ToJson());
//Console.WriteLine(d.ToJson());
Order o = DeserializeOrder(d);
Console.WriteLine("Id : {0}", o.OrderId);
Console.WriteLine("Customer : {0}", o.Customer);
Console.WriteLine("Price : {0}", o.Price);
foreach (OrderLine ol in o.Items)
{
Console.WriteLine(" {0,2} {1,-30} {2,10:F2} {3,4} {4,10:F2}", ol.ItemNo, ol.ItemName, ol.ItemPrice, ol.Quantity, ol.Price);
}
}
}
}
cde.Signal();
});
}
public static void Main(string[] args)
{
// open
MongoClient client = new MongoClient("mongodb://localhost:27017");
IMongoDatabase db = client.GetDatabase("TestDB");
IMongoCollection<BsonDocument> col = db.GetCollection<BsonDocument>("dn_order");
// setup
cde = new CountdownEvent(2);
Order o1 = new Order { OrderId = 1, Customer = "A A", Items = new List<OrderLine>() };
o1.Items.Add(new OrderLine { ItemNo = 1, ItemName = "A good C# book", ItemPrice = 19.95m, Quantity = 1 });
Task t1 = col.InsertOneAsync(Serialize(o1));
t1.ContinueWith(t =>
{
Console.WriteLine("insert 1 done");
cde.Signal();
});
Order o2 = new Order { OrderId = 2, Customer = "B B", Items = new List<OrderLine>() };
o2.Items.Add(new OrderLine { ItemNo = 1, ItemName = "ASUS MB", ItemPrice = 249.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 2, ItemName = "i5 CPU", ItemPrice = 299.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 3, ItemName = "4 GB kit", ItemPrice = 29.00m, Quantity = 4 });
Task t2 = col.InsertOneAsync(Serialize(o2));
t2.ContinueWith(t =>
{
Console.WriteLine("insert 2 done");
cde.Signal();
});
cde.Wait();
// query
cde = new CountdownEvent(7);
BsonDocument d;
d = new BsonDocument(); // all
Dump(col, d);
d = new BsonDocument("orderId", 2); // where id=2
Dump(col, d);
d = new BsonDocument("customer", "A A"); // where customer='A A'
Dump(col, d);
d = new BsonDocument("customer", new BsonDocument("$regex", "A.*"));// where customer like 'A%'
Dump(col, d);
d = new BsonDocument("items.itemName", "i5 CPU"); // where items.name='i5 CPU'
Dump(col, d);
d = new BsonDocument("items.itemName", new BsonDocument("$regex", ".*C#.*")); // where items.name like '%C#%'
Dump(col, d);
d = new BsonDocument("orderId", new BsonDocument("$in", new BsonArray(new int[] { 1, 2 }))); // where id in (1,2)
Dump(col, d);
cde.Wait();
}
}
}
This example does not make much sense from performance perspective as it does not do anything while waiting for database operations to complete, but it should show the async API.
from decimal import *
from pymongo import *
class OrderLine(object):
def __init__(self, _itemNo, _itemName, _itemPrice, _quantity = 1):
self.itemNo = _itemNo
self.itemName = _itemName
self.itemPrice = _itemPrice
self.quantity = _quantity
def getPrice(self):
return self.quantity * self.itemPrice
class Order(object):
def __init__(self, _orderId, _customer):
self.orderId = _orderId
self.customer = _customer
self.items = []
def getPrice(self):
return sum(ol.getPrice() for ol in self.items)
def serializeOrderLine(ol):
return { 'itemNo': ol.itemNo, 'itemName': ol.itemName, 'itemPrice': str(ol.itemPrice), 'quantity': ol.quantity }
def serializeOrder(o):
a = []
for ol in o.items:
a.append(serializeOrderLine(ol))
return { 'orderId': o.orderId, 'customer': o.customer, 'items': a }
def deserializeOrderLine(d):
return OrderLine(d['itemNo'], d['itemName'], Decimal(d['itemPrice']), d['quantity'])
def deserializeOrder(d):
res = Order(d['orderId'], d['customer'])
for d1 in d['items']:
res.items.append(deserializeOrderLine(d1))
return res
def dump(col, doc):
print('dumping ' + str(doc))
for d in col.find(doc):
#print(d)
o = deserializeOrder(d)
print('Id : %d' % (o.orderId))
print('Customer : %s' % (o.customer))
print('Price : %s' % (o.getPrice()))
for ol in o.items:
print(' %2d %-30s %10s %4d %10s' % (ol.itemNo, ol.itemName, ol.itemPrice, ol.quantity, ol.getPrice()))
# open
client = MongoClient('localhost', 27017)
db = client.TestDB
col = db.py_order
# setup
o1 = Order(1, 'A A')
o1.items.append(OrderLine(1, 'A good Python book', Decimal('19.95'), 1))
col.insert_one(serializeOrder(o1))
o2 = Order(2, 'B B');
o2.items.append(OrderLine(1, 'ASUS MB', Decimal('249.00'), 1));
o2.items.append(OrderLine(2, 'i5 CPU', Decimal('299.00'), 1));
o2.items.append(OrderLine(3, '4 GB kit', Decimal('29.00'), 4));
col.insert_one(serializeOrder(o2))
# query
d = { } # all
dump(col, d)
d = { 'orderId': 2 } # where id=2
dump(col, d)
d = { 'customer': 'A A' } # where customer='A A'
dump(col, d)
d = { 'customer': { '$regex': 'A.*' } } # where customer like 'A%'
dump(col, d)
d = { 'items.itemName': 'i5 CPU' } # where items.name='i5 CPU'
dump(col, d)
d = { 'items.itemName': { '$regex': '.*Python.*' } } # where items.name like '%Java%'
dump(col, d)
d = { 'orderId': { '$in': [ 1, 2 ] } } # where id in (1,2)
dump(col, d)
<?php
spl_autoload_register(function ($clznam) {
include $clznam . '.php';
});
require 'MongoDB/functions.php';
use MongoDB\Client;
class OrderLine {
private $itemNo;
private $itemName;
private $itemPrice;
private $quantity;
public function __construct($itemNo, $itemName, $itemPrice, $quantity = 1) {
$this->itemNo = $itemNo;
$this->itemName = $itemName;
$this->itemPrice = $itemPrice;
$this->quantity = $quantity;
}
public function getItemNo()
{
return $this->itemNo;
}
public function getItemName()
{
return $this->itemName;
}
public function getItemPrice()
{
return $this->itemPrice;
}
public function getQuantity()
{
return $this->quantity;
}
public function getPrice() {
return bcadd((string)$this->quantity, $this->itemPrice);
}
}
class Order {
private $orderId;
private $customer;
private $items;
public function __construct($orderId, $customer) {
$this->orderId = $orderId;
$this->customer = $customer;
$this->items = array();
}
public function getOrderId()
{
return $this->orderId;
}
public function getCustomer()
{
return $this->customer;
}
public function &getItems()
{
return $this->items;
}
public function getPrice() {
$res = '0.00';
foreach($this->items as $ol) {
$res = bcadd($res, $ol->getPrice());
}
return $res;
}
}
function serializeOrderLine($ol) {
return array('itemNo' => $ol->getItemNo(), 'itemName' => $ol->getItemName(), 'itemPrice' => $ol->getItemPrice(), 'quantity' => $ol->getQuantity());
}
function serializeOrder($o) {
$a = array();
foreach($o->getItems() as $ol) {
$a[] = serializeOrderLine($ol);
}
return array('orderId' => $o->getOrderId(), 'customer' => $o->getCustomer(), 'items' => $a);
}
function deserializeOrderLine($d) {
return new OrderLine($d['itemNo'], $d['itemName'], $d['itemPrice'], $d['quantity']);
}
function deserializeOrder($d) {
$res = new Order($d['orderId'], $d['customer']);
foreach($d['items'] as $d1) {
$res->getItems()[] = deserializeOrderLine($d1);
}
return $res;
}
function dump($col, $doc) {
echo 'dumping ' . json_encode($doc) . "\r\n";
$da = $col->find($doc)->toArray();
foreach($da as $d) {
//echo json_encode($d);
$o = deserializeOrder($d);
echo sprintf("Id : %d\r\n", $o->getOrderId());
echo sprintf("Customer : %s\r\n", $o->getCustomer());
echo sprintf("Price : %s\r\n", $o->getPrice());
foreach($o->getItems() as $ol) {
echo sprintf(" %2d %-30s %10s %4d %10s\r\n", $ol->getItemNo(), $ol->getItemName(), $ol->getItemPrice(), $ol->getQuantity(), $ol->getPrice());
}
}
}
// open
$client = new Client('mongodb://localhost:27017');
$db = $client->TestDB;
$col = $db->php_order;
// setup
$o1 = new Order(1, 'A A');
$o1->getItems()[] = new OrderLine(1, 'A good PHP book', '19.95', 1);
$col->insertOne(serializeOrder($o1));
$o2 = new Order(2, 'B B');
$o2->getItems()[] = new OrderLine(1, 'ASUS MB', '249.00', 1);
$o2->getItems()[] = new OrderLine(2, 'i5 CPU', '299.00', 1);
$o2->getItems()[] = new OrderLine(3, '4 GB kit', '29.00', 4);
$col->insertOne(serializeOrder($o2));
// query
$d = array(); // all
dump($col, $d);
$d = array('orderId' => 2); // where id=2
dump($col, $d);
$d = array('customer' => 'A A'); // where customer='A A'
dump($col, $d);
$d = array('customer' => array('$regex' => 'A.*'));// where customer like 'A%'
dump($col, $d);
$d = array('items.itemName' => 'i5 CPU'); // where items.name='i5 CPU'
dump($col, $d);
$d = array('items.itemName' => array('$regex' => '.*PHP.*')); // where items.name like '%Java%'
dump($col, $d);
$d = array('orderId' => array('$in' => array(1, 2))); // where id in (1,2)
dump($col, $d);
?>
Let us see an example where JSON is considered the native format and where no schema is enforced by the application and it is queries freely.
package nosql.mongodb;
import org.bson.Document;
import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
public class Test3 {
private static void dump(MongoCollection<Document> col, Document criteria) {
System.out.println("dumping " + criteria.toJson());
for(Document d : col.find(criteria)) {
System.out.println(d.toJson());
/*
System.out.printf("Id : %d\n", d.getInteger("no"));
System.out.printf("Customer : %s\n", d.getString("customer"));
for(Document d2 : d.getList("items", Document.class)) {
System.out.printf(" %2d %-30s %10s %4d %s\n", d2.getInteger("no"),
d2.getString("name"),
d2.getString("price"),
d2.getInteger("quantity", 1),
d2.getString("note") != null ? d2.getString("note") : "");
}
*/
}
}
public static void main(String[] args) {
// open
MongoClient client = new MongoClient("localhost", 27017);
MongoDatabase db = client.getDatabase("TestDB");
MongoCollection<Document> col = db.getCollection("java_json");
// setup
String s1 = "{ \"id\": 1, \"customer\": \"A A\", \"items\": [ { \"no\": 1, \"name\": \"A good Java book\", \"price\": \"19.95\" } ] }";
System.out.println(s1);
Document d1 = Document.parse(s1);
col.insertOne(d1);
String s2 = "{ \"id\": 2, \"customer\": \"B B\", \"items\": [ { \"no\": 1, \"name\": \"ASUS MB\", \"price\": \"249.00\" }, " +
"{ \"no\": 1, \"name\": \"i5 CPU\", \"price\": \"299.00\", \"note\": \"handle with care\" }, " +
"{ \"no\": 1, \"name\": \"4 GB kit\", \"price\": \"249.00\", \"quantity\": 4} ] }";
System.out.println(s2);
Document d2 = Document.parse(s2);
col.insertOne(d2);
// query
dump(col, Document.parse("{ }")); // all
dump(col, Document.parse("{ \"id\": 2 }")); // where id=2
dump(col, Document.parse("{ \"customer\": \"A A\" }")); // where customer='A A'
dump(col, Document.parse("{ \"customer\": { \"$regex\": \"A.*\"} }")); // where customer like 'A%'
dump(col, Document.parse("{ \"items.name\": \"i5 CPU\" }")); // where items.name='i5 CPU'
dump(col, Document.parse("{ \"items.name\": { \"$regex\": \".*Java.*\" } }")); // where items.name like '%Java%'
dump(col, Document.parse("{ \"id\": { \"$in\": [ 1, 2 ] } }")); // where id in (1,2)
dump(col, Document.parse("{ \"items.note\": { \"$exists\": true } }")); // where exists items.note
// close
client.close();
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using MongoDB.Driver;
using MongoDB.Bson;
namespace NoSQL.MongoDB
{
public class Program
{
private static void Dump(IMongoCollection<BsonDocument> col, BsonDocument criteria)
{
Console.WriteLine("dumping " + criteria.ToJson());
foreach (BsonDocument d in col.Find(criteria).ToEnumerable())
{
Console.WriteLine(d.ToJson());
/*
Console.WriteLine("Id : {0}", d.GetValue("id").AsInt32);
Console.WriteLine("Customer : {0}", d.GetValue("customer").AsString);
foreach (BsonDocument d2 in d.GetValue("items").AsBsonArray)
{
Console.WriteLine(" {0,2} {1,-30} {2,10:F2} {3,4} {4}", d2.GetValue("no").AsInt32,
d2.GetValue("name").AsString,
d2.GetValue("price").AsString,
d2.GetValue("quantity", 1).AsInt32,
d2.GetValue("note", "").AsString);
}
*/
}
}
public static void Main(string[] args)
{
// open
MongoClient client = new MongoClient("mongodb://localhost:27017");
IMongoDatabase db = client.GetDatabase("TestDB");
IMongoCollection<BsonDocument> col = db.GetCollection<BsonDocument>("dn_json");
// setup
string s1 = "{ \"id\": 1, \"customer\": \"A A\", \"items\": [ { \"no\": 1, \"name\": \"A good C# book\", \"price\": \"19.95\" } ] }";
Console.WriteLine(s1);
BsonDocument d1 = BsonDocument.Parse(s1);
col.InsertOne(d1);
String s2 = "{ \"id\": 2, \"customer\": \"B B\", \"items\": [ { \"no\": 1, \"name\": \"ASUS MB\", \"price\": \"249.00\" }, " +
"{ \"no\": 1, \"name\": \"i5 CPU\", \"price\": \"299.00\", \"note\": \"handle with care\" }, " +
"{ \"no\": 1, \"name\": \"4 GB kit\", \"price\": \"249.00\", \"quantity\": 4} ] }";
Console.WriteLine(s2);
BsonDocument d2 = BsonDocument.Parse(s2);
col.InsertOne(d2);
// query
Dump(col, BsonDocument.Parse("{ }")); // all
Dump(col, BsonDocument.Parse("{ \"id\": 2 }")); // where id=2
Dump(col, BsonDocument.Parse("{ \"customer\": \"A A\" }")); // where customer='A A'
Dump(col, BsonDocument.Parse("{ \"customer\": { \"$regex\": \"A.*\"} }")); // where customer like 'A%'
Dump(col, BsonDocument.Parse("{ \"items.name\": \"i5 CPU\" }")); // where items.name='i5 CPU'
Dump(col, BsonDocument.Parse("{ \"items.name\": { \"$regex\": \".*C#.*\" } }")); // where items.name like '%C#%'
Dump(col, BsonDocument.Parse("{ \"id\": { \"$in\": [ 1, 2 ] } }")); // where id in (1,2)
Dump(col, BsonDocument.Parse("{ \"items.note\": { \"$exists\": true } }")); // where exists items.note
}
}
}
import json
from pymongo import *
def dump(col, doc):
print('dumping ' + str(doc))
for d in col.find(doc):
print(d)
#print('Id : %d' % (d['id']))
#print('Customer : %s' % (d['customer']))
#for d1 in d['items']:
# print(' %2d %-30s %10s %4d %s' % (d1['no'], d1['name'], d1['price'], d1.get('quantity', 1), d1.get('note', '')))
# open
client = MongoClient('localhost', 27017)
db = client.TestDB
col = db.py_json
# setup
s1 = '{ "id": 1, "customer": "A A", "items": [ { "no": 1, "name": "A good Python book", "price": "19.95" } ] }'
print(s1)
#col.insert_one(json.loads(s1))
s2 = '{ "id": 2, "customer": "B B", "items": [ { "no": 1, "name": "ASUS MB", "price": "249.00" }, { "no": 1, "name": "i5 CPU", "price": "299.00", "note": "handle with care" }, { "no": 1, "name": "4 GB kit", "price": "249.00", "quantity": 4} ] }'
print(s2)
#col.insert_one(json.loads(s2))
# query
d = { } # all
dump(col, d)
d = { 'id': 2 } # where id=2
dump(col, d)
d = { 'customer': 'A A' } # where customer='A A'
dump(col, d)
d = { 'customer': { '$regex': 'A.*' } } # where customer like 'A%'
dump(col, d)
d = { 'items.name': 'i5 CPU' } # where items.name='i5 CPU'
dump(col, d)
d = { 'items.name': { '$regex': '.*Python.*' } } # where items.name like '%Java%'
dump(col, d)
d = { 'id': { '$in': [ 1, 2 ] } } # where id in (1,2)
dump(col, d)
<?php
spl_autoload_register(function ($clznam) {
include $clznam . '.php';
});
require 'MongoDB/functions.php';
use MongoDB\Client;
function dump($col, $doc) {
echo 'dumping ' . json_encode($doc) . "\r\n";
$da = $col->find($doc)->toArray();
foreach($da as $d) {
echo json_encode($d);
/*
echo sprintf("Id : %d\r\n", $d['id']);
echo sprintf("Customer : %s\r\n", $d['customer']);
foreach($d['items'] as $d1) {
echo sprintf(" %2d %-30s %10s %4d %10s\r\n", $d1['no'], $d1['name'], $d1['price'], isset($d1['quantity']) ? $d1['quantity'] : 1, isset($d1['note']) ? $d1['note'] : '');
}
*/
}
}
// open
$client = new Client('mongodb://localhost:27017');
$db = $client->TestDB;
$col = $db->php_json;
// setup
$s1 = '{ "id": 1, "customer": "A A", "items": [ { "no": 1, "name": "A good PHP book", "price": "19.95" } ] }';
echo "$s1\r\n";
//$col->insertOne(json_decode($s1));
$s2 = '{ "id": 2, "customer": "B B", "items": [ { "no": 1, "name": "ASUS MB", "price": "249.00" }, { "no": 1, "name": "i5 CPU", "price": "299.00", "note": "handle with care" }, { "no": 1, "name": "4 GB kit", "price": "249.00", "quantity": 4} ] }';
echo "$s2\r\n";
//$col->insertOne(json_decode($s2));
// query
$d = array(); // all
dump($col, $d);
$d = array('id' => 2); // where id=2
dump($col, $d);
$d = array('customer' => 'A A'); // where customer='A A'
dump($col, $d);
$d = array('customer' => array('$regex' => 'A.*'));// where customer like 'A%'
dump($col, $d);
$d = array('items.name' => 'i5 CPU'); // where items.name='i5 CPU'
dump($col, $d);
$d = array('items.name' => array('$regex' => '.*PHP.*')); // where items.name like '%Java%'
dump($col, $d);
$d = array('id' => array('$in' => array(1, 2))); // where id in (1,2)
dump($col, $d);
?>
Nitrite is a Java based open source document store, which expose a MongoDB like API.
It uses MVStore from H2 database as underlying storage.
It was first released in 2017 by Anindya Chatterjee.
It uses standard Apache 2.0 license.
It is an embedded database and run together with the application. That obviously limits scalability compared to MongoDB, but also makes deployment much easier.
Data model:
Supported platforms | Any platform with Java |
Supported languages | Java and other JVM languages |
Features | It has a replication feature |
Missing features |
Key value store style example:
package nosql.nitrite;
import java.io.Serializable;
public class Data implements Serializable {
private static final long serialVersionUID = 1L;
private String id;
private int iv;
private double xv;
private String sv;
public Data() {
this("", 0, 0.0, "");
}
public Data(String id, int iv, double xv, String sv) {
this.id = id;
this.iv = iv;
this.xv = xv;
this.sv = sv;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public int getIv() {
return iv;
}
public void setIv(int iv) {
this.iv = iv;
}
public double getXv() {
return xv;
}
public void setXv(double xv) {
this.xv = xv;
}
public String getSv() {
return sv;
}
public void setSv(String sv) {
this.sv = sv;
}
@Override
public String toString() {
return String.format("{iv: %d, xv: %f, sv: %s}", iv, xv, sv);
}
}
package nosql.nitrite;
import org.dizitart.no2.Document;
import org.dizitart.no2.Filter;
import org.dizitart.no2.Nitrite;
import org.dizitart.no2.NitriteCollection;
import org.dizitart.no2.mapper.JacksonFacade;
import org.dizitart.no2.mapper.MapperFacade;
import static org.dizitart.no2.filters.Filters.*;
public class Test {
private static MapperFacade map = new JacksonFacade();
public static Document serialize(Data o) {
Document res = new Document();
res.put("id", o.getId());
res.put("iv", o.getIv());
res.put("xv", o.getXv());
res.put("sv", o.getSv());
return res;
}
private static Data deserialize(Document doc) {
Data res = new Data();
res.setId((String)doc.get("id"));
res.setIv((Integer)doc.get("iv"));
res.setXv((Double)doc.get("xv"));
res.setSv((String)doc.get("sv"));
return res;
}
private static void dump(NitriteCollection col, Filter filter) {
System.out.println("dumping " + filter);
boolean foundsome = false;
for(Document d : col.find(filter)) {
System.out.println(map.toJson(d));
Data o = deserialize(d);
System.out.println(o);
foundsome = true;
}
if(!foundsome) {
System.out.println("Not found");
}
}
private static final int NREC = 1000;
public static void main(String[] args) {
// open
Nitrite db = Nitrite.builder().filePath("/work/N/TestDB.no2").openOrCreate();
NitriteCollection col = db.getCollection("data");
// put data
for(int i = 0; i < NREC; i++) {
Data o = new Data("Doc#" + (1000 + i + 1), i + 1, i + 1.0, String.format("This is value %d", i + 1));
col.insert(serialize(o));
}
//
Filter filter;
filter = eq("id", "Doc#" + 1077);
// get
dump(col, filter);
// delete
col.remove(filter);
// get non-existing
dump(col, filter);
//
filter = eq("id", "Doc#" + 1088);
// get and update
dump(col, filter);
Data o = deserialize(col.find(filter).firstOrDefault());
o.setIv(o.getIv() + 1);
o.setXv(o.getXv() + 0.1);
o.setSv(o.getSv() + " updated");
col.update(filter, serialize(o));
dump(col, filter);
// list all
int n = 0;
for(Document itd : col.find()) {
Data ito = deserialize(itd);
if(!ito.getId().startsWith("Doc#")) {
System.out.println("Unexpected document id: " + ito.getId());
}
if(ito. getIv() < 1 || NREC < ito.getIv()) {
System.out.println("Unexpected value :" + ito);
}
n++;
}
System.out.println(n);
// list documents where "Doc#1075" <= id < "Doc#1085"
int n2 = 0;
filter = and(gte("id", "Doc#" + 1075), lt("id", "Doc#" + 1085));
for(Document it2d : col.find(filter)) {
Data it2o = deserialize(it2d);
if(!it2o.getId().startsWith("Doc#")) {
System.out.println("Unexpected document id: " + it2o.getId());
}
n2++;
}
System.out.println(n2);
// close
col.close();
db.close();
}
}
Relational database style example:
package nosql.nitrite;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public String getItemName() {
return itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public int getQuantity() {
return quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
}
package nosql.nitrite;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public String getCustomer() {
return customer;
}
public List<OrderLine> getItems() {
return items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
}
package nosql.nitrite;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import org.dizitart.no2.Document;
import org.dizitart.no2.Filter;
import org.dizitart.no2.Nitrite;
import org.dizitart.no2.NitriteCollection;
//import org.dizitart.no2.mapper.JacksonFacade;
//import org.dizitart.no2.mapper.MapperFacade;
import static org.dizitart.no2.filters.Filters.*;
public class Test2 {
//private static MapperFacade map = new JacksonFacade();
public static Document serialize(OrderLine ol) {
Document d = new Document();
d.put("itemNo", ol.getItemNo());
d.put("itemName", ol.getItemName());
d.put("itemPrice", ol.getItemPrice().toString());
d.put("quantity", ol.getQuantity());
return d;
}
public static Document serialize(Order o) {
Document d = new Document();
d.put("orderId", o.getOrderId());
d.put("customer", o.getCustomer());
List<Document> a = new ArrayList<>();
for(OrderLine ol : o.getItems()) {
a.add(serialize(ol));
}
d.put("items", a);
return d;
}
public static OrderLine deserializeOrderLine(Document d) {
return new OrderLine((Integer)d.get("itemNo"), (String)d.get("itemName"), new BigDecimal((String)d.get("itemPrice")), (Integer)d.get("quantity"));
}
public static Order deserializeOrder(Document d) {
Order res = new Order((Integer)d.get("orderId"), (String)d.get("customer"));
@SuppressWarnings("unchecked")
List<Document> a = (List<Document>)d.get("items");
for(Document a1 : a) {
res.getItems().add(deserializeOrderLine(a1));
}
return res;
}
private static void dump(NitriteCollection col, Filter filter) {
System.out.println("dumping " + filter);
for(Document d : col.find(filter)) {
//System.out.println(map.toJson(d));
Order o = deserializeOrder(d);
System.out.printf("Id : %d\n", o.getOrderId());
System.out.printf("Customer : %s\n", o.getCustomer());
System.out.printf("Price : %s\n", o.getPrice());
for(OrderLine ol : o.getItems()) {
System.out.printf(" %2d %-30s %10s %4d %10s\n", ol.getItemNo(), ol.getItemName(), ol.getItemPrice(), ol.getQuantity(), ol.getPrice());
}
}
}
public static void main(String[] args) {
// open
Nitrite db = Nitrite.builder().filePath("/work/N/TestDB.no2").openOrCreate();
NitriteCollection col = db.getCollection("order");
// setup
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
col.insert(serialize(o1));
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
col.insert(serialize(o2));
// query
Filter filter;
filter = ALL; // all
dump(col, filter);
filter = eq("orderId", 2); // where id=2
dump(col, filter);
filter = eq("customer", "A A"); // where customer='A A'
dump(col, filter);
filter = regex("customer", "A.*");// where customer like 'A%'
dump(col, filter);
filter = elemMatch("items", eq("itemName", "i5 CPU")); // where items.name='i5 CPU'
dump(col, filter);
filter = elemMatch("items", regex("itemName", ".*Java.*")); // where items.name like '%Java%'
dump(col, filter);
filter = in("orderId", 1, 2); // where id in (1,2)
dump(col, filter);
// close
col.close();
db.close();
}
}
True document store style:
package nosql.nitrite;
//import java.util.List;
import org.dizitart.no2.Document;
import org.dizitart.no2.Filter;
import org.dizitart.no2.Nitrite;
import org.dizitart.no2.NitriteCollection;
import org.dizitart.no2.mapper.JacksonFacade;
import org.dizitart.no2.mapper.MapperFacade;
import static org.dizitart.no2.filters.Filters.*;
public class Test3 {
//@SuppressWarnings("unchecked")
private static void dump(NitriteCollection col, Filter filter) {
System.out.println("dumping " + filter);
for(Document d : col.find(filter)) {
System.out.println(d);
/*
System.out.printf("Id : %d\n", (Integer)d.get("id"));
System.out.printf("Customer : %s\n", (String)d.get("customer"));
for(Document d1 : (List<Document>)d.get("items")) {
System.out.printf(" %2d %-30s %10s %4d %s\n", (Integer)d1.get("no"),
(String)d1.get("name"),
(String)d1.get("price"),
d1.get("quantity") != null ? (Integer)d1.get("quantity") : new Integer(1),
d1.get("note") != null ? (String)d1.get("note") : "");
}
*/
}
}
public static void main(String[] args) {
// open
Nitrite db = Nitrite.builder().filePath("/work/N/TestDB.no2").openOrCreate();
NitriteCollection col = db.getCollection("json");
MapperFacade map = new JacksonFacade();
// setup
String s1 = "{ \"id\": 1, \"customer\": \"A A\", \"items\": [ { \"no\": 1, \"name\": \"A good Java book\", \"price\": \"19.95\" } ] }";
System.out.println(s1);
Document d1 = map.parse(s1);
col.insert(d1);
String s2 = "{ \"id\": 2, \"customer\": \"B B\", \"items\": [ { \"no\": 1, \"name\": \"ASUS MB\", \"price\": \"249.00\" }, " +
"{ \"no\": 1, \"name\": \"i5 CPU\", \"price\": \"299.00\", \"note\": \"handle with care\" }, " +
"{ \"no\": 1, \"name\": \"4 GB kit\", \"price\": \"249.00\", \"quantity\": 4} ] }";
System.out.println(s2);
Document d2 = map.parse(s2);
col.insert(d2);
// query
Filter filter;
filter = ALL; // all
dump(col, filter);
filter = eq("id", 2); // where id=2
dump(col, filter);
filter = eq("customer", "A A"); // where customer='A A'
dump(col, filter);
filter = regex("customer", "A.*");// where customer like 'A%'
dump(col, filter);
filter = elemMatch("items", eq("name", "i5 CPU")); // where items.name='i5 CPU'
dump(col, filter);
filter = elemMatch("items", regex("name", ".*Java.*")); // where items.name like '%Java%'
dump(col, filter);
filter = in("id", 1, 2); // where id in (1,2)
dump(col, filter);
// close
col.close();
db.close();
}
}
LiteDB is a .NET based open source document store, which expose a MongoDB like API.
It was first released in 2015 by Maurício David.
It uses standard MIT license.
It is an embedded database and run together with the application. That obviously limits scalability compared to MongoDB, but also makes deployment much easier.
Data model:
Supported platforms | Windows (.NET) Windows, Linux, macOS (.NET Core) |
Supported languages | C# and VB.NET |
Features | Transaction support |
Missing features |
Key value store style example:
using System;
using System.Linq;
using LiteDB;
namespace NoSQL.LiteDB
{
public class Data
{
public string Id { get; set; }
public int Iv { get; set; }
public double Xv { get; set; }
public string Sv { get; set; }
public override string ToString()
{
return string.Format("(iv: {0}, xv: {1}, sv: {2})", Iv, Xv, Sv);
}
}
public class Program
{
private static BsonDocument Serialize(Data o)
{
BsonDocument res = new BsonDocument();
res.Add("id", o.Id);
res.Add("iv", o.Iv);
res.Add("xv", o.Xv);
res.Add("sv", o.Sv);
return res;
}
private static Data Deserialize(BsonDocument doc)
{
Data res = new Data();
res.Id = doc["id"].AsString;
res.Iv = doc["iv"].AsInt32;
res.Xv = doc["xv"].AsDouble;
res.Sv = doc["sv"].AsString;
return res;
}
private static void Dump(ILiteCollection<BsonDocument> col, BsonExpression expr)
{
Console.WriteLine("dumping " + expr.Source);
bool foundsome = false;
foreach (BsonDocument d in col.Find(expr))
{
//Console.WriteLine(JsonSerializer.Serialize(d));
Data o = Deserialize(d);
Console.WriteLine(o);
foundsome = true;
}
if (!foundsome)
{
Console.WriteLine("Not found");
}
}
private const int NREC = 1000;
public static void Main(string[] args)
{
// open
ILiteDatabase db = new LiteDatabase(@"C:\Work\L\TestDB.LiD");
ILiteCollection<BsonDocument> col = db.GetCollection("data");
// put data
for(int i = 0; i < NREC; i++)
{
Data ox = new Data { Id = "Doc#" + (1000 + i + 1), Iv = i + 1, Xv = i + 1.0, Sv = string.Format("This is value {0}", i + 1) };
col.Insert(Serialize(ox));
}
//
BsonExpression expr;
expr = Query.EQ("id", "Doc#" + 1077);
// get
Dump(col, expr);
// delete
col.DeleteMany(expr);
// get non-existing
Dump(col, expr);
// update and get
expr = Query.EQ("id", "Doc#" + 1088);
Dump(col, expr);
BsonDocument d = col.FindOne(expr);
Data o = Deserialize(d);
o.Iv = o.Iv + 1;
o.Xv = o.Xv + 0.1;
o.Sv = o.Sv + " updated";
col.Update(d["_id"], Serialize(o));
Dump(col, expr);
// list all
int n = 0;
foreach (BsonDocument itd in col.FindAll())
{
Data ito = Deserialize(itd);
if (!ito.Id.StartsWith("Doc#"))
{
Console.WriteLine("Unexpected document id: " + ito.Id);
}
if (ito.Iv < 1 || NREC < ito.Iv)
{
Console.WriteLine("Unexpected value :" + ito);
}
n++;
}
Console.WriteLine(n);
// list documents where "Doc#1075" <= id < "Doc#1085"
int n2 = 0;
expr = Query.And(Query.GTE("id", "Doc#" + 1075), Query.LT("id", "Doc#" + 1085));
foreach (BsonDocument it2d in col.Find(expr))
{
Data it2o = Deserialize(it2d);
if (!it2o.Id.StartsWith("Doc#"))
{
Console.WriteLine("Unexpected document id: " + it2o.Id);
}
n2++;
}
Console.WriteLine(n2);
// close
db.Dispose();
}
}
}
Relational database style example:
using System;
using System.Collections.Generic;
using System.Linq;
using LiteDB;
namespace NoSQL.LiteDB
{
public class OrderLine
{
public int ItemNo { get; set; }
public string ItemName { get; set; }
public decimal ItemPrice { get; set; }
public int Quantity { get; set; }
public decimal Price
{
get
{
return ItemPrice * Quantity;
}
}
}
public class Order
{
public int OrderId { get; set; }
public string Customer { get; set; }
public IList<OrderLine> Items { get; set; }
public decimal Price
{
get
{
return Items.Sum(ol => ol.Price);
}
}
}
public class Program
{
private static BsonDocument Serialize(OrderLine ol)
{
BsonDocument d = new BsonDocument();
d.Add("itemNo", ol.ItemNo);
d.Add("itemName", ol.ItemName);
d.Add("itemPrice", ol.ItemPrice);
d.Add("quantity", ol.Quantity);
return d;
}
private static BsonDocument Serialize(Order o)
{
BsonDocument d = new BsonDocument();
d.Add("orderId", o.OrderId);
d.Add("customer", o.Customer);
BsonArray a = new BsonArray();
foreach (OrderLine ol in o.Items)
{
a.Add(Serialize(ol));
}
d.Add("items", a);
return d;
}
private static OrderLine DeserializeOrderLine(BsonDocument d)
{
return new OrderLine { ItemNo = d["itemNo"].AsInt32,
ItemName = d["itemName"].AsString,
ItemPrice = d["itemPrice"].AsDecimal,
Quantity = d["quantity"].AsInt32 };
}
private static Order DeserializeOrder(BsonDocument d)
{
Order res = new Order { OrderId = d["orderId"].AsInt32, Customer = d["customer"].AsString, Items = new List<OrderLine>() };
BsonArray a = d["items"].AsArray;
foreach (BsonDocument a1 in a)
{
res.Items.Add(DeserializeOrderLine(a1));
}
return res;
}
private static void Dump(ILiteCollection<BsonDocument> col, BsonExpression expr)
{
Console.WriteLine("dumping " + expr);
foreach (BsonDocument d in col.Find(expr))
{
Console.WriteLine(JsonSerializer.Serialize(d));
Order o = DeserializeOrder(d);
Console.WriteLine("Id : {0}", o.OrderId);
Console.WriteLine("Customer : {0}", o.Customer);
Console.WriteLine("Price : {0}", o.Price);
foreach (OrderLine ol in o.Items)
{
Console.WriteLine(" {0,2} {1,-30} {2,10:F2} {3,4} {4,10:F2}", ol.ItemNo, ol.ItemName, ol.ItemPrice, ol.Quantity, ol.Price);
}
}
}
public static void Main(string[] args)
{
// open
ILiteDatabase db = new LiteDatabase(@"C:\Work\L\TestDB.LiD");
ILiteCollection<BsonDocument> col = db.GetCollection("order");
// setup
Order o1 = new Order { OrderId = 1, Customer = "A A", Items = new List<OrderLine>() };
o1.Items.Add(new OrderLine { ItemNo = 1, ItemName = "A good C# book", ItemPrice = 19.95m, Quantity = 1 });
col.Insert(Serialize(o1));
Order o2 = new Order { OrderId = 2, Customer = "B B", Items = new List<OrderLine>() };
o2.Items.Add(new OrderLine { ItemNo = 1, ItemName = "ASUS MB", ItemPrice = 249.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 2, ItemName = "i5 CPU", ItemPrice = 299.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 3, ItemName = "4 GB kit", ItemPrice = 29.00m, Quantity = 4 });
col.Insert(Serialize(o2));
// query
BsonExpression expr;
expr = Query.GTE("orderId", 0); // all (where id >= 0)
Dump(col, expr);
expr = Query.EQ("orderId", 2); // where id=2
Dump(col, expr);
expr = Query.EQ("customer", "A A"); // where customer='A A'
Dump(col, expr);
expr = Query.StartsWith("customer", "A"); // where customer LIKE 'A%'
Dump(col, expr);
// **** I can't get it working **** // where items.name='i5 CPU'
// **** I can't get it working **** // where items.name like '%C#%'
expr = Query.In("orderId", new BsonArray(new List<BsonValue> { 1, 2 })); // where id IN (1,2)
Dump(col, expr);
// close
db.Dispose();
}
}
}
Note that LiteDB has much better query capabilities when used as object repository.
True document store style:
using System;
using System.Collections.Generic;
using System.Linq;
using LiteDB;
namespace NoSQL.LiteDB
{
public class Program
{
private static void Dump(ILiteCollection<BsonDocument> col, BsonExpression expr)
{
Console.WriteLine("dumping " + expr);
foreach (BsonDocument d in col.Find(expr))
{
Console.WriteLine(JsonSerializer.Serialize(d));
/*
Console.WriteLine("Id : {0}", d["id"].AsInt32);
Console.WriteLine("Customer : {0}", d["customer"].AsString);
foreach (BsonDocument d1 in d["items"].AsArray)
{
Console.WriteLine(" {0,2} {1,-30} {2,10:F2} {3,4} {4,10:F2}", d1["no"].AsInt32,
d1["name"].AsString,
d1["price"].AsString,
d1.ContainsKey("quantity") ? d1["quantity"].AsInt32 : 1,
d1.ContainsKey("note") ? d1["note"].AsString : "");
}
*/
}
}
public static void Main(string[] args)
{
// open
ILiteDatabase db = new LiteDatabase(@"C:\Work\L\TestDB.LiD");
ILiteCollection<BsonDocument> col = db.GetCollection("json");
// setup
string s1 = "{ \"id\": 1, \"customer\": \"A A\", \"items\": [ { \"no\": 1, \"name\": \"A good C# book\", \"price\": \"19.95\" } ] }";
Console.WriteLine(s1);
BsonDocument d1 = JsonSerializer.Deserialize(s1).AsDocument;
col.Insert(d1);
String s2 = "{ \"id\": 2, \"customer\": \"B B\", \"items\": [ { \"no\": 1, \"name\": \"ASUS MB\", \"price\": \"249.00\" }, " +
"{ \"no\": 1, \"name\": \"i5 CPU\", \"price\": \"299.00\", \"note\": \"handle with care\" }, " +
"{ \"no\": 1, \"name\": \"4 GB kit\", \"price\": \"249.00\", \"quantity\": 4} ] }";
Console.WriteLine(s2);
BsonDocument d2 = JsonSerializer.Deserialize(s2).AsDocument;
col.Insert(d2);
// query
BsonExpression expr;
expr = Query.GTE("id", 0); // all (where id >= 0)
Dump(col, expr);
expr = Query.EQ("id", 2); // where id=2
Dump(col, expr);
expr = Query.EQ("customer", "A A"); // where customer='A A'
Dump(col, expr);
expr = Query.StartsWith("customer", "A"); // where customer LIKE 'A%'
Dump(col, expr);
// **** I can't get it working **** // where items.name='i5 CPU'
// **** I can't get it working **** // where items.name like '%C#%'
expr = Query.In("id", new BsonArray(new List<BsonValue> { 1, 2 })); // where id IN (1,2)
Dump(col, expr);
// close
db.Dispose();
}
}
}
For examples of using OrientDB as graph database see here.
Let us see an example where OrientDB is really just used as a key value store - aka not fully utilizing the document store aspect.
Java API comes with OrientDB.
package nosql.orientdb.doc;
public class Data {
private String id;
private int iv;
private double xv;
private String sv;
public Data() {
this("", 0, 0.0, "");
}
public Data(String id, int iv, double xv, String sv) {
this.id = id;
this.iv = iv;
this.xv = xv;
this.sv = sv;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public int getIv() {
return iv;
}
public void setIv(int iv) {
this.iv = iv;
}
public double getXv() {
return xv;
}
public void setXv(double xv) {
this.xv = xv;
}
public String getSv() {
return sv;
}
public void setSv(String sv) {
this.sv = sv;
}
@Override
public String toString() {
return String.format("{iv: %d, xv: %f, sv: %s}", iv, xv, sv);
}
}
package nosql.orientdb.doc;
import com.orientechnologies.orient.core.db.ODatabaseSession;
import com.orientechnologies.orient.core.db.OrientDB;
import com.orientechnologies.orient.core.record.impl.ODocument;
import com.orientechnologies.orient.core.sql.executor.OResultSet;
public class Test {
private static ODocument serialize(Data o, ODocument d) {
d.field("id", o.getId());
d.field("iv", o.getIv());
d.field("xv", o.getXv());
d.field("sv", o.getSv());
return d;
}
private static ODocument serialize(Data o) {
return serialize(o, new ODocument("Data"));
}
private static Data deserialize(ODocument doc) {
Data res = new Data();
res.setId(doc.field("id"));
res.setIv(doc.field("iv"));
res.setXv(doc.field("xv"));
res.setSv(doc.field("sv"));
return res;
}
private static void dump(ODatabaseSession db, String id) {
System.out.println("dumping " + id);
boolean foundsome = false;
OResultSet rs = db.query("SELECT FROM Data WHERE id = ?", id);
while(rs.hasNext()) {
ODocument d = (ODocument)rs.next().getRecord().get();
Data o = deserialize(d);
System.out.println(o);
foundsome = true;
}
if(!foundsome) {
System.out.println("Not found");
}
}
private static final int NREC = 1000;
private static final int JAVA_OFFSET = 1 * NREC;
public static void main(String[] args) {
// open
OrientDB client = new OrientDB("remote:localhost", null);
ODatabaseSession db = client.open("test", "admin", "hemmeligt");
// put data
for(int i = 0; i < NREC; i++) {
Data o = new Data("Doc-" + (JAVA_OFFSET + i + 1), i + 1, i + 1.0, String.format("This is value %d", i + 1));
db.save(serialize(o));
}
//
String id;
ODocument d;
id = "Doc-" + (JAVA_OFFSET + 77);
// get
dump(db, id);
// delete
d = (ODocument)db.query("SELECT FROM Data WHERE id = ?", id).next().getRecord().get();
d.delete();
// get non existing
dump(db, id);
// update and get
id = "Doc-" + (JAVA_OFFSET + 88);
dump(db, id);
d = (ODocument)db.query("SELECT FROM Data WHERE id = ?", id).next().getRecord().get();
Data o = deserialize(d);
o.setIv(o.getIv() + 1);
o.setXv(o.getXv() + 0.1);
o.setSv(o.getSv() + " updated");
serialize(o, d).save(); // must reuse same ODocument to UPDATE instead of INSERT
dump(db, id);
// list all
int n = 0;
for(ODocument itd : db.browseClass("Data")) {
Data ito = deserialize(itd);
if(!ito.getId().startsWith("Doc-")) {
System.out.println("Unexpected document id: " + ito.getId());
}
if(ito.getIv() < 1 || NREC < ito.getIv()) {
System.out.println("Unexpected value :" + ito);
}
n++;
}
System.out.println(n);
// list documents where "Doc#n075" <= id < "Doc#n085"
int n2 = 0;
OResultSet rs = db.query("SELECT FROM Data WHERE id BETWEEN ? AND ?", "Doc-" + (JAVA_OFFSET + 75), "Doc-" + (JAVA_OFFSET + 84));
while(rs.hasNext()) {
ODocument it2d = (ODocument)rs.next().getRecord().get();
Data it2o = deserialize(it2d);
if(!it2o.getId().startsWith("Doc-")) {
System.out.println("Unexpected document id: " + it2o.getId());
}
n2++;
}
System.out.println(n2);
// close
db.close();
client.close();
}
}
Java API comes with OrientDB.
package nosql.orientdb.doc;
public class Data {
private String id;
private int iv;
private double xv;
private String sv;
public Data() {
this("", 0, 0.0, "");
}
public Data(String id, int iv, double xv, String sv) {
this.id = id;
this.iv = iv;
this.xv = xv;
this.sv = sv;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public int getIv() {
return iv;
}
public void setIv(int iv) {
this.iv = iv;
}
public double getXv() {
return xv;
}
public void setXv(double xv) {
this.xv = xv;
}
public String getSv() {
return sv;
}
public void setSv(String sv) {
this.sv = sv;
}
@Override
public String toString() {
return String.format("{iv: %d, xv: %f, sv: %s}", iv, xv, sv);
}
}
TestX.java:
package nosql.orientdb.doc;
import com.orientechnologies.orient.core.db.ODatabaseSession;
import com.orientechnologies.orient.core.db.OrientDB;
import com.orientechnologies.orient.core.record.impl.ODocument;
import com.orientechnologies.orient.core.sql.executor.OResultSet;
public class TestX {
private static Data deserialize(ODocument doc) {
Data res = new Data();
res.setId(doc.field("id"));
res.setIv(doc.field("iv"));
res.setXv(doc.field("xv"));
res.setSv(doc.field("sv"));
return res;
}
private static void dump(ODatabaseSession db, String id) {
System.out.println("dumping " + id);
boolean foundsome = false;
OResultSet rs = db.query("SELECT FROM Data WHERE id = ?", id);
while(rs.hasNext()) {
ODocument d = (ODocument)rs.next().getRecord().get();
Data o = deserialize(d);
System.out.println(o);
foundsome = true;
}
if(!foundsome) {
System.out.println("Not found");
}
}
private static final int NREC = 1000;
private static final int JAVA_OFFSET = 1 * NREC;
public static void main(String[] args) {
// open
OrientDB client = new OrientDB("remote:localhost", null);
ODatabaseSession db = client.open("test", "admin", "hemmeligt");
// put data
for(int i = 0; i < NREC; i++) {
Data o = new Data("Doc-" + (JAVA_OFFSET + i + 1), i + 1, i + 1.0, String.format("This is value %d", i + 1));
db.command("INSERT INTO Data (id,iv,xv,sv) VALUES(?,?,?,?)", o.getId(), o.getIv(), o.getXv(), o.getSv());
}
//
String id;
ODocument d;
id = "Doc-" + (JAVA_OFFSET + 77);
// get
dump(db, id);
// delete
d = (ODocument)db.query("SELECT FROM Data WHERE id = id", id).next().getRecord().get();
d.delete();
// get non existing
dump(db, id);
// update and get
id = "Doc-" + (JAVA_OFFSET + 88);
dump(db, id);
d = (ODocument)db.query("SELECT FROM Data WHERE id = ?", id).next().getRecord().get();
Data o = deserialize(d);
o.setIv(o.getIv() + 1);
o.setXv(o.getXv() + 0.1);
o.setSv(o.getSv() + " updated");
db.command("UPDATE Data SET iv = ?, xv = ?, sv = ? WHERE id = ?", o.getIv(), o.getXv(), o.getSv(), o.getId());
dump(db, id);
// list all
int n = 0;
for(ODocument itd : db.browseClass("Data")) {
Data ito = deserialize(itd);
if(!ito.getId().startsWith("Doc-")) {
System.out.println("Unexpected document id: " + ito.getId());
}
if(ito.getIv() < 1 || NREC < ito.getIv()) {
System.out.println("Unexpected value :" + ito);
}
n++;
}
System.out.println(n);
// list documents where "Doc#n075" <= id < "Doc#n085"
int n2 = 0;
OResultSet rs = db.query("SELECT FROM Data WHERE id BETWEEN ? AND ?", "Doc-" + (JAVA_OFFSET + 75), "Doc-" + (JAVA_OFFSET + 84));
while(rs.hasNext()) {
ODocument it2d = (ODocument)rs.next().getRecord().get();
Data it2o = deserialize(it2d);
if(!it2o.getId().startsWith("Doc-")) {
System.out.println("Unexpected document id: " + it2o.getId());
}
n2++;
}
System.out.println(n2);
// close
db.close();
client.close();
}
}
Java API comes with OrientDB.
package nosql.orientdb.doc;
public class Data {
private String id;
private int iv;
private double xv;
private String sv;
public Data() {
this("", 0, 0.0, "");
}
public Data(String id, int iv, double xv, String sv) {
this.id = id;
this.iv = iv;
this.xv = xv;
this.sv = sv;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public int getIv() {
return iv;
}
public void setIv(int iv) {
this.iv = iv;
}
public double getXv() {
return xv;
}
public void setXv(double xv) {
this.xv = xv;
}
public String getSv() {
return sv;
}
public void setSv(String sv) {
this.sv = sv;
}
@Override
public String toString() {
return String.format("{iv: %d, xv: %f, sv: %s}", iv, xv, sv);
}
}
package nosql.orientdb.doc;
import java.util.List;
import com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx;
import com.orientechnologies.orient.core.record.impl.ODocument;
import com.orientechnologies.orient.core.sql.query.OSQLSynchQuery;
public class Test {
private static ODocument serialize(Data o, ODocument d) {
d.field("id", o.getId());
d.field("iv", o.getIv());
d.field("xv", o.getXv());
d.field("sv", o.getSv());
return d;
}
private static ODocument serialize(Data o) {
return serialize(o, new ODocument("Data"));
}
private static Data deserialize(ODocument doc) {
Data res = new Data();
res.setId(doc.field("id"));
res.setIv(doc.field("iv"));
res.setXv(doc.field("xv"));
res.setSv(doc.field("sv"));
return res;
}
private static void dump(ODatabaseDocumentTx db, String id) {
System.out.println("dumping " + id);
List<ODocument> rs = db.query(new OSQLSynchQuery<ODocument>(String.format("SELECT FROM Data WHERE id = '%s'", id)));
if(rs.size() > 0) {
ODocument d = rs.get(0);
Data o = deserialize(d);
System.out.println(o);
} else {
System.out.println("Not found");
}
}
private static final int NREC = 1000;
private static final int JAVA_OFFSET = 1 * NREC;
public static void main(String[] args) {
// open
ODatabaseDocumentTx db = new ODatabaseDocumentTx("remote:192.168.60.129/test");
db.open("admin", "hemmeligt");
// put data
for(int i = 0; i < NREC; i++) {
Data o = new Data("Doc-" + (JAVA_OFFSET + i + 1), i + 1, i + 1.0, String.format("This is value %d", i + 1));
db.save(serialize(o));
}
//
String id;
ODocument d;
id = "Doc-" + (JAVA_OFFSET + 77);
// get
dump(db, id);
// delete
d = (ODocument)db.query(new OSQLSynchQuery<ODocument>(String.format("SELECT FROM Data WHERE id = '%s'", id))).get(0);
d.delete();
// get non existing
dump(db, id);
// update and get
id = "Doc-" + (JAVA_OFFSET + 88);
dump(db, id);
d = (ODocument)db.query(new OSQLSynchQuery<ODocument>(String.format("SELECT FROM Data WHERE id = '%s'", id))).get(0);
Data o = deserialize(d);
o.setIv(o.getIv() + 1);
o.setXv(o.getXv() + 0.1);
o.setSv(o.getSv() + " updated");
serialize(o, d).save(); // must reuse same ODocument to UPDATE instead of INSERT
dump(db, id);
// list all
int n = 0;
for(ODocument itd : db.browseClass("Data")) {
Data ito = deserialize(itd);
if(!ito.getId().startsWith("Doc-")) {
System.out.println("Unexpected document id: " + ito.getId());
}
if(ito.getIv() < 1 || NREC < ito.getIv()) {
System.out.println("Unexpected value :" + ito);
}
n++;
}
System.out.println(n);
// list documents where "Doc#n075" <= id < "Doc#n085"
int n2 = 0;
List<ODocument> rs = db.query(new OSQLSynchQuery<ODocument>(String.format("SELECT FROM Data WHERE id BETWEEN '%s' AND '%s'", "Doc-" + (JAVA_OFFSET + 75), "Doc-" + (JAVA_OFFSET + 84))));
for(ODocument it2d : rs) {
Data it2o = deserialize(it2d);
if(!it2o.getId().startsWith("Doc-")) {
System.out.println("Unexpected document id: " + it2o.getId());
}
n2++;
}
System.out.println(n2);
// close
db.close();
}
}
Java API comes with OrientDB.
package nosql.orientdb.doc;
public class Data {
private String id;
private int iv;
private double xv;
private String sv;
public Data() {
this("", 0, 0.0, "");
}
public Data(String id, int iv, double xv, String sv) {
this.id = id;
this.iv = iv;
this.xv = xv;
this.sv = sv;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public int getIv() {
return iv;
}
public void setIv(int iv) {
this.iv = iv;
}
public double getXv() {
return xv;
}
public void setXv(double xv) {
this.xv = xv;
}
public String getSv() {
return sv;
}
public void setSv(String sv) {
this.sv = sv;
}
@Override
public String toString() {
return String.format("{iv: %d, xv: %f, sv: %s}", iv, xv, sv);
}
}
package nosql.orientdb.doc;
import java.math.BigDecimal;
import java.util.List;
import com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx;
import com.orientechnologies.orient.core.record.impl.ODocument;
import com.orientechnologies.orient.core.sql.OCommandSQL;
import com.orientechnologies.orient.core.sql.query.OSQLSynchQuery;
public class TestX {
private static Data deserialize(ODocument doc) {
Data res = new Data();
res.setId(doc.field("id"));
res.setIv(doc.field("iv"));
if(doc.field("xv") instanceof Double) { // values seems to be able to end up as double, float and BigDecimal
res.setXv(doc.field("xv"));
} else if(doc.field("xv") instanceof Float) {
res.setXv((float)doc.field("xv"));
} else if(doc.field("xv") instanceof BigDecimal) {
res.setXv(((BigDecimal)doc.field("xv")).doubleValue());
}
res.setSv(doc.field("sv"));
return res;
}
private static void dump(ODatabaseDocumentTx db, String id) {
System.out.println("dumping " + id);
List<ODocument> rs = db.query(new OSQLSynchQuery<ODocument>(String.format("SELECT FROM Data WHERE id = '%s'", id)));
if(rs.size() > 0) {
ODocument d = rs.get(0);
Data o = deserialize(d);
System.out.println(o);
} else {
System.out.println("Not found");
}
}
private static final int NREC = 1000;
private static final int JAVA_OFFSET = 1 * NREC;
public static void main(String[] args) {
// open
ODatabaseDocumentTx db = new ODatabaseDocumentTx("remote:192.168.60.129/test");
db.open("admin", "hemmeligt");
// put data
for(int i = 0; i < NREC; i++) {
Data o = new Data("Doc-" + (JAVA_OFFSET + i + 1), i + 1, i + 1.0, String.format("This is value %d", i + 1));
db.command(new OCommandSQL(String.format("INSERT INTO Data (id,iv,xv,sv) VALUES('%s',%d,%f,'%s')", o.getId(), o.getIv(), o.getXv(), o.getSv()))).execute();
}
//
String id;
ODocument d;
id = "Doc-" + (JAVA_OFFSET + 77);
// get
dump(db, id);
// delete
d = (ODocument)db.query(new OSQLSynchQuery<ODocument>(String.format("SELECT FROM Data WHERE id = '%s'", id))).get(0);
d.delete();
// get non existing
dump(db, id);
// update and get
id = "Doc-" + (JAVA_OFFSET + 88);
dump(db, id);
d = (ODocument)db.query(new OSQLSynchQuery<ODocument>(String.format("SELECT FROM Data WHERE id = '%s'", id))).get(0);
Data o = deserialize(d);
o.setIv(o.getIv() + 1);
o.setXv(o.getXv() + 0.1);
o.setSv(o.getSv() + " updated");
db.command(new OCommandSQL(String.format("UPDATE Data SET iv = %d, xv = %f, sv = '%s' WHERE id = '%s'", o.getIv(), o.getXv(), o.getSv(), o.getId()))).execute();
dump(db, id);
// list all
int n = 0;
for(ODocument itd : db.browseClass("Data")) {
Data ito = deserialize(itd);
if(!ito.getId().startsWith("Doc-")) {
System.out.println("Unexpected document id: " + ito.getId());
}
if(ito.getIv() < 1 || NREC < ito.getIv()) {
System.out.println("Unexpected value :" + ito);
}
n++;
}
System.out.println(n);
// list documents where "Doc#n075" <= id < "Doc#n085"
int n2 = 0;
List<ODocument> rs = db.query(new OSQLSynchQuery<ODocument>(String.format("SELECT FROM Data WHERE id BETWEEN '%s' AND '%s'", "Doc-" + (JAVA_OFFSET + 75), "Doc-" + (JAVA_OFFSET + 84))));
for(ODocument it2d : rs) {
Data it2o = deserialize(it2d);
if(!it2o.getId().startsWith("Doc-")) {
System.out.println("Unexpected document id: " + it2o.getId());
}
n2++;
}
System.out.println(n2);
// close
db.close();
}
}
There are two OrientDB drivers for .NET - the old OrientDB-Net.binary.Innov8tive (not that '8' is not a typo!) driver and the new OrientDB.Net.Core driver. Both are available via NuGet.
As the new driver is still considered experimental we will use the old driver.
Note that this driver seems to only work with 2.x server - not with 3.x server.
using System;
using Orient.Client;
namespace NoSQL.OrientDB
{
public class Data
{
public string Id { get; set; }
public int Iv { get; set; }
public double Xv { get; set; }
public string Sv { get; set; }
public override string ToString()
{
return string.Format("(iv: {0}, xv: {1}, sv: {2})", Iv, Xv, Sv);
}
}
public class Program
{
private static Data Deserialize(ODocument d)
{
return new Data { Id = d.GetField<string>("id"), Iv = d.GetField<int>("iv"), Xv = d.GetField<double>("xv"), Sv = d.GetField<string>("sv") };
}
private static void Dump(ODatabase db, String id)
{
Console.WriteLine("dumping " + id);
bool foundsome = false;
foreach(ODocument d in db.Query(string.Format("SELECT FROM Data WHERE id = '{0}'", id)))
{
Data o = Deserialize(d);
Console.WriteLine(o);
foundsome = true;
}
if(!foundsome)
{
Console.WriteLine("Not found");
}
}
private const int NREC = 1000;
private const int DN_OFFSET = 2 * NREC;
public static void Main(string[] args)
{
// open
ODatabase db = new ODatabase("192.168.60.129", 2424, "test", ODatabaseType.Document, "admin", "hemmeligt");
// put data
for (int i = 0; i < NREC; i++)
{
Data onew = new Data { Id = "Doc-" + (DN_OFFSET + i + 1), Iv = i + 1, Xv = i + 1.0, Sv = string.Format("This is value {0}", i + 1) };
db.Insert().Into("Data").Set("id", onew.Id).Set("iv", onew.Iv).Set("xv", onew.Xv).Set("sv", onew.Sv).Run();
}
//
string id;
ODocument d;
id = "Doc-" + (DN_OFFSET + 77);
// get
Dump(db, id);
// delete
db.Delete.Document().Class("Data").Where("id").Equals(id).Run();
// get non existing
Dump(db, id);
// update and get
id = "Doc-" + (DN_OFFSET + 88);
Dump(db, id);
d = db.Query(string.Format("SELECT FROM Data WHERE id = '{0}'", id))[0];
Data o = Deserialize(d);
o.Iv = o.Iv + 1;
o.Xv = o.Xv + 0.1;
o.Sv = o.Sv + " updated";
db.Update().Class("Data").Where("id").Equals(id).Set("iv", o.Iv).Set("xv", o.Xv).Set("sv", o.Sv).Run();
Dump(db, id);
// list all
int n = 0;
foreach(ODocument itd in db.Query("SELECT FROM data"))
{
Data ito = Deserialize(itd);
if(!ito.Id.StartsWith("Doc-"))
{
Console.WriteLine("Unexpected document id: " + ito.Id);
}
if(ito.Iv < 1 || NREC < ito.Iv)
{
Console.WriteLine("Unexpected value :" + ito);
}
n++;
}
Console.WriteLine(n);
// list documents where "Doc#n075" <= id < "Doc#n085"
int n2 = 0;
foreach (ODocument it2d in db.Query(string.Format("SELECT FROM Data WHERE id BETWEEN '{0}' AND '{1}'", "Doc-" + (DN_OFFSET + 75), "Doc-" + (DN_OFFSET + 84))))
{
Data it2o = Deserialize(it2d);
if (!it2o.Id.StartsWith("Doc-"))
{
Console.WriteLine("Unexpected document id: " + it2o.Id);
}
n2++;
}
Console.WriteLine(n2);
// close
db.Close();
}
}
}
There are two OrientDB drivers for .NET - the old OrientDB-Net.binary.Innov8tive (not that '8' is not a typo!) driver and the new OrientDB.Net.Core driver. Both are available via NuGet.
As the new driver is still considered experimental we will use the old driver.
Note that this driver seems to only work with 2.x server - not with 3.x server.
using System;
using Orient.Client;
namespace NoSQL.OrientDB
{
public class Data
{
public string Id { get; set; }
public int Iv { get; set; }
public double Xv { get; set; }
public string Sv { get; set; }
public override string ToString()
{
return string.Format("(iv: {0}, xv: {1}, sv: {2})", Iv, Xv, Sv);
}
}
public class Program
{
private static Data Deserialize(ODocument d)
{
if(d.GetField<object>("xv") is double) // if the actual value is a valid integer then the type end up as int
{
return new Data { Id = d.GetField<string>("id"), Iv = d.GetField<int>("iv"), Xv = d.GetField<double>("xv"), Sv = d.GetField<string>("sv") };
}
else if(d.GetField<object>("xv") is int)
{
return new Data { Id = d.GetField<string>("id"), Iv = d.GetField<int>("iv"), Xv = d.GetField<int>("xv"), Sv = d.GetField<string>("sv") };
}
else
{
return null;
}
}
private static void Dump(ODatabase db, String id)
{
Console.WriteLine("dumping " + id);
bool foundsome = false;
foreach(ODocument d in db.Query(string.Format("SELECT FROM Data WHERE id = '{0}'", id)))
{
Data o = Deserialize(d);
Console.WriteLine(o);
foundsome = true;
}
if(!foundsome)
{
Console.WriteLine("Not found");
}
}
private const int NREC = 1000;
private const int DN_OFFSET = 2 * NREC;
public static void Main(string[] args)
{
// open
ODatabase db = new ODatabase("192.168.60.129", 2424, "test", ODatabaseType.Document, "admin", "hemmeligt");
// put data
for (int i = 0; i < NREC; i++)
{
Data onew = new Data { Id = "Doc-" + (DN_OFFSET + i + 1), Iv = i + 1, Xv = i + 1.0, Sv = string.Format("This is value {0}", i + 1) };
db.Command(string.Format("INSERT INTO Data(id,iv,xv,sv) VALUES('{0}',{1},{2},'{3}')", onew.Id, onew.Iv, onew.Xv, onew.Sv));
}
//
string id;
ODocument d;
id = "Doc-" + (DN_OFFSET + 77);
// get
Dump(db, id);
// delete
db.Command(string.Format("DELETE FROM Data WHERE id = '{0}'",id));
// get non existing
Dump(db, id);
// update and get
id = "Doc-" + (DN_OFFSET + 88);
Dump(db, id);
d = db.Query(string.Format("SELECT FROM Data WHERE id = '{0}'", id))[0];
Data o = Deserialize(d);
o.Iv = o.Iv + 1;
o.Xv = o.Xv + 0.1;
o.Sv = o.Sv + " updated";
db.Command(string.Format("UPDATE Data SET iv = {0}, xv = {1}, sv = '{2}' WHERE id = '{3}'", o.Iv, o.Xv, o.Sv, id));
Dump(db, id);
// list all
int n = 0;
foreach(ODocument itd in db.Query("SELECT FROM data"))
{
Data ito = Deserialize(itd);
if(!ito.Id.StartsWith("Doc-"))
{
Console.WriteLine("Unexpected document id: " + ito.Id);
}
if(ito.Iv < 1 || NREC < ito.Iv)
{
Console.WriteLine("Unexpected value :" + ito);
}
n++;
}
Console.WriteLine(n);
// list documents where "Doc#n075" <= id < "Doc#n085"
int n2 = 0;
foreach (ODocument it2d in db.Query(string.Format("SELECT FROM Data WHERE id BETWEEN '{0}' AND '{1}'", "Doc-" + (DN_OFFSET + 75), "Doc-" + (DN_OFFSET + 84))))
{
Data it2o = Deserialize(it2d);
if (!it2o.Id.StartsWith("Doc-"))
{
Console.WriteLine("Unexpected document id: " + it2o.Id);
}
n2++;
}
Console.WriteLine(n2);
// close
db.Close();
}
}
}
The pyorient OrientDB driver for Python is available via pip.
Note that this driver seems to only work with 2.x server - not with 3.x server.
import pyorient
class Data(object):
def __init__(self, _id = '', _iv = 0, _xv = 0.0, _sv = ''):
self.id = _id
self.iv = _iv
self.xv = _xv
self.sv = _sv
def __str__(self):
return '(%d, %f, %s)' % (self.iv, self.xv, self.sv)
def deserialize(d):
return Data(d.id, d.iv, d.xv, d.sv)
def dump(db, id):
print('dumping ' + id)
r = db.query("SELECT FROM Data WHERE id = '%s'" % (id))
if len(r) > 0:
d = r[0]
#print(d)
o = deserialize(d)
print(o)
else:
print('Not found')
NREC = 1000
PY_OFFSET = 3 * NREC
# open
db = pyorient.OrientDB('192.168.60.129', 2424)
db.db_open('test', 'admin', 'hemmeligt', pyorient.DB_TYPE_DOCUMENT)
db.command("DELETE FROM Data")
# put data
for i in range(NREC):
o = Data('Doc-' + str(PY_OFFSET + i + 1), i + 1, i + 1.0, 'This is value %d' % (i + 1))
db.command("INSERT INTO Data (id,iv,xv,sv) VALUES('%s',%d,%f,'%s')" % (o.id, o.iv, o.xv, o.sv))
#
id = 'Doc-' + str(PY_OFFSET + 77)
# get
dump(db, id)
# delete
db.command("DELETE FROM Data WHERE id = '" + id + "'")
# get non existing
dump(db, id)
# update and get
id = 'Doc-' + str(PY_OFFSET + 88)
dump(db, id)
o = deserialize(db.query("SELECT FROM Data WHERE id = '%s'" % (id))[0])
o.iv = o.iv + 1
o.xv = o.xv + 0.1
o.sv = o.sv + ' updated'
db.command("UPDATE Data SET iv = %d, xv = %f, sv = '%s' WHERE id = '%s'" % (o.iv, o.xv, o.sv, o.id))
dump(db, id)
# list all
n = 0
for itd in db.query("SELECT FROM Data", 10000):
ito = deserialize(itd)
if not ito.id.startswith('Doc-'):
print('Unexpected document: ' + ito.id)
if ito.iv < 1 or NREC < ito.iv:
print('Unexpected value: ' + ito)
n = n + 1
print(n)
# list documents where "Doc#n075" <= id < "Doc#n085"
n2 = 0
for it2d in db.query("SELECT FROM Data WHERE id BETWEEN '%s' AND '%s'" % ('Doc-' + str(PY_OFFSET + 75), 'Doc-' + str(PY_OFFSET + 84))):
it2o = deserialize(it2d)
if not it2o.id.startswith('Doc-'):
print('Unexpected document: ' + it2o.id)
n2 = n2 + 1
print(n2)
An OrientDB driver for PHP is available via composer:
php composer.phar require "ostico/phporient"
Note that this driver seems to only work with 2.x server - not with 3.x server.
<?php
require "vendor/autoload.php";
class Data {
public $id;
public $iv;
public $xv;
public $sv;
public function __construct($id = '', $iv = 0, $xv = 0.0, $sv = '') {
$this->id = $id;
$this->iv = $iv;
$this->xv = $xv;
$this->sv = $sv;
}
public function __toString() {
return sprintf('(%d, %f, %s)', $this->iv, $this->xv, $this->sv);
}
}
function deserialize($d) {
return new Data($d['id'], $d['iv'], $d['xv'], $d['sv']);
}
use PhpOrient\PhpOrient;
function dump($db, $id) {
echo 'dumping ' . $id . "\r\n";
$r = $db->query(sprintf("SELECT FROM Data WHERE id = '%s'", $id));
if(count($r) > 0) {
$d = $r[0];
//echo "$d\r\n";
$o = deserialize($d);
echo "$o\r\n";
} else {
echo "Not found\r\n";
}
}
define('NREC', 1000);
define('PHP_OFFSET', 4 * 1000);
// open
$db = new PhpOrient('192.168.60.129', 2424);
$db->dbOpen('test', 'admin', 'hemmeligt');
$db->command("DELETE FROM Data");
// put
for($i = 0; $i < NREC; $i++) {
$o = new Data('Doc-' . (PHP_OFFSET + $i + 1), $i + 1, $i + 1.0, 'This is value ' . ($i + 1));
$db->command(sprintf("INSERT INTO Data (id,iv,xv,sv) VALUES('%s',%d,%f,'%s')", $o->id, $o->iv, $o->xv, $o->sv));
}
//
$id = 'Doc-' . (PHP_OFFSET + 77);
// get
dump($db, $id);
// delete
$db->command(sprintf("DELETE FROM Data WHERE id = '%s'", $id));
// get non-existing
dump($db, $id);
// update and get
$id = 'Doc-' . (PHP_OFFSET + 88);
dump($db, $id);
$o = deserialize($db->query(sprintf("SELECT FROM Data WHERE id = '%s'", $id))[0]);
$o->iv = $o->iv + 1;
$o->xv = $o->xv + 0.1;
$o->sv = $o->sv . ' updated';
$db->command(sprintf("UPDATE Data SET iv = %d, xv = %f, sv = '%s' WHERE id = '%s'", $o->iv, $o->xv, $o->sv, $o->id));
dump($db, $id);
// list all
$n = 0;
foreach($db->query("SELECT FROM Data", 10000) as $itd) {
$ito = deserialize($itd);
if(strpos($ito->id, 'Doc#') != 0) {
echo 'Unexcpected document: ' . $ito->id . "\r\n";
}
if($ito->iv < 1 || NREC < $ito->iv) {
echo 'Unexpected value: ' . $ito . "\r\n";
}
$n = $n + 1;
}
echo "$n\r\n";
// list documents where "Doc#n075" <= id < "Doc#n085"
$n2 = 0;
foreach($db->query(sprintf("SELECT FROM Data WHERE id BETWEEN '%s' AND '%s'", 'Doc-' . (PHP_OFFSET + 75), 'Doc-' . (PHP_OFFSET + 84)), 10000) as $it2d) {
$it2o = deserialize($it2d);
if(strpos($it2o->id, 'Doc#') != 0) {
echo 'Unexpected document: ' . $it2o->id . "\r\n";
}
$n2 = $n2 + 1;
}
echo "$n2\r\n";
?>
Let us see an example where JSON is considered the native format and where no schema is enforced by the application and it is queries freely.
Java API comes with OrientDB.
package nosql.orientdb.doc;
import com.orientechnologies.orient.core.db.ODatabaseSession;
import com.orientechnologies.orient.core.db.OrientDB;
import com.orientechnologies.orient.core.record.impl.ODocument;
import com.orientechnologies.orient.core.sql.executor.OResultSet;
public class Test3 {
private static void dump(ODatabaseSession db, String criteria) {
System.out.println("dumping " + criteria);
OResultSet rs = db.query("SELECT FROM Order " + criteria);
while(rs.hasNext()) {
ODocument d = (ODocument)rs.next().getRecord().get();
System.out.println(d.toJSON());
}
}
public static void main(String[] args) {
// open
OrientDB client = new OrientDB("remote:localhost", null);
ODatabaseSession db = client.open("test3", "admin", "hemmeligt");
// setup
String s1 = "{ \"id\": 1, \"customer\": \"A A\", \"items\": [ { \"no\": 1, \"name\": \"A good Java book\", \"price\": \"19.95\" } ] }";
System.out.println(s1);
ODocument d1 = new ODocument("Order");
d1.fromJSON(s1);
db.save(d1);
String s2 = "{ \"id\": 2, \"customer\": \"B B\", \"items\": [ { \"no\": 1, \"name\": \"ASUS MB\", \"price\": \"249.00\" }, " +
"{ \"no\": 1, \"name\": \"i5 CPU\", \"price\": \"299.00\", \"note\": \"handle with care\" }, " +
"{ \"no\": 1, \"name\": \"4 GB kit\", \"price\": \"249.00\", \"quantity\": 4} ] }";
System.out.println(s2);
ODocument d2 = new ODocument("Order");
d2.fromJSON(s2);
db.save(d2);
// query
dump(db, ""); // all
dump(db, "WHERE id = 2"); // where id=2
dump(db, "WHERE customer = 'A A'"); // where customer='A A'
dump(db, "WHERE customer LIKE 'A%'"); // where customer like 'A%'
dump(db, "WHERE items CONTAINS(name = 'i5 CPU')"); // where items.name='i5 CPU'
dump(db, "WHERE items CONTAINS(name LIKE '%Java%')"); // where items.name like '%Java%'
dump(db, "WHERE id IN [1,2]"); // where id in (1,2)
dump(db, "WHERE items CONTAINS(note IS NOT NULL)"); // where exists items.note
// close
db.close();
client.close();
}
}
Java API comes with OrientDB.
package nosql.orientdb.doc;
import java.util.List;
import com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx;
import com.orientechnologies.orient.core.record.impl.ODocument;
import com.orientechnologies.orient.core.sql.query.OSQLSynchQuery;
public class Test3 {
private static void dump(ODatabaseDocumentTx db, String criteria) {
System.out.println("dumping " + criteria);
List<ODocument> rs = db.query(new OSQLSynchQuery<ODocument>("SELECT FROM Order " + criteria));
for(ODocument d : rs) {
System.out.println(d.toJSON());
}
}
public static void main(String[] args) {
// open
ODatabaseDocumentTx db = new ODatabaseDocumentTx("remote:192.168.60.129/test3");
db.open("admin", "hemmeligt");
// setup
String s1 = "{ \"id\": 1, \"customer\": \"A A\", \"items\": [ { \"no\": 1, \"name\": \"A good Java book\", \"price\": \"19.95\" } ] }";
System.out.println(s1);
ODocument d1 = new ODocument("Order");
d1.fromJSON(s1);
db.save(d1);
String s2 = "{ \"id\": 2, \"customer\": \"B B\", \"items\": [ { \"no\": 1, \"name\": \"ASUS MB\", \"price\": \"249.00\" }, " +
"{ \"no\": 1, \"name\": \"i5 CPU\", \"price\": \"299.00\", \"note\": \"handle with care\" }, " +
"{ \"no\": 1, \"name\": \"4 GB kit\", \"price\": \"249.00\", \"quantity\": 4} ] }";
System.out.println(s2);
ODocument d2 = new ODocument("Order");
d2.fromJSON(s2);
db.save(d2);
// query
dump(db, ""); // all
dump(db, "WHERE id = 2"); // where id=2
dump(db, "WHERE customer = 'A A'"); // where customer='A A'
dump(db, "WHERE customer LIKE 'A%'"); // where customer like 'A%'
dump(db, "WHERE items CONTAINS(name = 'i5 CPU')"); // where items.name='i5 CPU'
dump(db, "WHERE items CONTAINS(name LIKE '%Java%')"); // where items.name like '%Java%'
dump(db, "WHERE id IN [1,2]"); // where id in (1,2)
dump(db, "WHERE items CONTAINS(note IS NOT NULL)"); // where exists items.note
// close
db.close();
}
}
PostgreSQL is a relational (SQL) database. But it has JSON support that makes it possible to use it as a NoSQL document store.
We will use Google GSON for JSON serialization/deserialization.
package nosql.pgsql;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public String getItemName() {
return itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public int getQuantity() {
return quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
@Override
public String toString() {
return String.format("(%d,%s,%s,%d)", itemNo, itemName, itemPrice, quantity);
}
}
package nosql.pgsql;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public String getCustomer() {
return customer;
}
public List<OrderLine> getItems() {
return items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
@Override
public String toString() {
return String.format("(%d,%s,%s)", orderId, customer, items.stream().map(ol -> ol.toString()).collect(Collectors.joining(",")));
}
}
package nosql.pgsql;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.google.gson.Gson;
public class Test2Rel {
private static Gson gson = new Gson();
private static void dump(String label, PreparedStatement sel) throws SQLException {
System.out.println(label + ":");
ResultSet rs = sel.executeQuery();
while(rs.next()) {
String doc = rs.getString(1);
System.out.println(doc);
Order o = gson.fromJson(doc, Order.class);
System.out.println(o);
}
}
public static void main(String[] args) throws SQLException {
// open
Connection con = DriverManager.getConnection("jdbc:postgresql://localhost/Test", "postgres", "xxxxxx");
Statement cre = con.createStatement();
cre.executeUpdate("CREATE TABLE order_table (id INTEGER NOT NULL, doc JSONB, PRIMARY KEY(id))");
cre.close();
// setup
PreparedStatement ins = con.prepareStatement("INSERT INTO order_table VALUES(?,?::JSONB)");
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
ins.setInt(1, 1);
ins.setString(2, gson.toJson(o1));
ins.executeUpdate();
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
ins.setInt(1, 2);
ins.setString(2, gson.toJson(o2));
ins.executeUpdate();
// query
PreparedStatement sel;
sel = con.prepareStatement("SELECT doc FROM order_table");
dump("all", sel);
sel = con.prepareStatement("SELECT doc FROM order_table WHERE doc->>'orderId' = ?");
sel.setString(1, "2");
dump("where id=2", sel);
sel = con.prepareStatement("SELECT doc FROM order_table WHERE doc->>'customer' = ?");
sel.setString(1, "A A");
dump("where customer='A A'", sel);
sel = con.prepareStatement("SELECT doc FROM order_table WHERE doc->>'customer' LIKE ?");
sel.setString(1, "A%");
dump("where customer like 'A%'", sel);
sel = con.prepareStatement("SELECT doc FROM order_table WHERE EXISTS (SELECT * FROM jsonb_array_elements(doc->'items') items WHERE items->>'itemName' = ?)");
sel.setString(1, "i5 CPU");
dump("where items.name='i5 CPU'", sel);
sel = con.prepareStatement("SELECT doc FROM order_table WHERE EXISTS (SELECT * FROM jsonb_array_elements(doc->'items') items WHERE items->>'itemName' LIKE ?)");
sel.setString(1, "%Java%");
dump("where items.name like '%Java%'", sel);
sel = con.prepareStatement("SELECT doc FROM order_table WHERE doc->>'orderId' IN ('" + 1 + "','" + 2 + "')"); // prepared statement and variable parameters is a problem
dump("where id in (1,2)", sel);
// close
con.close();
}
}
We will use NewtonSoft JSON.Net for JSON serialization/deserialization.
using System;
using System.Collections.Generic;
using System.Linq;
using Npgsql;
using Newtonsoft.Json;
namespace NoSQL.PgSQL
{
public class OrderLine
{
public int ItemNo { get; set; }
public string ItemName { get; set; }
public decimal ItemPrice { get; set; }
public int Quantity { get; set; }
public decimal Price
{
get
{
return ItemPrice * Quantity;
}
}
public override string ToString()
{
return string.Format("({0},{1},{2},{3})", ItemNo, ItemName, ItemPrice, Quantity);
}
}
public class Order
{
public int OrderId { get; set; }
public string Customer { get; set; }
public IList<OrderLine> Items { get; set; }
public decimal Price
{
get
{
return Items.Sum(ol => ol.Price);
}
}
public override string ToString()
{
return string.Format("({0},{1},{2})", OrderId, Customer, string.Join(",", Items.Select(ol => ol.ToString()).ToArray()));
}
}
public class Program
{
private static void Dump(String label, NpgsqlCommand sel)
{
Console.WriteLine(label + ":");
NpgsqlDataReader rdr = sel.ExecuteReader();
while(rdr.Read())
{
string doc = (string)rdr[0];
Console.WriteLine(doc);
Order o = JsonConvert.DeserializeObject<Order>(doc);
Console.WriteLine(o);
}
rdr.Dispose();
sel.Dispose();
}
public static void Main(string[] args)
{
// open and close
using(NpgsqlConnection con = new NpgsqlConnection("Host=localhost; Port=5432; Database=Test; Username=postgres; Password=xxxxxx"))
{
con.Open();
// setup
NpgsqlCommand cre = new NpgsqlCommand("CREATE TABLE order_table (id INTEGER NOT NULL, doc JSONB, PRIMARY KEY(id))", con);
cre.ExecuteNonQuery();
cre.Dispose();
NpgsqlCommand ins = new NpgsqlCommand("INSERT INTO order_table VALUES(@id,@doc::JSONB)", con);
ins.Parameters.Add("@id", NpgsqlTypes.NpgsqlDbType.Integer);
ins.Parameters.Add("@doc", NpgsqlTypes.NpgsqlDbType.Jsonb);
Order o1 = new Order { OrderId = 1, Customer = "A A", Items = new List<OrderLine>() };
o1.Items.Add(new OrderLine { ItemNo = 1, ItemName = "A good C# book", ItemPrice = 19.95m, Quantity = 1 });
ins.Parameters["@id"].Value = 1;
ins.Parameters["@doc"].Value = JsonConvert.SerializeObject(o1);
ins.ExecuteNonQuery();
Order o2 = new Order { OrderId = 2, Customer = "B B", Items = new List<OrderLine>() };
o2.Items.Add(new OrderLine { ItemNo = 1, ItemName = "ASUS MB", ItemPrice = 249.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 2, ItemName = "i5 CPU", ItemPrice = 299.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 3, ItemName = "4 GB kit", ItemPrice = 29.00m, Quantity = 4 });
ins.Parameters["@id"].Value = 2;
ins.Parameters["@doc"].Value = JsonConvert.SerializeObject(o2);
ins.ExecuteNonQuery();
ins.Dispose();
// query
NpgsqlCommand sel;
sel = new NpgsqlCommand("SELECT doc FROM order_table", con);
Dump("all", sel);
sel = new NpgsqlCommand("SELECT doc FROM order_table WHERE doc->>'OrderId' = @id", con);
sel.Parameters.Add("@id", NpgsqlTypes.NpgsqlDbType.Text);
sel.Parameters["@id"].Value = "2";
Dump("where id=2", sel);
sel = new NpgsqlCommand("SELECT doc FROM order_table WHERE doc->>'Customer' = @customer", con);
sel.Parameters.Add("@customer", NpgsqlTypes.NpgsqlDbType.Text);
sel.Parameters["@customer"].Value = "A A";
Dump("where customer='A A'", sel);
sel = new NpgsqlCommand("SELECT doc FROM order_table WHERE doc->>'Customer' LIKE @customer", con);
sel.Parameters.Add("@customer", NpgsqlTypes.NpgsqlDbType.Text);
sel.Parameters["@customer"].Value = "A%";
Dump("where customer like 'A%'", sel);
sel = new NpgsqlCommand("SELECT doc FROM order_table WHERE EXISTS (SELECT * FROM jsonb_array_elements(doc->'Items') items WHERE items->>'ItemName' = @name)", con);
sel.Parameters.Add("@name", NpgsqlTypes.NpgsqlDbType.Text);
sel.Parameters["@name"].Value = "i5 CPU";
Dump("where items.name='i5 CPU'", sel);
sel = new NpgsqlCommand("SELECT doc FROM order_table WHERE EXISTS (SELECT * FROM jsonb_array_elements(doc->'Items') items WHERE items->>'ItemName' LIKE @name)", con);
sel.Parameters.Add("@name", NpgsqlTypes.NpgsqlDbType.Text);
sel.Parameters["@name"].Value = "%C#%";
Dump("where items.name like '%C#%'", sel);
sel = new NpgsqlCommand("SELECT doc FROM order_table WHERE doc->>'OrderId' IN ('" + 1 + "','" + 2 + "')", con); // variable parameters is a problem
Dump("where id in (1,2)", sel);
}
}
}
}
We will use builtin json_encode/json_decode and a hardcoded mapper for JSON serialization/deserialization.
<?php
class OrderLine {
public $itemNo;
public $itemName;
public $itemPrice;
public $quantity;
public function __construct($itemNo = 0, $itemName = '', $itemPrice = '0.00', $quantity = 1) {
$this->itemNo = $itemNo;
$this->itemName = $itemName;
$this->itemPrice = $itemPrice;
$this->quantity = $quantity;
}
public function getPrice() {
return bcadd((string)$this->quantity, $this->itemPrice);
}
public function __toString() {
return sprintf('(%d,%s,%s,%d)', $this->itemNo, $this->itemName, $this->itemPrice, $this->quantity);
}
}
class Order {
public $orderId;
public $customer;
public $items;
public function __construct($orderId = 0, $customer = '') {
$this->orderId = $orderId;
$this->customer = $customer;
$this->items = array();
}
public function getPrice() {
$res = '0.00';
foreach($this->items as $ol) {
$res = bcadd($res, $ol->getPrice());
}
return $res;
}
public function __toString() {
return sprintf('(%d,%s,%s)', $this->orderId, $this->customer, implode(',', $this->items));
}
}
// hack to map read object into the real class
function map($inobj) {
$outobj = new Order();
$outobj->orderId = $inobj->orderId;
$outobj->customer = $inobj->customer;
foreach($inobj->items as $it) {
$outobj->items[] = new OrderLine($it->itemNo, $it->itemName, $it->itemPrice, $it->quantity);
}
return $outobj;
}
function dump($label, $con, $stmt, $param) {
echo "$label:\r\n";
$rs = pg_execute($con, $stmt, $param) or die(pg_last_error());
while($row = pg_fetch_array($rs, NULL, PGSQL_ASSOC)) {
$doc = $row['doc'];
echo "$doc\r\n";
$o = map(json_decode($doc));
echo "$o\r\n";
}
pg_query("DEALLOCATE $stmt");
}
// open
$con = pg_connect('host=localhost port=5432 dbname=Test user=postgres password=xxxxxx') or die(pg_last_error());
pg_query('CREATE TABLE order_table (id INTEGER NOT NULL, doc JSONB, PRIMARY KEY(id))') or die(pg_last_error());
// setup
pg_prepare($con, 'stmt_ins', 'INSERT INTO order_table VALUES($1,$2::JSONB)') or die(pg_last_error());
$o1 = new Order(1, 'A A');
$o1->items[] = new OrderLine(1, 'A good PHP book', '19.95', 1);
pg_execute($con, 'stmt_ins', array(1, json_encode($o1))) or die(pg_last_error());
$o2 = new Order(2, 'B B');
$o2->items[] = new OrderLine(1, 'ASUS MB', '249.00', 1);
$o2->items[] = new OrderLine(2, 'i5 CPU', '299.00', 1);
$o2->items[] = new OrderLine(3, '4 GB kit', '29.00', 4);
pg_execute($con, 'stmt_ins', array(2, json_encode($o2))) or die(pg_last_error());
pg_query("DEALLOCATE stmt_ins");
// query
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table") or die(pg_last_error());
dump("all", $con, 'stmt_sel', array());
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE doc->>'orderId' = $1") or die(pg_last_error());
dump("where id=2", $con, 'stmt_sel', array('2'));
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE doc->>'customer' = $1") or die(pg_last_error());
dump("where customer = 'A A'", $con, 'stmt_sel', array('A A'));
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE doc->>'customer' LIKE $1") or die(pg_last_error());
dump("where customer like 'A%'", $con, 'stmt_sel', array('A%'));
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE EXISTS (SELECT * FROM jsonb_array_elements(doc->'items') items WHERE items->>'itemName' = $1)") or die(pg_last_error());
dump("where items.name='i5 CPU'", $con, 'stmt_sel', array('i5 CPU'));
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE EXISTS (SELECT * FROM jsonb_array_elements(doc->'items') items WHERE items->>'itemName' LIKE $1)") or die(pg_last_error());
dump("where items.name like '%PHP%'", $con, 'stmt_sel', array('%PHP%'));
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE doc->>'orderId' IN ('" . 1 . "','" . 2 . "')") or die(pg_last_error()); // prepared statement and variable parameters is a problem
dump("where id in (1,2)", $con, 'stmt_sel', array());
// close
pg_close($con);
?>
After the success of PostgreSQL JSON support other databases has implemented similar features. Including MySQL.
We will use Google GSON for JSON serialization/deserialization.
package nosql.mysql;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public String getItemName() {
return itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public int getQuantity() {
return quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
@Override
public String toString() {
return String.format("(%d,%s,%s,%d)", itemNo, itemName, itemPrice, quantity);
}
}
package nosql.mysql;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public String getCustomer() {
return customer;
}
public List<OrderLine> getItems() {
return items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
@Override
public String toString() {
return String.format("(%d,%s,%s)", orderId, customer, items.stream().map(ol -> ol.toString()).collect(Collectors.joining(",")));
}
}
package nosql.mysql;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.google.gson.Gson;
public class Test2Rel {
private static Gson gson = new Gson();
private static void dump(String label, PreparedStatement sel) throws SQLException {
System.out.println(label + ":");
ResultSet rs = sel.executeQuery();
while(rs.next()) {
String doc = rs.getString(1);
System.out.println(doc);
Order o = gson.fromJson(doc, Order.class);
System.out.println(o);
}
}
public static void main(String[] args) throws SQLException {
// open
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Test", "root", "xxxxxx");
Statement cre = con.createStatement();
cre.executeUpdate("CREATE TABLE order_table (id INTEGER NOT NULL, doc JSON, PRIMARY KEY(id))");
cre.close();
// setup
PreparedStatement ins = con.prepareStatement("INSERT INTO order_table VALUES(?,?)");
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
ins.setInt(1, 1);
ins.setString(2, gson.toJson(o1));
ins.executeUpdate();
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
ins.setInt(1, 2);
ins.setString(2, gson.toJson(o2));
ins.executeUpdate();
// query
PreparedStatement sel;
sel = con.prepareStatement("SELECT doc FROM order_table");
dump("all", sel);
sel = con.prepareStatement("SELECT doc FROM order_table WHERE doc->>'$.orderId' = ?");
sel.setString(1, "2");
dump("where id=2", sel);
sel = con.prepareStatement("SELECT doc FROM order_table WHERE doc->>'$.customer' = ?");
sel.setString(1, "A A");
dump("where customer='A A'", sel);
sel = con.prepareStatement("SELECT doc FROM order_table WHERE doc->>'$.customer' LIKE ?");
sel.setString(1, "A%");
dump("where customer like 'A%'", sel);
sel = con.prepareStatement("SELECT doc FROM order_table WHERE JSON_CONTAINS(doc->>'$.items[*].itemName', JSON_QUOTE(?))");
sel.setString(1, "i5 CPU");
dump("where items.name='i5 CPU'", sel);
// **** I can't get it working **** // where items.name like '%Java%'
sel = con.prepareStatement("SELECT doc FROM order_table WHERE doc->>'$.orderId' IN ('" + 1 + "','" + 2 + "')"); // prepared statement and variable parameters is a problem
dump("where id in (1,2)", sel);
// close
con.close();
}
}
We will use NewtonSoft JSON.Net for JSON serialization/deserialization.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
namespace NoSQL.MySQL
{
public class OrderLine
{
public int ItemNo { get; set; }
public string ItemName { get; set; }
public decimal ItemPrice { get; set; }
public int Quantity { get; set; }
public decimal Price
{
get
{
return ItemPrice * Quantity;
}
}
public override string ToString()
{
return string.Format("({0},{1},{2},{3})", ItemNo, ItemName, ItemPrice, Quantity);
}
}
public class Order
{
public int OrderId { get; set; }
public string Customer { get; set; }
public IList<OrderLine> Items { get; set; }
public decimal Price
{
get
{
return Items.Sum(ol => ol.Price);
}
}
public override string ToString()
{
return string.Format("({0},{1},{2})", OrderId, Customer, string.Join(",", Items.Select(ol => ol.ToString()).ToArray()));
}
}
public class Program
{
private static void Dump(String label, MySqlCommand sel)
{
Console.WriteLine(label + ":");
MySqlDataReader rdr = sel.ExecuteReader();
while(rdr.Read())
{
string doc = (string)rdr[0];
Console.WriteLine(doc);
Order o = JsonConvert.DeserializeObject<Order>(doc);
Console.WriteLine(o);
}
rdr.Dispose();
sel.Dispose();
}
public static void Main(string[] args)
{
// open and close
using(MySqlConnection con = new MySqlConnection("Host=localhost; Database=Test; Username=root; Password=hemmeligt"))
{
con.Open();
// setup
MySqlCommand cre = new MySqlCommand("CREATE TABLE order_table (id INTEGER NOT NULL, doc JSON, PRIMARY KEY(id))", con);
cre.ExecuteNonQuery();
cre.Dispose();
MySqlCommand ins = new MySqlCommand("INSERT INTO order_table VALUES(@id,@doc)", con);
ins.Parameters.Add("@id", MySqlDbType.Int32);
ins.Parameters.Add("@doc",MySqlDbType.JSON);
Order o1 = new Order { OrderId = 1, Customer = "A A", Items = new List<OrderLine>() };
o1.Items.Add(new OrderLine { ItemNo = 1, ItemName = "A good C# book", ItemPrice = 19.95m, Quantity = 1 });
ins.Parameters["@id"].Value = 1;
ins.Parameters["@doc"].Value = JsonConvert.SerializeObject(o1);
ins.ExecuteNonQuery();
Order o2 = new Order { OrderId = 2, Customer = "B B", Items = new List<OrderLine>() };
o2.Items.Add(new OrderLine { ItemNo = 1, ItemName = "ASUS MB", ItemPrice = 249.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 2, ItemName = "i5 CPU", ItemPrice = 299.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 3, ItemName = "4 GB kit", ItemPrice = 29.00m, Quantity = 4 });
ins.Parameters["@id"].Value = 2;
ins.Parameters["@doc"].Value = JsonConvert.SerializeObject(o2);
ins.ExecuteNonQuery();
ins.Dispose();
// query
MySqlCommand sel;
sel = new MySqlCommand("SELECT doc FROM order_table", con);
Dump("all", sel);
sel = new MySqlCommand("SELECT doc FROM order_table WHERE doc->>'$.OrderId' = @id", con);
sel.Parameters.Add("@id", MySqlDbType.Text);
sel.Parameters["@id"].Value = "2";
Dump("where id=2", sel);
sel = new MySqlCommand("SELECT doc FROM order_table WHERE doc->>'$.Customer' = @customer", con);
sel.Parameters.Add("@customer", MySqlDbType.Text);
sel.Parameters["@customer"].Value = "A A";
Dump("where customer='A A'", sel);
sel = new MySqlCommand("SELECT doc FROM order_table WHERE doc->>'$.Customer' LIKE @customer", con);
sel.Parameters.Add("@customer", MySqlDbType.Text);
sel.Parameters["@customer"].Value = "A%";
Dump("where customer like 'A%'", sel);
sel = new MySqlCommand("SELECT doc FROM order_table WHERE JSON_CONTAINS(doc->>'$.Items[*].ItemName', JSON_QUOTE(@Name))", con);
sel.Parameters.Add("@name", MySqlDbType.Text);
sel.Parameters["@name"].Value = "i5 CPU";
Dump("where items.name='i5 CPU'", sel);
// **** I can't get it working **** // where items.name like '%C#%'
sel = new MySqlCommand("SELECT doc FROM order_table WHERE doc->>'$.OrderId' IN ('" + 1 + "','" + 2 + "')", con); // variable parameters is a problem
Dump("where id in (1,2)", sel);
}
}
}
}
We will use builtin json_encode/json_decode and a hardcoded mapper for JSON serialization/deserialization.
<?php
class OrderLine {
public $itemNo;
public $itemName;
public $itemPrice;
public $quantity;
public function __construct($itemNo = 0, $itemName = '', $itemPrice = '0.00', $quantity = 1) {
$this->itemNo = $itemNo;
$this->itemName = $itemName;
$this->itemPrice = $itemPrice;
$this->quantity = $quantity;
}
public function getPrice() {
return bcadd((string)$this->quantity, $this->itemPrice);
}
public function __toString() {
return sprintf('(%d,%s,%s,%d)', $this->itemNo, $this->itemName, $this->itemPrice, $this->quantity);
}
}
class Order {
public $orderId;
public $customer;
public $items;
public function __construct($orderId = 0, $customer = '') {
$this->orderId = $orderId;
$this->customer = $customer;
$this->items = array();
}
public function getPrice() {
$res = '0.00';
foreach($this->items as $ol) {
$res = bcadd($res, $ol->getPrice());
}
return $res;
}
public function __toString() {
return sprintf('(%d,%s,%s)', $this->orderId, $this->customer, implode(',', $this->items));
}
}
// hack to map read object into the real class
function map($inobj) {
$outobj = new Order();
$outobj->orderId = $inobj->orderId;
$outobj->customer = $inobj->customer;
foreach($inobj->items as $it) {
$outobj->items[] = new OrderLine($it->itemNo, $it->itemName, $it->itemPrice, $it->quantity);
}
return $outobj;
}
function dump($label, $con, $sel) {
echo "$label:\r\n";
mysqli_stmt_execute($sel) or die(mysqli_error(mysqli_error($con)));
$rs = mysqli_stmt_get_result($sel);
while($row = mysqli_fetch_array($rs, MYSQLI_ASSOC)) {
$doc = $row['doc'];
echo "$doc\r\n";
$o = map(json_decode($doc));
echo "$o\r\n";
}
mysqli_stmt_close($sel);
}
// open
$con = mysqli_connect('localhost', 'root', 'hemmeligt', 'Test') or die(mysqli_connect_error());
mysqli_query($con, 'CREATE TABLE order_table (id INTEGER NOT NULL, doc JSON, PRIMARY KEY(id))') or die(mysqli_error($con));
// setup
$ins = mysqli_prepare($con, 'INSERT INTO order_table VALUES(?,?)') or die(mysqli_error($con));
$o1 = new Order(1, 'A A');
$o1->items[] = new OrderLine(1, 'A good PHP book', '19.95', 1);
$o1id = 1;
$o1json = json_encode($o1);
mysqli_stmt_bind_param($ins, 'is', $o1id, $o1json);
mysqli_stmt_execute($ins) or die(mysqli_error(mysqli_error($con)));
$o2 = new Order(2, 'B B');
$o2->items[] = new OrderLine(1, 'ASUS MB', '249.00', 1);
$o2->items[] = new OrderLine(2, 'i5 CPU', '299.00', 1);
$o2->items[] = new OrderLine(3, '4 GB kit', '29.00', 4);
$o2id = 2;
$o2json = json_encode($o2);
mysqli_stmt_bind_param($ins, 'is', $o2id, $o2json);
mysqli_stmt_execute($ins) or die(mysqli_error(mysqli_error($con)));
mysqli_stmt_close($ins);
// query
$sel = mysqli_prepare($con, "SELECT doc FROM order_table") or die(mysqli_error($con));
dump("all", $con, $sel);
$sel = mysqli_prepare($con, "SELECT doc FROM order_table WHERE doc->>'$.orderId' = ?") or die(mysqli_error($con));
$id = 2;
mysqli_stmt_bind_param($sel, 'i', $id);
dump("where id=2", $con, $sel);
$sel = mysqli_prepare($con, "SELECT doc FROM order_table WHERE doc->>'$.customer' = ?") or die(mysqli_error($con));
$cust = 'A A';
mysqli_stmt_bind_param($sel, 's', $cust);
dump("where customer = 'A A'", $con, $sel);
$sel = mysqli_prepare($con, "SELECT doc FROM order_table WHERE doc->>'$.customer' LIKE ?") or die(mysqli_error($con));
$cust = 'A%';
mysqli_stmt_bind_param($sel, 's', $cust);
dump("where customer like 'A%'", $con, $sel);
$sel = mysqli_prepare($con, "SELECT doc FROM order_table WHERE JSON_CONTAINS(doc->>'$.items[*].itemName', JSON_QUOTE(?))") or die(mysqli_error($con));
$itm = 'i5 CPU';
mysqli_stmt_bind_param($sel, 's', $itm);
dump("where items.name='i5 CPU'", $con, $sel);
// **** I can't get it working **** // where items.name like '%PHP%'
$sel = mysqli_prepare($con, "SELECT doc FROM order_table WHERE doc->>'$.orderId' IN ('" . 1 . "','" . 2 . "')") or die(mysqli_error($con)); // prepared statement and variable parameters is a problem
dump("where id in (1,2)", $con, $sel);
// close
mysqli_close($con);
?>
After the success of PostgreSQL JSON support other databases has implemented similar features. Including IBM DB2.
We will use Google GSON for JSON serialization/deserialization.
package nosql.db2;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public String getItemName() {
return itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public int getQuantity() {
return quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
@Override
public String toString() {
return String.format("(%d,%s,%s,%d)", itemNo, itemName, itemPrice, quantity);
}
}
package nosql.db2;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public String getCustomer() {
return customer;
}
public List<OrderLine> getItems() {
return items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
@Override
public String toString() {
return String.format("(%d,%s,%s)", orderId, customer, items.stream().map(ol -> ol.toString()).collect(Collectors.joining(",")));
}
}
package nosql.db2;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.google.gson.Gson;
public class Test2Rel {
private static Gson gson = new Gson();
private static void dump(String label, PreparedStatement sel) throws SQLException {
System.out.println(label + ":");
ResultSet rs = sel.executeQuery();
while(rs.next()) {
String doc = rs.getString(1);
System.out.println(doc);
Order o = gson.fromJson(doc, Order.class);
System.out.println(o);
}
}
public static void main(String[] args) throws SQLException {
// open
Connection con = DriverManager.getConnection("jdbc:db2://localhost:50000/Test", "arne", "hemmeligt");
Statement cre = con.createStatement();
cre.executeUpdate("CREATE TABLE order_table (id INTEGER NOT NULL, doc BLOB, PRIMARY KEY(id))");
cre.close();
// setup
PreparedStatement ins = con.prepareStatement("INSERT INTO order_table VALUES(?,SYSTOOLS.JSON2BSON(?))");
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
ins.setInt(1, 1);
ins.setString(2, gson.toJson(o1));
ins.executeUpdate();
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
ins.setInt(1, 2);
ins.setString(2, gson.toJson(o2));
ins.executeUpdate();
// query
PreparedStatement sel;
sel = con.prepareStatement("SELECT SYSTOOLS.BSON2JSON(doc) FROM order_table");
dump("all", sel);
sel = con.prepareStatement("SELECT SYSTOOLS.BSON2JSON(doc) FROM order_table WHERE JSON_VAL(doc, 'orderId', 'i') = ?");
sel.setInt(1, 2);
dump("where id=2", sel);
sel = con.prepareStatement("SELECT SYSTOOLS.BSON2JSON(doc) FROM order_table WHERE JSON_VAL(doc, 'customer', 's:100') = ?");
sel.setString(1, "A A");
dump("where customer='A A'", sel);
sel = con.prepareStatement("SELECT SYSTOOLS.BSON2JSON(doc) FROM order_table WHERE JSON_VAL(doc, 'customer', 's:100') LIKE ?");
sel.setString(1, "A%");
dump("where customer like 'A%'", sel);
// **** I can't get it working **** // where items.name='i5 CPU'
// **** I can't get it working **** // where items.name like '%Java%'
sel = con.prepareStatement("SELECT SYSTOOLS.BSON2JSON(doc) FROM order_table WHERE JSON_VAL(doc, 'orderId', 'i') IN ('" + 1 + "','" + 2 + "')"); // prepared statement and variable parameters is a problem
dump("where id in (1,2)", sel);
// close
con.close();
}
}
We will use NewtonSoft JSON.Net for JSON serialization/deserialization.
using System;
using System.Collections.Generic;
using System.Linq;
using IBM.Data.DB2;
using Newtonsoft.Json;
namespace NoSQL.DB2
{
public class OrderLine
{
public int ItemNo { get; set; }
public string ItemName { get; set; }
public decimal ItemPrice { get; set; }
public int Quantity { get; set; }
public decimal Price
{
get
{
return ItemPrice * Quantity;
}
}
public override string ToString()
{
return string.Format("({0},{1},{2},{3})", ItemNo, ItemName, ItemPrice, Quantity);
}
}
public class Order
{
public int OrderId { get; set; }
public string Customer { get; set; }
public IList<OrderLine> Items { get; set; }
public decimal Price
{
get
{
return Items.Sum(ol => ol.Price);
}
}
public override string ToString()
{
return string.Format("({0},{1},{2})", OrderId, Customer, string.Join(",", Items.Select(ol => ol.ToString()).ToArray()));
}
}
public class Program
{
private static void Dump(String label, DB2Command sel)
{
Console.WriteLine(label + ":");
DB2DataReader rdr = sel.ExecuteReader();
while(rdr.Read())
{
string doc = (string)rdr[0];
//Console.WriteLine(doc);
Order o = JsonConvert.DeserializeObject<Order>(doc);
Console.WriteLine(o);
}
rdr.Dispose();
sel.Dispose();
}
public static void Main(string[] args)
{
// open and close
using(DB2Connection con = new DB2Connection("DATABASE=Test"))
{
con.Open();
// setup
DB2Command cre = new DB2Command("CREATE TABLE order_table (id INTEGER NOT NULL, doc BLOB, PRIMARY KEY(id))", con);
cre.ExecuteNonQuery();
cre.Dispose();
DB2Command ins = new DB2Command("INSERT INTO order_table VALUES(@id,SYSTOOLS.JSON2BSON(@doc))", con);
ins.Parameters.Add("@id", DB2Type.Integer);
ins.Parameters.Add("@doc",DB2Type.Blob);
Order o1 = new Order { OrderId = 1, Customer = "A A", Items = new List<OrderLine>() };
o1.Items.Add(new OrderLine { ItemNo = 1, ItemName = "A good C# book", ItemPrice = 19.95m, Quantity = 1 });
ins.Parameters["@id"].Value = 1;
ins.Parameters["@doc"].Value = JsonConvert.SerializeObject(o1);
ins.ExecuteNonQuery();
Order o2 = new Order { OrderId = 2, Customer = "B B", Items = new List<OrderLine>() };
o2.Items.Add(new OrderLine { ItemNo = 1, ItemName = "ASUS MB", ItemPrice = 249.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 2, ItemName = "i5 CPU", ItemPrice = 299.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 3, ItemName = "4 GB kit", ItemPrice = 29.00m, Quantity = 4 });
ins.Parameters["@id"].Value = 2;
ins.Parameters["@doc"].Value = JsonConvert.SerializeObject(o2);
ins.ExecuteNonQuery();
ins.Dispose();
// query
DB2Command sel;
sel = new DB2Command("SELECT SYSTOOLS.BSON2JSON(doc) FROM order_table", con);
Dump("all", sel);
sel = new DB2Command("SELECT SYSTOOLS.BSON2JSON(doc) FROM order_table WHERE JSON_VAL(doc, 'OrderId', 'i') = @id", con);
sel.Parameters.Add("@id", DB2Type.Integer);
sel.Parameters["@id"].Value = "2";
Dump("where id=2", sel);
sel = new DB2Command("SELECT SYSTOOLS.BSON2JSON(doc) FROM order_table WHERE JSON_VAL(doc, 'Customer', 's:100') = @customer", con);
sel.Parameters.Add("@customer", DB2Type.VarChar);
sel.Parameters["@customer"].Value = "A A";
Dump("where customer='A A'", sel);
sel = new DB2Command("SELECT SYSTOOLS.BSON2JSON(doc) FROM order_table WHERE JSON_VAL(doc, 'Customer', 's:100') LIKE @customer", con);
sel.Parameters.Add("@customer", DB2Type.VarChar);
sel.Parameters["@customer"].Value = "A%";
Dump("where customer like 'A%'", sel);
// **** I can't get it working **** // where items.name='i5 CPU'
// **** I can't get it working **** // where items.name like '%C#%'
sel = new DB2Command("SELECT SYSTOOLS.BSON2JSON(doc) FROM order_table WHERE JSON_VAL(doc, 'OrderId', 'i') IN ('" + 1 + "','" + 2 + "')", con); // variable parameters is a problem
Dump("where id in (1,2)", sel);
}
}
}
}
An object repository (or object store) is just a document store with builtin serialization and deserialization.
In fact many document stores comes with support for object repository.
Illustration:
package nosql.mongodb.object;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine() {
this(0, "", BigDecimal.ZERO, 0);
}
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public void setItemNo(int itemNo) {
this.itemNo = itemNo;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public void setItemPrice(BigDecimal itemPrice) {
this.itemPrice = itemPrice;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
@Override
public String toString() {
return String.format("(%d,%s,%s,%d)", itemNo, itemName, itemPrice, quantity);
}
}
package nosql.mongodb.object;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order() {
this(0, "");
}
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getCustomer() {
return customer;
}
public void setCustomer(String customer) {
this.customer = customer;
}
public List<OrderLine> getItems() {
return items;
}
public void setItems(List<OrderLine> items) {
this.items = items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
@Override
public String toString() {
return String.format("(%d,%s,%s)", orderId, customer, items.stream().map(ol -> ol.toString()).collect(Collectors.joining(",")));
}
}
package nosql.mongodb.object;
import static com.mongodb.client.model.Filters.eq;
import static com.mongodb.client.model.Filters.gte;
import static com.mongodb.client.model.Filters.in;
import static com.mongodb.client.model.Filters.regex;
import java.math.BigDecimal;
import org.bson.codecs.configuration.CodecRegistries;
import org.bson.codecs.pojo.PojoCodecProvider;
import org.bson.conversions.Bson;
import com.mongodb.MongoClient;
import com.mongodb.MongoClientSettings;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
public class Test4 {
private static void dump(MongoCollection<Order> col, Bson criteria) {
System.out.println("dumping " + criteria);
for(Order o : col.find(criteria)) {
System.out.println(o);
}
}
public static void main(String[] args) {
// open
MongoClient client = new MongoClient("localhost", 27017);
MongoDatabase db = client.getDatabase("TestDB").withCodecRegistry(CodecRegistries.fromRegistries(MongoClientSettings.getDefaultCodecRegistry(),
CodecRegistries.fromProviders(PojoCodecProvider.builder().automatic(true).build())));
MongoCollection<Order> col = db.getCollection("java_order_object", Order.class);
// setup
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
col.insertOne(o1);
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
col.insertOne(o2);
// query
Bson d;
d = gte("orderId", 0); // all (simulated with id >= 0)
dump(col, d);
d = eq("orderId", 2); // where id=2
dump(col, d);
d = eq("customer", "A A"); // where customer='A A'
dump(col, d);
d = regex("customer", "A.*");// where customer like 'A%'
dump(col, d);
d = eq("items.itemName", "i5 CPU"); // where items.name='i5 CPU'
dump(col, d);
d = regex("items.itemName", ".*Java.*"); // where items.name like '%Java%'
dump(col, d);
d = in("orderId", 1, 2); // where id in (1,2)
dump(col, d);
// close
client.close();
}
}
Besides the builtin mapper there are also a third party library MongoJack that uses the well-known Jackson library to serialize and deserialize.
package nosql.mongodb.objectalt;
import java.math.BigDecimal;
import com.fasterxml.jackson.annotation.JsonIgnore;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine() {
this(0, "", BigDecimal.ZERO, 0);
}
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public void setItemNo(int itemNo) {
this.itemNo = itemNo;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public void setItemPrice(BigDecimal itemPrice) {
this.itemPrice = itemPrice;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
@JsonIgnore
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
@Override
public String toString() {
return String.format("(%d,%s,%s,%d)", itemNo, itemName, itemPrice, quantity);
}
}
package nosql.mongodb.objectalt;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import org.mongojack.Id;
import com.fasterxml.jackson.annotation.JsonIgnore;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order() {
this(0, "");
}
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
@Id
public String getId() {
return Integer.toString(orderId);
}
@Id
public void setId(String id) {
orderId = Integer.parseInt(id);
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getCustomer() {
return customer;
}
public void setCustomer(String customer) {
this.customer = customer;
}
public List<OrderLine> getItems() {
return items;
}
public void setItems(List<OrderLine> items) {
this.items = items;
}
@JsonIgnore
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
@Override
public String toString() {
return String.format("(%d,%s,%s)", orderId, customer, items.stream().map(ol -> ol.toString()).collect(Collectors.joining(",")));
}
}
Note that the getId() and setId() methods were only added because MongoJack prefer id to be String.
package nosql.mongodb.objectalt;
import static com.mongodb.client.model.Filters.eq;
import static com.mongodb.client.model.Filters.gte;
import static com.mongodb.client.model.Filters.in;
import static com.mongodb.client.model.Filters.regex;
import java.math.BigDecimal;
import org.bson.conversions.Bson;
import com.mongodb.client.MongoClient;
import com.mongodb.client.MongoClients;
import com.mongodb.client.MongoCollection;
import org.mongojack.JacksonMongoCollection;
public class Test4 {
private static void dump(MongoCollection<Order> col, Bson criteria) {
System.out.println("dumping " + criteria);
for(Order o : col.find(criteria)) {
System.out.println(o);
}
}
public static void main(String[] args) {
// open
MongoClient client = MongoClients.create("mongodb://localhost:27017/");
MongoCollection<Order> col = JacksonMongoCollection.builder().<Order>build(client, "TestDB", "java_order_object", Order.class);
// setup
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
col.insertOne(o1);
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
col.insertOne(o2);
// query
Bson d;
d = gte("orderId", 0); // all (simulated with id >= 0)
dump(col, d);
d = eq("orderId", 2); // where id=2
dump(col, d);
d = eq("customer", "A A"); // where customer='A A'
dump(col, d);
d = regex("customer", "A.*");// where customer like 'A%'
dump(col, d);
d = eq("items.itemName", "i5 CPU"); // where items.name='i5 CPU'
dump(col, d);
d = regex("items.itemName", ".*Java.*"); // where items.name like '%Java%'
dump(col, d);
d = in("orderId", 1, 2); // where id in (1,2)
dump(col, d);
// close
client.close();
}
}
JPA is a standard ORM API for Java. For normal relational database usage see here.
Hibernate OGM has a JPA provider for MongoDB, so it is possible to use standard JPA with MongoDB.
And then the difference between MongoDB and a relational database totally disappears.
package nosql.mongodb.object;
import java.math.BigDecimal;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Transient;
@Entity
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
private Order order;
public OrderLine() {
this(0, "", new BigDecimal("0.00"), 0);
}
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
@Id
public int getItemNo() {
return itemNo;
}
public void setItemNo(int itemNo) {
this.itemNo = itemNo;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public void setItemPrice(BigDecimal itemPrice) {
this.itemPrice = itemPrice;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
@ManyToOne
public Order getOrder() {
return order;
}
public void setOrder(Order order) {
this.order = order;
}
@Transient
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
@Override
public String toString() {
return String.format("(%d,%s,%s,%d)", itemNo, itemName, itemPrice, quantity);
}
}
package nosql.mongodb.object;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Transient;
@Entity
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order() {
this(0, "");
}
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
@Id
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getCustomer() {
return customer;
}
public void setCustomer(String customer) {
this.customer = customer;
}
@OneToMany(mappedBy="order", cascade=CascadeType.ALL)
public List<OrderLine> getItems() {
return items;
}
public void setItems(List<OrderLine> items) {
this.items = items;
}
@Transient
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
public void add(OrderLine ol) {
getItems().add(ol);
ol.setOrder(this);
}
@Override
public String toString() {
return String.format("(%d,%s,%s)", orderId, customer, items.stream().map(ol -> ol.toString()).collect(Collectors.joining(",")));
}
}
package nosql.mongodb.object;
import java.math.BigDecimal;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
public class Test4 {
private static <T> void dump(String label, TypedQuery<T> q) {
System.out.println(label);
for(T o : q.getResultList()) {
System.out.println(o);
}
}
public static void main(String[] args) {
// open
EntityManagerFactory emf = Persistence.createEntityManagerFactory("TestRepo");
EntityManager em = emf.createEntityManager();
// setup
em.getTransaction().begin();
Order o1 = new Order(1, "A A");
o1.add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
em.persist(o1);
Order o2 = new Order(2, "B B");
o2.add(new OrderLine(2, "ASUS MB", new BigDecimal("249.00"), 1));
o2.add(new OrderLine(3, "i5 CPU", new BigDecimal("299.00"), 1));
o2.add(new OrderLine(4, "4 GB kit", new BigDecimal("29.00"), 4));
em.persist(o2);
em.getTransaction().commit();
// query
TypedQuery<Order> q;
q = em.createQuery("SELECT o FROM Order AS o", Order.class);
dump("all", q);
q = em.createQuery("SELECT o FROM Order AS o WHERE orderId = 2", Order.class);
dump("where id=2", q);
q = em.createQuery("SELECT o FROM Order AS o WHERE customer = :pcustomer", Order.class);
q.setParameter("pcustomer", "A A");
dump("where customer='A A'", q);
q = em.createQuery("SELECT o FROM Order AS o WHERE customer LIKE :pcustomer", Order.class);
q.setParameter("pcustomer", "A" + "%");
dump("where customer like 'A%'", q);
// NOT SUPPORTED: where items.name='i5 CPU'
// NOT SUPPORTED: where items.name like '%Java%'
q = em.createQuery("SELECT o FROM Order AS o WHERE orderId IN (1,2)", Order.class);
dump("where id in (1,2)", q);
// close
em.close();
}
}
persistence.xml:
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="TestRepo">
<provider>org.hibernate.ogm.jpa.HibernateOgmPersistence</provider>
<properties>
<property name="hibernate.ogm.datastore.provider" value="mongodb"/>
<property name="hibernate.ogm.datastore.host" value="localhost:27017"/>
<property name="hibernate.ogm.datastore.database" value="TestRepo"/>
<property name="hibernate.ogm.datastore.create_database" value="true"/>
</properties>
</persistence-unit>
</persistence>
using System;
using System.Collections.Generic;
using System.Linq;
using MongoDB.Driver;
using MongoDB.Bson;
using MongoDB.Driver.Linq;
namespace NoSQL.MongoDB.Object
{
public class OrderLine
{
public int ItemNo { get; set; }
public string ItemName { get; set; }
public decimal ItemPrice { get; set; }
public int Quantity { get; set; }
public decimal Price
{
get
{
return ItemPrice * Quantity;
}
}
public override string ToString()
{
return string.Format("({0},{1},{2},{3})", ItemNo, ItemName, ItemPrice, Quantity);
}
}
public class Order
{
public ObjectId Id { get; set; } // required by MongoDB
public int OrderId { get; set; }
public string Customer { get; set; }
public IList<OrderLine> Items { get; set; }
public decimal Price
{
get
{
return Items.Sum(ol => ol.Price);
}
}
public override string ToString()
{
return string.Format("({0},{1},{2})", OrderId, Customer, string.Join(",", Items.Select(ol => ol.ToString()).ToArray()));
}
}
public class Program
{
private static void Dump(string label, IQueryable<Order> q)
{
Console.WriteLine(label);
foreach (Order o in q)
{
Console.WriteLine(o);
}
}
public static void Main(string[] args)
{
// open
MongoClient client = new MongoClient("mongodb://localhost:27017");
IMongoDatabase db = client.GetDatabase("TestDB");
IMongoCollection<Order> col = db.GetCollection<Order>("dn_order_object");
// setup
Order o1 = new Order { OrderId = 1, Customer = "A A", Items = new List<OrderLine>() };
o1.Items.Add(new OrderLine { ItemNo = 1, ItemName = "A good C# book", ItemPrice = 19.95m, Quantity = 1 });
col.InsertOne(o1);
Order o2 = new Order { OrderId = 2, Customer = "B B", Items = new List<OrderLine>() };
o2.Items.Add(new OrderLine { ItemNo = 1, ItemName = "ASUS MB", ItemPrice = 249.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 2, ItemName = "i5 CPU", ItemPrice = 299.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 3, ItemName = "4 GB kit", ItemPrice = 29.00m, Quantity = 4 });
col.InsertOne(o2);
// query
IQueryable<Order> q;
q = col.AsQueryable<Order>();
Dump("all", q);
q = col.AsQueryable().Where(o => o.OrderId == 2);
Dump("where id=2", q);
q = col.AsQueryable().Where(o => o.Customer == "A A");
Dump("where customer='A A'", q);
q = col.AsQueryable().Where(o => o.Customer.StartsWith("A A"));
Dump("where customer like 'A%'", q);
q = col.AsQueryable().Where(o => o.Items.Where(ol => ol.ItemName == "i5 CPU").Any());
Dump("where items.name='i5 CPU'", q);
q = col.AsQueryable().Where(o => o.Items.Where(ol => ol.ItemName.Contains("C#")).Any());
Dump("where items.name like '%C#%'", q);
int[] a = { 1, 2 };
q = col.AsQueryable().Where(o => a.Contains(o.OrderId));
Dump("where d in (1,2)", q);
}
}
}
Nitrite has object repository support.
Example:
package nosql.nitrite.object;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine() {
this(0, "", new BigDecimal("0.00"), 1);
}
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public String getItemName() {
return itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public int getQuantity() {
return quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
@Override
public String toString() {
return String.format("(%d,%s,%s,%d)", itemNo, itemName, itemPrice, quantity);
}
}
package nosql.nitrite.object;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order() {
this(0, "");
}
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public String getCustomer() {
return customer;
}
public List<OrderLine> getItems() {
return items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
@Override
public String toString() {
return String.format("(%d,%s,%s)", orderId, customer, items.stream().map(ol -> ol.toString()).collect(Collectors.joining(",")));
}
}
package nosql.nitrite.object;
import java.math.BigDecimal;
import org.dizitart.no2.Nitrite;
import org.dizitart.no2.objects.ObjectFilter;
import org.dizitart.no2.objects.ObjectRepository;
import static org.dizitart.no2.objects.filters.ObjectFilters.*;
public class Test4 {
private static <T> void dump(ObjectRepository<T> repo, String label, ObjectFilter filter) {
System.out.println(label);
for(T o : repo.find(filter)) {
System.out.println(o);
}
}
public static void main(String[] args) {
// open
Nitrite db = Nitrite.builder().filePath("/work/N/TestRepo.no2").openOrCreate();
ObjectRepository<Order> repo = db.getRepository(Order.class);
// setup
Order o1 = new Order(1, "A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
repo.insert(o1);
Order o2 = new Order(2, "B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
repo.insert(o2);
// query
ObjectFilter filter;
filter = ALL;
dump(repo, "all", filter);
filter = eq("orderId", 2);
dump(repo, "where id=2", filter);
filter = eq("customer", "A A");
dump(repo, "where customer='A A'", filter);
filter = regex("customer", "A.*");
dump(repo, "where customer like 'A%'", filter);
filter = elemMatch("items", eq("itemName", "i5 CPU"));
dump(repo, "where items.name='i5 CPU'", filter);
filter = elemMatch("items", regex("itemName", ".*Java.*"));
dump(repo, "where items.name like '%Java%'", filter);
filter = in("orderId", 1, 2);
dump(repo, "where id in (1,2)", filter);
// close
repo.close();
db.close();
}
}
LiteDB has great object repository support including LINQ support.
It seems to me that the project is prioritizing object repository over plain document store.
Example:
using System;
using System.Collections.Generic;
using System.Linq;
using LiteDB;
namespace NoSQL.LiteDB.Object
{
public class OrderLine
{
public int ItemNo { get; set; }
public string ItemName { get; set; }
public decimal ItemPrice { get; set; }
public int Quantity { get; set; }
public decimal Price
{
get
{
return ItemPrice * Quantity;
}
}
public override string ToString()
{
return string.Format("({0},{1},{2},{3})", ItemNo, ItemName, ItemPrice, Quantity);
}
}
public class Order
{
public int OrderId { get; set; }
public string Customer { get; set; }
public IList<OrderLine> Items { get; set; }
public decimal Price
{
get
{
return Items.Sum(ol => ol.Price);
}
}
public override string ToString()
{
return string.Format("({0},{1},{2})", OrderId, Customer, string.Join(",", Items.Select(ol => ol.ToString()).ToArray()));
}
}
public class Program
{
public static void Dump<T>(string lbl, ILiteQueryable<T> q)
{
Console.WriteLine(lbl);
foreach(T o in q.ToList())
{
Console.WriteLine(o);
}
}
public static void Main(string[] args)
{
// open
ILiteRepository db = new LiteRepository(@"C:\Work\L\TestRepo.LiD");
// setup
Order o1 = new Order { OrderId = 1, Customer = "A A", Items = new List<OrderLine>() };
o1.Items.Add(new OrderLine { ItemNo = 1, ItemName = "A good C# book", ItemPrice = 19.95m, Quantity = 1 });
db.Insert(o1);
Order o2 = new Order { OrderId = 2, Customer = "B B", Items = new List<OrderLine>() };
o2.Items.Add(new OrderLine { ItemNo = 1, ItemName = "ASUS MB", ItemPrice = 249.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 2, ItemName = "i5 CPU", ItemPrice = 299.00m, Quantity = 1 });
o2.Items.Add(new OrderLine { ItemNo = 3, ItemName = "4 GB kit", ItemPrice = 29.00m, Quantity = 4 });
db.Insert(o2);
// query
ILiteQueryable<Order> q;
q = db.Query<Order>();
Dump("all", q);
q = db.Query<Order>().Where(o => o.OrderId == 2);
Dump("where id=2", q);
q = db.Query<Order>().Where(o => o.Customer == "A A");
Dump("where customer='A A'", q);
q = db.Query<Order>().Where(o => o.Customer.StartsWith("A"));
Dump("where customer LIKE 'A%'", q);
q = db.Query<Order>().Where(o => o.Items.Where(ol => ol.ItemName == "i5 CPU").Any());
Dump("where items.name='i5 CPU'", q);
q = db.Query<Order>().Where(o => o.Items.Where(ol => ol.ItemName.Contains("C#")).Any());
Dump("where items.name like '%C#%'", q);
List<int> a = new List<int> { 1, 2 };
q = db.Query<Order>().Where(o => a.Contains(o.OrderId));
Dump("where id IN (1,2)", q);
// close
db.Dispose();
}
}
}
OrientDB has object repository support.
Example:
Java API comes with OrientDB.
package nosql.orientdb.object;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine() {
this(0, "", BigDecimal.ZERO, 0);
}
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public void setItemNo(int itemNo) {
this.itemNo = itemNo;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public void setItemPrice(BigDecimal itemPrice) {
this.itemPrice = itemPrice;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
@Override
public String toString() {
return String.format("(%d,%s,%s,%d)", itemNo, itemName, itemPrice, quantity);
}
}
package nosql.orientdb.object;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order() {
this(0, "");
}
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getCustomer() {
return customer;
}
public void setCustomer(String customer) {
this.customer = customer;
}
public List<OrderLine> getItems() {
return items;
}
public void setItems(List<OrderLine> items) {
this.items = items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
@Override
public String toString() {
return String.format("(%d,%s,%s)", orderId, customer, items.stream().map(ol -> ol.toString()).collect(Collectors.joining(",")));
}
}
package nosql.orientdb.object;
import java.math.BigDecimal;
import com.orientechnologies.orient.core.db.object.ODatabaseObject;
import com.orientechnologies.orient.core.record.impl.ODocument;
import com.orientechnologies.orient.core.sql.executor.OResultSet;
import com.orientechnologies.orient.object.db.OrientDBObject;
public class Test4 {
private static void dump(ODatabaseObject db, String criteria) {
System.out.println("dumping " + criteria);
OResultSet rs = db.query("SELECT FROM Order " + criteria);
while(rs.hasNext()) {
ODocument d = (ODocument)rs.next().getRecord().get();
Order o = (Order)db.getUserObjectByRecord(d, "*:-1");
db.detachAll(o); // I am not sure why this is necessary
System.out.println(o);
}
}
public static void main(String[] args) {
// open
OrientDBObject client = new OrientDBObject("remote:localhost", null);
ODatabaseObject db = client.open("test4","admin", "hemmeligt");
db.getEntityManager().registerEntityClass(Order.class);
db.getEntityManager().registerEntityClass(OrderLine.class);
// setup
Order o1 = db.newInstance(Order.class);
o1.setOrderId(1);
o1.setCustomer("A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
db.save(o1);
Order o2 = db.newInstance(Order.class);
o2.setOrderId(2);
o2.setCustomer("B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
db.save(o2);
// query
dump(db, ""); // all
dump(db, "WHERE orderId = 2"); // where id=2
dump(db, "WHERE customer = 'A A'"); // where customer='A A'
dump(db, "WHERE customer LIKE 'A%'"); // where customer like 'A%'
dump(db, "WHERE items.itemName CONTAINS 'i5 CPU'"); // where items.name='i5 CPU'
// **** I can't get it working **** // where items.name like '%Java%'
dump(db, "WHERE orderId IN [1,2]"); // where id in (1,2)
// **** I can't get it working **** // where exists items.note
// close
db.close();
client.close();
}
}
Java API comes with OrientDB.
package nosql.orientdb.object;
import java.math.BigDecimal;
public class OrderLine {
private int itemNo;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine() {
this(0, "", BigDecimal.ZERO, 0);
}
public OrderLine(int itemsNo, String itemName, BigDecimal itemPrice, int quantity) {
this.itemNo = itemsNo;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getItemNo() {
return itemNo;
}
public void setItemNo(int itemNo) {
this.itemNo = itemNo;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public void setItemPrice(BigDecimal itemPrice) {
this.itemPrice = itemPrice;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
public BigDecimal getPrice() {
return itemPrice.multiply(new BigDecimal(quantity));
}
@Override
public String toString() {
return String.format("(%d,%s,%s,%d)", itemNo, itemName, itemPrice, quantity);
}
}
package nosql.orientdb.object;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> items;
public Order() {
this(0, "");
}
public Order(int orderId, String customer) {
this(orderId, customer, new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> items) {
this.orderId = orderId;
this.customer = customer;
this.items = items;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getCustomer() {
return customer;
}
public void setCustomer(String customer) {
this.customer = customer;
}
public List<OrderLine> getItems() {
return items;
}
public void setItems(List<OrderLine> items) {
this.items = items;
}
public BigDecimal getPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : items) {
res = res.add(ol.getPrice());
}
return res;
}
@Override
public String toString() {
return String.format("(%d,%s,%s)", orderId, customer, items.stream().map(ol -> ol.toString()).collect(Collectors.joining(",")));
}
}
package nosql.orientdb.object;
import java.math.BigDecimal;
import java.util.List;
import com.orientechnologies.orient.core.sql.query.OSQLSynchQuery;
import com.orientechnologies.orient.object.db.OObjectDatabaseTx;
public class Test4 {
private static void dump(OObjectDatabaseTx db, String criteria) {
System.out.println("dumping " + criteria);
List<Order> rs = db.query(new OSQLSynchQuery<Order>("SELECT FROM Order " + criteria));
for(Order o : rs) {
db.detachAll(o, false); // I am not sure why this is necessary
System.out.println(o);
}
}
public static void main(String[] args) {
// open
OObjectDatabaseTx db = new OObjectDatabaseTx("remote:192.168.60.129/test4");
db.open("admin", "hemmeligt");
db.getEntityManager().registerEntityClass(Order.class);
db.getEntityManager().registerEntityClass(OrderLine.class);
// setup
Order o1 = db.newInstance(Order.class);
o1.setOrderId(1);
o1.setCustomer("A A");
o1.getItems().add(new OrderLine(1, "A good Java book", new BigDecimal("19.95"), 1));
db.save(o1);
Order o2 = db.newInstance(Order.class);
o2.setOrderId(2);
o2.setCustomer("B B");
o2.getItems().add(new OrderLine(1, "ASUS MB", new BigDecimal("249.00"), 1));
o2.getItems().add(new OrderLine(2, "i5 CPU", new BigDecimal("299.00"), 1));
o2.getItems().add(new OrderLine(3, "4 GB kit", new BigDecimal("29.00"), 4));
db.save(o2);
// query
dump(db, ""); // all
dump(db, "WHERE orderId = 2"); // where id=2
dump(db, "WHERE customer = 'A A'"); // where customer='A A'
dump(db, "WHERE customer LIKE 'A%'"); // where customer like 'A%'
dump(db, "WHERE items.itemName CONTAINS 'i5 CPU'"); // where items.name='i5 CPU'
// **** I can't get it working **** // where items.name like '%Java%'
dump(db, "WHERE orderId IN [1,2]"); // where id in (1,2)
// **** I can't get it working **** // where exists items.note
// close
db.close();
}
}
JDBC driver comes with OrientDB.
This example is query only.
package nosql.orientdb.relational;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
public class TestJDBC {
private static void dump(Statement sel, String criteria) throws SQLException {
System.out.println("dumping " + criteria);
ResultSet rs = sel.executeQuery("SELECT orderId,customer,items.itemNo,items.itemName,items.itemPrice,items.quantity FROM Order " + criteria);
while(rs.next()) {
int orderId = rs.getInt(1);
String customer = rs.getString(2);
@SuppressWarnings("unchecked")
List<Integer> itemNo = (List<Integer>)rs.getObject(3);
@SuppressWarnings("unchecked")
List<String> itemName = (List<String>)rs.getObject(4);
@SuppressWarnings("unchecked")
List<Double> price = (List<Double>)rs.getObject(5);
@SuppressWarnings("unchecked")
List<Integer> quantity = (List<Integer>)rs.getObject(6);
System.out.printf("(%d,%s) ->\n", orderId, customer);
for(int i = 0; i < itemNo.size(); i++) {
System.out.printf(" (%d,%s,%.2f,%d)\n", itemNo.get(i), itemName.get(i), price.get(i), quantity.get(i));
}
}
rs.close();
}
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("com.orientechnologies.orient.jdbc.OrientJdbcDriver");
Connection con = DriverManager.getConnection("jdbc:orient:remote:192.168.60.129/test4", "admin", "hemmeligt");
Statement sel = con.createStatement();
dump(sel, ""); // all
dump(sel, "WHERE orderId = 2"); // where id=2
dump(sel, "WHERE customer = 'A A'"); // where customer='A A'
dump(sel, "WHERE customer LIKE 'A%'"); // where customer like 'A%'
dump(sel, "WHERE items.itemName CONTAINS 'i5 CPU'"); // where items.name='i5 CPU'
// **** I can't get it working **** // where items.name like '%Java%'
dump(sel, "WHERE orderId IN [1,2]"); // where id in (1,2)
// **** I can't get it working **** // where exists items.note
sel.close();
con.close();
}
}
Version | Date | Description |
---|---|---|
1.0 | March 17th 2020 | Initial version |
1.1 | March 24th 2020 | Add MongODB alternate query variants, async API and object repository section |
1.2 | April 10th 2020 | Add PostgreSQL section |
1.3 | July 1st 2022 | Add MySQL section |
1.4 | July 23rd 2022 | Add MongoDB JNoSQL API examples |
1.5 | July 24th 2022 | Add DB2 section |
1.6 | January 22nd 2022 | Add OrientDB section |
1.7 | July 6th 2024 | Add MongoJack example |
See list of all articles here
Please send comments to Arne Vajhøj
In many cases I think the answer is no. Relational databases comes with so many benefits: rich query language (SQL), standardized database API's, higher level data access technologies (ORM), admin tools, reporting tools etc..
And there are plenty of relational databases to choose from.
There are cases where a document store NoSQL database may make sense: