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 。
创建表
在此示例中,无涯教程将编写一个用于创建表的程序,以下是一个名为 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: 注册 JDBC 驱动程序 Class.forName(JDBC_DRIVER); //STEP 2: 打开连接 System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); //STEP 3: 执行查询 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:关闭连接 stmt.close(); conn.close(); } catch(SQLException se) { //处理 JDBC 错误 se.printStackTrace(); } catch(Exception e) { //处理 Class.forName 的错误 e.printStackTrace(); } finally { //finally 块用于关闭资源 try{ if(stmt!=null) stmt.close(); } catch(SQLException se2) { } 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表中。
以下是一个名为 H2jdbcInsertDemo 的示例程序。
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class H2jdbcInsertDemo { //JDBC 驱动程序名称和数据库 URL static final String JDBC_DRIVER = "org.h2.Driver"; static final String DB_URL = "jdbc:h2:~/test"; //数据库凭据 static final String USER = "sa"; static final String PASS = ""; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ //STEP 1: 注册 JDBC 驱动程序 Class.forName(JDBC_DRIVER); //STEP 2: 打开连接 System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); System.out.println("Connected database successfully..."); //STEP 3: 执行查询 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: 关闭链接 stmt.close(); conn.close(); } catch(SQLException se) { //处理 JDBC 错误 se.printStackTrace(); } catch(Exception e) { //处理 Class.forName 的错误 e.printStackTrace(); } finally { //finally 块用于关闭资源 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!
读取数据
在此示例中,无涯教程将编写一个用于读取记录的程序,让无涯教程尝试从表注册中读取所有记录。
以下是一个名为 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!
更新数据
在此示例中,无涯教程将编写一个程序来更新记录,让无涯教程尝试从表注册中读取所有记录。
以下是一个名为 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!
删除数据
在此示例中,无涯教程将编写一个程序来删除记录,让无涯教程尝试从表注册中读取所有记录。
以下是一个名为 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 驱动程序名称和数据库 URL static final String JDBC_DRIVER = "org.h2.Driver"; static final String DB_URL = "jdbc:h2:~/test"; //数据库凭据 static final String USER = "sa"; static final String PASS = ""; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { //STEP 1: 注册 JDBC 驱动程序 Class.forName(JDBC_DRIVER); //STEP 2: 打开连接 System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); //STEP 3: 执行查询 System.out.println("Creating table in given database..."); stmt = conn.createStatement(); String sql = "DELETE FROM Registration " + "WHERE id=101"; stmt.executeUpdate(sql); //现在您可以提取所有记录 //查看剩余记录 sql = "SELECT id, first, last, age FROM Registration"; ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ //按列名检索 int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); //显示值 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!
参考链接
https://www.learnfk.com/h2/h2-database-jdbc-connection.html
标签:JDBC,java,String,H2,无涯,stmt,sql,conn From: https://blog.51cto.com/u_14033984/8163886