Insert -Read Array in Oracle table using JDBC
- Tuesday, October 18, 2011, 18:29
- JDBC Oracle, JDBC Tutorial
- Add a comment
Oracle
Array, one of the SQL 99 datatypes. offers you the facility to include an ordered list of values within the column. The java.sql.Array interface to store the values of the array types. To store the Array first we need to create a User-Defined-Type Array. this can be done by creating a UDT as array in database.
To understant this concept, lets create an application. We have to store the visa nos of the emloyee into an array. to do this first create an array of name visa_no ant type is varchar2. The syntax to create an array type in the database is as follows.
CREATE TYPE (type_name) AS VARRAY(LENGTH) OF (TYPE)
To Store the visa details of an employee, create an array of type varchar2. Following is the SQL.
CREATE TYPE visa_nos AS VARRAY(5) OF varchar2(16); CREATE TABLE emppassportDetails ( empno NUMBER, passportno varchar2(10), visas_taken visa_nos );
Now create a java program to insert the array into database. we can use PreparedStatement interfaces setArray() method to insert the array into column. Following is the program.
import java.sql.*; import java.util.*; import oracle.sql.*; public class InsertEmpPassportDetails { 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); PreparedStatement ps=con.prepareStatement( "insert into emppassportDetails values(?,?,?)"); ps.setInt(1,7934); ps.setString(2,"12345A134"); String s1[]={"v1","v2","v3","v4","v5"}; ArrayDescriptor ad=ArrayDescriptor.createDescriptor("VISA_NOS",con); ARRAY a=new ARRAY(ad,con,s1); ps.setArray(3,a); int i=ps.executeUpdate(); System.out.println("Row Inserted, count : "+i); con.close(); }//main }//class
Reading the Array
After inserting now see how to get the Array from database. To read the array, we can use getArray() method of ResultSet. This method return the ref of an Array.After getting the ref of Array call the getResultSet() method of Array, and get the ResultSet ref. After getting the ResultSet ref, Iterate that ResultSet for getting all the values of Array. Following is the java code to read the array.
Now create a java program to insert the array into database. we can use PreparedStatement interfaces setArray() method to insert the array into column. Following is the program.
import java.sql.*; import java.util.*; public class GetEmpPassportDetails { 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 passportno, visas_taken from emppassportDetails "+ "where empno="+s[0]); if (rs.next()) { System.out.println("\nEmployee Found, His Passport Details are:\n"); System.out.println("PassportNo:"+rs.getString(1)+"\n"); System.out.print("Visa's Taken are :\n\t"); Array a=rs.getArray(2); ResultSet rs1=a.getResultSet(); boolean flag=rs1.next(); while(flag) { System.out.print(rs1.getString(2)); flag=rs1.next(); if (flag) System.out.print(","); }//while }//if else System.out.println("Employee not Found"); System.out.println(); con.close(); }//main }//class
About the Author
Tousif Khan has written 22 posts on this blog.
