print Printer View e-mail E-mail this page Bookmark Bookmark 
/**
 * @author  Elangovan

 * @version 1.0
 *
 * Development Environment        :  Oracle9i JDeveloper
 * Name of the Application        :  NCHARsupport4UnicodeSample.java
 * Creation/Modification History  :
 *

 *    Elangovan       17-Oct-2001      Created
 *    Venky           17-Feb-2003      Certfied on Linux platform
 *
 * Overview of Application        :
 *
 *    This sample illustrates the access and manipulation of Unicode data using
 *  Oracle9i JDBC drivers.

 *
 *  Java programs can insert or retrieve Unicode data to and from columns of SQL CHAR
 *  and NCHAR datatypes. Specifically, JDBC allows Java programs to bind or define
 *  Java strings to SQL CHAR and NCHAR datatypes. SQL NCHAR datatypes were created
 *  for Globalization Support. SQL NCHAR datatypes include NCHAR, NVARCHAR2 and NCLOB.
 *
 *  For binding or defining Java string variables to SQL NCHAR datatypes,
 *  Oracle extends the JDBC specification to add the OraclePreparedStatement.setFormOfUse()

 *  method through which you can explicitly specify the target column of a bind variable
 *  to be a SQL NCHAR datatype.
 *
 *   Note that the setFormOfUse() method statement must be called before binding
 *  or defining Java variables to SQL NCHAR datatypes.
 *
 *   This class handles the logic of the application, the GUI is handled
 *   separately in NCHARsupport4UnicodeFrame.java.
 */

package oracle.otnsamples.oracle9ijdbc.ncharsupport4unicode;

import oracle.jdbc.OraclePreparedStatement;

// Oracle extensions
import oracle.jdbc.pool.OracleDataSource;

import java.io.IOException;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// JDBC imports
import java.sql.Statement;

import java.util.Enumeration;

// Java Utility Classes
import java.util.Properties;

import java.util.ResourceBundle;
import java.util.Vector;


public class NCHARsupport4UnicodeSample {
    // Language Id's of list of supported languages in Oracle
    private static String[] langId = null;

    // Connection instance
    Connection connection = null;

    // GUI handling

    NCHARsupport4UnicodeFrame gui = null;

    /**
    *  Constructor. Initializes the GUI
    **/
    public NCHARsupport4UnicodeSample() {
        gui = new NCHARsupport4UnicodeFrame(this);
    }

    /**
    *   Main entry point for the class. Connects to the database and displays
    *   the list of available products.
    **/

    public static void main(String[] args) {
        // Instantiate the class
        NCHARsupport4UnicodeSample sample = new NCHARsupport4UnicodeSample();

        // Connect to the database
        sample.dbConnect();

        // If connected, retrieve the product details.
        if (sample.connection != null) {
            sample.checkTables();
            sample.displayProductsInfo();
        }
    }


    /**
    *   Dispatches the appropriate methods to handle the events generated
    *   in the GUI. The methods do the required JDBC operations.
    **/
    public void dispatchEvent(String event) {
        if (event.equals("PRODUCT CHANGED")) {
            // Get product details for the selected product
            getProductDetails();
        } else if (event.equals("INSERT")) {
            // Insert product details
            insertProductDetails();
        } else if (event.equals("UPDATE")) {
            // Update the product details
            updateProductDetails();
        } else if (event.equals("EXIT")) {

            // Perform cleanup and close the application
            exitApplication();
        }
    }

    /**
     * This method reads a properties file which is passed as
     * the parameter to it and load it into a java Properties
     * object and returns it.
     */
    public static Properties loadParams(String file) throws IOException {
        // Loads a ResourceBundle and creates Properties from it
        Properties     prop   = new Properties();
        ResourceBundle bundle = ResourceBundle.getBundle(file);
        Enumeration    enum   = bundle.getKeys();
        String         key    = null;


        while (enum.hasMoreElements()) {
            key = (String) enum.nextElement();
            prop.put(key, bundle.getObject(key));
        }

        return prop;
    }

    /**
     * Creates a database connection object using DataSource object. Please
     * substitute the database connection parameters with appropriate values in
     * Connection.properties file
     */
    public void dbConnect() {
        try {
            gui.putStatus("Trying to connect to the Database");


            // Load the properties file to get the connection information
            Properties prop = NCHARsupport4UnicodeSample.loadParams("Connection");

            // Create a OracleDataSource instance
            OracleDataSource ods = new OracleDataSource();

            // Sets the driver type
            ods.setDriverType("thin");

            // Sets the database server name
            ods.setServerName((String) prop.get("HostName"));

            // Sets the database name
            ods.setDatabaseName((String) prop.get("SID"));

            // Sets the port number
            ods.setPortNumber(new Integer((String) prop.get("Port")).intValue());


            // Sets the user name
            ods.setUser((String) prop.get("UserName"));

            // Sets the password
            ods.setPassword((String) prop.get("Password"));

            // Create a connection  object
            connection = ods.getConnection();

            gui.putStatus(" Connected to " + prop.get("SID") + " Database as " +
                          prop.get("UserName"));
        } catch (SQLException ex) { // Handle SQL errors
            gui.putStatus("Error in Connecting to the Database " + '\n' +
                          ex.toString());
        }catch (IOException ex) { // Handle IO errors
            gui.putStatus("Error in reading the properties file " + '\n' +
                          ex.toString());
        }

    }

    /**
    *   Displays the list of available products and the list of languages
    *   supported by the database.
    **/
    public void displayProductsInfo() {
        // Display available products
        displayProducts();

        // Display the list of languages supported by Oracle
        displayLang();

        // Enable selection of product and language from the Comboboxs.
        gui.enableSelect();
    }

    /**
    *   Queries the list of products and displays them in the product id
    *   Combobox.

    **/
    public void displayProducts() {
        // Statement to fetch Product Id's from the product_descriptions table
        Statement stmt = null;

        // ResultSet to hold the returned Product Ids
        ResultSet rset = null;

        String    productId = null;

        try {
            // Create a statement object
            stmt = connection.createStatement();

            // Retrieve the Product Ids
            rset = stmt.executeQuery(" SELECT DISTINCT(Product_Id) " +
                                     " FROM Product_Descriptions ");

            // Loop until all the products are added to the Combobox
            while (rset.next()) {
                productId = rset.getString(1);

                gui.addToProdCombo(productId);
            }
        } catch (Exception ex) { // Trap errors
            gui.putStatus(" Error while querying Product Details :" +
                          ex.toString());
        } finally {
            try {
                // Close the Statement and ResultSet objects
                if (rset != null) {
                    rset.close();
                }

                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception ex) {
                gui.putStatus(" Error : " + ex.toString());
            }
        }
    }

    /**

    *   Queries the Languages table to display the list of supported languages.
    **/
    public void displayLang() {
        // Statement to query the Languages table
        Statement stmt = null;

        // ResultSet to hold the returned languages
        ResultSet rset = null;

        Vector    langId = new Vector();

        String    langName = null;

        try {
            // Create a Statement object
            stmt = connection.createStatement();

            // Retrieve the Language names
            rset = stmt.executeQuery(" SELECT LangId,LangName " +
                                     " FROM Languages ");

            // Loop until all the languages are added to the Combobox
            while (rset.next()) {

                // Add language Id to vector
                langId.addElement(rset.getString(1));

                // Add the language Name to Combobox
                langName = rset.getString(2);
                gui.addToLangCombo(langName);
            }

            // Initialize the length of String array to size of vector
            this.langId = new String[langId.size()];

            // Copy the data in the vector into the String array
            langId.copyInto(this.langId);

            langId = null;
        } catch (Exception ex) { // Trap errors
            gui.putStatus(" Error while querying languages :" + ex.toString());
        } finally {
            try {
                // Close the Statement and ResultSet objects
                if (rset != null) {
                    rset.close();
                }


                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception ex) {
                gui.putStatus(" Error : " + ex.toString());
            }
        }
    }

    /**
    *   Queries the Product_Descriptions table for the selected Product.
    *   If the product description is available in the selected language then,
    *   displays the name and description, else a message in the status bar is displayed.
    *   Here the Product Name and Description columns are of type NVARCHAR2.
    **/
    public void getProductDetails() {
        // PreparedStatement object to retrieve the product details
        PreparedStatement pstmt = null;

        // ResultSet to hold the retrieved product details
        ResultSet rset = null;

        // Get the selected Product Id and Language
        int productId = gui.getSelectedProductId();


        // Get the language Id
        String langId = this.langId[gui.getSelectedLanguage()];

        String productName = null;
        String productDesc = null;

        try {
            // Retrieve the product details in the selected language.
            pstmt = connection.prepareStatement(" SELECT Translated_Name, Translated_Description " +
                                                " FROM  Product_Descriptions " +
                                                " WHERE Product_Id=? AND Language_Id=? ");

            // Set the Product Id
            pstmt.setInt(1, productId);

            // Set the Language
            pstmt.setString(2, langId);

            // Execute the query
            rset = pstmt.executeQuery();

            // If product details available then retrieve the details
            if (rset.next()) {
                // The usage of SQL NCHAR and SQL NVARCHAR2 datatype is similar to that
                // of accessing any other SQL CHAR and SQL VARCHAR2 datatypes.
                // JDBC uses the same classes to access and manipulate SQL NCHAR

                // datatypes that are used for the corresponding SQL CHAR datatypes
                productName = rset.getString(1);
                productDesc = rset.getString(2);
            }

            // Display the details for view/update
            gui.displayProduct(productName, productDesc);
        } catch (Exception ex) { // Trap errors
            gui.putStatus(" Couldn't retrieve Product Desc:" + ex.toString());
        } finally {
            try {
                // Close the Statement and ResultSet objects
                if (rset != null) {
                    rset.close();
                }

                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception ex) {
                gui.putStatus(" Error : " + ex.toString());
            }
        }
    }

    /**
     *   Updates the Product Name and Description of the product to the

     *   Product_Descriptions table. Here the Product Name and Description columns
     *   are of type NVARCHAR2.
     **/
    public void updateProductDetails() {
        // PreparedStatement object to update the product details
        PreparedStatement pstmt = null;

        // Get the Product Id that is to be updated
        int productId = gui.getSelectedProductId();

        // Get the Language Id
        String langId = this.langId[gui.getSelectedLanguage()];

        // Get the updated Product name and description from Textboxes
        String productName = gui.getProductName();
        String productDesc = gui.getProductDesc();

        try {
            // Update the Product name and description with the new data
            pstmt = connection.prepareStatement(" UPDATE Product_Descriptions " +
                                                " SET Translated_Name=?,Translated_Description=? " +
                                                " WHERE Product_Id=? AND Language_Id=? ");

            // The only difference in usage between the SQL CHAR and SQL NCHAR
            // datatypes occur in a data bind situation. The JDBC program must call the
            // setFormOfUse() method to specify if the data is bound for a SQL NCHAR
            // datatype and it must be called before binding Java variables to
            // SQL NCHAR datatypes.

            ((OraclePreparedStatement) pstmt).setFormOfUse(1,
                                                           OraclePreparedStatement.FORM_NCHAR);

            // Set the Product Name
            pstmt.setString(1, productName);

            ((OraclePreparedStatement) pstmt).setFormOfUse(2,
                                                           OraclePreparedStatement.FORM_NCHAR);

            // Set the Product Description
            pstmt.setString(2, productDesc);

            // Set the product Id whose details have to be updated
            pstmt.setInt(3, productId);

            // Set the Language Id
            pstmt.setString(4, langId);

            // Execute the update
            pstmt.executeUpdate();

            gui.putStatus(" Product Details Updated  ");
        } catch (Exception ex) { // Trap errors
            gui.putStatus(" Couldn't UPDATE Product Desc:" + ex.toString());
        } finally {
            try {
                // Close the Statement object
                if (pstmt != null) {
                    pstmt.close();

                }
            } catch (Exception ex) {
                gui.putStatus(" Error : " + ex.toString());
            }
        }
    }

    /**
    *   Inserts the translated Product name and Product Description into
    *   Product_Descriptions table.
    **/
    public void insertProductDetails() {
        // PreparedStatement object to update the product details
        PreparedStatement pstmt = null;

        // Get the Product Id and language for which insertion has to be done
        int productId = gui.getSelectedProductId();

        // Get the Language Id
        String langId = this.langId[gui.getSelectedLanguage()];

        // Get the Product name and description from textboxes
        String productName = gui.getProductName();
        String productDesc = gui.getProductDesc();

        try {
            // Insert the Product name and description with the new data
            pstmt = connection.prepareStatement(" INSERT INTO Product_Descriptions " +
                                                " VALUES(?,?,?,?) ");


            // Set the Product Id
            pstmt.setInt(1, productId);

            // Set the Language Id
            pstmt.setString(2, langId);

            // The only difference in usage between the SQL CHAR and SQL NCHAR
            // datatypes occur in a data bind situation. The JDBC program must call the
            // setFormOfUse() method to specify if the data is bound for a SQL NCHAR
            // datatype and it must be called before binding Java variables to
            // SQL NCHAR datatypes.
            ((OraclePreparedStatement) pstmt).setFormOfUse(3,
                                                           OraclePreparedStatement.FORM_NCHAR);

            //  Set the Product Name
            pstmt.setString(3, productName);

            ((OraclePreparedStatement) pstmt).setFormOfUse(4,
                                                           OraclePreparedStatement.FORM_NCHAR);

            // Set the Product Description
            pstmt.setString(4, productDesc);

            // Execute the insert
            int inserted = pstmt.executeUpdate();

            gui.putStatus(inserted + " Product Detail Inserted  ");
        } catch (Exception ex) { // Trap errors
            gui.putStatus(" Couldn't Insert Product Details:" + ex.toString());
        } finally {
            try {

                // Close the Statement object
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception ex) {
                gui.putStatus(" Error : " + ex.toString());
            }
        }
    }

    /**
    * Checks if the table ('Languages') is present, else creates it.
    * Look into PopulateTable.java for more details
    */
    private void checkTables() {
        Statement stmt = null;
        ResultSet rset = null;

        try {
            stmt = connection.createStatement();

            // check from User_tables data dictionary table if the table is existing.
            rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables " +
                                     " WHERE Table_Name = 'Languages' ");

            // if the resultset of the above query does not have any record, it means
            // table is not existing. So the table is created.
            if (!rset.next()) {
                // call the class to create the table
                PopulateTable popTable = new PopulateTable(connection, gui);
                popTable.createSchemaTable();
                gui.putStatus("Languages Table created.");

            }
        } catch (SQLException sqlEx) {
            gui.putStatus("Could not create table Languages : " +
                          sqlEx.toString());
        } finally {
            try {
                if (rset != null) {
                    rset.close();
                }

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

    /**
    *  This method performs the clean up action and closes the application
    **/
    void exitApplication() {
        // If connected to database
        if (connection != null) {
            try {
                // Close the connection
                connection.close();
            } catch (Exception ex) { // Trap errors
                gui.putStatus(" Error while Closing the connection: " +
                              ex.toString());
            }
        }


        // Close the application
        System.exit(0);
    }
}
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy