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

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

Move Vertex command in OrientDB is to move one or more vertices from current location to different class or cluster. If you are applying move command on a particular vertex, then it will update all the edges that are connected to this vertex. If you are specifying a cluster to move vertex, then it moves the vertices to the server owner of the target cluster.

The following statement is the basic syntax of Move Vertex Command.

MOVE VERTEX <source> TO <destination>
[SET [<field>=<value>]* [,]]
[MERGE <JSON>]
[BATCH <batch-size>]

Following are the details about the options in the above syntax.

<source> − Defines the vertex you want to move. It accepts Record ID of a particular vertex or array of Record IDs for vertices.

<destination> − Defines where you want to move the vertex. It supports either class or a cluster as destination.

SET − Sets the values to fields.

MERGE − Sets the values to fields through JSON.

BATCH − Defines the batch size.

Note − This command updates all connected edges, but not links. When using Graph API, it is recommended to use edge connected to vertices.

Example

Try the following examples to learn how to move vertices.

Execute the following query to move a single vertex having Record ID #11:2 from its current position to Class Employee.

orientdb> MOVE VERTEX #11:2 TO CLASS:Employee

If the above query is executed successfully, you will get the following output −

Move vertex command executed with result ''[{old:#11:2, new:#13:0}]'' in 0.022000 sec(s)

Execute the following query to move set of vertices from the class ‘Customer’ to class ‘Employee’.

orientdb> MOVE VERTEX (SELECT FROM Customer) TO CLASS:Employee

If the above query is executed successfully, you will get the following output.

Move vertex command executed with result ''[{old:#11:0,
new:#13:1},{old:#11:1, new:#13:2},{old:#11:2, new:#13:3}]'' in 0.011000 sec(s)

OrientDB provides a web UI to carry out database operations through GUI. This chapter explains the different options available in OrientDB.

Studio Homepage

Studio is a web interface for the administration of OrientDB that comes in bundle with the OrientDB distribution.

First, you need to start the OrientDB server using the following command.

$ server.sh

If you run OrientDB in your machine, the web interface can be accessed via the URL −

http://localhost:2480

If the command is executed successfully, following will be the output on screen.

Connect to an Existing Database

To login, select a database from the databases list and use any database user. By default (username/password) reader/reader can read records from the database, writer/writer can read, create, update and delete records, while admin/admin has all rights.

Drop an Existing Database

Select a database from the databases list and click the trash icon. Studio will open a confirmation popup where you have to insert the Server User and Server Password.

Then click the “Drop database” button. You can find the server credentials in the $ORIENTDB_HOME/config/orientdb-server-config.xml file.

<users>
   <user name = "root" password = "pwd" resources = "*" />
</users>

Create a New Database

To create a new database, click the “New DB” button from the homepage.

Following information is needed to create a new database −

• Database name
• Database type (Document/Graph)
• Storage type (plocal/memory)
• Server user
• Server password

You can find the server credentials in the $ORIENTDB_HOME/config/orientdbserver-config.xml file.

<users>
   <user name = "root" password = "pwd" resources = "*" />
</users>

Once created, Studio will automatically login to the new database.

Execute a Query

Studio supports auto recognition of the language you”re using between those supported: SQL and Gremlin. While writing, use the auto-complete feature by pressing Ctrl + Space.

The following shortcuts are available in the query editor −

• Ctrl + Return − To execute the query or just click the Run button.

• Ctrl/Cmd + Z − To undo changes.

• Ctrl/Cmd + Shift + Z − To redo changes.

• Ctrl/Cmd + F − To search in the editor.

• Ctrl/Cmd + / − To toggle a comment.

The following screenshot shows how to execute a query.

By clicking any @rid value in the result-set, you will go into document edit mode if the record is a Document, otherwise you will go into vertex edit.

You can bookmark your queries by clicking the star icon in the results-set or in the editor. To browse bookmarked queries, click the Bookmarks button. Studio will open the bookmarks list on the left, where you can edit/delete or rerun queries.

Studio saves the executed queries in the Local Storage of the browser. In the query settings, you can configure how many queries studio will keep in history. You can also search a previously executed query, delete all the queries from the history, or delete a single query.

Edit Vertex

To edit the vertex of the graph, go to the Graph section. Then run the following query.

Select From Customer

On successfully running the query, following be the output screenshot. Select the particular vertex in the graph canvas to edit.

Select the edit symbol on the particular vertex. You will get the following screen which contains the options to edit the vertex.

Schema Manager

OrientDB can work in schema-less mode, schema mode or a mix of both. Here we”ll discuss the schema mode. Click on the Schema section on the top of web UI. You will get the following screenshot.

Create a New Class

To create a new Class, just click the New Class button. Following screenshot will appear. You will have to provide the following information as shown in the screenshot to create the new class.

View All Indexes

When you want to have an overview of all indexes created in your database, just click he all indexes button in the Schema UI. This will provide a quick access to some information about indexes (name, type, properties, etc.) and you can drop or rebuild them from here.

Edit Class

Click on any class on the schema section, you will get the following screenshot.

While editing a class, you can add a property or add a new index.

Add a Property

Click the New Property button to add property. You will get the following screenshot.

You have to provide the following details as shown in the screenshot to add property.

Add an Index

Click the New Index button. You will get the following screenshot. You have to provide the following details as shown in the screenshot to add an index.

Graph Editor

Click the graph section. Not only can you visualize your data in a graph style but you can also interact with the graph and modify it.

To populate the graph area, type a query in the query editor or use the functionality Send To Graph from the Browse UI.

Add Vertices

To add a new Vertex in your Graph Database and in the Graph Canvas area, you have to press the button Add Vertex. This operation is done in two steps.

In the first step, you have to choose the class for the new Vertex and then click Next.

In the second step, you have to insert the field values of the new vertex. You can also add custom fields as OrientDB supports schema-less mode. To make the new vertex persistent, click ‘Save changes’ and the vertex will be saved into the database and added to the canvas area.

Delete Vertices

Open the circular menu by clicking on the Vertex that you want to delete. Open the submenu by hovering the mouse to the menu entry more (…) and then click the trash icon.

Remove Vertices from Canvas

Open the circular menu, open the sub-menu by hovering the mouse to the menu entry more (…) and then click the eraser icon.

Inspect Vertices

If you want to take a quick look to the Vertex property, click to the eye icon.

Security

Studio 2.0 includes the new Security Management, where you can manage Users and Roles in a graphical way.

Users

You can perform the following actions to manage the database users −

• Search Users
• Add Users
• Delete Users
• Edit User: roles can be edited in-line, for name, status and password click the Edit button

Add Users

To add a new User, click the Add User button, complete the information for the new user (name, password, status, roles) and then save to add the new user to the database.

Roles

You can perform the following actions to manage the database roles −

• Search Role
• Add Role
• Delete Role
• Edit Role

Add Role

To add a new User, click the Add Role button, complete the information for the new role (name, parent role, mode) and then save to add the new role to the database.

Add Rule to a Role

To add a new security rule for the selected role, click the Add Rule button. This will ask you the string of the resource that you want to secure. Then you can configure the CRUD permissions on the newly created resource.

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.

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                                             |
+---------------------------------------------------------------------------+

Truncate Record command is used to delete the values of a particular record.

The following statement is the basic syntax of the Truncate command.

TRUNCATE RECORD <rid>*

Where <rid>* indicates the Record ID to truncate. You can use multiple Rids separated by comma to truncate multiple records. It returns the number of records truncated.

Example

Let us consider the same Customer table that we have used in the previous chapter.

Try the following query to truncate the record having Record ID #11:4.

Orientdb {db = demo}> TRUNCATE RECORD #11:4

If the above query is executed successfully, you will get the following output.

Truncated 1 record(s) in 0.008000 sec(s).

To check the record of Customer table you can use the following query.

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
----+-----+--------+----+-------+----

Update Record command is used to modify the value of a particular record. SET is the basic command to update a particular field value.

The following statement is the basic syntax of the Update command.

UPDATE <class>|cluster:<cluster>|<recordID>
   [SET|INCREMENT|ADD|REMOVE|PUT <field-name> = <field-value>[,]*] |[CONTENT| MERGE <JSON>]
   [UPSERT]
   [RETURN <returning> [<returning-expression>]]
   [WHERE <conditions>]
   [LOCK default|record]
   [LIMIT <max-records>] [TIMEOUT <timeout>]

Following are the details about the options in the above syntax.

SET − Defines the field to update.

INCREMENT − Increments the specified field value by the given value.

ADD − Adds the new item in the collection fields.

REMOVE − Removes an item from the collection field.

PUT − Puts an entry into map field.

CONTENT − Replaces the record content with JSON document content.

MERGE − Merges the record content with a JSON document.

LOCK − Specifies how to lock the records between load and update. We have two options to specify Default and Record.

UPSERT − Updates a record if it exists or inserts a new record if it doesn’t. It helps in executing a single query in the place of executing two queries.

RETURN − Specifies an expression to return instead of the number of records.

LIMIT − Defines the maximum number of records to update.

TIMEOUT − Defines the time you want to allow the update run before it times out.

Example

Let us consider the same Customer table that we have used in the previous chapter.

Try the following query to update the age of a customer ‘Raja’.

Orientdb {db = demo}> UPDATE Customer SET age = 28 WHERE name = ''Raja''

If the above query is executed successfully, you will get the following output.

Updated 1 record(s) in 0.008000 sec(s).

To check the record of Customer table you can use the following query.

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   |28
----+-----+--------+----+-------+----

Delete Record command is used to delete one or more records completely from the database.

The following statement is the basic syntax of the Delete command.

DELETE FROM <Class>|cluster:<cluster>|index:<index>
   [LOCK <default|record>]
   [RETURN <returning>]
   [WHERE <Condition>*]
   [LIMIT <MaxRecords>]
   [TIMEOUT <timeout>]

Following are the details about the options in the above syntax.

LOCK − Specifies how to lock the records between load and update. We have two options to specify Default and Record.

RETURN − Specifies an expression to return instead of the number of records.

LIMIT − Defines the maximum number of records to update.

TIMEOUT − Defines the time you want to allow the update run before it times out.

Note − Don’t use DELETE to remove Vertices or Edges because it effects the integrity of the graph.

Example

Let us consider the Customer table.

Try the following query to delete the record having id = 4.

orientdb {db = demo}> DELETE FROM Customer WHERE id = 4

If the above query is executed successfully, you will get the following output.

Delete 1 record(s) in 0.008000 sec(s).

To check the record of Customer table you can use the following query.

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
----+-----+--------+----+-------+----

OrientDB supports multi-model feature and provides different ways in approaching and understanding the basic concepts of a database. However, we can easily access these models from the perspective of Document database API. Like RDBMS, OrientDB also uses the Record as an element of storage but it uses the Document type. Documents are stored in the form of Key/Value pairs. We are storing fields and properties as key/value pairs which belong to a concepts class.

Class is a type of data model and the concept is drawn from the Object-oriented programming paradigm. Based on the traditional document database model, data is stored in the form of collection, while in the relational database model data it is stored in tables. OrientDB follows the Document API along with OPPS paradigm. As a concept, class in OrientDB has the closest relationship with the table in relational databases, but (unlike tables) classes can be schema-less, schema-full or mixed. Classes can inherit from other classes, creating trees of classes. Each class has its own cluster or clusters, (created by default, if none are defined).

The following statement is the basic syntax of the Create Class Command.

CREATE CLASS <class>
[EXTENDS <super-class>]
[CLUSTER <cluster-id>*]
[CLUSTERS <total-cluster-number>]
[ABSTRACT]

Following are the details about the options in the above syntax.

<class> − Defines the name of the class you want to create.

<super-class> − Defines the super-class you want to extend with this class.

<total-cluster-number> − Defines the total number of clusters used in this class. Default is 1.

ABSTARCT − Defines the class is abstract. This is optional.

Example

As discussed, class is a concept related to table. Therefore here we will create a table Account. However, while creating class we cannot define fields i.e., properties based on OOPS paradigm.

The following command is to create a class named Account.

orientdb> CREATE CLASS Account

If the above command is executed successfully, you will get the following output.

Class created successfully

You can use the following command to create a class Car which extends to class Vehicle.

orientdb> CREATE CLASS Car EXTENDS Vehicle

If the above command is executed successfully, you will get the following output.

Class created successfully

You can use the following command to create a class Person as abstract.

orientdb> CREATE CLASS Person ABSTRACT

If the above command is executed successfully, you will get the following output.

Class created successfully

Note − Without having properties, the class is useless and unable to build real object. In the further chapters, you can learn how to create properties for a particular class.

Class and Property in OrientDB are used to build a schema with the respective attributes such as class name, super-class, cluster, number of clusters, Abstract, etc. If you want to modify or update any attribute of existing classes in the schema then you have to use Alter Class command.

The following statement is the basic syntax of the Alter Class Command.

ALTER CLASS <class> <attribute-name> <attribute-value>

Following are the details about the options in the above syntax.

<class> − Defines the class name.

<attribute-name> − Defines the attribute you want to change.

<attribute-value> − Defines the value you want to set for the attribute.

The following table defines the list of attributes that support Alter Class command.

Example

Let us try few examples that will update or modify the attributes of the existing class.

The following query is used to define a super-class ‘Person’ for an existing class ‘Employee’.

orientdb> ALTER CLASS Employee SUPERCLASS Person

If the above query is executed successfully, you will get the following output.

Class altered successfully

The following query is used to add a super-class ‘Person’ for an existing class ‘Employee’.

orientdb> ALTER CLASS Employee SUPERCLASS +Person

If the above query is executed successfully, you will get the following output.

Class altered successfully

Truncate class will delete all records of clusters defined as part of class. In OrientDB, every class has an associated cluster with the same name. If you want to also remove all records from the class hierarchy, you need to use the POLYMORPHIC keyword.

The following statement is the basic syntax of Truncate Class command.

TRUNCATE CLASS <class> [ POLYMORPHIC ] [ UNSAFE ]

Following are the details about the options in the above syntax.

<class> − Defines the class you want to truncate.

POLYMORPHIC − Defines whether the command also truncates the hierarchy.

UNSAFE − Defines the command forces truncation on vertex or edge class.

Example

The following query to truncate a class Profile.

orientdb> TRUNCATE CLASS Profile

If the above query is executed successfully, you will get the following output.

Class truncated successfully