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. Using WHERE we can specify the selection criteria to select the required 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 by comma to include various conditions using a WHERE clause, but WHERE clause is an optional part of SELECT command.

You can specify any condition using WHERE clause.

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

A WHERE clause can also be used along with DELETE or UPDATE SQL command to specify a condition.

We can filter the record data by using conditions. We are using different operators in conditional WHERE clause. Here is the pst of operators, which can be used with WHERE clause.

Example

Here is an example that retrieves the details such as id, title, and the author of the book titled "Learn C". It is possible by using WHERE clause in the SELECT command. Following is the query for the same.

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

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

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

HSQLDB – JDBC Program

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

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

pubpc class WhereClause {
   
   pubpc static void main(String[] args) {
      Connection con = null;
      Statement stmt = null;
      ResultSet result = null;
      try {
         Class.forName("org.hsqldb.jdbc.JDBCDriver");
         con = DriverManager.getConnection(
            "jdbc:hsqldb:hsql://localhost/testdb", "SA", "");
         stmt = con.createStatement();
         result = stmt.executeQuery(
            "SELECT id, title, author FROM tutorials_tbl
            WHERE title =  Learn C ");
         
         while(result.next()){
            System.out.println(result.getInt("id")+" |
               "+result.getString("title")+" |
               "+result.getString("author"));
         }
      } catch (Exception e) {
         e.printStackTrace(System.out);
      }
   }

}

You can start the database using the following command.

>cd C:hsqldb-2.3.4hsqldb
hsqldb>java -classpath pb/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 testdb

Compile and execute the above code using the following command.

>javac WhereClause.java
>java WhereClause

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

101 | Learn C | Yaswanth