Using the DAO (Data Access Object) J2EE Design pattern with ADF

I have always been a big fan of ADF BC , after all it's a lot less coding but I still do write DAO (Data Access Objects) applications and using ADF it doesn't take look to get those DAO method which return database data results onto my page. I find the ADF JSF experience just a little too rich so I always opt for for using pure JSP pages and using the Struts Page Flow to quickly assemble my web UI. His an example of how easy it is to get data from your DAO queries onto your JSP pages using ADF in JDeveloper 10.1.3.3.

TalentDAO Interface

package oracle.support.au.dao;

import java.util.List;
import oracle.support.au.dao.beans.Manager;


public interface TalentDAO
{
public List<Manager> retrieveManagerList() throws TalentException;

.....

TalentDAOImpl Class

package oracle.support.au.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.List;

import java.util.logging.Level;

import java.util.logging.Logger;

import oracle.support.au.dao.beans.Manager;

public class TalentDAOImpl implements TalentDAO
{
// Use JDK Logging
private static Logger logger = Logger.getLogger("talentlogging");

public List<Manager> retrieveManagerList() throws TalentException
{
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
List<Manager> mgrs = null;

conn = TalentUtil.getDataSourceConnection();

try
{
  stmt = conn.createStatement();
  rset = stmt.executeQuery(Constants.ALL_MANAGERS);

  mgrs = TalentUtil.makeManagerListFromResultSet(rset);
}
catch (SQLException se)
{
  logger.log
   (Level.SEVERE,
    "** TALENT (TalentDAOImpl.retrieveManagerList() : " +
    se.getMessage());
  throw new TalentException(se);
}
finally
{
  // close all resources
  JDBCUtil.close(rset, stmt, conn);
}

return mgrs;
}

.....

1. Right click on my DAO Implementation class and select "Create Data Control"
2. Open up my Struts Page Flow diagram
3. Drag and Drop a "Data Page" onto the diagram
4. Double click on the Data Page icon and create your JSP page.
5. Open up the "Data Control Palette"
6. Drag the the "Result" of the method retrieveManagerList() onto the Visual Editor and select "Tables -> Read Only Table".

That's all you need to do here and the Manager bean which is simply a java class with getter/setters for each property is what is in the actual return list which we clearly specify by the method signature. ADF data binding will then do it's magic to expose the return List of Managers to the UI at runtime without having to write any code to setup the bindings.

public List<Manager> retrieveManagerList() throws TalentException

Be sure to run the Data Page itself and not the JSP page to see the return results in a table.

Avoiding having to enter password when invoking SQL*Plus

Back in the early days with JDeveloper you could invoke SQL*Plus without having to provide a password but now in 10g you are always promoted for a password. To get around this I simply created an "External Tools" entry which allowed me to invoke SQL*Plus without a password, his how I did this.

1. Tools -> External Tools
2. Select the "New" button
3. Leave the default option as "External Program" and press Next
4. For the "Program Executable" refer to SQL*PLus on your file system.

D:\oracle\product\10.2.0\db_1\BIN\sqlplusw.exe

5. Enter in the full description which includes the username/password in the arguments text field.

talent/talent@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=
(PROTOCOL=TCP)(HOST=papicell-au2.au.oracle.com)(PORT=1521)))
(CONNECT_DATA=(SID=lnx102))) @${file.path}

Notice how I use @ to invoke the current selected file which will automatically call my SQL for me.

6. Enter your display properties and click Next
7. I normally add my integration from the navigator context menu, so select that and press Next
8. Finally associate this with SQL files and press Finish

Now when we right click on a SQL file in navigator and select the new context menu option we just created it will run that SQL file in SQL*Plus without prompting me for a password. Saves a lot of time.

External Tables with 11g JDBC driver

External tables arrived in the Oracle 10g database today I had to give it a test drive on a 11g database with the 11g JDBC driver. In the old days I would use sql*loader to load a flat file into the database and then access the data but with external tables you can avoid that as shown in this very simple demo.

External Data file: mydata.txt

1, 'Pas Apicella'
2, 'Adrian Campanaro'
3, 'Fred Blog'
4, 'Dave Hughes'
5, 'Joe Blow'

1. Create a directory on the server where the file exists

create directory scott_dir as '/home/oracle/pas';

2. Create the external table as shown below

drop table my_emps;

create table my_emps
(empno number,
ename varchar2(20))
organization external
(type oracle_loader
default directory scott_dir
access parameters
(
  fields terminated by ','
  optionally enclosed by "'"
  missing field values are null
)
location ('mydata.txt')
);

3. Here is the simple JDBC code which sees it as an ordinary table

 public void run() throws SQLException
 {
   Connection conn = getConnection();
   conn.setAutoCommit(false);
   Statement stmt = null;
   ResultSet rset = null;
   String sql = "select empno, ename from my_emps";
   SQLWarning x = null;
 
   try
   {
     stmt = conn.createStatement();
     rset = stmt.executeQuery(sql);
     
     while (rset.next())
     {
       System.out.println
         (rset.getInt(1) + " : " +
          rset.getString(2));
     }
 

     System.out.println("Successfully read external table");
     conn.commit();
   }
   catch (Exception e)
   {
     e.printStackTrace();
   }
   finally
   {
     if (rset != null)
     {
       rset.close();
     }

     if (stmt != null)
     {
       stmt.close();
     }

     if (conn != null)
     {
       conn.close();
     }
   }
 }

PreparedStatement method setNClob(int, Reader) causes HANG with 11g JDBC Driver

When using JDK 1.6 with the new 11G JDBC driver the following code causes a HANG to occur.

   // Get connection
   Connection conn = getConnection();

   // start test
   String lobContent = "WLS JDBC4 test for NClob.";

   String insertSql = "INSERT INTO nclob_table VALUES (?, ?)";
   PreparedStatement pstmt = conn.prepareStatement(insertSql);

   int loopCount = 10;
   StringReader sreader = null;

   try
   {
       for (int i = 0; i < loopCount; i++)
       {
           pstmt.setInt(1, i);
           sreader = new StringReader(lobContent + "-" + i);
           // Hangs here
           pstmt.setNClob(2, sreader);
           pstmt.execute();
           sreader.close();
           System.out.println("inserted a row..." );
       }
   }
   finally
   {
       if (pstmt != null)
       {
           pstmt.close();
       }
       if (conn != null)
       {
           conn.close();
       }
   }
 }

The hang occurs at this line of code:

pstmt.setNClob(2, sreader);

In order to avoid the hang you can use the following method:

• setNClob(int, Reader, long)

This then works fine when the following is used.

pstmt.setNClob(2, sreader, sreader.toString().length());

Inserting/Reading BLOB's with 11g / JDK 1.6

Here is some code I used to insert/read BLOB images in an 11g database using 11g JDBC driver with JDK 1.6. In these tests I uploaded 30K images which was lighting fast as you would expect.

Table:

drop sequence blob_seq;
drop table blob_fun;

create sequence blob_seq
start with 1
increment by 1
/

create table blob_fun (id number, image_col blob, image_size number)
/

Insert Code:

  public void run() throws SQLException, IOException
 {
   Connection conn = getConnection();
   conn.setAutoCommit(false);
   PreparedStatement pstmt = null;
   String sql = "insert into blob_fun values (blob_seq.nextval, ?, ?)";
 
   byte [] b = new byte[1024];
    ByteArrayOutputStream b1 = new ByteArrayOutputStream();

   FileInputStream f1 = new FileInputStream("D:\\temp\\blob\\ip-settings.jpg");
 
   int i;
   do
   {
     i = f1.read(b);
     b1.write(b);
   } while( i!= -1);

   ByteArrayInputStream bais = new ByteArrayInputStream(b1.toByteArray());
 
   try
   {
     pstmt = conn.prepareStatement(sql);
     pstmt.setBlob(1, bais);
     pstmt.setInt(2, bais.available());
     pstmt.execute();
   
     System.out.println("Successfully uploaded BLOB");
     conn.commit();
     bais.close();
     f1.close();
   }
   catch (Exception e)
   {
     e.printStackTrace();
   }
   finally
   {
     if (pstmt != null)
     {
       pstmt.close();
     }
   
     if (conn != null)
     {
       conn.close();
     }
   }

   conn.close();
 }

Read Code:

public void run() throws SQLException, IOException
{
  Connection conn = getConnection();
  conn.setAutoCommit(false);
  PreparedStatement pstmt = null;
  BLOB b = null;
  ResultSet rset = null;
  String sql = "select image_col from blob_fun where id = 1";

  try
  {
    pstmt = conn.prepareStatement(sql);
    rset = pstmt.executeQuery();
    rset.next();
    b = (BLOB) rset.getBlob(1);
    int chunk = b.getChunkSize();
    byte[] buffer = new byte[chunk];
    int length;
        
    FileOutputStream outFile = null;
    outFile = new FileOutputStream
      ("D:\\temp\\blob\\readfromdbfile.jpg");
    InputStream instream = b.getBinaryStream();
    
    // Fetch data  
    while ((length = instream.read(buffer)) != -1)
    {
      outFile.write(buffer, 0, length);
    }

    // Close input and output streams
    instream.close();
    outFile.close();  
  
    System.out.println("Successfully read/saved BLOB to file system from DB");
    conn.commit();
  }
  catch (Exception e)
  {
     e.printStackTrace();
  }
  finally
  {
    if (rset != null)
    {
      rset.close();
    }
  
    if (pstmt != null)
    {
      pstmt.close();
    }

    if (conn != null)
    {
      conn.close();
    }
  }

  conn.close();
}

UpdateableResultSet with BLOB column

When trying to use an UpdateableResultSet with a BLOB column the following error occurs:

Exception in thread "main" java.sql.SQLException: Invalid operation for read only resultset: updateBlob
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)

The code being used looks something like this:

   String querySql2 = "SELECT id, col2 FROM blob_test";
 
   stmt = null;
   stmt = conn.createStatement
     (ResultSet.TYPE_SCROLL_INSENSITIVE,
      ResultSet.CONCUR_UPDATABLE);
   ResultSet rs = null;
   rs = stmt.executeQuery(querySql2);
   rs.next();
   // Fails at this line with exception
   rs.updateBlob(2, inputStreamUpdate); 

This occurs because updateBlob(int, java.io.InputStream) realizes that the user is binding a stream with unknown length and throws an exception. To get around this you need to use the following two methods which work fine.

• updateBlob(int, Blob)
• updateBlob(int,java.io.InputStream, long)

See the following documentation for more information on ResultSet's:

http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/resltset.htm#i1022319