利用oracle内置JVM跟触发器提高性能！

最新推荐文章于 2023-02-01 09:48:10 发布

congluosuo9156

最新推荐文章于 2023-02-01 09:48:10 发布

阅读量142

点赞数

文章标签：数据库 java

无意中发现一篇文章讲解oracle内置有JVM! you can achieve a performance improvement by utilizing it. How much of an improvement? Some tests have shown that JDBC operations utilizing the internal OracleJVM compared to an external JVM can increase performance by 600%, which is quite impressive.

以下是实例：

1：脚本 firstclass.java.sql

create or replace java source named FirstClass as

public class FirstClass{

public static String greeting(String name){

return "Hello " + name + "!";

}

create or replace function firstclass_greeting (name varchar2) return varchar as

language java name 'FirstClass.greeting(java.lang.String) return java.lang.String';

Now when you load firstclass.java.sql into SQLPlus and execute it, then you should see the following results, as shown in Listing 2.

Listing 2：

SQL> @firstclass.java.sql

Java created.

Function created.

So now we have our class loaded in the OracleJVM, and we also have a function that maps to our class's static method. So let's call that function and see what happens:

listing 3：

SQL> select firstclass_greeting('Bruce') from dual; FIRSTCLASS_GREETING('BRUCE') -------------------------------------------------------------------------------- Hello Bruce!

Creating the Book Reorder Trigger

脚本create_bookstore_tables.sql

drop table books

drop table publisher_supply_orders
/

create table books(
        book_id                 number  primary key,
        publisher_id    number,
        page_count              number,
        author_name             varchar2(50),
        book_title              varchar2(50),
        description             varchar2(500),
        status                  varchar2(10),
        inventory_qty   number
)
/

insert into books values(100, 200, 234, 'Bruce Hopkins', 'Bluetooth for Java', 'great book', 'IN STOCK', 10);
insert into books values(101, 200, 401, 'Sam Jones', 'Living on the East Coast', 'worth every penny', 'IN STOCK', 50);
insert into books values(102, 250, 278, 'Max Jason', 'The South of France', 'a best-seller', 'IN STOCK', 20);

create table publisher_supply_orders(
        book_id                 number,
        publisher_id    number,
        order_quantity  number
)
/

   So, as you can imagine, the business logic for our internal trigger is very simple. Whenever the books table is updated, check to see if the books.inventory_qty field is less than a particular threshold. If the books.inventory_qty is less than the threshold (which I set to be 5 books), then reorder the book by inserting a new row in the publisher_supply_orders table. At a later time, a batch process can read all the entries in the publisher_supply_orders and place the actual orders with the publishers at the one time. This way, you can aggregate the orders with the individual publishers, which is a lot more efficient compared to submitting the orders directly to the publishers for each title one at a time. The code located in Listing 5, located below creates the Java class, the PL/SQL wrapper, as well as the trigger that the database will use to call your Java class's static method.

脚本ReorderTrigger.java.sql:

        create or replace java source named "ReorderTrigger" as

        import java.sql.*;
        import oracle.jdbc.driver.*;
 
        public class ReorderTrigger {

                public static int REORDER_THRESHOLD = 5;
                public static int REORDER_QTY = 25;
 
                public static void reorderBooks(Integer bookID, Integer publisherID, Integer inventoryQty) {

                        if(inventoryQty.intValue() < REORDER_THRESHOLD){
                        
                                try {
                                Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
                                PreparedStatement prep = conn.prepareStatement("insert into publisher_supply_orders values(?,?,?)");
 
                                prep.setInt(1, bookID);
                                prep.setInt(2, publisherID);
                                prep.setInt(3, REORDER_QTY);
 
                                prep.executeUpdate();
                                prep.close();
                                conn.close();

                                } catch (Exception e){

                                }
                        }

                }
        }
/

create or replace procedure procedure_reorderbooks(bookID number, publisherID number, inventoryQty number) as

        language java name 'ReorderTrigger.reorderBooks(java.lang.Integer, java.lang.Integer)';

/

create or replace trigger trigger_reorderbooks
before update on books
for each row
begin
    procedure_reorderbooks(:new.book_id, :new.publisher_id, :new.inventory_qty);
end;
/

    As you can see in Listing 5, when your Java code executes from within the 
OracleJVM you still use all the JDBC classes and paradigms that you would use as 
if your code executed outside of the database. You should notice that the 
connection String that allows you to utilize the internal JDBC driver in the 
OracleJVM is "jdbc:default:connection:". Additionally, you should 
notice that we're creating a procedure as our PL/SQL wrapper for our Java method 
since we're not returning a result from the method call. After you execute the 
ReorderTrigger.java.sql file, you should see the results shown in 
Listing 6, below.

       SQL> @ReorderTrigger.java.sql  Java created.  Procedure created.  Trigger created.

  So now that we have all the components in place for the trigger to execute,

let's update the books table so that the inventory_qty is less than the threshold of 5 books. Figure 4 shows one of the rows of sample data in the books table to be modified under the threshold, and Figure 5 shows the new row that was inserted in the publisher_supply_orders table automatically by our trigger.

Conclusion

Whew! We've covered a lot of material today. The Oracle database is a powerful development platform. that not only allows developers to store and query relational data, but it also includes a JVM, which enables developers to create powerful server-side applications. Trust me, we've only scratched the surface here... Stay tuned for more.

Oracle JVM Version.jpg