Reading Oracle Struct Object Using JDBC getObject()
- Thursday, October 20, 2011, 2:43
- JDBC Oracle, JDBC Tutorial
- Add a comment
In Our last tutorial, Inserting Struct Object data Using JDBC i explain how to create a user-defined-type (Struct) using Oracle. We also see how to insert your own java object in that UDT. Now using the same tutorial, just we are going to see how you can read that UDT using PreparedStatement’s getObject() method.
To read that Object use java.sql.ResultSet interfaces getObject() method. Type cast that Object by using Struct type and call the Struct’s getAttributes() method. this method return array of Object (Object[]). Following is the code to read the Struct Object.
CREATE TYPE empaddress AS OBJECT ( flatno NUMBER, street varchar2(20), city varchar2(15), state varchar2(10), pincode NUMBER ); CREATE TABLE personal_details ( empno NUMBER, photo BLOB, permanent_address empaddress, present_address empaddress );
Following is the java code.
import java.sql.*; import java.util.*; import java.io.*; public class GetEmployeeAddressUsingStruct { public static void main(String s[]) throws Exception { Driver d= (Driver) ( Class.forName( "oracle.jdbc.driver.OracleDriver").newInstance()); Properties p=new Properties (); p.put("user","scott"); p.put("password","tiger"); Connection con=d.connect("jdbc:oracle:thin:@mysys:1521:khan",p); Statement st=con.createStatement(); ResultSet rs=st.executeQuery( "select permanent_address from personaldetails where empno="+s[0]); if (rs.next()){ Struct struct=(Struct)rs.getObject(1); System.out.println("Employee Found: Address"); Object addr[]=struct.getAttributes(); System.out.println("Flatno : "+addr[0]); System.out.println("Street : "+ addr[1]); System.out.println("Pin : "+addr[4]); }//if con.close(); }//main }//class
About the Author
Tousif Khan has written 22 posts on this blog.
