Archive for the ‘Java’ Category
Java Subclass with Struct
This blog post relies on the “How to write a Java Class with the SQLData Interface” blog post. Oracle object types support subclasses with the UNDER keyword from Oracle Database 10g forward. Naturally, you can create Java classes that subclass another Java class.
A subclass is a specialized behavior of a parent class. Parent classes are considered more generalized than their subclasses. There are certain rules and behaviors for using Java subclasses as Oracle object subtypes. That’s why we wrote this section.
The two subsections show you how to implement a Java subclass and how to implement an Oracle object subtype.
Creating a Java Subclass
Like Java classes and Oracle object types, you must implement the Java class file first, and the PL/SQL wrapper second. Java subclasses rely on the existence of their parent, and object subtypes rely on the existence of their parent.
The following ItemSt class is a subclass or specialization of the Java Item class. You should take note that it doesn’t redefine the attributes of the parent class. The ItemSt class does add one new attribute, so we can demonstrate how Java subclasses or Oracle subtypes work.
SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ItemSt" AS 2 // JAVA LIBRARY imports. 3 import JAVA.SQL.*; 4 import JAVA.io.*; 5 import oracle.SQL.*; 6 import oracle.jdbc.*; 7 import oracle.oracore.*; 8 import oracle.jdbc2.*; 9 import JAVA.math.*; 10 11 PUBLIC class ItemSt EXTENDS Item implements SQLData 12 { 13 // Implement the attributes AND operations FOR this TYPE. 14 PRIVATE String bluray; 15 16 // A getter FOR the rating attribute. 17 PUBLIC String getBluray() { 18 RETURN this.bluray; } 19 20 // A setter FOR the rating attribute. 21 PUBLIC void setBluray(String bluray) { 22 this.bluray = NEW String(bluray); } 23 24 // A setter FOR this object. 25 PUBLIC void setItem(Struct item) throws JAVA.SQL.SQLException { 26 27 // Assign Item instance variables. 28 super.setItem(item); 29 30 // Get the attributes OF the Item object. 31 Object[] attributes = (Object[]) item.getAttributes(); 32 33 // Assign the SUBTYPE element. 34 this.bluray = (String) attributes[6]; } 35 36 // DECLARE an instance toString method. 37 PUBLIC String toString() { 38 RETURN super.toString() + "Blu-ray [" + this.bluray +"]\n"; } 39 40 /* Implement SQLData interface. 41 || -------------------------------------------------------- 42 || Required interface components: 43 || ============================== 44 || 1. String sql_type instance variable. 45 || 2. getSQLTypeName() method returns the sql_type value. 46 || 3. readSQL() method to read from the Oracle session. 47 || 4. writeSQL() method to write to the Oracle session. 48 */ 49 50 // Required INTERFACE variable. 51 PRIVATE String sql_type; 52 53 // Reads the stream FROM the Oracle SESSION. 54 PUBLIC void readSQL(SQLInput stream, String typeName) throws SQLException { 55 56 // CALL TO parent class. 57 super.readSQL(stream, typeName); 58 59 // Map instance variables. 60 sql_type = typeName; 61 bluray = stream.readString(); } 62 63 // Writes the stream TO the Oracle SESSION. 64 PUBLIC void writeSQL(SQLOutput stream) throws SQLException { 65 // CALL TO parent class. 66 super.writeSQL(stream); 67 68 // Map instance variables. 69 stream.writeString(bluray); } 70 71 /* 72 || -------------------------------------------------------- 73 || End Implementation of SQLData interface. 74 */ 75 } 76 / |
Lines 25 through 34 show you how to handle a Struct of the ItemSt object type. This is where we implement a specialized setItem() method. You should note that in line 28 the subclass calls the parent class (or super) setItem() method. The balance of the setItem() method converts the Struct to an array of objects and then assigns the correct element to the
bluray variable.
Lines 38 and 39 show you how to handle the toString() method of a subclass. Like the setItem() method, the toString() method must call super to get the behavior of the parent
before adding on its own behavioral characteristics. Lines 57 and 66 also make super calls to the
readSQL() and writeSQL() methods of the parent class.
You should discover from this example that a subclass depends on the program logic in the parent class. Also, you should see that subclass methods generally override behaviors of equivalent methods in the subclass.
Testing the Java Subclass
Testing subclass behavior is also known as testing polymorphic behavior. Polymorphic behavior occurs when a subclass performs differently from its parent and sibling classes.
We need to slip one more example in here, and that’s a stand-alone function that constructs either a super class or subclass. That’s done with the following get_item function. You should note two things about the get_item function. First, it takes a list of parameters necessary to create an item_obj_st instance. Second, it makes any subtype-only parameters optional parameters in the function’s signature.
SQL> CREATE OR REPLACE FUNCTION get_item 2 ( id NUMBER 3 , title VARCHAR2 4 , subtitle VARCHAR2 5 , rating VARCHAR2 6 , rating_agency VARCHAR2 7 , release_date DATE 8 , bluray VARCHAR2 DEFAULT NULL ) 9 RETURN item_obj IS 10 /* Declare a local variable. */ 11 lv_item ITEM_OBJ; 12 BEGIN 13 /* Check for the subtype attribute. */ 14 IF bluray IS NULL THEN 15 lv_item := item_obj( id 16 , title 17 , subtitle 18 , rating 19 , rating_agency 20 , release_date); 21 ELSE 22 lv_item := item_obj_st( id 23 , title 24 , subtitle 25 , rating 26 , rating_agency 27 , release_date 28 , bluray); 29 END IF; 30 31 /* Return a type. */ 32 RETURN lv_item; 33 END; 34 / |
Line 8 provides the bluray attribute, which only belongs to the item_obj_st object type. The bluray parameter is optional because an item_obj instance can ignore that parameter. The IF statement on line 14 checks whether the subtype’s bluray variable is null. The get_item function creates an item_obj instance when the bluray variable is null and an item_obj_st when the bluray variable isn’t null.
The simplest way to test polymorphic behavior is an anonymous PL/SQL block. The following constructs item_obj and item_obj_st instances by leveraging our get_item function, and it assigns the result from the get_item function to a collection of the generalized item_obj class. Then, the anonymous block reads through the collection in a range FOR loop.
SQL> DECLARE 2 /* Declare a generalized object type. */ 3 lv_item_tab ITEM_TAB := item_tab(); 4 BEGIN 5 /* Assign an object to a generalization. */ 6 lv_item_tab.EXTEND; 7 lv_item_tab(lv_item_tab.COUNT) := 8 get_item( id => 2 9 , title => 'The Hobbit' 10 , subtitle => 'The Desolation of Smaug' 11 , rating => 'PG-13' 12 , rating_agency => 'MPAA' 13 , release_date => '13-DEC-2013'); 14 15 /* Assign an object to a specialization. */ 16 lv_item_tab.EXTEND; 17 lv_item_tab(lv_item_tab.COUNT) := 18 get_item( id => 3 19 , title => 'The Hobbit' 20 , subtitle => 'The Battle of the Five Armies' 21 , rating => 'PG-13' 22 , rating_agency => 'MPAA' 23 , release_date => '17-DEC-2014' 24 , bluray => 'Sony Blu-ray'); 25 26 /* Print header line. */ 27 dbms_output.put_line( 28 '---------------------------------------------'); 29 30 /* Print items in collection. */ 31 FOR i IN 1..lv_item_tab.COUNT LOOP 32 /* Print the contents of the item_obj. */ 33 parse_rows(lv_item_tab(i).to_string()); 34 dbms_output.put_line( 35 '---------------------------------------------'); 36 END LOOP; 37 END; 38 / |
For reference, the parse_rows procedure is in the “How to write a Java Class with the SQLData Interface” blog post.
Lines 8 through 14 assign an item_obj instance to the lv_item_tab collection, and lines 18 through 26 assign an item_obj_st instance to the lv_item_tab collection. Line 35 uses the parse_rows procedure.
It should print:
--------------------------------------------- ID # [2] Title [The Hobbit: The Desolation of Smaug] Rating [MPAA:PG-13] Release [2013-12-13] --------------------------------------------- ID # [3] Title [The Hobbit: The Battle of the Five Armies] Rating [MPAA:PG-13] Release [2014-12-17] Blu-ray [Sony Blu-ray] --------------------------------------------- |
The first element prints the item_obj output, and the second element prints the item_obj_st output. Please take note that Oracle determined which to run based on their base object type, and you didn’t need to write any specialized instanceOf logic.
This collection of articles should have enabled you to learn the necessary skills to implement Java classes inside the Oracle Database. The SQLData Interface affords the ability to read and write to the database and those methods may be replaced by other more direct methods using the JDBC standard approaches.
Java Class with Struct
If you’re new to writing Java classes that use the SQLData interface, inside the Oracle Database, please read the blog post on: “How to build a Java Class with the SQLData Interface”. This is a much larger example that shows you how to implement a Struct as a parameter option and how to work with other input and output data types.
Building the Java Object Class
The Item Java source file provides us with a lot of topics to cover. We debated about breaking the code up for display, but decided against it. In this section, we simply show you a complete Java
example class. We did limit the example to one getter, the getRating() method, and one setter, the setRating() method, for single attribute values.
We also gave you a getName() method that lets you capture the Java class name, and a setItem() method that lets you set the list of class attributes with a Struct object type. The setItem() method shows you how to set the instance values through a direct method call rather than the readSQL() method implementation of the SQLData interface.
The following SQL statement creates the Item class in an Oracle Database as follows:
SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Item" AS 2 3 // JAVA LIBRARY imports. 4 import JAVA.SQL.*; 5 import JAVA.io.*; 6 import oracle.SQL.*; 7 import oracle.jdbc.*; 8 import oracle.oracore.*; 9 import oracle.jdbc2.*; 10 import JAVA.math.*; 11 12 PUBLIC class Item implements SQLData 13 { 14 // Implement the attributes AND operations FOR this TYPE. 15 PRIVATE BigDecimal id; 16 PRIVATE String title; 17 PRIVATE String subtitle; 18 PRIVATE String rating; 19 PRIVATE String ratingAgency; 20 PRIVATE DATE releaseDate; 21 22 // A getter FOR the rating attribute. 23 PUBLIC String getRating() { 24 RETURN this.rating; } 25 26 // A getter FOR the class instance name. 27 PUBLIC String getName() { 28 RETURN this.getClass().getName(); } 29 30 // A getter FOR the rating attribute. 31 PUBLIC String getUserName() { 32 33 // DECLARE local variables. 34 String userName = NEW String(); 35 String query = "SELECT user"; 36 37 // CREATE a connection discover a USER name. 38 try { 39 // DECLARE an Oracle connection. 40 OracleConnectionWrapper conn = 41 (oracle.jdbc.OracleConnectionWrapper) 42 DriverManager.getConnection("jdbc:default:connection:"); 43 44 // Prepare AND EXECUTE a statement. 45 JAVA.SQL.PreparedStatement ps = conn.prepareStatement(query); 46 ResultSet rs = ps.executeQuery(); 47 48 // Read the result SET. 49 WHILE (rs.next()) 50 userName = rs.getString(1); } 51 catch (SQLException e) {} 52 // RETURN a USER name. 53 RETURN userName; } 54 55 // A setter FOR this object. 56 PUBLIC void setItem(Struct item) throws JAVA.SQL.SQLException { 57 58 // Get the attributes OF the Item object. 59 Object[] attributes = (Object[]) item.getAttributes(); 60 61 // Assign Item instance variables. 62 this.id = (BigDecimal) attributes[0]; 63 this.title = (String) attributes[1]; 64 this.subtitle = (String) attributes[2]; 65 this.rating = (String) attributes[3]; 66 this.ratingAgency = (String) attributes[4]; 67 this.releaseDate = NEW DATE(((TIMESTAMP) attributes[5]).getTime()); } 68 69 // A setter FOR the rating attribute. 70 PUBLIC void setRating(String rating) { 71 this.rating = rating; } 72 73 // DECLARE an instance toString method. 74 PUBLIC String toString() { 75 RETURN "ID # [" + this.id + "]\n" + "Title [" + this.title + ": " + this.subtitle +"]\n" + 76 "Rating [" + this.ratingAgency + ":" + this.rating + "]\n" + 77 "Release [" + this.releaseDate + "]\n"; } 78 79 /* Implement SQLData interface. 80 || -------------------------------------------------------- 81 || Required interface components: 82 || ============================== 83 || 1. String sql_type instance variable. 84 || 2. getSQLTypeName() method returns the sql_type value. 85 || 3. readSQL() method to read from the Oracle session. 86 || 4. writeSQL() method to write to the Oracle session. 87 */ 88 89 // Required INTERFACE variable. 90 PRIVATE String sql_type; 91 92 // Returns the INTERFACE required variable VALUE. 93 PUBLIC String getSQLTypeName() throws SQLException { 94 RETURN this.sql_type; } 95 96 // Reads the stream FROM the Oracle SESSION. 97 PUBLIC void readSQL(SQLInput stream, String typeName) throws SQLException { 98 99 // Map instance variables. 100 this.sql_type = typeName; 101 this.id = stream.readBigDecimal(); 102 this.title = stream.readString(); 103 this.subtitle = stream.readString(); 104 this.rating = stream.readString(); 105 this.ratingAgency = stream.readString(); 106 this.releaseDate = stream.readDate(); } 107 108 // Writes the stream TO the Oracle SESSION. 109 PUBLIC void writeSQL(SQLOutput stream) throws SQLException { 110 111 // Map instance variables. 112 stream.writeBigDecimal(this.id); 113 stream.writeString(this.title); 114 stream.writeString(this.subtitle); 115 stream.writeString(this.rating); 116 stream.writeString(this.ratingAgency); 117 stream.writeDate(this.releaseDate); } 118 119 /* 120 || -------------------------------------------------------- 121 || End Implementation of SQLData interface. 122 */ 123 } 124 / |
The generic getter on lines 23 and 24 lets you get the instance value of the rating attribute, and the generic setter on lines 70 and 71 lets you override the rating instance variable. Lines 56 through 67 contain the logic for the setItem method. The setItem method takes a Struct parameter, which illustrates how you can use a composite data type. Line 57 converts the fields of the Struct parameter to an array of objects. Most of the conversions are straightforward and map through simple casting operations based on the Oracle Database rules. A date data type in the Oracle database is a DATE data type. While you may think that an Oracle DATE data type should convert to a Java Date class, it doesn’t. An Oracle DATE inside a Struct converts to a Timestamp class. You must cast the Java Timestamp class to a Java Date class through a two-step method. First, use the getTime() method to capture the long value of the Timestamp. Second, construct a new Date class by using the long value result from the getTime() method.
Line 67 from the preceding Item class example demonstrate the required Oracle DATE to Java Timestamp technique:
67 | this.releaseDate = new Date(((Timestamp) attributes[5]).getTime()); } |
Line 90 declares the sql_type variable. Lines 93 and 94 implement the getSQLTypeName() method for the SQLData interface. Lines 97 through 106 implement the readSQL() method, and lines 109 through 117 implement the writeSQL() method for the SQLData interface.
After creating and compiling the Item class, you need to create an object type for it. The object type wraps access to the Java class file.
Creating the PL/SQL Object Type
The Item object type provides us with the syntax for mapping object methods against native Java classes. As a refresher, you create object types with a CREATE TYPE statement. Then, you can deploy them as persistent or transient objects. Persistent objects can be either stand-alone or embedded objects. Stand-alone objects are stored in a database table and have a unique identifier. Embedded objects are not stored in a database table but are embedded in another Oracle structure, like a stored program parameter, return type, or as an attribute of another object type. Transient objects are instances of objects that aren’t stored in the database. They have a lifetime limited to the duration of their use in a PL/SQL block.
You create an instance of an object type and use an INSERT or UPDATE statement to assign it as a persistent object in a table. Alternatively, you can retrieve an object type from an IN OUT or OUT-only mode parameter, or get one as the output parameter of a function.
The following CREATE TYPE statement creates the object declaration in the session scope. You should note that each object type’s methods point to valid Java methods from the same Java class.
SQL> CREATE OR REPLACE TYPE item_obj IS OBJECT 2 ( id NUMBER 3 , title VARCHAR2(60) 4 , subtitle VARCHAR2(60) 5 , rating VARCHAR2(8) 6 , rating_agency VARCHAR2(4) 7 , release_date DATE 8 , MEMBER FUNCTION get_rating RETURN VARCHAR2 9 AS LANGUAGE JAVA 10 NAME 'Item.getRating() return java.lang.String' 11 , MEMBER FUNCTION get_name RETURN VARCHAR2 12 AS LANGUAGE JAVA 13 NAME 'Item.getName() return java.lang.String' 14 , MEMBER FUNCTION get_sql_type RETURN VARCHAR2 15 AS LANGUAGE JAVA 16 NAME 'Item.getSQLTypeName() return java.lang.String' 17 , MEMBER FUNCTION get_user_name RETURN VARCHAR2 18 AS LANGUAGE JAVA 19 NAME 'Item.getUserName() return java.lang.String' 20 , MEMBER PROCEDURE set_item (item ITEM_OBJ) 21 AS LANGUAGE JAVA 22 NAME 'Item.setItem(java.sql.Struct)' 23 , MEMBER PROCEDURE set_rating (rating VARCHAR2) 24 AS LANGUAGE JAVA 25 NAME 'Item.setRating(java.lang.String)' 26 , MEMBER FUNCTION to_string RETURN VARCHAR2 27 AS LANGUAGE JAVA 28 NAME 'Item.toString() return java.lang.String') 29 INSTANTIABLE NOT FINAL; 30 / |
Lines 2 through 7 declare a list of attributes. The physical size assigned to the VARCHAR2 data types limits the size of the Java String attributes. Any attempt to pass a variable-length string longer than the imposed limit in the PL/SQL object type definition raises a “character string buffer too small” error.
Line 20 shows a set_item procedure that passes a SQL item_obj object type, which maps to a java.sql.Struct on line 22. All the other parameters and return types use Java String classes.
The next section shows you how to test the Java class instance by calling the PL/SQL object type in various contexts.
Testing the Java Class File
This section shows you how to insert instances of the item object type into an item_struct table, and how to query the persistent item_obj object instance from the item_struct table. Then, this section shows you how to work with transient object instances in an anonymous PL/SQL block.
The following CREATE TABLE statement creates the item_struct sample table:
SQL> CREATE TABLE item_struct 2 ( item_struct_id NUMBER DEFAULT item_struct_id_seq.NEXTVAL 3 , item_struct ITEM_OBJ); |
You can construct and insert an item_obj object instance to the item_struct table with the following syntax:
SQL> INSERT INTO item_struct VALUES 2 ( item_struct_s.NEXTVAL 3 , item_obj( 1 4 ,'The Hobbit' 5 ,'An Unexpected Journey' 6 ,'PG-13' 7 ,'MPAA' 8 ,'14-DEC-2012')); |
Lines 3 through 8 create an instance of (instantiate) the item_obj type. The instance is assigned as a flattened object to the VALUES clause of the INSERT statement, and is inserted into the item_struct table.
Any attempt to access a single object type from a table in a query returns
ORA-22905: cannot ACCESS ROWS FROM a non-nested TABLE item |
To avoid this error, you create an item_tab or table collection of item_obj with the following syntax:
SQL> CREATE OR REPLACE 2 TYPE item_tab IS TABLE OF item_obj; 3 / |
Now, you can query persistent copies of the item_obj data type like this:
SQL> COLUMN title FORMAT A36 HEADING "Title" SQL> COLUMN rating FORMAT A6 HEADING "Rating" SQL> COLUMN rating_agency FORMAT A6 HEADING "Rating|Agency" SQL> COLUMN release_date FORMAT A9 HEADING "Release|Date" SQL> SELECT title||': '||subtitle AS title 2 , rating 3 , rating_agency 4 , release_date 5 FROM item_struct 6 CROSS JOIN 7 TABLE( 8 SELECT CAST(COLLECT(item_struct) AS item_tab)); |
The combination of the SQL COLLECT, CAST, and TABLE built-in functions on lines 7 and 8 let us query persistent objects. Combining these SQL functions with a CROSS JOIN lets us write a SELECT statement to query persistent object types. The preceding query prints the following when you have one or more rows in the item_struct table:
Rating Release Title Rating Agency Date ------------------------------------ ------ ------ --------- The Hobbit: An Unexpected Journey PG-13 MPAA 14-DEC-12 |
An anonymous PL/SQL block provides a more robust test case solution. The following shows you how to create and assign an object type to a local variable, and shows you how to test the getter functions and setter procedures:
SQL> DECLARE 2 /* Create an object type instance. */ 3 lv_item ITEM_OBJ := 4 item_obj( 1 5 ,'The Hobbit' 6 ,'An Unexpected Journey' 7 ,'PG-13' 8 ,'MPAA' 9 ,'14-DEC-2012'); 10 BEGIN 11 /* Print the getter rating result. */ 12 dbms_output.put_line( 13 '---------------------------------------------'); 14 dbms_output.put_line( 15 'Rating Value: ['||lv_item.get_rating()||']'); 16 17 /* Set the value of the rating. */ 18 lv_item.set_rating('PG'); 19 20 /* Print the getter rating result. */ 21 dbms_output.put_line( 22 'Rating Value: ['||lv_item.get_rating()||']'); 23 dbms_output.put_line( 24 '---------------------------------------------'); 25 26 /* Print user name and sql_type value. */ 27 dbms_output.put_line( 28 'User Name: ['||lv_item.get_user_name()||']'); 29 dbms_output.put_line( 30 'Class Name: ['||lv_item.get_name()||']'); 31 dbms_output.put_line( 32 'Object Name: ['||lv_item.get_sql_type()||']'); 33 34 /* Print the toString value. */ 35 dbms_output.put_line( 36 '---------------------------------------------'); 37 parse_rows(lv_item.to_string()); 38 dbms_output.put_line( 39 '---------------------------------------------'); 40 END; 41 / |
Lines 3 through 9 create an instance of the item_obj object type and assign the instance to the lv_item local variable. Lines 14 and 15 print the current instance’s rating value, line 18 resets the instance’s rating value, and lines 21 and 22 print the modified rating value. These three activities demonstrate a call to the getter of an object type’s attribute before and after the call to the setter to reset the value of an object type’s attribute.
Lines 27 through 32 show how you can call several of the other getter functions, like the get_user function. The get_userto_string procedure of the lv_item local variable as a parameter to the parse_rows procedure.
The toString() methods of Java classes often return long strings. The parse_rows procedure lets you avoid the forced line breaks imposed by the dbms_output package. Wrapping text in a call to the following parse_rows procedure lets you avoid the 80-character limit of the put_line procedures:
SQL> CREATE OR REPLACE PROCEDURE parse_rows 2 ( pv_text VARCHAR2 ) IS 3 /* Declare parsing indexes. */ 4 lv_start NUMBER := 1; 5 lv_end NUMBER := 1; 6 lv_length NUMBER; 7 BEGIN 8 /* Assign an end value by parsing line return. */ 9 IF INSTR(pv_text,CHR(10),lv_start) = 0 THEN 10 lv_end := LENGTH(pv_text) + 1; 11 ELSE 12 lv_end := INSTR(pv_text,CHR(10),lv_start); 13 END IF; 14 15 /* Assign a length value to parsed strings. */ 16 lv_length := lv_end - lv_start; 17 18 /* Print first line. */ 19 dbms_output.put_line(SUBSTR(pv_text,lv_start,lv_length)); 20 21 /* Print the rows of a multiple line string. */ 22 WHILE (lv_end < LENGTH(pv_text)) LOOP 23 24 /* Assign a new start and end value. */ 25 lv_start := lv_end + 1; 26 IF INSTR(pv_text,CHR(10),lv_start + 1) = 0 THEN 27 lv_end := LENGTH(pv_text) + 1; 28 ELSE 29 lv_end := INSTR(pv_text,CHR(10),lv_start + 1); 30 END IF; 31 lv_length := lv_end - lv_start; 32 33 /* Print the individual rows. */ 34 dbms_output.put_line(SUBSTR(pv_text,lv_start,lv_length)); 35 36 END LOOP; 37 END; 38 / |
It prints the following:
--------------------------------------------- Rating Value: [PG-13] Rating Value: [PG] --------------------------------------------- User Name: [VIDEO] Class Name: [Item] Object Name: [VIDEO.ITEM_OBJ] --------------------------------------------- ID # [1] Title [The Hobbit: An Unexpected Journey] Rating [MPAA:PG] Release [2012-12-14] --------------------------------------------- |
The SELECT statement and anonymous PL/SQL blocks have shown you how to work with and test Oracle object types, that insert and query data. The next step would require you to learn how to use object subclasses, which are in the “How to write a Java Subclass with the SQLData Interface” blog post.
Build SQLData Java Class
If you’re new to writing Java inside the Oracle Database, please read the following two blog posts in sequence first: How to build a Deterministic Java Library and how to build a JDBC-Enabled Java Library.
Implementing the SQLData interface is done by providing a variable definition and three concrete methods in your Java class file. The following are the required components:
- A String data type named sql_type.
- A getSQLTypeName() method that returns a String data type.
- A readSQL() method that takes two formal parameters and returns a void. One formal parameter is a SQLInput that contains a stream. The other is a string that contains a data type name.
- A writeSQL() method that takes one formal parameter, which is a SQLOutput that contains a stream.
Oracle Database 12c forward also provides the OracleData and OracleDataFactory interfaces. Applying the Factory pattern is inherently more complex. Since applying the pattern requires advanced Java coding skills, they’re not covered in this blog post.
The SQLData interface only maps SQL objects. The OracleData interface lets you map SQL objects as well as any other SQL data type. The OracleData interface is necessary when you want to serialize RAW data in Java libraries. Please check the Oracle Database JDBC Developer’s Guide for more information on the OracleData and OracleDataFactory interfaces.
Subsequent examples in this chapter show you how to implement runtime Java classes. The HelloWorld4.java class file shows you a minimalist Java library to support a runtime instance of the HelloWorld4 object.
The source code for the class is as follows (with line numbers added for your convenience
while reading the discussion of the code):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | // Oracle class imports. import java.sql.*; import java.io.*; import oracle.sql.*; import oracle.jdbc.*; import oracle.oracore.*; import java.math.*; // Needed for BigDecimal maps of NUMBER data type. // Class definition. public class HelloWorld4 implements SQLData { // Declare class instance variable. private String whom; // Declare getter for SQL data type value. public String getSQLTypeName() throws SQLException { return sql_type; } // Required interface variable. private String sql_type; // Implements readSQL() method from the SQLData interface. public void readSQL(SQLInput stream, String typeName) throws SQLException { sql_type = typeName; this.whom = stream.readString(); } // Implements writeSQL() method from the SQLData interface. public void writeSQL(SQLOutput stream) throws SQLException { stream.writeString(whom); } // Declare a toString method. public String toString() { String datatype = null; try { datatype = getSQLTypeName(); } catch (SQLException e) {} // Return message. return datatype + " says hello [" + this.whom + "]!\n"; } } |
The Java class implements the SQLData interface. You must create four elements to implement the SQLData interface. The first element requires declaring a private sql_type variable (declared on line 20). The sql_type variable holds an object type name defined in the Oracle Database. The second element, on lines 23 through 26, lets you read data from the database into the Java object instance through a SQLInput stream. The third element, on lines 29 and 30, lets you write any changes to the local copies of the data (held in the Java class instance) into the database. The getSQLTypeName() method lets you read the user-defined data type from the class. The sample code uses the getSQLTypeName() method to print a message from the toString() method.
If you have not built a test schema, you should do so now. The code in this example runs in either a container database (CDB) or a pluggable database (PDB). We’re using a video PDB database with a trivial video password.
The HelloWorld4.java program contains import statements that require you to place the ojdbcX.jar file (where X is the version required for your Oracle Database’s compatibility) in your CLASSPATH environment variable. If you have not done so, you should do so now. The file is found in your $ORACLE_HOME/jdbc/lib directory on Linux or Unix, and %ORACLE_HOME%\jdbc\lib directory on Windows.
You can directly load a Java source file with the loadjava utility as follows into a CDB
schema with the default listener:
loadjava -r -f -o -user video/video HelloWorld4.java |
A slight change is required when you load this into a PDB. The following shows you must add the TNS alias to the credentials, which in this case is also videodb:
loadjava -r -f -o -user video/video@videodb HelloWorld4.java |
The loadjava utility command behaves slightly differently when you choose this option. It parses, stores the Java source as a text entry, and compiles the stored Java source into a Java byte stream in the Oracle JVM under the videodb schema.
Creating the Java class file is only the first step. Next, you need to create an Oracle object type that wraps the Java class. Object types are declared as SQL data types and are implemented in PL/SQL, or callable languages such as Java.
Just a quick note: After loading the Java class file into the database this way, you won’t be able to use the dropjava utility to remove the HelloWorld4.class file. Instead, use the dropjava utility to remove the HelloWorld4.java file, which also drops the HelloWorld4.class file.
Building the Object Type Wrapper
You’ll need to build a SQL object type to wrap the Java stored object class. That means you have to connect to the Oracle database. You can connect to a CDB or a PDB.
Assuming you have a c##video CDB schema, connect as follows:
sqlplus c##video/password |
Assuming you have a videodb PDB schema, you connect as follows:
sqlplus video/password@videodb
The following HelloWorld4.sql script builds the object type as a wrapper to the Java class object:
SQL> CREATE OR REPLACE TYPE hello_world4 AS OBJECT 2 ( whom VARCHAR2(100) 3 , MEMBER FUNCTION get_sql_type_name 4 RETURN VARCHAR2 AS LANGUAGE JAVA 5 NAME 'HelloWorld4.getSQLTypeName() return java.lang.String'; 6 , MEMBER FUNCTION to_string 7 RETURN VARCHAR2 AS LANGUAGE JAVA 8 NAME 'HelloWorld4.toString() return java.lang.String'; ) 10 INSTANTIABLE FINAL; 11 / |
The SQL object type declares a local whom attribute. The whom attribute has a maximum length of 100 characters. Both line 5 and line 8 wrap the name of the Java class name, method, and return type. As signatures to your methods become longer, we recommend that you take advantage of the ability to break these strings across multiple lines for readability.
You can create a table collection of the HELLO_WORLD4 type with this syntax:
SQL> CREATE OR REPLACE 2 TYPE hello_tab IS TABLE OF HELLO_WORLD4; 3 / |
Now, you can test this in SQL with the following complex query:
SQL> SELECT * 2 FROM TABLE( 3 SELECT CAST(COLLECT(hello_world4('Bilbo')) AS hello_tab)); |
You must collect the object type into a table collection and cast it to a table collection of the hello_tab base type. Then, you use the TABLE function to return a SQL result set.
The query prints from the toString() method of the HelloeWorld4 class:
VIDEO.HELLO_WORLD4 says hello [Bilbo]! |
The SQLData interface allows you to pass a user-defined type (UDT), which means you can use any defined user structure. If you debug the execution of the Java instance, you’ll find that each invocation of the instance method actually reinstantiates the class instance.
JDBC-Enabled Java Library
This section shows you how to build Java libraries that interact with data in an Oracle Database and relies on this earlier blog post. Here you build Java methods to support both functions and procedures. Building a procedure follows a very similar rule to how you build functions.
While PL/SQL functions and procedures have IN, IN OUT, and OUT parameter modes, you can’t use IN OUT mode in PL/SQL when wrapping a scalar parameter in a Java method. All formal parameters are defined as IN mode only. When you want output back, you should write a PL/SQL function, not a procedure.
The Oracle Database raises the following exception when you attempt to define a packagebody with a procedure using IN OUT modes:
PLS-00235: the external TYPE IS NOT appropriate FOR the parameter |
In this section you create functions and procedures with IN-only mode parameters. The difference between how you implement a Java method to support a function or procedure is simple. A Java method returns any data type except a void to support a PL/SQL function wrapper, and a Java method returns only a void data type when supporting a PL/SQL procedure.
There are two subsections. The first shows you how to work with scalar data types, such as strings. The second shows you how to work with Attribute Data Types (ADTs), which are collections of scalar data types.
JDBC Enabled Using Scalar Data Types
This section revisits how you will create a Java library externally and use the loadjava utility to deploy a Java class file into the database. You should note that we’ve tried to conserve space and show how to bind variables, which means the code passes a SQL statement and parameter to each of these Java methods.
The following Java source file supports both a function and a procedure:
// Oracle class imports. import java.sql.*; import oracle.jdbc.driver.*; // Class definition. public class HelloWorld3 { public static void doDML(String statement ,String name) throws SQLException { // Declare an Oracle connection. Connection conn = DriverManager.getConnection("jdbc:default:connection:"); // Declare prepared statement, run query and read results. PreparedStatement ps = conn.prepareStatement(statement); ps.setString(1,name); ps.execute(); } public static String doDQL(String statement) throws SQLException { // Define and initialize a local return variable. String result = new String(); // Declare an Oracle connection. Connection conn = DriverManager.getConnection("jdbc:default:connection:"); // Declare prepared statement, run query and read results. PreparedStatement ps = conn.prepareStatement(statement); ResultSet rs = ps.executeQuery(); while (rs.next()) result = rs.getString(1); return result; } } |
This program creates a Java Connection instance using the Oracle Database’s internal process connection. The
process calls a static getConnection() method of the DriverManager class. The program implements two methods—one to insert records and another to query records. While these methods don’t use explicit cursors, statements and result sets persist across calls, and their finalizers do not release database cursors. You should remember to always close explicitly opened
cursors.
The INSERT statement returns a void, and the query returns a string.
There is no main() method in the HelloWorld3.java class file. Including a main() method to test the program externally to the database would require changing the connection to a client-side or OCI driver. As with the other Java libraries, HelloWorld3.java is a library file. Assuming you’re using a demo schema, you could load the Java class file into the Oracle JVM with the loadjava utility using the following:
loadjava -r -f -o -user video/video@video HelloWorld3.class |
The loadjava utility command loads the Java HelloWorld3 class file into the Oracle JVM under the PLSQL schema. After loading the Java class file into the database, you need to build an example table and PL/SQL wrapper to use it.
The following HelloWorld3.sql script builds the package and package body as a wrapper to the Java class library. The definition of the package specification is
SQL> CREATE OR REPLACE PACKAGE hello_world3 AS 2 PROCEDURE doDML 3 ( dml VARCHAR2 4 , input VARCHAR2 ); 5 6 FUNCTION doDQL 7 ( dql VARCHAR2 ) 8 RETURN VARCHAR2; 9 END hello_world3; 10 / |
The definition of the package body is
SQL> CREATE OR REPLACE PACKAGE BODY hello_world3 AS 2 PROCEDURE doDML 3 ( dml VARCHAR2 4 , input VARCHAR2 ) IS 5 LANGUAGE JAVA 6 NAME 'HelloWorld3.doDML(java.lang.String,java.lang.String)'; 7 8 FUNCTION doDQL 9 ( dql VARCHAR2 ) 10 RETURN VARCHAR2 IS 11 LANGUAGE JAVA 12 NAME 'HelloWorld3.doDQL(java.lang.String) return String'; 13 END hello_world3; 14 / |
This program defines two methods:
- The doDML procedure takes two formal parameters that are VARCHAR2 data types and returns nothing as a stored procedure.
- The doDQL function takes one formal parameter that is a VARCHAR2 and returns a VARCHAR2 data type as a stored function.
You can verify that all components are present to test by querying the user_objects view with the following:
SQL> COLUMN object_name FORMAT A30 SQL> SELECT object_name 2 , object_type 3 , status 4 FROM user_objects 5 WHERE object_name IN ('HelloWorld3','HELLO_WORLD3') 6 ORDER BY CASE 7 WHEN object_type = 'JAVA CLASS' THEN 1 8 WHEN object_type = 'PACKAGE' THEN 2 9 ELSE 3 10 END; |
The script should output the following results:
OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ----------------------- ------- HelloWorld3 JAVA CLASS VALID HELLO_WORLD3 PACKAGE VALID HELLO_WORLD3 PACKAGE BODY VALID |
If you did not get the same output, you’ll need to see what step you may have skipped. Please do this before attempting to proceed. If you did get the same output, you can now test the Java class library in SQL and PL/SQL.
You can test the doDML procedure inside an anonymous block program to write a string value to the example table, which you can create with the following command:
SQL> CREATE TABLE example 2 (character VARCHAR2(100)); |
Now, you can insert the “Bobby McGee” string with the following anonymous PL/SQL block:
SQL> BEGIN 2 hello_world3.dodml('INSERT INTO example VALUES (?)','Bobby McGee'); 3 END; 4 / |
After inserting a row into the example table, you can test the doDQL function. Running a query is the easiest way. The following illustrates a SQL query of the wrapper, which uses the internal Java class file:
SQL> SELECT hello_world3.doDQL('SELECT character FROM example') AS "Name"; |
The query returns the following results:
Name
-----------------------------------
Bobby McGee |
You’ve now covered how to build Oracle database instance-stored Java class files that map a Java method to a PL/SQL procedure. The next section discusses how to build real Java objects wrapped by PL/SQL object types.
JDBC Enabled Using ADT Collections
This section shows you how to create a Java library that receives and sorts an Attribute Data Type (ADT) collection of a scalar VARCHAR2 data type. It introduces how you can map an ADT collection into a Java collection, and how you can map the Java collection back into a PL/SQL collection type.
The following are four key classes available in Oracle’s JDBC implementation:
- oracle.sql.ARRAY
- oracle.sql.ArrayDescriptor
- oracle.sql.Datum
- oracle.sql.STRUCT
The ARRAY class lets you implicitly cast an ADT or user-defined type (UDT) collection into a Java context without explicit conversion. You can transfer the contents of an ADT by calling the getArray() method of the ARRAY class. To transfer an Oracle collection of a scalar data type to a generic Object collection requires you to use the java.util.Map class. You map the Oracle data type to its equivalent Java data type, assuming that the Java library designates the collection’s formal parameter as a list, like
Object[] object = (Object[])list.getArray(map); |
Alternatively, you can avoid the expense of converting the object type by using the Oracle-
specific Datum class, like
Datum[] arrayOracle = list.getOracleArray(); |
A subsequent blog post shows you the more complex approach required to map UDT collections to Java structures. The ArrayDescriptor classes let you map a Java data type into an Oracle data type. You can find more about Oracle collections that other blog post, where you will work with UDT collections, or you can check Chapter 16 of the Oracle Database JDBC Developer’s Guide.
The first step requires that you create an ADT collection. An ADT collection uses a scalar data type as its base type. Scalar data types in Oracle are like primitive data types in Java, and they’re more or less numbers, dates, and strings.
The following syntax creates a table of strings:
SQL> CREATE OR REPLACE 2 TYPE stringlist IS TABLE OF VARCHAR2(4000); 3 / |
While it would have been straightforward to create this without the use of Java generics, we wanted to make sure you knew they’re available to you. Actually, you can use Java generics from Oracle Database 11g forward.
The following SQL statement creates the SortList Java class, which has a single sortTitleCaseList() method. The Java long name is the same as the short name because of its length.
SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortList" AS 2 3 // Import required classes. 4 import java.io.*; 5 import java.security.AccessControlException; 6 import java.sql.*; 7 import java.util.Arrays; 8 import java.util.Comparator; 9 import oracle.sql.driver.*; 10 import oracle.sql.ArrayDescriptor; 11 import oracle.sql.ARRAY; 12 13 // Define class. 14 public class Sorting { 15 public static ARRAY sortTitleCaseList(oracle.sql.ARRAY list) 16 throws SQLException, AccessControlException { 17 18 // Convert Oracle data type to Java data type. 19 String[] unsorted = (String[])list.getArray(); 20 21 // Sort elements. 22 Arrays.sort(unsorted, new Comparator<String>() { 23 public int compare(String s1, String s2) { 24 25 // Declare a sorting key integer for the return value. 26 int sortKey; 27 28 // Check if lowercase words match and sort them. 29 if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0) 30 sortKey = s1.substring(0,1).compareTo(s2.substring(0,1)); 31 else 32 sortKey = s1.toLowerCase().compareTo(s2.toLowerCase()); 33 34 // Return the sorting index. 35 return sortKey; }}); 36 37 // Define a connection (this is for Oracle 11g). 38 Connection conn = 39 DriverManager.getConnection("jdbc:default:connection:"); 40 41 // Declare a mapping to the schema-level SQL collection type. 42 ArrayDescriptor arrayDescriptor = 43 new ArrayDescriptor("STRINGLIST",conn); 44 45 // Translate the Java String{} to the Oracle SQL collection type. 46 ARRAY sorted = 47 new ARRAY(arrayDescriptor,conn,((Object[])unsorted)); 48 49 // Return the sorted list. 50 return sorted; } 51 } 52 / |
Lines 10 and 11 import two key Oracle Java classes—the ARRAY and ArrayDescriptor classes. As discussed earlier in this section, the ARRAY class lets you transfer a collection of numbers, dates, strings, or any other scalar data type to a Java data type. Line 19 avoids using the java.util.Map class, shown earlier in this section, by explicitly designating the Java String data type as the natural equivalent of a VARCHAR2 data type. Line 22 implements a Java generic Comparator method that performs a natural language sort of String data type.
The ArrayDescriptor class implements a mediator pattern, and the declaration on lines 42 and 43 creates a map to the Oracle stringlist data type. Lines 46 and 47 construct an instance of the Oracle ARRAY class with a collection of the Java Object type, which is the most generalized class in the single-inheritance Java tree.
The PL/SQL wrapper for this Java library is
SQL> CREATE OR REPLACE 2 FUNCTION sortTitleCaseList(list STRINGLIST) RETURN STRINGLIST IS 3 LANGUAGE JAVA NAME 4 'Sorting.sortTitleCaseList(oracle.sql.ARRAY) return oracle.sql.ARRAY'; 5 / |
You can test this configuration with the following anonymous PL/SQL block:
SQL> DECLARE 2 /* Declare a counter. */ 3 lv_counter NUMBER := 1; 4 /* Declare a unordered collection of fruit. */ 5 lv_list STRINGLIST := stringlist('Oranges' 6 ,'apples' 7 ,'Apples' 8 ,'Bananas' 9 ,'Apricots' 10 ,'apricots'); 11 BEGIN 12 /* Read through an element list. */ 13 FOR i IN (SELECT column_value 14 FROM TABLE(sortTitleCaseList(lv_list))) LOOP 15 dbms_output.put_line('['||lv_counter||']['||i.column_value||']'); 16 lv_counter := lv_counter + 1; 17 END LOOP; 18 END; 19 / |
Line 14 has a call to the TABLE function that translates the returned collection type into a SQL result set. The anonymous PL/SQL block returns
[1][Apples] [2][apples] [3][Apricots] [4][apricots] [5][Bananas] [6][Oranges] |
The program also demonstrates how to accept Oracle collections into a Java program and how to return collections from a Java program to an Oracle collection.
Deterministic Java Library
In this post, you build an internal server Java function by building a Java class file that will use the server-side internal connection or JDBC thick connection. As qualified earlier in the chapter, the JDBC thick connection depends on OCI libraries. All OCI libraries are directly accessible from your Java class file when you’ve loaded it into the Oracle JVM.
Java internal or server-side class files are built and accessed by a three-step process. You use Java to build and compile the class file. Then, you use the Oracle loadjava utility to load the compiled class file into the server. Once the class file is built and loaded into the server, you build a PL/SQL wrapper to the Java class library. Deployment requires that you load the compiled Java class file into the database, which you do with the loadjava utility.
Building a Java Library
To build a Java library for the Oracle Database server, the CLASSPATH must resolve to an ojdbcX.jar file (where X maps to the version for the Oracle Database Server). You can modify the earlier javaenv.bat file to include a CLASSPATH reference to the directory that contains the ojdbcX.jar file:
C:>copy con javaenv.bat SET PATH=%ORACLE_HOME%\jdk\bin;%PATH% SET CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc6.jar;. ^Z 1 file(s) copied. |
You’ll get the following prompt when the javaenv.bat file already exists:
Overwrite javaenv.bat? (Yes/No/All): Yes |
Naturally, you have the alternative of using any text editor of your choice. Several free or inexpensive editors are available, but we recommend Sublime as an inexpensive editor or Notepad++ as a free editor.
The Linux or Unix operating system would require the following changes for a javaenv.sh Bash shell environment:
export set PATH=%ORACLE_HOME%/jdk/bin:%PATH% export set CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc6.jar:. |
You need to source the javaenv.bat or javaenv.sh file before compiling the Java source file. You source the Windows batch file by simply running it from the command line, and you must use the dot (.) in the Bash or Korn shell or the keyword source in Tcsh.
Java Database Connectivity (JDBC) lets you build connections by using the DriverManager class. This is a change over the defaultConnection() method for internal Java class files and external connections. They now both use the getConnection() static method from the DriverManager class. The only difference between a thin client and a thick client is the actual parameter provided to the method. Examples in this chapter use the internal syntax, and examples in the appendix use the external thin client syntax.
The following assumes you have the correct CLASSPATH and PATH to use Java. If you are unable to compile or test the Java programs, it’s possible your environment is configured incorrectly.
// Oracle class imports. import oracle.jdbc.driver.*; // Class definition. public class HelloWorld2 { public static String hello() { return "Hello World."; } public static String hello(String name) { return "Hello " + name + "."; } public static void main(String args[]) { System.out.println(HelloWorld2.hello()); System.out.println(HelloWorld2.hello("Larry")); } } |
The program defines two overloaded hello methods. One takes no formal parameters and the other takes one. You can compile HelloWorld2.java with the following syntax:
javac HelloWorld2.java |
After you compile and run this program with this syntax:
java HelloWorld2 |
the method without any formal parameters always prints
Hello World. |
while the one that takes one formal parameter always prints
Hello Larry. |
This happens because the static main() method always sends either no parameter or the same actual parameter to the dynamic method. As a rule, you want to remove testing components like the main() method before loading them into the database and pass actual parameters to dynamic methods. You can leave the static main() method in the program because it harms nothing and enables your testing of the program with the ojvmjava interactive utility.
Deploying a Java Library
You deploy a Java library with the loadjava utility, which is run from the operating system’s command-line interface. This step requires the HelloWorld2.class file from the prior section. You should note that sometimes problems can occur with the loadjava utility. If you run into problems with the loadjava utility, check the “Building, Loading, and Dropping Java Class Library Objects” below in this blog post.
Deploying a Java library also requires a demo schema, which may be a container database (CDB) or a pluggable database (PDB). If you’re unfamiliar with the difference between a CDB and PDB, the former refers to the base container and the latter are pluggable from one container to another.
After creating the demo schema in a PDB and compiling the Java class file, you load it into the Oracle JVM with the loadjava utility as follows:
loadjava -r -f -o -user video/video@video HelloWorld2.class |
You can verify that the loadjava command worked with the following query:
SQL> SELECT object_name 2 FROM user_objects 3 WHERE object_name = 'HelloWorld2' 4 AND object_type = object_type LIKE 'JAVA%'; |
It displays:
OBJECT_NAME -------------- HelloWorld2 |
On the Microsoft platform, you may get a message that states “The procedure entry point kpuhhalo could not be located in the dynamic link library OCI.dll.” If you receive this error, it means you don’t have %ORACLE_HOME\bin% in your PATH environment variable.
The loadjava utility command loads the Java HelloWorld2 class file into the Oracle JVM under the demo schema. After loading the Java class file into the database, you’ll need to build a
PL/SQL wrapper to use it.
Wrapping a Java Library
PL/SQL lets you wrap Java libraries. This section shows you how to wrap the previously created HelloWorld2 Java library, which differs from wrapping a PL/SQL stored program unit. The following HelloWorld2.sql script (included in the online resources file) builds the hello_world2 package specification and a package body. The package body serves as a wrapper to the two methods implemented in the HelloWorld2 Java class.
SQL> CREATE OR REPLACE PACKAGE hello_world2 AS 2 FUNCTION hello 3 RETURN VARCHAR2; 4 5 FUNCTION hello 6 ( who VARCHAR2 ) 7 RETURN VARCHAR2; 8 END hello_world2; 9 / |
The following package body builds wrapper methods in the HelloWorld2 Java class library:
SQL> CREATE OR REPLACE PACKAGE BODY hello_world2 AS 2 FUNCTION hello 3 RETURN VARCHAR2 IS 4 LANGUAGE JAVA 5 NAME 'HelloWorld2.hello() return java.lang.String'; 6 7 FUNCTION hello 8 ( who VARCHAR2 ) 9 RETURN VARCHAR2 IS 10 LANGUAGE JAVA 11 NAME 'HelloWorld2.hello(java.lang.String) return java.lang.String'; 12 END hello_world2; 13 / |
Line 5 and line 11 designate a lowercase hello method. It is critical that the case-sensitive string description in the PL/SQL wrapper exactly match the case-sensitive method names that are used in the Java code. You raise the following exception if they don’t:
SELECT hello_world2.hello() FROM dual * ERROR AT line 1: ORA-29531: no method hello IN class HelloWorld2 |
When you ensure a case-sensitive match and run this in your schema, it creates a wrapper to the HelloWorld2.class file that you previously loaded. The return type of your PL/SQL wrapper is a VARCHAR2 data type. You map it to a java.lang.String class, and it must be that fully qualified path.
You can verify that all components are present to test by querying the user_objects view with the following:
COLUMN object_name FORMAT A30 SQL> SELECT object_name 2 , object_type 3 , status 4 FROM user_objects 5 WHERE object_name IN ('HelloWorld2','HELLO_WORLD2') 6 ORDER BY CASE 7 WHEN object_type = 'JAVA CLASS' THEN 1 8 WHEN object_type = 'PACKAGE' THEN 2 9 ELSE 3 10 END; |
The query will return the following results:
Method Call ----------------------------------- Hello Paul McCartney. |
This section has shown you how to wrap and identify Java class libraries and related PL/SQL packages. Next you’ll see how to compile, deploy, and wrap a Java library.
Compiling, Deploying and Wrapping a Java Library
As an alternative to using the loadjava utility, you can also build the entire Java source, publishing specification, and implementation in a single SQL file. Then, you can run it directly from the SQL*Plus command line. This is done by using a single Data Definition Language (DDL)
command.
The prototype for the DDL command is
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED <java_class_name> AS <java_source> / |
You must use a forward slash shown in the prototype to execute a DDL command that builds an internal Java class. If you substitute a semicolon, you’ll raise an ORA-29536 exception.
The Java class file name uniquely identifies the Java class in the Oracle JVM’s CLASSPATH. You can treat a class with multiple methods like a package equivalent and define a package as a wrapper to complete the Java class. Alternatively, you can ignore the similarity to a package and implement individual Java methods as functions or procedures. Java methods that return a static value act like PL/SQL procedures, and Java methods that return a value act like PL/SQL functions.
Overloading doesn’t work when you implement a schema-level function or procedure for each Java method in a Java class. You must implement PL/SQL wrappers inside a package when you want to leverage object-oriented overloading in Oracle’s PL/SQL programming language.
You create the Java HelloWorldSQL class with the following DDL command:
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS 2 // Class Definition. 3 PUBLIC class HelloWorldSQL { 4 5 PUBLIC static String hello() { 6 RETURN "Hello World."; } 7 8 PUBLIC static String hello(String name) { 9 RETURN "Hello " + name + "."; } 10 } 11 / |
RESOLVE JAVA SOURCE NAMED targets the class file name and parses and compiles the Java source into the instance. You can then publish individual methods as functions or procedures, or the class as a package.
The following publishes the class as a package specification:
SQL> CREATE OR REPLACE PACKAGE hello_world_sql AS 2 FUNCTION hello 3 RETURN VARCHAR2; 4 5 FUNCTION hello 6 ( who VARCHAR2 ) 7 RETURN VARCHAR2; 8 END hello_world_sql; 9 / |
And this wraps the class as a package body:
SQL> CREATE OR REPLACE PACKAGE BODY hello_world_sql AS 2 FUNCTION hello 3 RETURN VARCHAR2 IS 4 LANGUAGE JAVA 5 NAME 'HelloWorldSQL.hello() return String'; 6 7 FUNCTION hello 8 ( who VARCHAR2 ) 9 RETURN VARCHAR2 IS 10 LANGUAGE JAVA 11 NAME 'HelloWorldSQL.hello(java.lang.String) return String'; 12 END hello_world_sql; 13 / |
The package provides an overloaded hello function. You can call it with or without an actual parameter. You can then publish only a single method of the class as a function, like this:
SQL> CREATE OR REPLACE FUNCTION hello 2 ( who VARCHAR2) RETURN VARCHAR2 IS 3 LANGUAGE JAVA 4 NAME 'HelloWorldSQL.hello(java.lang.String) return String'; 5 / |
You can query the function by using
SQL> SELECT hello('Nathan') AS SALUTATION; |
and it returns
SALUTATION ----------------------------------- Hello Nathan. |
You have now covered how to build Oracle database instance-stored Java class files that map
methods to functions. The next blog post will examine how you build components to deliver
procedure DML behaviors.
SQL Developer & PostgreSQL
I had a request from one of the adjunct professors to connect SQL Developer to the PostgreSQL database. This is in support of our database programming class that teaches students how to write PL/SQL against the Oracle database and pgPL/SQL against the PostgreSQL database. We also demonstrate transactional management through Node.js, Python and Java.
Naturally, this is also a frequent step taken by those required to migrate PostgreSQL data models to an Oracle database. While my final solution requires mimicking Oracle’s database user to schema, it does work for migration purposes. I’ll update this post when I determine how to populate the database drop-down list.
The first step was figuring out where to put the PostgreSQL JDBC Java ARchive (.jar) file on a Linux distribution. You navigate to the end-user student account in a Terminal and change to the .sqldeveloper directory. Then, create a jdbc subdirectory as the student user with the following command:
mkdir /home/student/.sqldeveloper/jdbc |
Then, download the most current PostgreSQL JDBC Java ARchive (.jar) file and copy it into the /home/student/.sqldeveloper/jdbc, which you can see afterward with the following command:
ll /home/student/.sqldeveloper/jdbc |
It should display:
-rw-r--r--. 1 student student 1041081 Aug 9 13:46 postgresql-42.3.7.jar |
The next series of steps are done within SQL Developer. Launch SQL Developer and navigate to Tools and Preferences, like this:
Inside the Preferences dialog, navigate to Database and Third Party JDBC Drivers like shown and click the Add Entry button to proceed:
Inside the Select Path Entry dialog, select the current PostgreSQL JDBC Java ARchive (.jar) file, which is postgresql-42-3.7.jar in this example. Then, click the Select button.
You are returned to the Preferences dialog as shown below. Click the OK button to continue.
After completing the 3rd Party Java Driver setup, you attempt to create a new connection to the PostgreSQL database. You should see that you now have two available Database Type values: Oracle and PostgreSQL, as shown below:
When you click on the PostgreSQL Database Type, the dialog updates to the following view. Unfortunately, I couldn’t discover how to set the values in the list for the Choose Database drop down. Naturally, a sandboxed user can’t connect to the PostgreSQL database without qualifying the database name.
Unless you qualify the PostgreSQL database or connect as the postgres user with a privileged password, SQL Developer translates the absence of a database selection to a database name equivalent to the user’s name. That’s the default behavior for the Oracle database but differs from the behavior for MySQL, PostgreSQL, and Microsoft SQL Server. It returns the following
Status: Failure - Test failed: FATAL: database "student" does not exist |
As seen in the diaglog’s result when testing the connection:
Based on my hunch and not knowing how to populate the database field for the connection, I did the following:
- Created a Linux OS videodb user.
- Copied the .bashrc file with all the standard Oracle environment variables.
- Created the /home/videodb/.sqldeveloper/jdbc directory.
- Copied the postgresql-42.3.7.jar into the new jdbc directory.
- Connected as the postgres super user and created the PostgreSQL videodb user with this syntax:
CREATE USER videodb WITH ROLE dba ENCRYPTED PASSWORD 'cangetin';
- As the postgres super user, granted the following privileges:
-- Grant privileges on videodb database videodb user. GRANT ALL ON DATABASE "videodb" TO "videodb"; -- Connect to the videodb database. \c -- Grant privileges. GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO videodb; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO videodb;
- Added the following line to the pg_hba.conf file in the /var/lib/pgsql/15/data directory as the postgres user:
local all videodb peer - Connected as the switched from the student to videodb Linux user, and launched SQL Developer. Then, I used the Tools menu to create the 3rd party PostgreSQL JDBC Java ARchive (.jar) file in context of the SQL Developer program. Everything completed correctly.
- Created a new PostgreSQL connection in SQL Developer and tested it with success as shown:
- Saving the new PostgreSQL connection, I opened the connection and could run SQL statements and display the catalog information, as shown:
Connected as the videodb user to the videodb database I can display tables owned by student and videodb users:
-- List tables. \d List of relations Schema | Name | Type | Owner --------+--------------------------+----------+--------- public | new_hire | table | student public | new_hire_new_hire_id_seq | sequence | student public | oracle_test | table | videodb (3 rows)
In SQL Developer, you can also inspect the tables, as shown:
At this point, I’m working on trying to figure out how to populate the database drop-down table. However, I’ve either missed a key document or it’s unfortunate that SQL Developer isn’t as friendly as MySQL Workbench in working with 3rd Party drivers.
PostgreSQL Java
The majority of information to write this post comes form knowing how Java works and where to find the PostgreSQL JDBC Java archive (.jar) file and the standard documentation. Here are the URLs:
The rest of the example is simply demonstrating how to create a fully working program to return one or more rows from a static query. After you download the latest PostgreSQL JDBC archive, with a command like:
wget https://jdbc.postgresql.org/download/postgresql-42.3.7.jar |
Assuming you put it in test directory, like /home/student/java, you would add it to your Java $CLASSPATH environment variable, like this:
export set CLASSPATH="/home/student/Code/java/postgresql-42.3.7.jar:." |
If you’re new to Java and Linux, the . (dot) represents the present working directory and is required in the Java $CLASSPATH to avoid raising a java.lang.ClassNotFoundException when you test your code. For example, the sample program name is PostgreSQLDriver.java and if you failed to include the present working directory in the $CLASSPATH it would raise the following error message when you try to run the compiled class file:
Error: Could not find or load main class PSQL Caused by: java.lang.ClassNotFoundException: PSQL |
Now that you’ve set your Java $CLASSPATH correctly, you can copy or type this PostgreSQLDriver.java Java program into a file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | // Import classes. import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; /* You can't include the following on Linux without raising an exception. */ // import com.mysql.jdbc.Driver; public class PostgreSQLDriver { public PostgreSQLDriver() { /* Declare variables that require explicit assignments because they're addressed in the finally block. */ Connection conn = null; Statement stmt = null; ResultSet rset = null; /* Declare other variables. */ String url; String username = "student"; String password = "student"; String database = "videodb"; String hostname = "[::1]"; String port = "5432"; String sql; /* Attempt a connection. */ try { // Set URL. url = "jdbc:postgresql://" + hostname + ":" + port + "/" + database; // Create instance of MySQLDriver. conn = DriverManager.getConnection (url, username, password); // Query the version of the database. sql = "SELECT version()"; stmt = conn.createStatement(); rset = stmt.executeQuery(sql); System.out.println ("Database connection established"); // Read row returns for one column. while (rset.next()) { System.out.println("PostgreSQL Connected to the [" + rset.getString(1) + "] database."); } } catch (SQLException e) { System.err.println ("Cannot connect to database server:"); System.out.println(e.getMessage()); } finally { if (conn != null) { try { rset.close(); stmt.close(); conn.close(); System.out.println ("Database connection terminated"); } catch (Exception e) { /* ignore close errors */ } } } } /* Unit test. */ public static void main(String args[]) { new PostgreSQLDriver(); } } |
Now, you compile the program from the present working directory with this syntax:
javac PostgreSQLDriver.java |
It creates a PostgreSQLDriver.class file, which you can run with this syntax:
java PostgreSQLDriver |
It will return the following, which verifies you’ve connected to a valid database in the PostgreSQL instance. You should note that the IPV6 syntax is used in the example on line #25 but you could substitute localhost, an assigned host name, or an IP address.
AlmaLinxu + javac
I neglected to show how to install and configure the Java SDK on my earlier post without relying on including the $JAVA_HOME in the $PATH. This post shows how to install the Java and Java SDK on Alma Linux without layering it with the $JAVA_HOME environment variable.
You install the Java runtime and development kit with the following command:
sudo dnf install -y java-17-openjdk java-17-openjdk-devel |
Then, you need to add it to the $PATH environment variable with the following syntax in your .bashrc file or environment file of your choice.
export set PATH=$PATH:/usr/lib/jvm/java-17-openjdk-17.0.8.0.7-2.el9.x86_64/bin/ |
After you source the new $PATH environment value, you can then write a file like this Hello.java file to test it.
// Define the Java program. public class HelloWorld { public static void main(String[] args) { System.out.println("Hello World!"); } } |
You compile the Hello.java file with this syntax:
javac Hello.java |
Then, you run the compiled program with the following syntax:
java Hello |
It’ll print:
Hello World! |
AlmaLinux Install & Configuration
This is a collection of blog posts for installing and configuring AlmaLinux with the Oracle, PostgreSQL, MySQL databases and several programming languages. Sample programs show how to connect PHP and Python to the MySQL database.
- Installing AlmaLinux operating system
- Installing and configuring MySQL
- Installing Python-MySQL connector and provide sample programs
- Configuring Flask for Python on AlmaLinux with a complete software router instruction set.
- Installing Rust programming language and writing a sample program
- Installing and configuring LAMP stack with PHP and MySQL and a self-signed security key
- MySQL PNG Images in LAMP with PHP Programming
- Demonstration of how to write Perl that connects to MySQL
- Installing and configuring MySQL Workbench
- Installing and configuring PostgreSQL and pgAdmin4
- Identifying the required libnsl2-devel packages for SQL*Plus
- Writing and deploying a sqlplus function to use a read line wrapper
- Installing and configuring Visual Studio Code Editor
- Installing and configuring Java with connectivity to MySQL
- Installing and configuring Oracle SQL Developer
I used Oracle Database 11g XE in this instance to keep the footprint as small as possible. It required a few tricks and discovering the missing library that caused folks grief eleven years ago. I build another with a current Oracle Database XE after the new year.
If you see something that I missed or you’d like me to add, let me know. As time allows, I’ll try to do that. Naturally, the post will get updates as things are added later.
AlmaLinux+Java+MySQL
AlmaLinux generally has Java installed. You can check whether java is installed with this command:
which -a java |
It should return:
/usr/bin/java |
Then, you can check the Java version with this command:
java -version |
For AlmaLinux 9, it should return:
openjdk version "11.0.17" 2022-10-18 LTS OpenJDK Runtime Environment (Red_Hat-11.0.17.0.8-2.el9_0) (build 11.0.17+8-LTS) OpenJDK 64-Bit Server VM (Red_Hat-11.0.17.0.8-2.el9_0) (build 11.0.17+8-LTS, mixed mode, sharing) |
Next, you check whether javac is installed. You can use the which command to determine whether it is installed. Generally, its not installed and you use this command to
sudo dnf search jdk | egrep -- '-17' |
It should return:
Last metadata expiration check: 0:11:17 ago on Mon 19 Dec 2022 11:32:48 PM EST. java-17-openjdk.x86_64 : OpenJDK 17 Runtime Environment java-17-openjdk-demo.x86_64 : OpenJDK 17 Demos java-17-openjdk-devel.x86_64 : OpenJDK 17 Development Environment java-17-openjdk-headless.x86_64 : OpenJDK 17 Headless Runtime Environment java-17-openjdk-javadoc.x86_64 : OpenJDK 17 API documentation java-17-openjdk-javadoc-zip.x86_64 : OpenJDK 17 API documentation compressed in a single archive java-17-openjdk-jmods.x86_64 : JMods for OpenJDK 17 java-17-openjdk-src.x86_64 : OpenJDK 17 Source Bundle java-17-openjdk-static-libs.x86_64 : OpenJDK 17 libraries for static linking |
Now, you can install the Java JDK with the following dnf command:
sudo dnf install java-17-openjdk java-17-openjdk-devel |
The log file for this is:
Display detailed console log →
Last metadata expiration check: 0:28:26 ago on Mon 19 Dec 2022 11:32:48 PM EST. Dependencies resolved. ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: java-17-openjdk x86_64 1:17.0.5.0.8-2.el9_0 appstream 237 k java-17-openjdk-devel x86_64 1:17.0.5.0.8-2.el9_0 appstream 4.7 M Installing dependencies: java-17-openjdk-headless x86_64 1:17.0.5.0.8-2.el9_0 appstream 40 M ttmkfdir x86_64 3.0.9-65.el9 appstream 52 k xorg-x11-fonts-Type1 noarch 7.5-33.el9 appstream 499 k Transaction Summary ================================================================================ Install 5 Packages Total download size: 45 M Installed size: 199 M Is this ok [y/N]: y Downloading Packages: (1/5): java-17-openjdk-17.0.5.0.8-2.el9_0.x86_6 342 kB/s | 237 kB 00:00 (2/5): ttmkfdir-3.0.9-65.el9.x86_64.rpm 520 kB/s | 52 kB 00:00 (3/5): xorg-x11-fonts-Type1-7.5-33.el9.noarch.r 849 kB/s | 499 kB 00:00 (4/5): java-17-openjdk-devel-17.0.5.0.8-2.el9_0 1.7 MB/s | 4.7 MB 00:02 (5/5): java-17-openjdk-headless-17.0.5.0.8-2.el 6.1 MB/s | 40 MB 00:06 -------------------------------------------------------------------------------- Total 6.3 MB/s | 45 MB 00:07 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Running scriptlet: java-17-openjdk-headless-1:17.0.5.0.8-2.el9_0.x86_64 1/1 Preparing : 1/1 Installing : java-17-openjdk-headless-1:17.0.5.0.8-2.el9_0.x86_64 1/5 Running scriptlet: java-17-openjdk-headless-1:17.0.5.0.8-2.el9_0.x86_64 1/5 Installing : ttmkfdir-3.0.9-65.el9.x86_64 2/5 Installing : xorg-x11-fonts-Type1-7.5-33.el9.noarch 3/5 Running scriptlet: xorg-x11-fonts-Type1-7.5-33.el9.noarch 3/5 Installing : java-17-openjdk-1:17.0.5.0.8-2.el9_0.x86_64 4/5 Running scriptlet: java-17-openjdk-1:17.0.5.0.8-2.el9_0.x86_64 4/5 Installing : java-17-openjdk-devel-1:17.0.5.0.8-2.el9_0.x86_64 5/5 Running scriptlet: java-17-openjdk-devel-1:17.0.5.0.8-2.el9_0.x86_64 5/5 Running scriptlet: java-17-openjdk-headless-1:17.0.5.0.8-2.el9_0.x86_64 5/5 Running scriptlet: java-17-openjdk-1:17.0.5.0.8-2.el9_0.x86_64 5/5 Running scriptlet: java-17-openjdk-devel-1:17.0.5.0.8-2.el9_0.x86_64 5/5 Verifying : java-17-openjdk-1:17.0.5.0.8-2.el9_0.x86_64 1/5 Verifying : java-17-openjdk-devel-1:17.0.5.0.8-2.el9_0.x86_64 2/5 Verifying : java-17-openjdk-headless-1:17.0.5.0.8-2.el9_0.x86_64 3/5 Verifying : ttmkfdir-3.0.9-65.el9.x86_64 4/5 Verifying : xorg-x11-fonts-Type1-7.5-33.el9.noarch 5/5 Installed: java-17-openjdk-1:17.0.5.0.8-2.el9_0.x86_64 java-17-openjdk-devel-1:17.0.5.0.8-2.el9_0.x86_64 java-17-openjdk-headless-1:17.0.5.0.8-2.el9_0.x86_64 ttmkfdir-3.0.9-65.el9.x86_64 xorg-x11-fonts-Type1-7.5-33.el9.noarch Complete! |
After installing javac, you can verify it with the which command, and check the version with the javac utility.
which -a javac |
It should return:
/usr/bin/javac |
Then, you can check the Java version with this command:
java -version |
For AlmaLinux 9, it should return:
javac 17.0.5 |
Now, you need to download and install the jdk with the dnf utility. You download the jdk package with the wget utility.
wget https://download.oracle.com/java/17/latest/jdk-17_linux-x64_bin.rpm |
It will generate the following console output:
--2022-12-20 00:12:34-- https://download.oracle.com/java/17/latest/jdk-17_linux-x64_bin.rpm Resolving download.oracle.com (download.oracle.com)... 23.192.208.88 Connecting to download.oracle.com (download.oracle.com)|23.192.208.88|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 163101336 (156M) [application/x-redhat-package-manager] Saving to: ‘jdk-17_linux-x64_bin.rpm’ jdk-17_linux-x64_bi 100%[===================>] 155.54M 11.1MB/s in 17s 2022-12-20 00:12:51 (9.01 MB/s) - ‘jdk-17_linux-x64_bin.rpm’ saved [163101336/163101336] |
You install the jdk package with the dnf utility.
sudo rpm -Uvh jdk-17_linux-x64_bin.rpm |
The log file for this is:
Display detailed console log →
warning: jdk-17_linux-x64_bin.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY Verifying... ################################# [100%] Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Preparing... ################################# [100%] Updating / installing... 1:jdk-17-2000:17.0.5-ga ################################# [100%] |
After installing everything, you need to set the default Java. You can discover the available Java versions with the following command:
sudo alternatives --config java |
It should return a list and the ability to select one by using the “Selection” number. I recommend you chose #2.
There are 3 programs which provide 'java'. Selection Command ----------------------------------------------- 1 java-11-openjdk.x86_64 (/usr/lib/jvm/java-11-openjdk-11.0.17.0.8-2.el9_0.x86_64/bin/java) 2 java-17-openjdk.x86_64 (/usr/lib/jvm/java-17-openjdk-17.0.5.0.8-2.el9_0.x86_64/bin/java) *+ 3 /usr/java/jdk-17.0.5/bin/java Enter to keep the current selection[+], or type selection number: 2 |
Then, you can check the Java version with this command:
java -version |
Now, it should return:
openjdk version "17.0.5" 2022-10-18 LTS OpenJDK Runtime Environment (Red_Hat-17.0.5.0.8-2.el9_0) (build 17.0.5+8-LTS) OpenJDK 64-Bit Server VM (Red_Hat-17.0.5.0.8-2.el9_0) (build 17.0.5+8-LTS, mixed mode, sharing) |
You should define the $JAVA_HOME environment variable in the /etc/profile file. Sometimes, it is also appropriate to include it in your .bashrc file.
export set JAVA_HOME="/usr/lib/jvm/java-17-openjdk-17.0.5.0.8-2.el9_0.x86_64/bin" |
You should also add it to your $PATH variable in your .bashrc file, like:
export set PATH=$PATH:$JAVA_HOME |
Now, let’s write the basic “Hello World” program in Java:
// Define the Java program. public class HelloWorld { public static void main(String[] args) { System.out.println("Hello World!"); } } |
Compile it with:
javac HelloWorld.java |
Run it with the java command:
java HelloWorld |
It prints:
Hello World! |
Let’s download the MySQL/J Connector from the MySQL website, where we con select the operating system and its version, as shown in the image.
After you download the mysql-connector-j RPM package, you can install it with the following dnf command:
sudo dnf install -y mysql-connector-j-8.0.31-1.el9.noarch.rpm |
The log file for this is:
Display detailed console log →
Last metadata expiration check: 2:02:56 ago on Mon 19 Dec 2022 11:32:48 PM EST. Dependencies resolved. ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: mysql-connector-j noarch 1:8.0.31-1.el9 @commandline 2.3 M Transaction Summary ================================================================================ Install 1 Package Total size: 2.3 M Installed size: 2.7 M Downloading Packages: Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Preparing : 1/1 Installing : mysql-connector-j-1:8.0.31-1.el9.noarch 1/1 Verifying : mysql-connector-j-1:8.0.31-1.el9.noarch 1/1 Installed: mysql-connector-j-1:8.0.31-1.el9.noarch Complete! |
You should add the $CLASSPATH to your .bashrc file. Add the following line to your .bashrc file and resource your .bashrc file to test the JDBC driver.
export set CLASSPATH="/usr/share/java/mysql-connector-j.jar:." |
Let’s write a slightly larger Java program that tests connectivity to the MySQL database, like:
// Import classes. import java.sql.*; /* You can't include the following on Linux without raising an exception. */ // import com.mysql.jdbc.Driver; public class MySQLConnector { public MySQLConnector() { /* Declare variables that require explicit assignments because they're addressed in the finally block. */ Connection conn = null; Statement stmt = null; ResultSet rset = null; /* Declare other variables. */ String url; String username = "student"; String password = "student"; String database = "studentdb"; String hostname = "localhost"; String port = "3306"; String sql; /* Attempt a connection. */ try { // Set URL. url = "jdbc:mysql://" + hostname + ":" + port + "/" + database; // Create instance of MySQLDriver. Class.forName ("com.mysql.cj.jdbc.Driver").newInstance(); conn = DriverManager.getConnection (url, username, password); // Query the version of the database. sql = "SELECT version()"; stmt = conn.createStatement(); rset = stmt.executeQuery(sql); System.out.println ("Database connection established"); // Read row returns for one column. while (rset.next()) { System.out.println("MySQLDriver Version [" + rset.getString(1) + "]"); } } catch (SQLException e) { System.err.println ("Cannot connect to database server:"); System.out.println(e.getMessage()); } catch (ClassNotFoundException e) { System.err.println ("Cannot find MySQL driver class:"); System.out.println(e.getMessage()); } catch (InstantiationException e) { System.err.println ("Cannot instantiate class:"); System.out.println(e.getMessage()); } catch (IllegalAccessException e) { System.err.println ("Illegal access exception:"); System.out.println(e.getMessage()); } finally { if (conn != null) { try { rset.close(); stmt.close(); conn.close(); System.out.println ("Database connection terminated"); } catch (Exception e) { /* ignore close errors */ } } } } /* Unit test. */ public static void main(String args[]) { new MySQLDriver(); } } |
Just a note, there’s a deprecated behavior introduced in MySQL 8.0.24, which produces the following error message when compiled looking for deprecation:
javac -Xlint:deprecation MySQLDriver.java |
It produces the following error message, which I hope to sort and update later in the week:
MySQLDriver.java:30: warning: [deprecation] newInstance() in Class has been deprecated
Class.forName ("com.mysql.cj.jdbc.Driver").newInstance();
^
where T is a type-variable:
T extends Object declared in class Class
1 warning |
The code does compile because the deprecation is only a warning. When you run the program, like:
java MySQLDriver |
It returns, confirming a connection to the MySQL database:
Database connection established MySQLDriver Version [8.0.30] Database connection terminated |
As always, I hope this helps those trying to sort out new behaviors.
Subscribe via RSS