Saving and fetching multilingual data using JDBC and Oracle9i database!

A particular feature of storing and retrieving multilingual data in Oracle9i is using the column type NVARCHAR2. The encoding used in the SQL NCHAR/NVARCHAR2 datatypes is specified as the national character set of the database.The encoding can be AL16UTF16 or UTF8. This column type stores Unicode characters. NVARCHAR2 can be used as the datatype for fields that need to store multilingual data.

Java programs can insert or retrieve Unicode data to and from columns of SQL CHAR and NCHAR datatypes. 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.

Oracle JDBC drivers convert data in the SQL NCHAR/NVARCHAR2 column from the national character set, which is either UTF8 or AL16UTF16, directly to UTF-16 encoded Java strings. For that, we need to bind the Java string to a NVARCHAR2 column. For binding or defining Java string variables to SQL NCHAR datatypes,we can use edStatement.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.

Consider a table called Products with columns defined as below:
id - VARCHAR2(10) - product id
lang_id - VARCHAR2(10) - language id of the product description
description - NVARCHAR2(100) - product description in Unicode

The following snippet shows the code to do that :
import oracle.jdbc.OraclePreparedStatement;

// Get an Oracle preparedstatementOraclePreparedStatement orastmt =(OraclePreparedStatement)connection.prepareStatement("INSERT INTO PRODUCTS VALUES(?,?,?)");

// Bind the 3rd parameter to NVARCHAR2 form so that the data is stored as unicode
orastmt.setFormOfUse(3,OraclePreparedStatement.FORM_NCHAR);
orastmt.setString(1,product.getId());
orastmt.setString(2,product.getLangId());
orastmt.setString(3,product.getDescription());
orastmt.executeUpdate();
orastmt.close();

We use orastmt.setFormOfUse() method to specify that the column is of type NVARCHAR2. This will ensure that the data will be stored as Unicode.Retrieving the data from this column is straightforward uses normal JDBC code.

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM PRODUCTS ");

ResultSet rSet = stmt.executeQuery();