Category: h2 Database

H2 Database – Create ”; Previous Next CREATE is a generic SQL command used to create Tables, Schemas, Sequences, Views, and Users in H2 Database server. Create Table Create Table is a command used to create a user-defined table in the current database. Syntax Following is the generic syntax for the Create Table command. CREATE [ CACHED | MEMORY ] [ TEMP | [ GLOBAL | LOCAL ] TEMPORARY ] TABLE [ IF NOT EXISTS ] name [ ( { columnDefinition | constraint } [,…] ) ] [ ENGINE tableEngineName [ WITH tableEngineParamName [,…] ] ] [ NOT PERSISTENT ] [ TRANSACTIONAL ] [ AS select ] By using the generic syntax of the Create Table command, we can create different types of tables such as cached tables, memory tables, and temporary tables. Following is the list to describe different clauses from the given syntax. CACHED − The cached tables are the default type for regular tables. This means the number of rows is not limited by the main memory. MEMORY − The memory tables are the default type for temporary tables. This means the memory tables should not get too large and the index data is kept in the main memory. TEMPORARY − Temporary tables are deleted while closing or opening a database. Basically, temporary tables are of two types − GLOBAL type − Accessible by all connections. LOCAL type − Accessible by the current connection. The default type for temporary tables is global type. Indexes of temporary tables are kept in the main memory, unless the temporary table is created using CREATE CACHED TABLE. ENGINE − The ENGINE option is only required when custom table implementations are used. NOT PERSISTENT − It is a modifier to keep the complete table data in-memory and all rows are lost when the database is closed. TRANSACTIONAL − It is a keyword that commits an open transaction and this command supports only temporary tables. Example In this example, let us create a table named tutorials_tbl using the following given data. Sr.No Column Name Data Type 1 ID Int 2 Title Varchar(50) 3 Author Varchar(20) 4 Submission_date Date The following query is used to create a table tutorials_tbl along with the given column data. CREATE TABLE tutorials_tbl ( id INT NOT NULL, title VARCHAR(50) NOT NULL, author VARCHAR(20) NOT NULL, submission_date DATE ); The above query produces the following output. (0) rows effected Create Schema Create Schema is a command used to create a user-dependent schema under a particular authorization (under the currently registered user). Syntax Following is the generic syntax of the Create Schema command. CREATE SCHEMA [ IF NOT EXISTS ] name [ AUTHORIZATION ownerUserName ] In the above generic syntax, AUTHORIZATION is a keyword used to provide the respective user name. This command is optional which means if we are not providing the user name, then it will consider the current user. The user that executes the command must have admin rights, as well as the owner. This command commits an open transaction in this connection. Example In this example, let us create a schema named test_schema under SA user, using the following command. CREATE SCHEMA test_schema AUTHORIZATION sa; The above command produces the following output. (0) rows effected Create Sequence Sequence is concept which is used to generate a number by following a sequence for id or any random column values. Syntax Following is the generic syntax of the create sequence command. CREATE SEQUENCE [ IF NOT EXISTS ] newSequenceName [ START WITH long ] [ INCREMENT BY long ] [ MINVALUE long | NOMINVALUE | NO MINVALUE ] [ MAXVALUE long | NOMAXVALUE | NO MAXVALUE ] [ CYCLE long | NOCYCLE | NO CYCLE ] [ CACHE long | NOCACHE | NO CACHE ] This generic syntax is used to create a sequence. The datatype of a sequence is BIGINT. In this the sequence, values are never re-used, even when the transaction is roll backed. Example In this example, let us create a sequence named SEQ_ID, using the following query. CREATE SEQUENCE SEQ_ID; The above query produces the following output. (0) rows effected Print Page Previous Next Advertisements ”;

H2 Database – Merge ”; Previous Next MERGE command is used to update the existing rows and insert new rows into a table. The primary key column plays an important role while using this command; it is used to find the row. Syntax Following is the generic syntax of the MERGE command. MERGE INTO tableName [ ( columnName [,…] ) ] [ KEY ( columnName [,…] ) ] { VALUES { ( { DEFAULT | expression } [,…] ) } [,…] | select } In the above syntax, the KEY clause is used to specify the primary key column name. Along with VALUES clause, we can use primitive values to insert or we can retrieve and store another table values into this table using the select command. Example In this example, let us try to add a new record into Customers table. Following are the details of the new record in the table. Column Name Value ID 8 NAME Lokesh AGE 32 ADDRESS Hyderabad SALARY 2500 Using the following query, let us insert the given record into the H2 database query. MERGE INTO CUSTOMER KEY (ID) VALUES (8, ”Lokesh”, 32, ”Hyderabad”, 2500); The above query produces the following output. Update count: 1 Let us verify the records of the Customer table by executing the following query. SELECT * FROM CUSTOMER; The above query produces the following output. ID Name Age Address Salary 1 Ramesh 32 Ahmedabad 2000 2 Khilan 25 Delhi 1500 3 Kaushik 23 Kota 2000 4 Chaitali 25 Mumbai 6500 5 Hardik 27 Bhopal 8500 6 Komal 22 MP 4500 7 Muffy 24 Indore 10000 8 Lokesh 32 Hyderabad 2500 Now let us try to update the record using the Merge command. Following are the details of the record to be updated. Column Name Value ID 8 NAME Loki AGE 32 ADDRESS Hyderabad SALARY 3000 Use the following query to insert the given record into the H2 database query. MERGE INTO CUSTOMER KEY (ID) VALUES (8, ”Loki”, 32, ”Hyderabad”, 3000); The above query produces the following output. Update count: 1 Let us verify the records of the Customer table by executing the following query. SELECT * FROM CUSTOMER; The above query produces the following output − ID Name Age Address Salary 1 Ramesh 32 Ahmedabad 2000 2 Khilan 25 Delhi 1500 3 Kaushik 23 Kota 2000 4 Chaitali 25 Mumbai 6500 5 Hardik 27 Bhopal 8500 6 Komal 22 MP 4500 7 Muffy 24 Indore 10000 8 Loki 32 Hyderabad 3000 Print Page Previous Next Advertisements ”;

H2 Database – Quick Guide ”; Previous Next H2 Database – Introduction H2 is an open-source lightweight Java database. It can be embedded in Java applications or run in the client-server mode. Mainly, H2 database can be configured to run as inmemory database, which means that data will not persist on the disk. Because of embedded database it is not used for production development, but mostly used for development and testing. This database can be used in embedded mode or in server mode. Following are the main features of H2 database − Extremely fast, open source, JDBC API Available in embedded and server modes; in-memory databases Browser-based Console application Small footprint − Around 1.5MB jar file size Features of H2 Database The main features of H2 Database are as follows − It is an extremely fast database engine. H2 is open source and written in Java. It supports standard SQL and JDBC API. It can use PostgreSQL ODBC driver too. It has embedded and Server mode. H2 supports clustering and multi-version concurrency. It has strong security features. Additional Features Following are some additional features of H2 Database − H2 is a disk-based or in-memory databases and tables, read-only database support, temporary tables. H2 provides transaction support (read committed), 2-phase-commit multiple connections, table level locking. H2 is a cost-based optimizer, using a genetic algorithm for complex queries, zeroadministration. H2 contains scrollable and updatable result set support, large result set, external result sorting, functions can return a result set. H2 supports encrypted database (AES), SHA-256 password encryption, encryption functions, and SSL. Components in H2 Database In order to use H2 Database, you need to have the following components − A web browser A H2 console server This is a client/server application, so both server and client (a browser) are required to run it. H2 Database – Installation H2 is a database written in Java. We can easily embed this database to our application by using JDBC. We can run this on many different platforms or any version of Java Runtime Environment. However, before installing the database, there should be Java installed in the system. Verify Java Installation If JDK is installed in the system, try the following command to verify the Java version. java –version If JDk is successfully installed in the system, then we will get the following output. 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 JDK is not installed in the system, then visit the following link to Install JDK. Install H2 Database We can run this database on many different platforms. In this chapter, we will learn about H2 Database installation on Windows. Following are the steps to install H2 Database on Windows operating system. Step 1: Download H2 Setup File Download the latest version of H2 Database from the given link. In this link, you will get the latest version of H2 database in two types. One is Windows Installer type (that is .exe file) and second is Platform-Independent zip file for other operating systems. Click the Windows installer for downloading the Windows supportable H2 database after downloading the .exe file. In this case, we are using H2 Database with the version 1.4.192. Step 2: Install H2 Database After downloading we get the H2 Windows installer file (i.e. h2-setup-yyyy-mm-dd.exe) in the Downloads directory. To start the installation process of H2 Database, double click on the installer file. The following screen is the first step in the installation process. Provide a path where we want to install the H2 database server as shown in the following screenshot. As seen in the above screenshot, by default it will take C:ProgramFiles (x86)H2 as the destination folder. Click next to proceed to the next step. The following screen pops up. In the above screenshot, click the Install button to start the installation process. After installation, we get the following screenshot. Click Finish to complete the installation process. Step 3: Verify H2 Database Installation After installation, let us verify the database installation in the system. Click Windows → type H2 Console → Click H2 console icon. Connect to the URL http://localhost:8082. At the time of connecting, the H2 database will ask for database registration as shown in the following screenshot. Fill all the details in the above dialog box such as Saved Settings, Settings Name, Driver Class, JDBC URL, User Name, and Password. In the JDBC URL, specify the database is located and the database name. User Name and Password are the fields for user name and password of the database. Click Connect. The Database welcome page pops up as shown in the following screenshot. H2 Database – Select Select command is used to fetch record data from a table or multiple tables. If we design a select query, then it returns data in the form of result table called result sets. Syntax The basic syntax of SELECT statement is as follows − SELECT [ TOP term ] [ DISTINCT | ALL ] selectExpression [,…] FROM tableExpression [,…] [ WHERE expression ] [ GROUP BY expression [,…] ] [ HAVING expression ] [ { UNION [ ALL ] | MINUS | EXCEPT | INTERSECT } select ] [ ORDER BY order [,…] ] [ [ LIMIT expression ] [ OFFSET expression ] [ SAMPLE_SIZE rowCountInt ] ] [ FOR UPDATE ] To fetch all the available fields, use the following syntax. SELECT * FROM table_name; Example Consider the CUSTOMER table having the following records − +—-+———-+—–+———–+———-+ | 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 |

H2 Database – Update ”; Previous Next The UPDATE query is used to update or modify the existing records in a table. We can use WHERE clause with UPDATE query to update the selected rows, otherwise all the rows would be affected. Syntax Following is the basic syntax of the UPDATE query. UPDATE tableName [ [ AS ] newTableAlias ] SET { { columnName = { DEFAULT | expression } } [,…] } | { ( columnName [,…] ) = ( select ) } [ WHERE expression ] [ ORDER BY order [,…] ] [ LIMIT expression ] In this UPDATE syntax, we can combine more than one condition by using AND or OR clauses. Example Consider the CUSTOMER table having the following records. +—-+———-+—–+———–+———-+ | 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 | +—-+———-+—–+———–+———-+ If you want to get the customer table along with the given data, execute the following queries. CREATE TABLE CUSTOMER (id number, name varchar(20), age number, address varchar(20), salary number); INSERT into CUSTOMER values (1, ”Ramesh”, 32, ”Ahmedabad”, 2000); INSERT into CUSTOMER values (2, ”Khilan”, 25, ”Delhi”, 1500); INSERT into CUSTOMER values (3, ”kaushik”, 23, ”Kota”, 2000); INSERT into CUSTOMER values (4, ”Chaitali”, 25, ”Mumbai”, 6500); INSERT into CUSTOMER values (5, ”Hardik”, 27, ”Bhopal”, 8500); INSERT into CUSTOMER values (6, ”Komal”, 22, ”MP”, 4500); INSERT into CUSTOMER values (7, ”Muffy”, 24, ”Indore”, 10000); The following command is an example, which would update ADDRESS for a customer whose ID is 6 − UPDATE CUSTOMERS SET ADDRESS = ”Pune” WHERE ID = 6; Now, CUSTOMERS table would have the following records. We can check the customer table records by executing the following query. SELECT * FROM CUSTOMERS; The above query produces the following result. +—-+———-+—–+———–+———-+ | 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 | Pune | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +—-+———-+—–+———–+———-+ To modify all ADDRESS and SALARY column values in CUSTOMERS table, we need not use the WHERE clause. The UPDATE query would be as follows − UPDATE CUSTOMERS SET ADDRESS = ”Pune”, SALARY = 1000.00; Now, CUSTOMERS table would have the following records. We can check the customer table records by executing the following query. SELECT * FROM CUSTOMERS; The above query produces the following result − +—-+———-+—–+———+———+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———+———+ | 1 | Ramesh | 32 | Pune | 1000.00 | | 2 | Khilan | 25 | Pune | 1000.00 | | 3 | kaushik | 23 | Pune | 1000.00 | | 4 | Chaitali | 25 | Pune | 1000.00 | | 5 | Hardik | 27 | Pune | 1000.00 | | 6 | Komal | 22 | Pune | 1000.00 | | 7 | Muffy | 24 | Pune | 1000.00 | +—-+———-+—–+———+———+ Print Page Previous Next Advertisements ”;

H2 Database – Backup ”; Previous Next BACKUP is the command used to take database backup into a separate .zip file. Objects are not locked, and when it takes backup the transaction log is also copied. Admin rights are required to execute this command. Syntax Following is the generic syntax of the Backup command. BACKUP TO fileNameString; Example In this example, let us take a backup of the current database into backup.zip file. Use the following command for the same. BACKUP TO ”backup.zip”; On executing the above command, you will get the backup.zip file in your local file system. Print Page Previous Next Advertisements ”;

H2 Database – Call ”; Previous Next CALL is a SQL command which belongs to H2 database server. This command is used to calculate a simple expression. It returns the result of the given expression in a single column field. When it returns an array of results, then each element in the array is displayed as a column value. Syntax Following is the generic syntax of the CALL command. CALL expression; We can use the arithmetic expression in this syntax. Example Let us take an example and execute an arithmetic expression (15 * 25) using call command. CALL 15*25; The above command produces the following output. 375 375 Print Page Previous Next Advertisements ”;

H2 Database – Insert ”; Previous Next The SQL INSERT statement is used to add new rows of data to a table in the database. Syntax Following is the basic syntax of INSERT INTO statement. INSERT INTO tableName { [ ( columnName [,…] ) ] { VALUES { ( { DEFAULT | expression } [,…] ) } [,…] | [ DIRECT ] [ SORTED ] select } } | { SET { columnName = { DEFAULT | expression } } [,…] } Using this INSERT statement, we can insert a new record or new rows into a table. When using DIRECT clause, the results are directly affected to the target table without any intermediate step. However, while adding values for all the columns of the table, make sure the order of the values is in the same order as the columns in the table. Example Let us take an example and try to insert the following given records into the Customer table. ID Name Age Address Salary 1 Ramesh 32 Ahmedabad 2000 2 Khilan 25 Delhi 1500 3 Kaushik 23 Kota 2000 4 Chaitail 25 Mumbai 6500 5 Hardik 27 Bhopal 8500 6 Komal 22 MP 4500 7 Muffy 24 Indore 10000 We can get all the given records into the customer table by executing the following commands. INSERT INTO CUSTOMER VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000); INSERT INTO CUSTOMER VALUES (2, ”Khilan”, 25, ”Delhi”, 1500); INSERT INTO CUSTOMER VALUES (3, ”kaushik”, 23, ”Kota”, 2000); INSERT INTO CUSTOMER VALUES (4, ”Chaitali”, 25, ”Mumbai”, 6500); INSERT INTO CUSTOMER VALUES (5, ”Hardik”, 27, ”Bhopal”, 8500); INSERT INTO CUSTOMER VALUES (6, ”Komal”, 22, ”MP”, 4500); INSERT INTO CUSTOMER VALUES (7, ”Muffy”, 24, ”Indore”, 10000); Print Page Previous Next Advertisements ”;

H2 Database – Introduction ”; Previous Next H2 is an open-source lightweight Java database. It can be embedded in Java applications or run in the client-server mode. Mainly, H2 database can be configured to run as inmemory database, which means that data will not persist on the disk. Because of embedded database it is not used for production development, but mostly used for development and testing. This database can be used in embedded mode or in server mode. Following are the main features of H2 database − Extremely fast, open source, JDBC API Available in embedded and server modes; in-memory databases Browser-based Console application Small footprint − Around 1.5MB jar file size Features of H2 Database The main features of H2 Database are as follows − It is an extremely fast database engine. H2 is open source and written in Java. It supports standard SQL and JDBC API. It can use PostgreSQL ODBC driver too. It has embedded and Server mode. H2 supports clustering and multi-version concurrency. It has strong security features. Additional Features Following are some additional features of H2 Database − H2 is a disk-based or in-memory databases and tables, read-only database support, temporary tables. H2 provides transaction support (read committed), 2-phase-commit multiple connections, table level locking. H2 is a cost-based optimizer, using a genetic algorithm for complex queries, zeroadministration. H2 contains scrollable and updatable result set support, large result set, external result sorting, functions can return a result set. H2 supports encrypted database (AES), SHA-256 password encryption, encryption functions, and SSL. Components in H2 Database In order to use H2 Database, you need to have the following components − A web browser A H2 console server This is a client/server application, so both server and client (a browser) are required to run it. Print Page Previous Next Advertisements ”;

H2 Database Tutorial PDF Version Quick Guide Resources Job Search Discussion H2 is an open-source lightweight Java database. It can be embedded in Java applications or run in the client-server mode. H2 database can be configured to run as in-memory database, which means that data will not persist on the disk. In this brief tutorial, we will look closely at the various features of H2 and its commands, one of the best open-source, multi-model, next generation SQL product. Audience This tutorial is designed for all those software professionals who would like to learn how to use H2 database in simple and easy steps. This tutorial will give you a good overall understanding on the basic concepts of H2 database. Prerequisites H2 database primarily deals with relational data. Hence, you should first of all have a good understanding of the concepts of databases in general, especially RDBMS concepts, before going ahead with this tutorial. Print Page Previous Next Advertisements ”;

H2 Database – Select ”; Previous Next Select command is used to fetch record data from a table or multiple tables. If we design a select query, then it returns data in the form of result table called result sets. Syntax The basic syntax of SELECT statement is as follows − SELECT [ TOP term ] [ DISTINCT | ALL ] selectExpression [,…] FROM tableExpression [,…] [ WHERE expression ] [ GROUP BY expression [,…] ] [ HAVING expression ] [ { UNION [ ALL ] | MINUS | EXCEPT | INTERSECT } select ] [ ORDER BY order [,…] ] [ [ LIMIT expression ] [ OFFSET expression ] [ SAMPLE_SIZE rowCountInt ] ] [ FOR UPDATE ] To fetch all the available fields, use the following syntax. SELECT * FROM table_name; Example Consider the CUSTOMER table having the following records − +—-+———-+—–+———–+———-+ | 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 | +—-+———-+—–+———–+———-+ To get the customer table along with the given data, execute the following queries. CREATE TABLE CUSTOMER (id number, name varchar(20), age number, address varchar(20), salary number); INSERT into CUSTOMER values (1, ”Ramesh”, 32, ”Ahmedabad”, 2000); INSERT into CUSTOMER values (2, ”Khilan”, 25, ”Delhi”, 1500); INSERT into CUSTOMER values (3, ”kaushik”, 23, ”Kota”, 2000); INSERT into CUSTOMER values (4, ”Chaitali”, 25, ”Mumbai”, 6500); INSERT into CUSTOMER values (5, ”Hardik”, 27, ”Bhopal”, 8500); INSERT into CUSTOMER values (6, ”Komal”, 22, ”MP”, 4500); INSERT into CUSTOMER values (7, ”Muffy”, 24, ”Indore”, 10000); The following command is an example, which would fetch ID, Name and Salary fields of the customers available in the CUSTOMER table. SELECT ID, NAME, SALARY FROM CUSTOMERS; The above command produces the following result. +—-+———-+———-+ | ID | NAME | SALARY | +—-+———-+———-+ | 1 | Ramesh | 2000.00 | | 2 | Khilan | 1500.00 | | 3 | kaushik | 2000.00 | | 4 | Chaitali | 6500.00 | | 5 | Hardik | 8500.00 | | 6 | Komal | 4500.00 | | 7 | Muffy | 10000.00 | +—-+———-+———-+ Use the following query to fetch all the fields of CUSTOMERS table. SQL> SELECT * FROM CUSTOMERS; The above query produces the following result − +—-+———-+—–+———–+———-+ | 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 | +—-+———-+—–+———–+———-+ Print Page Previous Next Advertisements ”;