How to save image files to mysql with sql code and how to display them with java?

Question

I want to save image files to database by java, file chooser.

My db name is "gallery_images", table name is "image_table", and the columns are "image id(int)"autoincrement, "image(longblob)", and "username(varchar2(45))".

I can add images to my mysql database by workbench to a longblob (right click on longblob cell, "Load Value From File"), and when Ii execute it, it gives me this code:

INSERT INTO `image_table`.`gallery_images` (`image`, `username`) VALUES (?, 'viktor');

The "?" should be the image, so I didn't learn much by this code. When I read a File by java, how could I insert it into my table (with jdbc)?

And when I done with this, I want to query every data from that table:

SELECT * FROM image_table;

I want to save the images from the DB to a List:

private List<Image> images;

I think, I should use the "while(resultset.next()", but how should I get the image from the resultset in the while? Can any one help me with this? Thank you!

(Why) Do you need to save an image in a database? http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay — Andreas, Mar 05 '13 at 19:55
I want to create a JAR program to my friend, who should read and insert datas from/to DB, such as like images — victorio, Mar 05 '13 at 19:56
See [`What is the best way to serialize an image (compatible with Swing) from Java to Android?`](http://stackoverflow.com/a/10004271/597657). — Eng.Fouad, Mar 05 '13 at 19:57
This Tutorial will help you understand How to insert an Image and load an Image file from database http://www.youtube.com/watch?v=AZE4A5sT5q4 — Azad, Mar 05 '13 at 20:42

score 1 · Answer 1 · answered Mar 05 '13 at 21:30

Images insertion (from JPG files on local disk) into MySQL db using JDBC:

package dbTest;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.DriverManager;

import com.example.pompeymenu.Dish;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class DishAdder {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub

        int bytesRead;

        try {
                //setting filenames
                File[] filesd = new File[6];
                String fileName[] = new String[] {"1.JPG", "2.JPG", "3.JPG", "4.JPG", "5.JPG", "6.JPG"};
                String dishName[] = new String[] {
                        "Cake with cherries",
                        "Tiramisu",
                        "Cheese cake",
                        "Pana Cotta",
                        "Ice cream",
                        "Apple Pie"
                };

                Connection conn = null;
                String userName = "your_user_name";
                String password = "your_db_password";
                String url = "jdbc:mysql://your_db_url";
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn = (Connection) DriverManager.getConnection(url, userName, password);
                //Statement s = null;
                PreparedStatement s = null;
                String qryInsert = "INSERT INTO MENU (DISH_NAME, DISH_IMG) VALUES (?, ?)";                  

                //for (int i=5; i<6; i++) {
                    int i=5;
                    ByteArrayOutputStream bos = new ByteArrayOutputStream();
                    filesd[i] = new File("E:/Android/Images/" + fileName[i]);
                    InputStream is = new FileInputStream(filesd[i]);
                    byte[] b = new byte[8096];
                    while ((bytesRead = is.read(b)) != -1) {
                           bos.write(b);
                    }
                    byte[] bytes = bos.toByteArray();

                    //save byte[] to DB

                        s = (PreparedStatement) conn.prepareStatement(qryInsert);
                        s.setString(1, dishName[i]);
                        s.setBytes(2, bytes);
                        s.executeUpdate();  

                //}

                s.close();
                conn.close();

        } catch (Exception e) { e.printStackTrace(); }  


    }

}

http://benignosales.wordpress.com/2011/10/24/jsf-2-primefaces-pgalleria-exemplo-de-galeria-de-fotos/ <-- it could be a solution, he does almost the same I want to, upload image files by primefaces into mysql DB, and then browse the images from the DB, but it doesn't show the DAO files, which (I think) the real solution, how to upload image to DB, and download image into a folder in WebContent folder — victorio, Mar 06 '13 at 14:42
http://knowledgeshare.awardspace.info/?p=87 <-- this is an another example, but in this project, the program doesn't save the file into a folder, it goes to a servlet doGet methot, and that method makes the magic to show the image. Am I right? I don't really understand, how this doGet works, but I will figurate it out... — victorio, Mar 06 '13 at 15:10

score 1 · Answer 2 · answered Mar 05 '13 at 21:34

Read images from MySQL db using JDBC, works for me on Android:

import java.sql.DriverManager;
import java.util.ArrayList;

import android.graphics.Bitmap;
import android.graphics.BitmapFactory;

import com.mysql.jdbc.Blob;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;

/**
 * 
 * Loads products from external MySql database, registered on db4free.net
 * 
 * @author Rodion Altshuler
 *
 */
public class CatalogDBSource implements CatalogSource{

    Connection conn=null;
    String userName = "???";
    String password = "???";
    String url = "jdbc:mysql://???";

     /**
     * Set this flag=true before calling getProducts(), for example, in order to make several requests to DB
     * without re-opening connection each time
     */
    public boolean flag_closeConnection=true; //if set to false, connection after getProducts() will retain opened 


    /**gets products from external DB
     * needs INTERNET permission and MySQL driver       
    */
    @Override
    public ArrayList<Product> getProducts() {


        Thread getProductsThread = new Thread(new Runnable(){

            @Override
            public void run(){

                try {

                    if (conn==null) {
                            Class.forName("com.mysql.jdbc.Driver").newInstance();
                            conn = (Connection) DriverManager.getConnection(url, userName, password);
                    }


                    Statement s = (Statement) conn.createStatement();
                    //String qry = "SELECT _ID, DISH_ID, DISH_NAME, DISH_CATEGORY, DISH_IMG FROM MENU";     
                    String qry = "SELECT _ID, DISH_NAME, DISH_IMG FROM MENU";       
                    s.executeQuery(qry);

                    ResultSet rs = null;
                    rs = (ResultSet) s.getResultSet();

                    while (rs.next()) {
                        int id = rs.getInt("_ID");
                        String productName = rs.getString("DISH_NAME");
                        Blob b = (Blob) rs.getBlob("DISH_IMG");
                        Bitmap productImg = bitmapFromBlob(b);
                        Product p = new Product(id, productName, productImg, "");
                        //adding new item in ArrayList<Product> products, declared in interface CatalogSource
                        products.add(p);
                    }


        rs.close();      
        s.close();


        if (flag_closeConnection) {
            conn.close();
        }


      } catch (Exception e) {
            e.printStackTrace();                     
      }

      }
    });

    getProductsThread.start();

    try {
        getProductsThread.join();
    } catch (InterruptedException e1) {
        e1.printStackTrace();
    }   

        return products;
    }

    /**
     * Converts Blob to Bitmap 
     * @param b Blob object should be converted to Bitmap
     * @return Bitmap object
     */
    static Bitmap bitmapFromBlob(Blob b)  {


        Bitmap bitmap=null;

        try {
            byte[] temp = b.getBytes(1,(int) b.length());   
            bitmap = BitmapFactory.decodeByteArray(temp,0, temp.length);            
        } catch (Exception e) {
            e.printStackTrace();
        }

        return bitmap;

    }


}

score 1 · Answer 3 · answered Mar 05 '13 at 22:11

package fileChooser;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.swing.JFileChooser;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
public class FileChooserDialog {
  public static void main(String[] args) throws IOException, SQLException {
    JFileChooser fileopen = new JFileChooser();
    FileFilter filter = new FileNameExtensionFilter("c files", "c");
    fileopen.addChoosableFileFilter(filter);

    int ret = fileopen.showDialog(null, "Open file");

    if (ret == JFileChooser.APPROVE_OPTION) {
      File file = fileopen.getSelectedFile();
      System.out.println("file()===>>>"+file);
      String url = "jdbc:mysql://localhost:3306/test";
      String username="root";
      String password="root";
      Connection con=null;
      con = DriverManager.getConnection(url,username,password);
      String sql = "INSERT INTO image (id, image) VALUES (?, ?)";
      PreparedStatement stmt = con.prepareStatement(sql);
      stmt.setInt(1, 1);
      //stmt.setString(2, "Java Official Logo");

      FileInputStream   fis = new FileInputStream(file);
      stmt.setBinaryStream(2, fis, (int) file.length());
      stmt.execute();
      fis.close();
      con.close();
    }
  }
}

score 0 · Answer 4 · answered Mar 07 '13 at 01:35

0

If you want to add just one image I guess you can use:

INSERT INTO image_table (username, image) VALUES ('viktor',load_file('path/image.jpg'));

answered Mar 07 '13 at 01:35

Kashtan

25
1
4

How to save image files to mysql with sql code and how to display them with java?

4 Answers4