Saving and retrieving files is a common problem when it comes to writing programs that has a database back end. In this post I will show you how to save and retrieve image files to and from a MySQL database, using Java. This method can be used to store any file in a database.
The Database:
For this example I will create a database called ‘DB’ and a table called ‘images’ where I will store the files. I use wampserver as my database manager.
The format for the ‘images’ table is as follows:
Index : INT
Photo : MEDIUMBLOB
The data type BLOB can be used to store byte values. There are four such data types available, namely TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB. Check here to find out the maximum sizes supported by each data type.
Java Program:
The following java program reads an image file (test.jpg) and saves it in the database in a record with index as 5. Then it reads back all the records that are in the table, and stores those images back on the hard disk.
Make sure you have installed MySQL Connector/J. If you are using NetBeans, you can add MySQL JDBC Driver to you libraries folder.
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.awt.image.BufferedImage;
import java.io.*;
import java.sql.*;
import javax.imageio.ImageIO;
public class Main {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mysql";
Connection con = DriverManager.getConnection(url, "root", "");
System.out.println("Connected to db");
//saving the image
PreparedStatement psmnt = (PreparedStatement) con.prepareStatement("INSERT INTO DB.images VALUES(?,?)");
psmnt.setInt(1, 5);
File f = new File("test.jpg");
psmnt.setBlob(2, new FileInputStream(f), f.length());
psmnt.executeUpdate();
System.out.println("Image saved in DB");
//retrieving it
Statement stmt = (Statement) con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM DB.images");
int count = 0;
while (rs.next()) {
InputStream is = rs.getBinaryStream("photo");
BufferedImage image = ImageIO.read(is); //the image is read in
//store it back again as a file
ImageIO.write(image, "jpg", new FileOutputStream("recived" + count + ".jpg"));
count++;
}
System.out.println(count+" images saved on disk");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
If you are trying to upload a large file, sometimes you might get a ‘packet size too large’ exception. You can increase the ‘max_allowed_packet’ variable of your server. Check here to find out how to do this.
If you want to know how to save, send and receive Java Image objects over a network have a look at this. It explains how to convert an Image object to a byte array. This byte array can be saved in an SQL database in a BLOB type data field.
Happy Coding!
I think now the best practice will be to look at “Cyber Asylum” before trying to work on any assignment( Specially the ones which are related to “programming challenge” 😉 )
Lol!! That’s a subject module I really enjoy.. So yeah, you’ll hopefully see more posts in that area
Mchn, in the next tutorial give us a guide on how to create a password and privilege level based user login system that uses databases. =)
Thanx a lot for the help buddy…
Thanks for the idea… I guess there can be a lot of ways to implement that, and I also don’t have a good idea as to which method would be the best, but we’ll see
Hi,
I want to create an application used mysql but
with saving the database in file .sql or something like that,please anybody have an idea answer me.
Thanks a lot of;
I suppose what you want to do is to create an application that would save your database as a .sql file. There is an SQL command called mysqldump that can be executed on a shell. (http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html), you can use java to run it.
Check these articles:
1. http://captainlog.tk/backup-mysql-database-from-java-part-1_29
2. http://captainlog.tk/backup-mysql-database-from-java-part-2
3. http://captainlog.tk/mysql-backup-database-java
I didn’t try this out my self. Hope it’ll work
-> psmnt.setBlob(2, new FileInputStream(f), f.length());
can u explain..pls
setblob(arg1,arg2) accepts only 2 parameters and the second parameter should be of blob type….its not wrkng
No, there is a method with three arguments:
public void setBlob(int parameterIndex, InputStream inputStream, long length) throws SQLException
Refer: http://download.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setBlob(int, java.io.InputStream, long)
A good post..but i need something like the program should upload a pdf/docx file and the filename to the database table and a program that allows me to download the same file along with its filename.. the file must be uploaded from the input type=”file” form parameter…Please help me out .
Hi Akash, what you can do is to create another field in you table (say ‘filename’) and store the filename in the database. You can get the name of the file by using file.getName().
And when downloading, take the ‘filename’ from the database and pass it to the FileOutputStream.
okay thanks Janith,,,actually when ever i do the stuffs of uploading a file to my database in my localhost..then it gets uploaded successfuly,,but when i deploy it to internet then it stops working(uploading a file) as in the program i specified a pathname from which it takes the file but how to fix it if a take the program to the server…So please try helping me out..
Hi, can you tell me your server details? You can email it to me.
Hi this is my program…
= 0)) {
DataInputStream in = new DataInputStream(request.getInputStream());
int formDataLength = request.getContentLength();
byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;
while (totalBytesRead You have successfully upload the file by the name of:
0) {
response.sendRedirect(“mailHR.jsp”);
}
else{
response.sendRedirect(“resume1.jsp?error1=Something went wrong..please Check.”);
}
}
catch(Exception e){
response.sendRedirect(“resume1.jsp?error1=Something went wrong..please Check.”);
}
}
%>
In the above program you can see that there is line as saveFile=”D://”+saveFile;
,,which takes file from D folder and then stores into my db … the code works fine if use to upload file on my localhost….but the same program when i use it after deploying to the internet it dont work …it gives a error page as file not found …So,what should be the exact path of that particular line so that it work fine when i deploy it to the internet…
Hi!,i’m getting harder to find topic for my thesis proposal can I ask your help?
I’m a bachelor of science in computer science students.
What are some various algorithm for saving files in mysql database?
hi this is my program..
= 0)) {
DataInputStream in = new DataInputStream(request.getInputStream());
int formDataLength = request.getContentLength();
byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;
while (totalBytesRead You have successfully upload the file by the name of:
<%
ResultSet rs = null;
PreparedStatement psmnt = null;
FileInputStream fis;
//int a=2;
try {
File f = new File(saveFile);
psmnt = con.prepareStatement("update resumedata set resume=? where email='"+email+"'");
fis = new FileInputStream(f);
psmnt.setBlob(1, (InputStream)fis);
In the above program you can see that there is line as saveFile="D://"+saveFile;
,,which takes file from D folder and then stores into my db … the code works fine if use to upload file on my localhost….but the same program when i use it after deploying to the internet it dont work …it gives a error page as file not found …So,what should be the exact path of that particular line so that it work fine when i deploy it to the internet…
if have some problem insert text file (*.txt) to MySQL
when i run the program, there is no error, but the data isn’t save to database
do you have advice for my problem, thank before
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class testsave_5 {
public static Connection getConnection() throws Exception {
String driver = “com.mysql.jdbc.Driver”;
String url = “jdbc:mysql://localhost:3306/db_auto”;
String username = “root”;
String password = “”;
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,username, password);
return conn;
}
public static void main(String[] args) throws Exception {
String textValue = “”;
int textLength = 0;
String category = “funny”;
String lineRead = null;
PreparedStatement pstmt = null;
String fileNameToInsertTextFrom = “D:/telex.txt”;
FileReader fileReader = new FileReader(new File(fileNameToInsertTextFrom));
Connection conn = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
BufferedReader reader = new BufferedReader(fileReader);
pstmt = conn.prepareStatement(
“insert into testlongtele(address,time,day,hour,minute,second)”+
“values (?, ?, ?, ?, ?, ?)”);
while ((lineRead = reader.readLine()) != null) {
try {
textValue = lineRead.trim();
textLength = textValue.length();
//if (textLength > 0) {
if (textLength > 0) {
pstmt.setString(1, textValue);
pstmt.setInt(2, textLength);
pstmt.setString(3, category);
//pstmt.executeUpdate();
conn.commit();
}
} catch (NullPointerException npe) {
// do nothing proceed to another line
}
lineRead = null;
}
} catch (Exception e) {
System.err.println(“Error: ” + e.getMessage());
e.printStackTrace();
} finally {
pstmt.close();
conn.close();
}
}
}
A new prepared statement should be created for each query. So include the “pstmt = conn.prepareStatement(..” part in to the while loop.
i want to create ,save and read a “txt” file in java….using database can u help me..?
Hi Raj,
You can give any file to the above code (instead of “test.jpg” give your “text.txt” file. Did I answer your question properly?? If not, can you explain your question a bit more, and I will be able to help you.
Please can somebody tell me the steps to inserting data accepted from jtextfield from users into mysql database table. Please am very stranded in between a database driven application i want to develop Thanks for your anticipated help.
You can get the value of your text field by: jTextField.getText().
Then you can insert it to the database using an insert query.
To connect to the database, check line 12-18 of my 1st code.
PreparedStatement psmnt = (PreparedStatement) con.prepareStatement(“INSERT INTO table_name VALUES(?)”);
psmnt.setString(1, jTextField.getText());
Hello guys ! I’m beginner to programing.
I need to know how to Save image to MySQL without using prepared Statement.
like this query: (Table Name= img)
(“insert into img (ID,Name,img) values (‘”+String ID+”‘,'”+String Name+”‘,'”+ ? +”‘)”) ;
how to connect j combobox value in mysql server