How to browse and save image into Oracle DB using SQL?

Question

I am working on an Employee Management System using Java/Swing/Oracle 10g database and
have a table named photo with photoID as number andphoto as blob and I am using JFileChooser that opens and displays a photo but it doesn't save it to my database.

Error

java.sql.SQLException: Invalid column index

Code

enter code here

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * Photo.java
 *
 * Created on Mar 18, 2014, 5:51:22 PM
 */

package ems;

import java.io.*;
import javax.swing.ImageIcon;
import javax.swing.JFileChooser;
import java.sql.*;

/**
 *
 * @author css102134
 */
public class Photo extends javax.swing.JFrame {

    /** Creates new form Photo */

    public Photo() {
        initComponents();
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">
    private void initComponents() {

        Jlabel = new javax.swing.JLabel();
        browseButton = new javax.swing.JButton();
        jLabel2 = new javax.swing.JLabel();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        Jlabel.setText("choose file:");

        browseButton.setText("browse");
        browseButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                browseButtonActionPerformed(evt);
            }
        });

        jLabel2.setText("photo");

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(33, 33, 33)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jLabel2)
                    .addComponent(Jlabel))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(browseButton)
                .addContainerGap(241, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(28, 28, 28)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(Jlabel)
                    .addComponent(browseButton))
                .addGap(29, 29, 29)
                .addComponent(jLabel2)
                .addContainerGap(206, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>

    private void browseButtonActionPerformed(java.awt.event.ActionEvent evt) {

        JFileChooser fc = new JFileChooser();
fc.showOpenDialog(this);
File f = fc.getSelectedFile();
String path = f.getAbsolutePath();
Jlabel.setIcon(new ImageIcon(path));
try
{

Class.forName("oracle.jdbc.driver.OracleDriver");
 String url="jdbc:oracle:thin:@localhost:1521:XE";
            String u="ems2";
            String p="ems2";
Connection con = DriverManager.getConnection(url,u,p);

PreparedStatement ps = con.prepareStatement("insert into photo (photos) values(?)");

  FileInputStream fin=new FileInputStream("D:/Workspace/ems_ui/photos/prasanth.jpg");
  System.out.println("hi");
  ps.setBinaryStream(3, fin);
int status = ps.executeUpdate();
if(status>0)
{
jLabel2.setText("successfully inserted in Db");
}
else
{
jLabel2.setText("Image not inserted in Db");
}
con.close();

}
catch(Exception e)
{
System.out.println(e);
}

    }

    /**
    * @param args the command line arguments
    */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new Photo().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify
    private javax.swing.JLabel Jlabel;
    private javax.swing.JButton browseButton;
    private javax.swing.JLabel jLabel2;
    // End of variables declaration

}

Answer 1

There's a few things that jump out...

You create a PreparedStatement PreparedStatement ps = con.prepareStatement("insert into photo values(?,?)"); but only bind a single value ps.setBinaryStream(3, fin, len); and not even to a valid parameter...

Either the photoID parameter needs to excluded from the query or a value needs to bound to the parameter.

Let's assume that the ID is auto generated by the database, then you could use...

PreparedStatement ps = con.prepareStatement("insert into photo (photo) values(?)");
ps.setBinaryStream(1, fin);

If not, you would need use something more like...

PreparedStatement ps = con.prepareStatement("insert into photo (photoID, photo) values(?, ?)");
ps.setInt(1, id);
ps.setBinaryStream(2, fin);

Also, you never close the stream, this is leaving resources open and preventing them from getting marked for garbage collection. Golden rule, if you open, you close it...

If you're using Java 7...

try (FileInputStream fin = new FileInputStream(f); Connection con = DriverManager.getConnection(url, u, p)) {...

Otherwise...

FileInputStream fin = null;
Connection con = null;
try {
    fin = new FileInputStream(f);
    con = DriverManager.getConnection(url, u, p);
    //...
} catch (...) {
    //..
} finally {
    try {
        fin.close();
    } catch (Excepiton exp) {
    }
    try {
        con.close();
    } catch (Excepiton exp) {
    }
}