Scribd
Upload
46 views13 pages

H2 Database - JDBC Connection - Tutorialspoint PDF

Uploaded by

NEERAJ YADAV
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
46 views13 pages

H2 Database - JDBC Connection - Tutorialspoint PDF

Uploaded by

NEERAJ YADAV
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

H2 Database - JDBC Connection

Advertisements

Free Bee INSTALL


4.0 FREE

Previous Page Next Page  

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.

[Link] ("[Link]");

Step 2 − Opening the connection.

Connection conn = [Link] ("jdbc:h2:~/test", "sa","");

Step 3 − Creating a statement.

Statement st = [Link]();

Step 4 − Executing a statement and receiving Resultset.

[Link]("sql statement");
We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy.
Step 5 − Closing a connection.
Accept
[Link]();

Learn more
Before moving on to create a full program, we need to add [Link] file to
CLASSPATH. We can get this jar from the folder C:\Program Files (x86)\H2\bin.

Create Table

In this example, we will write a program for create table. Consider a table named
Registration having the following fields.

[Link] 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 [Link];
import [Link];
import [Link];
import [Link];

public class H2jdbcCreateDemo {


// JDBC driver name and database URL
static final String JDBC_DRIVER = "[Link]";
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
[Link](JDBC_DRIVER);

//STEP 2: Open a connection


[Link]("Connecting to database...");
conn = [Link](DB_URL,USER,PASS);

//STEP 3: Execute a query


[Link]("Creating table in given database...");
stmt = [Link]();
String sql = "CREATE TABLE REGISTRATION " +
"(id INTEGER not NULL, " +
" first VARCHAR(255), " +
" last VARCHAR(255), " +
" age INTEGER, " +
" PRIMARY KEY ( id ))";
[Link](sql);
[Link]("Created table in given database...");

// STEP 4: Clean-up environment


[Link]();
[Link]();
} catch(SQLException se) {
//Handle errors for JDBC
[Link]();
} catch(Exception e) {
//Handle errors for [Link]
[Link]();
} finally {
//finally block used to close resources
try{
if(stmt!=null) [Link]();
} catch(SQLException se2) {
} // nothing we can do
try {
if(conn!=null) [Link]();
} catch(SQLException se){
[Link]();
} //end finally try
} //end try
[Link]("Goodbye!");
}
}

Save the above program into [Link]. Compile and execute the above
program by executing the following commands in the command prompt.

\>javac [Link]
\>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 [Link];
import [Link];
import [Link];
import [Link];

public class H2jdbcInsertDemo {


// JDBC driver name and database URL
static final String JDBC_DRIVER = "[Link]";
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
[Link](JDBC_DRIVER);

// STEP 2: Open a connection


[Link]("Connecting to a selected database...");
conn = [Link](DB_URL,USER,PASS);
[Link]("Connected database successfully...");

// STEP 3: Execute a query


stmt = [Link]();
String sql = "INSERT INTO Registration " + "VALUES (100, 'Zara', 'Ali', 18)"

[Link](sql);
sql = "INSERT INTO Registration " + "VALUES (101, 'Mahnaz', 'Fatma', 25)"

[Link](sql);
sql = "INSERT INTO Registration " + "VALUES (102, 'Zaid', 'Khan', 30)"

[Link](sql);
sql = "INSERT INTO Registration " + "VALUES(103, 'Sumit', 'Mittal', 28)"

[Link](sql);
[Link]("Inserted records into the table...");

// STEP 4: Clean-up environment


[Link]();
[Link]();
} catch(SQLException se) {
// Handle errors for JDBC
[Link]();
} catch(Exception e) {
// Handle errors for [Link]
[Link]();
} finally {
// finally block used to close resources
try {
if(stmt!=null) [Link]();
} catch(SQLException se2) {
} // nothing we can do
try {
if(conn!=null) [Link]();
} catch(SQLException se) {
[Link]();
} // end finally try
} // end try
[Link]("Goodbye!");
}
}

Save the above program into [Link]. Compile and execute the above
program by executing the following commands in the command prompt.

\>javac [Link]
\>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 [Link];
import [Link];
import [Link];
import [Link];
import [Link];

public class H2jdbcReadDemo {


// JDBC driver name and database URL
static final String JDBC_DRIVER = "[Link]";
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
[Link](JDBC_DRIVER);

// STEP 2: Open a connection


[Link]("Connecting to database...");
conn = [Link](DB_URL,USER,PASS);

// STEP 3: Execute a query


[Link]("Connected database successfully...");
stmt = [Link]();
String sql = "SELECT id, first, last, age FROM Registration";
ResultSet rs = [Link](sql);

// STEP 4: Extract data from result set


while([Link]()) {
// Retrieve by column name
int id = [Link]("id");
int age = [Link]("age");
String first = [Link]("first");
String last = [Link]("last");

// Display values
[Link]("ID: " + id);
[Link](", Age: " + age);
[Link](", First: " + first);
[Link](", Last: " + last);
}
// STEP 5: Clean-up environment
[Link]();
} catch(SQLException se) {
// Handle errors for JDBC
[Link]();
} catch(Exception e) {
// Handle errors for [Link]
[Link]();
} finally {
// finally block used to close resources
try {
if(stmt!=null) [Link]();
} catch(SQLException se2) {
} // nothing we can do
try {
if(conn!=null) [Link]();
} catch(SQLException se) {
[Link]();
} // end finally try
} // end try
[Link]("Goodbye!");
}
}

Save the above program into [Link]. Compile and execute the above
program by executing the following commands in the command prompt.

\>javac [Link]
\>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!

Update Records
In this example, we will write a program to update records. Let us try to read all records
from the table Registration.
Following is an example program named H2jdbcUpdateDemo.

import [Link];
import [Link];
import [Link];
import [Link];
import [Link];

public class H2jdbcUpdateDemo {


// JDBC driver name and database URL
static final String JDBC_DRIVER = "[Link]";
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
[Link](JDBC_DRIVER);

// STEP 2: Open a connection


[Link]("Connecting to a database...");
conn = [Link](DB_URL,USER,PASS);

// STEP 3: Execute a query


[Link]("Connected database successfully...");
stmt = [Link]();
String sql = "UPDATE Registration " + "SET age = 30 WHERE id in (100, 101)"
[Link](sql);

// Now you can extract all the records


// to see the updated records
sql = "SELECT id, first, last, age FROM Registration";
ResultSet rs = [Link](sql);

while([Link]()){
// Retrieve by column name
int id = [Link]("id");
int age = [Link]("age");
String first = [Link]("first");
String last = [Link]("last");

// Display values
[Link]("ID: " + id);
[Link](", Age: " + age);
[Link](", First: " + first);
[Link](", Last: " + last);
}
[Link]();
} catch(SQLException se) {
// Handle errors for JDBC
[Link]();
} catch(Exception e) {
// Handle errors for [Link]
[Link]();
} finally {
// finally block used to close resources
try {
if(stmt!=null) [Link]();
} catch(SQLException se2) {
} // nothing we can do
try {
if(conn!=null) [Link]();
} catch(SQLException se) {
[Link]();
} // end finally try
} // end try
[Link]("Goodbye!");
}
}

Save the above program into [Link]. Compile and execute the above
program by executing the following commands in the command prompt.

\>javac [Link]
\>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 Records
In this example, we will write a program to delete records. Let us try to read all records from
the table Registration.
Following is an example program named H2jdbcDeleteDemo.

import [Link];
import [Link];
import [Link];
import [Link];
import [Link];

public class H2jdbcDeleteDemo {


// JDBC driver name and database URL
static final String JDBC_DRIVER = "[Link]";
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
[Link](JDBC_DRIVER);

// STEP 2: Open a connection


[Link]("Connecting to database...");
conn = [Link](DB_URL,USER,PASS);

// STEP 3: Execute a query


[Link]("Creating table in given database...");
stmt = [Link]();
String sql = "DELETE FROM Registration " + "WHERE id = 101";
[Link](sql);

// Now you can extract all the records


// to see the remaining records
sql = "SELECT id, first, last, age FROM Registration";
ResultSet rs = [Link](sql);

while([Link]()){
// Retrieve by column name
int id = [Link]("id");
int age = [Link]("age");
String first = [Link]("first");
String last = [Link]("last");

// Display values
[Link]("ID: " + id);
[Link](", Age: " + age);
[Link](", First: " + first);
[Link](", Last: " + last);
}
[Link]();
} catch(SQLException se) {
// Handle errors for JDBC
[Link]();
} catch(Exception e) {
// Handle errors for [Link]
[Link]();
} finally {
// finally block used to close resources
try {
if(stmt!=null) [Link]();
} catch(SQLException se2) {
} // nothing we can do
try {
if(conn!=null) [Link]();
} catch(SQLException se) {
[Link]();
} // end finally try
} // end try
[Link]("Goodbye!");
}
}

Save the above program into [Link]. Compile and execute the above
program by executing the following commands in the command prompt.

\>javac [Link]
\>java H2jdbcDeleteDemo

The above command produces the following output.

Connecting to a selected database...


Connected database successfully...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Goodbye!

Previous Page Next Page  

Advertisements
PostgreSQL For Business -
Fujitsu

[Link]/fujitsu/postgresql

Click Here To See FUJITSU Enterprise Postgres.

About us
©
Terms of use

Cookies Policy

FAQ's

Helping
Contact
Copyright 2019. All Rights Reserved.

You might also like