HSQLDB

0
33

HSQLDB – Introduction

HyperSQL Database (HSQLDB) is a modern relational database manager thead put on conforms nearare locatedr to the SQL:2011 standard and JDBC 4 specifications. It supslots all core features and RDBMS. HSQLDB is used for development, checking, and deployment of database applications.

The main and unique feature of HSQLDB is Standard Compliance. It can provide database access wislim the user's application process, wislim an application server, or as a separate server process.

Features of HSQLDB

  • HSQLDB uses in-memory structure for fast operations against DB server. It uses disk persistence as per user flexibility, with a reliable crash recovery.

  • HSQLDB is furthermore suitable for business intelligence, ETL, and other applications thead put on process huge data sets.

  • HSQLDB has a wide range of enterprise deployment options, such as XA transbehaveions, interinterconnection pooling data sources, and remote authentication.

  • HSQLDB is composed in the Java programming language and operates in a Java Virtual Machine (JVM). It supslots the JDBC interface for database access.

Components of HSQLDB

There are 3 various components in HSQLDB jar package.

  • HyperSQL RDBMS Engine (HSQLDB)

  • HyperSQL JDBC Dlake

  • Database Manager (GUI database access tool, with Swing and AWT versions)

HyperSQL RDBMS and JDBC Dlake provide the core functionality. Database Managers are general-purpose database access tools thead put on can become used with any database engine having a JDBC dlake.

An additional jar calimmediateed sqltool.jar, contains Sql Tool, which is a command collection database access tool. This is a general purpose command. Line database access tool thead put on can become used with other database engines as well.

HSQlDB – Installation

HSQLDB is a relational database management system implemented in pure Java. You can easily embecomed this particular particular database to your own own application uperform JDBC. Or you can use the operations separately.

Prerequisit downes

Follow the prerequisit downe delicatebattlee installations for HSQLDB.

Verify Java Installation

Since HSQLDB is a relational database management system implemented in pure Java, you must install JDK (Java Development Kit) delicatebattlee becomefore installing HSQLDB. If you already have JDK installation in your own own system, then check the following command to verify the Java version.

java –version

If JDK is successextensivey instalimmediateed in your own own system, you will get the following awayplace.

java version "1.8.0_91"
Java(TM) SE Runtime Environment (build 1.8.0_91-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)

If you don’t have JDK instalimmediateed in your own own system, then visit down the following link to Install JDK.

HSQLDB Installation

Following are the steps to install HSQLDB.

Step 1 − Download HSQLDB bundle

Download the lacheck version of HSQLDB database from the following link /index.php?s=httpssourceforgenetprojectshsqldbfiles. Once you click the link, you will get the following screenshot.

Download HsqlDB

Click HSQLDB and the download will start immediately. Finally, you will get the zip file named hsqldb-2.3.4.zip.

Step 2 − Extrbehave the HSQLDB zip file

Extrbehave the zip file and place it into the C: immediateory. After extrbehaveion, you will get a file structure as shown in the following screenshot.

Extrbehave HsqlDB

Step 3 − Create a default database

There is no default database for HSQLDB, generally proper now therefore, you need to make a database for HSQLDB. Let us make a properlinks file named server.properlinks which degoods a brand new database named demodb. Take a look at the following database server properlinks.

server.database.0 = file:hsqldb/demodb
server.dbname.0 = checkdb

Place this particular particular server.properlinks file into HSQLDB home immediateory thead put on is C:hsqldb- 2.3.4hsqldb.

Now execute the following command on command prompt.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server

After execution of the above command, you will receive the server status as shown in the following screenshot.

Default Database

Later, you will get to find the following folder structure of the hsqldb immediateory in the HSQLDB home immediateory thead put on is C:hsqldb-2.3.4hsqldb. Those files are temp file, lck file, log file, properlinks file, and script file of demodb database maked simply by HSQLDB database server.

Create Database

Step 4 − Start the database server

Once you are done creating a database, you have to start the database simply by uperform the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 checkdb

After execution of the above command, you get the following status.

Start Database Server

Now, you can open up up the database home screen thead put on is operateManagerSwing.bat from C:hsqldb-2.3.4hsqldbbin location. This bat file will open up up the GUI file for HSQLDB database. Before thead put on it will ask you for database settings through a dialog package. Take a look at the following screenshot. In this particular particular dialog package, enter the Setting Name, URL as shown above and click Ok.

Run ManagerSwing

You will get the GUI screen of HSQLDB database as shown in the following screenshot.

Hsql Database

HSQlDB – Connect

In the installation chapter, we speak abawayed how to connect the database manually. In this particular particular chapter, we will speak abaway how to connect the database programmatically (uperform Java programming).

Take a look at the following program, which will start the server and make a interinterconnection becometween the Java application and the database.

Example

imslot java.sql.Connection;
imslot java.sql.DlakeManager;

public course ConnectDatabase {
   public static void main(String[] args) {
      Connection con = null;
      
      check {
         //Registering the HSQLDB JDBC dlake
         Class.forName("org.hsqldb.jdbc.JDBCDlake");
         //Creating the interinterconnection with HSQLDB
         con = DlakeManager.getConnection("jdbc:hsqldb:hsql://localhost/checkdb", "SA", "");
         if (con!= null){
            System.away.println("Connection maked successextensivey");
            
         }else{
            System.away.println("Problem with creating interinterconnection");
         }
      
      }  catch (Exception e) {
         e.printStackTrace(System.away);
      }
   }
}

Save this particular particular code into ConnectDatabase.java file. You will have to start the database uperform the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 checkdb

You can use the following command to compile and execute the code.

>javac ConnectDatabase.java
>java ConnectDatabase

After execution of the above command, you will receive the following awayplace −

Connection maked successextensivey

HSQLDB – Data Types

This chapter exbasics the various datakinds of HSQLDB. HSQLDB server provides six categories of data kinds.

Exbehave Numeric Data Types

Data Type From To
hugeint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
smallint 0 255
bit 0 1
decimal -10^38 +1 10^38 -1
numeric -10^38 +1 10^38 -1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647

Approximate Numeric Data Types

Data Type From To
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38

Date and Time Data Types

Data Type From To
datetime Jan 1, 1753 Dec 31, 9999
smalldatetime Jan 1, 1900 Jun 6, 2079
date Stores a date like June 30, 1991
time Stores a time of day like 12:30 P.M.

Note − Here, datetime has 3.33 milli2nds accuracy whereas small datetime has 1- moment accuracy.

Charbehaveer Strings Data Types

Data Type Description
char Maximum size of 8,000 charbehaveers (Fixed size non-Unicode charbehaveers)
varchar Maximum of 8,000 charbehaveers (Variable-size non-Unicode data)
varchar(max) Maximum size of 231 charbehaveers, variable-size non-Unicode data (SQL Server 2005 only)
text Variable-size non-Unicode data with a maximum size of 2,147,483,647 charbehaveers

Unicode Charbehaveer Strings Data Types

Data Type Description
nchar Maximum size of 4,000 charbehaveers (Fixed size Unicode)
nvarchar Maximum size of 4,000 charbehaveers (Variable size Unicode)
nvarchar(max) Maximum size of 231 charbehaveers (SQL Server 2005 only),(Variable size Unicode)
ntext Maximum size of 1,073,741,823 charbehaveers (Variable size Unicode)

Binary Data Types

Data Type Description
binary Maximum size of 8,000 simply bytes (Fixed size binary data)
varbinary Maximum size of 8,000 simply bytes (Variable size binary data)
varbinary(max) Maximum size of 231 simply bytes (SQL Server 2005 only), (Variable size Binary data)
image Maximum size of 2,147,483,647 simply bytes (Variable size Binary Data)

Misc Data Types

Data Type Description
sql_variant Stores values of various SQL Server-supsloted data kinds, except text, ntext, and timestamp
timestamp Stores a database-wide unique numbecomer thead put on gets updated every time a line gets updated
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only)
cursor Reference to a cursor object
table Stores a result set for later procesperform

HSQLDB – Create Table

The basic mandatory requirements to make a table are table name, field names, and the data kinds to those fields. Optionally, you can furthermore provide the key constraints to the table.

Syntax

Take a look at the following syntax.

CREATE TABLE table_name (column_name column_kind);

Example

Let us make a table named tutorials_tbl with the field-names such as id, title, author, and submission_date. Take a look at the following query.

CREATE TABLE tutorials_tbl (
   id INT NOT NULL,
   title VARCHAR(50) NOT NULL,
   author VARCHAR(20) NOT NULL,
   submission_date DATE,
   PRIMARY KEY (id) 
);

After execution of the above query, you will receive the following awayplace −

(0) lines effected

HSQLDB – JDBC Program

Following is the JDBC program used to make a table named tutorials_tbl into the HSQLDB database. Save the program into CreateTable.java file.

imslot java.sql.Connection;
imslot java.sql.DlakeManager;
imslot java.sql.Statement;

public course CreateTable {
   
   public static void main(String[] args) {
      
      Connection con = null;
      Statement stmt = null;
      int result = 0;
      
      check {
         Class.forName("org.hsqldb.jdbc.JDBCDlake");
         con = DlakeManager.getConnection("jdbc:hsqldb:hsql://localhost/checkdb", "SA", "");
         stmt = con.makeStatement();
         
         result = stmt.executeUpdate("CREATE TABLE tutorials_tbl (
            id INT NOT NULL, title VARCHAR(50) NOT NULL,
            author VARCHAR(20) NOT NULL, submission_date DATE,
            PRIMARY KEY (id));
         ");
			
      }  catch (Exception e) {
         e.printStackTrace(System.away);
      }
      System.away.println("Table maked successextensivey");
   }
}

You can start the database uperform the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 checkdb

Compile and execute the above program uperform the following command.

>javac CreateTable.java
>java CreateTable

After execution of the above command, you will receive the following awayplace −

Table maked successextensivey

HSQLDB – Drop Table

It is very easy to fall an existing HSQLDB table. However, you need to become very careful while depermiting any existing table as any data lost will not become recovecrimson after depermiting a table.

Syntax

Following is a generic SQL syntax to fall a HSQLDB table.

DROP TABLE table_name;

Example

Let us conpartr an example to fall a table named employee from the HSQLDB server. Following is the query to fall a table named employee.

DROP TABLE employee;

After execution of the above query, you will receive the following awayplace −

(0) lines effected

HSQLDB – JDBC Program

Following is the JDBC program used to fall the table employee from the HSQLDB server.

Save the following code into DropTable.java file.

imslot java.sql.Connection;
imslot java.sql.DlakeManager;
imslot java.sql.Statement;

public course DropTable {
   public static void main(String[] args) {
      Connection con = null;
      Statement stmt = null;
      int result = 0;
      
      check {
         Class.forName("org.hsqldb.jdbc.JDBCDlake");
         con = DlakeManager.getConnection("jdbc:hsqldb:hsql://localhost/checkdb", "SA", "");
         stmt = con.makeStatement();
         result = stmt.executeUpdate("DROP TABLE employee");
      }catch (Exception e) {
         e.printStackTrace(System.away);
      }
      
      System.away.println("Table fallped successextensivey");
   }
}

You can start the database uperform the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 checkdb

Compile and execute the above program uperform the following command.

>javac DropTable.java
>java DropTable

After execution of the above command, you will receive the following awayplace −

Table fallped successextensivey

HSQLDB – Insert Query

You can achieve Insert query statement in HSQLDB simply by uperform the INSERT INTO command. You have to provide the user-degoodd data following the column field order from the table.

Syntax

Following is the generic syntax to INSERT a query.

INSERT INTO table_name (field1, field2,...fieldN)
VALUES (value1, value2,...valueN );

To insert a string kind data into a table, you will have to use double or performle quotes to provide string value into the insert query statement.

Example

Let us conpartr an example thead put on inserts a record into a table named tutorials_tbl with the values id = 100, title = Learn PHP, Author = John Poul, and the submission date is current date.

Following is the query for the given example.

INSERT INTO tutorials_tbl VALUES (100,'Learn PHP', 'John Poul', NOW());

After execution of the above query, you will receive the following awayplace −

1 line effected

HSQLDB – JDBC Program

Here is the JDBC program to insert the record into the table with the given values, id =100, title = Learn PHP, Author = John Poul, and the submission date is current date. Take a look at the given program. Save the code into the InserQuery.java file.

imslot java.sql.Connection; 
imslot java.sql.DlakeManager; 
imslot java.sql.Statement;  

public course InsertQuery {
   public static void main(String[] args) { 
      Connection con = null; 
      Statement stmt = null; 
      int result = 0; 
      check { 
         Class.forName("org.hsqldb.jdbc.JDBCDlake"); 
         con = DlakeManager.getConnection( 
            "jdbc:hsqldb:hsql://localhost/checkdb", "SA", ""); 
         stmt = con.makeStatement(); 
         result = stmt.executeUpdate("INSERT INTO tutorials_tbl 
            VALUES (100,'Learn PHP', 'John Poul', NOW())"); 
         con.commit(); 
      }catch (Exception e) { 
         e.printStackTrace(System.away); 
      } 
      System.away.println(result+" lines effected"); 
      System.away.println("Rows inserted successextensivey"); 
   } 
} 

You can start the database uperform the following command.

>cd C:hsqldb-2.3.4hsqldb 
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0 
file:hsqldb/demodb --dbname.0 checkdb 

Compile and execute the above program uperform the following command.

>javac InsertQuery.java 
>java InsertQuery 

After execution of the above command, you will receive the following awayplace −

1 lines effected 
Rows inserted successextensivey 

Try to insert the following records into the tutorials_tbl table simply by uperform the INSERT INTO command.

Id Title Author Submission Date
101 Learn C Yaswanth Now()
102 Learn MySQL Abdul S Now()
103 Learn Excellularular Bavya kanna Now()
104 Learn JDB Ajith kumar Now()
105 Learn Junit Sathya Murthi Now()

HSQLDB – Select Query

The SELECT command is used to fetch the record data from HSQLDB database. Here, you need to mention the requicrimson fields list in the Select statement.

Syntax

Here is the generic syntax for Select query.

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]
  • You can fetch one or more fields in a performle SELECT command.

  • You can specify star (*) in place of fields. In this particular particular case, SELECT will return all the fields.

  • You can specify any condition uperform WHERE clause.

  • You can specify an offset uperform OFFSET from where SELECT will start returning records. By default, offset is zero.

  • You can limit the numbecomer of returns uperform LIMIT attribute.

Example

Here is an example thead put on fetches id, title, and author fields of all records from tutorials_tbl table. We can achieve this particular particular simply by uperform the SELECT statement. Following is the query for the example.

SELECT id, title, author FROM tutorials_tbl

After execution of the above query, you will receive the following awayplace.

+------+----------------+-----------------+
|  id  |      title     |    author       |
+------+----------------+-----------------+
| 100  |     Learn PHP  |    John Poul    |
| 101  |     Learn C    |    Yaswanth     |
| 102  |   Learn MySQL  |     Abdul S     |
| 103  |   Learn Excellularular |   Bavya kanna   |
| 104  |   Learn JDB    |    Ajith kumar  |
| 105  |   Learn Junit  |   Sathya Murthi |
+------+----------------+-----------------+

HSQLDB – JDBC Program

Here is the JDBC program thead put on will fetch id, title, and author fields of all records from tutorials_tbl table. Save the following code into the SelectQuery.java file.

imslot java.sql.Connection;
imslot java.sql.DlakeManager;
imslot java.sql.ResultSet;
imslot java.sql.Statement;

public course SelectQuery {
   
   public static void main(String[] args) {
      Connection con = null;
      Statement stmt = null;
      ResultSet result = null;
      
      check {
         Class.forName("org.hsqldb.jdbc.JDBCDlake");
         con = DlakeManager.getConnection(
            "jdbc:hsqldb:hsql://localhost/checkdb", "SA", "");
         stmt = con.makeStatement();
         result = stmt.executeQuery(
            "SELECT id, title, author FROM tutorials_tbl");
         
         while(result.next()){
            System.away.println(result.getInt("id")+" | "+
               result.getString("title")+" | "+
               result.getString("author"));
         }
      } catch (Exception e) {
         e.printStackTrace(System.away);
      }
   }
}

You can start the database uperform the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 checkdb

Compile and execute the above code uperform the following command.

>javac SelectQuery.java
>java SelectQuery

After execution of the above command, you will receive the following awayplace −

100 | Learn PHP | John Poul
101 | Learn C | Yaswanth
102 | Learn MySQL | Abdul S
103 | Learn Excellularular | Bavya Kanna
104 | Learn JDB | Ajith kumar
105 | Learn Junit | Sathya Murthi

HSQLDB – Where Clause

Generally, we use SELECT command to fetch data from HSQLDB table. We can use WHERE conditional clause to filter the resultant data. Uperform WHERE we can specify the selection criteria to select the requicrimson records from a table.

Syntax

Following is the syntax of SELECT command WHERE clause to fetch data from HSQLDB table.

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
  • You can use one or more tables separated simply by comma to include various conditions uperform a WHERE clause, but WHERE clause is an optional part of SELECT command.

  • You can specify any condition uperform WHERE clause.

  • You can specify more than one conditions uperform AND or OR operators.

  • A WHERE clause can furthermore become used asizey with DELETE or UPDATE SQL command to specify a condition.

We can filter the record data simply by uperform conditions. We are uperform various operators in conditional WHERE clause. Here is the list of operators, which can become used with WHERE clause.

Operator Description Example
= Checks if the values of 2 operands are equal or not, if yes then the condition becomecomes true. (A = B) is not true
!= Checks if the values of 2 operands are equal or not, if values are not equal then the condition becomecomes true. (A != B) is true
> Checks if the value of the left operand is greater than the value of the proper operand, if yes then the condition becomecomes true. (A > B) is not true
< Checks if the value of the left operand is less than the value of the proper operand, if yes then the condition becomecomes true. (A < B) is true
>= Checks if the value of the left operand is greater than or equal to the value of the proper operand, if yes then the condition becomecomes true. (A >= B) is not true
<= Checks if the value of the left operand is less than or equal to the value of the proper operand, if yes then the condition becomecomes true. (A <= B) is true

Example

Here is an example thead put on retrieves the details such as id, title, and the author of the book titimmediateed "Learn C". It is achievable simply by uperform WHERE clause in the SELECT command. Following is the query for the exbehave same.

SELECT id, title, author FROM tutorials_tbl WHERE title = 'Learn C';

After execution of the above query, you will receive the following awayplace.

+------+----------------+-----------------+
| id   |      title     |    author       |
+------+----------------+-----------------+
| 101  |      Learn C   |   Yaswanth      |
+------+----------------+-----------------+

HSQLDB – JDBC Program

Here is the JDBC program thead put on retrieves the record data from the table tutorials_tblhaving the title Learn C. Save the following code into WhereClause.java.

imslot java.sql.Connection;
imslot java.sql.DlakeManager;
imslot java.sql.ResultSet;
imslot java.sql.Statement;

public course WhereClause {
   
   public static void main(String[] args) {
      Connection con = null;
      Statement stmt = null;
      ResultSet result = null;
      check {
         Class.forName("org.hsqldb.jdbc.JDBCDlake");
         con = DlakeManager.getConnection(
            "jdbc:hsqldb:hsql://localhost/checkdb", "SA", "");
         stmt = con.makeStatement();
         result = stmt.executeQuery(
            "SELECT id, title, author FROM tutorials_tbl
            WHERE title = 'Learn C'");
         
         while(result.next()){
            System.away.println(result.getInt("id")+" |
               "+result.getString("title")+" |
               "+result.getString("author"));
         }
      } catch (Exception e) {
         e.printStackTrace(System.away);
      }
   }

}

You can start the database uperform the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 checkdb

Compile and execute the above code uperform the following command.

>javac WhereClause.java
>java WhereClause

After execution of the above command, you will receive the following awayplace.

101 | Learn C | Yaswanth

HSQLDB – Update Query

Whenever you want to modify the values of a table, you can use the UPDATE command. This will modify any field value from any HSQLDB table.

Syntax

Here is the generic syntax for UPDATE command.

UPDATE table_name SET field1 = brand new-value1, field2 = brand new-value2 [WHERE Clause]
  • You can update one or more field alwith every other.
  • You can specify any condition uperform WHERE clause.
  • You can update values in a performle table at a time.

Example

Let us conpartr an example thead put on updates the title of the tutorial from "Learn C" to "C and Data Structures" having an id "101". Following is the query for the update.

UPDATE tutorials_tbl SET title = 'C and Data Structures' WHERE id = 101;

After execution of the above query, you will receive the following awayplace.

(1) Rows effected

HSQLDB – JDBC Program

Here is the JDBC program thead put on will update a tutorial title from Learn C to C and Data Structures having an id 101. Save the following program into the UpdateQuery.java file.

imslot java.sql.Connection;
imslot java.sql.DlakeManager;
imslot java.sql.Statement;

public course UpdateQuery {
   
   public static void main(String[] args) {
      Connection con = null;
      Statement stmt = null;
      int result = 0;
      
      check {
         Class.forName("org.hsqldb.jdbc.JDBCDlake");
         con = DlakeManager.getConnection(
            "jdbc:hsqldb:hsql://localhost/checkdb", "SA", "");
         stmt = con.makeStatement();
         result = stmt.executeUpdate(
            "UPDATE tutorials_tbl SET title = 'C and Data Structures' WHERE id = 101");
      } catch (Exception e) {
         e.printStackTrace(System.away);
      }
      System.away.println(result+" Rows effected");
   }
}

You can start the database uperform the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 checkdb

Compile and execute the above program uperform the following command.

>javac UpdateQuery.java
>java UpdateQuery

After execution of the above command, you will receive the following awayplace −

1 Rows effected

HSQLDB – Depermite Clause

Whenever you want to depermite a record from any HSQLDB table, you can use the DELETE FROM command.

Syntax

Here is the generic syntax for DELETE command to depermite data from a HSQLDB table.

DELETE FROM table_name [WHERE Clause]
  • If WHERE clause is not specified, then all the records will become depermited from the given MySQL table.

  • You can specify any condition uperform WHERE clause.

  • You can depermite records in a performle table at a time.

Example

Let us conpartr an example thead put on depermites the record data from the table named tutorials_tbl having id 105. Following is the query thead put on implements the given example.

DELETE FROM tutorials_tbl WHERE id = 105;

After execution of the above query, you will receive the following awayplace −

(1) lines effected

HSQLDB – JDBC Program

Here is the JDBC program thead put on implements the given example. Save the following program into DepermiteQuery.java.

imslot java.sql.Connection;
imslot java.sql.DlakeManager;
imslot java.sql.Statement;

public course DepermiteQuery {
   
   public static void main(String[] args) {
      Connection con = null;
      Statement stmt = null;
      int result = 0;
      
      check {
         Class.forName("org.hsqldb.jdbc.JDBCDlake");
         con = DlakeManager.getConnection(
            "jdbc:hsqldb:hsql://localhost/checkdb", "SA", "");
         stmt = con.makeStatement();
         result = stmt.executeUpdate(
            "DELETE FROM tutorials_tbl   WHERE id=105");
      } catch (Exception e) {
      
         e.printStackTrace(System.away);
      }
      System.away.println(result+" Rows effected");
   }
}

You can start the database uperform the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 checkdb

Compile and execute the above program uperform the following command.

>javac DepermiteQuery.java
>java DepermiteQuery

After execution of the above command, you will receive the following awayplace −

1 Rows effected

HSQLDB – LIKE Clause

There is a WHERE clause in the RDBMS structure. You can use the WHERE clause with an equal to sign (=) where we want to do an exbehave go with. But generally proper now there may become a requirement where we want to filter away all the results where the author name need to contain "john". This can become handimmediateed uperform the SQL LIKE clause asizey with the WHERE clause.

If the SQL LIKE clause is used asizey with % charbehaveers, then it will work like a metacharbehaveer (*) in UNIX while listing away all the files or immediateories at command prompt.

Syntax

Following is the generic SQL syntax of the LIKE clause.

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] fiimmediateed2 = 'severalvalue'
  • You can specify any condition uperform the WHERE clause.

  • You can use the LIKE clause asizey with the WHERE clause.

  • You can use the LIKE clause in place of the equal to sign.

  • When the LIKE clause is used asizey with the % sign, then it will work like a metacharbehaveer oceanrch.

  • You can specify more than one conditions uperform AND or OR operators.

  • A WHERE…LIKE clause can become used asizey with the DELETE or the UPDATE SQL command to specify a condition.

Example

Let us conpartr an example thead put on retrieves the list of tutorials data where the author name starts with John. Following is the HSQLDB query for the given example.

SELECT * from tutorials_tbl WHERE author LIKE 'John%';

After execution of the above query, you will receive the following awayplace.

+-----+----------------+-----------+-----------------+
|  id |      title     |   author  | submission_date |
+-----+----------------+-----------+-----------------+
| 100 |    Learn PHP   | John Poul | 2016-06-20      |
+-----+----------------+-----------+-----------------+

HSQLDB – JDBC Program

Following is the JDBC program thead put on retrieves the list of tutorials data where the author name starts with John. Save the code into LikeClause.java.

imslot java.sql.Connection;
imslot java.sql.DlakeManager;
imslot java.sql.ResultSet;
imslot java.sql.Statement;

public course LikeClause {
   
   public static void main(String[] args) {
      Connection con = null;
      Statement stmt = null;
      ResultSet result = null;
      
      check {
         Class.forName("org.hsqldb.jdbc.JDBCDlake");
         con = DlakeManager.getConnection(
            "jdbc:hsqldb:hsql://localhost/checkdb", "SA", "");
         stmt = con.makeStatement();
         result = stmt.executeQuery(
            "SELECT * from tutorials_tbl WHERE author LIKE 'John%';");
         
         while(result.next()){
            System.away.println(result.getInt("id")+" |
               "+result.getString("title")+" |
               "+result.getString("author")+" |
               "+result.getDate("submission_date"));
         }
      } catch (Exception e) {
         e.printStackTrace(System.away);
      }
   }
}

You can start the database uperform the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 checkdb

Compile and execute the above code uperform the following command.

>javac LikeClause.java
>java LikeClause

After execution of the following command, you will receive the following awayplace.

100 | Learn PHP | John Poul | 2016-06-20

HSQLDB – Sorting Results

The SQL SELECT command fetches data from the HSQLDB table whenever generally proper now there is a requirement thead put on follows a particular order while retrieving and displaying records. In thead put on case, we can use the ORDER BY clause.

Syntax

Here is the syntax of the SELECT command asizey with ORDER BY clause to sort data from HSQLDB.

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
  • You can sort the returned result on any field provided thead put on field is becomeing listed away.

  • You can sort the result on more than one field.

  • You can use the keyword ASC or DESC to get the result in an ascending or descending order. By default, it's in an ascending order.

  • You can use the WHERE…LIKE clause in a usual way to place a condition.

Example

Let us conpartr an example thead put on fetches and sorts the records of tutorials_tbl table simply by ordering the author name in an ascending order. Following is the query for the exbehave same.

SELECT id, title, author from tutorials_tbl ORDER BY author ASC;

After execution of the above query, you will receive the following awayplace.

+------+----------------+-----------------+
| id   |     title      |     author      |
+------+----------------+-----------------+
| 102  |  Learn MySQL   |     Abdul S     | 
| 104  |  Learn JDB     |    Ajith kumar  |
| 103  |  Learn Excellularular  |    Bavya kanna  |
| 100  |  Learn PHP     |    John Poul    |
| 105  |  Learn Junit   |   Sathya Murthi |
| 101  |  Learn C       |    Yaswanth     |
+------+----------------+-----------------+

HSQLDB – JDBC Program

Here is the JDBC program thead put on fetches and sorts the records of tutorials_tbl table simply by ordering the author name in an ascending order. Save the following program into OrderBy.java.

imslot java.sql.Connection;
imslot java.sql.DlakeManager;
imslot java.sql.ResultSet;
imslot java.sql.Statement;

public course OrderBy {
   
   public static void main(String[] args) {
      Connection con = null;
      Statement stmt = null;
      ResultSet result = null;
      
      check {
         Class.forName("org.hsqldb.jdbc.JDBCDlake");
         con = DlakeManager.getConnection(
            "jdbc:hsqldb:hsql://localhost/checkdb", "SA", "");
         stmt = con.makeStatement();
         result = stmt.executeQuery(
            "SELECT id, title, author from tutorials_tbl
            ORDER BY author ASC");
         
         while(result.next()){
            System.away.println(result.getInt("id")+" |
            "+result.getString("title")+" |
            "+result.getString("author"));
         }
      } catch (Exception e) {
         e.printStackTrace(System.away);
      }
   }
}

You can start the database uperform the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -courserawaye lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 checkdb

Compile and execute the above program uperform the following command.

>javac OrderBy.java
>java OrderBy

After execution of the above command, you will receive the following awayplace.

102 | Learn MySQL           | Abdul S
104 | Learn JDB             | Ajith kumar
103 | Learn Excellularular          | Bavya Kanna
100 | Learn PHP             | John Poul
105 | Learn Junit           | Sathya Murthi
101 | C and Data Structures | Yaswanth

HSQLDB – Joins

Whenever generally proper now there is a requirement to retrieve data from multiple tables uperform a performle query, you can use JOINS from RDBMS. You can use multiple tables in your own own performle SQL query. The behave of sign up foring in HSQLDB refers to smashing 2 or more tables into a performle table.

Conpartr the following Customers and Orders tables.

Customer:
+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |  ADDRESS  |  SALARY  |
+----+----------+-----+-----------+----------+
| 1  |  Ramesh  | 32  | Ahmedabad |  2000.00 |
| 2  |  Khilan  | 25  |   Delhi   |  1500.00 |
| 3  |  kaushik | 23  |   Kota    |  2000.00 |
| 4  | Chaitali | 25  |   Mumbai  |  6500.00 |
| 5  |  Hardik  | 27  |   Bhopal  |  8500.00 |
| 6  |  Komal   | 22  |    MP     |  4500.00 |
| 7  |  Muffy   | 24  |   Indore  | 10000.00 |
+----+----------+-----+-----------+----------+
Orders:
+-----+---------------------+-------------+--------+
|OID  |         DATE        | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |      3      |  3000  |
| 100 | 2009-10-08 00:00:00 |      3      |  1500  |
| 101 | 2009-11-20 00:00:00 |      2      |  1560  |
| 103 | 2008-05-20 00:00:00 |      4      |  2060  |
+-----+---------------------+-------------+--------+

Now, permit us check to retrieve the data of the customers and the order amount thead put on the respective customer placed. This means we are retrieving the record data from both customers and orders table. We can achieve this particular particular simply by uperform the JOINS concept in HSQLDB. Following is the JOIN query for the exbehave same.

SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS, ORDERS WHERE CUSTOMERS.ID =
ORDERS.CUSTOMER_ID;

After execution of the above query, you will receive the following awayplace.

+----+----------+-----+--------+
| ID |   NAME   | AGE | AMOUNT |
+----+----------+-----+--------+
|  3 | kaushik  |  23 |  3000  |
|  3 | kaushik  |  23 |  1500  |
|  2 | Khilan   |  25 |  1560  |
|  4 | Chaitali |  25 |  2060  |
+----+----------+-----+--------+

JOIN Types

There are various kinds of sign up fors available in HSQLDB.

  • INNER JOIN − Returns the lines when generally proper now there is a go with in both tables.

  • LEFT JOIN − Returns all lines from the left table, also if generally proper now there are no go withes in the proper table.

  • RIGHT JOIN − Returns all lines from the proper table, also if generally proper now there are no go withes in the left table.

  • FULL JOIN − Returns the lines when generally proper now there is a go with in one of the tables.

  • SELF JOIN − Used to sign up for a table to it’self as if the table were 2 tables, temporarily renaming at minimum one table in the SQL statement.

Inner Join

The many regularly used and imslotant of the sign up fors is the INNER JOIN. It is furthermore refercrimson to as an EQUIJOIN.

The INNER JOIN makes a brand new result table simply by combining the column values of 2 tables (table1 and table2) based upon the sign up for-pcrimsonicate. The query compares every line of table1 with every line of table2 to find all pairs of lines, which satisfy the sign up for-pcrimsonicate. When the sign up for-pcrimsonicate is satisfied, the column values for every go withed pair of lines A and B are combined into a result line.

Syntax

The basic syntax of INNER JOIN is as follows.

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Example

Conpartr the following 2 tables, one titimmediateed as CUSTOMERS table and one more titimmediateed as ORDERS table as follows −

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |  ADDRESS  | SALARY   |
+----+----------+-----+-----------+----------+
| 1  |  Ramesh  | 32  | Ahmedabad | 2000.00  |
| 2  |  Khilan  | 25  |   Delhi   | 1500.00  |
| 3  |  kaushik | 23  |   Kota    | 2000.00  |
| 4  | Chaitali | 25  |   Mumbai  | 6500.00  |
| 5  |  Hardik  | 27  |   Bhopal  | 8500.00  |
| 6  |  Komal   | 22  |     MP    | 4500.00  |
| 7  |  Muffy   | 24  |   Indore  | 10000.00 |
+----+----------+-----+-----------+----------+

+-----+---------------------+-------------+--------+
| OID |         DATE        | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |      3      | 3000   |
| 100 | 2009-10-08 00:00:00 |      3      | 1500   |
| 101 | 2009-11-20 00:00:00 |      2      | 1560   |
| 103 | 2008-05-20 00:00:00 |      4      | 2060   |
+-----+---------------------+-------------+--------+

Now, permit us sign up for these 2 tables uperform INNER JOIN query as follows −

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

After execution of the above query, you will receive the following awayplace.

+----+----------+--------+---------------------+
| ID |   NAME   | AMOUNT |         DATE        |
+----+----------+--------+---------------------+
| 3  |  kaushik |  3000  | 2009-10-08 00:00:00 |
| 3  |  kaushik |  1500  | 2009-10-08 00:00:00 |
| 2  |  Khilan  |  1560  | 2009-11-20 00:00:00 |
| 4  | Chaitali |  2060  | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+

Left Join

The HSQLDB LEFT JOIN returns all lines from the left table, also if generally proper now there are no go withes in the proper table. This means thead put on if the ON clause go withes 0 (zero) records in the proper table, the sign up for will still return a line in the result, but with NULL in every column from the proper table.

This means thead put on a left sign up for returns all the values from the left table, plus go withed values from the proper table or NULL in case of no go wislimg sign up for pcrimsonicate.

Syntax

The basic syntax of LEFT JOIN is as follows −

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

Here the given condition can become any given expression based on your own own requirement.

Example

Conpartr the following 2 tables, one titimmediateed as CUSTOMERS table and one more titimmediateed as ORDERS table as follows −

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |  ADDRESS  | SALARY   |
+----+----------+-----+-----------+----------+
| 1  |  Ramesh  | 32  | Ahmedabad | 2000.00  |
| 2  |  Khilan  | 25  |   Delhi   | 1500.00  |
| 3  |  kaushik | 23  |   Kota    | 2000.00  |
| 4  | Chaitali | 25  |   Mumbai  | 6500.00  |
| 5  |  Hardik  | 27  |   Bhopal  | 8500.00  |
| 6  |  Komal   | 22  |    MP     | 4500.00  |
| 7  |  Muffy   | 24  |  Indore   | 10000.00 |
+----+----------+-----+-----------+----------+

+-----+---------------------+-------------+--------+
| OID |        DATE         | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |     3       | 3000   |
| 100 | 2009-10-08 00:00:00 |     3       | 1500   |
| 101 | 2009-11-20 00:00:00 |     2       | 1560   |
| 103 | 2008-05-20 00:00:00 |     4       | 2060   |
+-----+---------------------+-------------+--------+

Now, permit us sign up for these 2 tables uperform the LEFT JOIN query as follows −

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

After execution of the above query, you will receive the following awayplace −

+----+----------+--------+---------------------+
| ID |   NAME   | AMOUNT |         DATE        |
+----+----------+--------+---------------------+
|  1 |  Ramesh  |  NULL  |        NULL         |
|  2 |  Khilan  |  1560  | 2009-11-20 00:00:00 |
|  3 |  kaushik |  3000  | 2009-10-08 00:00:00 |
|  3 |  kaushik |  1500  | 2009-10-08 00:00:00 |
|  4 | Chaitali |  2060  | 2008-05-20 00:00:00 |
|  5 |  Hardik  |  NULL  |        NULL         |
|  6 |  Komal   |  NULL  |        NULL         |
|  7 |  Muffy   |  NULL  |        NULL         |
+----+----------+--------+---------------------+

Right Join

The HSQLDB RIGHT JOIN returns all lines from the proper table, also if generally proper now there are no go withes in the left table. This means thead put on if the ON clause go withes 0 (zero) records in the left table, the sign up for will still return a line in the result, but with NULL in every column from the left table.

This means thead put on a proper sign up for returns all the values from the proper table, plus go withed values from the left table or NULL in case of no go wislimg sign up for pcrimsonicate.

Syntax

The basic syntax of RIGHT JOIN is as follows −

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

Example

Conpartr the following 2 tables, one titimmediateed as CUSTOMERS table and one more titimmediateed as ORDERS table as follows −

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |  ADDRESS  |  SALARY  |
+----+----------+-----+-----------+----------+
| 1  |  Ramesh  | 32  | Ahmedabad |  2000.00 |
| 2  |  Khilan  | 25  |   Delhi   |  1500.00 |
| 3  |  kaushik | 23  |   Kota    |  2000.00 |
| 4  | Chaitali | 25  |   Mumbai  |  6500.00 |
| 5  |  Hardik  | 27  |   Bhopal  |  8500.00 |
| 6  |  Komal   | 22  |     MP    |  4500.00 |
| 7  |  Muffy   | 24  |   Indore  | 10000.00 |
+----+----------+-----+-----------+----------+

+-----+---------------------+-------------+--------+
| OID |       DATE          | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |      3      |  3000  |
| 100 | 2009-10-08 00:00:00 |      3      |  1500  |
| 101 | 2009-11-20 00:00:00 |      2      |  1560  |
| 103 | 2008-05-20 00:00:00 |      4      |  2060  |
+-----+---------------------+-------------+--------+

Now, permit us sign up for these 2 tables uperform the RIGHT JOIN query as follows −

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

After execution of the above query, you will receive the following result.

+------+----------+--------+---------------------+
|  ID  |   NAME   | AMOUNT |        DATE         |
+------+----------+--------+---------------------+
|  3   |  kaushik |  3000  | 2009-10-08 00:00:00 |
|  3   |  kaushik |  1500  | 2009-10-08 00:00:00 |
|  2   |  Khilan  |  1560  | 2009-11-20 00:00:00 |
|  4   | Chaitali |  2060  | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

Full Join

The HSQLDB FULL JOIN combines the results of both left and proper awayer sign up fors.

The sign up fored-coloured table will contain all records from both tables, and fill in NULLs for the misperform go withes on possibly part.

Syntax

The basic syntax of FULL JOIN is as follows −

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

Here the given condition can become any given expression based on your own own requirement.

Example

Conpartr the following 2 tables, one titimmediateed as CUSTOMERS table and one more titimmediateed as
ORDERS table as follows −

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |  ADDRESS  |  SALARY  |
+----+----------+-----+-----------+----------+
|  1 |  Ramesh  | 32  | Ahmedabad | 2000.00  |
|  2 |  Khilan  | 25  |   Delhi   | 1500.00  |
|  3 |  kaushik | 23  |   Kota    | 2000.00  |
|  4 | Chaitali | 25  |   Mumbai  | 6500.00  |
|  5 |  Hardik  | 27  |   Bhopal  | 8500.00  |
|  6 |  Komal   | 22  |   MP      | 4500.00  |
|  7 |  Muffy   | 24  |   Indore  | 10000.00 |
+----+----------+-----+-----------+----------+

+-----+---------------------+-------------+--------+
| OID |         DATE        | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |    3        | 3000   |
| 100 | 2009-10-08 00:00:00 |    3        | 1500   |
| 101 | 2009-11-20 00:00:00 |    2        | 1560   |
| 103 | 2008-05-20 00:00:00 |    4        | 2060   |
+-----+---------------------+-------------+--------+

Now, permit us sign up for these 2 tables uperform the FULL JOIN query as follows −

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

After execution of the above query, you will receive the following result.

+------+----------+--------+---------------------+
|  ID  |    NAME  | AMOUNT |        DATE         |
+------+----------+--------+---------------------+
|   1  |  Ramesh  |  NULL  |        NULL         |
|   2  |  Khilan  |  1560  | 2009-11-20 00:00:00 |
|   3  |  kaushik |  3000  | 2009-10-08 00:00:00 |
|   3  |  kaushik |  1500  | 2009-10-08 00:00:00 |
|   4  | Chaitali |  2060  | 2008-05-20 00:00:00 |
|   5  |  Hardik  |  NULL  |        NULL         |
|   6  |   Komal  |  NULL  |        NULL         |
|   7  |   Muffy  |  NULL  |        NULL         |
|   3  |  kaushik |  3000  | 2009-10-08 00:00:00 |
|   3  |  kaushik |  1500  | 2009-10-08 00:00:00 |
|   2  |  Khilan  |  1560  | 2009-11-20 00:00:00 |
|   4  | Chaitali |  2060  | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

Self Join

The SQL SELF JOIN is used to sign up for a table to it’self as if the table were 2 tables, temporarily renaming at minimum one table in the SQL statement.

Syntax

The basic syntax of SELF JOIN is as follows −

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

Here, the WHERE clause can become any given expression based on your own own requirement.

Example

Conpartr the following 2 tables, one titimmediateed as CUSTOMERS table and one more titimmediateed as ORDERS table as follows −

+----+----------+-----+-----------+----------+
| ID |    NAME  | AGE |   ADDRESS |   SALARY |
+----+----------+-----+-----------+----------+
|  1 |  Ramesh  |  32 | Ahmedabad | 2000.00  |
|  2 |  Khilan  |  25 |   Delhi   | 1500.00  |
|  3 |  kaushik |  23 |   Kota    | 2000.00  |
|  4 | Chaitali |  25 |   Mumbai  | 6500.00  |
|  5 |  Hardik  |  27 |   Bhopal  | 8500.00  |
|  6 |  Komal   |  22 |   MP      | 4500.00  |
|  7 |  Muffy   |  24 |   Indore  | 10000.00 |
+----+----------+-----+-----------+----------+

Now, permit us sign up for this particular particular table uperform the SELF JOIN query as follows −

SELECT a.ID, b.NAME, a.SALARY FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY > b.SALARY;

After execution of the above query, you will receive the following awayplace −

+----+----------+---------+
| ID |   NAME   | SALARY  |
+----+----------+---------+
| 2  |  Ramesh  | 1500.00 |
| 2  |  kaushik | 1500.00 |
| 1  | Chaitali | 2000.00 |
| 2  | Chaitali | 1500.00 |
| 3  | Chaitali | 2000.00 |
| 6  | Chaitali | 4500.00 |
| 1  |  Hardik  | 2000.00 |
| 2  |  Hardik  | 1500.00 |
| 3  |  Hardik  | 2000.00 |
| 4  |  Hardik  | 6500.00 |
| 6  |  Hardik  | 4500.00 |
| 1  |  Komal   | 2000.00 |
| 2  |  Komal   | 1500.00 |
| 3  |  Komal   | 2000.00 |
| 1  |  Muffy   | 2000.00 |
| 2  |  Muffy   | 1500.00 |
| 3  |  Muffy   | 2000.00 |
| 4  |  Muffy   | 6500.00 |
| 5  |  Muffy   | 8500.00 |
| 6  |  Muffy   | 4500.00 |
+----+----------+---------+

HsqlDB – Null Values

SQL NULL is a term used to represent a misperform value. A NULL value in a table is a value in a field thead put on appears to become blank. Whenever we check to give a condition, which compare the field or column value to NULL, it does not work properly.

We can handle the NULL values simply by uperform the 3 slimgs.

  • IS NULL − The operator returns true if the column value is NULL.

  • IS NOT NULL − The operator returns true if the column value is NOT NULL.

  • <=> − The operator compares values, which (unlike the = operator) is true also for 2 NULL values.

To look for columns thead put on are NULL or NOT NULL, use IS NULL or IS NOT NULL respectively.

Example

Let us conpartr an example where generally proper now there is a table tcount_tbl thead put on contains 2 columns, author and tutorial_count. We can provide NULL values to the tutorial_count indicates thead put on the author did not publish also one tutorial. Therefore, the tutorial_count value for thead put on respective author is NULL.

Execute the following queries.

make table tcount_tbl(author varchar(40) NOT NULL, tutorial_count INT);
INSERT INTO tcount_tbl values ('Abdul S', 20);
INSERT INTO tcount_tbl values ('Ajith kumar', 5);
INSERT INTO tcount_tbl values ('Jen', NULL);
INSERT INTO tcount_tbl values ('Bavya kanna', 8);
INSERT INTO tcount_tbl values ('mahran', NULL);
INSERT INTO tcount_tbl values ('John Poul', 10);
INSERT INTO tcount_tbl values ('Sathya Murthi', 6);

Use the following command to display all the records from the tcount_tbl table.

select * from tcount_tbl;

After execution of the above command, you will receive the following awayplace.

+-----------------+----------------+
|     author      | tutorial_count |
+-----------------+----------------+
|      Abdul S    |      20        |
|    Ajith kumar  |      5         |
|        Jen      |     NULL       |
|    Bavya kanna  |      8         |
|       mahran    |     NULL       |
|     John Poul   |      10        |
|   Sathya Murthi |      6         |
+-----------------+----------------+

To find the records where the tutorial_count column IS NULL, following is the query.

SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL;

After execution of the query, you will receive the following awayplace.

+-----------------+----------------+
|     author      | tutorial_count |
+-----------------+----------------+
|       Jen       |     NULL       |
|      mahran     |     NULL       |
+-----------------+----------------+

To find the records where the tutorial_count column IS NOT NULL, following is the query.

SELECT * FROM tcount_tbl WHERE tutorial_count IS NOT NULL;

After execution of the query, you will receive the following awayplace.

+-----------------+----------------+
|      author     | tutorial_count |
+-----------------+----------------+
|      Abdul S    |      20        |
|     Ajith kumar |       5        |
|     Bavya kanna |       8        |
|     John Poul   |      10        |
|   Sathya Murthi |       6        |
+-----------------+----------------+

HSQLDB – JDBC Program

Here is the JDBC program thead put on retrieves the records separately from the table tcount_tbl where the tutorial_ count is NULL and the tutorial_count is NOT NULL. Save the following program into NullValues.java.

imslot java.sql.Connection;
imslot java.sql.DlakeManager;
imslot java.sql.ResultSet;
imslot java.sql.Statement;

public course NullValues {
   public static void main(String[] args) {
      Connection con = null;
      Statement stmt_is_null = null;
      Statement stmt_is_not_null = null;
      ResultSet result = null;
      check {
         Class.forName("org.hsqldb.jdbc.JDBCDlake");
         con = DlakeManager.getConnection(
            "jdbc:hsqldb:hsql://localhost/checkdb", "SA", "");
         stmt_is_null = con.makeStatement();
         stmt_is_not_null = con.makeStatement();
         result = stmt_is_null.executeQuery(
            "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL;");
         System.away.println("Records where the tutorial_count is NULL");
         
         while(result.next()){
            System.away.println(result.getString("author")+" |
            "+result.getInt("tutorial_count"));
         }
         result = stmt_is_not_null.executeQuery(
            "SELECT * FROM tcount_tbl WHERE tutorial_count IS NOT NULL;");
         System.away.println("Records where the tutorial_count is NOT NULL");
         
         while(result.next()){
            System.away.println(result.getString("author")+" |
            "+result.getInt("tutorial_count"));
         }
      } catch (Exception e) {
         e.printStackTrace(System.away);
      }
   }
}

Compile and execute the above program uperform the following command.

>javac NullValues.java
>Java NullValues

After execution of the above command, you will receive the following awayplace.

Records where the tutorial_count is NULL
Jen         | 0
mahran      | 0

Records where the tutorial_count is NOT NULL
Abdul S        | 20
Ajith kumar    | 5
Bavya kanna    | 8
John Poul      | 10
Sathya Murthi  | 6

HSQLDB – Regular Expressions

HSQLDB supslots several special symbols for pattern go wislimg operation based on regular expressions and the REGEXP operator.

Following is the table of pattern, which can become used asizey with REGEXP operator.

Pattern Whead put on the Pattern Matches
^ Beginning of the string
$ End of the string
. Any performle charbehaveer
[…] Any charbehaveer listed becometween the square brackets
[^…] Any charbehaveer not listed becometween the square brackets
p1|p2|p3 Alternation; go withes any of the patterns p1, p2, or p3
* Zero or more instances of the preceding element
+ One or more instances of the preceding element
{n} n instances of the preceding element
{m,n} m through n instances of the preceding element

Example

Let us check various example queries to meet our requirements. Take a look at the following given queries.

Try this particular particular Query to find all the authors in in whose name starts with '^A'.

SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author,'^A.*');

After execution of the above query, you will receive the following awayplace.

+-----------------+
|     author      |
+-----------------+
|     Abdul S     |
|    Ajith kumar  |
+-----------------+

Try this particular particular Query to find all the authors in in whose name ends with 'ul$'.

SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author,'.*ul$');

After execution of the above query, you will receive the following awayplace.

+-----------------+
|     author      |
+-----------------+
|    John Poul    |
+-----------------+

Try this particular particular Query to find all the authors in in whose name contains 'th'.

SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author,'.*th.*');

After execution of the above query, you will receive the following awayplace.

+-----------------+
|     author      |
+-----------------+
|    Ajith kumar  | 
|     Abdul S     |
+-----------------+

Try this particular particular query to find all the authors in in whose name starts with vowel (a, e, i, o, u).

SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author,'^[AEIOU].*');

After execution of the above query, you will receive the following awayplace.

+-----------------+
|     author      |
+-----------------+
|     Abdul S     |
|    Ajith kumar  |
+-----------------+

HSQLDB – Transbehaveions

A Transbehaveion is a sequential group of database manipulation operations, which is performed and conpartcrimson as one performle work unit. In other words, when all the operations are successextensivey executed, only then the entire transbehaveion will become comppermite. If any operation wislim the transbehaveion fails, then the entire transbehaveion will fail.

Properlinks of Transbehaveions

Basically, transbehaveion supslots 4 standard properlinks. They can become refercrimson to as ACID properlinks.

Atomicity − All the operations in the transbehaveions are executed successextensivey, otherwise the transbehaveion gets aborted at the stage of failure and the previous operations are rolimmediateed back to their particular particular previous posit downion.

Consistency − The database properly modifys states upon a successextensivey committed transbehaveion.

Isolation − It enables the transbehaveion to operate independently on and transmother or father to every other.

Durcapability − The result or effect of a committed transbehaveion persists in case of a system failure.

Commit, Rollback, and Savestage

These keywords are mainly used for HSQLDB transbehaveions.

Commit− Always the successful transbehaveion need to become comppermited simply by executing the COMMIT command.

Rollback − If a failure occurs in the transbehaveion, then the ROLLBACK command need to become executed to return every table referenced in the transbehaveion to it’s previous state.

Savestage − Creates a stage wislim the group of transbehaveions in which to rollback.

Example

The following example exbasics the transbehaveions concept asizey with commit, rollback, and Savestage. Let us conpartr the table Customers with the columns id, name, age, adgown, and salary.

Id Name Age Adgown Salary
1 Ramesh 32 Ahmedabad 2000.00
2 Kaoperate 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitanya 25 Mumbai 6500.00
5 Harish 27 Bhopal 8500.00
6 Kamesh 22 MP 1500.00
7 Murali 24 Indore 10000.00

Use the following commands to make the customer table asizey the collections of the above data.

CREATE TABLE Customer (id INT NOT NULL, name VARCHAR(100) NOT NULL, age INT NOT
NULL, adgown VARCHAR(20), Salary INT, PRIMARY KEY (id));
Insert into Customer values (1, "Ramesh", 32, "Ahmedabad", 2000);
Insert into Customer values (2, "Kaoperate", 25, "Delhi", 1500);
Insert into Customer values (3, "Kaushik", 23, "Kota", 2000);
Insert into Customer values (4, "Chaitanya", 25, "Mumbai", 6500);
Insert into Customer values (5, "Harish", 27, "Bhopal", 8500);
Insert into Customer values (6, "Kamesh", 22, "MP", 1500);
Insert into Customer values (7, "Murali", 24, "Indore", 10000);

Example for COMMIT

The following query depermites lines from the table having age = 25 and uses the COMMIT command to apply those modifys in the database.

DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;

After execution of the above query, you will receive the following awayplace.

2 lines effected

After successful execution of the above command, check the records of the customer table simply by executing the becomelow given command.

Select * from Customer;

After execution of the above query, you will receive the following awayplace.

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |   ADDRESS |  SALARY  |
+----+----------+-----+-----------+----------+
| 1  |  Ramesh  |  32 | Ahmedabad |   2000   |
| 3  |  kaushik |  23 |   Kota    |   2000   |
| 5  |  Harish  |  27 |   Bhopal  |   8500   |
| 6  |  Kamesh  |  22 |    MP     |   4500   |
| 7  |  Murali  |  24 |   Indore  |   10000  |
+----+----------+-----+-----------+----------+

Example for Rollback

Let us conpartr the exbehave same Customer table as inplace.

Id Name Age Adgown Salary
1 Ramesh 32 Ahmedabad 2000.00
2 Kaoperate 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitanya 25 Mumbai 6500.00
5 Harish 27 Bhopal 8500.00
6 Kamesh 22 MP 1500.00
7 Murali 24 Indore 10000.00

Here is the example query thead put on exbasics abaway Rollback functionality simply by depermiting records from the table having age = 25 and then ROLLBACK the modifys in the database.

DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;

After successful execution of the above 2 queries, you can see the record data in the Customer table uperform the following command.

Select * from Customer;

After execution of the above command, you will receive the following awayplace.

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |   ADDRESS |  SALARY  |
+----+----------+-----+-----------+----------+
|  1 |  Ramesh  |  32 | Ahmedabad |   2000   |
|  2 |  Kaoperate   |  25 |   Delhi   |   1500   |
|  3 |  Kaushik |  23 |   Kota    |   2000   |
|  4 | Chaitanya|  25 |   Mumbai  |   6500   |
|  5 |  Harish  |  27 |   Bhopal  |   8500   |
|  6 |  Kamesh  |  22 |     MP    |   4500   |
|  7 |  Murali  |  24 |    Indore |   10000  |
+----+----------+-----+-----------+----------+

The depermite query depermites the record data of customers in in whose age = 25. The Rollback command, rolls back those modifys on the Customer table.

Example for Savestage

Savestage is a stage in a transbehaveion when you can roll the transbehaveion back to a specific stage withaway rolling back the entire transbehaveion.

Let us conpartr the exbehave same Customer table as inplace.

Id Name Age Adgown Salary
1 Ramesh 32 Ahmedabad 2000.00
2 Kaoperate 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitanya 25 Mumbai 6500.00
5 Harish 27 Bhopal 8500.00
6 Kamesh 22 MP 1500.00
7 Murali 24 Indore 10000.00

Let us conpartr in this particular particular example, you plan to depermite the 3 various records from the Customers table. You want to make a Savestage becomefore every depermite, so thead put on you can roll back to any Savestage at any time to return the appropriate data to it’s unique state.

Here is the series of operations.

SAVEPOINT SP1;
DELETE FROM CUSTOMERS WHERE ID = 1;
SAVEPOINT SP2;
DELETE FROM CUSTOMERS WHERE ID = 2;
SAVEPOINT SP3;
DELETE FROM CUSTOMERS WHERE ID = 3;

Now, you have maked 3 Savestage and depermited 3 records. In this particular particular sit downuation, if you want to roll back the records having Id 2 and 3 then use the following Rollback command.

ROLLBACK TO SP2;

Notice thead put on only the preliminary depermition took place since you rolimmediateed back to SP2. Use the following query to display all the records of the customers.

Select * from Customer;

After execution of the above query, you will receive the following awayplace.

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |   ADDRESS |  SALARY  |
+----+----------+-----+-----------+----------+
|  2 |   Kaoperate  |  25 |  Delhi    |   1500   |
|  3 |  Kaushik |  23 |  Kota     |   2000   |
|  4 | Chaitanya|  25 |  Mumbai   |   6500   |
|  5 |  Harish  |  27 |  Bhopal   |   8500   |
|  6 |  Kamesh  |  22 |  MP       |   4500   |
|  7 |  Murali  |  24 |  Indore   |  10000   |
+----+----------+-----+-----------+----------+

Relreare locatedve Savestage

We can relreare locatedve the Savestage uperform the RELEASE command. Following is the generic syntax.

RELEASE SAVEPOINT SAVEPOINT_NAME;

HsqlDB – Alter Command

Whenever generally proper now there is a need to modify the name of a table or a field, modify the order of fields, modify the datakind of fields, or any table structure, you can achieve the exbehave same uperform the ALTER command.

Example

Let us conpartr an example thead put on exbasics the ALTER command uperform various scenarios.

Use the following query to make a table named checkalter_tbl with the fields’ id and name.

//becomelow given query is to make a table checkalter_tbl table.
make table checkalter_tbl(id INT, name VARCHAR(10));

//becomelow given query is to verify the table structure checkalter_tbl.
Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
   'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';

After execution of the above query, you will receive the following awayplace.

+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM |  TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
|   PUBLIC   |TESTALTER_TBL|     ID     |     4     |   INTEGER |     4      |
|   PUBLIC   |TESTALTER_TBL|    NAME    |     12    |   VARCHAR |     10     |
+------------+-------------+------------+-----------+-----------+------------+

Dropping or Adding a Column

Whenever you want to DROP an existing column from the HSQLDB table, then you can use the DROP clause asizey with the ALTER command.

Use the following query to fall a column (name) from the table checkalter_tbl.

ALTER TABLE checkalter_tbl DROP name;

After successful execution of the above query, you can get to know if the name field is fallped from the table checkalter_tbl uperform the following command.

Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
   'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';

After execution of the above command, you will receive the following awayplace.

+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM |  TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
|  PUBLIC    |TESTALTER_TBL|      ID    |      4    |   INTEGER |     4      |
+------------+-------------+------------+-----------+-----------+------------+

Whenever you want to add any column into the HSQLDB table, you can use the ADD clause asizey with the ALTER command.

Use the following query to add a column named NAME to the table checkalter_tbl.

ALTER TABLE checkalter_tbl ADD name VARCHAR(10);

After successful execution of the above query, you can get to know if the name field is added to the table checkalter_tbl uperform the following command.

Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
   'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';

After execution of the above query, you will receive the following awayplace.

+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM |  TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
|  PUBLIC    |TESTALTER_TBL|      ID    |     4     |   INTEGER |     4      |
|  PUBLIC    |TESTALTER_TBL|     NAME   |     12    |   VARCHAR |     10     |
+------------+-------------+------------+-----------+-----------+------------+

Changing a Column Definition or Name

Whenever generally proper now there is a requirement of changing the column definition, use the MODIFY or CHANGE clause asizey with the ALTER command.

Let us conpartr an example thead put on will exbasic how to use the CHANGE clause. The table checkalter_tbl contains 2 fields – id and name – having datakinds int and varchar respectively. Now permit us check to modify the datakind of id from INT to BIGINT. Following is the query to make the modify.

ALTER TABLE checkalter_tbl CHANGE id id BIGINT;

After successful execution of the above query, the table structure can become verified uperform the following command.

Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
   'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';

After execution of the above command, you will receive the following awayplace.

+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM |  TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
|  PUBLIC    |TESTALTER_TBL|      ID    |     4     |   BIGINT  |     4      |
|  PUBLIC    |TESTALTER_TBL|     NAME   |     12    |   VARCHAR |     10     |
+------------+-------------+------------+-----------+-----------+------------+

Now permit us check to incrreare locatedve the size of a column NAME from 10 to 20 in the checkalter_tbl table. Following is the query to achieve this particular particular uperform the MODIFY clause asizey with the ALTER command.

ALTER TABLE checkalter_tbl MODIFY name VARCHAR(20);

After successful execution of the above query, the table structure can become verified uperform the following command.

Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
   'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';

After execution of the above command, you will receive the following awayplace.

+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM |  TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
|  PUBLIC    |TESTALTER_TBL|    ID      |      4    |    BIGINT |     4      |
|  PUBLIC    |TESTALTER_TBL|    NAME    |     12    |   VARCHAR |    20      |
+------------+-------------+------------+-----------+-----------+------------+

HSQLDB – Indexes

A database index is a data structure thead put on improves the speed of operations in a table. Indexes can become maked uperform one or more columns, providing the basis for both rapid random lookups and effective ordering of access to the records.

While creating an index, it need to become conpartcrimson exbehavely whead put on are the columns which will become used to make SQL queries, and make one or more indexes on those columns.

Prbehaveically, indexes are furthermore kind of tables, which maintain the primary key or the index field and a stageer to every record into the behaveual table.

The users cannot see the indexes. They are simply used to speed up queries and will become used simply by the Database Search Engine to fastly locate records.

The INSERT and UPDATE statements take more time on tables having indexes, whereas SELECT statements operate faster on those tables. The reason becomeing while inserting or updating, the database needs to insert or update the index values as well.

Simple & Unique Index

You can make a unique index on a table. A unique index means thead put on 2 lines cannot have the exbehave same index value. Following is the syntax to make an Index on a table.

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2,...);

You can use one or more columns to make an index. For example, make an index on tutorials_tbl uperform tutorial_author.

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

You can make a easy index on a table. Just omit the UNIQUE keyword from the query to make a easy index. A easy index permit’s duplicate values in a table.

If you want to index the values in a column in a descending order, you can add the reserved word DESC after the column name.

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)

ALTER Command to Add & Drop INDEX

There are four kinds of statements for adding indexes to a table −

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) − This statement adds a PRIMARY KEY, which means thead put on indexed values must become unique and cannot become NULL.

  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) − This statement makes an index for which the values must become unique (with the exception of NULL values, which may appear multiple times).

  • ALTER TABLE tbl_name ADD INDEX index_name (column_list) − This adds an ordinary index in which any value may appear more than once.

  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − This makes a special FULLTEXT index thead put on is used for text-oceanrching purposes.

Following is the query to add index in an existing table.

ALTER TABLE checkalter_tbl ADD INDEX (c);

You can fall any INDEX simply by uperform the DROP clause asizey with the ALTER command. Following is the query to fall the above-maked index.

ALTER TABLE checkalter_tbl DROP INDEX (c);

Displaying INDEX Information

You can use the SHOW INDEX command to list away all the indexes associated with a table. Vertical-format awayplace (specified simply by G) regularly is useful with this particular particular statement, to avoid sizey collection wraparound.

Following is the generic syntax to display the index information abaway a table.

SHOW INDEX FROM table_nameG
SHARE
Previous articleQUnit
Next articleHTTP

NO COMMENTS

LEAVE A REPLY