Saving files to MySQL databases using Java
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.
Share this:
25 thoughts on “ Saving files to MySQL databases using Java ”
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());
setblob(arg1,arg2) accepts only 2 parameters and the second parameter should be of blob type….its not wrkng can u explain..pls
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 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 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: 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.
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+”‘,’”+ ? +”‘)”) ;
Java Programs and Examples with Output
This utility tool writes file content to a Database CLOB .
The tool reads the list of files , creates CLOB of the files and
updates the existing CLOB in the database with the CLOB created newly.
This tool is especially useful in WebDepot architecture where XSLs(stored as CLOB )
need to be updated in the DB.
This tool can also be used for converting XML files as CLOBS and updating in the DB.
The SQL query can be appropriately modified to suit user’s needs
Change the below values appropriately to conenct to the DB of your choice:
String url = «jdbc:oracle:thin:@THDEV02.NAM.NSROOT.NET:1521:UAUTHDEV»;
String user = «uauth_app4»;
String password = «uapdev04»;
Create a file : c:\\ListofFilesToUpdateCLOBInDB.txt that contains the list of files to
be converted as CLOB object and updated in the Database .
Sample ListofFilesToUpdateCLOBInDB file will contain:
C:\Data\views\sp97924_wd_2007_09_shw\project\ct\webconfig\wd_conf\DATAFILES\PAGEXSL\enUS\PLCN\PLCN_JJILL_MENU.xsl
C:\Data\views\sp97924_wd_2007_09_shw\project\ct\webconfig\wd_conf\DATAFILES\PAGEXSL\enUS\PLCN\PLCN_JJILL_HEADER_REG.xsl
Query to update or insert can be modified as per user’s needs.
Sample update query :
prepStm = conn.prepareStatement ( «UPDATE «+tableName+» SET «+ CLOBColumnName+» = ? WHERE «+updateCondition+» ='»+xslName+»‘» );
package utility; import java.io.BufferedReader; import java.io.File; import java.io.FileReader; import java.io.StringReader; import java.io.Writer; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.HashMap; import java.util.Map; import oracle.sql.CLOB; /** * * This utility tool writes file content to a Database CLOB . * The tool reads the list of files , creates CLOB of the files and * updates the existing CLOB in the database with the CLOB created newly. * * This tool is especially useful in WebDepot architecture where XSLs(stored as CLOB ) * need to be updated in the DB. * * This tool can also be used for converting XML files as CLOBS and updating in the DB. * * The SQL query can be appropriately modified to suit user's needs * * */ public class WriteFileToDBClob < String FileName = null; String FileContent = null; String xslName = null; static oracle.jdbc.OracleConnection conn = null; public static void main(String[] args) < try < Class.forName("oracle.jdbc.driver.OracleDriver"); >catch(ClassNotFoundException e) < System.out.println("Could not load the JDBC driver. " + e); >/* * Connect to the corresponding DB */ String url = "jdbc:oracle:thin:@THDEV02.NAM.NSROOT.NET:1521:UAUTHDEV"; String user = "uauth_app4"; String password = "uapdev04"; try < conn = (oracle.jdbc.OracleConnection) java.sql.DriverManager.getConnection(url, user, password); System.out.println("connected"); >catch(SQLException e) < System.out.println("Connection attempt failed. " + e); e.printStackTrace(); >catch(Exception genex) < genex.printStackTrace(); >WriteFileToDBClob writeFileToDBClob= new WriteFileToDBClob(); writeFileToDBClob.readUpdateFileList(); > public void readUpdateFileList()< try< BufferedReader reader= new BufferedReader(new FileReader("c:\\ListofFilesToUpdateCLOBInDB.txt")); /* The list of files should be in this format : C:\Data\views\sp97924_wd_2007_09_shw\project\ct\webconfig\wd_conf\DATAFILES\PAGEXSL\enUS\PLCN\PLCN_JJILL_MENU.xsl C:\Data\views\sp97924_wd_2007_09_shw\project\ct\webconfig\wd_conf\DATAFILES\PAGEXSL\enUS\PLCN\PLCN_JJILL_HEADER_REG.xsl */ while((FileName = reader.readLine())!=null) < if(!(FileName.indexOf("%20")>-1)) < readFile(); updateFileClobInDB(); >else < System.out.println("This file name contains a space which cannot be processed:"+FileName+"\n"); >> > catch(Exception e) < e.printStackTrace(); >finally < conn = null; >> public void readFile() < try< File f = new File(FileName); xslName = f.getName().substring(0,f.getName().length()-4); BufferedReader reader = new BufferedReader(new FileReader(f)); String line = null; int count = 0; StringBuffer sb = new StringBuffer(); String replaceString = null; Map temp = new HashMap(0); while((line = reader.readLine()) != null) < sb.append(line+"\r\n"); >FileContent = sb.toString(); >catch(Exception e) < System.out.println(e); e.printStackTrace(); >> public void updateFileClobInDB() < boolean autoCommit = false; PreparedStatement prepStm=null; try < autoCommit = conn.getAutoCommit(); // autocommit MUST be false for this CLOB conn.setAutoCommit(false); CLOB newClob = getCLOB(FileContent, conn); FileContent = null ; // update clob-data in database String tableName = "wd_pagexsl"; String updateCondition = "template"; String CLOBColumnName = "XSL"; prepStm = conn.prepareStatement ( "UPDATE "+tableName+" SET "+ CLOBColumnName+" = ? WHERE "+updateCondition+" ='"+xslName+"'" ); prepStm.setClob ( 1, newClob ); prepStm.executeUpdate(); conn.commit(); // commit after each update. You can change this if you prefer committing after all updates. >catch(SQLException sqle) < sqle.printStackTrace(); >> private static CLOB getCLOB(String fileContent, Connection conn) throws SQLException < CLOB tempClob = null; try< // If the temporary CLOB has not yet been created, create new tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION); // Open the temporary CLOB in readwrite mode to enable writing tempClob.open(CLOB.MODE_READWRITE); // Get the output stream to write Writer tempClobWriter = tempClob.getCharacterOutputStream(); // Write the data into the temporary CLOB tempClobWriter.write(fileContent); // Flush and close the stream tempClobWriter.flush(); tempClobWriter.close(); // Close the temporary CLOB tempClob.close(); >catch(SQLException sqlexp) < tempClob.freeTemporary(); sqlexp.printStackTrace(); >catch(Exception exp) < tempClob.freeTemporary(); exp.printStackTrace(); >return tempClob; > >