Java utility to create/populate a DVM from XL sheet -SOA 11g

Today I am posting the java code which reads in a input XL with the dvm data and creates a dvm file. I had created a utility in 10g , now just modified it for 11g.This is very useful in scenarios where there is huge amount of data to be inserted into DVM before the interface can start working. Even though we have soa composer and JDev to enter the recods into DVM. manual entry of large number of DVM data is not a feasible solution For running the java program pass the 2 arguments
for eg: -inputXL=D:\Projects\DVMTest\Sites.xls
-outputDVM=D:\Projects\DVMTest\SiteTest.dvm
This will work for any number of columns.The first row in the XL sheet represents the column name that needs to appear in the DVM. If decimal points are not required modify the code to replace the decimal value. Hope this helps.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


/**
 * This class has methods which will read the excel file and convert it into a
 * a DVM file.
 *
 *@author George Thomas
 *@version 1.0
 *
 */
public class DVMCreator {
    private final static Logger logger =
        Logger.getLogger(DVMCreator.class.getName());

    public DVMCreator() {
        super();
    }


    private String inputFile;

    /**
     * @param inputFile
     */
    public void setInputFile(String inputFile) {
        this.inputFile = inputFile;
    }


    /**
     * This method listens for incoming records and handles them as required.
     *
     * @return sheetData
     * @throws Exception
     */
    public List getSheetData() throws Exception {
        //
        // Create an ArrayList to store the data read from excel sheet.
        //
        List sheetData = new ArrayList();

        FileInputStream fis = null;
        try {
            //
            // Create a FileInputStream that will be use to read the
            // excel file.
            //
            fis = new FileInputStream(inputFile);

            //
            // Create an excel workbook from the file system.
            //
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            //
            // Get the first sheet on the workbook.
            //
            HSSFSheet sheet = workbook.getSheetAt(0);

            //
            // When we have a sheet object in hand we can iterator on
            // each sheet's rows and on each row's cells. We store the
            // data read on an ArrayList so that we can printed the
            // content of the excel to the console.
            //
            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow)rows.next();
                Iterator cells = row.cellIterator();
                List data = new ArrayList();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell)cells.next();
                    data.add(cell);
                }

                sheetData.add(data);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                fis.close();
            }
            showExcelData(sheetData);
            // processExelData(sheetData);
            return sheetData;
        }
    }


     private static String convertExcelToDVMData(List sheetData,String dvmName ) {
        //
        // Iterates the data and print it out to the console.
        //
        StringBuffer xmlData = new StringBuffer();
      
      dvmName = dvmName.replaceAll(".dvm", "");

        
        Map map = new HashMap();
        xmlData.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
        xmlData.append("<dvm name=\"");
        xmlData.append(dvmName);
        xmlData.append("\"");
        xmlData.append(" xmlns=\"http://xmlns.oracle.com/dvm\">");
        
        xmlData.append("<description></description>");
        xmlData.append("<columns>");


        List columnNamelist = (List)sheetData.get(0);
        for (int j = 0; j < columnNamelist.size(); j++) {
            xmlData.append("<column name=\"");
            HSSFCell cell = (HSSFCell)columnNamelist.get(j);
            if (cell.getCellType() == 0) {
                logger.log(Level.INFO, " " + cell.getNumericCellValue());
                xmlData.append(cell.getNumericCellValue());

            } else if (cell.getCellType() == 1) {
                logger.log(Level.INFO,
                           "S " + cell.getRichStringCellValue().getString());
                xmlData.append(cell.getRichStringCellValue().getString());
            }
            xmlData.append("\"/>");

        }
        logger.log(Level.INFO, "");
        xmlData.append("</columns>");


        xmlData.append("<rows>");

        for (int i = 1; i < sheetData.size(); i++) {
            xmlData.append("<row>");
            List list = (List)sheetData.get(i);
            for (int j = 0; j < list.size(); j++) {
                xmlData.append("<cell>");
                HSSFCell cell = (HSSFCell)list.get(j);
                if (cell.getCellType() == 0) {
                    logger.log(Level.INFO, " " + cell.getNumericCellValue());
                    xmlData.append(cell.getNumericCellValue());

                } else if (cell.getCellType() == 1) {
                    logger.log(Level.INFO,
                               "S " + cell.getRichStringCellValue().getString());
                    xmlData.append(cell.getRichStringCellValue().getString());
                }
                xmlData.append("</cell>");
                if (j < list.size() - 1) {
                    logger.log(Level.INFO, ", ");
                }
            }
            logger.log(Level.INFO, "");
            xmlData.append("</row>");
        }
        xmlData.append("</rows>");
        xmlData.append("</dvm>");

        return xmlData.toString();
    }

  



    

  public static boolean writeDVM(String xmlData, File file) throws Exception {

       
      try {
          FileOutputStream fOut = new FileOutputStream(file);
          fOut.write(xmlData.getBytes());
          fOut.flush();
          fOut.close();
          logger.log(Level.INFO, "File Created .::" + file.getName());

      } catch (Exception e) {
          logger.log(Level.INFO,
                     "!!BOOOM!! xlCreate() failed : " + e.getMessage());
          throw e;
      }
      return true;
  }
  
  
    /**
     * main method
     *
     * @param args      Expect one argument that is the file to read.
     * @throws IOException  When there is an error processing the file.
     */
    public static void main(String[] args) throws IOException {
        DVMCreator reader = new DVMCreator ();
        String spliter[] = null;
        String inputXL = null;
        String outputDVM = null;
        if (args.length == 0 || args.length == 1) {
            logger.log(Level.INFO, " USAGE:- Enter the input excel file \n" +
                    "     -inputXL  and enter the output DVM file location and name -outputDVM \n");
        } else if (args[0].equals("-help")) {
            logger.log(Level.INFO, "USAGE:- Enter the input excel file \n" +
                    "     -inputXL  and enter the output DVM file location and name -outputDVM \n");
        } else {
            if (args[0].contains("-inputXL") &&
                args[1].contains("-outputDVM")) {
                spliter = args[0].split("=");
                inputXL = spliter[1];
                reader.setInputFile(inputXL);
                spliter = args[1].split("=");
                outputDVM = spliter[1];
                File file = null;
              try {
                   file = new File(outputDVM);
                  logger.log(Level.INFO, "File Created .::" + outputDVM);

              } catch (Exception e) {
                  logger.log(Level.INFO,
                             "!!BOOOM!! xlCreate() failed : " + e.getMessage());
                
              }

                try {
                    List data = reader.getSheetData();
                    
                    String xmlData = convertExcelToDVMData(data,file.getName());
                    boolean flag = writeDVM(xmlData, file);
                    logger.log(Level.INFO, "XML data ::" + xmlData);


                } catch (Exception e) {
                    logger.log(Level.INFO, "Error occured" + e.getMessage());
                    e.printStackTrace();
                }
                logger.log(Level.INFO, "DVM  write done.");

            }


        }
    }
}

SOA Composer with DVM’s

DVM’s become a headache if you have shared DVM’s across projects and multiple people try to update it.SOA Composer helps you in maintaining consistency for editing and maintaining the DVMs by allowing users to access all DVMs through the console. The SOA Composer is an EAR file, which is installed as part of Oracle SOA Suite installation. It enables you to manage domain value maps at runtime. I will take you thru steps on how to use it.

Login using the below URL

http://hostname:port/soa/composer

To view domain value maps at runtime:

From the Open menu, select Open DVM.

It will list you all the DVM’s present in the server with Partition and Composite name. It will mention Composite Name if DVM is deployed along with the Composite. If the Composite is accessed from MDS store, then Composite name will be shown as N/A

Select DVM to view the entries in DVM

How to Edit Domain Value Maps at Runtime

The domain value map opens in an edit session.

To add rows:

1.    Click Add Domain Values.

2.    Enter values and click OK. The entered values are added to the domain value map.

To edit rows:

1.    Select the row that you want to edit.

2.    Click Edit Domain Values. Edit the values as required and click OK.

To delete rows:

1.    Select the rows that you want to delete.

2.    Click Delete Domain Values.

Saving DVM’s at Runtime

Every time a domain value map is opened in an edit session, a sandbox is created per domain value map, per user. If you save your changes, then the changes are saved in your sandbox.

1.    Click the Save menu item to save your changes. If your changes are saved successfully, you receive a notification message.

Undo Changes made to DVM’s at Runtime

You can also revert a domain value map to the last saved state.

1.    Click the Revert menu item. A confirmation dialog is displayed.

Commit Changes at Runtime

Once you commit the changes, runtime picks up the changes and saves them in the MDS repository. In a session, you can also save your changes without committing them. In such a case, the domain value map remains in the saved state. You can reopen the domain value map and commit the changes later.

Note :

In some scenarios customers prefer to keep all DVMs in one shared project and use these DVMs across all SOA projects. After DVM project is deployed to production, DVM values are added /modified from SOA composer. But when they try to add a new DVM to the shared DVM project and redeploy to production, it overrides all changes made through SOA composer. This is a typical scenario you should avoid while designing. In order to avoid this issue, try to use MDS for storing the DVM’s. If you have DVM’s @MDS we can always go ahead an export the updated DVM’s and modify the exported DVM for further usage.