Posts Tagged ‘Oracle’

Working with BLOB / CLOB data types in database is sometime a trivial taspring-hibernate-blob-mysql-tutorialsk. I found particularly when working with Hibernate 3 to store and retrieve BLOB objects we need certain things to be taken care of. Let us see a tutorial where we will using Spring 3 MVC and Hibernate 3 to store and retrieve blob objects in database.

Our Goal

Our goal is to create a Document Manager application in Spring 3 MVC and Hibernate. Following is the functionality.

  1. A form is displayed on main page with fields such as Document name, description and browse button to select document from file system.
  2. User can select any document from local drive and upload the same using Save document functionality.
  3. All the documents saved are added in a database table.
  4. List of all the documents present in database is displayed on the main page.
  5. Each document in the list have two buttons: Delete and Download.
  6. Any document can be downloaded by clicking on download button.
  7. Any document can be deleted by clicking on delete button.

Here is the final screen shot of Document manager application.
document-manager-hibernate-spring-blob

Step 1: Create Database Table

For Document Manager application, we will use MySQL database. Create a table documents in MySQL database docdb. This is very preliminary example and thus we have minimum columns to represent a document. Feel free to extend this example and create a more complex application.

CREATE DATABASE `docdb`;

USE `docdb`;

CREATE TABLE `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` text NOT NULL,
  `filename` varchar(200) NOT NULL,
  `content` mediumblob NOT NULL, /* for ORACLE enter BLOB*/
  `content_type` varchar(255) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

Step 2: Create Maven Project in Eclipse

The document manager application will use Maven for build and dependency management. For this we will use the Maven Dynamic Web Project in Eclipse as the base architecture of our application.

Or directly download the below source code:
Maven Dynamic Web Project (6.7 KB)

Once you have imported / created the Maven web project in Eclipse. Copy following content into Maven’s pom.xml file. These are the dependencies we will use in our Document manager application.
File: /pom.xml

<!--?xml version="1.0" encoding="UTF-8"?>
  4.0.0
  MavenWeb
  MavenWeb
  war
  0.0.1-SNAPSHOT

        maven-compiler-plugin

          1.5
          1.5

        maven-war-plugin
        2.0

      javax.servlet
      servlet-api
      2.5

      org.springframework
      spring-beans
      ${org.springframework.version}

      org.springframework
      spring-jdbc
      ${org.springframework.version}

      org.springframework
      spring-web
      ${org.springframework.version}

      org.springframework
      spring-webmvc
      ${org.springframework.version}

      org.springframework
      spring-orm
      ${org.springframework.version}

      taglibs
      standard
      1.1.2

      javax.servlet
      jstl
      1.1.2

      org.hibernate
      hibernate-entitymanager
      3.3.2.ga

      mysql
      mysql-connector-java
      5.1.10

      commons-dbcp
      commons-dbcp
      20030825.184428

      commons-pool
      commons-pool
      1.5.4

      commons-fileupload
      commons-fileupload
      1.2.1

      commons-io
      commons-io
      1.3

    3.0.2.RELEASE
    UTF-8

document-manager-project-structure
Unzip the source code to your hard drive and import the project in Eclipse. Once the project is imported in Eclipse, we will create package structure for Java source. First rename the project to DocumentManager and create following packages under src/main/java folder.

  1. net.viralpatel.docmanager.controller – This package will contain Spring Controller classes for Document Manager application.
  2. net.viralpatel.docmanager.model – This package will contain form object for Document manager application. Document model will be a simple POJO class with different attributes such as document name, description, filename etc.
  3. net.viralpatel.docmanager.dao – This is the DAO layer of Document manager application. It consists of DocumentDao class which will use Hibernate API to interact with database.
  4. The src/main/resources folder will have hibernate configuration file: hibernate.cfg.xml.
  5. The WEB-INF folder will have jsp/documents.jsp file to render document list and add form and jdbc.properties file containing database connection configuration. Also it contains spring-servlet.xml which will define all the Controller class and web.xml which contain spring configuration.

Entity class – The Hibernate model class

Let us start with the coding of Document manager application. First we will create a model object or hibernate POJO class to store document information. Also this class will be an Entity class and will be linked with DOCUMENTS table in database.

Create a java class Document.java under net.viralpatel.docmanager.model package and copy following code into it.
File: /src/main/java/net/viralpatel/docmanager/model/Document.java

package net.viralpatel.docmanager.model;

import java.sql.Blob;
import java.sql.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.Table;

@Entity
@Table(name="documents")
public class Document {

	@Id
	@GeneratedValue
	@Column(name="id")
	private Integer id;

	@Column(name="name")
	private String name;

	@Column(name="description")
	private String description;

	@Column(name="filename")
	private String filename;

	@Column(name="content")
	@Lob
	private Blob content;

	@Column(name="content_type")
	private String contentType;

	@Column(name="created")
	private Date created;

	//Getter and Setter methods
}

The first thing you’ll notice is that the import statements import from javax.persistence rather than a Hibernate or Spring package. Using Hibernate with Spring, the standard JPA annotations work just as well and that’s what I’m using here.

  • First we’ve annotated the class with @Entity which tells Hibernate that this class represents an object that we can persist.
  • The @Table(name = "documents") annotation tells Hibernate which table to map properties in this class to documents table. The first property in this class on line 20 is our object ID which will be unique for all events persisted. This is why we’ve annotated it with @Id.
  • The @GeneratedValue annotation says that this value will be determined by the datasource, not by the code.
  • The @Column(name = "filename") annotation is used to map this property to the FILENAME column in the DOCUMENTS table.

The Data Access (DAO) Layer

The DAO layer of Document Manager application consist of a class DocumentDAO. Ideal solution will be to create an interface (DocumentDAO) and its corresponding implementation class DocumentDAOImpl. But for sake of simplicity we will create just normal DAO class DocumentDAO.java.

File: src/main/java/net/viralpatel/docmanager/dao/DocumentDAO.java

package net.viralpatel.docmanager.dao;

import java.util.List;

import net.viralpatel.docmanager.model.Document;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class DocumentDAO {

	@Autowired
	private SessionFactory sessionFactory;

	@Transactional
	public void save(Document document) {
		Session session = sessionFactory.getCurrentSession();
		session.save(document);
	}

	@Transactional
	public List<Document> list() {
		Session session = sessionFactory.getCurrentSession();
		List<Document> documents = null;
		try {
			documents = (List<Document>)session.createQuery("from Document").list();

		} catch (HibernateException e) {
			e.printStackTrace();
		}
		return documents;
	}

	@Transactional
	public Document get(Integer id) {
		Session session = sessionFactory.getCurrentSession();
		return (Document)session.get(Document.class, id);
	}

	@Transactional
	public void remove(Integer id) {
		Session session = sessionFactory.getCurrentSession();

		Document document = (Document)session.get(Document.class, id);

		session.delete(document);
	}
}

Methods:

  • list() Method – This method gets the list of all documents stored in documents table and return a List of Document objects.
  • save() Method – This method is used to store a new document (including BLOB) into database.
  • get() Method – This method returns Document entry for a given ID from database. Used in download functionality to download a stored document from database.
  • remove() Method – This method is used to delete a document with specific ID from database.

Note that we have used two Spring annotations @Repository and @Autowired. Classes marked with annotations are candidates for auto-detection by Spring when using annotation-based configuration and classpath scanning. The @Component annotation is the main stereotype that indicates that an annotated class is a “component”.

The @Repository annotation is yet another stereotype that was introduced in Spring 2.0. This annotation is used to indicate that a class functions as a repository and needs to have exception translation applied transparently on it. The benefit of exception translation is that the service layer only has to deal with exceptions from Spring’s DataAccessException hierarchy, even when using plain JPA in the DAO classes.

Another annotation used in DocumentDAO is @Autowired. This is used to autowire the dependency of the DocumentDAO on the SessionFactory.

Also note that we have used @Transactional annotation on each method. Ideally the DAO layer is access from a Service layer and transaction management needs to be specified at Service layer. But again for sake of simplicity we will not include service layer in our example and directly call the DAO layer from Spring Controller. Again, feel free to change this implementation and add your own service layer in between.

For more information about A layered application with Services in Spring MVC and Hibernate read this tutorial.
Spring MVC Hibernate Maven example

Adding Spring MVC Support to Webapplication

Let us add Spring MVC support to our web application.
Update the web.xml file and add servlet mapping for org.springframework.web.servlet.DispatcherServlet. Also note that we have mapped url / with springServlet so all the request are handled by spring.

File: /src/webapp/WEB-INF/web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://java.sun.com/xml/ns/javaee"
	xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
	id="WebApp_ID" version="2.5">
	<display-name>DocumentManager</display-name>
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
	</welcome-file-list>
	<servlet>
		<servlet-name>spring</servlet-name>
		<servlet-class>
			org.springframework.web.servlet.DispatcherServlet
		</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>spring</servlet-name>
		<url-pattern>*.html</url-pattern>
	</servlet-mapping>
</web-app>

Once the web.xml is configured, let us add spring-servlet.xml and jdbc.properties files in /src/main/webapp/WEB-INF folder.

File: /src/main/webapp/WEB-INF/jdbc.properties

jdbc.driverClassName= com.mysql.jdbc.Driver
jdbc.dialect=org.hibernate.dialect.MySQLDialect
jdbc.databaseurl=jdbc:mysql://localhost:3306/docdb
jdbc.username=root
jdbc.password=password

The jdbc.properties file contains database connection information such as database url, username, password, driver class. You may want to edit the driverclass and dialect to other DB if you are not using MySQL.

File: /src/main/webapp/WEB-INF/spring-servlet.xml

<?xml  version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jee="http://www.springframework.org/schema/jee"
	xmlns:lang="http://www.springframework.org/schema/lang"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
		http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

	<context:annotation-config />
	<context:component-scan base-package="net.viralpatel.docmanager" />

	<bean id="jspViewResolver"
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="viewClass"
			value="org.springframework.web.servlet.view.JstlView" />
		<property name="prefix" value="/WEB-INF/jsp/" />
		<property name="suffix" value=".jsp" />
	</bean>

	<bean id="propertyConfigurer"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
		p:location="/WEB-INF/jdbc.properties" />

	<bean id="dataSource"
		class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
		p:driverClassName="${jdbc.driverClassName}"
		p:url="${jdbc.databaseurl}" p:username="${jdbc.username}"
		p:password="${jdbc.password}" />

	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation">
			<value>classpath:hibernate.cfg.xml</value>
		</property>
		<property name="configurationClass">
			<value>org.hibernate.cfg.AnnotationConfiguration</value>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">${jdbc.dialect}</prop>
				<prop key="hibernate.show_sql">true</prop>
				<prop key="hibernate.connection.SetBigStringTryClob">true</prop>
				<prop key="hibernate.jdbc.batch_size">0</prop>
			</props>
		</property>
	</bean>
	<bean id="multipartResolver"
		class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

		<!-- one of the properties available; the maximum file size in bytes -->
		<property name="maxUploadSize" value="10000000" />
	</bean>
	<tx:annotation-driven />

	<bean id="transactionManager"
		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>
</beans>

The spring-servlet.xml file contains different spring mappings such as transaction manager, hibernate session factory bean, data source etc.

  • jspViewResolver bean – This bean defined view resolver for spring mvc. For this bean we also set prefix as “/WEB-INF/jsp/” and suffix as “.jsp”. Thus spring automatically resolves the JSP from WEB-INF/jsp folder and assigned suffix .jsp to it.
  • propertyConfigurer bean – This bean is used to load database property file jdbc.properties. The database connection details are stored in this file which is used in hibernate connection settings.
  • dataSource bean – This is the java datasource used to connect to document manager database. We provide jdbc driver class, username, password etc in configuration.
  • sessionFactory bean – This is Hibernate configuration where we define different hibernate settings. hibernate.cfg.xml is set a config file which contains entity class mappings. Also note that in sessionFactory we have specified few hibernate properties such as hibernate.connection.SetBigStringTryClob and hibernate.jdbc.batch_size. These are used to configure BLOB / CLOB settings in hibernate.
  • multipartResolver bean – We use Spring MVCs CommonsMultipartResolver. This resolver will resolve multipart form data such as file uploads from the request and make available File object to spring controller. Note that we have specified property maxUploadSize with value 10000000. This is the maximum limit of filesize which can be uploaded in our example.
  • transactionManager bean – We use hibernate transaction manager to manage the transactions of our document manager application.

File: /src/main/resources/hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <mapping />
    </session-factory>

</hibernate-configuration>

The Controller – Spring MVC controller class

We are almost done with our application. Just add following Spring controller class DocumentController.java to net.viralpatel.docmanager.controller package.

File: /src/main/java/net/viralpatel/docmanager/controller/DocumentController.java

package net.viralpatel.docmanager.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import net.viralpatel.docmanager.dao.DocumentDAO;
import net.viralpatel.docmanager.model.Document;

import org.apache.commons.io.IOUtils;
import org.hibernate.Hibernate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

@Controller
public class DocumentController {

	@Autowired
	private DocumentDAO documentDao;

	@RequestMapping("/index")
	public String index(Map<String, Object> map) {
		try {
			map.put("document", new Document());
			map.put("documentList", documentDao.list());
		}catch(Exception e) {
			e.printStackTrace();
		}

		return "documents";
	}

	@RequestMapping(value = "/save", method = RequestMethod.POST)
	public String save(
			@ModelAttribute("document") Document document,
			@RequestParam("file") MultipartFile file) {

		System.out.println("Name:" + document.getName());
		System.out.println("Desc:" + document.getDescription());
		System.out.println("File:" + file.getName());
		System.out.println("ContentType:" + file.getContentType());

		try {
			Blob blob = Hibernate.createBlob(file.getInputStream());

			document.setFilename(file.getOriginalFilename());
			document.setContent(blob);
			document.setContentType(file.getContentType());
		} catch (IOException e) {
			e.printStackTrace();
		}

		try {
			documentDao.save(document);
		} catch(Exception e) {
			e.printStackTrace();
		}

		return "redirect:/index.html";
	}

	@RequestMapping("/download/{documentId}")
	public String download(@PathVariable("documentId")
			Integer documentId, HttpServletResponse response) {

		Document doc = documentDao.get(documentId);
		try {
			response.setHeader("Content-Disposition", "inline;filename="" +doc.getFilename()+ """);
			OutputStream out = response.getOutputStream();
			response.setContentType(doc.getContentType());
			IOUtils.copy(doc.getContent().getBinaryStream(), out);
			out.flush();
			out.close();

		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@RequestMapping("/remove/{documentId}")
	public String remove(@PathVariable("documentId")
			Integer documentId) {

		documentDao.remove(documentId);

		return "redirect:/index.html";
	}
}

The spring controller defines four methods to manipulate document manager application.

  • index method – This method uses list() method of DocumentDAO to fetch the list of all documents from database. Note that we have mapped request “/index” to this method. Thus Spring will automatically calls this method whenever it encounters this url in request.
  • save method – This method adds a new document to document list. The document details are fetched in Document object using @ModelAttribute annotation. Also note that the request “/save” is mapped with this method. The request method should also be POST. Once the document is added in document list, we redirect to /index.html page which in turn calls index() method to display document list to user. One more thing to note here is @RequestParam. We are mapping MultipartFile object using @RequestParam(“file”) annotation. Spring automatically detects “file” data from request and map it with MultipartFile object. This object is later converted to BLOB object and set in the Document content.

    Related: Forms in Spring MVC

  • download method – This method is used to download a selected testcase. Note that we are fetching the document content from database using DAO class and thn set the Data stream in Response. Also note that we are using response.setHeader() method to set "Content-Disposition". This will raise a Save As dialog box in browser whenever user tries to download a document.
  • remove method – This methods removes a document from the document list. Similar to save() this method also redirects user to /index.html page once the document is removed. One thing to note in this method is the way we have mapped request url using @RequestMapping annotation. The url “/remove/{documentId}” is mapped thus whenever user send a request /remove/12.html, the remove method will try to delete document with ID:12.

Finally add following JSP file to WEB-INF/jsp folder.
File: /src/main/webapp/WEB-INF/jsp/documents.jsp

<%@taglib uri="http://www.springframework.org/tags" prefix="spring"%>
<%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
	<title>Document Manager - viralpatel.net</title>
</head>
<body>

<h2>Document Manager</h2>

<h3>Add new document</h3>
<form:form method="post" action="save.html" commandName="document" enctype="multipart/form-data">
	<form:errors path="*" cssClass="error"/>
	<table>
	<tr>
		<td><form:label path="name">Name</form:label></td>
		<td><form:input path="name" /></td> 
	</tr>
	<tr>
		<td><form:label path="description">Description</form:label></td>
		<td><form:textarea path="description" /></td>
	</tr>
	<tr>
		<td><form:label path="content">Document</form:label></td>
		<td><input type="file" name="file" id="file"></input></td>
	</tr>
	<tr>
		<td colspan="2">
			<input type="submit" value="Add Document"/>
		</td>
	</tr>
</table>	
</form:form>

<br/>
<h3>Document List</h3>
<c:if  test="${!empty documentList}">
<table>
<tr>
	<th>Name</th>
	<th>Description</th>
	<th>&nbsp;</th>
</tr>
<c:forEach items="${documentList}" var="document">
	<tr>
		<td width="100px">${document.name}</td>
		<td width="250px">${document.description}</td>
		<td width="20px">
			<a href="${pageContext.request.contextPath}/download/${document.id}.html"><img 
				src="${pageContext.request.contextPath}/img/save_icon.gif" border="0" 
				title="Download this document"/></a> 

			<a href="${pageContext.request.contextPath}/remove/${document.id}.html"
				onclick="return confirm('Are you sure you want to delete this document?')"><img 
				src="${pageContext.request.contextPath}/img/delete_icon.gif" border="0" 
				title="Delete this document"/></a> 
		</td>
	</tr>
</c:forEach>
</table>
</c:if>
</body>
</html>

Download Source code

Click here to download full source code of Document manager application (16 KB)

Enhanced by Zemanta

This tutorial guides us on how to pass Array objects from Java to stored procedures in Oracle and also, how to retrieve an array object in Java.

All PLSQL arrays can not be called from java. An array needs to be created as TYPE, at SCHEMA level in the database and then it can be used with ArrayDescriptor in Java, as oracle.sql.ArrayDescriptor class in Java can not access at package level.

Database Code

First, Create an array, at SCHEMA level. An example is shown below:

CREATE TYPE array_table AS TABLE OF VARCHAR2 (50); -- Array of String
CREATE TYPE array_int AS TABLE OF NUMBER;          -- Array of integers

Next, Create a procedure which takes an array as an input parameter and returns an array as its OUT parameter.

An example of one such procedure is shown below, which has 2 parameters –

  1. an array of String as its IN parameter – p_array
  2. an array of Integers as OUT parameter – p_arr_int
CREATE OR REPLACE PROCEDURE SchemaName.proc1 (p_array     IN     array_table,
                                              len            OUT NUMBER,
                                              p_arr_int      OUT array_int)
AS
   v_count   NUMBER;
BEGIN
   p_arr_int := NEW array_int ();
   p_arr_int.EXTEND (10);
   len := p_array.COUNT;
   v_count := 0;
   FOR i IN 1 .. p_array.COUNT
   LOOP
      DBMS_OUTPUT.put_line (p_array (i));
      p_arr_int (i) := v_count;
      v_count := v_count + 1;
   END LOOP;
END;
/

After this, Execution permission would be required to execute the procedure created by you:

GRANT EXECUTE ON SchemaNAme.proc1 TO UserName;

Java Code

Create a java class which makes a call to the procedure proc1, created before.

Below is an example which contains the whole flow from creating a connection with the database, to making a call to the stored procedure, passing an array to Oracle procedure, retrieving an array from an Oracle procedure and displaying the result.

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class TestDatabase {
    
    public static void passArray()
    {
        try{
        
            Class.forName("oracle.jdbc.OracleDriver");         
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:url ","UserName","Password");;
            
            String array[] = {"one", "two", "three","four"};
            
            ArrayDescriptor des = ArrayDescriptor.createDescriptor("SchemaName.ARRAY_TABLE", con);
            ARRAY array_to_pass = new ARRAY(des,con,array);
            
            CallableStatement st = con.prepareCall("call SchemaName.proc1(?,?,?)");
            // Passing an array to the procedure -
            st.setArray(1, array_to_pass);
            st.registerOutParameter(2, Types.INTEGER);
            st.registerOutParameter(3,OracleTypes.ARRAY,"SchemaName.ARRAY_INT");
            st.execute();
            
            System.out.println("size : "+st.getInt(2));
            // Retrieving array from the resultset of the procedure after execution -
            ARRAY arr = ((OracleCallableStatement)st).getARRAY(3);
             BigDecimal[] recievedArray = (BigDecimal[])(arr.getArray());
            for(int i=0;i<recievedArray.length;i++)
                System.out.println("element" + i + ":" + recievedArray[i] + "\n");
            
        } catch(Exception e) {
            System.out.println(e);
        }
    }
    public static void main(String args[]){
        passArray();
    }
}

Brief Explanations:

  1. Class.forName() – Returns the Class object associated with the class or interface with the given string name.
  2. DriverManager.getConnection() – Attempts to establish a connection to the given database URL.
  3. oracle.sql.ArrayDescriptor – Describes an array class
  4. ArrayDescriptor.createDescriptor() – Descriptor factory. Lookup the name in the database, and determine the characteristics of this array.
  5. oracle.sql.ARRAY – An Oracle implementation for generic JDBC Array interface.
  6. CallableStatement – The interface used to execute SQL stored procedures.

References

  

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

public class Main {

  public static void main(String[] argsthrows Exception {

    Connection conn = getOracleConnection();

    // Step-2: identify the stored procedure

    String proc3StoredProcedure = “{ call proc3(?, ?, ?) }”;

    // Step-3: prepare the callable statement

    CallableStatement cs = conn.prepareCall(proc3StoredProcedure);

    // Step-4: set input parameters …

    // first input argument

    cs.setString(1“abcd”);

    // third input argument

    cs.setInt(310);

    // Step-5: register output parameters …

    cs.registerOutParameter(2, java.sql.Types.VARCHAR);

    cs.registerOutParameter(3, java.sql.Types.INTEGER);

    // Step-6: execute the stored procedures: proc3

    cs.execute();

    // Step-7: extract the output parameters

    // get parameter 2 as output

    String param2 = cs.getString(2);

    // get parameter 3 as output

    int param3 = cs.getInt(3);

    System.out.println(“param2=” + param2);

    System.out.println(“param3=” + param3);

    conn.close();

  }

  private static Connection getHSQLConnection() throws Exception {

    Class.forName(“org.hsqldb.jdbcDriver”);

    System.out.println(“Driver Loaded.”);

    String url = “jdbc:hsqldb:data/tutorial”;

    return DriverManager.getConnection(url, “sa”“”);

  }

  public static Connection getMySqlConnection() throws Exception {

    String driver = “org.gjt.mm.mysql.Driver”;

    String url = “jdbc:mysql://localhost/demo2s”;

    String username = “oost”;

    String password = “oost”;

    Class.forName(driver);

    Connection conn = DriverManager.getConnection(url, username, password);

    return conn;

  }

  public static Connection getOracleConnection() throws Exception {

    String driver = “oracle.jdbc.driver.OracleDriver”;

    String url = “jdbc:oracle:thin:@localhost:1521:caspian”;

    String username = “mp”;

    String password = “mp2”;

    Class.forName(driver)// load Oracle driver

    Connection conn = DriverManager.getConnection(url, username, password);

    return conn;

  }

}

           

         

    

  

CallStoresProcedureInOracleAndPassInOutParameters.zip( 3,849 k)