How to store and retrieve multilingual data using JDBC and
Oracle9i database
After going through this How To document, you should be
able to understand how database supports multilingual data and how to use JDBC
to retrieve such data.
Introduction
Globalization is supported in Oracle9i
database at various levels. We shall concentrate on a particular feature of
storing and retrieving multilingual data 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.
Unicode is a universal encoded character set that allows
you to store information from any language using a single character set. Unicode
provides a unique code value for every character, regardless of the platform,
program, or language.
So NVARCHAR2 can be used as the datatype
for fields that need to store multilingual data.
We will have 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(2000) - product description in Unicode
We will have two classes to manipulate this table.
A simple Javabean Product, which represent a record
in the Products table. The bean has getters and setters for the fields.
ProductManager class, which defines
operations like addProduct,deleteProduct and getProduct on this table.
The JSP files display.jsp and add.jsp
will be used to display/add/delete products.
JDBC allows Java programs to access columns of datatype
NVARCHAR2 in an Oracle9i
database. 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.
The following snippet shows the code to do that.
// Get an Oracle preparedstatement OraclePreparedStatement 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.
While displaying the Unicode data in a JSP, we set the character
set to UTF 8 at the beginning of the page.
<%@ page contentType="text/html;charset=UTF-8"%>
While accepting user input from an HTML form, we set the
character encoding for the request to UTF 8.
request.setCharacterEncoding("UTF-8");
Running the sample
To run from Oracle9i
JDeveloper
Connect to Oracle9i
database and run the script create_table.sql
Open the file Globalization.jws in Oracle9i
JDeveloper
Modify the values of variables JDBC_URL,USER
and PASSWORD in ProductManager.java
Select Globalization.jpr and select Run. This will deploy
the application to embedded OC4J. You can see a list of products on the browser,
some in English and some in Japanese. You can use the 'Add new
product' link to enter details of a new product in your desired language.
To
run on OC4J,
Start OC4J.
On command prompt , set the environment variables ANT_HOME,JAVA_HOME,OC4J_HOME
and OC4J_HOST
Point the browser to http://<servername>:<port>/Globalization/display.jsp
Summary
In this how-to you have learnt how to store and retrieve
data in different languages from Oracle9i,
using character encoding schemes and NVARCHAR2 datatype.
Bookmark
