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()));
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.
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) { 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 } 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.