Category: orientdb

OrientDB – Insert Record ”; Previous Next OrientDB is a NoSQL database that can store the documents and graph-oriented data. NoSQL database does not contain any table, so how can you insert data as a record. Here you can see the table data in the form of class, property, vertex, and edge meaning classes are like tables, and properties are like files in the tables. We can define all these entities using schema in OrientDB. Property data can be inserted into a class. Insert command creates a new record in the database schema. Records can be schema-less or follow some specified rules. The following statement is the basic syntax of the Insert Record command. INSERT INTO [class:]<class>|cluster:<cluster>|index:<index> [(<field>[,]*) VALUES (<expression>[,]*)[,]*]| [SET <field> = <expression>|<sub-command>[,]*]| [CONTENT {<JSON>}] [RETURN <expression>] [FROM <query>] Following are the details about the options in the above syntax. SET − Defines each field along with the value. CONTENT − Defines JSON data to set field values. This is optional. RETURN − Defines the expression to return instead of number of records inserted. The most common use cases are − @rid − Returns the Record ID of the new record. @this − Returns the entire new record. FROM − Where you want to insert the record or a result set. Example Let us consider a Customer table with the following fields and types. Sr.No. Field Name Type 1 Id Integer 2 Name String 3 Age Integer You can create the Schema (table) by executing the following commands. CREATE DATABASE PLOCAL:/opt/orientdb/databases/sales CREATE CLASS Customer CREATE PROPERTY Customer.id integer CREATE PROPERTY Customer.name String CREATE PROPERTY Customer.age integer After executing all the commands, you will get the table name Customer with id, name, and age fields. You can check the table by executing select query into the Customer table. OrientDB provides different ways to insert a record. Consider the following Customer table containing the sample records. Sr.No. Name Age 1 Satish 25 2 Krishna 26 3 Kiran 29 4 Javeed 21 5 Raja 29 The following command is to insert the first record into the Customer table. INSERT INTO Customer (id, name, age) VALUES (01,”satish”, 25) If the above command is successfully executed, you will get the following output. Inserted record ”Customer#11:0{id:1,name:satish,age:25} v1” in 0.069000 sec(s). The following command is to insert the second record into the Customer table. INSERT INTO Customer SET id = 02, name = ”krishna”, age = 26 If the above command is successfully executed, you will get the following output. Inserted record ”Customer#11:1{id:2,age:26,name:krishna} v1” in 0.005000 sec(s). The following command is to insert the third record into the Customer table. INSERT INTO Customer CONTENT {“id”: “03”, “name”: “kiran”, “age”: “29”} If the above command is successfully executed, you will get the following output. Inserted record ”Customer#11:2{id:3,name:kiran,age:29} v1” in 0.004000 sec(s). The following command is to insert the next two records into the Customer table. INSERT INTO Customer (id, name, age) VALUES (04,”javeed”, 21), (05,”raja”, 29) If the above command is successfully executed, you will get the following output. Inserted record ”[Customer#11:3{id:4,name:javeed,age:21} v1, Customer#11:4{id:5,name:raja,age:29} v1]” in 0.007000 sec(s). You can check if all these records are inserted or not by executing the following command. SELECT FROM Customer If the above command is successfully executed, you will get the following output. —-+—–+——–+—-+——-+—- # |@RID |@CLASS |id |name |age —-+—–+——–+—-+——-+—- 0 |#11:0|Customer|1 |satish |25 1 |#11:1|Customer|2 |krishna|26 2 |#11:2|Customer|3 |kiran |29 3 |#11:3|Customer|4 |javeed |21 4 |#11:4|Customer|5 |raja |29 —-+—–+——–+—-+——-+—- Print Page Previous Next Advertisements ”;

OrientDB – Load Record ”; Previous Next Load Record is used to load a particular record from the schema. Load record will load the record with the help of Record ID. It is represented with @rid symbol in the resultset. The following statement is the basic syntax of the LOAD Record command. LOAD RECORD <record-id> Where <record-id> defines the record id of the record you want to load. If you don’t know the Record ID of a particular record, then you can execute any query against the table. In the result-set you will find the Record ID (@rid) of the respective record. Example Let us consider the same Customer table that we have used in previous chapters. Sr.No. Name Age 1 Satish 25 2 Krishna 26 3 Kiran 29 4 Javeed 21 5 Raja 29 Try the following query to retrieve the record having Record ID @rid: #11:0. orientdb {db = demo}> LOAD RECORD #11:0 If the above query is executed successfully, you will get the following output. +—————————————————————————+ | Document – @class: Customer @rid: #11:0 @version: 1 | +—————————————————————————+ | Name | Value | +—————————————————————————+ | id | 1 | | name | satish | | age | 25 | +—————————————————————————+ Print Page Previous Next Advertisements ”;

OrientDB – Reload Record ”; Previous Next Reload Record also works similar to Load Record command and is also used to load a particular record from the schema. Load record will load the record with the help of Record ID. It is represented with @rid symbol in the result-set. The main difference is Reload record ignores the cache which is useful when external concurrent transactions is applied to change the record. It will give the latest update. The following statement is the basic syntax of the RELOAD Record command. RELOAD RECORD <record-id> Where <record-id> defines the record id of the record you want to reload. If you don’t know the Record ID of a particular record, then you can execute any query against the table. In the result-set you will find the Record ID (@rid) of the respective record. Example Let us consider the same Customer table that we have used in the previous chapter. Sr.No. Name Age 1 Satish 25 2 Krishna 26 3 Kiran 29 4 Javeed 21 5 Raja 29 Try the following query to retrieve the record having Record ID @rid: #11:0. orientdb {db = demo}> LOAD RECORD #11:0 If the above query is executed successfully, you will get the following output. +—————————————————————————+ | Document – @class: Customer @rid: #11:0 @version: 1 | +—————————————————————————+ | Name | Value | +—————————————————————————+ | id | 1 | | name | satish | | age | 25 | +—————————————————————————+ Print Page Previous Next Advertisements ”;

OrientDB – Display Records ”; Previous Next Similar to RDBMS, OrientDB supports different types of SQL queries to retrieve the records from the database. While retrieving the records we have different variations or options of queries along with the select statement. The following statement is the basic syntax of the SELECT command. SELECT [ <Projections> ] [ FROM <Target> [ LET <Assignment>* ] ] [ WHERE <Condition>* ] [ GROUP BY <Field>* ] [ ORDER BY <Fields>* [ ASC|DESC ] * ] [ UNWIND <Field>* ] [ SKIP <SkipRecords> ] [ LIMIT <MaxRecords> ] [ FETCHPLAN <FetchPlan> ] [ TIMEOUT <Timeout> [ <STRATEGY> ] ] [ LOCK default|record ] [ PARALLEL ] [ NOCACHE ] Following are the details about the options in the above syntax. <Projections> − Indicates the data you want to extract from the query as a result records set. FROM − Indicates the object to query. This can be a class, cluster, single Record ID, set of Record IDs. You can specify all these objects as target. WHERE − Specifies the condition to filter the result-set. LET − Indicates the context variable which are used in projections, conditions or sub queries. GROUP BY − Indicates the field to group the records. ORDER BY − Indicates the filed to arrange a record in order. UNWIND − Designates the field on which to unwind the collection of records. SKIP − Defines the number of records you want to skip from the start of the result-set. LIMIT − Indicates the maximum number of records in the result-set. FETCHPLAN − Specifies the strategy defining how you want to fetch results. TIMEOUT − Defines the maximum time in milliseconds for the query. LOCK − Defines the locking strategy. DEFAULT and RECORD are the available lock strategies. PARALLEL − Executes the query against ‘x’ concurrent threads. NOCACHE − Defines whether you want to use cache or not. Example Let’s consider the following Customer table created in the previous chapter. Sr.No. Name Age 1 Satish 25 2 Krishna 26 3 Kiran 29 4 Javeed 21 5 Raja 29 Try different select queries to retrieve the data records from the Customer table. Method 1 − You can use the following query to select all records from the Customer table. orientdb {db = demo}> SELECT FROM Customer If the above query is executed successfully, you will get the following output. —-+—–+——–+—-+——-+—- # |@RID |@CLASS |id |name |age —-+—–+——–+—-+——-+—- 0 |#11:0|Customer|1 |satish |25 1 |#11:1|Customer|2 |krishna|26 2 |#11:2|Customer|3 |kiran |29 3 |#11:3|Customer|4 |javeed |21 4 |#11:4|Customer|5 |raja |29 —-+—–+——–+—-+——-+—- Method 2 − Select all records whose name starts with the letter ”k”. orientdb {db = demo}> SELECT FROM Customer WHERE name LIKE ”k%” OR you can use the following query for the above example. orientdb {db = demo}> SELECT FROM Customer WHERE name.left(1) = ”k” If the above query is executed successfully, you will get the following output. —-+—–+——–+—-+——-+—- # |@RID |@CLASS |id |name |age —-+—–+——–+—-+——-+—- 0 |#11:1|Customer|2 |krishna|26 1 |#11:2|Customer|3 |kiran |29 —-+—–+——–+—-+——-+—- Method 3 − Select id, name records from the Customer table with names in uppercase letters. orientdb {db = demo}> SELECT id, name.toUpperCase() FROM Customer If the above query is executed successfully, you will get the following output. —-+——–+—-+——- # |@CLASS |id |name —-+——–+—-+——- 0 |null |1 |SATISH 1 |null |2 |KRISHNA 2 |null |3 |KIRAN 3 |null |4 |JAVEED 4 |null |5 |RAJA —-+——–+—-+——- Method 4 − Select all records from the Customer table where age is in the range of 25 to 29. orientdb {db = demo}> SELECT FROM Customer WHERE age in [25,29] If the above query is executed successfully, you will get the following output. —-+—–+——–+—-+——-+—- # |@RID |@CLASS |id |name |age —-+—–+——–+—-+——-+—- 0 |#11:0|Customer|1 |satish |25 1 |#11:2|Customer|3 |kiran |29 2 |#11:4|Customer|5 |raja |29 —-+—–+——–+—-+——-+—- Method 5 − Select all records from the Customer table where any field contains the word ‘sh’. orientdb {db = demo}> SELECT FROM Customer WHERE ANY() LIKE ”%sh%” If the above query is executed successfully, you will get the following output. —-+—–+——–+—-+——-+—- # |@RID |@CLASS |id |name |age —-+—–+——–+—-+——-+—- 0 |#11:0|Customer|1 |satish |25 1 |#11:1|Customer|2 |krishna|26 —-+—–+——–+—-+——-+—- Method 6 − Select all records from the Customer table, ordered by age in descending order. orientdb {db = demo}> SELECT FROM Customer ORDER BY age DESC If the above query is executed successfully, you will get the following output. —-+—–+——–+—-+——-+—- # |@RID |@CLASS |id |name |age —-+—–+——–+—-+——-+—- 0 |#11:2|Customer|3 |kiran |29 1 |#11:4|Customer|5 |raja |29 2 |#11:1|Customer|2 |krishna|26 3 |#11:0|Customer|1 |satish |25 4 |#11:3|Customer|4 |javeed |21 —-+—–+——–+—-+——-+—- Print Page Previous Next Advertisements ”;

OrientDB – Export Record ”; Previous Next Export Record is the command used to export the loaded record into the requested and supported format. If you are executing any wrong syntax, it will give the list of supported formats. OrientDB is a family of Document database, therefore JSON is the default supported format. The following statement is the basic syntax of the Export Record command. EXPORT RECORD <format> Where <Format> defines the format you want to get the record. Note − Export command will export the loaded record based on Record ID. Example Let us consider the same Customer table that we have used in the previous chapter. Sr.No. Name Age 1 Satish 25 2 Krishna 26 3 Kiran 29 4 Javeed 21 5 Raja 29 Try the following query to retrieve the record having Record ID @rid: #11:0. orientdb {db = demo}> LOAD RECORD #11:0 If the above query is executed successfully, you will get the following output. +—————————————————————————+ | Document – @class: Customer @rid: #11:0 @version: 1 | +—————————————————————————+ | Name | Value | +—————————————————————————+ | id | 1 | | name | satish | | age | 25 | +—————————————————————————+ Use the following query to export he loaded record (#11:0) into JSON format. orientdb {db = demo}> EXPORT RECORD json If the above query is executed successfully, you will get the following output. { “@type”: “d”, “@rid”: “#11:0”, “@version”: 1, “@class”: “Customer”, “id”: 1, “name”: “satish”, “age”: 25 } Print Page Previous Next Advertisements ”;

OrientDB – Java Interface ”; Previous Next Similar to RDBMS, OrientDB supports JDBC. For this, first we need to configure the environment for JDBC programming. Following is the procedure to create a connection between your application and database. First, we need to download the JDBC Driver. Visit the following link https://code.google.com/archive/p/orient/downloads to download OrientDB-JDBC. Following are the basic five steps to achieve OrientDB-jdbc connectivity. Load JDBC driver Create Connection Create statement Execute statement Close connection Example Try the following example to understand OrientDB-JDBC connectivity. Let us consider we have an employee table which contains the following fields and its types. Sr.No. Field Name Type 1 Id Integer 2 Name String 3 Salary Integer 4 Join date Date You can create a Schema (table) by executing the following commands. CREATE DATABASE PLOCAL:/opt/orientdb/databases/testdb CREATE CLASS Employee CREATE PROPERTY Customer.id integer CREATE PROPERTY Customer.name String CREATE PROPERTY Customer.salary integer CREATE PROPERTY Customer.join_date date After executing all the commands, you will get the Employee table with the following fields, employee name with id, age, and join_date fields. Save the following code into OrientJdbcDemo.java file. import com.orientechnologies.common.log.OLogManager; import com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx; import org.junit.After; import org.junit.Before; import org.junit.BeforeClass; import java.io.File; import java.sql.DriverManager; import java.util.Properties; import static com.orientechnologies.orient.jdbc.OrientDbCreationHelper.createSchemaDB; import static com.orientechnologies.orient.jdbc.OrientDbCreationHelper.loadDB; import static java.lang.Class.forName; public abstract class OrientJdbcDemo { protected OrientJdbcConnection conn; public static void main(String ar[]){ //load Driver forName(OrientJdbcDriver.class.getName()); String dbUrl = “memory:testdb”; ODatabaseDocumentTx db = new ODatabaseDocumentTx(dbUrl); String username = “admin”; String password = “admin”; createSchemaDB(db); loadDB(db, 20); dbtx.create(); //Create Connection Properties info = new Properties(); info.put(“user”, username); info.put(“password”, password); conn = (OrientJdbcConnection) DriverManager.getConnection(“jdbc:orient:” + dbUrl, info); //create and execute statement Statement stmt = conn.createStatement(); int updated = stmt.executeUpdate(“INSERT into emplyoee (intKey, text, salary, date) values (”001”,”satish”,”25000”,”” + date.toString() + “”)”); int updated = stmt.executeUpdate(“INSERT into emplyoee (intKey, text, salary, date) values (”002”,”krishna”,”25000”,”” + date.toString() + “”)”); System.out.println(“Records successfully inserted”); //Close Connection if (conn != null && !conn.isClosed()) conn.close(); } } The following command is used to compile the above program. $ javac –classpath:.:orientdb-jdbc-1.0-SNAPSHOT.jar OrientJdbcDemo.java $ java –classpath:.:orientdb-jdbc-1.0-SNAPSHOT.jar OrientJdbcDemo If the above command is executed successfully, you will get the following output. Records Successfully Inserted Print Page Previous Next Advertisements ”;

OrientDB – Optimize Database ”; Previous Next As per technical terminology Optimization means “Achieve the better possible performance in the quickest amount of time.” With reference to database, optimization involves maximizing the speed and efficiency with which data is retrieved. OrientDB supports lightweight edges, which means a direct relation between the data entities. In simple terms, it is a field-to-field relation. OrientDB provides different ways to optimize the database. It supports the conversion of regular edges to lightweight edges. The following statement is the basic syntax of the Optimize database command. OPTMIZE DATABASE [-lwedges] [-noverbose] Where lwedges converts regular edges into lightweight edges and noverbose disables the output. Example In this example, we will use the same database named ‘demo’ that we created in the previous chapter. You can use the following optimize database command. OPTIMIZE DATABASE -lwedges If it is successfully executed, you will get some successful notifications along with the completion message. Database Optimization completed in 35ms Print Page Previous Next Advertisements ”;

OrientDB – Export Database ”; Previous Next Like RDBMS, OrientDB also provides features like Export and Import the database. OrientDB uses the JSON format to export the data. By default export command is using the GZIP algorithm to compress the files. While exporting a database it is not locking the database, which means you can perform concurrent read and write operations on it. It also means that you can create an exact copy of that data because of concurrent read and write operations. In this chapter, you can learn how to export the database from the OrientDB command line. The following statement is the basic syntax of the Export database command. EXPORT DATABASE <output file> Note − You can use this command only after connecting to a particular database. Example In this example, we will use the same database named ‘demo’ that we created in the previous chapter. You can use the following command to export the database to a file named ‘export-demo’. orientdb {db = demo}> EXPORT DATABASE ./export-demo.export If it is successfully executed, it will create a file named ‘export-demo.zip’ or ‘exportdemo.gz’ based on the operating system and you will get the following output. Exporting current database to: DATABASE /home/linuxtp/Desktop/demo.export in GZipped JSON format … Started export of database ”demo” to /home/linuxtp/Desktop/demo.export.gz… Exporting database info…OK Exporting clusters…OK (12 clusters) Exporting schema…OK (11 classes) Exporting records… – Cluster ”internal” (id = 0)…OK (records = 3/3) – Cluster ”index” (id = 1)…OK (records = 0/0) – Cluster ”manindex” (id = 2)…OK (records = 0/0) – Cluster ”default” (id = 3)…OK (records = 0/0) – Cluster ”orole” (id = 4)…OK (records = 3/3) – Cluster ”ouser” (id = 5)…OK (records = 3/3) – Cluster ”ofunction” (id = 6)…OK (records = 0/0) – Cluster ”oschedule” (id = 7)…OK (records = 0/0) – Cluster ”orids” (id = 8)…OK (records = 0/0) – Cluster ”v” (id = 9)…OK (records = 0/0) – Cluster ”e” (id = 10)…OK (records = 0/0) – Cluster ”_studio” (id = 11)…OK (records = 1/1) Done. Exported 10 of total 10 records Exporting index info… – Index dictionary…OK – Index OUser.name…OK – Index ORole.name…OK OK (3 indexes) Exporting manual indexes content… – Exporting index dictionary …OK (entries = 0) OK (1 manual indexes) Database export completed in 377ms Print Page Previous Next Advertisements ”;

OrientDB – Release Database ”; Previous Next In this chapter, you can learn how to release the database from the freeze state through OrientDB command line. The following statement is the basic syntax of the Release database command. RELEASE DATABASE Note − You can use this command only after connecting to a particular database, which is in freeze state. Example In this example, we will use the same database named ‘demo’ that we created in the previous chapter. We will release the database that was freezed in the previous chapter. You can use the following command to release the database. Orientdb {db = demo}> RELEASE DATABASE If it is successfully executed, you will get the following output. Database ”demo” was release successfully Print Page Previous Next Advertisements ”;

Discuss OrientDB ”; Previous Next OrientDB is an Open Source NoSQL Database Management System, which contains the features of traditional DBMS along with the new features of both Document and Graph DBMS. It is written in Java and is amazingly fast. It can store 220,000 records per second on commodity hardware. In the following chapters of this tutorial, we will look closely at OrientDB, one of the best open-source, multi-model, next generation NoSQL product. Print Page Previous Next Advertisements ”;