Set - 5

Question 1 :

How do I display and parse a date?

Answer :

The Java I18N way is to use a DateFormat. While SimpleDateFormat, which is generally returned, creates a large number of objects, it is locale aware and will handle most of your needs. The following sample code initially creates a java.sql.Date object and formats it for the default locale. An initial actionPerformed call additionally formats/displays it for a German locale and also displays the resulting java.sql.Date in standard escape format. Other dates can be entered and parsed after the initial display.

// JDFDP.java - Display and Parse java.sql.Date

import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.text.*;
import java.util.*;

public class JDFDP extends JFrame
implements ActionListener,
WindowListener
{
// create a java.sql.Date
java.sql.Date jsqlDate = new java.sql.Date(
System.currentTimeMillis() );

DateFormat dfLocal = DateFormat.getDateInstance(
DateFormat.SHORT );
DateFormat dfGermany = DateFormat.getDateInstance(
DateFormat.SHORT, Locale.GERMANY );

JButton jb = new JButton( "Go" );
JLabel jlI = new JLabel("Input a Date:"),
jlD = new JLabel("Display German:"),
jlP = new JLabel("Parsed:");

JPanel jp = new JPanel();

JTextField jtI = new JTextField( 10 ),
jtD = new JTextField( 10 ),
jtP = new JTextField( 10 );


public JDFDP()
{
super( "JDFDP" );
addWindowListener( this );

jb.addActionListener( this );

jp.add(jlI);
jp.add(jtI);
jp.add(jb);
jp.add(jlD);
jp.add(jtD);
jp.add(jlP);
jp.add(jtP);

getContentPane().add( jp, BorderLayout.CENTER );
pack();

// set text by sending dummy event
jtI.setText( dfLocal.format( jsqlDate ) );
actionPerformed(
new ActionEvent( this, 12, "12" ) );

show();

} // end constructor


// ActionListener Implementation
public void actionPerformed(ActionEvent e)
{
jtD.setText( "" );
jtP.setText( "" );
try
{
java.util.Date d = dfLocal.parse(
jtI.getText() );
jtI.setText( dfLocal.format( d ) );
jtD.setText( dfGermany.format( d ) );
d = dfGermany.parse( jtD.getText() );
// get new java.sql.Date
jsqlDate = new java.sql.Date( d.getTime() );

jtP.setText( jsqlDate.toString() );
}
catch( ParseException pe ) { jtI.setText( "" ); }

} // End actionPerformed


// Window Listener Implementation
public void windowOpened(WindowEvent e) {}
public void windowClosing(WindowEvent e)
{
dispose();
System.exit(0);
}
public void windowClosed(WindowEvent e) {}
public void windowIconified(WindowEvent e) {}
public void windowDeiconified(WindowEvent e) {}
public void windowActivated(WindowEvent e) {}
public void windowDeactivated(WindowEvent e) {}
// End Window Listener Implementation


public static void main(String[] args)
{
new JDFDP();
}

} // end class JDFDP


Question 2 :

How can I retrieve string data from a database in Unicode format?

Answer :

The data is already in Unicode when it arrives
in your program. Conversion from and to the
encoding/charset/CCSID in the database from/to
Unicode in the program is part of the JDBC driver's job.

If, for some reason, you want to see the data in
'\uHHHH' format ( where 'H' is the hex value ),
the following code, while not very efficient,
should give you some ideas:


public class UniFormat
{

public static void main( String[] args )
{
char[] ac = args[0].toCharArray();
int iValue;
String s = null;
StringBuffer sb = new StringBuffer();

for( int ndx = 0; ndx < ac.length; ndx++ )
{
iValue = ac[ndx];

if( iValue < 0x10 )
{
s = "\\u000";
}
else
if( iValue < 0x100 )
{
s = "\\u00";
}
else
if( iValue < 0x1000 )
{
s = "\\u0";
}

sb.append( s + Integer.toHexString( iValue ) );
} // end for

System.out.println("The Unicode format of " +
args[0] + " is " + sb );

} // end main

} // end class UniFormat


Question 3 :

Can ResultSets be passed between methods of a class? Are there any special usage

Answer :

Yes. There is no reason that a ResultSet can't be used as a method parameter just like any other object reference. You must ensure that access to the ResultSet is synchronized. This should not be a problem is the ResultSet is a method variable passed as a method parameter - the ResultSet will have method scope and multi-thread access would not be an issue.

As an example, say you have several methods that obtain a ResultSet from the same table(s) and same columns, but use different queries. If you want these ResultSets to be processed the same way, you would have another method for that. This could look something like:

public List getStudentsByLastName(String lastName) {
ResultSet rs = ... (JDBC code to retrieve students by last name);
return processResultSet(rs);
}

public List getStudentsByFirstName(String firstName) {
ResultSet rs = ... (JDBC code to retrieve students by first name);
return processResultSet(rs);
}

private List processResultSet(ResultSet rs) {
List l = ... (code that iterates through ResultSet to build a List of Student objects);
return l;
}

Since the ResultSet always has method scope - sychronization is never an issue.

1. There is only one ResultSet. Dont assume that the ResultSet is at the start (or in any good state...) just because you received it as a parameter. Previous operations involving the ResultSet will have had the side-effect of changing its state.
2. You will need to be careful about the order in which you close the ResultSet and CallableStatement/PreparedStatement/etc

From my own experience using the Oracle JDBC drivers and CallableStatements the following statements are true:

* If you close the CallableStatement the ResultSet retrieved from that CallableStatement immediately goes out-of-scope.
* If you close the ResultSet without reading it fully, you must close the CallableStatement or risk leaking a cursor on the database server.
* If you close the CallableStatement without reading it's associated ResultSet fully, you risk leaking a cursor on the database server.

No doubt, these observations are valid only for Oracle drivers. Perhaps only for some versions of Oracle drivers.

The recommended sequence seems to be:
* Open the statement
* Retrieve the ResultSet from the statement
* Read what you need from the ResultSet
* Close the ResultSet
* Close the Statement


Question 4 :

How can I convert a java array to a java.sql.Array?

Answer :

A Java array is a first class object and all of the references basically use PreparedStatement.setObject() or ResultSet.updateObject() methods for putting the array to an ARRAY in the database. Here's a basic example:
String[] as = { "One", "Two", "Three" };
...
PreparedStatement ps = con.prepareStatement(
"UPDATE MYTABLE SET ArrayNums = ? WHERE MyKey = ?" );
...
ps.setObject( 1, as );


Question 5 :

Could we get sample code for retrieving more than one parameter from a stored procedure?

Answer :

Assume we have a stored procedure with this signature:
MultiSP (IN I1 INTEGER, OUT O1 INTEGER, INOUT IO1 INTEGER)
The code snippet to retrieve the OUT and INOUT parameters follows:
CallableStatement cs = connection.prepareCall( "(CALL MultiSP(?, ?, ?))" );
cs.setInt(1, 1); // set the IN parm I1 to 1
cs.setInt(3, 3); // set the INOUT parm IO1 to 3

cs.registerOutParameter(2, Types.INTEGER); // register the OUT parm O1
cs.registerOutParameter(3, Types.INTEGER); // register the INOUT parm IO1

cs.execute();
int iParm2 = cs.getInt(2);
int iParm3 = cs.getInt(3);
cs.close();

The code really is just additive; be sure that for each IN parameter that setXXX() is called and that for each INOUT and OUT parameter that registerOutParameter() is called.


Question 6 :

What is the difference between client and server database cursors?

Answer :

What you see on the client side is the current row of the cursor which called a Result (ODBC) or ResultSet (JDBC). The cursor is a server-side entity only and remains on the server side.


Question 7 :

How can I pool my database connections so I don't have to keep reconnecting to the database?

Answer :

There are plenty of connection pool implementations described in books or availalble on the net. Most of them implement the same model. The process is always the same :
* you gets a reference to the pool
* you gets a free connection from the pool
* you performs your different tasks
* you frees the connection to the pool

Since your application retrieves a pooled connection, you don't consume your time to connect / disconnect from your data source. You can find some implementation of pooled connection over the net, for example:
* Db Connection Broker (http://www.javaexchange.com/), a package quite stable ( I used it in the past to pool an ORACLE database on VMS system)
You can look at the JDBC 2.0 standard extension API specification from SUN which defines a number of additional concepts.


Question 8 :

How can I connect to an Excel spreadsheet file using jdbc?

Answer :

Let's say you have created the following Excel spreadsheet in a worksheet called Sheet1 (the default sheet name). And you've saved the file in c:\users.xls.
USERID FIRST_NAME LAST_NAME
pkua Peter Kua
jlsmith John Smith
gh2312 Everett Johnson
chimera Faiz Abdullah
roy6943 Roy Sudirman

Since Excel comes with an ODBC driver, we'll use the JDBC-ODBC bridge driver that comes packaged with Sun's JDK to connect to our spreadsheet.
In Excel, the name of the worksheet is the equivalent of the database table name, while the header names found on the first row of the worksheet is the equivalent of the table field names. Therefore, when accessing Excel via jdbc, it is very important to place your data with the headers starting at row 1.
1. Create a new ODBC Data Source using the Microsoft Excel Driver. Name the DSN "excel", and have it point to c:\users.xls.
2. Type in the following code:

package classes;
import java.sql.*;

public class TestServer
{
static
{
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (Exception e) {
System.err.println(e);
}
}

public static void main(String args[]) {
Connection conn=null;
Statement stmt=null;
String sql="";
ResultSet rs=null;

try {
conn=DriverManager.getConnection("jdbc:odbc:excel","","");
stmt=conn.createStatement();
sql="select * from [Sheet1$]";
rs=stmt.executeQuery(sql);

while(rs.next()){
System.out.println(rs.getString("USERID")+
" "+ rs.getString("FIRST_NAME")+" "+
rs.getString("LAST_NAME"));
}
}
catch (Exception e){
System.err.println(e);
}
finally {
try{
rs.close();
stmt.close();
conn.close();
rs=null;
stmt=null;
conn=null;
}
catch(Exception e){}
}
}
}

Notice that we have connected to the Excel ODBC Data Source the same way we would connect to any normal database server.
The only significant difference is in the SELECT statement. Although your data is residing in the worksheet called "Sheet1", you'll have to refer to the sheet as Sheet1$ in your SQL statements. And because the dollar sign symbol is a reserved character in SQL, you'll have to encapsulate the word Sheet1$ in brackets, as shown in the code.