结合使用 JRuby 和 Oracle Database

作者：Pas Apicella 和 Chris Jones

2011 年 3 月发布

本文介绍如何将 JRuby 编程语言与 Oracle Database 11g 第 2 版结合使用。JRuby 通过强大、易用的脚本语言来增强 Java 平台和内容广泛的库。

软件要求

可以使用以下软件：

这些示例还将适用于其他一些版本。

安装每个组件时需遵循推荐的安装说明。

演示安装

在终端窗口中，设置 Oracle 环境并验证已安装了 Oracle 标准的人力资源 (HR) 演示模式。

$ sqlplus hr/welcome
[. . .]
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
COUNTRIES
JOBS
EMPLOYEES
LOCATIONS
DEPARTMENTS
JOB_HISTORY
REGIONS

如果该模式不可用，请参阅 Oracle Database Sample Schemas 11g Release 2 (11.2) 指南中的 Installing the HR Schema。

验证是否已安装 JDK 1.6：

$ java -version
java version "1.6.0_23"
Java(TM) SE Runtime Environment (build 1.6.0_23-b05)
Java HotSpot(TM) 64-Bit Server VM (build 19.0-b09, mixed mode)

将 JRUBY_HOME 复制到 JRuby 的安装目录，并验证 JRuby 是否运行：

$ export JRUBY_HOME=$HOME/jruby-1.5.6
$ export PATH=$PATH:$JRUBY_HOME/bin
$ jruby -v

jruby 1.5.6 (ruby 1.8.7 patchlevel 249) (2010-12-03 9cf97c3) (Java HotSpot(TM) 64-Bit Server VM 1.6.0_23) [amd64-java]

将 Oracle JDBC 驱动程序 ojdbc6.jar 复制到 $JRUBY_HOME/lib 目录中：

$ ls -l $JRUBY_HOME/lib
total 20084
-rw-r--r--   1 pas     usergrp  8102598 Dec  3 08:58 jruby.jar
drwxr-xr-x  13 pas     usergrp     4096 Dec  3 08:57 native
-rw-r--r--   1 pas     usergrp  2152051 Feb  7 17:34 ojdbc6.jar
drwxr-xr-x   6 pas     usergrp     4096 Dec  3 08:58 ruby

如果安装了 JDK 1.5，请改为复制 ojdbc5.jar。

运行演示

示例代码使用 JRuby 和 Oracle Database 演示了一些不同的数据操作。

jruby_plsql.sql 文件创建一个 PL/SQL 程序包以演示如何通过 JRuby 调用 PL/SQL：

-- jruby_plsql.sql

create or replace package emp_pack as
  function get_employee_name (p_employee_id in number) return varchar2;
  procedure delete_department (p_department_id in number);
end emp_pack;
/
show errors;

create or replace package body emp_pack as

  function get_employee_name (p_employee_id in number) return varchar2 is
    l_name varchar2(46) := null;
  begin
      select first_name || ' ' || last_name
        into (l_name)
        from employees
        where employee_id = p_employee_id;
      return l_name;
  end get_employee_name;

  procedure delete_department (p_department_id in number) is
  begin
    delete from departments where department_id = p_department_id;
    commit;
  end delete_department;

end emp_pack;
/
show errors;

可以使用 SQL*Plus 安装该程序包：

$ sqlplus hr/welcome
SQL> @jruby_plsql.sql

Package created.

No errors.

Package body created.

No errors.

通过显式调用该程序包来验证安装：

SQL> select emp_pack.get_employee_name(199) from dual;

EMP_PACK.GET_EMPLOYEE_NAME(199)
-----------------------------------
Douglas Grant

jdbc_connection.rb 文件包含用于通过 Oracle JDBC 驱动程序连接到数据库的代码。

该文件创建一个新的 JRuby 类 OracleConnection，该类公开 DriverManager（负责通过注册的子协议 OracleDriver 连接到 Oracle）的属性。

# jdbc_connection.rb

require 'java'

java_import 'oracle.jdbc.OracleDriver'
java_import 'java.sql.DriverManager'

class OracleConnection

  @conn = nil

  def initialize (user, passwd, url)
    @user = user
    @passwd = passwd
    @url = url

    # Load driver class
    oradriver = OracleDriver.new

    DriverManager.registerDriver oradriver
    @conn = DriverManager.get_connection url, user, passwd
    @conn.auto_commit = false

  end

  # Add getters and setters for all attrributes we wish to expose
  attr_reader :user, :passwd, :url, :connection

  def close_connection()
    @conn.close() unless @conn
  end

  def prepare_call(call)
    @conn.prepare_call call
  end

  def create_statement()
    @conn.create_statement
  end

  def prepare_statement(sql)
    @conn.prepare_statement sql
  end

  def commit()
    @conn.commit
  end

  def self.create(user, passwd, url)
    conn = new(user, passwd, url)
  end

  def to_s
    "OracleConnection [user=#{@user}, url=#{@url}]"
  end
  alias_method :to_string, :to_s

end

对 test_oracle.rb 进行编辑，定义 HR 模式用户名、口令和连接字符串。此示例显示数据库在本地计算机上运行，服务名称为 orcl。

该文件显示 SQL 和 PL/SQL 的四种常见用法。

# test_oracle.rb

require 'jdbc_connection'

# Edit these for your database schema
user   = "hr"
passwd = "welcome"
url    = "jdbc:oracle:thin:@localhost/orcl"

# Helpers to call our PL/SQL package
PLSQL_BLOCK_FUNCTION_CALL = <<EOF
begin
  :1 := emp_pack.get_employee_name(:2);
end;
EOF

PLSQL_BLOCK_PROCEDURE_CALL = <<EOF
begin
  emp_pack.delete_department(:1);
end;
EOF

print "Run at #{Time.now} using JRuby #{RUBY_VERSION}\n\n"

insert_stmt, select_stmt, rest, delete_stmt, function_stmt = nil

begin
  conn = OracleConnection.create(user, passwd, url)
  # Display connection using the to_s method of OracleConnection
  puts conn, "\n"

  # Insert a new Department
  puts "1. Inserting department 281 into the DEPARTMENTS table"
  insert_sql = "insert into departments values (:1, :2, NULL, NULL)"
  insert_stmt = conn.prepare_statement(insert_sql)
  insert_stmt.set_int    1, 281
  insert_stmt.set_string 2, "JRuby Department"
  insert_stmt.execute
  conn.commit

  # Select all departments
  puts "2. Showing all DEPARTMENTS rows"
  select_sql = "select department_id, department_name from departments order by 1"
  select_stmt = conn.create_statement
  rset = select_stmt.execute_query select_sql
  while (rset.next)
    puts "    Department [#{rset.getInt(1)}, #{rset.getString(2)}]"
  end

  # Call a PL/SQL package function to display an employee name
  puts "3. Finding name of employee 200"
  function_stmt = conn.prepare_call PLSQL_BLOCK_FUNCTION_CALL
  function_stmt.set_int 2, 200
  function_stmt.register_out_parameter 1, 12
  function_stmt.execute_update
  puts "    Employee 200 is #{function_stmt.get_string 1}\n"

  # Call a PL/SQL package procedure to delete a department record
  puts "4. Deleting department 281 from the DEPARTMENTS table"
  delete_stmt = conn.prepare_call PLSQL_BLOCK_PROCEDURE_CALL
  delete_stmt.set_int 1, 281
  delete_stmt.execute_update

rescue
    puts "\n** Error occured **\n"
    puts "Failed executing Oracle demo from JRuby ", $!, "\n"

ensure
   # We have to ensure everything is closed here.
   if (!insert_stmt.nil?)
    insert_stmt.close
   end
   if (!select_stmt.nil?)
    select_stmt.close
   end
   if (!rset.nil?)
    rset.close
   end
   if (!delete_stmt.nil?)
    delete_stmt.close
   end
   if (!function_stmt.nil?)
    function_stmt.close
   end

   conn.close_connection
end

print "\nEnded at #{Time.now}\n"

建立连接后，该文件使用公开的 OracleConnection 属性显示这些数据库操作：

• 插入一个新部门 281。部门标识符和姓名是绑定的，从而能够高效地使用数据库资源。
• 查询所有部门记录。
• 调用一个 PL/SQL 函数，查找某个员工姓名。该姓名作为 PLSQL_BLOCK_FUNCTION_CALL 中匿名 PL/SQL 块的“OUT”参数返回。
• 通过调用一个 PL/SQL 过程来删除新创建的部门 281。

通过 JRuby 运行测试程序：

$ jruby test_oracle.rb
Run at Fri Feb 11 14:54:25 -0800 2011 using JRuby 1.8.7

OracleConnection [user=hr, url=jdbc:oracle:thin:@localhost/orcl]

1. Inserting department 281 into the DEPARTMENTS table
2. Showing all DEPARTMENTS rows
    Department [10, Administration]
    Department [20, Marketing]
    Department [30, Purchasing]
    Department [40, Human Resources]
    Department [50, Shipping]
    Department [60, IT]
    Department [70, Public Relations]
    Department [80, Sales]
    Department [90, Executive]
    Department [100, Finance]
    Department [110, Accounting]
    Department [120, Treasury]
    Department [130, Corporate Tax]
    Department [140, Control And Credit]
    Department [150, Shareholder Services]
    Department [160, Benefits]
    Department [170, Manufacturing]
    Department [180, Construction]
    Department [190, Contracting]
    Department [200, Operations]
    Department [210, IT Support]
    Department [220, NOC]
    Department [230, IT Helpdesk]
    Department [240, Government Sales]
    Department [250, Retail Sales]
    Department [260, Recruiting]
    Department [270, Payroll]
    Department [281, JRuby Department]
3. Finding name of employee 200
    Employee 200 is Jennifer Whalen
4. Deleting department 281 from the DEPARTMENTS table

Ended at Fri Feb 11 14:54:32 -0800 2011

如果测试更改为插入部门 100 而非 281，那么将显示一个错误堆栈：

** Error occurred **
Failed executing Oracle demo from JRuby
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (HR.DEPT_ID_PK) violated

恭喜，您刚刚已经学习了如何使用 JRuby 访问 Oracle 11g 第 2 版数据库！

Pas Apicella [pas.apicella@oracle.com] 是首席技术支持工程师，Chris Jones [christopher.jones@oracle.com] 是 Oracle 的咨询人员。