H2 Database 简明教程

H2 Database - JDBC Connection

H2 是一个 JAVA 数据库。我们可以使用 JDBC 与此数据库进行交互。在本章中，我们将看到如何使用 JDBC 连接 H2 数据库以及 H2 数据库的 CRUD 操作。

通常，有五步来创建 JDBC 连接。

Step 1 − 注册 JDBC 数据库驱动程序。

Class.forName ("org.h2.Driver");

Step 2 − 打开连接。

Connection conn = DriverManager.getConnection ("jdbc:h2:~/test", "sa","");

Step 3 − 创建一个语句。

Statement st = conn.createStatement();

Step 4 − 执行语句并接收 Resultset。

Stmt.executeUpdate("sql statement");

Step 5 − 关闭连接。

conn.close();

在继续创建完整程序之前，我们需要将 h2-1.4.192.jar file 添加到 CLASSPATH。我们可以从文件夹中获得这个 jar C:\Program Files (x86)\H2\bin 。

Create Table

在这个例子中，我们将编写一个创建表的程序。考虑一张名为 Registration 的表，它有以下字段。

S.No

Column Name

Data Type

NOT NULL

Primary Key

Number

Yes

First

Varchar(255)

Last

Varchar(255)

Age

Number

以下是一个名为 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!");
   }
}

将上述程序保存到 H2jdbcCreateDemo.java 中。通过在命令提示符中执行以下命令编译并执行上述程序。

\>javac H2jdbcCreateDemo.java
\>java H2jdbcCreateDemo

以上命令会产生以下输出。

Connecting to database...
Creating table in given database...
Created table in given database...
Goodbye!

在此执行后，我们可以使用 H2 SQL 界面检查创建的表。

Insert Records

在这个例子中，我们将编写一个插入记录的程序。让我们将以下记录插入到注册表中。

ID	First	Last	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Khan	30
103	Sumit	Mital	28

以下是一个名为 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!");
   }
}

将上述程序保存到 H2jdbcInsertDemo.java 中。通过在命令提示符中执行以下命令编译并执行上述程序。

\>javac H2jdbcInsertDemo.java
\>java H2jdbcInsertDemo

以上命令会产生以下输出。

Connecting to a selected database...
Connected database successfully...
Inserted records into the table...
Goodbye!

Read Record

在这个例子中，我们将编写一个读取记录的程序。让我们尝试从表 Registration 中读取所有记录。

以下是一个名为 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 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!");
   }
}

将以上程序保存到 H2jdbcReadDemo.java 中。在命令提示符中执行以下命令来编译和执行上述程序。

\>javac H2jdbcReadDemo.java
\>java H2jdbcReadDemo

以上命令会产生以下输出。

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

在这个示例中，我们将编写一个程序来更新记录。让我们尝试从表 Registration 中读取所有记录。

以下是一个名为 H2jdbcUpdateDemo 的示例程序。

import java.sql.Connection;
import java.sql.DriverManager;
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 = DriverManager.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!");
   }
}

将以上程序保存到 H2jdbcUpdateDemo.java 中。在命令提示符中执行以下命令来编译和执行上述程序。

\>javac H2jdbcUpdateDemo.java
\>java H2jdbcUpdateDemo

以上命令会产生以下输出。

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

在这个示例中，我们将编写一个程序来删除记录。让我们尝试从表 Registration 中读取所有记录。

以下是一个名为 H2jdbcDeleteDemo 的示例程序。

import java.sql.Connection;
import java.sql.DriverManager;
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 = DriverManager.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!");
   }
}

将以上程序保存到 H2jdbcDeleteDemo.java 中。在命令提示符中执行以下命令来编译和执行上述程序。

\>javac H2jdbcDeleteDemo.java
\>java H2jdbcDeleteDemo

以上命令会产生以下输出。

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!