CODEWITHSUNDEEP
javamysqlfile
user2752385
user2752385

Reputation: 83

Reading to text file from MySQL

I'm trying to store a text file in a MySQL database, and when needed, save it to a file.

To save the file, I do:

public void saveFile_InDB(File file)
{  
     try {

        String sql = "INSERT INTO sent_emails (fileName, time, clientName) values (?, ?, ?)";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1, new Date().toString());
        statement.setString(2, new Date().toString());

        InputStream inputStream = new FileInputStream(file); 
        statement.setBinaryStream(3, inputStream);

        int row = statement.executeUpdate();
        if (row > 0) {
            System.out.println("File saved sucessfully.");
        }
        conn.close();

    } catch (SQLException ex) {
        ex.printStackTrace();
    } catch (IOException ex) {
        ex.printStackTrace();
    }
}

And to retreive and save the file:

public void retrieveFile_fromDB()
{
     try {

         Statement stmt = (Statement) conn.createStatement();
         ResultSet res = stmt.executeQuery("SELECT * FROM sent_emails WHERE clientName='sally'");
         FileOutputStream fos = new FileOutputStream("file.txt");  

         if (res.next()) {

             Blob File = (Blob) res.getBlob("fileName");
             InputStream is = File.getBinaryStream();
             int b = 0;

             while ((b = is.read()) != -1) {
                 fos.write(b);   
             }

             fos.flush();

         }
     } catch (IOException e) {
         e.getMessage (); e.printStackTrace(); 
         System.out.println(e); 
     } catch (SQLException e) {
         e.getMessage (); e.printStackTrace(); 
         System.out.println(e); 
     }
}

Storing the file works, but when I try to retrieve and save it, nothing is stored in the output file?

Upvotes: 1

Views: 1800

Answers (2)

yousef
yousef

Reputation: 1363

if you want read file from db Mysql change this part in your code 

  Blob File = (Blob) res.getBlob("fileName");
         InputStream is = File.getBinaryStream();
         int b = 0;

         while ((b = is.read()) != -1) {
             fos.write(b);   
         }

         fos.flush();

use this code read array of bytes 

    byte [] bs=res.getBytes("fileName");
    fos.write(bs);

it will work if you return multiple files from db you must declare 

 FileOutputStream fos = new FileOutputStream("file.txt");

inside while loop and change name of file to avoid overriding

Upvotes: 1

Jon Gjengset
Jon Gjengset

Reputation: 4236

You do not seem to put into the database the things that the column names describe?

fileName and time are for example both set to a timestamp, and clientName is set to the contents of the file. When you later try to select based on clientName, you are actually selecting based on the contents of the file.

Furthermore, when reading the data, you are reading the blob data from the column fileName, but this is wrong because:

  1. fileName contains new Date().toString(), not the contents of the file
  2. fileName should surely contain the file's name, not its contents?

Upvotes: 0

Related Questions