H2 数据库 – JDBC 连接
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 – 关闭连接。
conn.close();
在继续创建完整程序之前，我们需要将h2-1.4.192.jar 文件添加到 CLASSPATH。我们可以从文件夹C:\Program Files (x86)\H2\bin 中获取这个jar。
创建表
在这个例子中，我们将编写一个创建表的程序。考虑一个名为Registration的表，它具有以下字段。
| S.No | 列名 | 数据类型 | 非空 | 首要的关键 |
|---|---|---|---|---|
| 1 | ID | 数字 | 是的 | 是的 |
| 2 | 第一的 | 瓦尔查尔(255) | 不 | 不 |
| 3 | 最后的 | 瓦尔查尔(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!
