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 – 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 ”;

H2 Database – Truncate ”; Previous Next TRUNCATE is a command used to delete the data from the table. Unlike DELETE FROM without WHERE clause, this command cannot be rolled back. This command commits an open transaction in this connection. Syntax Following is the generic syntax of the truncate command. TRUNCATE TABLE tableName Example In this example, we will truncate a table named test using the following query. TRUNCATE TABLE test; The above query produces the following output. (6) row (s) effected Print Page Previous Next Advertisements ”;

H2 Database – Grant ”; Previous Next Grant is a command coming from the SQL grammar used to grant the rights to a table, to a user, or to a role. Admin rights are required to execute this command. This command commits an open transaction in this connection. In this chapter, we will discuss the different scenarios of Grant command. Grant Right Grant Right is a command to provide admin rights to a table, to a user, or to a role. Syntax Following is the generic syntax of the Grant command. GRANT { SELECT | INSERT | UPDATE | DELETE | ALL } [,…] ON { { SCHEMA schemaName } | { tableName [,…] } } TO { PUBLIC | userName | roleName } Example In this example, we will grant the test table as read-only using the following command. GRANT SELECT ON TEST TO READONLY The above command produces the following output. Grant successfully Grant Alter Any Schema Grant Alter Any Schema is a command to grant schema altering rights to a respective user. Syntax Following is the generic syntax of the Grant Alter Any Schema command. GRANT ALTER ANY SCHEMA TO userName Example In this example, we will grant altering privileges of a schema to a user named test_user. Make sure that test_user exists. Following is the query to grant altering privileges. GRANT ALTER ANY SCHEMA TO test_user; The above query produces the following output. Granted successfully to test_user Print Page Previous Next Advertisements ”;