Storing songs on MySQL Database (BLOb support)

Question

I am trying to store songs and lyrics on MySQl db. I googled for examples how to do this,but no help.However,i am able to store images:

  con = DriverManager.getConnection(connectionURL, "root", "");
PreparedStatement ps = con.prepareStatement("INSERT INTO image VALUES(?,?)");
 File file = new File("E://guitar.gif");
FileInputStream fs = new FileInputStream(file);
ps.setInt(1,id);
ps.setBinaryStream(2,fs,fs.available());
int i = ps.executeUpdate();
ps.close();
con.close();
//rest code

Can anyone help me how to store songs?with example?and how to retrieve it back?

I think MySQL was designed to store photos and recipes in the first place, but not songs. Just kidding. Are you getting an exception? — mbelow
– mbelow, Commented Nov 28, 2012 at 15:45
What is the state after executing the query? Is a new record created at all? Is the blob field empty? Please provide more information. — mbelow
– mbelow, Commented Nov 28, 2012 at 15:49
@mbelow after executing the query,the BLOb field contains the binary data something like this 000011010000000.... (suppose to be) It does creat a new record. — joey rohan
– joey rohan, Commented Nov 28, 2012 at 15:53

tibtof · Accepted Answer · 2012-11-28 17:14:56Z

2

There's nothing different in storing a song than storing an image. You could add another column for the file name's, and the you can do something similar to store the files:

//..
PreparedStatement ps = con.prepareStatement("INSERT INTO data_table VALUES(?, ?, ?)");
//...
ps.setInt(1,id);
ps.setString(2, file.getName());
ps.setBinaryStream(3, fs,fs.available());
int i = ps.executeUpdate();
//...

An then to retrieve it:

PreparedStatement ps = con.prepareStatement("SELECT file_name, content from data_table where *some condition*");
ResultSet rs = ps.executeQuery();
while(rs.hasNext) {
    rs.next();
    String fileName = rs.getString("file_name");
    Blob blob = rs.getBlob("content");
    byte[] content = blob.getBytes(1, (int) blob.length());
    //now content contains the data, you ca store it in a file if you need
    OutputStream os = new FileOutputStream(new File("d:/test/" + fileName));
    os.write(content);
    os.close();
}

Don't forget exception handling!

EDIT: Another version with byte arrays: Write:

//..
PreparedStatement ps = con.prepareStatement("INSERT INTO data_table VALUES(?, ?, ?)");
//...
byte[] content = new byte[fs.available()];
ps.setInt(1,id);
ps.setString(2, file.getName());
ps.setBytes(3, content);
int i = ps.executeUpdate();
//...

Read:

PreparedStatement ps = con.prepareStatement("SELECT file_name, content from data_table where *some condition*");
ResultSet rs = ps.executeQuery();
while(rs.hasNext) {
    rs.next();
    String fileName = rs.getString("file_name");
    byte[] content = rs.getBytes("content");
    //now content contains the data, you ca store it in a file if you need
    OutputStream os = new FileOutputStream(new File("d:/test/" + fileName));
    os.write(content);
    os.close();
}

edited Nov 28, 2012 at 17:14

answered Nov 28, 2012 at 15:51

tibtof

7,9871 gold badge35 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

joey rohan Over a year ago

+1 for answer.can you please explain: ps.setString(2, file.getName()); ps.setBinaryStream(3, fs,fs.available());

tibtof Over a year ago

setString is for setting file's name (optional, I think it would be useful when retrieving the file back) and setBinaryStream for setting the file's content.

joey rohan Over a year ago

one more thing.does that code saves file in d drive? if yes,i can't find it :(

joey rohan Over a year ago

it is not inserting the sound file.:(

joey rohan Over a year ago

It was not inserting sound file.Then i changed fs.available(); to fs.read(),it worked:),but still,having problem with reading

|

Collectives™ on Stack Overflow

Storing songs on MySQL Database (BLOb support)

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related