JDBC - Streaming ASCII and Binary Data

Previous
Next

A PreparedStatement object has the ability to use input and output streams to supply parameter data. This enables you to place entire files into database columns that can hold large values, such as CLOB and BLOB data types.

There are following methods, which can be used to stream data −

  • setAsciiStream() − This method is used to supply large ASCII values.

  • setCharacterStream() − This method is used to supply large UNICODE values.

  • setBinaryStream() − This method is used to supply large binary values.

The setXXXStream() method requires an extra parameter, the file size, besides the parameter placeholder. This parameter informs the driver how much data should be sent to the database using the stream.

Using setAsciiStream() to store Ascii Value in database Example

This example would create a database table XML_Data and then XML content would be written into this table. In this program, we've defined few static strings for database connection, username, password and for queries. One query is to select data from table XML_Data, one query is to insert data into XML_data table, one query is to create the XML_Data table and one query is to drop the table. One string contains xml data to be stored in the table.

Using createXMLTable() method, we're first droping the table and then creating it. In main method, using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a Statement object using connection.createStatement() method and a PreparedStatement object using connection.prepareStatement() method. createXMLTable() method is called to create the XML_Data table. A ByteArrayInputStream object is created with static XML string. Using PreparedStatement.setAsciiStream() method, that byte stream is stored in preparedStatement. Using preparedStatement.execute() method, byte stream is stored in database.

Using statement.executeQuery() method, the select query is fired and result is stored in a result set. Result Set is iterated and using getAsciiStream(), the ascii stream is retrieved as input stream and using ByteArrayInputStream.toString(), this stream is printed to the console.

Copy and paste the following example in TestApplication.java, compile and run as follows −

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT Data FROM XML_Data WHERE id=100";
   static final String INSERT_QUERY="INSERT INTO XML_Data VALUES (?,?)";
   static final String CREATE_TABLE_QUERY = "CREATE TABLE XML_Data (id INTEGER, Data LONG)";
   static final String DROP_TABLE_QUERY = "DROP TABLE XML_Data";
   static final String XML_DATA = "<Employee><id>100</id><first>Zara</first><last>Ali</last><Salary>10000</Salary><Dob>18-08-1978</Dob></Employee>";
   
   public static void createXMLTable(Statement stmt) 
      throws SQLException{
      System.out.println("Creating XML_Data table..." );
      //Drop table first if it exists.
      try{
         stmt.executeUpdate(DROP_TABLE_QUERY);
      }catch(SQLException se){
      }
      stmt.executeUpdate(CREATE_TABLE_QUERY);
   }

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
         PreparedStatement pstmt = conn.prepareStatement(INSERT_QUERY);
      ) {		      
         createXMLTable(stmt);

         ByteArrayInputStream bis = new ByteArrayInputStream(XML_DATA.getBytes());

         pstmt.setInt(1,100);
         pstmt.setAsciiStream(2,bis,XML_DATA.getBytes().length);
         pstmt.execute();

         //Close input stream
         bis.close();

         ResultSet rs = stmt.executeQuery(QUERY);
         // Get the first row
         if (rs.next ()){
            //Retrieve data from input stream
            InputStream xmlInputStream = rs.getAsciiStream (1);
            int c;
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            while (( c = xmlInputStream.read ()) != -1)
               bos.write(c);
            //Print results
            System.out.println(bos.toString());
         }
         // Clean-up environment
         rs.close();

      } catch (SQLException | IOException e) {
         e.printStackTrace();
      } 
   }
}

Now let us compile the above example as follows −

C:\>javac TestApplication.java
C:\>

When you run TestApplication, it produces the following result −

C:\>java TestApplication
Creating XML_Data table...
<Employee><id>100</id><first>Zara</first><last>Ali</last><Salary>10000</Salary><Dob>18-08-1978</Dob></Employee>
C:\>

Using setBinaryStream() to store Binary Value in database Example

In this example, we're using JDBC_BLOB_CLOB table created in JDBC - Data Types chapter. In this program, we've defined few static strings for database connection, username, password and a query to insert binary image into the table. In main method, using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a PreparedStatement object using connection.prepareStatement() method. To get image stream, we've used FileInputStream to read the image file from the File System and using PreparedStatement.setBinaryStream(), the stream is stored in the preparedStatement object. Using executeUpdate(), the image is stored in the table.

Using connection.createStatement() method, a statement is created to get the image data from the database table. Using executeQuery(), the select query is fired and result is stored in a result set. ResultSet is iterated and using getBinaryStream(), an inputstream object is retrieved which is then used to store the data in a byte array. The byte array is then stored in a File using FileOutputStream and result is displayed.

import java.sql.*;
import java.io.*;

//This class demonstrates use of setBinaryStream
public class BinaryStreamExample {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = " INSERT INTO jdbc_blob_clob (name, image) VALUES (?, ?);";
	
   public static void main(String args[]) {     
      Connection conn = null;
      PreparedStatement pstmt = null;
      Statement stmt = null;
      try{
         conn = DriverManager.getConnection(DB_URL,USER,PASS);
         System.out.println("Connection to db  established.");
             
         File image = new File("C:\\Users\\Saikat\\OneDrive\\Documents\\saikat_upwork1.jpg");
         pstmt = conn.prepareStatement(QUERY);
    
         FileInputStream fis = new FileInputStream(image);
         pstmt.setString(1, "TutorialsPoint");
         pstmt.setBinaryStream(2, fis );
         pstmt.executeUpdate();
            
         fis.close();
         System.out.println("Successfully inserted image in db");
         stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery("select name, image from jdbc_blob_clob where name='TutorialsPoint'");
         if (rs.next()) {
            InputStream ins = rs.getBinaryStream("image");
            byte byteArray[] = new byte[ins.available()];
            ins.read(byteArray);
            String filePath = "C:\\Users\\Saikat\\output_saikat_upwork1.jpg";
            FileOutputStream outPutStream = new FileOutputStream(filePath);
            outPutStream.write(byteArray);
            outPutStream.close();
            System.out.println("Binary image successfully stored at: "+ filePath);
         }
         pstmt.close();
         conn.close();
      } 
      catch(IOException ioe){
         ioe.printStackTrace();
      }
      catch(SQLException e){
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:\>javac BinaryStreamExample.java
C:\>

When you run BinaryStreamExample, it produces the following result −

C:\>java BinaryStreamExample
Connection to db  established.
Successfully inserted image in db
Binary image successfully stored at: C:\Users\Saikat\output_saikat_upwork1.jpg

C:\>

Using setCharacterStream() to store Character Stream in database Example

In this example, we're using JDBC_BLOB_CLOB table created in JDBC - Data Types chapter. In this program, we've defined few static strings for database connection, username, password and a query to insert large text into the table. In main method, using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a PreparedStatement object using connection.prepareStatement() method. To get character stream, we've used FileReader to read the text file from the File System and using PreparedStatement.setCharacterStream(), the stream is stored in the preparedStatement object. Using executeUpdate(), the text file content is stored in the table.

Using connection.createStatement() method, a statement is created to get the image data from the database table. Using executeQuery(), the select query is fired and result is stored in a result set. ResultSet is iterated and using getCharacterStream(), an inputstream object is retrieved which is then used to store the data in a reader. The reader content is stored in a File using FileWriter and result is displayed.

import java.sql.*;
import java.io.*;
        
// This class demonstrates use of setCharacterStream
public class JDBCCharacterStream {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = " INSERT INTO jdbc_blob_clob(name, plain_text) VALUES(?,?);";
    
   public static void main(String args[]) {
        
      Connection conn = null;
      PreparedStatement pstmt = null;
      Statement stmt = null;
      try{
         conn = DriverManager.getConnection(DB_URL,USER,PASS);
         System.out.println("Connection to db  established.");
        
         pstmt = conn.prepareStatement(QUERY);
         pstmt.setString(1, "SetCharacterStream Example");
         FileReader fileReader = new FileReader("C:\\Users\\Saikat\\OneDrive\\Documents\\mysql_create_table.txt");
         pstmt.setCharacterStream(2, fileReader);
         pstmt.execute();
             
         System.out.println("Successfully inserted file data using setCharacterStream.");
             
         stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT name, plain_text from jdbc_blob_clob where name='SetCharacterStream Example'");
         String filePath = "C:\\Users\\Saikat\\clob_output_mysql_create_table.txt";
         Reader r;
         while(rs.next()) {
            String name = rs.getString("Name");
            r = rs.getCharacterStream("plain_text");
                
            FileWriter writer = new FileWriter(filePath);
            int i=0;
            while ((i=r.read())!=-1) {
               writer.write(i);
            }
               writer.close();
         }
         
         System.out.println("Successfully retrieved file using getCharacterStream at " + filePath ); 
         pstmt.close();
         stmt.close();
		 conn.close();
             
      }catch(IOException ioe){
         ioe.printStackTrace();
      }catch(SQLException sqle){
         sqle.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:\>javac JDBCCharacterStream.java
C:\>

When you run JDBCCharacterStream, it produces the following result −

C:\>java JDBCCharacterStream
Connection to db  established.
Successfully inserted file data using setCharacterStream.
Successfully retrieved file using getCharacterStream at C:\Users\Saikat\clob_output_mysql_create_table.txt

C:\>
Print Page
Previous
Next
Advertisements