H2 JDBC连接
H2 是一个 JAVA 数据库。我们可以使用 JDBC 与这个数据库进行交互。在本章中,我们将看到如何创建与 H2 数据库的 JDBC 连接以及与 H2 数据库的 CRUD 操作。
通常,创建 JDBC 连接有五个步骤。
步骤 1-注册 JDBC 数据库驱动程序。
Class.forName ("org.h2.Driver");
步骤 2-打开连接。
Connection conn = DriverManager.getConnection ("jdbc:h2:~/test", "sa","");
步骤 3-创建语句。
Statement st = conn.createStatement();
步骤 4-执行语句并接收结果集。
Stmt.executeUpdate("sql statement");
步骤 5-关闭连接。
在继续创建完整程序之前,我们需要将
h2-1.4.192.jar 文件添加到 CLASSPATH。我们可以从文件夹
C:\Program Files (x86)\H2\bin 中获取这个
jar。
创建表
在这个例子中,我们将编写一个创建表的程序。考虑一个名为
Registration 的表,它具有以下字段。
S.No |
列名 |
数据类型 |
NOT NULL |
主键 |
1 |
身份证 |
数量 |
是 |
是 |
2 |
第一次 |
Varchar(255) |
没有 |
没有 |
3 |
最后 |
Varchar(255) |
没有 |
没有 |
4 |
年龄 |
数量 |
没有 |
没有 |
以下是一个名为
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接口创建的表。
插入记录
在这个例子中,我们将编写一个插入记录的程序。让我们将以下记录插入到表Registration中。
ID |
第一 |
最后 |
年龄 |
100 |
扎拉 |
阿里 |
18 |
101 |
马赫纳兹 |
法特玛 |
25 |
102 |
扎伊德 |
汗 |
30 |
103 |
顶 |
米塔尔 |
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!
读取记录
在这个例子中,我们将编写一个读取记录的程序。让我们尝试从
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!
更新记录
在这个例子中,我们将编写一个程序来更新记录。让我们尝试从
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!
删除记录
在这个例子中,我们将编写一个程序来删除记录。让我们尝试从
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!