Search code examples
javaoracle-databasejdbcblobfileinputstream

i cant insert blob image to oracle db using jdbc

I am trying to upload an image into oracle DB using JDBC. I get an exception "The system cannot find the file specified"

This is the code

<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.io.FileInputStream"%>
<%@page import="java.io.*"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.util.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>
 <body>

  <%
try{ 
    Class.forName("oracle.jdbc.driver.OracleDriver");
    System.out.println("Connection loaded");
    Connection c=DriverManager.getConnection       ("jdbc:oracle:thin:@localhost:1521:xe","system","oracle123");
System.out.println("Connection created");
String ll=request.getParameter("user_file");
String id=request.getParameter("id");
File imgfile = new File(ll);//till this pint the code is running correctly
FileInputStream fin = new FileInputStream(imgfile);//working in this point...
PreparedStatement pre = c.prepareStatement("insert into PHOTOS (id,photo) values(?,?)");
pre.setString(1,id);
pre.setBinaryStream(2,fin,(int)imgfile.length());
pre.executeUpdate();
pre.close();
}catch(Exception E){out.println("the eror is  "+ E);}
      %>
</body>
</html>
Solution

  • Assuming that you are coming from a multipart HTTP POST like

    <form action="upload.jsp" method="post" enctype="multipart/form-data">
    <input type="text" name="id" size="10" />
    <br />
    <input type="file" name="user_file" size = "50" />
    <br />
    <input type="submit" value="Upload File" />
</form>

    You need a file upload component like org.apache.commons.fileupload and something like

    Class.forName("oracle.jdbc.driver.OracleDriver");

DiskFileItemFactory factory = new DiskFileItemFactory();
// Set memory threshold - beyond which files are stored in disk 
factory.setSizeThreshold(1024*1024); // 1Mb
// Set temporary location to store files
factory.setRepository(new File(System.getProperty("java.io.tmpdir")));

ServletFileUpload upload = new ServletFileUpload(factory);

try (Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle123")) {
    String id = null;
    InputStream photo = null;
    int photoSize = 0;

    for (FileItem item : upload.parseRequest(request)) {

        if (item.isFormField()) {
                if (item.getFieldName().equals("id")) {
                    id = item.getString();
                }
        } else {
            if (item.getFieldName().equals("user_file")){
                photo = item.getInputStream();
                photoSize = item.getSize();
            }
        }
    }

    assert (id!=null);
    assert (photo!=null);

    try (PreparedStatement pre = c.prepareStatement("insert into PHOTOS (id,photo) values(?,?)")) {
        pre.setString(1,id);
        pre.setBinaryStream(2,item.getInputStream(),item.getSize());
        pre.executeUpdate();
    }
}