ARRAY

Database table is created with column type as an array. If column type is an array, it can hold more than one value of similar type.

How to create array type in oracle database

create type <typename> as varray(<size>) of <type>;

SQL> create type ctype as varray(3) of varchar2(20);

SQL> create table student(id number(4), name varchar2(20), course ctype);

PreparedStatement provides the following method -

void setArray(int paramindex, Array x) - Sets the designated parameter to the given java.sql.Array object.

ArrayDescriptor

ArrayDescriptor class provided by driver. ArrayDescriptor hold description of array-type exist in database.

ArrayDescriptor ad = ArrayDescriptor.createDescriptor(con,"CTYPE");

ARRAY

ARRAY is a class, it contain mapping java array type with SQL type.

Array a = new ARRAY(c,con,ad);

PROGRAM TO INSERT RECORD USING ARRAY IN DATABASE

import java.sql.Array;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.Scanner;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

public class Test21 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "rakesh");

PreparedStatement ps = con.prepareStatement("insert into student values(?,?,?)");

Scanner scan = new Scanner(System.in);

System.out.println("Input id");

int id = scan.nextInt();

System.out.println("Input name");

String name = scan.next();

System.out.println("Input how many course");

int cno = scan.nextInt();

String c[]=new String[cno];

for(int j=0;j<cno;j++)

c[j]=scan.next();

ArrayDescriptor ad = ArrayDescriptor.createDescriptor("CTYPE", con);

Array a = new ARRAY(ad,con,c);

ps.setInt(1, id);

ps.setString(2, name);

ps.setArray(3, a);

int count = ps.executeUpdate();

System.out.println(count);

}

}

Output -

Input id

1

Input name

rakesh

Input how many course

2

jdbc

oracle

1