Oracle
---------
I think Mysql wont support varray as per my knowledge.
1)Create varray type
CREATE OR REPLACE TYPE STUDENTARRAY as varray(20) of varchar2(50)
2)Create table with new type studentarray
create table studentsvarraytable(
starray studentarray
);
3)Create procedure which accepts argument of type studentarray
CREATE OR REPLACE PROCEDURE VARRAYPROC ( svarray in studentarray )
is
begin
insert into studentsvarraytable values ( svarray );
end;
4)Pass array from java code in to pl/sql procedure which inserts the passed
students in to table
Code:
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package jdbcsample;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
/**
*
* @author Rishitha
*/
public class ArrayPassingClass {
/**
* @param args the command line arguments
*/
public static Connection getConnection() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Please add ojdbc6.jar in your classpath if you are using oracle,"
+ "otherwise set the respective database driver jar file.In netbeans right click on"
+ "libraries,click on add jar/folder and provide your jar file");
}
Connection connection = null;
try {
//XE -- Here Give your database name
//hr -- Give Your database username
//hr -- Give your database password
//You have to add ojdbc.jar in your classpath
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE", "hr",
"hr");
System.out.println("Hurrah got connection " + connection);
} catch (SQLException e) {
System.out.println("Please check if database name username password are correct ?");
}
return connection;
}
public static void main(String args[]) throws Exception {
Connection con = getConnection();
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("STUDENTARRAY",con);
String[] students = new String[] { "Anilkumar", "Sunilkumar" };
ARRAY array = new ARRAY ( desc, con, students );
String stmtString =
"begin VARRAYPROC(?); end;";
CallableStatement cstmt = con.prepareCall( stmtString );
cstmt.setArray( 1, array );
cstmt.execute();
}
}
5)Select * from studentsvarraytable;

---------
I think Mysql wont support varray as per my knowledge.
1)Create varray type
CREATE OR REPLACE TYPE STUDENTARRAY as varray(20) of varchar2(50)
2)Create table with new type studentarray
create table studentsvarraytable(
starray studentarray
);
3)Create procedure which accepts argument of type studentarray
CREATE OR REPLACE PROCEDURE VARRAYPROC ( svarray in studentarray )
is
begin
insert into studentsvarraytable values ( svarray );
end;
4)Pass array from java code in to pl/sql procedure which inserts the passed
students in to table
Code:
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package jdbcsample;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
/**
*
* @author Rishitha
*/
public class ArrayPassingClass {
/**
* @param args the command line arguments
*/
public static Connection getConnection() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Please add ojdbc6.jar in your classpath if you are using oracle,"
+ "otherwise set the respective database driver jar file.In netbeans right click on"
+ "libraries,click on add jar/folder and provide your jar file");
}
Connection connection = null;
try {
//XE -- Here Give your database name
//hr -- Give Your database username
//hr -- Give your database password
//You have to add ojdbc.jar in your classpath
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE", "hr",
"hr");
System.out.println("Hurrah got connection " + connection);
} catch (SQLException e) {
System.out.println("Please check if database name username password are correct ?");
}
return connection;
}
public static void main(String args[]) throws Exception {
Connection con = getConnection();
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("STUDENTARRAY",con);
String[] students = new String[] { "Anilkumar", "Sunilkumar" };
ARRAY array = new ARRAY ( desc, con, students );
String stmtString =
"begin VARRAYPROC(?); end;";
CallableStatement cstmt = con.prepareCall( stmtString );
cstmt.setArray( 1, array );
cstmt.execute();
}
}
5)Select * from studentsvarraytable;
No comments:
Post a Comment