Skip to main content

Retrieve File from table by using JDBC with example

To retrieve file from table by using the JDBC here is the example. Let's, come to the first to database as MySQL and table name as myFiles and column name as file_type. These table data we have already inserted files in previous program.

Now, we should retrieve from the ResultSet using getClob() method.

1
Clob file = rs.getClob();

Here, getClob() method retrieving data from column and storing it into Clob class object as file.

Use getCharacterStream() method which will available under the Clob object, it also gets the file data from Clob object into a reader object.

1
Reader r = file.getCharacterStream();

Here, we are reding data from Reader object r and writing it into FileWriter which stores it into a file as D:/OutPutFile.txt.

Registering the Driver and creating Connection and Statements are same in the all JDBC programs. In query we are fetching only file column from table. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
package com.javatbrains.jdbc;

import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class RetrieveFiles {

   static Connection con = null;
   static Statement st = null;
   static ResultSet rs = null;

   public static void main(String[] args) throws IOException {
     try {
     // Register Driver
     DriverManager.registerDriver(new com.mysql.jdbc.Driver());

     // Establish Connection
     con = DriverManager
.getConnection("jdbc:mysql://localhost:3306/test?user=root&password=root");

     // Create a Statement
     st = con.createStatement();

     // Here is the query to fetch file column from table
     String query = "Select file_type from myfiles";

     // Execute the query by using the statement
     rs = st.executeQuery(query);
     FileWriter fw = null;
     while(rs.next()){
        // go to first row
        Clob file = rs.getClob(1);

        /*
        * Read file data from Clob instance name as file and sore into
        * Reader object
        */
        Reader r = file.getCharacterStream();

        /* Read data from reader and write into newfile.txt */
        int ch;
        fw = new FileWriter("D:/OutPutFile.txt");
          while ((ch = r.read()) != -1) {
            fw.write((char) ch);
          }
        } 
        fw.close();
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         try {
           con.close();
           st.close();
           rs.close();
         } catch (SQLException e) {
           e.printStackTrace();
         }
      }

   }

}

Do you know?
Labels:
Location: Bengaluru, Karnataka 560017, India

Comments

Post a Comment

Popular posts from this blog

JVM, JRE and JDK in Java

JVM, JRE and JDK are the most basic common concepts to know in java. These are the basic features to understand how Java architecture works? JVM stands for Java Virtual Machine, which doesn't have any physical directories created in java installation. JRE stands for Java Runtime Environment, which creates the directory under Java installation path and also present in JDK. JDK stands for Java Development Kit, which creates the directory in Java installation path and also it has it's own JRE. Since we have already learn that Java is platform independent means if we have implemented any of the java class in one environment, it will be executed in any other environment and provides the same output. But, JVM, JRE and JDK all are platform dependent . So that, for windows, linux, unix, mac, solaris..etc has it's own JVM, JRE and JDK. One will be not compatible with other environments. While installing the Java, we might come to know a bit about JRE and JDK. But, JVM is the other...
8 comments
Read more

Prime, Fibonacci and Factorial number with example in java

Prime number, Fibonacci series and Factorial number programs are most commonly asked questions in interview. Read this article to know what is and how to write programs for prime number, fibonacci series and factorial number. Prime Number: prime number is natural number greater than 1 that has no positive divisor other than 1 and itself. A natural number greater than 1 is not a prime number, is called Composite number . For example, 7 is a prime number. Because it can divide with 1 and 7 only. Where as 8 is composite number. Since it has the divisor 2 and 4 in addition to the 1 and 8. The below example represents the finding the passing number is prime number or not. If the passing number is prime number it will print true otherwise it will print false. package com . javatbrains . practice ; public class PrimeNumber { public boolean isPrimeNumber ( int number ) { if ( number <= 1 ) return false ; // There's only one ...
1 comment
Read more

How to insert images into database using JDBC?

How to insert images into database table by using JDBC? To know about these topic follow the below lines,           Before going to write a JDBC program for inserting images into database table, we need to create a specific table for inserting images column datatype. For creating the database table, execute the below command in MySQL command prompt. 1 2 3 4 CREATE TABLE ` images ` ( ` image_id ` int ( 11 ) NOT NULL AUTO_INCREMENT, ` image_flag ` int ( 11 ) NOT NULL , ` image ` longblob NOT NULL , PRIMARY KEY ( ` image_id ` ));           After executing the above query in MySQL command prompt, execute the below query to know weather the above table query is created or not. 1 Show tables;            When you have executed the above query that will show you the list of table which all exists in the current database. Current tab...
3 comments
Read more