Appendixes

 
[Page 1150 ( continued )]

33.6. Storing and Retrieving Images in JDBC

A database can store not only numbers and strings, but also images. SQL3 introduced a new data type called BLOB ( B inary L arge OB ject) for storing binary data, which can be used to store images. Another new SQL3 type is CLOB ( C haracter L arge OB ject), for storing a large text in the character format. JDBC 2 introduced the interfaces java.sql.Blob and java.sql.Clob to support mapping for these new SQL types. JBDC 2 also added new methods in the interfaces ResultSet and PreparedStatement , such as getBlob , setBinaryStream , getClob , setBlob and setClob , to access SQL BLOB, and CLOB values.

To store an image into a cell in a table, the corresponding column for the cell must be of the BLOB type. For example, the following SQL statement creates a table whose type for the flag column is BLOB:

   create table   Country( name   varchar   (   30   ),  flag   blob    , description   varchar   (   255   )); 

In the preceding statement, the description column is limited to 255 characters, which is the upper limit for MySQL. For Oracle, the upper limit is 32,672 bytes. For a large character field, you can use the CLOB type for Oracle, which can store up to two GB characters. MySQL does not support CLOB. However, you can use BLOB to store a long string and convert binary data into characters .

Note

Access does not support the BLOB and CLOB types.


To insert a record with images to a table, define a prepared statement like this one:

 PreparedStatement pstmt = connection.prepareStatement(   "insert into Country values(?, ?, ?)");   

Images are usually stored in files. You may first get an instance of InputStream for an image file and then use the setBinaryStream method to associate the input stream with a cell in the table, as follows :

  // Store image to the table cell  File file =   new   File(imageFilename); InputStream inputImage =   new   FileInputStream(file); pstmt.setBinaryStream(   2   , inputImage, (   int   )(file.length())); 

To retrieve an image from a table, use the getBlob method, as shown below:

  // Retrieve image from the table cell  Blob blob = rs.getBlob(   1   ); ImageIcon imageIcon =   new   ImageIcon( blob.getBytes(   1   , (   int   )blob.length())); 


[Page 1151]

To demonstrate how to store and retrieve images in JDBC, let us create a table, populate it with data, including images, and retrieve and display images. The table is named Country and can be created using the following SQL statement:

   create table   Country(name   varchar   (  30  ), flag   blob   , description   varchar   (255)); 

Each record in the table consists of three fields: name, flag, and description. Flag is an image field. The program first creates the table and stores data to it. Then the program retrieves the country names from the table and adds them to a combo box. When the user selects a name from the combo box, the country's flag and description are displayed, as shown in Figure 33.6. Listing 33.10 gives the program.

Figure 33.6. The program enables you to retrieve data, including images, from a table and displays them.


Listing 33.10. StoreAndRetrieveImage.java
(This item is displayed on pages 1151 - 1153 in the print version)
 1   import   java.sql.*; 2   import   java.io.*; 3   import   javax.swing.*; 4   import   java.awt.*; 5   import   java.awt.event.*; 6 7   public class   StoreAndRetrieveImage   extends   JApplet { 8  // Connection to the database  9   private   Connection connection; 10 11  // Statement for static SQL statements  12   private   Statement stmt; 13 14  // Prepared statement  15   private   PreparedStatement pstmt =   null   ; 16   private   DescriptionPanel descriptionPanel1 =   new   DescriptionPanel(); 17 18   private   JComboBox jcboCountry =   new   JComboBox(); 19 20  /** Creates new form StoreAndRetrieveImage */  21   public   StoreAndRetrieveImage() { 22   try   { 23  connectDB();   // Connect to DB  24  storeDataToTable();   //Store data to the table (including image)  25  fillDataInComboBox();   // Fill in combo box  26  retrieveFlagInfo((String)(jcboCountry.getSelectedItem()));  27 } 28   catch   (Exception ex) { 29 ex.printStackTrace(); 30 } 31 32 jcboCountry.addItemListener(   new   ItemListener() { 33   public void   itemStateChanged(ItemEvent evt) { 

[Page 1152]
 34 retrieveFlagInfo((String)(evt.getItem())); 35 } 36 }); 37 38 add(jcboCountry, BorderLayout.NORTH); 39 add(descriptionPanel1, BorderLayout.CENTER); 40 } 41 42   private void   connectDB()   throws   Exception { 43  // Load the driver  44  Class.forName(   "com.mysql.jdbc.Driver"   );  45 System.out.println(   "Driver loaded"   ); 46 47  // Establish connection  48  connection = DriverManager.getConnection  49  (   "jdbc:mysql://localhost/test"    );   50 System.out.println(   "Database connected"   ); 51 52  // Create a statement for static SQL  53  stmt = connection.createStatement();  54 55  // Create a prepared statement to retrieve flag and description  56  pstmt = connection.prepareStatement(   "select flag, description "   +  57    "from Country where name = ?"   );  58 } 59 60   private void   storeDataToTable() { 61 String[] countries = {   "Canada"   ,   "UK"   ,   "USA"   ,   " Germany "   , 62   "India"   ,   "China"   }; 63 64 String[] imageFilenames = {   "image/ca.gif"   ,   "image/uk.gif"   , 65   "image/us.gif"   ,   "image/germany.gif"   ,   "image/india.gif"   , 66   "image/china.gif"   }; 67 68 String[] descriptions = {   "A text to describe Canadian "   + 69   "flag is omitted"   ,   "British flag ..."   ,   "American flag ..."   , 70   "German flag ..."   ,   "Indian flag ..."   ,   "Chinese flag ..."   }; 71 72   try   { 73  // Create a prepared statement to insert records  74  PreparedStatement pstmt = connection.prepareStatement(  75    "insert into Country values(?, ?, ?)"    76 77  // Store all predefined records  78   for   (   int   i =     ; i < countries.length; i++) { 79  pstmt.setString(   1   , countries[i]);  80 81  // Store image to the table cell  82 java.net.URL url = 83   this   .getClass().getResource(imageFilenames[i]); 84 InputStream inputImage = url.openStream(); 85  pstmt.setBinaryStream(   2   , inputImage,  86  (   int   )(inputImage.available()));  87 88  pstmt.setString(   3   , descriptions[i]);  89  pstmt.executeUpdate();  90 } 91 92 System.out.println(   "Table Country populated "   ); 93 } 

[Page 1153]
 94   catch   (Exception ex) { 95 ex.printStackTrace(); 96 } 97 } 98 99   private void   fillDataInComboBox()   throws   Exception { 100 ResultSet rs = stmt.executeQuery(   "select name from Country"   ); 101   while   (rs. next ()) { 102  jcboCountry.addItem(rs.getString(   1   ));  103 } 104 } 105 106   private void   retrieveFlagInfo(String name) { 107   try   { 108  pstmt.setString(   1   , name);  109  ResultSet rs = pstmt.executeQuery();  110   if   (  rs.next()  ) { 111 Blob blob = rs.getBlob(   1   ); 112 ImageIcon imageIcon =   new   ImageIcon( 113 blob.getBytes(   1   , (   int   )blob.length())); 114 descriptionPanel1.setImageIcon(imageIcon); 115 descriptionPanel1.setName(name); 116 String description = ; 117 descriptionPanel1.setDescription(description); 118 } 119 } 120   catch   (Exception ex) { 121 System.err.println(ex); 122 } 123 } 124 } 

DescriptionPanel (line 16) is a component for displaying a country (name, flag, and description). This component was presented in Listing15.6, TextAreaDemo.java.

The storeDataToTable method (lines 60 “97) populates the table with data. The fillDataInComboBox method (lines 99 “104) retrieves the country names and adds them to the combo box. The retrieveFlagInfo(name) method (lines 106 “123) retrieves the flag and description for the specified country name.

 


Introduction to Java Programming-Comprehensive Version
Introduction to Java Programming-Comprehensive Version (6th Edition)
ISBN: B000ONFLUM
EAN: N/A
Year: 2004
Pages: 503

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net