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("关闭连接时发生异常");
}
}
}
}