Retreving PDF File from Database using Java

[Aravind Prasad]

Dear Ranchers,

I am trying to insert a PDF file into the database and to retrieve it. I can insert the pdf successfully. Kindly send me the sample code for retreving the pdf file from the database.

Thanks/Regards

Aravind Prasad

[Aravind Prasad]

Sorry The code i am using is written below.

public void openFile(){
try {
st = con.createStatement();
rs = st.executeQuery("select img from image");
int i = 1;
 
blob = null;
while (rs.next()) {
blob = rs.getBlob("img");
}
 
input = blob.getBinaryStream();
output=new FileOutputStream("C:/Users/Aravind Prassad/Documents/FromDatabase.pdf");
byte by[]=blob.getBytes(0,(int)blob.length());
 
output.write(by);
 

} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (java.sql.SQLException sqle) {
 
}
}
}

Please assist at the earliest.

Thanks/Regards
APrasad

[Ulf Dittmer]

What is the problem with the code - doesn't it work?

You should close the FileOutputStream.

[Aravind Prasad]

it is nt working.
it is creating a file with pdf extension but no data inside it.

[Aravind Prasad]

This is the complete code.
With this code, it is inserting the pdf in the database and also retreving. The file size which is inserting is same as file size retreving. But it is displaying only a blank pdf document. (meaning the pages are empty)

Please help

import java.io.File;
import java.io.FileOutputStream;
import java.io.*;
import java.io.FileReader;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
 
public class PDFFile{
Connection con;
Statement st;
ResultSet rs;
PreparedStatement pst;
Blob blob;
InputStream in;
InputStream input;
OutputStream output;
 
PDFFile(){
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection( "jdbc:mysql:///test","root","root");
} catch (ClassNotFoundException cnf) {
cnf.printStackTrace();
} catch (java.sql.SQLException sql) {
sql.printStackTrace();
}
}
 
public void insertFile() {
FileReader fr;
File file;
try {
file=new File("C:\\Users\\Aravind Prassad\\Documents\\1.pdf");
fr=new FileReader(file);
pst = con.prepareStatement("insert into image (img) values(?)");
pst.setCharacterStream(1,fr,(int)file.length());
pst.executeUpdate();
}
 catch (Exception e) {
e.printStackTrace();
}
}
/*public void openFile(){
try {
st = con.createStatement();
rs = st.executeQuery("select img from image");
int i = 1;
 
blob = null;
while (rs.next()) {
blob = rs.getBlob("img");
}
 
input = blob.getBinaryStream();
output=new FileOutputStream("C:/Users/Aravind Prassad/Documents/FromDatabase.pdf");
byte by[]=blob.getBytes(0,(int)blob.length());
 
output.write(by);
 

} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (java.sql.SQLException sqle) {
 
}
}
}*/
 
public void test()
{try
{st = con.createStatement();
rs = st.executeQuery("Select img from image");
int i=1;
in = null;
while(rs.next())
{in = rs.getBinaryStream(1);
}
 
int available1 = in.available();
byte[] bt = new byte[available1];
in.read(bt);
 
FileOutputStream fout = new FileOutputStream("C:/Users/Aravind Prassad/Documents/test.pdf");
DataOutputStream dout = new DataOutputStream(fout);
dout.write(bt,0,bt.length);
fout.close();
}
catch(Exception e)
{System.out.println(e);
}
 
}
 
public static void main(String args[]){
PDFFile obj=new PDFFile();
obj.insertFile();
//obj.openFile();
obj.test();
}
}

[Ulf Dittmer]

You shouldn't ignore the result of the in.read call: http://faq.javaranch.com/java/ReadDoesntDoWhatYouThinkItDoes

Also, don't use a DataOutputStream - it's not meant for situations like this. Just use the FileOutputStream.

[Aravind Prasad]

Hi Thankyou.. my problem is solved.
The correct code is given below for others to refer.

import java.io.File;
import java.io.FileOutputStream;
import java.io.*;
import java.io.FileReader;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement.*;
import java.sql.*;
 
public class PDFFile{
Connection con;
Statement st;
ResultSet rs;
PreparedStatement pst;
Blob blob;
InputStream in;
InputStream input;
OutputStream output;
 
PDFFile(){
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection( "jdbc:mysql:///test","root","root");
} catch (ClassNotFoundException cnf) {
cnf.printStackTrace();
} catch (java.sql.SQLException sql) {
sql.printStackTrace();
}
}
 
public void insertFile() {
FileReader fr;
File file;
try {
file=new File("C:\\Users\\Aravind Prassad\\Documents\\1.pdf");
 
int fileLength = (int) file.length();
InputStream stream = (InputStream) new FileInputStream(file);
pst = con.prepareStatement("insert into image (img) values(?)");
pst.setAsciiStream(1, stream, fileLength);
 

pst.executeUpdate();
}
 catch (Exception e) {
e.printStackTrace();
}
}
public void test()
{try
{st = con.createStatement();
rs = st.executeQuery("Select img from image");
int i=1;
in = null;
while(rs.next())
{in = rs.getBinaryStream(1);
}
 
int available1 = in.available();
byte[] bt = new byte[available1];
in.read(bt);
 
FileOutputStream fout = new FileOutputStream("C:/Users/Aravind Prassad/Documents/aravind.pdf");
DataOutputStream dout = new DataOutputStream(fout);
dout.write(bt,0,bt.length);
fout.close();
}
catch(Exception e)
{System.out.println(e);
}
 
}
 
public static void main(String args[]){
PDFFile obj=new PDFFile();
obj.insertFile();
obj.test();
}
}
 

Cheers!!!
Aravind Prasad

[chandra reddy pandillapalle]

if i am using the above code for uploading pdf file into databse it is giveing following error


java.sql.SQLException: Io exception: Char array not long enough: javaCharsToUtf8Bytes
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2061)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at PDFFile.insertFile(PDFFile.java:46)
at PDFFile.main(PDFFile.java:79)

[chandra reddy pandillapalle]

please any one give reply what is the problem in that code
Thanks,
Chandra

[Paul Clapham]

Did you use the same type of column that Aravind Prasad used?
[ March 31, 2008: Message edited by: Paul Clapham ]

[Aravind Prasad]

Reddy Bhai.

My table structure is given below. Please go thru this

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a              |
| abc            |
| image          |
| main           |
| myview         |
| sub            |
| test           |
+----------------+
7 rows in set (0.00 sec)
 
mysql> desc image
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | NO   | PRI | NULL    |       |
| img   | blob        | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.08 sec)
 
mysql>

This code will work perfectly. Also use longblob instead of Blob

Kindly update also

Regards

[chandra reddy pandillapalle]

this is my table structure i am also using same
CREATE TABLE IMAGE
(
IMG BLOB
)

please let me know why it is giving error

[joseph okon]

Hi
Am using the same codes as above it is not giving me the required output that is it is not writing to the folder i want it to write to,
what can be wrong with my codes
please i need help
thanks

this is my code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
 
<%@ page import="java.sql.* "%>
<%@ page import="javax.sql.* "%>
<%@ page import="java.io.* "%>
<%@ page import="java.util.* "%>
<html><body bgcolor="gray">
<center>
<table border="1" cellpadding="3" cellspacing="0">
  <tr><td><b>FIle Name</b></td></tr>
 
<%
  
   if(request.getParameter("filename") != null) {

 String dbUserName ="root";
         String dbPassword ="idoreyin";
     String url="jdbc:mysql://localhost:3306/filesdb";
 
    Connection conn;
    Statement st;
    ResultSet rs;
    PreparedStatement pst;
    Blob blob;
    InputStream in;
    InputStream input;
    OutputStream fwriterput;
 
String searchsql = request.getParameter("filename").toLowerCase();
String sqlQuery ="SELECT file_id,file_img FROM bigfiles WHERE file_img='" + searchsql+ " ' ";
  try {
 
 Class.forName("com.mysql.jdbc.Driver");
 
 } catch (ClassNotFoundException cnf) {
cnf.printStackTrace();
}
 
        try {
conn= DriverManager.getConnection(url,dbUserName,dbPassword);
st = conn.createStatement();
 rs = st.executeQuery(sqlQuery);
  int i =1;
  in =null;
  while(rs.next()) {
   blob = rs.getBlob("file_img");
    in = blob.getBinaryStream();
         }
  int available1 = in.available();
  byte[] bt = new byte[available1];
  in.read(bt);
    FileOutputStream fwriter = new FileOutputStream("D:/joe.jpeg");   
    DataOutputStream dout = new DataOutputStream(fwriter);
    dout.write(bt,0,bt.length);
    
}catch (IOException sqle) {
 System.out.println(sqle.getMessage());
 }
catch(Exception ioe) { 
System.out.println(ioe.getMessage());
        }
}
  
%>
<tr><td colspan="2"><p><%= request.getParameter("filename ") %> has been store in your D://filesfolder</p>
</td></tr>
</table></center>
</body></html>

Perfection is for God only

[Aravind Prasad]

Hi Joseph,

Could you please kindly post your table structure also. Also the value you are passing thru request.getParameter().

Regards

Aravind

[joseph okon]

Hi Joseph,
Could you please kindly post your table structure also. Also the value you are passing thru request.getParameter().
Regards
Aravind

this is my table structure:

mysql> show tables;
+-------------------+
| Tables_in_filesdb |
+-------------------+
| bigfiles |
| login |
+-------------------+
2 rows in set (0.06 sec)
mysql> desc bigfiles;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| file_id | varchar(15) | YES | | NULL | |
| file_img | longblob | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.19 sec)
thanks for your assistance
Best regards
joseph

[joseph okon]

Searchfile.jsp

<html>
<body>
<center>
<form action="Search.jsp" method="post">
<p>Enter the name of the file here</p>
<input type="text" name="filename" size="15" maxlength="30" 
 value="<% if(request.getParameter("filename") != null) 
out.print(request.getParameter("filename")); %>"><br>
<input type="Submit" value="Search">
</center>
</body>
</html>

Search.jsp code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
 
<%@ page import="java.sql.* "%>
<%@ page import="javax.sql.* "%>
<%@ page import="java.io.* "%>
<%@ page import="java.util.* "%>
<html><body bgcolor="gray">
<center>
<table border="1" cellpadding="3" cellspacing="0">
  <tr><td><b>FIle Name</b></td></tr>
 
<%
  
   if(request.getParameter("filename") != null) {

 String dbUserName ="root";
         String dbPassword ="idoreyin";
     String url="jdbc:mysql://localhost:3306/filesdb";
 
    Connection conn;
    Statement st;
    ResultSet rs;
    PreparedStatement pst;
    Blob blob;
    InputStream in;
    InputStream input;
    OutputStream fwriterput;
 
String searchsql = request.getParameter("filename").toLowerCase();
String sqlQuery ="SELECT file_id,file_img FROM bigfiles WHERE file_img='" + searchsql+ " ' ";
  try {
 
 Class.forName("com.mysql.jdbc.Driver");
 
 } catch (ClassNotFoundException cnf) {
cnf.printStackTrace();
}
 
        try {
conn= DriverManager.getConnection(url,dbUserName,dbPassword);
st = conn.createStatement();
 rs = st.executeQuery(sqlQuery);
  int i =1;
  in =null;
  while(rs.next()) {
   blob = rs.getBlob("file_img");
    in = blob.getBinaryStream();
         }
  int available1 = in.available();
  byte[] bt = new byte[available1];
  in.read(bt);
    FileOutputStream fwriter = new FileOutputStream("D:/joe.jpeg");   
    DataOutputStream dout = new DataOutputStream(fwriter);
    dout.write(bt,0,bt.length);
    
}catch (IOException sqle) {
 System.out.println(sqle.getMessage());
 }
catch(Exception ioe) { 
System.out.println(ioe.getMessage());
        }
}
  
%>
<tr><td colspan="2"><p><%= request.getParameter("filename ") %> has been store in your D://filesfolder</p>
</td></tr>
</table></center>
</body></html>

[Aravind Prasad]

Hoi Joseph

Try to modify the query you are using from

String sqlQuery ="SELECT file_id,file_img FROM bigfiles WHERE file_img='" + searchsql+ " ' ";

to

String sqlQuery ="SELECT file_id,file_img FROM bigfiles WHERE file_id='" + searchsql+ " ' ";

Also pass the parameter value as the file_id value.

Regards

Aravind Prasad

[joseph okon]

Aravind Prasad
i have try the changes you made but it is still not doing it?? it produces null when run. i am short of idea can you write a piece of code to get parameter from html/jsp and retrieve the corresponding item from the database then place it in a folder for me? thanks