jdbc mysql api_JDBC核心API

3 JDBC实现对Dept数据表的简单查询(Oracle)

Oracle数据库中部门dept表的表结构如表－1所示：

表－1部门表dept 信息

部门表 dept中的示例数据，如图－1所示：

图－1

本案例要求使用JDBC连接Oracle数据库，查询dept表的所有部门的ID、部门名称以及部门所在地。

参考答案

实现此案例需要按照如下步骤进行。

步骤一：导入连接Oracle数据库所需的jar包

创建工程，在当前工程下导入连接Oracle数据库对应的驱动程序jar包。

步骤二：新建类DeptDAO及方法findAll

代码如下所示：

publicclass DeptDAO {

publicstaticvoid main(String[] args) {

}

publicvoid findAll() {

}

}

步骤三：构建连接数据库所需的对象以及相应的异常处理

在findAll方法中，构建连接库数据所需的对象以及相应的异常处理，代码如下所示：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

publicclass DeptDAO {

publicstaticvoid main(String[] args) {

}

publicvoid findAll() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

} catch (ClassNotFoundException e) {

System.out.println("驱动类无法找到!");

thrownew RuntimeException(e);

} catch (SQLException e) {

System.out.println("数据库访问异常!");

thrownew RuntimeException(e);

}

}

}

步骤四：装载驱动程序

代码如下所示：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

publicclass DeptDAO {

publicstaticvoid main(String[] args) {

}

publicvoid findAll() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

Class.forName("oracle.jdbc.OracleDriver");

} catch (ClassNotFoundException e) {

System.out.println("驱动类无法找到!");

thrownew RuntimeException(e);

} catch (SQLException e) {

System.out.println("数据库访问异常!");

thrownew RuntimeException(e);

}

}

}

步骤五：建立连接

通过调用DriverManager的getConnection方法，获取Connection类的对象，建立连接。代码如下所示：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

publicclass DeptDAO {

publicstaticvoid main(String[] args) {

}

publicvoid findAll() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

Class.forName("oracle.jdbc.OracleDriver");

con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");

} catch (ClassNotFoundException e) {

System.out.println("驱动类无法找到!");

thrownew RuntimeException(e);

} catch (SQLException e) {

System.out.println("数据库访问异常!");

thrownew RuntimeException(e);

}

}

}

步骤六：发送和执行SQL语句

首先，通过Connection的createStatement()方法获取数据库操作对象Statement。通过调用Statement对象的executeQuery方法来执行SQL语句。代码如下所示：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

publicclass DeptDAO {

publicstaticvoid main(String[] args) {

}

publicvoid findAll() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

Class.forName("oracle.jdbc.OracleDriver");

con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");

stmt = con.createStatement();

rs = stmt

.executeQuery("select deptno,dname,loc from dept");

} catch (ClassNotFoundException e) {

System.out.println("驱动类无法找到!");

thrownew RuntimeException(e);

} catch (SQLException e) {

System.out.println("数据库访问异常!");

thrownew RuntimeException(e);

}

}

}

步骤七：处理查询结果

Statement的executeQuery方法的返回值为ResultSet对象。ResultSet表示数据库查询操作的结果集。它具有指向其当前数据行的光标。最初，光标被置于第一行之前，调用其next 方法将光标移动到下一行，该方法在 ResultSet 对象没有下一行时返回 false，因此可以在 while 循环中使用它来迭代结果集。代码如下所示：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

publicclass DeptDAO {

publicstaticvoid main(String[] args) {

}

publicvoid findAll() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

Class.forName("oracle.jdbc.OracleDriver");

con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");

stmt = con.createStatement();

rs = stmt

.executeQuery("select deptno,dname,loc from dept");

while (rs.next()) {

System.out.println(rs.getInt("deptno") + ","

+ rs.getString("dname") + ","

+ rs.getString("loc"));

}

} catch (ClassNotFoundException e) {

System.out.println("驱动类无法找到!");

thrownew RuntimeException(e);

} catch (SQLException e) {

System.out.println("数据库访问异常!");

thrownew RuntimeException(e);

}

}

}

从上述代码中看出ResultSet提供了getXXX(String column)方法，例如：getInt(String column)等，获取当前ResultSet对象的当前行中指定列名的值，其中参数column表示数据库表中的列名字。

步骤八：释放资源

在finally块中，依次关闭ResultSet对象、Statement对象以及Connection对象。代码如下：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

publicclass DeptDAO {

publicstaticvoid main(String[] args) {

}

publicvoid findAll() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

Class.forName("oracle.jdbc.OracleDriver");

con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");

stmt = con.createStatement();

rs = stmt

.executeQuery("select deptno,dname,loc from dept");

while (rs.next()) {

System.out.println(rs.getInt("deptno") + ","

+ rs.getString("dname") + ","

+ rs.getString("loc"));

}

} catch (ClassNotFoundException e) {

System.out.println("驱动类无法找到!");

thrownew RuntimeException(e);

} catch (SQLException e) {

System.out.println("数据库访问异常!");

thrownew RuntimeException(e);

} finally {

try {

if (rs != null) {

rs.close();

}

if (stmt != null) {

stmt.close();

}

if (con != null) {

con.close();

}

} catch (SQLException e) {

System.out.println("关闭连接时发生异常");

}

}

}

}

步骤九：测试

在main方法中，调用findAll方法，代码如下所示：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

publicclass DeptDAO {

publicstaticvoid main(String[] args) {

DeptDAO dao = new DeptDAO();

dao.findAll();

}

publicvoid findAll() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

Class.forName("oracle.jdbc.OracleDriver");

con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");

stmt = con.createStatement();

rs = stmt

.executeQuery("select deptno,dname,loc from dept");

while (rs.next()) {

System.out.println(rs.getInt("deptno") + ","

+ rs.getString("dname") + ","

+ rs.getString("loc"));

}

} catch (ClassNotFoundException e) {

System.out.println("驱动类无法找到!");

thrownew RuntimeException(e);

} catch (SQLException e) {

System.out.println("数据库访问异常!");

thrownew RuntimeException(e);

} finally {

try {

if (rs != null) {

rs.close();

}

if (stmt != null) {

stmt.close();

}

if (con != null) {

con.close();

}

} catch (SQLException e) {

System.out.println("关闭连接时发生异常");

}

}

}

}

运行DeptDAO类，控制台输出结果如下所示：

10,ACCOUNTING,NEW YORK

20,RESEARCH,DALLAS

30,SALES,CHICAGO

40,OPERATIONS,BOSTON

从输出结果可以看出，已经查询到dept表中的所有部门的ID、名称、所在地。

本案例的完整代码如下所示：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

publicclass DeptDAO {

publicstaticvoid main(String[] args) {

DeptDAO dao = new DeptDAO();

dao.findAll();

}

publicvoid findAll() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

Class.forName("oracle.jdbc.OracleDriver");

con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");

stmt = con.createStatement();

rs = stmt

.executeQuery("select deptno,dname,loc from dept");

while (rs.next()) {

System.out.println(rs.getInt("deptno") + ","

+ rs.getString("dname") + ","

+ rs.getString("loc"));

}

} catch (ClassNotFoundException e) {

System.out.println("驱动类无法找到!");

thrownew RuntimeException(e);

} catch (SQLException e) {

System.out.println("数据库访问异常!");

thrownew RuntimeException(e);

} finally {

try {

if (rs != null) {

rs.close();

}

if (stmt != null) {

stmt.close();

}

if (con != null) {

con.close();

}

} catch (SQLException e) {

System.out.println("关闭连接时发生异常");

}

}

}

}