步骤
实现此案例需要按照如下步骤进行。
步骤一:导入连接Oracle数据库所需的jar包
创建工程,在当前工程下导入连接Oracle数据库对应的驱动程序jar包。
步骤二:新建类EmpDAO及方法findAll
代码如下所示:
- public class EmpDAO {
- public static void main(String[] args) {
- }
-
- public void findAll() {
- }
- }
步骤三:构建连接数据所需的对象以及相应的异常处理
在findAll方法中,构建连接数据所需的对象以及相应的异常处理,代码如下所示:
- public class EmpDAO {
- public static void main(String[] args) {
- }
-
- public void findAll() {
- Connection con = null;
- Statement stmt = null;
- ResultSet rs = null;
-
- try {
- Class.forName("oracle.jdbc.OracleDriver");
-
- } catch (ClassNotFoundException e) {
- System.out.println("驱动类无法找到!");
- throw new RuntimeException(e);
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- throw new RuntimeException(e);
- }
- }
- }
步骤四:装载驱动程序
代码如下所示:
- public class EmpDAO {
- public static void main(String[] args) {
- }
-
- public void findAll() {
- Connection con = null;
- Statement stmt = null;
- ResultSet rs = null;
-
- try {
- Class.forName("oracle.jdbc.OracleDriver");
- } catch (ClassNotFoundException e) {
- System.out.println("驱动类无法找到!");
- throw new RuntimeException(e);
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- throw new RuntimeException(e);
- }
- }
- }
步骤五:建立连接。
通过调用DriverManager的getConnection方法,获取Connection类的对象,建立连接。代码如下所示:
- public class EmpDAO {
- public static void main(String[] args) {
- }
-
- public void 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("驱动类无法找到!");
- throw new RuntimeException(e);
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- throw new RuntimeException(e);
- }
- }
- }
步骤六:发送和执行SQL语句
首先,通过Connection的createStatement()方法获取数据库操作对象Statement。通过调用Statement对象的executeQuery方法来执行SQL语句。代码如下所示:
- public class EmpDAO {
- public static void main(String[] args) {
- }
-
- public void 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 empno, ename, sal, hiredate from emp");
- } catch (ClassNotFoundException e) {
- System.out.println("驱动类无法找到!");
- throw new RuntimeException(e);
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- throw new RuntimeException(e);
- }
- }
- }
步骤七:处理查询结果
Statement的executeQuery方法的返回值为ResultSet对象。ResultSet表示数据库查询操作的结果集。它具有指向其当前数据行的光标。最初,光标被置于第一行之前,调用其next 方法将光标移动到下一行,该方法在 ResultSet 对象没有下一行时返回 false,因此可以在 while 循环中使用它来迭代结果集。代码如下所示:
- public class EmpDAO {
- public static void main(String[] args) {
- }
-
- public void 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 empno, ename, sal, hiredate from emp");
- while (rs.next()) {
- System.out.println(rs.getInt("empno") + ","
- + rs.getString("ename") + ","
- + rs.getDouble("sal") + "," + rs.getDate("hiredate"));
- }
- } catch (ClassNotFoundException e) {
- System.out.println("驱动类无法找到!");
- throw new RuntimeException(e);
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- throw new RuntimeException(e);
- }
- }
- }
从上述代码中看出ResultSet提供了getXXX(String column)方法,例如:getInt(String column)等,获取当前ResultSet对象的当前行中指定列名的值,其中参数column表示数据库表中的列名字。
步骤八:释放资源
在finally块中,依次关闭ResultSet对象、Statement对象以及Connection对象。代码如下:
- public class EmpDAO {
- public static void main(String[] args) {
- }
-
- public void 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 empno, ename, sal, hiredate from emp");
- while (rs.next()) {
- System.out.println(rs.getInt("empno") + ","
- + rs.getString("ename") + ","
- + rs.getDouble("sal") + "," + rs.getDate("hiredate"));
- }
- } catch (ClassNotFoundException e) {
- System.out.println("驱动类无法找到!");
- throw new RuntimeException(e);
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- throw new 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方法,代码如下所示:
- public class EmpDAO {
- public static void main(String[] args) {
- EmpDAO dao = new EmpDAO();
- dao.findAll();
- }
-
- public void 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 empno, ename, sal, hiredate from emp");
- while (rs.next()) {
- System.out.println(rs.getInt("empno") + ","
- + rs.getString("ename") + ","
- + rs.getDouble("sal") + "," + rs.getDate("hiredate"));
- }
- } catch (ClassNotFoundException e) {
- System.out.println("驱动类无法找到!");
- throw new RuntimeException(e);
- } catch (SQLException e) {
- System.out.println("数据库访问异常!");
- throw new 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("关闭连接时发生异常");
- }
- }
- }
- }
运行EmpDAO类,控制台输出结果如下所示:
- 7369,SMITH,,800.0,1980-12-17
- 7499,ALLEN,,1600.0,1981-02-20
- 7521,WARD,,1250.0,1981-02-22
- 7566,JONES,,2975.0,1981-04-02
- 7654,MARTIN,,1250.0,1981-09-28
- 7698,BLAKE,,2850.0,1981-05-01
- 7782,CLARK,,2450.0,1981-06-09
- 7788,SCOTT,,3000.0,1987-04-19
- 7839,KING,,5000.0,1981-11-17
- 7844,TURNER,,1500.0,1981-09-08
- 7876,ADAMS,,1100.0,1987-05-23
- 7900,JAMES,,950.0,1981-12-03
- 7902,FORD,,3000.0,1981-12-03
- 7934,MILLER,,1300.0,1982-01-23
从输出结果可以看出,已经查询到emp表的所有员工的ID、姓名、薪资以及入职时间。