Category: h2 Database

H2 Database – Rollback ”; Previous Next ROLLBACK is a command from the SQL grammar used to roll back the transaction to a Savepoint or to the previous transaction. By using this command, we can either roll back to the specific Savepoint or we can roll back to the previous executed transaction. Syntax There are two different syntaxes for ROLLABCK command. Following is the generic syntax for the rollback command. ROLLBACK [ TO SAVEPOINT savepointName ] Following is the generic syntax of the Rollback command to the specific transaction. ROLLBACK TRANSACTION transactionName Example 1 In this example, we will roll back the current transaction to a Savepoint named sp1_test using the following command. ROLLBACK sp1_test; The above command produces the following output. Rollback successfully Example 2 In the following example, we will roll back the complete transaction named tx_test using the given command. ROLLBACK TRANSACTION tx_test; The above command produces the following output. Rollback successfully Print Page Previous Next Advertisements ”;

Discuss H2 Database ”; Previous Next 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. 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 – JDBC Connection ”; Previous Next H2 is a JAVA database. We can interact with this database by using JDBC. In this chapter, we will see how to create a JDBC connection with H2 database and the CRUD operations with the H2 database. Generally, there are five steps to create a JDBC connection. Step 1 − Registering the JDBC database driver. Class.forName (“org.h2.Driver”); Step 2 − Opening the connection. Connection conn = DriverManager.getConnection (“jdbc:h2:~/test”, “sa”,””); Step 3 − Creating a statement. Statement st = conn.createStatement(); Step 4 − Executing a statement and receiving Resultset. Stmt.executeUpdate(“sql statement”); Step 5 − Closing a connection. conn.close(); Before moving on to create a full program, we need to add h2-1.4.192.jar file to CLASSPATH. We can get this jar from the folder C:Program Files (x86)H2bin. Create Table In this example, we will write a program for create table. Consider a table named Registration having the following fields. S.No Column Name Data Type NOT NULL Primary Key 1 ID Number Yes Yes 2 First Varchar(255) No No 3 Last Varchar(255) No No 4 Age Number No No Following is an example program named H2jdbcCreateDemo. import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class H2jdbcCreateDemo { // JDBC driver name and database URL static final String JDBC_DRIVER = “org.h2.Driver”; static final String DB_URL = “jdbc:h2:~/test”; // Database credentials static final String USER = “sa”; static final String PASS = “”; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 1: Register JDBC driver Class.forName(JDBC_DRIVER); //STEP 2: Open a connection System.out.println(“Connecting to database…”); conn = DriverManager.getConnection(DB_URL,USER,PASS); //STEP 3: Execute a query System.out.println(“Creating table in given database…”); stmt = conn.createStatement(); String sql = “CREATE TABLE REGISTRATION ” + “(id INTEGER not NULL, ” + ” first VARCHAR(255), ” + ” last VARCHAR(255), ” + ” age INTEGER, ” + ” PRIMARY KEY ( id ))”; stmt.executeUpdate(sql); System.out.println(“Created table in given database…”); // STEP 4: Clean-up environment stmt.close(); conn.close(); } catch(SQLException se) { //Handle errors for JDBC se.printStackTrace(); } catch(Exception e) { //Handle errors for Class.forName e.printStackTrace(); } finally { //finally block used to close resources try{ if(stmt!=null) stmt.close(); } catch(SQLException se2) { } // nothing we can do try { if(conn!=null) conn.close(); } catch(SQLException se){ se.printStackTrace(); } //end finally try } //end try System.out.println(“Goodbye!”); } } Save the above program into H2jdbcCreateDemo.java. Compile and execute the above program by executing the following commands in the command prompt. >javac H2jdbcCreateDemo.java >java H2jdbcCreateDemo The above command produces the following output. Connecting to database… Creating table in given database… Created table in given database… Goodbye! After this execution, we can check the table created using the H2 SQL interface. Insert Records In this example, we will write a program for inserting records. Let us insert the following records into the table Registration. ID First Last Age 100 Zara Ali 18 101 Mahnaz Fatma 25 102 Zaid Khan 30 103 Sumit Mital 28 Following is an example program named H2jdbcInsertDemo. import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class H2jdbcInsertDemo { // JDBC driver name and database URL static final String JDBC_DRIVER = “org.h2.Driver”; static final String DB_URL = “jdbc:h2:~/test”; // Database credentials static final String USER = “sa”; static final String PASS = “”; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ // STEP 1: Register JDBC driver Class.forName(JDBC_DRIVER); // STEP 2: Open a connection System.out.println(“Connecting to a selected database…”); conn = DriverManager.getConnection(DB_URL,USER,PASS); System.out.println(“Connected database successfully…”); // STEP 3: Execute a query stmt = conn.createStatement(); String sql = “INSERT INTO Registration ” + “VALUES (100, ”Zara”, ”Ali”, 18)”; stmt.executeUpdate(sql); sql = “INSERT INTO Registration ” + “VALUES (101, ”Mahnaz”, ”Fatma”, 25)”; stmt.executeUpdate(sql); sql = “INSERT INTO Registration ” + “VALUES (102, ”Zaid”, ”Khan”, 30)”; stmt.executeUpdate(sql); sql = “INSERT INTO Registration ” + “VALUES(103, ”Sumit”, ”Mittal”, 28)”; stmt.executeUpdate(sql); System.out.println(“Inserted records into the table…”); // STEP 4: Clean-up environment stmt.close(); conn.close(); } catch(SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch(Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if(stmt!=null) stmt.close(); } catch(SQLException se2) { } // nothing we can do try { if(conn!=null) conn.close(); } catch(SQLException se) { se.printStackTrace(); } // end finally try } // end try System.out.println(“Goodbye!”); } } Save the above program into H2jdbcInsertDemo.java. Compile and execute the above program by executing the following commands in the command prompt. >javac H2jdbcInsertDemo.java >java H2jdbcInsertDemo The above command produces the following output. Connecting to a selected database… Connected database successfully… Inserted records into the table… Goodbye! Read Record In this example, we will write a program for reading records. Let us try to read all records from the table Registration. Following is an example program named H2jdbcRecordDemo. import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class H2jdbcReadDemo { // JDBC driver name and database URL static final String JDBC_DRIVER = “org.h2.Driver”; static final String DB_URL = “jdbc:h2:~/test”; // Database credentials static final String USER = “sa”; static final String PASS = “”; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 1: Register JDBC driver Class.forName(JDBC_DRIVER); // STEP 2: Open a connection System.out.println(“Connecting to database…”); conn = DriverManager.getConnection(DB_URL,USER,PASS); // STEP 3: Execute a query System.out.println(“Connected database successfully…”); stmt = conn.createStatement(); String sql = “SELECT id, first, last, age FROM Registration”; ResultSet rs = stmt.executeQuery(sql); // STEP 4: Extract data from result set while(rs.next()) { // Retrieve by column name int id = rs.getInt(“id”); int age = rs.getInt(“age”); String first = rs.getString(“first”); String last = rs.getString(“last”); // Display values System.out.print(“ID: ” + id); System.out.print(“, Age: ” + age); System.out.print(“, First: ” + first); System.out.println(“, Last: ” + last); } // STEP 5: Clean-up environment rs.close(); } catch(SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch(Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to

H2 Database – Drop ”; Previous Next DROP is a command taken from the generic SQL grammar. This command is used to delete a database component and its structure from the memory. There are different scenarios with the Drop command that we will discuss in this chapter. Drop Table Drop Table is a command that deletes the respective table and its structure. Syntax Following is the generic syntax of the Drop Table command. DROP TABLE [ IF EXISTS ] tableName [,…] [ RESTRICT | CASCADE ] The command will fail if we are using RESTRICT and the table having dependent views exist. All dependent views are dropped, when we are using CASCADE keyword. Example In this example, we will drop a table named test using the following query. DROP TABLE test; The above query produces the following output. (6) row (s) effected Drop Schema Drop Schema is a command that drops a respective schema from the database server. It will not work from the current schema. Syntax DROP SCHEMA [ IF EXISTS ] schemaName Example In this example, we will drop a schema named test_schema using the following query. DROP SCHEMA TEST_SCHEMA; The above query produces the following output. (0) row(s) effected Drop Sequence Drop Sequence is a command used to drop a sequence from the table structure. Syntax Following is the generic syntax of the Drop Sequence command. DROP SEQUENCE [ IF EXISTS ] sequenceName This command commits an open transaction in this connection. Example In this example, we will drop a sequence named sequence_id. Following is the command. DROP SEQUENCE sequence_id; The above command produces the following output. (0) row (s) effected Drop View Drop View is a command used to drop the existing view. All dependent views are dropped as well if the CASCADE clause is used. Syntax Following is the generic syntax of the Drop View command. DROP VIEW [ IF EXISTS ] viewName [ RESTRICT | CASCADE ] Example In this example, we will drop a view named sample_view using the following query. DROP VIEW sample_view; The above query produces the following output. (0) row (s) effected Print Page Previous Next Advertisements ”;

H2 Database – Savepoint ”; Previous Next SAVEPOINT is a command used to temporarily save the transaction. It is better to maintain savepoints in your transaction as it is helpful to roll back the transaction to the respective Savepoint whenever necessary. Syntax Following is the generic syntax of the Savepoint command. SAVEPOINT savepointName Example In this example, we will create a Savepoint named Half_Done using the following command. SAVEPOINT Half_Done; The above command produces the following output. Savepoint created Print Page Previous Next Advertisements ”;

H2 Database – Useful Resources ”; Previous Next The following resources contain additional information on H2 Database. Please use them to get more in-depth knowledge on this. Useful Video Courses Database Design Course with MySQL 32 Lectures 6 hours Eduonix Learning Solutions More Detail Microsoft AZURE: Database Services on Cloud 18 Lectures 1.5 hours Pranjal Srivastava More Detail Learn ASP.Net MVC and Entity Framework (Database First) Most Popular 20 Lectures 5 hours Trevoir Williams More Detail Local SQLite Database with Node for beginners 18 Lectures 1 hours Laurence Svekis More Detail AWS Database Course – Learn RDS, Neptune, DynamoDB 17 Lectures 1.5 hours Harshit Srivastava More Detail AWS and IBM Databases on Cloud 34 Lectures 3 hours Harshit Srivastava More Detail Print Page Previous Next Advertisements ”;

H2 Database – Commit ”; Previous Next COMMIT is a command from the SQL grammar used to commit the transaction. We can either commit the specific transaction or we can commit the currently executed transaction. Syntax There are two different syntaxes for COMMIT command. Following is the generic syntax for the commit command to commit the current transaction. COMMIT [ WORK ] Following is the generic syntax for the commit command to commit the specific transaction. COMMIT TRANSACTION transactionName Example 1 In this example, let us commit the current transaction using the following command. COMMIT The above command produces the following output. Committed successfully Example 2 In this example, we will commit the transaction named tx_test using the following command. COMMIT TRANSACTION tx_test; The above command produces the following output. Committed successfully Print Page Previous Next Advertisements ”;

H2 Database – Show ”; Previous Next SHOW is a command used to display the list of Schemas, Tables, or Columns of the table. Syntax Following is the generic syntax of the SHOW command. SHOW { SCHEMAS | TABLES [ FROM schemaName ] | COLUMNS FROM tableName [ FROM schemaName ] } Example The following command can be used to get the list of tables in the current database. SHOW TABLES; The above command produces the following output. TABLE_NAME TABLE_SCHEMA CUSTOMER PUBLIC EMP PUBLIC Print Page Previous Next Advertisements ”;