Advanced data types in JDBC 2.0

The new data types introduced in the SQL3 standard are provided in JDBC 2.0, such as BLOBs (Binary large object), Clob (character large object), Array object, REF (object reference, objects Reference) and UDT (user-defined data type, user-defined datatype), and so on. These new data types combine to make it possible for database designers to create richer schemas and simplify processing and persistence of complex data.

For example, to insert a picture of a user into a tbl_user table, you can use a stream to import a Blob object into the database:

String sql = "intsert into tbl_User values(?, ?)";
PreparedStatement pstmt = con.prepareStatement(sql) ;
File file = new File("C:/images/photo.jpg") ;
FileInputStream fis = new FileInputStream(file);
pstmt.setString(1, "John");
pstmt.setBinaryStream(2, fis, (int)file.length());
pstmt.executeUpdate();
pstmt.close();
fis.close();

Where the first parameter of the SQL statement is the user name, the second parameter is photo, and it is a blob-type object. So after inserting the data into the database, we can get the data using the program:

String sql = "select photo from tbl_User where username = ?";
PreparedStatement pstmt = con.prepareStatement(selectSQL) ;
pstmt.setString(1, "John");
ResultSet rs = pstmt.executeQuery() ;
rs.next();
Blob blob = rs.getBlob("photo") ;
ImageIcon icon = new ImageIcon(blob.getBytes(1, (int)blob.length())) ;
JLabel photo = new JLabel(icon);
rs.close();
pstmt.close();

Similarly, we can also manipulate the CLOB objects accordingly. The following is an example of inserting a Clob object directly into a database from an ASCII stream:

String sql = "insert into tbl_Articles values(?,?)";
PreparedStatement pstmt = con.prepareStatement(sql) ;
File file = new File("C:/data/news.txt") ;
FileInputStream fis = new FileInputStream(file);
pstmt.setString(1, "Iraq War");
pstmt.setAsciiStream(2, fis, (int)file.length());
pstmt.executeUpdate();
pstmt.close();
fis.close();

Similarly, we can take a Clob object out of the database in a similar way:

String sql = "select content from tbl_Articles where title = ?";
PreparedStatement pstmt = con.prepareStatement(sql) ;
pstmt.setString(1, "Iraq War");
ResultSet rs = pstmt.executeQuery() ;
rs.next() ;
Clob clob = rs.getClob("content") ;
InputStreamReader in = new InputStreamReader(clob.getAsciiStream()) ;
JTextArea text = new JTextArea(readString(in)) ;
rs.close();
pstmt.close();