H2 Database - JDBC Connection
Donald Green
0
203
H2 is a JAVA database. We can interact with this database using JDBC. In this chapter, we will see how to create JDBC connection to H2 database and CRUD operations to 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 a connection.
Connection conn = DriverManager.getConnection("jdbc:h2:~/test", "sa","");
Step 3 - Create an application.
Statement st = conn.createStatement();
Step 4 - Execute the statement and get the Resultset.
Stmt.executeUpdate("sql statement");
Step 5 - Closing the connection.
conn.close();
Before moving on to building the complete program, we need to add the h2-1.4.192.jar file to the CLASSPATH. We can get this file from C:\Program Files (x86)\H2\bin folder .
Create table
In this example, we will write a program to create a table. Consider a table named Registration that has the following fields.
| S.No | Column name | Data type | NON-ZERO | Primary key |
|---|---|---|---|---|
| one | I'D | Number | Yes | Yes |
| 2 | First | varchar(255) | No | No |
| 3 | past | varchar(255) | No | No |
| 4 | Age | Number | No | No |
Below is an example program named H2jdbcCreateDemo .
import java . sql . Connection ; import java . sql . Driver Manager ; 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 = Driver Manager . 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 . 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 in H2jdbcCreateDemo.java. Compile and execute the above program by running the following commands on the command line.
\>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.
Paste Records
In this example, we will write a program to insert records. Insert the following records into the Registration table.
| I'D | First | past | Age |
|---|---|---|---|
| one hundred | Zara | Ali | eighteen |
| 101 | Mahnaz | Fatma | 25 |
| 102 | Zaid | khan | thirty |
| 103 | Summit | Mital | 28 |
Below is an example program named H2jdbcInsertDemo .
import java . sql . Connection ; import java . sql . Driver Manager ; 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 = Driver Manager . 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 in H2jdbcInsertDemo.java. Compile and execute the above program by running the following commands on the command line.
\>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 entry
In this example, we will write a program to read records. Let's try to read all records from the registration table .
Below is an example program named H2jdbcRecordDemo .
import java . sql . Connection ; import java . sql . Driver Manager ; 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 = Driver Manager . 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 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 in H2jdbcReadDemo.java. Compile and execute the above program by running the following commands on the command line.
\>javac H2jdbcReadDemo.java \> java h2jdbcreaddemo
The above command produces the following output.
Connecting to a selected database... Connected database successfully... ID: 100, Age: 18, First: Zara, Last: Ali ID: 101, Age: 25, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 28, First: Sumit, Last: Mittal Goodbye!
Refresh entries
In this example, we will write a program to update records. Let's try to read all records from the registration table .
Below is an example program named H2jdbcUpdateDemo .
import java . sql . Connection ; import java . sql . Driver Manager ; import java . sql . ResultSet ; import java . sql . SQLException ; import java . sql . Statement ; public class H2jdbcUpdateDemo { // 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 database..." ); conn = Driver Manager . getConnection ( DB_URL , USER , PASS ); // STEP 3: Execute a query System . out . println ( "Connected database successfully..." ); stmt = conn . createStatement (); String sql = "UPDATE Registration " + "SET age = 30 WHERE id in (100, 101)" ; stmt . executeUpdate ( sql ); // Now you can extract all the records // to see the updated records sql = "SELECT id, first, last, age FROM Registration" ; ResultSet rs = stmt . executeQuery ( sql ); 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 ); } 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 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 in H2jdbcUpdateDemo.java. Compile and execute the above program by running the following commands on the command line.
\>javac H2jdbcUpdateDemo.java \>java H2jdbcUpdateDemo
The above command produces the following output.
Connecting to a selected database... Connected database successfully... ID: 100, Age: 30, First: Zara, Last: Ali ID: 101, Age: 30, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 28, First: Sumit, Last: Mittal Goodbye!
Delete entries
In this example, we will write a program to delete records. Let's try to read all records from the registration table .
Below is an example program named H2jdbcDeleteDemo .
import java . sql . Connection ; import java . sql . Driver Manager ; import java . sql . ResultSet ; import java . sql . SQLException ; import java . sql . Statement ; public class H2jdbcDeleteDemo { // 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 = Driver Manager . getConnection ( DB_URL , USER , PASS ); // STEP 3: Execute a query System . out . println ( "Creating table in given database..." ); stmt = conn . createStatement (); String sql = "DELETE FROM Registration " + "WHERE id = 101" ; stmt . executeUpdate ( sql ); // Now you can extract all the records // to see the remaining records sql = "SELECT id, first, last, age FROM Registration" ; ResultSet rs = stmt . executeQuery ( sql ); 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 ); } 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 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 in H2jdbcDeleteDemo.java. Compile and execute the above program by running the following commands on the command line.
\>javac H2jdbcDeleteDemo.java \>java H2jdbcDeleteDemo
The above command produces the following output.
